#!/usr/bin/env python3 """ Fetch Soccer Match Data from Football-Data.org API Free tier: 10 requests/minute, covers major European leagues. No xG directly but has scores and can be cross-referenced. For xG, we'll also try API-Football free tier. Run: Daily after matches """ import requests import psycopg2 import os import time from datetime import datetime, timezone, timedelta import argparse FOOTBALL_DATA_BASE = 'https://api.football-data.org/v4' # League codes for Football-Data.org LEAGUE_CODES = { 'epl': 'PL', # Premier League 'laliga': 'PD', # Primera Division 'seriea': 'SA', # Serie A 'bundesliga': 'BL1', # Bundesliga 'ligue1': 'FL1', # Ligue 1 'ucl': 'CL', # Champions League } REQUEST_DELAY = 6 # Free tier: 10 req/min def load_db_url(): env_path = '/var/www/html/eventheodds/.env' try: with open(env_path, 'r') as f: for line in f: if line.startswith('SPORTS_DATABASE_URL='): return line.split('=', 1)[1].strip().split('?')[0] except FileNotFoundError: pass return os.environ.get('SPORTS_DATABASE_URL', '').split('?')[0] def load_api_key(): """Load Football-Data.org API key from env""" env_path = '/var/www/html/eventheodds/.env' try: with open(env_path, 'r') as f: for line in f: if line.startswith('FOOTBALL_DATA_API_KEY='): return line.split('=', 1)[1].strip() except FileNotFoundError: pass return os.environ.get('FOOTBALL_DATA_API_KEY') def ensure_soccer_table(cur): """Create/update SoccerXG table""" cur.execute(''' CREATE TABLE IF NOT EXISTS "SoccerXG" ( id BIGSERIAL PRIMARY KEY, league VARCHAR(50) NOT NULL, "gameId" BIGINT, "gameDate" DATE, "homeTeam" VARCHAR(100), "awayTeam" VARCHAR(100), "homeScore" INT, "awayScore" INT, "homeXG" FLOAT, "awayXG" FLOAT, "xgDiff" FLOAT, "homeXGPerformance" FLOAT, "awayXGPerformance" FLOAT, source VARCHAR(50) DEFAULT 'football-data', "externalMatchId" VARCHAR(50), season INT, status VARCHAR(50), "createdAt" TIMESTAMP DEFAULT NOW(), UNIQUE(league, "gameDate", "homeTeam", "awayTeam") ) ''') cur.execute('CREATE INDEX IF NOT EXISTS "SoccerXG_league_date_idx" ON "SoccerXG" (league, "gameDate")') def fetch_matches(league, api_key=None, days_back=30): """Fetch recent matches from Football-Data.org""" if league not in LEAGUE_CODES: print(f" Unknown league: {league}") return [] code = LEAGUE_CODES[league] # Calculate date range date_to = datetime.now(timezone.utc).date() date_from = date_to - timedelta(days=days_back) url = f'{FOOTBALL_DATA_BASE}/competitions/{code}/matches' params = { 'dateFrom': date_from.isoformat(), 'dateTo': date_to.isoformat(), } headers = {} if api_key: headers['X-Auth-Token'] = api_key try: resp = requests.get(url, params=params, headers=headers, timeout=30) if resp.status_code == 403: print(f" API key required or rate limited") return [] if resp.status_code == 429: print(f" Rate limited, waiting...") time.sleep(60) return [] if resp.status_code != 200: print(f" API returned {resp.status_code}") return [] data = resp.json() matches = [] for match in data.get('matches', []): try: utc_date = match.get('utcDate', '') game_date = datetime.fromisoformat(utc_date.replace('Z', '+00:00')).date() if utc_date else None home_team = match.get('homeTeam', {}).get('shortName') or match.get('homeTeam', {}).get('name', '') away_team = match.get('awayTeam', {}).get('shortName') or match.get('awayTeam', {}).get('name', '') score = match.get('score', {}) full_time = score.get('fullTime', {}) half_time = score.get('halfTime', {}) home_score = full_time.get('home') away_score = full_time.get('away') home_ht = half_time.get('home') away_ht = half_time.get('away') matches.append({ 'id': match.get('id'), 'date': game_date, 'home_team': home_team, 'away_team': away_team, 'home_score': home_score, 'away_score': away_score, 'home_ht': home_ht, 'away_ht': away_ht, 'status': match.get('status'), 'season': match.get('season', {}).get('startDate', '')[:4], }) except Exception as e: continue return matches except Exception as e: print(f" Error: {e}") return [] def fetch_matches_for_league(league, api_key=None, days_back=30): """Fetch and store match data""" db_url = load_db_url() conn = psycopg2.connect(db_url) cur = conn.cursor() ensure_soccer_table(cur) conn.commit() print(f"\n{league.upper()}: Fetching from Football-Data.org...") time.sleep(REQUEST_DELAY) matches = fetch_matches(league, api_key, days_back) print(f" Found {len(matches)} matches") inserted = 0 finished = 0 for match in matches: if not match['date'] or not match['home_team']: continue if match['status'] == 'FINISHED': finished += 1 season = int(match['season']) if match['season'] else None # Calculate second-half scores home_2h = None away_2h = None if match.get('home_score') is not None and match.get('home_ht') is not None: home_2h = match['home_score'] - match['home_ht'] if match.get('away_score') is not None and match.get('away_ht') is not None: away_2h = match['away_score'] - match['away_ht'] try: cur.execute(''' INSERT INTO "SoccerXG" ( league, "gameDate", "homeTeam", "awayTeam", "homeScore", "awayScore", "homeHT", "awayHT", "home2H", "away2H", "externalMatchId", season, status, source ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'football-data') ON CONFLICT (league, "gameDate", "homeTeam", "awayTeam") DO UPDATE SET "homeScore" = COALESCE(EXCLUDED."homeScore", "SoccerXG"."homeScore"), "awayScore" = COALESCE(EXCLUDED."awayScore", "SoccerXG"."awayScore"), "homeHT" = COALESCE(EXCLUDED."homeHT", "SoccerXG"."homeHT"), "awayHT" = COALESCE(EXCLUDED."awayHT", "SoccerXG"."awayHT"), "home2H" = COALESCE(EXCLUDED."home2H", "SoccerXG"."home2H"), "away2H" = COALESCE(EXCLUDED."away2H", "SoccerXG"."away2H"), status = EXCLUDED.status ''', ( league, match['date'], match['home_team'], match['away_team'], match['home_score'], match['away_score'], match.get('home_ht'), match.get('away_ht'), home_2h, away_2h, str(match['id']) if match['id'] else None, season, match['status'] )) inserted += 1 except Exception as e: print(f" Insert error: {e}") conn.commit() cur.close() conn.close() print(f" {league}: Inserted/updated {inserted} matches, {finished} finished") return {'matches': inserted, 'finished': finished} def main(): parser = argparse.ArgumentParser(description='Fetch soccer data from Football-Data.org') parser.add_argument('--leagues', type=str, default='epl,laliga,seriea,bundesliga', help='Comma-separated leagues') parser.add_argument('--days', type=int, default=60, help='Days to look back') args = parser.parse_args() api_key = load_api_key() if not api_key: print("Note: No FOOTBALL_DATA_API_KEY found - using unauthenticated access (limited)") print("=" * 60) print("FETCH SOCCER DATA FROM FOOTBALL-DATA.ORG") print(f"Time: {datetime.now(timezone.utc).isoformat()}") print("=" * 60) leagues = [l.strip().lower() for l in args.leagues.split(',')] total_matches = 0 total_finished = 0 for league in leagues: result = fetch_matches_for_league(league, api_key, args.days) total_matches += result['matches'] total_finished += result['finished'] print(f"\n{'='*60}") print(f"TOTAL: {total_matches} matches, {total_finished} finished") print("=" * 60) if __name__ == '__main__': main()