審判ファンの文系SEがFlaskでプロ野球審判出場記録アプリを作る②【Python】【sqlite3】

2022年8月29日 22:34

出場試合数、どうやって取得する？

ここ一週間はずっとSQL文と格闘していました。分かったことを忘れないようにnoteにまとめようと思います。

今まで勘違いしていたのですが、オールスターやプレシーズンは出場試合数には入らないのだと思っていました。全部込み込みのようです。昨年に書いた記事のコードが本当に冗長で本当にひどいのでこんなに進化したんだぜ！という記事も書きたいわけなのですが、そんなこんなでSeleniumで取得してきたデータをsqlite3にこのように格納しています。

今悩んでいるのは日本シリーズやCSとか何回出てます！っていう何回をどうやって取得するかということ

2021年の出場審判記録。game(year)でそれぞれ2022年から2018年まで格納。

#year、umpire_nameは変数。

      "select count(*) from game"　+ year + " where '" + umpire_name
        + "' IN (球審,一塁,二塁,三塁,線審（左）,線審（右）)"

2021年のテーブルでカラム名が球審～線審までの中にある審判員名をそれぞれCOUNTさせれば試合数は取得できそうです。

引退していく人や同姓をどうするか問題

最初はプロフィールも取得しているので名字と名前で分けて名字の列をリストに追加してループさせればいけんじゃね？と思っていたのですが、

山本力と山本貴が、、、山本なので、、、引っかからないのでだめだとなったり、2021年だと杉永、橋本、2020年だと佐々木、橋本とか、、、いらっしゃるので、プロフィールから引っ張るのは使えないのです。小林和さんも今でこそ小林ですがいつ小林毅や小林達がきて小林和だったり小林だったりしますし、、、そんな数はないので楽は楽ですが毎年、出場登録名の列を作ってあげなきゃいけないのか？もっとスマートなやり方があるのではないかと模索した結果、

「塁審はみんなやっているだろうから列の重複を削除してリストに入れればいいのでは？」

という方法に至りました。GROUP BYとHAVING句の出番です。

 "select 一塁 from game" + year + " group by 一塁 having count(一塁) > 1 "
)

初出場とか考えたら三塁でGROUP BYした方がいい気がしてきた、、、一塁の列にあるデータをグループ化してHAVING COUNTで一塁に1つ以上値があるデータをSELECTしていきます。

printした結果
[('丹波',), ('原',), ('吉本',), ('名幸',), ('土山',), ('小林',), ('山口',), ('山本貴',), ('山村',), ('山路',), ('岩下',), ('嶋田',), ('川口',), ('市川',), ('敷田',), ('有隅',), ('木内',), ('本田',), ('杉永',), ('村山',), ('栁田',), ('梅木',), ('森',), ('橋本',), ('橘髙',), ('水口',), ('津川',), ('深谷',), ('牧田',), ('白井',), ('眞鍋',), ('石山',), ('福家',), ('秋村',), ('笠原',), ('芦原',), ('西本',), ('長井',), ('長川',), ('青木',), ('須山',), ('飯塚',)]

2021年に一軍に出場した審判員名を取り出すことができました。(’’)が邪魔なので関数で消しちゃいます。

#いらない記号を削除する関数
def str_henkan(moji):
    return str(moji)[2:-3]

for文でループさせてselect_ump_listに追加していきます。

SELECT文をループ処理するときの落とし穴

各年の審判員名が取得できたので今度は各審判員の試合数を取り出します。ここまでくればSELECT文をループさせりゃええじゃん！と楽観視していましたがSQLはそんなに甘くありませんでした。

ここでも軽く沼ったのですが、結論から申すとデータベースに接続する際に使うコネクションでexecuteするのではなくカーソルを使ってexecuteすることで解決しました。調べてみるとカーソルはデータを一行ずつ取得することができるみたいです。

import sqlite3
DATABASE = umpire_game(データベース名)
con = sqlite3.connect(DATABASE)
curs = con.cursor()

for i in select_ump_list:
    curs.execute( "select count(*) from game" + year  + " where '"  + i  + "' IN 
    (球審,一塁,二塁,三塁,線審（左）,線審（右）)"    )
    
#fetchallでSQLで取得したデータをPythonで扱えるようリストに変換
    docs = curs.fetchall()

#関数でいらない記号を取り除きつつループして試合数をループで追加
    for doc in docs:      
　　　 umpire_game_numner.append(int_henkan(doc))
　　　 print(umpire_game_numner)

printした結果
[100, 90, 107, 100, 100, 95, 82, 77, 40, 103, 54, 98, 95, 88, 91, 103, 86, 99, 69, 88, 98, 51, 93, 96, 76, 15, 96, 103, 103, 102, 97, 101, 102, 96, 93, 91, 96, 82, 34, 23, 55, 72]

各審判の出場試合数を取得することができました！審判員名と試合数が別々のリストに入っているので扱いやすいように辞書型に追加します。やっと最近になって辞書型の便利さを理解しました。「丹波」って検索したら「100」って値が出てくるのですから超便利！！

# 辞書型の宣言
umpire_dic = {}
# キー(審判員名)と値(出場試合数)をループで辞書型に追加
for k in range(len(select_ump_list)): 
　umpire_dic[select_ump_list[k]] = umpire_game_numner[k]

printした結果
{'丹波': 100, '原': 90, '吉本': 107, '名幸': 100, '土山': 100, '小林': 95, '山口': 82, '山本貴': 77, '山村': 40, '山路': 103, '岩下': 54, '嶋田': 98, '川口': 95, '市川': 88, '敷田': 91, '有隅': 103, '木内': 86, '本田': 99, '杉永': 69, '村山': 88, '栁田': 98, '梅木': 51, '森': 93, '橋本': 96, '橘髙': 76, '水口': 15, '津川': 96, '深谷': 103, '牧田': 103, '白井': 102, '眞鍋': 97, '石山': 101, '福家': 102, ' 秋村': 96, '笠原': 93, '芦原': 91, '西本': 96, '長井': 82, '長川': 34, '青木': 23, '須山': 55, '飯塚': 72}

やりましたー！！！！データベースから各審判員の出場試合数を引き出すことができました。あとはポジション別、球団別の出場試合数や試合時間の平均なども求めていきたいです。SQL文は難しいですが、「データベースは所詮ただの箱」と語っていた会社の先輩の一言に尽きるんだなと思いました。ほしいデータをどう引き出すかの方が大事でした。

Flask側のコードが脚注なしで書かれていて分けわかんなくなってきているので整理しつつHTMLの構成を練ります。