def lock_tables(cursor, tables=frozenset(nfldbproj_tables)):
log('Locking write access to tables {}...'.format(', '.join(tables)),
end='')
cursor.execute(';\n'.join(
'LOCK TABLE {} IN SHARE ROW EXCLUSIVE MODE'.format(table)
for table in tables))
log('done.')
def lock_tables(cursor, tables=frozenset(nfldbproj_tables)):
log('Locking write access to tables {}...'.format(', '.join(tables)), end='')
cursor.execute(';\n'.join(
'LOCK TABLE {} IN SHARE ROW EXCLUSIVE MODE'.format(table)
for table in tables
))
log('done.')
def add_name_disambiguations(db, ids_by_names):
"""
Inserts rows to `name_disambiguation`.
The parameter `ids_by_names` should be a dictionary mapping names to ids.
"""
log('Writing rows to name_disambiguation...')
with Tx(db) as c:
lock_tables(c, ['name_disambiguation'])
c.execute('INSERT INTO name_disambiguation (name_as_scraped, fantasy_player_id) VALUES '
+ ', '.join(c.mogrify('(%s, %s)', item) for item in ids_by_names.items()))
log('done.')
def assign_gsis_ids(db, df, metadata):
log('finding game ids...', end='')
for (week, team, home, opp), sub_df in df.groupby(['week', 'team', 'home', 'opp']):
gsis_id = get_gsis_id(
db,
season_year=metadata['season_year'],
season_type=metadata.get('season_type', 'Regular'),
week=week,
home_team=team if home else opp,
)
df.loc[(df['week'] == week) & (df['team'] == team), 'gsis_id'] = gsis_id
log('done')
def _insert_if_new(cursor, table, data, **kwargs):
"""
Check if row specified in dictionary `data` exists in table `table`,
and if it doesn't, insert it.
Keyword arguments (notably `returning`) are passed to `_insert_dict`.
"""
pk_only_data = _subdict(METADATA_PRIMARY_KEYS[table], data, enforce_key_presence=True)
if not _exists(cursor, table, pk_only_data):
log('inserting new {}...'.format(table), end='')
result = _insert_dict(cursor, table, data, **kwargs)
log('done.')
return result
def add_name_disambiguations(db, ids_by_names):
"""
Inserts rows to `name_disambiguation`.
The parameter `ids_by_names` should be a dictionary mapping names to ids.
"""
log("Writing rows to name_disambiguation...")
with Tx(db) as c:
lock_tables(c, ["name_disambiguation"])
c.execute(
"INSERT INTO name_disambiguation (name_as_scraped, fantasy_player_id) VALUES "
+ ", ".join(c.mogrify("(%s, %s)", item) for item in ids_by_names.items())
)
log("done.")
def _insert_if_new(cursor, table, data, **kwargs):
"""
Check if row specified in dictionary `data` exists in table `table`,
and if it doesn't, insert it.
Keyword arguments (notably `returning`) are passed to `_insert_dict`.
"""
pk_only_data = _subdict(METADATA_PRIMARY_KEYS[table],
data,
enforce_key_presence=True)
if not _exists(cursor, table, pk_only_data):
log('inserting new {}...'.format(table), end='')
result = _insert_dict(cursor, table, data, **kwargs)
log('done.')
return result
def error(*args, **kwargs):
log('ERROR:', *args, file=sys.stderr, **kwargs)
def warn(*args, **kwargs):
log('WARNING:', *args, file=sys.stderr, **kwargs)
def error(*args, **kwargs):
log('ERROR:', *args, file=sys.stderr, **kwargs)
def warn(*args, **kwargs):
log('WARNING:', *args, file=sys.stderr, **kwargs)
def assign_player_ids(db, df):
log('finding player ids...', end='')
df['fantasy_player_id'] = None
for name, sub_df in df.groupby('name'):
df.loc[df['name'] == name, 'fantasy_player_id'] = name_to_id(db, sub_df['name'].iloc[0])
log('done')
