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

[Python] 241 tkinter 21 ウィンドウとフレームのclass化

[Python]240の続きです。

ウィンドウとフレームをclass化しました。

tkinterドキュメントの’A Simple Hello World Program’を参考に書き上げました。

class化については麻雀アプリでも扱っていたのでスムーズでした。

これですっきりとしたコードになりました。

import tkinter as tk
import tkinter.font as font
from tkinter import ttk

class Horse(tk.Tk):
    def __init__(self, master=None):
        super().__init__(master)
        self.title("HORSE SEARCH")
        self.geometry("310x130")
        self.configure(background = '#B0E0E6')
        self.create_menu()
        # Horseのグリッドを 1x1 にする
        self.grid_rowconfigure(0, weight=1)
        self.grid_columnconfigure(0, weight=1)

    def create_menu(self):
        menubar = tk.Menu(self)

        # 親メニューの設定
        menu_kinou = tk.Menu(menubar)
        menubar.add_cascade(label='機能', menu=menu_kinou)

        # 子メニューの設定
        menu_kinou.add_command(label='馬名検索', command=create_A)
        menu_kinou.add_separator()
        menu_kinou.add_command(label='レース検索', command=create_B)

        self.config(menu=menubar)

class FrameA(tk.Frame):
    def __init__(self, master=None):
        super().__init__(master)
        self.configure(background = '#B0E0E6')
        self.grid(row=0,column=0, sticky=tk.NSEW, padx=5, pady=10)
        self.create_widgets()

    def create_widgets(self):
        # ラベルの作成・配置
        label = tk.Label(self,text='馬名',background = '#B0E0E6',foreground = '#8b0000',font=my_font)
        label.grid(row=0, column=0)

        # パス入力エントリの作成・配置
        entry = tk.Entry(self,width=15,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entry.grid(row=0,column=1)

        # 実行ボタンの作成・配置
        btn = tk.Button(self, text="検索",command=lambda:var.set(1),width=2,font=my_font)
        btn.grid(row=0,column=2,padx=2)

        # クリアボタンの作成・配置
        btn2 = tk.Button(self, text="クリア",command=lambda:entry.delete(0,tk.END),width=2,font=my_font2)
        btn2.grid(row=1,column=2,padx=2)

class FrameB(tk.Frame):
    def __init__(self, master=None):
        super().__init__(master)
        self.configure(background = '#B0E0E6')
        self.grid(row=0,column=0, sticky=tk.NSEW, padx=5, pady=10)
        self.create_widgets()

    def create_widgets(self):
        # frameB2,frameB3の作成
        frameB2 = tk.Frame(self,background = '#B0E0E6')
        frameB2.grid(row=1,column=1, sticky=tk.NSEW, padx=5, pady=0)

        frameB3 = tk.Frame(self,background = '#B0E0E6')
        frameB3.grid(row=2,column=1, sticky=tk.NSEW, padx=5, pady=0)

        # frameBラベル1の作成・配置
        labelB = tk.Label(self,text='レース',background = '#B0E0E6',foreground = '#8b0000',font=my_font)
        labelB.grid(row=0, column=0)

        # frameBラベル2の作成・配置
        labelB2 = tk.Label(self,text='開始日',background = '#B0E0E6',foreground = '#8b0000',font=my_font2)
        labelB2.grid(row=1, column=1,sticky=tk.W)

        # frameBラベル3の作成・配置
        labelB3 = tk.Label(self,text='終了日',background = '#B0E0E6',foreground = '#8b0000',font=my_font2)
        labelB3.grid(row=2, column=1,sticky=tk.W)

        # frameBエントリ1の作成・配置
        entryB = tk.Entry(self,width=15,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entryB.grid(row=0,column=1)

        # frameBエントリ2の作成・配置
        entryB2 = tk.Entry(frameB2,width=8,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entryB2.pack(padx=2,side=tk.RIGHT)

        # frameBエントリ3の作成・配置
        entryB3 = tk.Entry(frameB3,width=8,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entryB3.pack(padx=2,side=tk.RIGHT)

        # frameB実行ボタンの作成・配置
        btnB = tk.Button(self, text="検索",command=lambda:var.set(2),width=2,font=my_font)
        btnB.grid(row=0,column=2,padx=2)

        # frameBクリアボタンの作成・配置
        btnB2 = tk.Button(self, text="クリア",command=lambda:entryB.delete(0,tk.END),width=2,font=my_font)
        btnB2.grid(row=1,column=2,padx=2)

def create_A():
    global frame

    frame.destroy
    children[-3].delete(0,tk.END)
    frame = FrameA(master=horse)

def create_B():
    global frame
    
    frame.destroy
    children[1].delete(0,tk.END)
    frame = FrameB(master=horse)

# ウィンドウの作成
horse = Horse(master=None)

# フォント設定
my_font = font.Font(horse,family="System",size=18,weight="normal")
my_font2 = font.Font(horse,family="System",size=16,weight="normal")

# フレームの作成
frame = FrameA(master=horse)

# IntVarの初期化
var = tk.IntVar()

for i in range(1000):
    print(f'var for文先頭 {var.get()}')

    children = frame.winfo_children()
    print(f'children {children}')

    if var.get() == 0 or var.get() == 1:
        print('分岐A')
        # 馬名の入力を待機
        children[2].wait_variable(var)

        # 入力した馬名を取得
        name = children[1].get()
        print(f'name {name}')
        
        # 馬名検索モジュールは省略

    else:
        print('分岐B')
        children = frame.winfo_children()
        print(f'elseウィジェットinfo\n{children}\n')

        # レース名の入力を待機
        children[-2].wait_variable(var)

        # 入力したレース名を取得
        race = children[-3].get()
        print(f'race {race}')

        # レース検索モジュールは省略

frame.mainloop()

[Python] 240 tkinter 20 画面遷移の繰り返し対応

[Python] 239の続きです。

別フレームの作成や元フレームの消去などで画面を遷移させるのは容易ですが、画面内への入力内容を取得するのにかなり苦労しました。

結局Widget変数IntVarを利用して解決しました。具体的にはIntVarが0または1であれば画面A、2であれば画面Bというif文を作成しました。

if文から抜け出すために検索ボタンを2回クリックする必要がありますが、この方法であればどう画面遷移しても固まらずに対応できます。ただし画面Aと画面Bでエントリの位置が異なることが条件になります。

ネットには日本語・英語共にピッタリな情報は見あたらず、一晩考えてようやくアイデアが浮かびました。

いつものことですが、出来上がってみると実にシンプルな内容です。

# frameBを消去・frameAを作成、frameAを消去・frameBを作成する関数を設定する
# 上記関数とメニューの'馬名検索','レース検索'をそれぞれ連携させる

<中略>

# 親メニューの設定
menu_kinou = tk.Menu(menubar)
menubar.add_cascade(label='機能', menu=menu_kinou)

# 子メニューの設定
menu_kinou.add_command(label='馬名検索', command=create_A)
menu_kinou.add_separator()
menu_kinou.add_command(label='レース検索', command=create_B)

<中略>

# 初期画面・実行ボタンの作成・配置
var = tk.IntVar()
btn = tk.Button(frame, text="検索",command=lambda:var.set(1),width=2,font=my_font)
btn.grid(row=0,column=2,padx=2)

<中略>

for i in range(1000):
    print(f'var for文先頭 {var.get()}')
    try:
        children = frame.winfo_children()
    except:
        children = frameB.winfo_children()

    if var.get() == 0 or var.get() == 1:
        print('分岐A')
        # 馬名の入力を待機
        btn.wait_variable(var)

        # 入力した馬名を取得
        try:
            name = children[1].get()
        except:
            pass
        else:
            print(f'name {name}')
            # 実際は<馬名検索モジュール>

    else:
        print(f'分岐B')
        children = frameB.winfo_children()
  
        # レース名の入力を待機
        children[-2].wait_variable(var)

        # 入力したレース名を取得
        try:
            race = children[-3].get()
        except:
            pass
        else:
            print(f'race {race}')
            # 実際は<レース検索モジュール>

horse.mainloop()
--------------------------------------------------

出力
--------------------------------------------------
var for文先頭 0
分岐A
name サリオス
var for文先頭 1
分岐A
name クロノジェネシス
var for文先頭 1
分岐A
var for文先頭 2
分岐B
race 有馬記念
var for文先頭 2
分岐B
var for文先頭 1
分岐A

[Python] 239 tkinter 19 メニューの設定と画面の切り替え

引っ越ししてからは初のtkinterネタです。

MySQLツールにメニューバーを設置し、馬名検索とレース検索で画面を切り替えるようにしました。

Macの場合はメニューバーはディスプレイ上端に表示されるので注意が必要です。

# rootの設定
root = tk.Tk()

# メニューバーの設定
menubar = tk.Menu(root)

# 親メニューの設定
menu_kinou = tk.Menu(menubar)
menubar.add_cascade(label='機能', menu=menu_kinou)

# 子メニューの設定
menu_kinou.add_command(label='馬名検索', command=lambda:frame.tkraise())
menu_kinou.add_separator()
menu_kinou.add_command(label='レース検索', command=lambda:frameB.tkraise())

root.config(menu=menubar)

<中略>

# frameBの作成・配置
frameB = tk.Frame(root,background = '#B0E0E6')
frameB.grid(row=0,column=0, sticky=tk.NSEW, padx=5, pady=10)

<中略>

# frameを前面にする
frame.tkraise()

[Python] 238 tkinter 18 MySQLツールのGUI化

MySQLツールの土台ができたので早速GUI化しました。

いつものガワを使い回しです。

今は競走馬成績検索のみですが、少しずつ拡充していきます。

次は過去レース検索あたりでしょうか。

import glob,os,shutil
import sys,json
import tkinter as tk
import tkinter.font as font
from tkinter import ttk
from HR_library import mysql_search # [Python]237のコードをモジュール化

<中略>

for i in range(100): # 100回処理可能
    # 馬名の入力を待機
    btn.wait_variable(var_act)

    # 入力した馬名を取得
    name = entry.get()

    # 競走馬成績を検索
    mysql_search.Mysql_search().horse_result(name)

root.mainloop()

[Python] DB仮完成までの道のり

昨年の今頃は主要都道府県のコロナ集計データをスクレイピングしていました。サイトの形式が自治体によってバラバラかつ頻繁にXPathが変わるのでコード管理が大変でした。今はYahoo!のトップに特集サイトがあるのでそれを利用しています。

プログラミング未経験者が2018年春にスクレイピングにいきなり挑戦して玉砕。一昨年12月にホビープログラマとなり、2020年3月から本格的にスクレイピングに取り組み始めて関係ないコードも色々書きながらここまで1年ちょっと掛かりました。感慨深いものがあります。

これまで何冊か関連本を購入しましたが積ん読状態でスクールも利用せず、ほとんど開発ドキュメントやネット情報だけでここまで来ました。

プログラミング学習を通して、自分が座学やビデオ・書籍を使った学習に向いていないことが改めて良くわかりました。

何かを知りたいと思った時にすぐに関連情報を収集し、自分なりに仮説を立て検証を繰り返すというやり方が向いているようです。

実践を通して経験値を増やし、後から体系化していく感じでしょうか。とにかく能動的に学習しないと身につかないです。

理論科学よりも実験科学が好きなのも、そういった性格によるものでしょう。

[Python] 237 MySQL 05 馬名からレース検索・CSV出力

馬名からのhorseID検索、raceID検索、レース名検索を全てMySQLで実行しました。

結果出力までたった1秒の高速処理でかなり快適です。

CSVファイルは開けたときの見栄えが今ひとつなのでHTML化も考えています。

import mysql.connector,glob,csv,re,datetime,sys

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

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

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

horseID_l = list()
for y in range(1986,2019 +1):
	table = f'horse{y}'
	try:
		cur.execute(f'SELECT horseID FROM horse_list.{table} WHERE 検索馬名 = "{name}"')
	finally:
		result = cur.fetchall()
		if result != []:
			horseID_l.append(result)

print(horseID_l)

if len(horseID_l) >= 2:
    print('該当する馬が複数います。番号を入力してください。')
    for i,data in enumerate(horseID_l):
        print(f'{i+1} {data}')
    num = input()
    id = str(horseID_l[int(num)-1][0]).replace('(','').replace(')','').replace(',','')

elif len(horseID_l) == 0:
    print('該当する馬はいません')
    sys.exit()

else:
    id = str(horseID_l[0][0]).replace('(','').replace(')','').replace(',','')

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

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

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

	try:
		cur.execute(f'SELECT 着順,枠番,馬番,斤量,騎手,タイム,通過,上り,単勝,人気,馬体重,賞金,raceID FROM horse_race_result.{table} WHERE horseID = {id}')
	finally:
		result = cur.fetchall()
		# print(result)
		if result != []:
			data_l_pre.append(result)

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

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

raceID_l = [l[-1] for l in data_l]
print(raceID_l)

data2_l_pre = list()
for id in raceID_l:
    table = f'race_n_{id[1:7]}'
    try:
        cur.execute(f'SELECT 日付,開催,レース,レース名,コース,天候,馬場状態 FROM horse_race_name.{table} WHERE raceID = "{id}"')
    finally:
        result = cur.fetchall()
        if result != []:
            data2_l_pre.append(result)

# 接続終了
cur.close()
conn.close()

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

print(f'data2_lデータ数 {len(data2_l)}')
print(data2_l)

# ソート用日付データの作成
date_l = list()
for d in data2_l:
    date = d[0]
    date_dt = datetime.datetime.strptime(date,'%Y年%m月%d日')
    date_d = datetime.date(date_dt.year, date_dt.month, date_dt.day)
    date_l.append(str(date_d))

print(f'date_lデータ数 {len(date_l)}')
print(date_l)

# レースデータ、結果データ、日付データを結合する
data3_l = list()
for a,b,c in zip(data2_l,data_l,date_l):
    d = list(a) + list(b) + [c]
    data3_l.append(d)

print(f'data3_lデータ数 {len(data3_l)}')
print(data3_l)

# 日付でソートする
data4_l = sorted(data3_l, key=lambda x: x[-1])
print(f'data4_lデータ数 {len(data4_l)}')
print(data4_l)

# 列タイトルを追加する
column_title = [['日付','開催','レース','レース名','コース','天候','馬場状態','着順','枠番','馬番','斤量','騎手','タイム','通過','上り','単勝','人気','馬体重','賞金','raceID','ソート用日付']]
data5_l = column_title + data4_l
print(f'data5_lデータ数 {len(data5_l)}')
print(data5_l)

# 結果ファイルの作成
datetime_now = datetime.datetime.now()
datetime_now_str = datetime_now.strftime('%y%m%d%H%M')
filename = f"/Desktop/{datetime_now_str}_mysql.csv"

with open(filename, 'w', newline='',encoding = 'shift_jis') as f:
    writer = csv.writer(f)
    writer.writerows(data5_l)