Статистика футбольных матчей

CREATE TABLE raw_matches (     id SERIAL PRIMARY KEY,     season INTEGER,     data JSONB );

import json import os import psycopg2 import sys from typing import Any, List  # Database connection try:     conn = psycopg2.connect(         host="localhost",         port=5432,         dbname="football",         user="postgres",         password="postgres"     )     cur = conn.cursor() except psycopg2.Error as e:     print(f"Database connection error: {e}")     sys.exit(1)  # Directory containing JSON files json_dir = '/mnt/c/Disk/dimension/footballstats/JSON'  def flatten_matches(data: Any) -> List[Any]:     """Flatten nested match structures"""     matches = []          if isinstance(data, list):         for item in data:             if isinstance(item, dict) and 'home_team' in item and 'away_team' in item:                 # This is a match object                 matches.append(item)             elif isinstance(item, list):                 # This is a nested list, recursively flatten                 matches.extend(flatten_matches(item))             else:                 print(f"Unexpected item type: {type(item)}")     elif isinstance(data, dict):         # Check if this is a match object         if 'home_team' in data and 'away_team' in data:             matches.append(data)         else:             # This might be a container with matches inside             for key, value in data.items():                 if isinstance(value, (list, dict)):                     matches.extend(flatten_matches(value))          return matches  for filename in os.listdir(json_dir):     if filename.startswith('match_data') and filename.endswith('.json'):         filepath = os.path.join(json_dir, filename)                  try:             # Extract season from filename             season = int(filename[10:14])         except ValueError as e:             print(f"Error extracting season from {filename}: {e}")             continue          try:             with open(filepath, 'r', encoding='utf-8') as f:                 raw_data = json.load(f)                              # Debug: Print the type and structure of the loaded data             print(f"DEBUG: {filename} - Type of loaded data: {type(raw_data)}")             if isinstance(raw_data, list) and len(raw_data) > 0:                 print(f"DEBUG: {filename} - First element type: {type(raw_data[0])}")                          # Flatten the data structure to extract all matches             matches = flatten_matches(raw_data)             print(f"DEBUG: {filename} - Found {len(matches)} matches after flattening")                          if len(matches) > 0:                 print(f"DEBUG: {filename} - First match: {json.dumps(matches[0], indent=2)}")                      except Exception as e:             print(f"Error reading/parsing {filename}: {e}")             continue          match_count = 0         error_count = 0                  for match in matches:             try:                 cur.execute(                     "INSERT INTO raw_matches (season, data) VALUES (%s, %s)",                     (season, json.dumps(match))                 )                 match_count += 1             except psycopg2.Error as e:                 error_count += 1                 print(f"Database error inserting match from {filename}: {e}")                 print(f"Problematic match data: {json.dumps(match)}")             except Exception as e:                 error_count += 1                 print(f"Unexpected error with match from {filename}: {e}")                 print(f"Problematic match data: {json.dumps(match)}")          print(f"Loaded {match_count} matches from {filename} with {error_count} errors")                  try:             conn.commit()         except psycopg2.Error as e:             print(f"Commit error after {filename}: {e}")             try:                 conn.rollback()             except:                 pass  cur.close() conn.close()

CREATE TABLE matches (     year VARCHAR,     home_team VARCHAR,     away_team VARCHAR,     score VARCHAR,     goals_minutes_1 TIMESTAMP,     goals_minutes_2 TIMESTAMP,     goals_minutes_3 TIMESTAMP,     goals_minutes_4 TIMESTAMP,     goals_minutes_5 TIMESTAMP,     goals_minutes_6 TIMESTAMP,     goals_minutes_7 TIMESTAMP,     goals_minutes_8 TIMESTAMP,     goals_minutes_9 TIMESTAMP,     goals_minutes_10 TIMESTAMP,     home_goals INTEGER,     away_goals INTEGER,     goals INTEGER,     home_result VARCHAR,     away_result VARCHAR );

