pengpeng9099's picture
Deploy World Cup 2026 Predictor (docker space)
1790bde verified
Raw History Blame Contribute Delete
2.77 kB
"""SQLite schema + helpers. Single-file DB, zero-config."""
from __future__ import annotations
import sqlite3
from contextlib import contextmanager
from config import DB_PATH
SCHEMA = """
CREATE TABLE IF NOT EXISTS teams (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
confederation TEXT,
fifa_rank INTEGER,
elo REAL,
attack REAL,
defence REAL,
group_label TEXT -- A..L for the 48 WC qualifiers, else NULL
);
CREATE TABLE IF NOT EXISTS matches (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date TEXT NOT NULL,
home_team TEXT NOT NULL,
away_team TEXT NOT NULL,
home_score INTEGER,
away_score INTEGER,
tournament TEXT,
city TEXT,
country TEXT,
neutral INTEGER,
stage TEXT -- 'group' / 'R32' ... only for WC 2026 fixtures
);
CREATE INDEX IF NOT EXISTS idx_matches_date ON matches(date);
CREATE INDEX IF NOT EXISTS idx_matches_tourn ON matches(tournament);
CREATE TABLE IF NOT EXISTS predictions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
home_team TEXT NOT NULL,
away_team TEXT NOT NULL,
neutral INTEGER DEFAULT 1,
p_home REAL, p_draw REAL, p_away REAL,
exp_home REAL, exp_away REAL,
top_scores TEXT, -- JSON [[h,a,prob],...]
grid TEXT, -- JSON 2D matrix
over25 REAL, btts REAL,
model_run_id INTEGER,
UNIQUE(home_team, away_team, neutral, model_run_id)
);
CREATE TABLE IF NOT EXISTS simulations (
run_id INTEGER,
team TEXT,
group_label TEXT,
p_group_win REAL, p_advance REAL, p_r16 REAL, p_qf REAL,
p_sf REAL, p_final REAL, p_win REAL,
exp_points REAL,
PRIMARY KEY (run_id, team)
);
CREATE TABLE IF NOT EXISTS model_runs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
model_name TEXT,
xi REAL,
trained_at TEXT,
n_matches INTEGER,
rps REAL,
brier REAL,
logloss REAL,
n_sims INTEGER,
notes TEXT
);
CREATE TABLE IF NOT EXISTS calibration (
run_id INTEGER,
bin_lo REAL, bin_hi REAL,
predicted REAL, observed REAL, count INTEGER
);
"""
def connect() -> sqlite3.Connection:
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA journal_mode=WAL;")
return conn
def init_db() -> None:
with connect() as conn:
conn.executescript(SCHEMA)
@contextmanager
def cursor():
conn = connect()
try:
yield conn.cursor()
conn.commit()
finally:
conn.close()
if __name__ == "__main__":
init_db()
print(f"DB initialised at {DB_PATH}")