
    \j*                        S r SSKrSSKrSSKrSSKrSSKrSSKJrJr  SSK	J
r
  SSKrSSKrSSKrSSKrSSKJr  \
" \5      R'                  5       R(                  R(                  r\S-  S-  S-  r\S-  S	-  S
-  r\S-  S	-  S-  rSSSSSSS.r\" \" SS5      5      rSSSS.r\R<                  " S5      r\R<                  " S5      r \R<                  " S5      r! S+S\"S\#S\#S\RH                  S \%S!\"S-  4S" jjr&S#\"S\"S!\\'   4S$ jr(S+S%\\"   S \%S!\RR                  4S& jjr*S'\RR                  S!S4S( jr+S!\#4S) jr,\-S*:X  a  \R\                  " \," 5       5        gg),u/  
Scarica gli arbitri di TUTTE le leghe dalle pagine di giornata di Transfermarkt
e li importa in matches.referee (nomi completi, es. "Daniele Orsato").

Fonte: https://www.transfermarkt.com/<slug>/spieltag/wettbewerb/<code>/saison_id/<Y>/spieltag/<N>
Una pagina per giornata (~2.000 pagine in tutto), con cache locale: lo script è
interrompibile e ripartibile senza rifare i download.

La mappatura nomi squadra Transfermarkt -> football-data viene imparata
automaticamente accoppiando le partite per (lega, data, risultato) nei casi
non ambigui (stessa tecnica di import_xg.py).

Uso:
    python scripts/download_referees.py                   # tutto
    python scripts/download_referees.py --leagues I1 I2   # solo Italia
    python scripts/download_referees.py --no-download     # solo parse+import dalla cache
    N)Counterdefaultdict)Path)BeautifulSoupdatarawtransfermarkt	processedzreferees_tm.csvzteam_mapping_tm.csv)zserie-aIT1&   )zserie-bIT2r   )zpremier-leagueGB1r   )laligaES1r   )