import psycopg2 import json import logging from datetime import datetime, timedelta from typing import Optional, Dict, Any, List  # Set up logging logging.basicConfig(     level=logging.INFO,     format='%(asctime)s - %(levelname)s - %(message)s',     handlers=[         logging.FileHandler('data_load.log'),         logging.StreamHandler()     ] )  def parse_goal_minute(minute_str: str) -> Optional[int]:     """     Parse goal minute string, handling additional time notation     Examples:      - "45+1'" becomes 46     - "90+2'" becomes 92     - "30'" becomes 30     """     try:         if not minute_str:             return None                      # Remove any trailing apostrophes/quotes         minute_str = minute_str.rstrip("'’\"")                  if '+' in minute_str:             parts = minute_str.split('+')             base_minute = int(parts[0])             added_minutes = int(parts[1])             return base_minute + added_minutes         else:             return int(minute_str)     except (ValueError, IndexError, AttributeError) as e:         logging.error(f"Error parsing goal minute '{minute_str}': {e}")         return None  def clean_score(score_str: str) -> str:     """     Clean score string by removing asterisks and other non-numeric characters     """     if not score_str:         return score_str          # Remove asterisks and other non-standard characters     cleaned = score_str.replace('*', '').replace('--', '0')          # Handle cases like "0 - 3*" -> "0 - 3"     if ' - ' in cleaned:         parts = cleaned.split(' - ')         # Ensure both parts are numeric or can be converted to 0         try:             int(parts[0])         except ValueError:             parts[0] = '0'         try:             int(parts[1])         except ValueError:             parts[1] = '0'         cleaned = ' - '.join(parts)          return cleaned  def process_matches():     conn = None     cursor = None     processed_count = 0     error_count = 0     skipped_count = 0          try:         # Database connection - update with your credentials         conn = psycopg2.connect(             host="localhost",             database="football",             user="postgres",             password="postgres",             port=5432         )          cursor = conn.cursor()         logging.info("Connected to database successfully")          # Fetch raw matches         cursor.execute("SELECT id, season, data FROM raw_matches ORDER BY id")         raw_matches = cursor.fetchall()         logging.info(f"Fetched {len(raw_matches)} raw matches")          # Process each match         for match_id, season, data in raw_matches:             try:                 # data is already a dict from psycopg2 for JSONB fields                 match_data = data                                  # Log full data for problematic matches (for debugging)                 if match_id in [3177, 4941, 5002, 5640, 9710, 14831, 15279, 17657, 20733,                                27177, 28565, 28633, 32717, 36030, 36052, 39788, 39794, 39862, 40158]:                     logging.warning(f"DEBUG - Problematic match {match_id} full data: {match_data}")                                  # Validate required fields                 required_fields = ['score', 'home_team', 'away_team']                 missing_fields = [field for field in required_fields if field not in match_data]                 if missing_fields:                     logging.warning(f"Match {match_id} missing required fields: {missing_fields}. Full data: {match_data}")                     error_count += 1                     continue                                  # Parse score safely with cleaning                 try:                     original_score = match_data['score']                     cleaned_score = clean_score(original_score)                                          score_parts = cleaned_score.split(' - ')                     if len(score_parts) != 2:                         raise ValueError(f"Invalid score format after cleaning: {cleaned_score} (original: {original_score})")                                          home_goals = int(score_parts[0])                     away_goals = int(score_parts[1])                     total_goals = home_goals + away_goals                                          # Skip matches with more than 10 goals                     if total_goals > 10:                         logging.info(f"Skipping match {match_id} with {total_goals} goals (more than 10)")                         skipped_count += 1                         continue                                          # Determine results                     home_result = 'Wins' if home_goals > away_goals else 'Draws' if home_goals == away_goals else 'Losses'                     away_result = 'Wins' if away_goals > home_goals else 'Draws' if away_goals == home_goals else 'Losses'                                      except (ValueError, IndexError) as e:                     logging.error(f"Error parsing score for match {match_id}: {e}. Original score: '{match_data['score']}'. Full data: {match_data}")                     error_count += 1                     continue                                  # Collect all goal times from both teams                 all_goal_times = []                                  # Process home goals                 home_goals_minutes = match_data.get('home_goals_minutes', [])                 for minute_str in home_goals_minutes:                     minute = parse_goal_minute(minute_str)                     if minute is not None:                         goal_time = datetime(2000, 1, 1) + timedelta(minutes=minute)                         all_goal_times.append(goal_time)                                  # Process away goals                 away_goals_minutes = match_data.get('away_goals_minutes', [])                 for minute_str in away_goals_minutes:                     minute = parse_goal_minute(minute_str)                     if minute is not None:                         goal_time = datetime(2000, 1, 1) + timedelta(minutes=minute)                         all_goal_times.append(goal_time)                                  # Sort goal times chronologically                 all_goal_times.sort()                                  # Prepare goal columns                 goal_columns = [None] * 10                 for i, goal_time in enumerate(all_goal_times):                     if i < 10:  # Only store up to 10 goals                         goal_columns[i] = goal_time                                  # Insert the match record                 cursor.execute("""                     INSERT INTO matches (                         year, home_team, away_team, score,                          goals_minutes_1, goals_minutes_2, goals_minutes_3,                          goals_minutes_4, goals_minutes_5, goals_minutes_6,                          goals_minutes_7, goals_minutes_8, goals_minutes_9,                          goals_minutes_10,                         home_goals, away_goals, goals,                         home_result, away_result                     ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)                 """, (                     str(season),                      match_data['home_team'],                      match_data['away_team'],                      match_data['score'],                     goal_columns[0], goal_columns[1], goal_columns[2],                      goal_columns[3], goal_columns[4], goal_columns[5],                      goal_columns[6], goal_columns[7], goal_columns[8],                      goal_columns[9],                     home_goals,                      away_goals,                      total_goals,                     home_result,                      away_result                 ))                                  processed_count += 1                 if processed_count % 1000 == 0:                     logging.info(f"Progress: {processed_count} matches processed")                              except Exception as e:                 logging.error(f"Error processing match {match_id}: {e}. Full data: {match_data}")                 error_count += 1                 continue                  # Commit the transaction         conn.commit()         logging.info(f"Data load completed: {processed_count} matches processed, {skipped_count} skipped, {error_count} errors")              except Exception as e:         logging.error(f"Database connection error: {e}")         if conn:             conn.rollback()     finally:         if cursor:             cursor.close()         if conn:             conn.close()         logging.info("Database connection closed")  def test_parse_goal_minute():     """Test function for goal minute parsing"""     test_cases = [         ("45+1'", 46),         ("90+2'", 92),         ("30'", 30),         ("26'", 26),         ("7'", 7),         ("", None),         (None, None),         ("invalid", None)     ]          logging.info("Testing goal minute parsing...")     for input_str, expected in test_cases:         result = parse_goal_minute(input_str)         status = "✓" if result == expected else "✗"         logging.info(f"{status} '{input_str}' -> {result} (expected: {expected})")  def test_clean_score():     """Test function for score cleaning"""     test_cases = [         ("0 - 2*", "0 - 2"),         ("3* - 0", "3 - 0"),         ("0 - 3*", "0 - 3"),         ("-- - --*", "0 - 0"),         ("1 - 0", "1 - 0"),         ("2 - 2", "2 - 2"),     ]          logging.info("Testing score cleaning...")     for input_str, expected in test_cases:         result = clean_score(input_str)         status = "✓" if result == expected else "✗"         logging.info(f"{status} '{input_str}' -> '{result}' (expected: '{expected}')")  if __name__ == "__main__":     # Run tests first     test_parse_goal_minute()     test_clean_score()          # Process matches     process_matches()

