Advertisement
Guest User

Stack Overflow MCVE - SqlAlchemy schema extension

a guest
Jan 22nd, 2017
252
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 2.95 KB | None | 0 0
  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('-----------')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement