stat_import.py

import nfl_data_py as nfl
import pandas as pd
from database import DatabaseConnector
from models import Player, WeeklyStat

def load_weekly_stats(target_years):
    db = DatabaseConnector()
    session = db.session
    
    print(f"Downloading weekly stats for seasons: {target_years}...")
    
    weekly_df = nfl.import_weekly_data(target_years)
    weekly_df = weekly_df[weekly_df['season_type'] == 'REG']
    
    print(f"Processing {len(weekly_df)} weekly stat lines...")
    
    # Dictionary to cache player IDs to minimize database queries even though it probably doesnt matter but I thought it was a good idea
    player_cache = {}
    
    for index, row in weekly_df.iterrows():
        full_name = row.get('player_display_name')
        if pd.isna(full_name):
            continue
            
        if full_name not in player_cache:
            name_parts = str(full_name).split(' ', 1)
            first = name_parts[0]
            last = name_parts[1] if len(name_parts) > 1 else ""
            
            # Query the DB to find the integer player_id assigned to them
            db_player = session.query(Player).filter_by(first_name=first, last_name=last).first()
            
            if db_player:
                player_cache[full_name] = db_player.player_id
            else:
                player_cache[full_name] = None
                
        player_id = player_cache[full_name]
        
        # Storing weekly stats
        if player_id:
            pass_tds = int(row.get('passing_tds', 0))
            rush_tds = int(row.get('rushing_tds', 0))
            rec_tds = int(row.get('receiving_tds', 0))
            total_tds = pass_tds + rush_tds + rec_tds
            
            stat_record = WeeklyStat(
                player_id=player_id,
                season=int(row['season']),
                week=int(row['week']),
                
                passing_yards=int(row.get('passing_yards', 0)),
                passing_tds=pass_tds,
                
                rushing_yards=int(row.get('rushing_yards', 0)),
                rushing_tds=rush_tds,
                
                receptions=int(row.get('receptions', 0)),
                receiving_yards=int(row.get('receiving_yards', 0)),
                receiving_tds=rec_tds,
                
                total_tds=total_tds
            )
            session.merge(stat_record)
            
    try:
        session.commit()
        print("Weekly stats successfully mapped and loaded!")
    except Exception as e:
        session.rollback()
        print(f"Database insertion failed: {e}")
    finally:
        session.close()

if __name__ == "__main__":
    load_weekly_stats([2022, 2023, 2024]) # These three years are the specific seasons of data the database will contain