def load_data(records, aa_data):
with closing(engine().connect()) as conn:
conn.execute(persons.delete())
conn.execute(orcids.delete())
conn.execute(dlcs.delete())
conn.execute(aa_articles.delete())
for r in records:
conn.execute(persons.insert(), r['person'])
conn.execute(orcids.insert(), r['orcid'])
conn.execute(dlcs.insert(), r['dlc'])
conn.execute(aa_articles.insert(), aa_data)
yield
with closing(engine().connect()) as conn:
conn.execute(persons.delete())
conn.execute(orcids.delete())
conn.execute(dlcs.delete())
conn.execute(aa_articles.delete())
def articles():
"""An article generator.
Returns an iterator over the AA_ARTICLE table.
"""
sql = select([aa_articles]) \
.where(aa_articles.c.ARTICLE_ID != None) \
.where(aa_articles.c.ARTICLE_TITLE != None) \
.where(aa_articles.c.DOI != None) \
.where(aa_articles.c.MIT_ID != None)
with closing(engine().connect()) as conn:
for row in conn.execute(sql):
yield dict(zip(row.keys(), row))
def people():
"""A person generator.
Returns an iterator of person dictionaries.
"""
sql = select([persons.c.MIT_ID, persons.c.KRB_NAME_UPPERCASE,
persons.c.FIRST_NAME, persons.c.MIDDLE_NAME,
persons.c.LAST_NAME, persons.c.EMAIL_ADDRESS,
persons.c.ORIGINAL_HIRE_DATE, dlcs.c.DLC_NAME,
persons.c.PERSONNEL_SUBAREA_CODE, orcids.c.ORCID]) \
.select_from(persons.outerjoin(orcids).join(dlcs)) \
.where(persons.c.EMAIL_ADDRESS != None) \
.where(persons.c.LAST_NAME != None) \
.where(persons.c.KRB_NAME_UPPERCASE != None) \
.where(persons.c.KRB_NAME_UPPERCASE != 'UNKNOWN') \
.where(persons.c.MIT_ID != None) \
.where(persons.c.APPOINTMENT_END_DATE >= datetime(2009, 1, 1)) \
.where(func.upper(dlcs.c.ORG_HIER_SCHOOL_AREA_NAME).in_(AREAS)) \
.where(persons.c.PERSONNEL_SUBAREA_CODE.in_(PS_CODES)) \
.where(func.upper(persons.c.JOB_TITLE).in_(TITLES))
with closing(engine().connect()) as conn:
for row in conn.execute(sql):
yield dict(zip(row.keys(), row))
def app_init():
engine.configure('sqlite://')
metadata.bind = engine()
metadata.create_all()