今回は「画像処理を用いてハチナイの打撃成績を取得してみた」で取得した成績をデータベースに追加できるようにして，データベースで成績を管理したいと思います．

追記（2017/09/08）

プリンセス杯で集計したデータをブログで公開してます．
ハチナイ成績日記

目標

成績に選手名や守備位置などの情報を付加する．
選手情報と成績をデータベース(SQLite3)に追加する．
SQLite3用ソフトウェア(PupSQLite)を使って成績を管理する．

環境

使用言語

Python3.5
##使用したライブラリ

OpenCV
PyQt5
Pandas
sqlite3

ソースコード


# -*- coding: utf-8 -*-

import sys
import cv2
import numpy as np
import PyQt5.QtCore as QtCore
import PyQt5.QtGui as QtGui
import PyQt5.QtWidgets as QtWidgets
import pandas as pd
import json
import sqlite3

#OpenCVの画像をPyQtで表示できるように変換
#こちらのソースコードを利用
#http://qiita.com/odaman68000/items/c8c4093c784bff43d319
def create_QPixmap(image):
    qimage = QtGui.QImage(image.data, image.shape[1], image.shape[0], image.shape[1] * image.shape[2], QtGui.QImage.Format_RGB888)
    pixmap = QtGui.QPixmap.fromImage(qimage)
    return pixmap

#テンプレートマッチングの実行
def matching(img,num,threshold,img_res,cell_y,cell_x):
    template = cv2.imread('./template/number/{}.png'.format(num),0)
    template = template[6:-6,:]
    w, h = template.shape[::-1]

    res = cv2.matchTemplate(img,template,cv2.TM_CCOEFF_NORMED)
    loc = np.where( res >= threshold)
    res_loc = []
    for pt in zip(*loc[::-1]):
        #重複して検出されたものを除外
        flag=True
        for pt2 in res_loc:
            if pt2[0] + w > pt[0]:
                flag = False
        if flag:
            res_loc.append(pt)
            #元の画像に検出した数字と枠を描画
            cv2.rectangle(img_res, (pt[0]+cell_x, pt[1]+cell_y), (pt[0]+cell_x+w, pt[1]+cell_y+h), (0,0,255), 2)
            n = "-" if num == "mai" else num
            cv2.putText(img_res, str(n), (pt[0]+cell_x,pt[1]+cell_y), cv2.FONT_HERSHEY_SIMPLEX, 1, (0, 0, 0), 3)
    return res_loc

