Guest User

Stack Overflow MCVE - SqlAlchemy schema extension

a guest
Jan 22nd, 2017
173
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. from datetime import datetime, timedelta
  2. import os
  3.  
  4. from sqlalchemy import Column, BigInteger, String, CHAR, TIMESTAMP
  5. from sqlalchemy import create_engine, func
  6. from sqlalchemy.ext.declarative import declarative_base
  7. from sqlalchemy.inspection import inspect
  8. from sqlalchemy.orm.session import sessionmaker
  9.  
  10. # Part 1: Setup
  11.  
  12. OrmBase = declarative_base()
  13.  
  14. class Encounter(OrmBase):
  15.     __tablename__ = 'encounter_table'
  16.     encounterid = Column(BigInteger, primary_key=True)
  17.     title = Column(String(64))
  18.     starttime = Column(TIMESTAMP)
  19.     endtime = Column(TIMESTAMP)
  20.  
  21.  
  22. class Combatant(OrmBase):
  23.     __tablename__ = 'combatant_table'
  24.     combatantid = Column(BigInteger, primary_key=True)
  25.     ally = Column(CHAR(1))
  26.     name = Column(String(64))
  27.     starttime = Column(TIMESTAMP)
  28.     endtime = Column(TIMESTAMP)
  29.     damage = Column(BigInteger)
  30.  
  31. envFile = 'envfile.txt'
  32. if envFile != '' and envFile is not None:
  33.     with open(envFile) as envVars:
  34.         for line in envVars:
  35.             var, val = line.split('=', 1)
  36.             var = var.strip()
  37.             val = val.strip()
  38.             os.environ[var] = val
  39.  
  40. engine = create_engine(os.environ['DBCONNECTION'])
  41. Session = sessionmaker(bind=engine)
  42. dbSession = Session()
  43.  
  44. # Part 2: Where Things Work
  45.  
  46. columns = [
  47.       {'attr': 'title', 'name': 'Encounter'},
  48.       {'attr': 'starttime', 'name': 'Start Time'}
  49.   ]
  50.  
  51. objects = dbSession.query(Encounter) \
  52.         .filter(Encounter.endtime > (datetime.now() - timedelta(days=33))) \
  53.         .order_by(Encounter.endtime.desc()).limit(5).all()
  54.  
  55. for obj in objects:
  56.     for col in columns:
  57.         v = inspect(obj).attrs[col['attr']].value
  58.         printVal = col['formatter'](v) if col.get('formatter', None) else v
  59.         print('Column:', col['name'], ', Value:', printVal)
  60.     print('-----------')
  61.  
  62. # Part 3: Trying to extend the data model
  63.  
  64. def characterQuery(dbSession):
  65.     return dbSession.query(
  66.         Combatant.name.label('name'),
  67.         func.min(Combatant.starttime).label('firstSeen'),
  68.         func.max(Combatant.endtime).label('lastSeen'),
  69.         func.sum(Combatant.damage).label('damage'),
  70.                            ).group_by(Combatant.name)
  71.  
  72. # Part 4: Where things fall apart
  73.  
  74. columns = [
  75.       {'attr': 'name', 'name': 'Character Name'},
  76.       {'attr': 'firstSeen', 'name': 'First Seen'},
  77.       {'attr': 'lastSeen', 'name': 'Last Seen'},
  78.       {'attr': 'duration', 'name': 'Total Seen (s)'},
  79.       {'attr': 'damage', 'name': 'Total Damage'},
  80.   ]
  81.  
  82. objects = characterQuery(dbSession) \
  83.         .having(func.max(Combatant.endtime) >
  84.                 (datetime.now() - timedelta(days=33))) \
  85.         .order_by(func.max(Combatant.endtime).desc()).limit(25).all()
  86.  
  87. for obj in objects:
  88.     for col in columns:
  89.         v = inspect(obj).attrs[col['attr']].value
  90.         printVal = col['formatter'](v) if col.get('formatter', None) else v
  91.         print('Column:', col['name'], ', Value:', printVal)
  92.     print('-----------')
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×