bundesligaL1"   )zligue-1FR1r   )I1I2E0SP1D1F1i  i  zoMozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36z#it-IT,it;q=0.9,en-US;q=0.8,en;q=0.7zhttps://www.google.com/)z
User-AgentzAccept-LanguageRefererz^\(\d+\.\)\s*(.+)$z^(.+?)\s*\(\d+\.\)$z^(\d+):(\d+)$leagueseasonround_nsessionforcereturnc                 ,   [         R                  SSS9  [         U  SU SUS S3-  nUR                  5       (       a  U(       d  UR                  SSS9$ [        U    u  pgnS	U S
U SU SU 3n	[        S5       H  n
 UR                  U	[        SS9nUR                  S;   a/  SU
S-   -  n[        SU S35        [        R                  " U5        MX  UR                  5         UR                  UR                  SS9  [        R                  " S[        R                   " SS5      -   5        UR                  s  $    g ! ["        R$                   aE  n[        SU
S-    SU  SU SU SU 3
5        [        R                  " SU
S-   -  5         S nAGM  S nAff = f)NTparentsexist_ok_02dz.htmlutf-8replaceencodingerrorszhttps://www.transfermarkt.com/z/spieltag/wettbewerb/z/saison_id/z
/spieltag/      )headerstimeout)i  i     z    [rate limit] attendo zs...)r+   g      ?r   g333333?z    [retry z/3]  z g:    )RAW_DIRmkdirexists	read_textLEAGUESrangegetHEADERSstatus_codeprinttimesleepraise_for_status
write_texttextrandomuniformrequestsRequestException)r   r   r   r   r    cacheslugtm_coder&   urlattemptrespwaitexcs                 4C:\wamp64\www\Tigertips\scripts\download_referees.py
fetch_pagerQ   >   s   MM$M.&73-u==E||~~e	BBvD1+D61Fwi
7)5C8	*;;sGR;@D:-Wq[)1$t<=

4 !!#TYY9JJsV^^As33499   (( 	*K!}D&G9BseTUJJqGaK())	*s    =AD:A#D::F9FFhtmlc                    [        U S5      n/ nUR                  S5       GH  nUR                  S5      nUc  M  UR                  S5      n[        S UR                  S5       5       S 5      nS =pUR                  S5       H~  n
U
R	                  SS	S
9nUc/  [
        R                  U5      nU(       a  UR                  S5      nME  U	b  MJ  Uc  MO  [        R                  U5      nU(       d  Mm  UR                  S5      n	  O   U(       a  U(       a  U(       a  U	(       d  GM  [        R                  UR	                  S	S
95      n [        R                  " UR	                  S	S
9S	S9nUR                  XX[        UR                  S5      5      [        UR                  S5      5      UR	                  S	S
9S.5        GM     U$ ! [         a     GM  f = f)Nlxmlzdiv.boxz"a[href*="/profil/schiedsrichter/"]za[href*="datum"]c              3   x   #    U  H0  n[         R                  UR                  S S95      (       d  M,  Uv   M2     g7f)TstripN)RE_SCOREmatchget_text).0as     rP   	<genexpr>parse_page.<locals>.<genexpr>b   s1      F&M&nnQZZdZ-CD 1&Ms   +:	:za[href*="/spielbericht/"]ztd.hauptlinkr2   TrV   r1   )dayfirst   )r   datehome_tmaway_tmhgagreferee)r   select
select_onenextrZ   RE_HOMErY   groupRE_AWAYrX   pdto_datetime
ValueErrorappendint)rR   r   souprowsboxref_link	date_link
score_linkhomeawaytdrC   mra   s                 rP   
parse_pager|   Z   s   v&DD{{9%>>"FGNN#56	 Fcjj1L&M FGKM
**^,B;;s$;/D|MM$'771:D| 0MM$'1771:D - jTdNN:..T.:;	>>)"4"44"4"@4PD 	v $qwwqz?#aggaj/ ( 1 1 1 =? 	@7 &> K  		s   "G
GGleaguesc                    [         R                  " 5       n/ nU  H  n[        U   u    pV[         H  n[	        U5      n[        SUS-   5       H1  n	[        XGXUS9n
U
(       d  M  UR                  [        X5      5        M3     [        SU SU S[        US-   5      SS   S[	        U5      U-
   S3	5        M     M     [        R                  " U5      nUR                  / S	QS
9nU$ )Nr1   r      r2   /r`   r3   z partiter   ra   rb   rc   subset)rF   Sessionr9   SEASONSlenr:   rQ   extendr|   r>   strrm   	DataFramedrop_duplicates)r}   r    r   all_rowsr   r&   
max_roundsr   n_beforer   rR   dfs               rP   download_allr      s     GH"6?1F8}H JN3!&'%P4OOJt$<= 4 BvhaxqVaZ)<(=R]X-.h8 9   
h	B			#K		LBI    r   c           
      F
   [         R                  " UR                  UR                  UR                  UR
                  SS9nUR                  5       nUR                  S5        [        R                  " UR                  5       / SQS9n[        R                  " US   5      US'   / SQnU R                  U5      R                  S 5      nUR                  U5      R                  S	 5      nUR                  XuS
9n[        [         5      n	UR#                  SS9 HI  n
XR$                     U
R&                  ==   S-  ss'   XR(                     U
R*                  ==   S-  ss'   MK     0 nU	R-                  5        HY  u  pUR/                  S5      S   u  pU[1        UR3                  5       5      -  S:  a  [5        SU S[7        U5       35        XU'   M[     [        R                  " [9        UR-                  5       5      SS/S9R;                  [<        SS9  [5        S[?        U5       S[<        R@                   S35        [C        U S   5      [C        U S   5      -  [C        U5      -
  nU(       a  [5        S[9        U5       35        U RE                  5       n U S   RG                  U5      U S'   U S   RG                  U5      U S'   U RI                  SS/S9n / nU RK                  SS9RK                  SS 9nURM                  SS!0S9nS" H  nURN                  (       a    OpURE                  5       nUS   [        RP                  " US#9-   US!'   UR                  U/ S$QS%S&9nURS                  U5        UUS   RU                  US   5      )    nM     [        RV                  " USS'9nURY                  S(/S)S*9nUUS+   US,   :g  US-   US.   :g  -     n[?        U5      (       a1  [5        S/[?        U5       S035        UR[                  UR\                  S9nUR#                  SS9 V
s/ s H#  oR^                  [a        U
Rb                  5      4PM%     nn
[e        S[?        U5      S15       H+  nURg                  S2UUUS1-    5        URi                  5         M-     [5        S3[?        U 5       S4[?        U5       35        UR                  S55        [5        S65        UR                  5        H/  u  nnn[5        S7US8 S9[a        U=(       d    S5      S: S;U 35        M1     URk                  5         g s  sn
f )<N	tigertips)hostportuserpassworddatabasea  
        SELECT m.id, l.code, m.match_date, h.name, a.name, m.home_goals, m.away_goals
        FROM matches m
        JOIN leagues l ON l.id = m.league_id
        JOIN teams h ON h.id = m.home_team_id
        JOIN teams a ON a.id = m.away_team_id
        WHERE m.result IS NOT NULL
    )idr   ra   home_dbaway_dbrd   re   )columnsra   )r   ra   rd   re   c                     [        U 5      S:H  $ Nr1   r   gs    rP   <lambda>import_to_db.<locals>.<lambda>       Q1r   c                     [        U 5      S:H  $ r   r   r   s    rP   r   r      r   r   )onFindexr1   r   g?z"  [ATTENZIONE] mappatura ambigua 'z': r	   football_datazMappatura nomi: z squadre (in )rb   rc   z(  [ATTENZIONE] squadre senza mappatura: r   r   r   T)droptm_idx)names	join_date)r   r1   )days)r   r   r   r   )_tm_db)r   suffixes)ignore_indexr   first)r   keephg_tmhg_dbag_tmag_dbz  [ATTENZIONE] z. accoppiamenti con risultato diverso, scartatii  z-UPDATE matches SET referee = %s WHERE id = %sz
Partite Transfermarkt: z | agganciate al DB: z
        SELECT l.code, COUNT(*), SUM(m.referee IS NOT NULL)
        FROM matches m JOIN leagues l ON l.id = m.league_id
        WHERE m.result IS NOT NULL
        GROUP BY l.code ORDER BY l.code
    zCopertura arbitri nel DB:r   z<5r2   z>6z / )6pymysqlconnectr   r   r   r   cursorexecuterm   r   fetchallrn   groupbyfiltermerger   r   
itertuplesrb   r   rc   r   itemsmost_commonsumvaluesr>   dictsortedto_csvMAP_CSVr   namesetcopymapdropnareset_indexrenameempty	Timedeltarp   isinconcatr   r   r   rf   rq   r   r:   executemanycommitclose)r   argsconncurdbkey	tm_unique	db_uniquepairedvotesrname_maptm_namecounterbestn_bestunmappedmatched_parts	remaining
db_renamedshiftrL   mergedmatchedbadrs   startcodetotwith_refs                                 rP   import_to_dbr      s   ??						$(MMKID
++-CKK  	 
clln /B 
CB6
+BvJ )C

3&&'<=I

3&&'<=I__Y_/F +G 4EU+ii#q(#ii#q(# , H!KKM**1-a0C())C/6wis4=/RS 	 *
 LL())?;==CVGSXV=Y	S]O=a
HIByM"SI%773x=HH89I8JKL 
ByM%%h/ByMyM%%h/ByM	9i0	1BMD)55H5EIFK#89J??.."&v51IIz"O(6  8 	V$y277x8HIIJ	  iiD9G%%dV'%BG
77#ww'777#ww'779 :C
3xxCz)WXY,,SYY,/,3,>,>U,>,KL,KqYYADD	",KDLq#d)T*GU54<0	2 +
 
%c"gY.CCL>
RSKK  	 

%&"||~c84)1SQ/33se<=  .JJL! Ms   *Tc            
         [         R                  " SS9n U R                  SS[        [        5      [        [        5      S9  U R                  SSSS	9  U R                  S
SS9  [
        R                  " U 5        U R                  5       nUR                  (       a  / n[        [        R                  S5      5       H[  nUR                  R                  S5      S   nXAR                  ;   d  M2  UR                  [!        UR#                  SSS9U5      5        M]     [$        R&                  " U5      R)                  / SQS9nO[+        UR                  UR,                  S9nUR.                  (       a  [1        S5        g[2        R4                  R7                  SSS9  UR9                  [2        SS9  [1        S[;        U5       SUS   R=                  5        S[2        R                   35        [?        XQ5        g)Nz*Arbitri di tutte le leghe da Transfermarkt)descriptionz	--leagues+)nargsdefaultchoicesz--no-download
store_truez)Usa solo la cache locale (parse + import))actionhelpz--force)r   z*.htmlr&   r   r(   r)   r*   r   r   r   zNessun dato.r1   Tr#   Fr   z
Totale partite con arbitro: z (rf   z arbitri) -> ) argparseArgumentParseradd_argumentlistr9   configadd_db_args
parse_argsno_downloadr   r5   globr   splitr}   r   r|   r8   rm   r   r   r   r    r   r>   OUT_CSVparentr6   r   r   nuniquer   )parserr   rs   pathr   r   s         rP   mainr     s   $$1]^F
3W $W  /
H  J
	,7
vD7<<12DYY__S)!,F%JNNGINFP Q 3
 \\$//; 0 = $,,djj9	xxnNN5IIgUI#	*3r7) 4m##%&mGLL>C D r   __main__)F)/__doc__r   rD   resysr?   collectionsr   r   pathlibr   pandasrm   r   r  rF   bs4r   __file__resolver
  PROJECT_ROOTr5   r	  r   r9   r  r:   r   r<   compilerj   rl   rX   r   rq   r   boolrQ   r   r|   r   r   r   r  __name__exit r   rP   <module>r     s  $   	 
  ,      H~%%'..55

%
'/
9

+
-0A
A

+
-0E
E !
 
' 
"
  uT4 
!G<(	 ***
+
**+
,::&' #s C # @P@P '*Tz8"S "# "$t* "J$s) D R\\ $TR\\ TD Tnc D zHHTV r   