#画像をドロップした際に開くウィンドウ
class Add_widget(QtWidgets.QDialog):

    def __init__(self,frame,clipboard,parent=None):
        super(Add_widget, self).__init__(parent)
        self.initUI(frame,clipboard,parent)

    def initUI(self,frame,clipboard,parent):
        self.lbl = QtWidgets.QLabel()
        self.frame = frame

        self.datatable = QtWidgets.QTableWidget()
        self.datatable.setColumnCount(9+6)
        self.datatable.setRowCount(9)
        
        self.spinlbl = QtWidgets.QLabel("threshold")
        self.spinbox = QtWidgets.QDoubleSpinBox()
        self.spinbox.setRange(0,1)
        self.spinbox.setSingleStep(0.01)
        self.spinbox.setValue(0.90)
        self.spinbox.valueChanged.connect(self.get_result)
        self.sbin_hbox = QtWidgets.QHBoxLayout()
        self.sbin_hbox.addWidget(self.spinlbl)
        self.sbin_hbox.addWidget(self.spinbox)
        self.sbin_hbox.addStretch(1)

        # self.button = QtWidgets.QPushButton("copy to clipboard")
        # self.button.clicked.connect(self.copy_to_clipboard)

        self.button = QtWidgets.QPushButton("データベースに追加")
        self.button.clicked.connect(self.add_database)

        self.vbox = QtWidgets.QVBoxLayout()
        self.vbox.addWidget(self.lbl)
        self.vbox.addWidget(self.datatable)
        self.vbox.addLayout(self.sbin_hbox)
        self.vbox.addWidget(self.button)
        self.setLayout(self.vbox)
        self.setWindowTitle('result')
        self.clipboard = clipboard

        self.get_result()

    #テーブルを取得した成績で更新
    def update_table(self,df):
        for i in range(len(df.index)):
            for j in range(len(df.columns)):
                self.datatable.setItem(i,j,QtWidgets.QTableWidgetItem(str(df.get_value(i, j))))
    
    #調子の識別や数字の検出を行う
    def detection_value(self,frame,threshold):

        try:
            f = open("player.json", 'r')
            player_data = json.load(f)
        except UnicodeDecodeError:
            f = open("player.json", 'r', encoding='utf-8')
            player_data = json.load(f)

        img_res = frame.copy()
        img_gray = cv2.cvtColor(img_res, cv2.COLOR_BGR2GRAY)
        
        df = pd.DataFrame()
        li=[0,2,3,2,2,3,2,3,2]

        #1行ごとに成績を取得
        for row in range(9):
            player_list = []

            player_list.append(player_data["date"])
            player_list.append(player_data["opponent"])

            player_list.append(player_data["player{}".format(row+1)]["scene"])
            player_list.append(player_data["player{}".format(row+1)]["name"])
            player_list.append(player_data["player{}".format(row+1)]["position"])
            #打順
            player_list.append(row+1)
            player_list.append(player_data["team_buff"])

            #調子の識別
            condi_cell = frame[210+sum(li[:row+1])+(84*(row)):210+sum(li[:row+1])+(84*(row+1)),687:758]
            condi_list = np.zeros(5)

            for i in range(5):
                condi = cv2.imread("./template/condition/{}.png".format(i))
                #差分値を計算
                sad = np.sum(np.abs(condi_cell.astype(np.float32) - condi.astype(np.float32)))
                condi_list[i] = sad
            #1番差分が小さい画像を選択
            c = np.argmin(condi_list)
            player_list.append(c+1)
            cv2.putText(img_res, str(c+1), (687, 210+sum(li[:row+1])+(84*(row+1))), cv2.FONT_HERSHEY_PLAIN, 4, (0, 0, 0), 5)

            #列ごとに分割
            for col in range(8):
                cell_y = 210+sum(li[:row+1])+(84*(row))
                cell_width = 105 if col < 7 else 128
                cell_x = 759+col*105
                img_cell = img_gray[cell_y:cell_y+84,cell_x:cell_x+cell_width]
                list_num = []

                #0~9までテンプレートマッチングを行う
                for num in range(10):
                    loc = matching(img_cell,num,threshold,img_res,cell_y,cell_x)
                    for pt in loc:
                        list_num.append([num,pt[0],pt[1]])

                #x座標でソートする
                list_num.sort(key=lambda x:(x[1]))   

                #x座標でソートした数字を連結する
                s = ""
                for i in range(len(list_num)):
                    #打率の場合は頭に"0."を付ける
                    if col == 6 and i == 0:
                        s += "0."
                    s += "{}".format(list_num[i][0])
                    #RCの場合は1番目の数字の後に"."を付ける（RCが二桁になることはほぼないという前提）
                    if col == 7 and i == 0:
                        s += "."
                #連結した打率が最終的に"0.100"になった場合，"1.00"に変える（1試合で10打数1安打はないという前提）
                if col == 6 and s == "0.100":
                    s = "1.00"
                #数字を検出できなかった場合，-10000にする
                try:
                    res_num = float(s)
                except ValueError:
                    res_num = -10000.0
                #RC検出時はマイナスについてテンプレートマッチングを行い，マイナスがあればー1倍する
                if col == 7:
                    loc = matching(img_cell,"mai",threshold,img_res,cell_y,cell_x)
                    if len(loc) > 0:
                        res_num *= -1
                player_list.append(res_num)
            #pandasを用いて1行ずつ成績を追加
            se = pd.Series(player_list)
            df = df.append(se, ignore_index=True)

        self.df = df
        return img_res
    
    #テーブルの中身をクリップボードにコピーする
    def copy_to_clipboard(self):
        s = ""
        for r in range(self.datatable.rowCount()):
            for c in range(self.datatable.columnCount()):
                try:
                    s += str(self.datatable.item(r,c).text()) + "\t"
                except AttributeError:
                    s += "\t"
            s = s[:-1] + "\n"
        self.clipboard.setText(s)

    #データベースに追加
    def add_database(self):
        try:
            db_name = "hachinai.db"
            con = sqlite3.connect(db_name)
            for i in range(9):
                con.execute("insert into results("\
                            + "date,"\
                            + "opponent,"\
                            + "scene,"\
                            + "name,"\
                            + "position,"\
                            + "batting_order,"\
                            + "team_buff,"\
                            + "condition,"\
                            + "at_bat,"\
                            + "hit,"\
                            + "homerun,"\
                            + "RBI,"\
                            + "BB,"
                            + "base_hit,"\
                            + "batting_average,"\
                            + "RC"\
                            + ")"\
                            + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",tuple(self.df.ix[i]))
            con.commit()
            con.close()
            self.accept()
        except sqlite3.OperationalError:
            w = Message_Widget()
            w.exec_()

    #成績を取得する
    def get_result(self):
        img_res = self.detection_value(self.frame,self.spinbox.value())
        self.update_table(self.df)

        img_res = cv2.cvtColor(img_res, cv2.COLOR_BGR2RGB)
        img_res = cv2.resize(img_res, (1280,720))
        qt_img = create_QPixmap(img_res)
        self.lbl.setPixmap(qt_img)

    def show(self):
        self.exec_()

#エラーメッセージ表示
class Message_Widget(QtWidgets.QMessageBox):
    def __init__(self,parent=None):
        super(Message_Widget, self).__init__(parent)
        self.initUI(parent)

    def initUI(self,parent):
        self.setText("データベースが存在しません")
        self.setIcon(QtWidgets.QMessageBox.Warning)
        self.setStandardButtons(QtWidgets.QMessageBox.Close)

#ドラッグアンドドロップに対応したQLabelクラス
class DropLabel(QtWidgets.QLabel):
    def __init__(self,parent):
        super().__init__(parent)
        self.parent = parent
        self.setAcceptDrops(True)
        self.setAlignment(QtCore.Qt.AlignCenter);
        self.setText("Drop here.")

    def dragEnterEvent(self, e):
            e.accept()

    def dropEvent(self, e):
        mimeData = e.mimeData()
        files = [u.toLocalFile() for u in mimeData.urls()]
        for f in files:
            print("loading {}".format(f))
            #ドロップされた画像を読み込み
            frame = cv2.imread(f)
            #読み込みに失敗した場合は処理を行わない
            if frame is not None:
                frame = cv2.resize(frame, self.parent.size)
                add_widget = Add_widget(frame,self.parent.clipboard,self)
                add_widget.show()

#画像をドロップするウィンドウ
class Hachinai_widget(QtWidgets.QWidget):

    def __init__(self,clipboard=None,parent=None):
        super(Hachinai_widget, self).__init__(parent)
        super().__init__()

        self.initUI(clipboard,parent)

    def initUI(self,clipboard,parent):
        self.parent=parent
        self.height = 1080
        self.width = 1920
        self.size = (self.width,self.height)
        self.clipboard = clipboard

        self.lbl = DropLabel(self)
        self.lbl.setMinimumSize(640,480)
        self.lbl.setFrameStyle(QtWidgets.QFrame.Box | QtWidgets.QFrame.Plain)

        self.vbox = QtWidgets.QVBoxLayout()
        self.vbox.addWidget(self.lbl)
        self.setLayout(self.vbox)
        self.setWindowTitle('hachinai')
        self.show()
        

if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    clipboard = app.clipboard()
    screen = Hachinai_widget(clipboard)
    sys.exit(app.exec_())

プログラム実行準備

テーブルの作成

上記のプログラムを実行する前に次のプログラムを実行して，成績を保存するresultsテーブルを作成します．このテーブルはhachinai.dbというファイルに保存されます．


# -*- coding: utf-8 -*-

import sqlite3

db_name = "hachinai.db"
con = sqlite3.connect(db_name)

#テーブルの存在確認
cur = con.execute("SELECT * FROM sqlite_master WHERE type='table' and name='results'")
if cur.fetchone() == None: #存在してないので作る
    con.execute("CREATE TABLE results("\
                + "id INTEGER PRIMARY KEY,"\
                + "date DATE,"\
                + "opponent TEXT,"\
                + "scene TEXT,"\
                + "name TEXT,"\
                + "position TEXT,"\
                + "batting_order INTEGER,"\
                + "team_buff TEXT,"\
                + "condition INTEGER,"\
                + "at_bat INTEGER,"\
                + "hit INTEGER,"\
                + "homerun INTEGER,"\
                + "RBI INTEGER,"\
                + "BB INTEGER,"\
                + "base_hit INTEGER,"\
                + "batting_average DOUBLE,"\
                + "RC DOUBLE)"\
                )
    con.commit()
con.close()

##選手情報の記述

今回はplayer.jsonというファイルに選手情報を記述し，プログラム上で読み込むことで成績に付加しています．

player.json

{
    "date":"2017-11-06",
    "opponent":"シンデレラマッチ",

    "team_buff":"0",

    "player1":{
        "scene":"夢のスタート地点",
        "name":"有原 翼",
        "position":"6"
    },

    "player2":{
        "scene":"同じ空の下",
        "name":"秋乃 小麦",
        "position":"3"
    },

    "player3":{
        "scene":"猪突猛進ダッシュ!",
        "name":"竹富 亜矢",
        "position":"8"
    },

    "player4":{
        "scene":"ここが勝負どころ!",
        "name":"河北 智恵",
        "position":"4"
    },

    "player5":{
        "scene":"もっと目立ちたいのッ!",
        "name":"逢坂 ここ",
        "position":"9"
    },

    "player6":{
        "scene":"豪快試し打ち",
        "name":"岩城 良美",
        "position":"5"
    },

    "player7":{
        "scene":"入念なストレッチ",
        "name":"柊 琴葉",
        "position":"7"
    },

    "player8":{
        "scene":"綿密な作戦",
        "name":"鈴木 和香",
        "position":"2"
    },

    "player9":{
        "scene":"一球入魂!",
        "name":"野崎 夕姫",
        "position":"1"
    }
}

「_2」は被りキャラです．メインに比べて育成が雑です．

プログラムの実行

プログラムを実行すると，このようなウィンドウが表示されます．

画像をドロップすると成績を取得します．このとき，player.jsonから選手情報を読み込んでいます．

ここで，「データベースに追加」をクリックすると先ほど作成したテーブルにデータを追加して成績ウィンドウを閉じます．

成績の管理

PupSQLiteを実行してhachinai.dbを開きます．そして，Tablesにあるresultsをダブルクリックすると次のように保存した成績が表示されます．

「SQL文入力」->「クエリウィンドウを開く」をクリックするとSQL入力画面が表示されます．ここで，以下のようなSQL文を入力して実行すると選手の通算成績を表示できます．

SELECT シーン,  名前, 守備位置, 調子, round((安打*1.0) / (打数*1.0), 3) as 打率 , 試合数, 打席, 打数, 安打, 本塁打, 打点, 四球, 塁打, round(((安打 + 四球)*1.0) / (打席*1.0), 3) as 出塁率 , round((塁打*1.0) / (打数*1.0), 3) as 長打率 ,  round(((安打 + 四球)*1.0) / (打席*1.0) + (塁打*1.0) / (打数*1.0), 3) as OPS, RC  from ( 
SELECT scene as シーン,  name as 名前,  position as 守備位置, round(avg(condition), 2) as 調子, count(scene) as 試合数, sum(at_bat) as 打席, sum(at_bat) - sum(BB) as 打数,  sum(hit) as 安打, sum(homerun) as 本塁打, sum(RBI) as 打点, sum(BB) as 四球, sum(base_hit) as 塁打, round(avg(RC), 2) as RC FROM results GROUP BY scene ) ;results GROUP BY scene ) ;

