Spaces:
Sleeping
Sleeping
File size: 4,242 Bytes
a3d10df 336608c a3d10df | 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 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | import os
import pandas as pd
from dotenv import load_dotenv
from sqlalchemy import create_engine
load_dotenv('../../.env.local')
class DataLoader:
def __init__(self):
db_url = os.getenv("SUPABASE_DB_URL")
if not db_url:
raise ValueError("SUPABASE_DB_URL not found in .env.local")
# SQLAlchemy requires postgresql:// instead of postgres:// in some versions,
# but supabase usually provides postgresql://
self.engine = create_engine(db_url)
def get_rankings(self):
query = """
SELECT team_id, rating, rank
FROM worldcup_team_rankings
"""
df = pd.read_sql(query, self.engine)
return df.set_index('team_id')
def get_team_form(self):
query = """
SELECT team_id, match_date, opponent_elo, goals_for, goals_against, is_home
FROM worldcup_team_form
ORDER BY team_id, match_date DESC
"""
return pd.read_sql(query, self.engine)
def get_teams(self):
query = """
SELECT id as team_id, is_host
FROM worldcup_teams
"""
df = pd.read_sql(query, self.engine)
return df.set_index('team_id')
def get_matches(self):
query = """
SELECT id, stage, round, home_team_id, away_team_id,
home_score_90, away_score_90, status
FROM worldcup_matches
WHERE status != 'cancelled'
"""
return pd.read_sql(query, self.engine)
def get_manual_features(self):
query = """
SELECT match_id, odds_1x2_home, odds_1x2_draw, odds_1x2_away,
injury_impact_home, injury_impact_away,
lineup_strength_home, lineup_strength_away
FROM worldcup_manual_features
"""
df = pd.read_sql(query, self.engine)
return df.set_index('match_id')
def get_latest_odds_summary(self):
query = """
WITH ranked AS (
SELECT
match_id,
bookmaker_key,
bookmaker_title,
market_key,
market_title,
home_odds,
draw_odds,
away_odds,
last_update,
snapshot_time,
ROW_NUMBER() OVER (
PARTITION BY match_id, bookmaker_key, market_key
ORDER BY COALESCE(last_update, snapshot_time) DESC, snapshot_time DESC
) AS rn
FROM worldcup_market_odds_snapshots
WHERE market_key = 'h2h'
)
SELECT *
FROM ranked
WHERE rn = 1
ORDER BY match_id, bookmaker_title
"""
try:
return pd.read_sql(query, self.engine)
except Exception:
return pd.DataFrame()
def get_latest_weather_summary(self):
query = """
WITH ranked AS (
SELECT
match_id,
forecast_time,
snapshot_time,
temperature_c,
apparent_temperature_c,
humidity_pct,
precipitation_probability_pct,
precipitation_mm,
wind_speed_kmh,
wind_gusts_kmh,
weather_code,
ROW_NUMBER() OVER (
PARTITION BY match_id
ORDER BY snapshot_time DESC
) AS rn
FROM worldcup_weather_snapshots
)
SELECT *
FROM ranked
WHERE rn = 1
"""
try:
df = pd.read_sql(query, self.engine)
if df.empty:
return df
return df.set_index('match_id')
except Exception:
return pd.DataFrame()
def close(self):
self.engine.dispose()
if __name__ == "__main__":
loader = DataLoader()
print("Rankings loaded:", len(loader.get_rankings()))
print("Matches loaded:", len(loader.get_matches()))
loader.close()
|