SQL（オセロのデータ整理003：コード見直し、まとめ）
■コードを見直して10ファイル分を入れてみる。
Powershellのオセロプログラムから得られるオセロの結果のテキストデータをSQLiteのデータベースに入れるよう見てきたけど、今回で一度区切りにする。
サンプルで作ってきたゲームテーブルを改めて見直すと、resultを後ろの列に置いているので見づらかった。ので、経過（history）と結果（result）の２つのテーブルにゲーム内の手順（Trial00~Trail68）と結果（White_result~Note）を分けるように修正した。
ゲームテーブル（ID、Trial00、Trial01、（省略）、Trial68、White_result、Black_result、Result、Note）
↓
historyテーブル（ID、Trial00、Trial01、（省略）、Trial68）
resultテーブル（ID、White_result、Black_result、Result、Note）
テーブル同士の結合JOINを使えば、ゲームテーブルと同じような表示にすることもできる。
データベースに入れるときに、接続や切断を行う部分（connectやclose）があるけど、前のコードでは１行毎にそれらを行うようになっていた。明らかにパフォーマンスを落としているので、始めと終わりだけ行うようにしたところ、10万ゲームの読み込みに1500秒かかるところが60~150秒程度になった。
10ファイル分（各10万ゲーム）をデータベースに入力し、DB Broswerのソフトで中身を見てみると問題なく表示できた（下の図）。また、updateで特定のレコードのNote部分の更新も問題なくできた。データベースファイルのファイルサイズは12.7GB。もともとテキストファイル１つ分が1.3GBなので、やや小さくなった。
Pythonを使ってSQLiteのデータベース作成をし、その中のテーブルへテキストファイルからデータを移した。テキストエディタでは表示・操作ができなかったが、これでできるようになった。
以下は最終的なコード。
import sqlite3
import time
POWERSHELL_OTHELLO_FILENAME = "C:\\Users\\user01\\Web\\it\\Powershell\\Data\\100000text_●50204vs〇45703vsE4093.txt"
# POWERSHELL_OTHELLO_FILENAME = "result.txt"
DBNAME = "othello_data.db"
ASSUMED_MAX_TURN = 69
def update_Note(note, idnum):
connect = sqlite3.connect(DBNAME)
cursor = connect.cursor()
sql = 'UPDATE result SET Note=' + note + ' where id=' + idnum
cursor.execute(sql)
connect.commit()
connect.close()
def create_table():
connect = sqlite3.connect(DBNAME)
cursor = connect.cursor()
cursor.execute(
'CREATE TABLE IF NOT EXISTS history(id INTEGER PRIMARY KEY, Trial00 nchar(206), Trial01 nchar(206), Trial02 nchar(206), Trial03 nchar(206), Trial04 nchar(206), Trial05 nchar(206), Trial06 nchar(206), Trial07 nchar(206), Trial08 nchar(206), Trial09 nchar(206), Trial10 nchar(206), Trial11 nchar(206), Trial12 nchar(206), Trial13 nchar(206), Trial14 nchar(206), Trial15 nchar(206), Trial16 nchar(206), Trial17 nchar(206), Trial18 nchar(206), Trial19 nchar(206), Trial20 nchar(206), Trial21 nchar(206), Trial22 nchar(206), Trial23 nchar(206), Trial24 nchar(206), Trial25 nchar(206), Trial26 nchar(206), Trial27 nchar(206), Trial28 nchar(206), Trial29 nchar(206), Trial30 nchar(206), Trial31 nchar(206), Trial32 nchar(206), Trial33 nchar(206), Trial34 nchar(206), Trial35 nchar(206), Trial36 nchar(206), Trial37 nchar(206), Trial38 nchar(206), Trial39 nchar(206), Trial40 nchar(206), Trial41 nchar(206), Trial42 nchar(206), Trial43 nchar(206), Trial44 nchar(206), Trial45 nchar(206), Trial46 nchar(206), Trial47 nchar(206), Trial48 nchar(206), Trial49 nchar(206), Trial50 nchar(206), Trial51 nchar(206), Trial52 nchar(206), Trial53 nchar(206), Trial54 nchar(206), Trial55 nchar(206), Trial56 nchar(206), Trial57 nchar(206), Trial58 nchar(206), Trial59 nchar(206), Trial60 nchar(206), Trial61 nchar(206), Trial62 nchar(206), Trial63 nchar(206), Trial64 nchar(206), Trial65 nchar(206), Trial66 nchar(206), Trial67 nchar(206), Trial68 nchar(206))'
)
cursor.execute(
'CREATE TABLE IF NOT EXISTS result(id INTEGER PRIMARY KEY, Result nchar(1), White_result tinyint, Black_result tinyint, Note varchar(255))'
)
connect.close()
def insert_result(game, cur):
insertedData_history = [
(game[0], game[1], game[2], game[3], game[4], game[5], game[6], game[7], game[8], game[9], game[10],
game[11], game[12], game[13], game[14], game[15], game[16], game[17], game[18], game[19], game[20],
game[21], game[22], game[23], game[24], game[25], game[26], game[27], game[28], game[29], game[30],
game[31], game[32], game[33], game[34], game[35], game[36], game[37], game[38], game[39], game[40],
game[41], game[42], game[43], game[44], game[45], game[46], game[47], game[48], game[49], game[50],
game[51], game[52], game[53], game[54], game[55], game[56], game[57], game[58], game[59], game[60],
game[61], game[62], game[63], game[64], game[65], game[66], game[67], game[68])]
cur.executemany(
'INSERT INTO history values(null,?, ?, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)',
insertedData_history)
insertedData_result = [(game[71], game[69], game[70], "")]
cur.executemany('INSERT INTO result values(null,?, ?, ?,?)', insertedData_result)
def txtfile_to_DB(filename, dbname):
f = open(filename, 'r', encoding='UTF-8')
textdata = []
board = ""
flag = -1
connect = sqlite3.connect(dbname)
cursor = connect.cursor()
while True:
line = f.readline()
if line:
if line.find("Start Othello") > 0:
line = f.readline()
flag = 0
elif line.find("Trial") > 0:
textdata.append(board.strip())
board = ""
flag = 0
line = f.readline()
elif line.find("End Othello") > 0:
textdata.append(board.strip())
flag = 1
if flag == 0:
board = board + line
elif flag == 1:
Black = int(line[line.find("●") + 1:line.find("vs")])
White = int(line[line.find("〇") + 1:line.find("Game")])
if len(textdata) < ASSUMED_MAX_TURN:
while True:
if len(textdata) == ASSUMED_MAX_TURN:
break
else:
textdata.append("")
textdata.append(White)
textdata.append(Black)
if Black > White:
textdata.append("B")
elif Black < White:
textdata.append("W")
elif Black == White:
textdata.append("E")
# connect = sqlite3.connect(dbname)
# cursor = connect.cursor()
insert_result(textdata, cursor)
# connect.commit()
# connect.close()
board = ""
textdata = []
else:
break
connect.commit()
connect.close()
f.close()
# create_table()
start_time = time.time()
# txtfile_to_DB(POWERSHELL_OTHELLO_FILENAME, DBNAME)
end_time = time.time()
print("process_time:", round(end_time - start_time, 5))
update_Note("''", "'11'")
