Spaces:
Running
Running
File size: 2,769 Bytes
1790bde | 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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | """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}")
|