日: 2021年4月24日

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