How to use test_column method in pandera

Best Python code snippet using pandera_python

Name_to_ID.py

Source:Name_to_ID.py

1import mysql.connector2import pandas as pd34MLBdb = mysql.connector.connect(5    host = '127.0.0.1',6    port = 3306,7    user = 'root',8    password = 'root',9    database = 'last_test'10    )11#12# MLBdb = mysql.connector.connect(13#     host = '1.tcp.jp.ngrok.io',14#     port = 23879,15#     user = 'yulin',16#     password = 'clubgogo',17#     database = 'MoneyBallDatabase'18# )musqldump1920cursor = MLBdb.cursor()2122#è£½ä½columnsçlist23column1 = 'describe mlb_game '24cursor.execute(column1)25column = pd.DataFrame(cursor.fetchall())26xx = column.loc[:,0]27columns_list = []28for i in xx :29    columns_list += [i]30print(len(columns_list))31323334'''35#â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â                       ããã36#   WAY1 é£æ¥MySQL ç¨MySQLç´æ¥æIDã(ç¼ºååä¸ä¸æ¨£ç)ãã37#â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â 38'''39# sql_command = 'SELECT * FROM ID_reference '40# cursor.execute(sql_command)41# show = cursor.fetchall()42# # print(show)43#44# #è£½ä½çå¡:IDçdict45# id_dic = {i[1]:i[0] for i in show }46# print(id_dic)47# # T_id_dic = {v: k for k, v in id_dic.items()}48# # print(T_id_dic) #(ID,NAME)49#50# '''51# ä¸åä¸åcolumnsèç52# '''53# #è£½ä½forè¿´åç¨ç54# # columns_list = ['away_pitcher','home_pitcher','away_1','away_2','away_3','away_4','away_5','away_6','away_7','away_8','away_9','home_1','home_2','home_3','home_4','home_5','home_6','home_7','home_8','home_9']55# columns_list = ['away_pitcher','home_pitcher']56#57# # sql_for_name = f'SELECT * FROM ID_reference '  #58# # cursor.execute(sql_for_name)59# # name_list = cursor.fetchall() #ååº('åå',)60# # print(name_list)61#62# sql_unlock = 'SET SQL_SAFE_UPDATES=0'63# cursor.execute(sql_unlock)64#65#66# lost_player = []67# # for id_name in id_dic :68# #     id = id_dic[id_name]69# for test_column in columns_list :70#     sql_for_name = f'SELECT {test_column} FROM test '  #71#     cursor.execute(sql_for_name)72#     name_list = cursor.fetchall()  # ååº('åå',)73#74#     for i in name_list:75#         name = i[0]76#77#         try:78#             id = id_dic[name] #è©¦ç¶id_dic[name]æIDæé²ä¾åèç79#             print('')80#81#             if name == 'Chris Young':82#                 print(f'èç{name}')83#84#                 # unlock = 'SET SQL_SAFE_UPDATES=0'85#                 # cursor.execute(unlock)86#87#                 sql_command1 = f'''88#                 UPDATE test SET {test_column}=432934 WHERE89#                 {test_column} = '{name}'90#                 AND91#                 (92#                 (YEAR(game_date)=2010)AND((matchup LIKE 'SD%' OR matchup LIKE '%SD')) OR93#                 ((YEAR(game_date)=2011 OR YEAR(game_date)=2012)AND((matchup LIKE 'NYM%')OR matchup LIKE '%NYM')) OR94#                 (YEAR(game_date)=2014 AND (matchup LIKE 'SEA%'OR matchup LIKE '%SEA')) OR95#                 ((YEAR(game_date)=2015 OR YEAR(game_date)=2016 OR YEAR(game_date)=2017) AND (matchup LIKE 'KC%'OR matchup LIKE '%KC'))96#                 )97#                 '''98#                 cursor.execute(sql_command1)99#                 MLBdb.commit()100#101#                 sql_command2 = f'UPDATE test SET {test_column}=455759 WHERE {test_column}="{name}"'102#                 cursor.execute(sql_command2)103#                 MLBdb.commit()104#105#                 # lock = 'SET SQL_SAFE_UPDATES=1'106#                 # cursor.execute(lock)107#108#             elif name == 'Rich Thompson':109#                 print(f'èç{name}')110#111#                 # unlock = 'SET SQL_SAFE_UPDATES=0'112#                 # cursor.execute(unlock)113#114#                 sql_command1 = f'''115#                 UPDATE test SET {test_column}=430829 WHERE116#                 {test_column} = '{name}'117#                 AND118#                 (YEAR(game_date)=2012)AND(matchup LIKE 'TB%' OR matchup LIKE '%TB')119#                 '''120#                 cursor.execute(sql_command1)121#                 MLBdb.commit()122#123#                 sql_command2 = f'UPDATE test SET {test_column}=460366 WHERE {test_column}="{name}"'124#                 cursor.execute(sql_command2)125#                 MLBdb.commit()126#127#                 # lock = 'SET SQL_SAFE_UPDATES=1'128#                 # cursor.execute(lock)129#130#             elif name == 'Angel Sanchez':131#                 print(f'èç{name}')132#133#                 # unlock = 'SET SQL_SAFE_UPDATES=0'134#                 # cursor.execute(unlock)135#136#                 sql_command1 = f'''137#                 UPDATE test SET {test_column}=447816 WHERE138#                 {test_column} = '{name}'139#                 AND140#                 (141#                 (YEAR(game_date)=2010 AND ((matchup LIKE 'BOS%' OR matchup LIKE '%BOS') OR (matchup LIKE 'HOU%' OR matchup LIKE '%HOU')))142#                 OR143#                 (YEAR(game_date)=2011 AND (matchup LIKE 'HOU%' OR matchup LIKE '%HOU'))144#                 OR145#                 (YEAR(game_date)=2013 AND (matchup LIKE 'CHW%' OR matchup LIKE '%CHW'))146#                 )147#                 '''148#                 cursor.execute(sql_command1)149#                 MLBdb.commit()150#151#                 sql_command2 = f'UPDATE test SET {test_column}=605795 WHERE {test_column}="{name}"'152#                 cursor.execute(sql_command2)153#                 MLBdb.commit()154#                 #155#                 # lock = 'SET SQL_SAFE_UPDATES=1'156#                 # cursor.execute(lock)157#158#             elif name == 'Chris Carpenter' :159#                 print(f'èç{name}')160#161#                 # unlock = 'SET SQL_SAFE_UPDATES=0'162#                 # cursor.execute(unlock)163#164#                 sql_command1 = f'''165#                 UPDATE test SET {test_column}=112020 WHERE166#                 {test_column} = '{name}'167#                 AND168#                 ((YEAR(game_date)=2010 OR YEAR(game_date)=2011 OR YEAR(game_date)=2012) AND (matchup LIKE 'STL%'OR matchup LIKE '%STL'))169#170#                 '''171#                 cursor.execute(sql_command1)172#                 MLBdb.commit()173#174#                 sql_command2 = f'UPDATE test SET {test_column}=452764 WHERE {test_column}="{name}"'175#                 cursor.execute(sql_command2)176#                 MLBdb.commit()177#178#                 # lock = 'SET SQL_SAFE_UPDATES=1'179#                 # cursor.execute(lock)180#181#             elif name == 'Luis Castillo':182#                 print(f'èç{name}')183#                 # unlock = 'SET SQL_SAFE_UPDATES=0'184#                 # cursor.execute(unlock)185#186#                 sql_command1 = f'''187#                 UPDATE test SET {test_column}=622419 WHERE188#                 {test_column} = '{name}'189#                 AND190#                 (191#                 (YEAR(game_date)=2017 OR YEAR(game_date)=2018 OR YEAR(game_date)=2019) AND (matchup LIKE 'CIN%'OR matchup LIKE '%CIN')192#                 )193#194#                 '''195#                 cursor.execute(sql_command1)196#                 MLBdb.commit()197#198#                 sql_command2 = f'UPDATE test SET {test_column}=112116 WHERE {test_column}="{name}"'199#                 cursor.execute(sql_command2)200#                 MLBdb.commit()201#202#                 # lock = 'SET SQL_SAFE_UPDATES=1'203#                 # cursor.execute(lock)204#205#             elif name == 'Josh Fields':206#                 print(f'èç{name}')207#                 # unlock = 'SET SQL_SAFE_UPDATES=0'208#                 # cursor.execute(unlock)209#210#                 sql_command1 = f'''211#                 UPDATE test SET {test_column}=451661 WHERE212#                 {test_column} = '{name}'213#                 AND214#                 (215#                 YEAR(game_date)=2010 AND (matchup LIKE 'KC%'OR matchup LIKE '%KC')216#                 )217#218#                 '''219#                 cursor.execute(sql_command1)220#                 MLBdb.commit()221#222#                 sql_command2 = f'UPDATE test SET {test_column}=425222 WHERE {test_column}="{name}"'223#                 cursor.execute(sql_command2)224#                 MLBdb.commit()225#226#                 # lock = 'SET SQL_SAFE_UPDATES=1'227#                 # cursor.execute(lock)228#229#             elif name == 'Chris Smith':230#                 print(f'èç{name}')231#232#                 # unlock = 'SET SQL_SAFE_UPDATES=0'233#                 # cursor.execute(unlock)234#235#                 sql_command1 = f'''236#                 UPDATE test SET {test_column}=434672 WHERE237#                 {test_column} = '{name}'238#                 AND239#                 (240#                 ((YEAR(game_date)=2010)AND(matchup LIKE 'MIL%' OR matchup LIKE '%MIL')) OR241#                 ((YEAR(game_date)=2016 OR YEAR(game_date)=2017) AND (matchup LIKE 'OAK%' OR matchup LIKE '%OAK'))242#                 )243#                 '''244#                 cursor.execute(sql_command1)245#                 MLBdb.commit()246#247#                 sql_command2 = f'UPDATE test SET {test_column}=627262 WHERE {test_column}="{name}"'248#                 cursor.execute(sql_command2)249#                 MLBdb.commit()250#251#                 # lock = 'SET SQL_SAFE_UPDATES=1'252#                 # cursor.execute(lock)253#254#             elif name == 'Kyle Waldrop':255#                 print(f'èç{name}')256#257#                 # unlock = 'SET SQL_SAFE_UPDATES=0'258#                 # cursor.execute(unlock)259#260#                 sql_command1 = f'''261#                 UPDATE test SET {test_column}=592835 WHERE262#                 {test_column} = '{name}'263#                 AND264#                 ((YEAR(game_date)=2015) OR (YEAR(game_date)=2016)) AND (matchup LIKE 'CIN%' OR matchup LIKE '%CIN')265#                 '''266#                 cursor.execute(sql_command1)267#                 MLBdb.commit()268#269#                 sql_command2 = f'UPDATE test SET {test_column}=448252 WHERE {test_column}="{name}"'270#                 cursor.execute(sql_command2)271#                 MLBdb.commit()272#273#                 # lock = 'SET SQL_SAFE_UPDATES=1'274#                 # cursor.execute(lock)275#276#             elif name == 'Chris Carter':277#                 print(f'èç{name}')278#279#                 # unlock = 'SET SQL_SAFE_UPDATES=0'280#                 # cursor.execute(unlock)281#282#                 sql_command1 = f'''283#                 UPDATE test SET {test_column}=452080 WHERE284#                 {test_column} = '{name}'285#                 AND286#                 (YEAR(game_date)=2010)AND(matchup LIKE 'NYM%' OR matchup LIKE '%NYM')287#                 '''288#                 cursor.execute(sql_command1)289#                 MLBdb.commit()290#291#                 sql_command2 = f'UPDATE test SET {test_column}=474892 WHERE {test_column}="{name}"'292#                 cursor.execute(sql_command2)293#                 MLBdb.commit()294#295#                 # lock = 'SET SQL_SAFE_UPDATES=1'296#                 # cursor.execute(lock)297#298#             elif name == 'Luis Jimenez':299#                 print(f'èç{name}')300#301#                 # unlock = 'SET SQL_SAFE_UPDATES=0'302#                 # cursor.execute(unlock)303#304#                 sql_command1 = f'''305#                 UPDATE test SET {test_column}=455921 WHERE306#                 {test_column} = '{name}'307#                 AND308#                 (YEAR(game_date)=2012)AND(matchup LIKE 'SEA%' OR matchup LIKE '%SEA')309#                 '''310#                 cursor.execute(sql_command1)311#                 MLBdb.commit()312#313#                 sql_command2 = f'UPDATE test SET {test_column}=499864 WHERE {test_column}="{name}"'314#                 cursor.execute(sql_command2)315#                 MLBdb.commit()316#317#                 # lock = 'SET SQL_SAFE_UPDATES=1'318#                 # cursor.execute(lock)319#320#             elif name == 'Miguel Gonzalez':321#                 print(f'èç{name}')322#323#                 # unlock = 'SET SQL_SAFE_UPDATES=0'324#                 # cursor.execute(unlock)325#326#                 sql_command1 = f'''327#                 UPDATE test SET {test_column}=456068 WHERE328#                 {test_column} = '{name}'329#                 AND330#                 (331#                 (((YEAR(game_date)=2012)OR(YEAR(game_date)=2013)OR(YEAR(game_date)=2014)OR(YEAR(game_date)=2015))AND(matchup LIKE 'BAL%' OR matchup LIKE '%BAL'))332#                 OR333#                 (((YEAR(game_date)=2016)OR(YEAR(game_date)=2017)OR(YEAR(game_date)=2018))AND(matchup LIKE 'CHW%' OR matchup LIKE '%CHW'))334#                 OR335#                 (YEAR(game_date)=2016 AND (matchup LIKE 'TEX%' OR matchup LIKE '%TEX') )336#                 )337#                 '''338#                 cursor.execute(sql_command1)339#                 MLBdb.commit()340#341#                 sql_command2 = f'UPDATE test SET {test_column}=544838 WHERE {test_column}="{name}"'342#                 cursor.execute(sql_command2)343#                 MLBdb.commit()344#345#                 # lock = 'SET SQL_SAFE_UPDATES=1'346#                 # cursor.execute(lock)347#348#             elif name == 'Javy Guerra':349#                 print(f'èç{name}')350#351#                 # unlock = 'SET SQL_SAFE_UPDATES=0'352#                 # cursor.execute(unlock)353#354#                 sql_command1 = f'''355#                 UPDATE test SET {test_column}=642770 WHERE356#                 {test_column} = '{name}'357#                 AND358#                 ((YEAR(game_date)=2018)OR(YEAR(game_date)=2019))AND(matchup LIKE 'SD%' OR matchup LIKE '%SD')359#360#                 '''361#                 cursor.execute(sql_command1)362#                 MLBdb.commit()363#364#                 sql_command2 = f'UPDATE test SET {test_column}=457915 WHERE {test_column}="{name}"'365#                 cursor.execute(sql_command2)366#                 MLBdb.commit()367#368#                 # lock = 'SET SQL_SAFE_UPDATES=1'369#                 # cursor.execute(lock)370#371#             elif name == 'Josh Bell':372#                 print(f'èç{name}')373#374#                 # unlock = 'SET SQL_SAFE_UPDATES=0'375#                 # cursor.execute(unlock)376#377#                 sql_command1 = f'''378#                 UPDATE test SET {test_column}=605137 WHERE379#                 {test_column} = '{name}'380#                 AND381#                 ((YEAR(game_date)=2016)OR(YEAR(game_date)=2017)OR(YEAR(game_date)=2018)OR(YEAR(game_date)=2019))AND(matchup LIKE 'PIT%' OR matchup LIKE '%PIT')382#383#                 '''384#                 cursor.execute(sql_command1)385#                 MLBdb.commit()386#387#                 sql_command2 = f'UPDATE test SET {test_column}=458679 WHERE {test_column}="{name}"'388#                 cursor.execute(sql_command2)389#                 MLBdb.commit()390#391#                 # lock = 'SET SQL_SAFE_UPDATES=1'392#                 # cursor.execute(lock)393#394#             elif name == 'Matt Reynolds':395#                 print(f'èç{name}')396#397#                 # unlock = 'SET SQL_SAFE_UPDATES=0'398#                 # cursor.execute(unlock)399#400#                 sql_command1 = f'''401#                 UPDATE test SET {test_column}=608703 WHERE402#                 {test_column} = '{name}'403#                 AND404#                 (405#                 (((YEAR(game_date)=2016)OR(YEAR(game_date)=2017))AND(matchup LIKE 'NYM%' OR matchup LIKE '%NYM'))OR406#                 ((YEAR(game_date)=2018)AND(matchup LIKE 'WSH%' OR matchup LIKE '%WSH'))407#                 )408#                 '''409#                 cursor.execute(sql_command1)410#                 MLBdb.commit()411#412#                 sql_command2 = f'UPDATE test SET {test_column}=519186 WHERE {test_column}="{name}"'413#                 cursor.execute(sql_command2)414#                 MLBdb.commit()415#416#                 # lock = 'SET SQL_SAFE_UPDATES=1'417#                 # cursor.execute(lock)418#419#             elif name == 'Will Smith':420#                 print(f'èç{name}')421#422#                 # unlock = 'SET SQL_SAFE_UPDATES=0'423#                 # cursor.execute(unlock)424#425#                 sql_command1 = f'''426#                 UPDATE test SET {test_column}=669257 WHERE427#                 {test_column} = '{name}'428#                 AND429#                 (YEAR(game_date)=2019)AND(matchup LIKE 'LAD%' OR matchup LIKE '%LAD')430#                 '''431#                 cursor.execute(sql_command1)432#                 MLBdb.commit()433#434#                 sql_command2 = f'UPDATE test SET {test_column}=519293 WHERE {test_column}="{name}"'435#                 cursor.execute(sql_command2)436#                 MLBdb.commit()437#438#                 # lock = 'SET SQL_SAFE_UPDATES=1'439#                 # cursor.execute(lock)440#441#             elif name == 'Jose Ramirez':442#                 print(f'èç{name}')443#444#                 # unlock = 'SET SQL_SAFE_UPDATES=0'445#                 # cursor.execute(unlock)446#447#                 sql_command1 = f'''448#                 UPDATE test SET {test_column}=608070 WHERE449#                 {test_column} = '{name}'450#                 AND451#                 ((YEAR(game_date)=2013)OR(YEAR(game_date)=2014)OR(YEAR(game_date)=2015)OR(YEAR(game_date)=2016)OR(YEAR(game_date)=2017)OR452#                 (YEAR(game_date)=2018)OR(YEAR(game_date)=2019))453#                 AND(matchup LIKE 'CLE%' OR matchup LIKE '%CLE')454#                 '''455#                 cursor.execute(sql_command1)456#                 MLBdb.commit()457#458#                 sql_command2 = f'UPDATE test SET {test_column}=542432 WHERE {test_column}="{name}"'459#                 cursor.execute(sql_command2)460#                 MLBdb.commit()461#462#                 # lock = 'SET SQL_SAFE_UPDATES=1'463#                 # cursor.execute(lock)464#465#             elif name == 'Austin Adams':466#                 print(f'èç{name}')467#468#                 # unlock = 'SET SQL_SAFE_UPDATES=0'469#                 # cursor.execute(unlock)470#471#                 sql_command1 = f'''472#                 UPDATE test SET {test_column}=613534 WHERE473#                 {test_column} = '{name}'474#                 AND475#                 (476#                 ((YEAR(game_date)=2017)OR(YEAR(game_date)=2018)OR(YEAR(game_date)=2019))477#                 AND(matchup LIKE 'WSH%' OR matchup LIKE '%WSH'))478#                 OR479#                 ((YEAR(game_date)=2019)AND(matchup LIKE 'SEA%' OR matchup LIKE '%SEA')480#                 )481#                 '''482#                 cursor.execute(sql_command1)483#                 MLBdb.commit()484#485#                 sql_command2 = f'UPDATE test SET {test_column}=542866 WHERE {test_column}="{name}"'486#                 cursor.execute(sql_command2)487#                 MLBdb.commit()488#                 #489#                 # lock = 'SET SQL_SAFE_UPDATES=1'490#                 # cursor.execute(lock)491#492#             elif name == 'Daniel Robertson':493#                 print(f'èç{name}')494#495#                 # unlock = 'SET SQL_SAFE_UPDATES=0'496#                 # cursor.execute(unlock)497#498#                 sql_command1 = f'''499#                 UPDATE test SET {test_column}=621002 WHERE500#                 {test_column} = '{name}'501#                 AND502#                 ((YEAR(game_date)=2017)OR(YEAR(game_date)=2018)OR(YEAR(game_date)=2019))AND(matchup LIKE 'TB%' OR matchup LIKE '%TB')503#504#                 '''505#                 cursor.execute(sql_command1)506#                 MLBdb.commit()507#508#                 sql_command2 = f'UPDATE test SET {test_column}=543706 WHERE {test_column}="{name}"'509#                 cursor.execute(sql_command2)510#                 MLBdb.commit()511#512#                 # lock = 'SET SQL_SAFE_UPDATES=1'513#                 # cursor.execute(lock)514#515#             elif name == 'Matt Duffy':516#                 print(f'èç{name}')517#518#                 # unlock = 'SET SQL_SAFE_UPDATES=0'519#                 # cursor.execute(unlock)520#521#                 sql_command1 = f'''522#                 UPDATE test SET {test_column}=592274 WHERE523#                 {test_column} = '{name}'524#                 AND525#                 ((YEAR(game_date)=2015)OR(YEAR(game_date)=2016))AND(matchup LIKE 'HOU%' OR matchup LIKE '%HOU')526#527#                 '''528#                 cursor.execute(sql_command1)529#                 MLBdb.commit()530#531#                 sql_command2 = f'UPDATE test SET {test_column}=622110 WHERE {test_column}="{name}"'532#                 cursor.execute(sql_command2)533#                 MLBdb.commit()534#535#                 # lock = 'SET SQL_SAFE_UPDATES=1'536#                 # cursor.execute(lock)537#538#             else:539#                 # sql_unlock = 'SET SQL_SAFE_UPDATES=0'540#                 # cursor.execute(sql_unlock)541#                 # print(f'UPDATE test SET {test_column}={id_dic[name]} WHERE {test_column}="{name}"')542#                 # pass543#                 sql_update =f'UPDATE test SET {test_column}={id_dic[name]} WHERE {test_column}="{name}"'544#                 cursor.execute(sql_update)545#                 print('OK')546#                 # sql_lock = 'SET SQL_SAFE_UPDATES=1'547#                 # cursor.execute(sql_lock)548#549#         except KeyError:550#             lost_player += [name]551#             print('xx')552#553#554# sql_lock = 'SET SQL_SAFE_UPDATES=1'555# cursor.execute(sql_lock)556# MLBdb.commit()  #å²åå·è¡çåä½557#558#559# print('æ¥ç¡IDå½±é¿å ´æ¸:{}'.format(len(lost_player)))560# print(lost_player)561#562# lost_ID_dic = {}563# for i in lost_player:564#     if lost_player.count(i)>1:565#         lost_ID_dic[i] = lost_player.count(i)566# print('æ¥ç¡IDäººæ¸:{}'.format(len(lost_ID_dic)))567# print(lost_ID_dic)568#569#570# cursor.clos571572573'''574#â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â                       ããã575#   WAY2 å©ç¨pandasè¼å°æ¬å°ç«¯åèçããã576#â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â â 577'''578# åå°è¦èççtableç¨SQLDBè®pythonè®å°579# ç¶å¾å°æ ¼å¼è½ä¹dataframeåè¼å°æ¬å°ç«¯580# (åä¸æ¬¡å³å¯è¨»è§£æ)581# mlb_game = 'SELECT * FROM mlb_game'582# cursor.execute(mlb_game)583# df = pd.DataFrame(cursor.fetchall())584# df.to_csv('../game_test/raw_mlb_game.csv',encoding='utf8',index=False)585# MLBdb.close()586587#ç¨id_referenceè£½ä½åå¸ (éå ä»¥å å§åé¯èª¤èå¦å¤èç)588get_id_reference = 'select * FROM id_reference'589cursor.execute(get_id_reference)590name_id_dic = { id[1] : id[0]for id in cursor.fetchall()}  #è£½æåå¸å¾KEYéè¤çæè®ä¸å,ä¸ééè¤åèªä¹åå·²ç¶æåºä¾åä¾å¤èçæä»¥ä¸æå½±é¿)591592593594#è®åååè£½ä½çCSVæª595mlb_game = pd.read_csv('../game_test/raw_mlb_game.csv',index_col=False)596    # df.iat[colume, row] æ¥è©¢597    # x=df.iat[colume, row] ææxå¼598599600#ååºéåå¤æ·ç¨æ¬ä½(å¹´)(éä¼)601for j in range(mlb_game.shape[0]): #(æ¯årowé½è®ä¸æ¬¡)602    YMD = mlb_game.iat[j, 1]  #åå¹´603    MATCHUP = mlb_game.iat[j,3].split('@')604    # print(MATCHUP[0],MATCHUP[1]) #åä¸»å®¢é605    for i in range(4,24) :606        chigau_name_dic = {'John Mayberry':460055,'Nori Aoki':493114,'Matthew Joyce':459964,'Ivan De Jesus':474443,'Luis Cruz':458501,607                           'Chin-lung Hu':464341,'Juan Perez':448722,'T.J. House':543334,'Tony Gwynn':448242,'Tony Pena':430963,'Josh Smith':595001,608                           'Matt den Dekker':544925,'Mike Brosseau':670712,'Jon Niese':477003,'Rick van den Hurk':462995,'Phil Ervin':640447,609                           'Charles Leesman':489056,'Mike Morse':434604,'Rickie Weeks':430001,'Mike Ryan':408050,'Nathan Adcock':502264,'Val Pascucci':407831,610                           'Steven Tolleson':476270,'Dae-ho Lee':493193,'Albert Almora':546991,'Sam Deduno':465679,'Dwight Smith Jr.':596105,'Jae-gyun Hwang':666561,611                           'Russ Mitchell':452199,'Daniel Muno':594940,'Rafael Lopez':607257,'Robbie Ross':543726,'Melvin Upton Jr.':425834,'Luis Rodriguez':425904612                           }613        name = mlb_game.iat[j,i]614        # print(name) #åäººå615616        if name == 'Chris Young':617            print(f'èç{name}')618            if (YMD==2010 and ('SD'in MATCHUP)) or \619                ((YMD==2011 or YMD==2012) and ('NYM' in MATCHUP)) or \620                (YMD==2014 and ('SEA'in MATCHUP)) or \621                ((YMD==2015 or YMD==2016 or YMD==2017) and ('KC' in MATCHUP)):622                mlb_game.iat[j, i] = 432934623            else:624                mlb_game.iat[j, i] = 455759625626627        elif name == 'Rich Thompson':628            print(f'èç{name}')629            if (YMD==2012 and ('TB'in MATCHUP)) :630                mlb_game.iat[j, i] = 430829631            else:632                mlb_game.iat[j, i] = 460366633634635        elif name == 'Angel Sanchez':636            print(f'èç{name}')637            if (YMD == 2010 and (('BOS' in MATCHUP)or('HOU' in MATCHUP))) or \638                    ((YMD == 2011 ) and ('HOU' in MATCHUP)) or \639                    (YMD == 2012 and ('CHW' in MATCHUP)):640                mlb_game.iat[j, i] = 447816641            else:642                mlb_game.iat[j, i] = 605795643644645        elif name == 'Chris Carpenter' :646            print(f'èç{name}')647            if ((YMD == 2010 or YMD == 2011 or YMD == 2012) and ('STL' in MATCHUP)):648                mlb_game.iat[j, i] = 112020649            else:650                mlb_game.iat[j, i] = 452764651652653        elif name == 'Luis Castillo':654            print(f'èç{name}')655            if ((YMD == 2017 or YMD == 2018 or YMD == 2019) and ('CIN' in MATCHUP)):656                mlb_game.iat[j, i] = 622419657            else:658                mlb_game.iat[j, i] = 112116659660661        elif name == 'Josh Fields':662            print(f'èç{name}')663            if (YMD == 2010 and ('KC' in MATCHUP)):664                mlb_game.iat[j, i] = 451661665            else:666                mlb_game.iat[j, i] = 425222667668669        elif name == 'Chris Smith':670            print(f'èç{name}')671            if (YMD == 2010 and ('MIL' in MATCHUP)) or \672                ((YMD==2016 or YMD==2017) and ('OAK' in MATCHUP)):673                mlb_game.iat[j, i] = 434672674            else:675                mlb_game.iat[j, i] = 627262676677678        elif name == 'Kyle Waldrop':679            print(f'èç{name}')680            if ((YMD==2015 or YMD==2016)and ('CIN' in MATCHUP)):681                mlb_game.iat[j, i] = 592835682            else:683                mlb_game.iat[j, i] = 448252684685686        elif name == 'Chris Carter':687            print(f'èç{name}')688            if (YMD == 2010 and ('NYM' in MATCHUP)):689                mlb_game.iat[j, i] = 452080690            else:691                mlb_game.iat[j, i] = 474892692693694        elif name == 'Luis Jimenez':695            print(f'èç{name}')696            if (YMD == 2012 and ('SEA' in MATCHUP)):697                mlb_game.iat[j, i] = 455921698            else:699                mlb_game.iat[j, i] = 499864700701702        elif name == 'Miguel Gonzalez':703            print(f'èç{name}')704            if ((YMD == 2012 or YMD==2013 or YMD==2014 or YMD==2015 )and ('BAL' in MATCHUP)) or \705                ((YMD == 2016 or YMD==2017 or YMD==2018)and ('CHW' in MATCHUP)) or \706                (YMD == 2017 and ('TEX' in MATCHUP)):707                mlb_game.iat[j, i] = 456068708            else:709                mlb_game.iat[j, i] = 544838710711712        elif name == 'Javy Guerra':713            print(f'èç{name}')714            if ((YMD == 2018 or YMD==2019) and ('SD' in MATCHUP)):715                mlb_game.iat[j, i] =642770716            else:717                mlb_game.iat[j, i] =457915718719720        elif name == 'Josh Bell':721            print(f'èç{name}')722            if ((YMD == 2016 or YMD == 2017 or YMD == 2018 or YMD == 2019) and ('PIT' in MATCHUP)):723                mlb_game.iat[j, i] = 605137724            else:725                mlb_game.iat[j, i] = 458679726727728        elif name == 'Matt Reynolds':729            print(f'èç{name}')730            if ((YMD == 2016 or YMD == 2017) and ('NYM' in MATCHUP)) or \731                (YMD == 2018 and ('WSH' in MATCHUP)) :732                mlb_game.iat[j, i] = 608703733            else:734                mlb_game.iat[j, i] = 519186735736737        elif name == 'Will Smith':738            print(f'èç{name}')739            if (YMD == 2019 and ('LAD' in MATCHUP)):740                mlb_game.iat[j, i] = 669257741            else:742                mlb_game.iat[j, i] = 519293743744745        elif name == 'Jose Ramirez':746            print(f'èç{name}')747            if ((YMD == 2013 or YMD==2014 or YMD==2015 or YMD==2016 or YMD==2017 or YMD==2018 or YMD==2019) and ('CLE' in MATCHUP)):748                mlb_game.iat[j, i] = 608070749            else:750                mlb_game.iat[j, i] = 542432751752753        elif name == 'Austin Adams':754            print(f'èç{name}')755            if ((YMD == 2017 or YMD==2018 or YMD==2019) and ('WSH' in MATCHUP)):756                mlb_game.iat[j, i] = 613534757            else:758                mlb_game.iat[j, i] = 542866759760761        elif name == 'Daniel Robertson':762            print(f'èç{name}')763            if ((YMD == 2017 or YMD==2018 or YMD==2019) and ('TB' in MATCHUP)):764                mlb_game.iat[j, i] = 621002765            else:766                mlb_game.iat[j, i] = 543706767768769        elif name == 'Matt Duffy':770            print(f'èç{name}')771            if ((YMD == 2015 or YMD==2016) and ('HOU' in MATCHUP)):772                mlb_game.iat[j, i] = 592274773            else:774                mlb_game.iat[j, i] = 622110775776        elif name in chigau_name_dic.keys():777            mlb_game.iat[j, i] = chigau_name_dic[name]778            # print('Good')779780781        else:782            mlb_game.iat[j, i] = name_id_dic[name]783            # print('OK')784    #ææéæ ¼å¼785    mlb_game.iat[j, 2] = mlb_game.iat[j, 2][7:12]786787mlb_game.columns = columns_list788mlb_game.to_csv('ttt2.csv',encoding='utf-8',index=False)789790
...

