日: 2021年4月15日

[Python] 236 MySQL 04 馬名からレース検索

[Python] 235の続きです。

さすがに1万ファイルは多かったようで良く見るとphpMyAdminが白旗をあげていました。

そこでレース結果ファイルを年毎に1つにまとめてインポートしました。

これでレース検索するとraceID検索までで2秒程度でした。しかし結合した年ファイルであればCSV横断検索でも同じ位です。

とは言えSQLによる値の取り出しやすさは捨てがたいものがあり、horseIDの取得からレース検索完了までフルでMySQLを使ったらどんな感じになるかか試してみたいです。

Target frontierはJRA-VANのデータを元に独自データベースとして機能しているわけですから、過度にSQLにこだわる必要もなさそうです。

ただあちらはC言語用のテキストデータなのでより高速なのは間違いないです。

# horseIDはCSV横断検索、receIDはSQL検索
import mysql.connector,glob,csv,re,datetime,sys

config = {
  'user': 'root',
  'password': 'root',
  'host': 'localhost',
  'port': 3306,
  'raise_on_warnings': True
}

print('検索したい馬名を入力してください')
name = input()

＜中略＞

print(f'検索馬のID {id} 誕生年 {id[0:4]}')

start_year = int(id[0:4]) + 2
print(f'推定デビュー年 {start_year}')

conn = mysql.connector.connect(**config)
cur = conn.cursor()

raceID_l = list()
for y in range(start_year,2017 +1):
	table = f'race_r_{y}'

	try:
		cur.execute(f'SELECT raceID FROM horse_race_result.{table} WHERE horseID = {id}')

	finally:
		result = cur.fetchall()
		if result != []:
			raceID_l.append(result)

# ネストを平滑化する
raceID_l_flatten = [e for l in raceID_l for e in l]

print(f'出走数 {len(raceID_l_flatten)}')
print(raceID_l_flatten)

cur.close()
conn.close()
--------------------------------------------------

出力
--------------------------------------------------
検索したい馬名を入力してください
キタサンブラック
[['201202013', 'キタサンブラック']]
検索馬のID 201202013 誕生年 2012
推定デビュー年 2014
出走数 20
[('r201505010105',), ('r201505010807',), ('r201505021210',), ('r201506020811',), ('r201506030811',), ('r201506040511',), ('r201506050810',), ('r201508040711',), ('r201605050811',), ('r201606050910',), ('r201608030411',), ('r201608040311',), ('r201609020411',), ('r201609030811',), ('r201705040911',), ('r201705050811',), ('r201706050811',), ('r201708030411',), ('r201709020411',), ('r201709030811',)]

[Python] 235 MySQL 03 CSVを一括インポートする

1万を超えるレースデータを一括インポートしてみました。

実際は最初のfor文を回さず年ごとに3回に分けてインポートしました。1〜2KB約3400ファイルで2分掛からなかった感じです。

インポート直後にはphpMyAdminがダウンしますが、MAMPを再起動すると正常に動きました。

私が扱うデータ量程度ではMySQLのキャパを超えることはないと思います。

これからせっせとデータを入れていきます。

import csv,mysql.connector,glob
import pandas as pd

config = {
    'user': 'root',
    'password': 'root',
    'host': 'localhost',
    'port': 3306,
    'raise_on_warnings': True
}

for year in range(2015,2017 +1):
    # DB化するCSVファイルのパスとTable名(拡張子なしのファイル名)をリスト化する
    file_l = list()
    table_l = list()
    for file in glob.glob(f'/horse_racing/race_result/{year}/*/*/*.csv'):
        file_l.append(file)
        table_l.append(file[-23:-4])

    # CSVファイルの列タイトルをリストにする
    df = pd.read_csv(file_l[0], encoding='Shift-JIS',header = None)
    column_title = list(df.loc[0])

    column_type = ['varchar(10)','int(2)','int(2)','varchar(200)','varchar(200)','float','varchar(200)','varchar(200)','varchar(200)','varchar(200)','varchar(200)','varchar(10)','varchar(10)','varchar(10)','varchar(200)','varchar(200)','varchar(200)','varchar(200)','varchar(200)','varchar(200)','varchar(200)','varchar(13)','int(9)']

    # SQL文に使う列タイトルの文字列を作成する(角括弧を丸括弧に置換えるなど)
    column_l = list()
    for ti,ty in zip(column_title,column_type):
        column = ti + ' ' + str(ty)
        column_l.append(column)

    column_l_str = str(column_l).replace('[','(').replace(']',')').replace("'",'').replace('(万円)','')

    # sqlのリストを作成
    sql_l = list()
    for table in table_l:
        sql = f"create table horse_race_result.{table} {column_l_str}"
        sql_l.append(sql)

    # mysqlに接続
    conn = mysql.connector.connect(**config)
    cur = conn.cursor()

    # データベースhorse_race_resultにtableを作成する
    for file,table,sql in zip(file_l,table_l,sql_l):
        cur.execute(sql)
        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:
                    row_str = str(row).replace('[','(').replace(']',')')
                    print(row_str)
                    sql = f'insert into horse_race_result.{table} values {row_str}'
                    print(sql)
                    cur.execute(sql)

        cur.execute('commit')

    conn.close()