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