-- Basic indexes for common filtering CREATE INDEX idx_matches_year ON matches ("year"); CREATE INDEX idx_matches_home_team ON matches (home_team); CREATE INDEX idx_matches_away_team ON matches (away_team); CREATE INDEX idx_matches_home_result ON matches (home_result); CREATE INDEX idx_matches_away_result ON matches (away_result);  -- Composite indexes for common query patterns CREATE INDEX idx_matches_team_year ON matches (home_team, "year"); CREATE INDEX idx_matches_away_year ON matches (away_team, "year"); CREATE INDEX idx_matches_year_result ON matches ("year", home_result);  -- Indexes for goal-related queries CREATE INDEX idx_matches_home_goals ON matches (home_goals); CREATE INDEX idx_matches_away_goals ON matches (away_goals); CREATE INDEX idx_matches_total_goals ON matches (goals);  -- Indexes for score column CREATE INDEX idx_matches_score ON matches (score);  -- Indexes for goals_minutes columns CREATE INDEX idx_matches_goals_minutes_1 ON matches (goals_minutes_1); CREATE INDEX idx_matches_goals_minutes_2 ON matches (goals_minutes_2); CREATE INDEX idx_matches_goals_minutes_3 ON matches (goals_minutes_3); CREATE INDEX idx_matches_goals_minutes_4 ON matches (goals_minutes_4); CREATE INDEX idx_matches_goals_minutes_5 ON matches (goals_minutes_5); CREATE INDEX idx_matches_goals_minutes_6 ON matches (goals_minutes_6); CREATE INDEX idx_matches_goals_minutes_7 ON matches (goals_minutes_7); CREATE INDEX idx_matches_goals_minutes_8 ON matches (goals_minutes_8); CREATE INDEX idx_matches_goals_minutes_9 ON matches (goals_minutes_9); CREATE INDEX idx_matches_goals_minutes_10 ON matches (goals_minutes_10);

Важно! После загрузки данных не забываем создать индексы.