Ni|sdZddlZddlZddlZddlZddlZddlZddlZddlmZm Z m Z ddl m Z ddl mZmZmZmZmZddlZej(j+dej(j-ej(j/eddlmZe ej6j6dz Zej;ree5ZeD]Z e jCse jEdr&d e vs+e jCjGd d \Z$Z%ejLjOe$e%jCd jCd  dddejLjQd dZ)ejLjQdZ*ejLjQddZ+e*se,dejZd iddddddddddddddddd dddd!d"d#d$dd%d&d'd dd(d)d*ddd+d,d d-dd.d,d d-dd/d0d'd*dd1d2d'd*dd3d4d'd*dd5d6d'd*dd7d8d'd*dd9d:dddd;dd?Z0d@ee1fdAZ2d@ee3fdBZ4dCed@efdDZ5dCed@eee1ee1ffdEZ6dCed@eefdFZ7dGZ8dHeedIe9d@ee1e1ffdJZ:dKeed@e1fdLZ;dMeedIe9d@e1fdNZdRZ?e@dSk(re?yy#1swYxYw)Ta SportsGameOdds API - Historical Data Backfill Script Backfills historical games, odds, scores, and player stats from SportsGameOdds API. Supports date ranges for 2024, 2025, and 2026 seasons. Usage: python scripts/backfill_sportsGameOdds.py [league] [--start YYYY-MM-DD] [--end YYYY-MM-DD] python scripts/backfill_sportsGameOdds.py --all-leagues --year 2025 python scripts/backfill_sportsGameOdds.py nba --season 2024-25 Examples: python scripts/backfill_sportsGameOdds.py nba --start 2025-01-01 --end 2025-12-31 python scripts/backfill_sportsGameOdds.py --all-leagues --year 2025 python scripts/backfill_sportsGameOdds.py nfl --season 2025-26 N)datetimetimezone timedelta)Path)DictListAnyOptionalTuple)normalize_to_fullz.env#="'SPORTSGAMEODDS_BASE_URLz!https://api.sportsgameodds.com/v2SPORTSGAMEODDS_API_KEYSPORTS_DATABASE_URLz4ERROR: SPORTSGAMEODDS_API_KEY not set in environmentnbaNBA )id season_start season_endnflNFL mlbMLBnhlNHLncaabNCAAB ncaafNCAAFwnbaWNBAmmaUFC ufceplEPLlaligaLALIGAserieaSERIEA bundesliga BUNDESLIGAligue1LIGUE1uclUEFA_CHAMPIONS_LEAGUEmlsMLS) rrr!r$r&r*r0r4r6r8r:r<c veZdZdZefdedefdZddededefd Z dd ed ed ed e de ef dZ d ede efdZ y)SportsGameOddsBackfiller0Backfill historical data from SportsGameOdds APIapi_keybase_urlc||_||_tj|_|jj j |ddd|_d|_d|_ y)Nzapplication/json)z x-api-keyz Content-Typer) rErFrequestsSessionsessionheadersupdate requests_madeevents_fetchedplayers_fetched)selfrErFs "scripts/backfill_sportsGameOdds.py__init__z!SportsGameOddsBackfiller.__init__Ss]   '')  ## .%   Nendpointparamsreturncl|jd|}d}t|D]} |jj||d}|xjdz c_|j dk(r7t d|dzdz}td|d tj|}|j d k(rd d d cS|j|jcSd dd S#tjj$r<}||dz krtjdYd}~d t|d cYd}~cSd}~wwxYw)z!Make API request with retry logic/r#<)rUtimeoutrirz Rate limited, waiting zs...iFzInvalid API key)successerrorr/NzMax retries exceeded)rFrangerJgetrM status_codeminprinttimesleepraise_for_statusjsonrH exceptionsRequestExceptionstr) rPrTrUurl max_retriesattemptrespwaites rQ_requestz!SportsGameOddsBackfiller._request_s+q + [) ;G ;||''FB'G""a'"##s*rGaK2#56D4TF$?@JJt$##s*',7HII%%'yy{" ;,!+ABB &&77 ;[1_,JJqM#(3q6::  ;s0A8CC2CD37D. D.&D3.D3 league_id start_dateend_dateinclude_completedc g}d}d}||d|ddd}|sd|d< |d z }|r||d <|jd |} | jd d rd| vr!td| jdd |S| jdg} | s |S|j| |xjt | z c_td|dt | dt |d| jd}|s |St jd)z0Fetch all events in a date range with paginationNrz T00:00:00Zz T23:59:59Zd)leagueID startsAfter startsBeforelimittrue oddsAvailableTrcursoreventsr[r\z Error: z Unknown errordataz Batch : z events (total: ) nextCursorg333333?)ror^raextendrNlenrbrc) rPrprqrrrs all_eventsr|batchrUresultr}s rQfetch_events_in_rangez.SportsGameOddsBackfiller.fetch_events_in_range|sD "(\4'j 3  !&,F? # QJE#)x ]]8V4F::i.'V2C FJJw$H#IJK$!ZZ+F   f %   3v; .  JugRF }4DS_DUUVW XZZ -F  JJsO3rScVg}d} |dd}|r||d<|jd|}|jdds |S|jdg}|s |S|j||xjt |z c_|jd }|s |St j d ) zFetch all players for a leagueNTi)rvryr|playersr[r~rg?)ror^rrOrrbrc)rPrp all_playersr|rUrrs rQ fetch_playersz&SportsGameOddsBackfiller.fetch_playerss "+c:F#)x ]]9f5F::i.jj,G   w '  CL 0 ZZ -F JJsO+rS)N)T) __name__ __module__ __qualname____doc__ SGO_BASE_URLrhrRrroboolrrrrSrQrCrCPs:5A ! !s !CCdCdC<9=-s--s-15-AEd-^stDzrSrCrVc|yt|ttfr t|St|tr t|j ddSy#t $rYywxYw)zParse odds string to integerN+r) isinstanceintfloatrhreplace ValueErrorvalues rQparse_odds_valuers` }%#u&5z% u}}S"-. .   sA AAc|yt|ttfr t|St|tr t|Sy#t$rYywxYw)zParse line value to floatN)rrrrhrrs rQparse_line_valuersT }%#u&U|% <    s A A Aeventc L|jdi}|jdi}|jdi}t|jdxs|jd}t|jdxs|jd}|jdi}t|jdxs|jd}|jd i}t|jd xs|jd } t|jd xs|jd } t|jdxs|jd} t|jdxs|jd} ||||r| nd| | | | dS)zExtract odds data from eventoddszpoints-home-game-ml-homezpoints-away-game-ml-awaybookOddsfairOddszpoints-home-game-sp-home bookSpread fairSpreadzpoints-all-game-ou-over bookOverUnder fairOverUnder openBookOdds openFairOddsopenBookSpreadopenFairSpreadopenBookOverUnderopenFairOverUnderN) moneylineHome moneylineAway spreadHome spreadAwaytotalopenMoneylineHomeopenSpreadHome openTotal)r^rr) r odds_dataml_homeml_awayhome_mlaway_mlsp_home spread_homeou_overr open_home_ml open_spread open_totals rQ extract_oddsrss &"%Imm6;Gmm6;Gw{{:6Q'++j:QRGw{{:6Q'++j:QRGmm6;G"7;;|#<#Y L@YZKmm5r:G W[[9YW[[=Y ZE$GKK$?$^7;;~C^_L"7;;/?#@#aGKKP`DabK!'++.A"B"fgkkReFfgJ! !&1{lt)%  rSc|jdi}|jdi}|jdijd}|jdijd}|T|jdi}|jdijd}|jdijd}||fS)zExtract final scores from eventresultsgamehomepointsawayteamsscorer^)rr game_results home_score away_scorers rQextract_scoresr sii 2&G;;vr*L!!&"-11(;J!!&"-11(;J '2&YYvr*..w7 YYvr*..w7 z !!rScg}|jd}|jd}|jdi}|jd}|rPtj|jdd}|jdk\r |j n|j dz }n|S|jd i}|jd i} |jd i} | jd ijd ijdd} | jdijd ijdd} |jdi} dD]}| j|i}|d k(r| n| }|d k(r| n| }|jd i}|j D]c\}}t|ts| j|i}|jd|}|jd}|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd |jd!d" }|j D]1\}}| |j||d#||||||||t|d$ 3f|S)%z,Extract player game stats from event resultseventIDrvstatusstartsAtZ+00:00rrrrrnamesshortUNKrr)rrnamepositionrreboundsassistsstealsblocks turnoversminutesfieldGoalsMadefieldGoalsAttemptedthreePointersMadethreePointersAttemptedfreeThrowsMadefreeThrowsAttempted) rrrrrrrrrrrrrzg:) leagueseasongameKeygameDateplayerExternalId playerNamerteamopponentstatKeyr) r^r fromisoformatrmonthyearitemsrdictappendr)rstatsevent_idrr start_time game_daterr players_datar home_team away_teamrteam_key team_results team_namerplayers_in_team player_id player_stats player_info player_namer stat_mappingsstat_keyrs rQextract_player_statsrs Eyy#H YYz "F YYx $FJ'J**:+=+=c8+LM #,??a#7Y^^a=O ii 2&G99Y+L IIgr "E &"%))'26::7EJI &"%))'26::7EJI;;vr*L$/#''"5 !)V!3I (F 29 '**9b9'6'<'<'>'  #I|lD1&**9b9K%//&)CCr}rc|r|sy|j}d}d}|D]} |jd}|jdi}t|jdijdijddxs2|jdijdijd d |} t|jd ijdijddxs2|jd ijdijd d |} |jd i} | jd } | jdrd} n*| jdrd} n| jdrd} nd} t|\}}t |}| rPt j | jdd}|jdk\r |jn|jdz }n|jd||jf|j}|r@|jd||| |d|d|d|d|dd||jf |dz }nC|jd|j||| | |||| |d|d|d|d|ddf|dz }|j|j||fS#t$r }Yd }~d }~wwxYw)!z Upsert games to SportsGame table)rrrrrrrlongrrrrrr completedfinallive cancelled scheduledrrrrzu SELECT id FROM "SportsGame" WHERE "externalGameId" = %s AND league = %s a UPDATE "SportsGame" SET "homeScore" = COALESCE(%s, "homeScore"), "awayScore" = COALESCE(%s, "awayScore"), status = %s, "moneylineHome" = COALESCE(%s, "moneylineHome"), "moneylineAway" = COALESCE(%s, "moneylineAway"), "spreadHome" = COALESCE(%s, "spreadHome"), "spreadAway" = COALESCE(%s, "spreadAway"), total = COALESCE(%s, total), "oddsSource" = %s, "oddsUpdatedAt" = NOW(), "updatedAt" = NOW() WHERE "externalGameId" = %s AND league = %s rrrrrsportsgameodds-backfilla INSERT INTO "SportsGame" ( league, season, "gameDate", "homeTeam", "awayTeam", "externalGameId", "homeScore", "awayScore", status, "moneylineHome", "moneylineAway", "spreadHome", "spreadAway", total, "oddsSource", "oddsUpdatedAt", "createdAt", "updatedAt" ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW(), NOW() ) ON CONFLICT (league, season, "gameDate", "homeTeam", "awayTeam") DO UPDATE SET "homeScore" = COALESCE(EXCLUDED."homeScore", "SportsGame"."homeScore"), "awayScore" = COALESCE(EXCLUDED."awayScore", "SportsGame"."awayScore"), status = EXCLUDED.status, "moneylineHome" = COALESCE(EXCLUDED."moneylineHome", "SportsGame"."moneylineHome"), "moneylineAway" = COALESCE(EXCLUDED."moneylineAway", "SportsGame"."moneylineAway"), "spreadHome" = COALESCE(EXCLUDED."spreadHome", "SportsGame"."spreadHome"), "spreadAway" = COALESCE(EXCLUDED."spreadAway", "SportsGame"."spreadAway"), total = COALESCE(EXCLUDED.total, "SportsGame".total), "oddsSource" = EXCLUDED."oddsSource", "oddsUpdatedAt" = NOW(), "updatedAt" = NOW() N)r|r^r rrrrrrrexecutelowerfetchonercommitclose)rr}rr|insertedupdatedrrrrr status_datarrrrrrrexistingrns rQ upsert_gamesr-sR v [[]FHG[Z yy+HIIgr*E)%))FB*?*C*CGR*P*T*TU[]_*`+_didmdmntvxdyd}d}FHJeKeOeOPWY^e_aghI)%))FB*?*C*CGR*P*T*TU[]_*`+_didmdmntvxdyd}d}FHJeKeOeOPWY^e_aghI))Hb1K$4J{+ (-$$%3E%: "J &D$22:3E3Ec83TU +4??a+?Y^^VWEW NNFLLN+ - (H  F)4+@&\(:DM-x (1  ,LLNFIy)j*f)4+@&\(:DM- +8A q[z KKM LLN W     sGK2B7K K$K$rc|r|sy|j}d}|D]} |jd|d|d|d|d|d|jd|jd |jd |jd |d |d f |jdkDr|dz }|j |j |S#t$r }Yd}~d}~wwxYw)zUpsert player game metricsra INSERT INTO "PlayerGameMetric" ( league, season, "gameKey", "gameDate", "playerExternalId", "playerName", position, team, opponent, "statKey", value, "createdAt" ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW()) ON CONFLICT (league, season, "gameKey", "playerExternalId", "statKey") DO UPDATE SET value = EXCLUDED.value rrrrrrrrrrrrN)r|r$r^rowcountrr'r()rrr|r)statrns rQupsert_player_statsr1s u [[]FH  NNXXYjAQ'($((<*@$((:BV $((:"6Yg  "A #* KKM LLN O    sBC CCrc |r|sy|j}d}|D]} |jd|j|jd|jd|jd|jdt j |f|j dkDr|dz }|j|j|S#t$r }Yd}~d}~wwxYw) zUpsert player recordsra INSERT INTO "Player" ( league, "externalPlayerId", name, position, team, raw, "createdAt", "updatedAt" ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW()) ON CONFLICT (league, "externalPlayerId") DO UPDATE SET name = EXCLUDED.name, position = COALESCE(EXCLUDED.position, "Player".position), team = COALESCE(EXCLUDED.team, "Player".team), "updatedAt" = NOW() playerIDrrteamIDrN) r|r$upperr^redumpsr/rr'r()rrrr|r)playerrns rQupsert_playersr8s w [[]FH  NN   :& 6" :& 8$ 6"  ""A )0 KKM LLN O    sB C  CCc |r|sy|j}d}|D]} tj|t}t j |j j}|jddd|j||f|jdkDr|dz }|j|j|S#t$rYwxYw)z%Save raw events to ExternalFeedRecordr)defaulta INSERT INTO "ExternalFeedRecord" ( source, kind, league, "contentHash", raw, "fetchedAt", "createdAt" ) VALUES (%s, %s, %s, %s, %s, NOW(), NOW()) ON CONFLICT (source, "contentHash") DO NOTHING r# historicalr)r|rer6rhhashlibmd5encode hexdigestr$r5r/rr'r()rr}rr|r)rraw_json content_hashs rQsave_external_feedrB9s v [[]FH zz%5H";;x'89CCEL NN ,\6<<>6{  0 0JHJH|t d?|t d@|| t;|z } |rt=|||\}}t dA|dB|dC| |z } | |z } t?|||}t dD|g}|D]@}|jdEijdFs%tA|}|jC|B|rtE||}t dG|| |z } |jFr@t dH|jI|}|r"|r tK|||}t dI|| |z } |s7t dJ|ddKD]}|jdLi}|jdMijdNijdOd}|jdPijdNijdOd} |jdEijdQdddR}!tM|\}"}#|jdEijdFrdSndT}$|"r|#dU|"ndV}%t dW|!dX| dY|dZ|%d[|$d\ Bt d]t d^t dt d_| t d`| t da| t db| t dc| t dd|jN|jd}&|&jdrf|&djdi}t de|jd"ijd&d'df|jd"ijd#d$|r|jQyy)gNrD) descriptionrr rzLeague to backfill)nargsr:helpz--startzStart date (YYYY-MM-DD))typerUz--endzEnd date (YYYY-MM-DD)z--yearzYear to backfill (e.g., 2025)z--seasonz"Season to backfill (e.g., 2024-25)z --all-leagues store_truezBackfill all major leagues)actionrUz--include-playersz Also fetch and store player dataz --dry-runzDon't save to databasezF======================================================================z"SPORTSGAMEODDS HISTORICAL BACKFILLz account/usager~ rateLimitsz API Tier: tierunknownzDaily requests: zper-dayzcurrent-requestsrz / z max-requests unlimitedzDaily entities: zcurrent-entitiesz max-entitiesz-01-01rFz%Y-%m-%dZ)daysz Connected to PostgreSQL databasez&WARNING: Could not connect to databasez Unknown league: r z<============================================================z BACKFILLING: z Date range: z to z No events found for c3dK|](}|jdijds%d*yw)rrrNr.0rns rQ zmain..s(RaQUU8R-@-D-D[-QRs&00c3DK|]}|jdsdyw)rrNrras rQrczmain..s;aQUU6];s  c3>K|]}t|ddyw)rNr)rras rQrczmain..sP~a/@/C/O!Psz Total events: z Completed games: z With odds: z With scores: z Database: z inserted, z updatedz External feed records: rrz Player stats: z Fetching players...z Players: z Sample events:r#rrrmediumrrrFinal ScheduledrDTBDz rz @ z (z) []zG ======================================================================zBACKFILL SUMMARYzTotal events fetched: zDatabase inserts: zDatabase updates: zPlayer stats saved: zPlayers saved: zAPI requests made: z Remaining daily requests: z - ))argparseArgumentParser add_argumentrhr parse_argsrarC SGO_API_KEYror^ all_leagues ALL_LEAGUESrr%startendrrrnowstrftimerdry_runrrJrQrsumrr-rBrrr1include_playersrr8rrMr()'parserargs backfillerusage rate_limitsleaguesrqrrr total_gamestotal_inserted total_updatedtotal_player_stats total_playersrrKrpr}r with_odds with_scoresr)r* feed_count all_statsrrstats_insertedrplayers_insertedrrrrrrrr score_str final_usages' rQmainr~s  $ $1c dF UAUV  2KL c0GH s1PQ  3WX  C_` +LGij  L?WX    D (O ./ (O)+6J    0E yyFm'' b9  5=,,VY?@AB  B!?!C!CDVXY!Z [[^_j_n_noxz|_}`B`BCQS^`__`a b  B!?!C!CDVXY!Z [[^_j_n_noxz|_}`B`BCQS^`__`a b ;;$$&' zzdhhZZ 88  {&) ii['  <<>**:6llnyb'99CCJO <<4%6%8D  01 \\ 67KNMMOPkk&)  &vh/ 0 %  ;;#3FDKK#H J 6(m i[)* ZLXJ78 11)ZR *9+6 7 R6RR ;6;; PVPP   V ./ #I;/0 i[)*  }-.s6{"   ,T66 B Hg L +gYhG H h &N W $M,D&&AJ -j\: ;I ,99Xr*..{;07E$$U+ , !4T9!E((89:"n4"    ) + ..y9G4#1$#H  $4#567!11   & ' P '2.yy,00"=AA(CPyy,00"=AA(CP (B/33JDSbI)7)>& J$)IIh$;$?$? $LR]rs" 2233277??277??8#<=>( > ' '& 0 ?? hF1 FDzz|DOOC$8SD[::<--c15S %%c399S>+?+?+DE FFzz~~79\] jjnn56  4b9  @A CHHQK  %1 =  %! <  %" =    %1 =    GRq A   GQa @  61B ?  %" =  %" =  %! <   XqB! " XqB# $qJ% & XqB' ( )1A N) * %" =+ 2w wwt x}  x    F"$"5# )E#F""JJdJZ@htDzh3h5c?hVT$ZCD"$t*"c"c"JT$Z<$ S$ #$ %S/$ N`F zF{FFs K##K#5K#:AK##K-