Hachinai's grade management using SQL
This time, I made it possible to add the results obtained in "I tried to get the batting results of Hachinai using image processing" to the database, and in the database I want to manage my grades.
Postscript (2017/09/08)
The data collected at the Princess Cup is published on the blog. Hachinai Grade Diary
Target
--Add information such as player name and defensive position to the results. --Add player information and grades to the database (SQLite3). --Manage grades using SQLite3 software (PupSQLite).
environment
Language used
Python3.5
Library used
- OpenCV
- PyQt5
- Pandas
- sqlite3
Source code
# -*- 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
#Convert OpenCV images so that they can be displayed in PyQt
#Use this source code
#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
#Perform template matching
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]):
#Exclude duplicates detected
flag=True
for pt2 in res_loc:
if pt2[0] + w > pt[0]:
flag = False
if flag:
res_loc.append(pt)
#Draw the detected numbers and frame on the original image
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
#The window that opens when you drop an image
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("Add to database")
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()
#Update with the grades obtained from the table
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))))
#Identify tone and detect numbers
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]
#Get grades line by line
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"])
#Batting order
player_list.append(row+1)
player_list.append(player_data["team_buff"])
#Identification of tone
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))
#Calculate the difference value
sad = np.sum(np.abs(condi_cell.astype(np.float32) - condi.astype(np.float32)))
condi_list[i] = sad
#Select the image with the smallest difference
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)
#Split by column
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~Perform template matching up to 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]])
#Sort by x coordinate
list_num.sort(key=lambda x:(x[1]))
#Concatenate numbers sorted by x coordinate
s = ""
for i in range(len(list_num)):
#In the case of batting average"0."Attach
if col == 6 and i == 0:
s += "0."
s += "{}".format(list_num[i][0])
#For RC, after the first number"."(Assuming that RC is rarely double digit)
if col == 7 and i == 0:
s += "."
#The connected batting average is finally"0.100"If it becomes"1.00"(Assuming that there is no 1 hit in 10 at bats in one game)
if col == 6 and s == "0.100":
s = "1.00"
#If the number cannot be detected-Set to 10000
try:
res_num = float(s)
except ValueError:
res_num = -10000.0
#When RC is detected, template matching is performed for minus, and if there is minus, it is multiplied by -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)
#Add grades line by line using pandas
se = pd.Series(player_list)
df = df.append(se, ignore_index=True)
self.df = df
return img_res
#Copy the contents of the table to the clipboard
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)
#Add to database
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_()
#Get grades
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_()
#Error message display
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("Database does not exist")
self.setIcon(QtWidgets.QMessageBox.Warning)
self.setStandardButtons(QtWidgets.QMessageBox.Close)
#QLabel class for drag and drop
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))
#Load the dropped image
frame = cv2.imread(f)
#If reading fails, no processing is performed
if frame is not None:
frame = cv2.resize(frame, self.parent.size)
add_widget = Add_widget(frame,self.parent.clipboard,self)
add_widget.show()
#Window to drop image
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_())
Program execution preparation
Creating a table
Before running the above program, run the following program to create a results table that stores your grades. This table is saved in a file called hachinai.db.
# -*- coding: utf-8 -*-
import sqlite3
db_name = "hachinai.db"
con = sqlite3.connect(db_name)
#Confirmation of table existence
cur = con.execute("SELECT * FROM sqlite_master WHERE type='table' and name='results'")
if cur.fetchone() == None: #Make it because it doesn't exist
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()
Description of player information
This time, player information is described in a file called player.json and added to the grade by reading it in the program.
player.json
{
"date":"2017-11-06",
"opponent":"Cinderella match",
"team_buff":"0",
"player1":{
"scene":"Dream starting point",
"name":"Arihara Tsubasa",
"position":"6"
},
"player2":{
"scene":"Under the same sky",
"name":"Akino wheat",
"position":"3"
},
"player3":{
"scene":"Rushing dash!",
"name":"Aya Taketomi",
"position":"8"
},
"player4":{
"scene":"This is the place to play!",
"name":"Chie Hebei",
"position":"4"
},
"player5":{
"scene":"I want to stand out more!",
"name":"Osaka here",
"position":"9"
},
"player6":{
"scene":"Exciting trial hit",
"name":"Yoshimi Iwaki",
"position":"5"
},
"player7":{
"scene":"Careful stretch",
"name":"Hiiragi Kotoha",
"position":"7"
},
"player8":{
"scene":"In-depth strategy",
"name":"Waka Suzuki",
"position":"2"
},
"player9":{
"scene":"One ball soul!",
"name":"Yuhime Nozaki",
"position":"1"
}
}
"_2" is a wearing character. Training is more complicated than the main one.
Program execution
When you run the program, you will see a window like this.
You can get grades by dropping the image. At this time, the player information is read from player.json.
If you click "Add to database" here, data will be added to the table created earlier and the grade window will be closed.
Grade management
Run PupSQLite and open hachinai.db. Then, double-click results in Tables to display the saved grades as shown below.
Click "SQL statement input"-> "Open query window" to display the SQL input screen. Here, you can display the player's total results by entering and executing the following SQL statement.
SELECT scene,name,Defensive position,Condition, round((Hits*1.0) / (At bat*1.0), 3)as batting average,Number of games,At bat, At bat, Hits,Home run,RBI,Walks,Base hit, round(((Hits +Walks)*1.0) / (At bat*1.0), 3)as on-base percentage, round((Base hit*1.0) / (At bat*1.0), 3)as slugging percentage, round(((Hits +Walks)*1.0) / (At bat*1.0) + (Base hit*1.0) / (At bat*1.0), 3) as OPS, RC from (
SELECT scene as scene,name as name,position as defensive position, round(avg(condition), 2)as condition, count(scene)as number of games, sum(at_bat)as at bat, sum(at_bat) - sum(BB)as at bat, sum(hit)as hits, sum(homerun)as home run, sum(RBI)as RBI, sum(BB)as walks, sum(base_hit)as base hit, round(avg(RC), 2) as RC FROM results GROUP BY scene ) ;results GROUP BY scene ) ;
You can also sort by clicking the column name.
Looking at the data, I feel that players who are in good shape tend to perform well. I think that the batting average of Kana Kuju is so high because she has exceeded the limit twice. I'm not sure why Minako Nitta has a high slugging percentage.
Furthermore, various data can be output by devising the SQL statement. For example, if you change the SQL statement as follows, you can get the total grade for each condition.
SELECT scene,name,Defensive position,Condition, round((Hits*1.0) / (At bat*1.0), 3)as batting average,Number of games,At bat, At bat, Hits,Home run,RBI,Walks,Base hit, round(((Hits +Walks)*1.0) / (At bat*1.0), 3)as on-base percentage, round((Base hit*1.0) / (At bat*1.0), 3)as slugging percentage, round(((Hits +Walks)*1.0) / (At bat*1.0) + (Base hit*1.0) / (At bat*1.0), 3) as OPS, RC from (
SELECT scene as scene,name as name,position as defensive position, round(avg(condition), 2)as condition, count(scene)as number of games, sum(at_bat)as at bat, sum(at_bat) - sum(BB)as at bat, sum(hit)as hits, sum(homerun)as home run, sum(RBI)as RBI, sum(BB)as walks, sum(base_hit)as base hit, round(avg(RC), 2) as RC FROM results WHERE scene = 'Take a break' GROUP BY condition ) ;
in conclusion
Data management has become much easier because I no longer have to copy and paste the results into Excel. I would like to collect data in a ranking match that will be held soon.