Examples of Bash off in Action
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment