async def get_teams(): logger.info('Getting list of teams') postgres_connection = await asyncpg.connect(**postgres_creds()) result = await postgres_connection.fetch('''SELECT ratingId FROM teams''') await postgres_connection.close() ids = [r[0] for r in result] logger.info(f'{len(ids)} active teams') return ids
async def get_team_name(team) -> str: postgres_connection = await asyncpg.connect(**postgres_creds()) query = ''' SELECT name FROM teams WHERE ratingid = $1 ''' data = await postgres_connection.fetch(query, team) return data[0]
async def get_all_teams_for_release(release_id=None): postgres_connection = await asyncpg.connect(**postgres_creds()) query = f''' SELECT team_id, team_name, three_months, twelve_months FROM medians WHERE release_id = $1 ORDER BY three_months DESC ''' data = await postgres_connection.fetch(query, release_id) return [dict(row) for row in data]
async def get_all_releases_for_team(team) -> List[Dict]: postgres_connection = await asyncpg.connect(**postgres_creds()) query = ''' SELECT release_date, release_id, three_months, twelve_months FROM medians WHERE team_id = $1 ORDER BY release_date DESC ''' data = await postgres_connection.fetch(query, team) return [dict(row) for row in data]
async def save_data(data): table = 'team_releases' postgres_connection = await asyncpg.connect(**postgres_creds()) await create_temp_table(postgres_connection, table) logger.info(f'Created {table}_temp table') import_result = await import_data(postgres_connection, data, table) import_count = import_result.split(' ')[-1] logger.info(f'Copied {import_count} records to {table}_temp') await replace_old_table(postgres_connection, table) logger.info(f'Replaced {table} with the new one') await drop_old_table(postgres_connection, table) logger.info(f'Dropped {table}_old') await recreate_indexes(postgres_connection) logger.info(f'Recreated indexes') await create_functions(postgres_connection) logger.info(f'Created SQL functions')