====== 第4章 データベースへの結果データ保存【中級編】 ======
この章では3章でCSVで出力していたログをデータベースに入れて、管理しやすくする方法を学びます。\\
次章でBIツールで利用するための準備となります。\\
===== 第一項 データベースの作成 =====
SCORER Edgeの左メニューからSDK→Relational Databaseを表示して右上のADDボタンを押します。\\
設定は基本標準のままで、パスワードだけ、推奨を記録するか自分で入力してください\\
{{::db01.png?400|}}\\
作成したら「open dashboard」リンクを開くと下記のような画面になるので、先ほどのパスワードでログインしてください\\
{{::db02.png?400|}}\\
するとこのような画面となりデータベースサーバーが操作できるようになります\\
{{:db03.png?400|}}\\
データベースはかなり雑に捉えれば、データベースサーバー＞データベース＞テーブル＞レコード（ここに実データが入っている）\\
という関係性となっているので、まずはデータベースを作成します。\\
「データベースを作成」をクリックして「tutorial」という名前のデータベースを作成しましょう。照合順序はutf8_general_ciにしておきましょう。\\
{{::db04.png?400|}}\\
===== 第二項 データベースへの接続 =====
前項で作成したものに対してSDKからテーブルを作成したりデータを入れてみましょう。
import mysql.connector
db=mysql.connector.connect(host="127.0.0.1", port="23301", user="root", password="12345678")#パスワードは適宜変更
cursor=db.cursor()
cursor.execute("USE tutorial")
db.commit()
cursor.execute("""CREATE TABLE IF NOT EXISTS fruits_table(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
fruits VARCHAR(32),
value INT);""")
db.commit()
cursor.execute("""INSERT INTO fruits_table (fruits,value) VALUES ('apple',1);""")
cursor.execute("""INSERT INTO fruits_table (fruits,value) VALUES ('orange',2);""")
cursor.execute("""INSERT INTO fruits_table (fruits,value) VALUES ('banana',3);""")
db.commit()
に対して\\
python 04dbtest_createtable.py
で実行すると\\
{{::db06.png?600|}}\\
でfruitsテーブルに3行データが追加されていることがわかります。\\
cursor.executeで実行されている中身はSQL文というもので、[[https://www.1keydata.com/jp/sql/]]など多くのサイトで学ぶための情報を得ることができます。\\
===== 第三項 スターターコードへの組み込み =====
前項までの練習用プログラムを03stopinfront_sketch.pyに組み込むと下記のようになります。\\
import mysql.connector
import sys
import os
import json
import cv2
import math
from datetime import datetime, timedelta, timezone
import csv
import glob
import numpy as np
if __name__ == '__main__':
dirpath = 'data'
duration = 5
area_def_file = os.environ['HOME']+'/drawui/sketchareas.json'
JST = timezone(timedelta(hours=+9), 'JST')
print(sys.argv[1]+"を集計しています・・")
file_list = sorted(glob.glob('./'+dirpath+'/'+sys.argv[1]+'/raw_'+sys.argv[1]+'*.json'))
print("対象ファイル一覧")
for filename in file_list:
print(filename)
json_open = open(area_def_file, 'r')
result_json_file = open('result_sketchareas_'+sys.argv[1]+'.json', 'w')
areadef = json.load(json_open)
resultarr = {}
for areaidx, area in enumerate(areadef['areas']):
areaname='areaID'+str(areaidx)
print(areaname)
resultarr[areaname]={}
print("集計・・")
for areaidx, area in enumerate(areadef['areas']):
areaname='areaID'+str(areaidx)
print(areaname)
polygon = []
for point in area['points']:
polygon.append([point['x'],point['y']])
contour = np.array(polygon)
print(contour)
for filename in file_list:
print(areaname+"_"+filename)
if os.path.getsize(filename) == 0:
continue
raw_json_open = open(filename, 'r')
raw_json = json.load(raw_json_open)
for frame in raw_json:
jptime = datetime.fromtimestamp(frame['frame_time'],JST)
people = frame['objects']
for person in people:
#print(areaname+'_'+person['tracking_id']+'_'+str(judgenum))
personpos = [person["floor"]["x"],person["floor"]["y"]]
test = cv2.pointPolygonTest(contour, personpos, False)
if test>0:
if person['tracking_id'] in resultarr[areaname]:
resultarr[areaname][person['tracking_id']]['end_time'] = frame['frame_time']
resultarr[areaname][person['tracking_id']]['duration'] = frame['frame_time']-resultarr[areaname][person['tracking_id']]['start_time']
resultarr[areaname][person['tracking_id']]['end_time_jp'] = jptime.isoformat()
if resultarr[areaname][person['tracking_id']]['duration'] > duration:
resultarr[areaname][person['tracking_id']]['stop_judge'] = True
else:
resultarr[areaname][person['tracking_id']]={'area':areaname,'start_time_jp':jptime.isoformat(),'end_time_jp':jptime.isoformat(),'start_time':frame['frame_time'],'end_time':frame['frame_time'],'stream_id':frame['stream_id'],'duration':0,'stop_judge':False}
json.dump(resultarr,result_json_file)
db=mysql.connector.connect(host="127.0.0.1", port="23301", user="root", password="12345678")#パスワードは適宜変更
cursor=db.cursor()
cursor.execute("USE tutorial")
db.commit()
cursor.execute("""CREATE TABLE IF NOT EXISTS stopinfront_sketch(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
stream_id VARCHAR(32),
areaname VARCHAR(32),
tracking_id VARCHAR(32),
start_time_jp DATETIME,
end_time_jp DATETIME,
total_duration INT,
stop_judge TINYINT);""")
db.commit()
for areaname, records in resultarr.items():
print(areaname)
f = open("sketchareas_"+sys.argv[1]+"_"+areaname+".csv", 'w', encoding='utf-8', newline='')
write = csv.writer(f)
write.writerow(["stream_id","areaname","tracking_id","start_time_jp","end_time_jp","total_duration","stop_judge"])
for tracking_id, record in records.items():
write.writerow([record['stream_id'],areaname,tracking_id,record['start_time_jp'],record['end_time_jp'],record['duration'],record['stop_judge']])
cursor.execute("INSERT INTO stopinfront_sketch (stream_id,areaname,tracking_id,start_time_jp,end_time_jp,total_duration,stop_judge) VALUES (%s,%s,%s,%s,%s,%s,%s);",(record['stream_id'],areaname,tracking_id,jptime.fromisoformat(record['start_time_jp']),jptime.fromisoformat(record['end_time_jp']),record['duration'],record['stop_judge']))
db.commit()
f.close()
ハイライトしているところが変更点です。\\
python 04stopinfront_db_sketch.py 20211101
などで実行すると下記のようにデータベースにデータが入っていることがわかります。\\
{{::db07.png?600|}}
なお、2回実行すると同じデータが追加で入ってしまいます（自動付与されるIDは別になりますが）。\\
同じ日付を再度実行したい場合はその分のデータをSQLで削除するか、テーブルごと削除して再度実行しましょう。\\
次項はこのデータをMetabaseというBIツールを用いてグラフ表示してみましょう。\\