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 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')