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