-
Notifications
You must be signed in to change notification settings - Fork 0
/
database_utils.py
48 lines (37 loc) · 1.58 KB
/
database_utils.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
import pandas as pd
def save_club_stats(club_stats, db_conn):
"""Saves club statistics in the database"""
df_stats = pd.DataFrame(club_stats).transpose()
# Line above has turned the column 'club_name' into the df index
df_stats = df_stats.reset_index()
# Reseting index doesn't restore the original column name
df_stats = df_stats.rename(columns={"index": "club_name"})
cursor = db_conn.cursor()
# Create a table where club stats will be kept
query_create_table = "CREATE TABLE IF NOT EXISTS clubs (club_name, league_id, points, goal_difference)"
cursor.execute(query_create_table)
db_conn.commit()
# Dump the club stats to the database table
df_stats.to_sql("clubs", db_conn, if_exists="replace", index=False)
def get_scoreboard(league_id, db_conn):
"""Retrieves scoreboard data.
Rank is determined by three keys:
1. Number of Points: Descending
2. Goal difference: Descending
3. Name: Ascending
Returns:
scoreboard_data (list of tuples): Each element of the tuple
consists out of 3 elements - (club_name, points, goal_difference)
"""
cursor = db_conn.cursor()
query_get_scoreboard = """
SELECT club_name, points, goal_difference
FROM clubs WHERE league_id = ?
ORDER BY points DESC, goal_difference DESC, club_name ASC
"""
query_arguments = [league_id]
cursor.execute(query_get_scoreboard, query_arguments)
db_conn.commit()
scoreboard_data = cursor.fetchall()
success = len(scoreboard_data) > 0
return scoreboard_data, success