Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- from datetime import datetime, timedelta
- import os
- from sqlalchemy import Column, BigInteger, String, CHAR, TIMESTAMP
- from sqlalchemy import create_engine, func
- from sqlalchemy.ext.declarative import declarative_base
- from sqlalchemy.inspection import inspect
- from sqlalchemy.orm.session import sessionmaker
- # Part 1: Setup
- OrmBase = declarative_base()
- class Encounter(OrmBase):
- __tablename__ = 'encounter_table'
- encounterid = Column(BigInteger, primary_key=True)
- title = Column(String(64))
- starttime = Column(TIMESTAMP)
- endtime = Column(TIMESTAMP)
- class Combatant(OrmBase):
- __tablename__ = 'combatant_table'
- combatantid = Column(BigInteger, primary_key=True)
- ally = Column(CHAR(1))
- name = Column(String(64))
- starttime = Column(TIMESTAMP)
- endtime = Column(TIMESTAMP)
- damage = Column(BigInteger)
- envFile = 'envfile.txt'
- if envFile != '' and envFile is not None:
- with open(envFile) as envVars:
- for line in envVars:
- var, val = line.split('=', 1)
- var = var.strip()
- val = val.strip()
- os.environ[var] = val
- engine = create_engine(os.environ['DBCONNECTION'])
- Session = sessionmaker(bind=engine)
- dbSession = Session()
- # Part 2: Where Things Work
- columns = [
- {'attr': 'title', 'name': 'Encounter'},
- {'attr': 'starttime', 'name': 'Start Time'}
- ]
- objects = dbSession.query(Encounter) \
- .filter(Encounter.endtime > (datetime.now() - timedelta(days=33))) \
- .order_by(Encounter.endtime.desc()).limit(5).all()
- for obj in objects:
- for col in columns:
- v = inspect(obj).attrs[col['attr']].value
- printVal = col['formatter'](v) if col.get('formatter', None) else v
- print('Column:', col['name'], ', Value:', printVal)
- print('-----------')
- # Part 3: Trying to extend the data model
- def characterQuery(dbSession):
- return dbSession.query(
- Combatant.name.label('name'),
- func.min(Combatant.starttime).label('firstSeen'),
- func.max(Combatant.endtime).label('lastSeen'),
- func.sum(Combatant.damage).label('damage'),
- ).group_by(Combatant.name)
- # Part 4: Where things fall apart
- columns = [
- {'attr': 'name', 'name': 'Character Name'},
- {'attr': 'firstSeen', 'name': 'First Seen'},
- {'attr': 'lastSeen', 'name': 'Last Seen'},
- {'attr': 'duration', 'name': 'Total Seen (s)'},
- {'attr': 'damage', 'name': 'Total Damage'},
- ]
- objects = characterQuery(dbSession) \
- .having(func.max(Combatant.endtime) >
- (datetime.now() - timedelta(days=33))) \
- .order_by(func.max(Combatant.endtime).desc()).limit(25).all()
- for obj in objects:
- for col in columns:
- v = inspect(obj).attrs[col['attr']].value
- printVal = col['formatter'](v) if col.get('formatter', None) else v
- print('Column:', col['name'], ', Value:', printVal)
- print('-----------')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement