
    \j)                        S r SSKrSSKrSSKJr  SSKrSSKrSSK	r	SSK
r
\" \5      R                  5       R                  R                  r\S-  S-  S-  rSrSS	S
SSSS.rSr0 SS_SS_SS_SS_SS_SS_SS_SS_S S!_S"S#_S$S%_S&S'_S(S)_S*S+_S,S-_S.S/_S0S1_0 S2S3_S4S5_S6S7_S8S9_S:S;_S<S=_S>S?_S@SA_SBSC_SDSE_SFSG_SHSI_SJSK_SLSM_SNSO_SPSQ_SRSS_E0 STSU_SVSW_SXSY_SZS[_S\S]_S^S__S`Sa_SbSc_SdSe_SfSg_ShSi_SjSk_SlSm_SnSo_SpSq_SrSs_ErSzSt\S-  Su\	R*                  4Sv jjrSw rSu\4Sx jr\Sy:X  a  \R6                  " \" 5       5        gg){up  
Importa il dataset (data/processed/matches.csv) in un database MySQL "tigertips"
con tabelle normalizzate, pronte per essere usate dal futuro sito web.

Schema:
    leagues  - le leghe (codice football-data, nome, paese)
    teams    - le squadre (nome unico)
    matches  - le partite: risultato, statistiche, quote, probabilità implicite

Lo script è idempotente: si può rilanciare dopo ogni nuovo download e aggiorna
le partite esistenti (chiave unica: data + squadra casa + squadra ospite).

Uso:
    python scripts/import_db.py
    python scripts/import_db.py --host 127.0.0.1 --port 3306 --user root --password ""
    N)Pathdata	processedzmatches.csv	tigertips)zSerie AItalia)zSerie Br   )zPremier LeagueInghilterra)zLa LigaSpagna)
BundesligaGermania)zLigue 1Francia)I1I2E0SP1D1F1uw  
CREATE TABLE IF NOT EXISTS leagues (
    id          TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code        VARCHAR(5)  NOT NULL UNIQUE,
    name        VARCHAR(50) NOT NULL,
    country     VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS teams (
    id          SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS matches (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    league_id           TINYINT UNSIGNED  NOT NULL,
    season              VARCHAR(7)        NOT NULL,          -- es. '2023/24'
    match_date          DATE              NOT NULL,
    match_time          TIME              NULL,
    home_team_id        SMALLINT UNSIGNED NOT NULL,
    away_team_id        SMALLINT UNSIGNED NOT NULL,

    -- Risultato (ht = primo tempo, result: H/D/A)
    home_goals          TINYINT UNSIGNED  NULL,
    away_goals          TINYINT UNSIGNED  NULL,
    result              CHAR(1)           NULL,
    ht_home_goals       TINYINT UNSIGNED  NULL,
    ht_away_goals       TINYINT UNSIGNED  NULL,
    ht_result           CHAR(1)           NULL,

    -- Statistiche di gioco
    home_shots          TINYINT UNSIGNED  NULL,
    away_shots          TINYINT UNSIGNED  NULL,
    home_shots_target   TINYINT UNSIGNED  NULL,
    away_shots_target   TINYINT UNSIGNED  NULL,
    home_corners        TINYINT UNSIGNED  NULL,
    away_corners        TINYINT UNSIGNED  NULL,
    home_fouls          TINYINT UNSIGNED  NULL,
    away_fouls          TINYINT UNSIGNED  NULL,
    home_yellow         TINYINT UNSIGNED  NULL,
    away_yellow         TINYINT UNSIGNED  NULL,
    home_red            TINYINT UNSIGNED  NULL,
    away_red            TINYINT UNSIGNED  NULL,

    -- Quote 1X2 pre-match
    b365_home           DECIMAL(7,3) NULL,
    b365_draw           DECIMAL(7,3) NULL,
    b365_away           DECIMAL(7,3) NULL,
    pinnacle_home       DECIMAL(7,3) NULL,
    pinnacle_draw       DECIMAL(7,3) NULL,
    pinnacle_away       DECIMAL(7,3) NULL,
    avg_home            DECIMAL(7,3) NULL,
    avg_draw            DECIMAL(7,3) NULL,
    avg_away            DECIMAL(7,3) NULL,
    max_home            DECIMAL(7,3) NULL,
    max_draw            DECIMAL(7,3) NULL,
    max_away            DECIMAL(7,3) NULL,

    -- Quote 1X2 di chiusura (closing)
    b365c_home          DECIMAL(7,3) NULL,
    b365c_draw          DECIMAL(7,3) NULL,
    b365c_away          DECIMAL(7,3) NULL,
    pinnaclec_home      DECIMAL(7,3) NULL,
    pinnaclec_draw      DECIMAL(7,3) NULL,
    pinnaclec_away      DECIMAL(7,3) NULL,
    avgc_home           DECIMAL(7,3) NULL,
    avgc_draw           DECIMAL(7,3) NULL,
    avgc_away           DECIMAL(7,3) NULL,
    maxc_home           DECIMAL(7,3) NULL,
    maxc_draw           DECIMAL(7,3) NULL,
    maxc_away           DECIMAL(7,3) NULL,

    -- Over/Under 2.5
    b365_over25         DECIMAL(7,3) NULL,
    b365_under25        DECIMAL(7,3) NULL,
    avg_over25          DECIMAL(7,3) NULL,
    avg_under25         DECIMAL(7,3) NULL,

    -- Probabilità implicite normalizzate (senza margine del bookmaker)
    imp_prob_home       DECIMAL(6,4) NULL,
    imp_prob_draw       DECIMAL(6,4) NULL,
    imp_prob_away       DECIMAL(6,4) NULL,
    imp_prob_source     VARCHAR(20)  NULL,

    UNIQUE KEY uq_match (match_date, home_team_id, away_team_id),
    KEY idx_league_season (league_id, season),
    KEY idx_date (match_date),
    KEY idx_home_team (home_team_id),
    KEY idx_away_team (away_team_id),
    CONSTRAINT fk_matches_league FOREIGN KEY (league_id)    REFERENCES leagues (id),
    CONSTRAINT fk_matches_home   FOREIGN KEY (home_team_id) REFERENCES teams (id),
    CONSTRAINT fk_matches_away   FOREIGN KEY (away_team_id) REFERENCES teams (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
FTHG
home_goalsFTAG
away_goalsFTRresultHTHGht_home_goalsHTAGht_away_goalsHTR	ht_resultHS
home_shotsAS
away_shotsHSThome_shots_targetASTaway_shots_targetHChome_cornersACaway_cornersHF
home_foulsAF
away_foulsHYhome_yellowAYaway_yellowHRhome_redARaway_redB365H	b365_homeB365D	b365_drawB365A	b365_awayPSHpinnacle_homePSDpinnacle_drawPSApinnacle_awayAvgHavg_homeAvgDavg_drawAvgAavg_awayMaxHmax_homeMaxDmax_drawMaxAmax_awayB365CH
b365c_homeB365CD
b365c_drawB365CA
b365c_awayPSCHpinnaclec_homePSCDpinnaclec_drawPSCApinnaclec_awayAvgCH	avgc_homeAvgCD	avgc_drawAvgCA	avgc_awayMaxCH	maxc_homeMaxCD	maxc_drawMaxCA	maxc_awayzB365>2.5b365_over25zB365<2.5b365_under25zAvg>2.5
avg_over25zAvg<2.5avg_under25ImpProbHimp_prob_homeImpProbDimp_prob_drawImpProbAimp_prob_awayImpProbSourceimp_prob_sourcedatabasereturnc           
          [         R                  " U R                  U R                  U R                  U R
                  USSS9$ )Nutf8mb4F)hostportuserpasswordrs   charset
autocommit)pymysqlconnectrw   rx   ry   rz   )argsrs   s     ,C:\wamp64\www\Tigertips\scripts\import_db.pyr~   r~      s3    ??YYTYYTYY9     c                 P   U bK  [        U [        5      (       a  [        R                  " U 5      (       d  [        R
                  " U 5      (       a  g[        U [        R                  45      (       a  [        U 5      $ [        U [        R                  45      (       a  [        U 5      $ U $ )zBConverte NaN/tipi numpy in tipi Python accettati dal driver MySQL.N)	
isinstancefloatnpisnanpdisnaintegerintfloating)values    r   to_pyr      sn    }E511bhhuoo"''RW..%"**''5z%"++((U|Lr   c                  
   [         R                  " SS9n [        R                  " U 5        U R	                  5       n[
        R                  5       (       d  [        S[
         S35        g[        R                  " [
        SS9n[        R                  " US   5      R                  R                  US'   [        S	[        U5       S
35        [        U5      nUR                  5        nUR!                  S["         S35        S S S 5        UR%                  5         UR'                  5         [        U["        5      nUR                  5       n[(        R+                  S5       H+  nUR-                  5       (       d  M  UR!                  U5        M-     UR%                  5         UR/                  S[0        R3                  5        VVVs/ s H  u  nu  pxXgU4PM     snnn5        UR%                  5         UR!                  S5        [5        UR7                  5       5      n	[9        [;        US   5      [;        US   5      -  5      n
UR/                  SU
 Vs/ s H  o4PM     sn5        UR%                  5         UR!                  S5        [5        UR7                  5       5      n[        S[        U5       35        / SQ[=        [>        RA                  5       5      -   nSRC                  S/[        U5      -  5      nSRC                  S U 5       5      nSSRC                  U5       SU SU 3n/ nURE                  5        H  u  nn[G        URI                  S5      5      nURK                  [M        U	US      US   US   UUUS      UUS      /[>         Vs/ s H  n[G        URI                  U5      5      PM     sn-   5      5        M     Sn[O        S [        U5      U5       HZ  nUR/                  UUUUU-    5        UR%                  5         [        S![Q        UU-   [        U5      5       S"[        U5       3S#S$9  M\     [        5         UR!                  S%5        URS                  5       S    nUR!                  S&5        [        S'["         S(35        [        S)U 35        UR7                  5        H!  u  nnnn[        S*US+ S,US- S.U S/U 35        M#     UR'                  5         UR'                  5         g ! , (       d  f       GN= fs  snnnf s  snf s  snf )0Nz0Importa matches.csv nel database MySQL tigertips)descriptionzDataset non trovato: z.
Esegui prima: python scripts/download_data.py   F)
low_memoryDatez	Dataset: z partite da importarezCREATE DATABASE IF NOT EXISTS z1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;zINSERT INTO leagues (code, name, country) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE name = VALUES(name), country = VALUES(country)zSELECT code, id FROM leaguesHomeTeamAwayTeamz+INSERT IGNORE INTO teams (name) VALUES (%s)zSELECT name, id FROM teamsz	Squadre: )	league_idseason
match_date
match_timehome_team_idaway_team_idz, z%sc              3   B   #    U  H  nUS ;  d  M  U SU S3v   M     g7f))r   r   r   z
 = VALUES()N ).0cs     r   	<genexpr>main.<locals>.<genexpr>   s.      Tg$RR -1#Zs!,gs   
zINSERT INTO matches (z
) VALUES (z) ON DUPLICATE KEY UPDATE TimeDivSeasoni  r   z  importate /)endzSELECT COUNT(*) FROM matchesz
        SELECT l.name, COUNT(*), MIN(m.match_date), MAX(m.match_date)
        FROM matches m JOIN leagues l ON l.id = m.league_id
        GROUP BY l.name ORDER BY COUNT(*) DESC
    z
=== Database 'z' ===zPartite in tabella: z  z<16 z>6z   z -> )*argparseArgumentParserconfigadd_db_args
parse_argsCSV_PATHexistsprintr   read_csvto_datetimedtdatelenr~   cursorexecuteDB_NAMEcommitcloseSCHEMAsplitstripexecutemanyLEAGUESitemsdictfetchallsortedsetlist	CSV_TO_DBvaluesjoiniterrowsr   getappendtuplerangeminfetchone)parserr   dfconncur	statementcodenamecountry
league_ids
team_namesnteam_idsdb_colsplaceholdersupdatessqlrows_rtime_valcsv_col
batch_sizestarttotalcountdmindmaxs                               r   mainr      sa   $$1cdF
vD??%hZ/^_`	X%	0B6
+..33BvJ	Ic"gY3
45 4=D	#4WI >G G 	H 
 	KKMJJL4!D
++-C\\#&	??KK	" ' 	KKM OO	Q<CMMOLO#84$$g	OL
 	KKMKK./clln%J BzN+c"Z..AABJOOA#-.:aT:.0KKMKK,-CLLN#H	Ic(m_
%&/15i6F6F6H1IJG99dVc'l23Lii Tg T TG"499W#5"6j O&&-Y0C D1v'E%!1X;&	8a
m$hq}&=?4=>IuQUU7^$I>?
 	  Jq#d)Z0T%
(:;<S!3SY?@#d)MSWX 1 
G KK./LLN1EKK  	
 
WIU
+,	 
()#&<<>eT44*AeBZs4&TF;< $2 IIKJJLQ 
" 	M /* ?s   $S*>S<
T
	$T*
S9__main__)N)__doc__r   syspathlibr   numpyr   pandasr   r}   r   __file__resolveparentPROJECT_ROOTr   r   r   r   r   str
Connectionr~   r   r   r   __name__exitr   r   r   <module>r      s$  "  
     H~%%'..55& ;.>
  

+ 
$
 ]
@
L ,05x
O#_6;[ 	, l 
	 !&':	
 	.
  	, l 	- } 	* J [ "; 18 
? "? 5:? J 
 -3J J 
 -3J l %l 5=l  %&6 9?@P [ "; 18  [!  ";!  18!" #"  *>#$ |%$ '%& '& ",_'& ?I/'( &)	0C$J '2D2D Wc Wt zHHTV r   