また，列名をクリックするとソートできます．

データを見ると，調子のいい選手が良い成績を出す傾向があるような感じがします．九十九加奈の打率がやたら高いのはミートを2回限界突破しているためだと思います．新田美奈子の長打率が高い理由はよくわからないです．

さらに，SQL文を工夫することで様々なデータが出せます．例えば，以下のようにSQL文を変えると調子ごとに通算成績を出せます．

SELECT シーン,  名前, 守備位置, 調子, round((安打*1.0) / (打数*1.0), 3) as 打率 , 試合数, 打席, 打数, 安打, 本塁打, 打点, 四球, 塁打, round(((安打 + 四球)*1.0) / (打席*1.0), 3) as 出塁率 , round((塁打*1.0) / (打数*1.0), 3) as 長打率 ,  round(((安打 + 四球)*1.0) / (打席*1.0) + (塁打*1.0) / (打数*1.0), 3) as OPS, RC  from ( 
SELECT scene as シーン,  name as 名前,  position as 守備位置, round(avg(condition), 2) as 調子, count(scene) as 試合数, sum(at_bat) as 打席, sum(at_bat) - sum(BB) as 打数,  sum(hit) as 安打, sum(homerun) as 本塁打, sum(RBI) as 打点, sum(BB) as 四球, sum(base_hit) as 塁打, round(avg(RC), 2) as RC FROM results WHERE scene = 'ちょっと一息' GROUP BY condition ) ;

おわりに

いちいち成績をエクセルにコピペする手間が無くなったのでデータ管理がかなり楽になりました．近々開催されるであろうランキングマッチでデータを集めたいと思います．