Examples of Bash off in Action

Bash Off

from sqlalchemy import create_engine, MetaData, ForeignKey
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

import time
from openpyxl import load_workbook

# create database model stuff
engine = create_engine('sqlite:///the.db')
Base = declarative_base()

class BookingModel(Base):

    __tablename__ = "bookingmodel"
    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __init__(self, name):
        self.name = name


class InvalidCatB(Base):

    __tablename__ = "invalidcatb"
    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __init__(self, name):
        self.name = name


class BookingCat(Base):

    __tablename__ = "bookingmodel_invalidcatb"
    id = Column(Integer, primary_key=True)
    booking_id = Column(Integer, ForeignKey("bookingmodel.id"))
    category_id = Column(Integer, ForeignKey("invalidcatb.id"))

    def __init__(self, booking_id, category_id):
        self.booking_id = booking_id
        self.category_id = category_id

def connect():
    Session = sessionmaker(bind=engine)
    return Session()


def DropAllTables():
    meta = MetaData(engine)
    meta.reflect()
    meta.drop_all()


def update_booking(name,session,bm_list):
    # Check if entry is in db
    #insert
    new_booking = BookingModel(name)
    session.add(new_booking)
    q = session.query(
        BookingModel.id
    ).filter(BookingModel.name==name).first()
    bm_list[name] = q[0]
    return bm_list



def update_invalidcatb(name, session,icb_list):
    # Check if entry is in db
    #insert
    new_invalcatb = InvalidCatB(name)
    session.add(new_invalcatb)
    q = session.query(
        InvalidCatB.id
    ).filter(InvalidCatB.name==name).first()
    icb_list[name] = q[0]
    return icb_list


def update_bookingcat(bm,icb,session):
    # Check if entry is in db
    q = session.query(
        BookingCat.booking_id
    ).filter(BookingCat.booking_id==bm,BookingCat.category_id==icb).first()
    if q is None:
        new_bookingcat = BookingCat(booking_id=bm,category_id=icb)
        session.add(new_bookingcat)


def timeit(method):
    def timed(*args, **kw):
        ts = time.time()
        result = method(*args, **kw)
        te = time.time()
        print 'Created lookup table in: %2.2f sec' % (te-ts)
        return result
    return timed


def print_lookup_table():
    result = engine.execute("""
        SELECT invalidcatb.name, bookingmodel.name FROM bookingmodel_invalidcatb
        LEFT JOIN invalidcatb ON invalidcatb.id = bookingmodel_invalidcatb.category_id
        LEFT JOIN bookingmodel ON bookingmodel.id = bookingmodel_invalidcatb.booking_id
        ORDER BY bookingmodel.name DESC""")
    print "BookingModel,InvalidCatB"
    for row in result:
        print row[1],",",row[0]
    result.close()

# excel reading stuff
data_file = 'bookingmodel.xlsx'
sheet_name = 'BookingModel-Key Value Table'
ws = load_workbook(filename=data_file, use_iterators = True).get_sheet_by_name(name=sheet_name)

db = connect()
start = 6
bmlist = dict()
icblist = dict()

@timeit
def main():

    DropAllTables()
    Base.metadata.create_all(engine)

    for row in ws.iter_rows():
        if row[7].row >= start:
            bm=row[8].internal_value
            icb=row[7].internal_value
            if bm not in bmlist:
                update_booking(bm, db, bmlist)
            if icb not in icblist:
                update_invalidcatb(icb, db, icblist)

            update_bookingcat(bmlist[bm],icblist[icb],db)

    db.commit()
    print_lookup_table()

if __name__ == "__main__":
    main()