Skip to content

Instantly share code, notes, and snippets.

@soimafreak soimafreak/Bash Off Secret
Last active Dec 17, 2015

Embed
What would you like to do?
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
You can’t perform that action at this time.