def create_goal_features_table():
create = '''CREATE TABLE goal_feature
(id integer PRIMARY KEY AUTOINCREMENT,
goal_diff_with_away real,
home_goals_with_away real,
away_goals_with_home real,
home_goal_mean real,
away_goal_mean real,
match_id integer,
FOREIGN KEY(match_id) REFERENCES match(id));'''
execute_statement(create)
def create_simulation_table():
query = '''CREATE TABLE IF NOT EXISTS "match_simulation" (
"id" integer PRIMARY KEY AUTOINCREMENT,
"match_id" INTEGER,
"date" TIMESTAMP,
"home_team" TEXT,
"away_team" TEXT,
"home_score" INTEGER,
"away_score" INTEGER,
"outcome" INTEGER,
"home_win_prob" REAL,
"draw_prob" REAL,
"away_win_prob" REAL
);'''
execute_statement(query)
def create_match_table():
query = '''CREATE TABLE IF NOT EXISTS "match" (
"id" integer PRIMARY KEY AUTOINCREMENT,
"date" TIMESTAMP,
"home_team" TEXT,
"away_team" TEXT,
"home_score" INTEGER,
"away_score" INTEGER,
"tournament" TEXT,
"city" TEXT,
"country" TEXT,
"year" INTEGER,
"simulation" INTEGER
);'''
execute_statement(query)
def create_and_init_elo_table():
def init_elo_for_every_team(init_date='1800-01-01', init_value=1500):
statement = '''select home_team as teams from match
union
select away_team from match'''
teams = [team[0] for team in fetchall(statement)]
tuples = [(init_date, team, init_value) for team in teams]
execute_many(
"insert into elo_rating (date, team, elo) values (?, ?, ?)",
tuples)
create_elo_table = '''CREATE TABLE elo_rating
(id integer PRIMARY KEY AUTOINCREMENT,
date text, team text, elo real,
match_id integer,
FOREIGN KEY(match_id) REFERENCES match(id));'''
execute_statement(create_elo_table)
init_elo_for_every_team()
def insert(**kwargs):
query = build_insert_query(kwargs, table_name)
values = get_value_tuple(kwargs)
execute_statement((query, values))
return execute_statement("select last_insert_rowid()")
def delete_tables():
for table in tables:
query = f"DROP TABLE IF EXISTS {table};"
execute_statement(query)
def delete_all():
query = 'delete from match_simulation;'
execute_statement(query)
def delete_simulations():
query = 'delete from match where simulation=1;'
execute_statement(query)
def delete_elos_after_date(date):
query = 'delete from elo_rating where date > ?;'
execute_statement((query, (date, )))