mowareのブログ – ページ 84 – 気ままにコードを書いているホビープログラマ。iOS, watchOS, visionOS, Microsoft Storeアプリ開発者です。

[Python] 251 BeautifulSoupのparser選択

久々のスクレイピングネタです。

万能だと思っていた構文解析器 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)]

[Python] 250 データフレームをHTMLファイルに変換する

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)

[Python] 249 今日、今月、今年、曜日の表記

よく使うのでメモ書きしておきます。

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
曜日 水

[Python] 248 tkinter 22 MySQLツールの拡充レース検索

期間指定でレース検索ができるようになりました。

日付文字列をハイフン付きに変換して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()

[Python] 247 MySQL 10 年月日表記をdate型として取り込む

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')

[Python] 246 MySQL 09 一時テーブルを使って上書き

前回の記事では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()

[Python] 245 MySQL 08 データフレームをSQLテーブルに変換

レース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] 244 データフレーム重複行の削除

[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)

[Python] 243 MySQL 07 テーブルをキーで結合する

レース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()

[Python] 242 MySQL 06 データの一部を置き換え

競馬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()

日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31