久々のスクレイピングネタです。
万能だと思っていた構文解析器 lxmlがそうでもなかったのでメモ書き。
from bs4 import BeautifulSoup
＜中略＞
# webページのソースコードを取得
html = driver.page_source.encode('utf-8')
# 基本的にはlxmlを使っています
try:
soup = BeautifulSoup(html, "lxml")
# うまくいかない場合はデフォルトのhtml.parserを使ってみる
except:
soup = BeautifulSoup(html, "html.parser")
# 要素を抽出(例)
elements = soup.find_all("td",{"class":"txt_l"})
href_l = [str(e) for e in elements if 'href="/race' in str(e)]pandasの機能の中でも特に使い出があります。
私製競馬DBの検索結果はCSVよりもHTMLの方が断然見やすいです。
CSVをExcelで開くと通過順を日付にしようとする悪癖が出るので、HTMLをデフォルトにしたいです。
クロノジェネシスの検索結果を見て改めて感じたのですが、グランアレグリア、ラヴズオンリーユー、カレンブーケドールなど、2019年クラシック牝馬のレベルはものすごく高いですね。
# リストをデータフレームに変換する
df_index_columns = pd.DataFrame(race_l[1:], columns=race_l[0])
# 結果ファイル名の作成
datetime_now = datetime.datetime.now()
datetime_now_str = datetime_now.strftime('%y%m%d%H%M')
filename = f"/{datetime_now_str}_{name}.csv"
filename_html = f"/{datetime_now_str}_{name}.html"
with open(filename, 'w',encoding = 'shift_jis') as f:
df_index_columns.to_csv(f,index=False)
with open(filename_html, 'w',encoding = 'shift_jis') as f2:
df_index_columns.to_html(f2,index=False)
よく使うのでメモ書きしておきます。
import datetime
import locale
today = datetime.date.today()
this_month = today.month
this_year = today.year
print(f'今日 {today})
print(f'今月 {this_month})
print(f'今年 {this_year})
locale.setlocale(locale.LC_TIME, 'ja_JP.UTF-8')
youbi = today.strftime('%a')
print(f'曜日 {youbi}')
--------------------------------------------------
出力
--------------------------------------------------
今日 2021-04-28
今月 4
今年 2021
曜日 水
期間指定でレース検索ができるようになりました。
日付文字列をハイフン付きに変換してWHERE条件式に代入しました。
検索条件は日付だけでなく、距離、競馬場、条件戦なども追加するつもりです。
使っていて感じるのですが、もしかしたらTARGET Frontierよりも断然早いのでは。
今のところ機能面では話にならないものの、検索速度自体はMySQLに分がありそうです。まあ操作している人間は初学者ですが、一応本式のデータベースですから。
TARGET Frontierは久しく使っていないので、期間限定で再契約しノウハウを学びたいです。
＜コードの一部を掲載。なお入力値は自作ライブラリmysql_searchにて処理＞
# フレームの作成
frameA = FrameA(master=horse)
# IntVarの初期化
var = tk.IntVar()
for i in range(1000):
print(f'var for文先頭 {var.get()}')
children = frameA.winfo_children()
if var.get() == 0 or var.get() == 1:
print('分岐A')
# 馬名の入力を待機
children[2].wait_variable(var)
# 入力した馬名を取得
name = children[1].get()
print(f'name {name}')
if name != '':
# 競走馬成績を検索
mysql_search.Mysql_search().horse_result(name)
else:
print(f'分岐B')
childrenB = frameB.winfo_children()
childrenB2 = frameB2.winfo_children()
childrenB3 = frameB3.winfo_children()
print(f'frameB_info\n{childrenB}\n')
print(f'frameB2_info\n{childrenB2}\n')
print(f'frameB3_info\n{childrenB3}\n')
# レース名の入力を待機
childrenB[1].wait_variable(var)
# 入力したレース名を取得
race = childrenB[1].get()
start = childrenB2[0].get()
if start == '':
start = '860101'
end = childrenB3[0].get()
if end == '':
end = (datetime.date.today()).strftime("%y%m%d")
print(f'race {race}')
print(f'開始日 {start}')
print(f'終了日 {end}')
if race != '':
# レース結果を検索
mysql_search.Mysql_search().race_result(race,start,end)
frame.mainloop()
Y年m月d日と表記された文字列はそのままではdate型として取り込めないため、YYYY-mm-dd表記に変換する必要があります。
<前後は省略>
for file,table,sql in zip(file_l,table_l,sql_l):
try:
cur.execute(sql)
except:
pass
else:
cur.execute('BEGIN')
# CSVファイルを読み込み、各行をtableに挿入する
with open(file, 'rt', encoding='Shift-JIS') as f:
reader = csv.reader(f)
for i,row in enumerate(reader):
print(f'row {row}')
if i != 0:
# 年月日の文字列からdatetimeに変換
row_str_date_pre = datetime.datetime.strptime(str(row[0]),\
'%Y年%m月%d日')
# datetimeからハイフン入り年月日の文字列に変換
row_str_date = row_str_date_pre.strftime("%Y-%m-%d")
# 日付とその他を結合してタプルにする
row_str = tuple([row_str_date] + row[1:])
sql = f'INSERT INTO horse_race_name.{table} VALUES {row_str}'
cur.execute(sql)
cur.execute('COMMIT')
前回の記事ではSELECT文で作成したテーブルをデータフレームにして加工し、またテーブルに戻しました。しかし、この方法ではデータ型が全てtextになってしまいました。
そこでテーブルのまま加工してからデータ全てを置き換える方法でやってみました。
できることはできましたがかなり難航しました。結構泥臭い内容です。
import glob,mysql.connector
＜接続設定は省略＞
# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_name/*/*.csv')]
# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-17:-4] for path in file_l]
print(table_l)
# mysqlに接続
con = mysql.connector.connect(**config)
cur = con.cursor()
# データベースhorse_race_nameとhorse_race_winnerを結合して不要な列を削除した一時
# テーブルhorse_race_name.tmpを作成し元のテーブルに上書きする
for table in table_l:
cur.execute('BEGIN')
year = table[-6:-2]
sql = f'CREATE TEMPORARY TABLE horse_race_name.tmp SELECT 日付,開催,レース,\
レース名,条件,コース,天候,馬場状態,発走時刻,horse_race_winner.{year}_1着馬リスト.\
1着馬,horse_race_name.{table}.raceID FROM horse_race_name.{table} \
INNER JOIN horse_race_winner.{year}_1着馬リスト \
ON horse_race_name.{table}.raceID = horse_race_winner.{year}_1着馬リスト.\
raceID'
cur.execute(sql)
cur.execute(f'DROP TABLE horse_race_name.{table}')
cur.execute(f'CREATE TABLE horse_race_name.{table} LIKE horse_race_name.tmp')
cur.execute(f'INSERT INTO horse_race_name.{table} SELECT * from horse_race_name.tmp')
cur.execute('DROP TABLE horse_race_name.tmp')
cur.execute('COMMIT')
con.close()
レースDBに1着馬名の列を追加しました。
mysql.connectorではデータフレームをSQLテーブルに変換できないので、PyMySQLを使いました。
今回のケースではわざわざデータフレームに変換しなくても、ALTER TABLE文で列削除できたのかもしれません。
import csv,glob
import pandas as pd
import pymysql
import sqlalchemy as sqa
# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_name/*/*.csv')]
# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-17:-4] for path in file_l]
# mysqlに接続
url = 'mysql+pymysql://<username>:<password>@<host>'
engine = sqa.create_engine(url, echo=True)
# データベースhorse_race_nameとhorse_race_winnerのテーブルを結合して不要な列を削除後、MySQLに戻す
for table in table_l:
year = table[-6:-2]
sql = f'SELECT * FROM horse_race_name.{table} INNER JOIN \
horse_race_winner.{year}_1着馬リスト ON horse_race_name.{table}.raceID \
= horse_race_winner.{year}_1着馬リスト.raceID'
data = pd.read_sql_query(sql=sql, con=engine)
data_new = data.drop(data.columns[[-2]], axis=1)
data_new.to_sql(name=f'{table}', con=engine, schema='horse_race_name', \
if_exists='replace', index=False)
[Python] 243の続きです。
1着馬DBで1着同着の場合は2頭の名前を登録するようにしました。horseIDは先頭馬1頭のままです。
pandasのduplicatedメソッドを使いました。
これでレースの行が1行にまとまります。
同着レースの1着馬を置き換えたCSVファイルを1着馬DBとして登録します。
import pandas as pd
for year in range(1986,2021 +1):
filename = f'/horse_racing/race_winner/{year}_1着馬リスト.csv'
df = pd.read_csv(filename,encoding='shift_jis')
dup_rows = df.duplicated(['raceID'])
print(f'重複行 {dup_rows}')
dup_l = list()
for i,row in enumerate(dup_rows):
if row == True:
dup_l.append(i)
print(dup_l)
for num in dup_l:
rows = df.iloc[[num -1 ,num]]
row_l=rows.values.tolist()
# 馬名2頭分を作成
names = row_l[0][2] + ',' + row_l[1][2]
# 馬名2頭分に置き換え
df.iloc[num -1, 2] = names
# 置き換えの確認
rows2 = df.iloc[[num -1 ,num]]
row_l2=rows2.values.tolist()
print(row_l2)
# 重複行の削除
df_new = df.drop(df.index[[dup_l]])
# 新ファイル名を作成
filename_new = f'/horse_racing/race_winner/{year}_1着馬リスト_replace.csv'
with open(filename_new, 'w', newline='',encoding = 'shift_jis') as f:
df_new.to_csv(f,index=False)
レースDBに1着馬の列を加えることにしました。
まずレースDBと1着馬DBとをraceIDをキーとして内部結合します。結合データをpandasでデータフレームにしてCSV化します。
1着同着のレースは2行になるので何らかの処理が必要です。
重複列、同着レースの処理を済ませたら、MySQLにDB登録します。
import mysql.connector,glob
import pandas as pd
＜接続設定は省略＞
# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_name/*/*.csv')]
# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-17:-4] for path in file_l]
# mysqlに接続
con = mysql.connector.connect(**config)
# データベースhorse_race_nameとhorse_race_winnerを結合してCSV出力する
for table in table_l:
year = table[-6:-2]
sql = f'SELECT * FROM horse_race_name.{table} INNER JOIN \
horse_race_winner.{year}_1着馬リスト ON horse_race_name.{table}.raceID \
= horse_race_winner.{year}_1着馬リスト.raceID'
data = pd.read_sql_query( sql = sql , con = con )
# 結合ファイル名の作成
filename = f'{table}.csv'
with open(filename, 'w', newline='',encoding = 'shift_jis') as f:
data.to_csv(f,index=False)
con.close()
競馬DBの作成でかなり根を詰めて作業をしていたので、5日ほどベアボーンPCやAdobe XDをいじって気分転換してました。
競馬DBの着順と人気が小数点付きになってしまっているため、これを削除するコードを書きました。データの更新ですからUPDATE文になります。
全てが小数点数になっていればデータ検索に支障はないはずですが、実際は整数と混在しているため処理します。
import mysql.connector,glob
＜接続設定は省略＞
# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_result/*.csv')]
# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-15:-4] for path in file_l]
print(table_l)
# sqlのリストを作成
sql_l = list()
for table in table_l:
sql = f"update horse_race_result.{table} set 着順 = \
replace(着順, '.0', ''), 人気 = replace(人気, '.0', '')"
sql_l.append(sql)
# mysqlに接続
con = mysql.connector.connect(**config)
cur = conn.cursor()
# データベースhorse_race_resultの着順と人気から'.0'を削除する
for sql in sql_l:
cur.execute('begin')
cur.execute(sql)
cur.execute('commit')
con.close()