test_types.py

Source:test_types.py

1from enum import Enum2import sqlalchemy as sa3from pytest import mark, raises4from sqlalchemy_utils import (5    ChoiceType,6    ColorType,7    CountryType,8    EmailType,9    IntRangeType,10    PasswordType,11    PhoneNumberType,12    URLType,13    UUIDType14)15from sqlalchemy_utils.types import arrow, phone_number, WeekDaysType  # noqa16from wtforms.fields import (17    BooleanField,18    FloatField,19    PasswordField,20    TextAreaField21)22from wtforms.validators import Length, URL23from wtforms_components import Email24from wtforms_components.fields import (25    ColorField,26    DateField,27    DateTimeField,28    DecimalField,29    EmailField,30    IntegerField,31    IntIntervalField,32    SelectField,33    StringField,34    TimeField35)36from tests import ModelFormTestCase37from wtforms_alchemy import (38    CountryField,39    ModelForm,40    null_or_unicode,41    PhoneNumberField,42    UnknownTypeException,43    WeekDaysField44)45from wtforms_alchemy.utils import ClassMap46try:47    import passlib  # noqa48except ImportError:49    passlib = None50class UnknownType(sa.types.UserDefinedType):51    def get_col_spec(self):52        return 'UNKNOWN()'53class CustomUnicodeTextType(sa.types.TypeDecorator):54    impl = sa.types.UnicodeText55class CustomUnicodeType(sa.types.TypeDecorator):56    impl = sa.types.Unicode57class CustomNumericType(sa.types.TypeDecorator):58    impl = sa.types.Numeric59class TestModelColumnToFormFieldTypeConversion(ModelFormTestCase):60    def test_raises_exception_for_unknown_type(self):61        with raises(UnknownTypeException):62            self.init(type_=UnknownType)63            self.form_class()64    def test_raises_exception_for_array_type(self):65        with raises(UnknownTypeException):66            self.init(type_=sa.ARRAY(sa.Integer))67            self.form_class()68    def test_unicode_converts_to_text_field(self):69        self.init()70        self.assert_type('test_column', StringField)71    def test_custom_unicode_converts_to_text_field(self):72        self.init(type_=CustomUnicodeType)73        self.assert_type('test_column', StringField)74    def test_string_converts_to_text_field(self):75        self.init(type_=sa.String)76        self.assert_type('test_column', StringField)77    def test_integer_converts_to_integer_field(self):78        self.init(type_=sa.Integer)79        self.assert_type('test_column', IntegerField)80    def test_unicode_text_converts_to_text_area_field(self):81        self.init(type_=sa.UnicodeText)82        self.assert_type('test_column', TextAreaField)83    def test_custom_unicode_text_converts_to_text_area_field(self):84        self.init(type_=CustomUnicodeTextType)85        self.assert_type('test_column', TextAreaField)86    def test_boolean_converts_to_boolean_field(self):87        self.init(type_=sa.Boolean)88        self.assert_type('test_column', BooleanField)89    def test_datetime_converts_to_datetime_field(self):90        self.init(type_=sa.DateTime)91        self.assert_type('test_column', DateTimeField)92    def test_date_converts_to_date_field(self):93        self.init(type_=sa.Date)94        self.assert_type('test_column', DateField)95    def test_float_converts_to_float_field(self):96        self.init(type_=sa.Float)97        self.assert_type('test_column', FloatField)98    def test_numeric_converts_to_decimal_field(self):99        self.init(type_=sa.Numeric)100        self.assert_type('test_column', DecimalField)101    def test_numeric_scale_converts_to_decimal_field_scale(self):102        self.init(type_=sa.Numeric(scale=4))103        form = self.form_class()104        assert form.test_column.places == 4105    def test_custom_numeric_converts_to_decimal_field(self):106        self.init(type_=CustomNumericType)107        self.assert_type('test_column', DecimalField)108    def test_enum_field_converts_to_select_field(self):109        choices = ['1', '2']110        self.init(type_=sa.Enum(*choices))111        self.assert_type('test_column', SelectField)112        form = self.form_class()113        assert form.test_column.choices == [(s, s) for s in choices]114    def test_nullable_enum_uses_null_or_unicode_coerce_func_by_default(self):115        choices = ['1', '2']116        self.init(type_=sa.Enum(*choices), nullable=True)117        field = self._get_field('test_column')118        assert field.coerce == null_or_unicode119    def test_custom_choices_override_enum_choices(self):120        choices = ['1', '2']121        custom_choices = [('2', '2'), ('3', '3')]122        self.init(type_=sa.Enum(*choices), info={'choices': custom_choices})123        form = self.form_class()124        assert form.test_column.choices == custom_choices125    def test_column_with_choices_converts_to_select_field(self):126        choices = [(u'1', '1'), (u'2', '2')]127        self.init(type_=sa.Integer, info={'choices': choices})128        self.assert_type('test_column', SelectField)129        form = self.form_class()130        assert form.test_column.choices == choices131    def test_assigns_email_validator_for_email_type(self):132        self.init(type_=EmailType)133        self.assert_has_validator('test_column', Email)134    def test_assigns_url_validator_for_url_type(self):135        self.init(type_=URLType)136        self.assert_has_validator('test_column', URL)137    def test_time_converts_to_time_field(self):138        self.init(type_=sa.types.Time)139        self.assert_type('test_column', TimeField)140    def test_varchar_converts_to_text_field(self):141        self.init(type_=sa.types.VARCHAR)142        self.assert_type('test_column', StringField)143    def test_text_converts_to_textarea_field(self):144        self.init(type_=sa.types.TEXT)145        self.assert_type('test_column', TextAreaField)146    def test_char_converts_to_text_field(self):147        self.init(type_=sa.types.CHAR)148        self.assert_type('test_column', StringField)149    def test_real_converts_to_float_field(self):150        self.init(type_=sa.types.REAL)151        self.assert_type('test_column', FloatField)152    def test_json_converts_to_textarea_field(self):153        self.init(type_=sa.types.JSON)154        self.assert_type('test_column', TextAreaField)155    @mark.xfail('phone_number.phonenumbers is None')156    def test_phone_number_converts_to_phone_number_field(self):157        self.init(type_=PhoneNumberType)158        self.assert_type('test_column', PhoneNumberField)159    @mark.xfail('phone_number.phonenumbers is None')160    def test_phone_number_country_code_passed_to_field(self):161        self.init(type_=PhoneNumberType(region='SE'))162        form = self.form_class()163        assert form.test_column.region == 'SE'164    @mark.xfail('phone_number.phonenumbers is None')165    def test_phone_number_type_has_no_length_validation(self):166        self.init(type_=PhoneNumberType(country_code='FI'))167        field = self._get_field('test_column')168        for validator in field.validators:169            assert validator.__class__ != Length170    @mark.parametrize(('type', 'field'), (171        (IntRangeType, IntIntervalField),172    ))173    def test_range_type_conversion(self, type, field):174        self.init(type_=type)175        self.assert_type('test_column', field)176    @mark.xfail('passlib is None')177    def test_password_type_converts_to_password_field(self):178        self.init(type_=PasswordType)179        self.assert_type('test_column', PasswordField)180    @mark.xfail('arrow.arrow is None')181    def test_arrow_type_converts_to_datetime_field(self):182        self.init(type_=arrow.ArrowType)183        self.assert_type('test_column', DateTimeField)184    def test_url_type_converts_to_string_field(self):185        self.init(type_=URLType)186        self.assert_type('test_column', StringField)187    def test_uuid_type_converst_to_uuid_type(self):188        self.init(type_=UUIDType)189        self.assert_type('test_column', StringField)190    def test_color_type_converts_to_color_field(self):191        self.init(type_=ColorType)192        self.assert_type('test_column', ColorField)193    def test_email_type_converts_to_email_field(self):194        self.init(type_=EmailType)195        self.assert_type('test_column', EmailField)196    def test_country_type_converts_to_country_field(self):197        self.init(type_=CountryType)198        self.assert_type('test_column', CountryField)199    def test_choice_type_converts_to_select_field(self):200        choices = [('1', 'choice 1'), ('2', 'choice 2')]201        self.init(type_=ChoiceType(choices))202        self.assert_type('test_column', SelectField)203        assert list(self.form_class().test_column.choices) == choices204    def test_choice_type_uses_custom_coerce_func(self):205        choices = [('1', 'choice 1'), ('2', 'choice 2')]206        self.init(type_=ChoiceType(choices))207        self.assert_type('test_column', SelectField)208        model = self.ModelTest(test_column=u'2')209        form = self.form_class(obj=model)210        assert '<option selected value="2">' in str(form.test_column)211    def test_choice_type_with_enum(self):212        class Choice(Enum):213            choice1 = 1214            choice2 = 2215            def __str__(self):216                return self.name217        self.init(type_=ChoiceType(Choice))218        self.assert_type('test_column', SelectField)219        assert self.form_class().test_column.choices == [220            (1, 'choice1'), (2, 'choice2')221        ]222    @mark.parametrize(223        ['type_', 'impl'],224        [225            (int, sa.Integer()),226            (str, sa.String())227        ]228    )229    def test_choice_type_with_enum_uses_custom_coerce_func(self, type_, impl):230        class Choice(Enum):231            choice1 = type_(1)232            choice2 = type_(2)233            def __str__(self):234                return self.name235        self.init(type_=ChoiceType(Choice, impl=impl))236        self.assert_type('test_column', SelectField)237        model = self.ModelTest(test_column=type_(2))238        form = self.form_class(obj=model)239        assert '<option selected value="2">' in str(form.test_column)240class TestWeekDaysTypeConversion(ModelFormTestCase):241    def test_weekdays_type_converts_to_weekdays_field(self):242        self.init(type_=WeekDaysType)243        self.assert_type('test_column', WeekDaysField)244class TestCustomTypeMap(ModelFormTestCase):245    def test_override_type_map_on_class_level(self):246        class ModelTest(self.base):247            __tablename__ = 'model_test'248            id = sa.Column(sa.Integer, primary_key=True)249            test_column = sa.Column(sa.Unicode(255), nullable=False)250        class ModelTestForm(ModelForm):251            class Meta:252                model = ModelTest253                not_null_validator = None254                type_map = ClassMap({sa.Unicode: TextAreaField})255        form = ModelTestForm()256        assert isinstance(form.test_column, TextAreaField)257    def test_override_type_map_with_callable(self):258        class ModelTest(self.base):259            __tablename__ = 'model_test'260            id = sa.Column(sa.Integer, primary_key=True)261            test_column_short = sa.Column(sa.Unicode(255), nullable=False)262            test_column_long = sa.Column(sa.Unicode(), nullable=False)263        class ModelTestForm(ModelForm):264            class Meta:265                model = ModelTest266                not_null_validator = None267                type_map = ClassMap({268                    sa.Unicode: lambda column: (269                        StringField if column.type.length else TextAreaField270                    )271                })272        form = ModelTestForm()273        assert isinstance(form.test_column_short, StringField)...

test_select_field.py

Source:test_select_field.py

1from decimal import Decimal2import six3import sqlalchemy as sa4from wtforms_components import SelectField5from tests import ModelFormTestCase6class MultiDict(dict):7    def getlist(self, key):8        return [self[key]]9class TestSelectFieldDefaultValue(ModelFormTestCase):10    def test_option_selected_by_field_default_value(self):11        choices = [(u'1', '1'), (u'2', '2')]12        self.init(13            type_=sa.Integer,14            default=u'1',15            info={'choices': choices}16        )17        form = self.form_class(MultiDict({'test_column': '2'}))18        assert (19            '<option selected value="2">2</option>' in20            str(form.test_column)21        )22class TestSelectFieldCoerce(ModelFormTestCase):23    def test_integer_coerces_values_to_integers(self):24        choices = [(u'1', '1'), (u'2', '2')]25        self.init(type_=sa.Integer, info={'choices': choices})26        form = self.form_class(MultiDict({'test_column': '2'}))27        assert form.test_column.data == 228    def test_nullable_integer_coerces_values_to_integers(self):29        choices = [(u'1', '1'), (u'2', '2')]30        self.init(type_=sa.Integer, nullable=True, info={'choices': choices})31        form = self.form_class(MultiDict({'test_column': '2'}))32        assert form.test_column.data == 233    def test_integer_coerces_empty_strings_to_nulls(self):34        choices = [(u'1', '1'), (u'2', '2')]35        self.init(type_=sa.Integer, info={'choices': choices})36        form = self.form_class(MultiDict({'test_column': ''}))37        assert form.test_column.data is None38    def test_big_integer_coerces_values_to_integers(self):39        choices = [(u'1', '1'), (u'2', '2')]40        self.init(type_=sa.BigInteger, info={'choices': choices})41        self.assert_type('test_column', SelectField)42        form = self.form_class(MultiDict({'test_column': '2'}))43        assert form.test_column.data == 244    def test_small_integer_coerces_values_to_integers(self):45        choices = [(u'1', '1'), (u'2', '2')]46        self.init(type_=sa.SmallInteger, info={'choices': choices})47        form = self.form_class(MultiDict({'test_column': '2'}))48        assert form.test_column.data == 249    def test_numeric_coerces_values_to_decimals(self):50        choices = [(u'1.0', '1.0'), (u'2.0', '2.0')]51        self.init(type_=sa.Numeric, info={'choices': choices})52        form = self.form_class(MultiDict({'test_column': '2.0'}))53        assert form.test_column.data == Decimal('2.0')54    def test_float_coerces_values_to_floats(self):55        choices = [(u'1.0', '1.0'), (u'2.0', '2.0')]56        self.init(type_=sa.Float, info={'choices': choices})57        form = self.form_class(MultiDict({'test_column': '2.0'}))58        assert form.test_column.data == 2.059    def test_unicode_coerces_values_to_unicode_strings(self):60        choices = [('1.0', '1.0'), ('2.0', '2.0')]61        self.init(type_=sa.Unicode(255), info={'choices': choices})62        form = self.form_class(MultiDict({'test_column': '2.0'}))63        assert form.test_column.data == u'2.0'64        assert isinstance(form.test_column.data, six.text_type)65    def test_unicode_text_coerces_values_to_unicode_strings(self):66        choices = [('1.0', '1.0'), ('2.0', '2.0')]67        self.init(type_=sa.UnicodeText, info={'choices': choices})68        form = self.form_class(MultiDict({'test_column': '2.0'}))69        assert form.test_column.data == u'2.0'...

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.