第4章　データベースへの結果データ保存【中級編】

この章では3章でCSVで出力していたログをデータベースに入れて、管理しやすくする方法を学びます。
次章でBIツールで利用するための準備となります。

SCORER Edgeの左メニューからSDK→Relational Databaseを表示して右上のADDボタンを押します。
設定は基本標準のままで、パスワードだけ、推奨を記録するか自分で入力してください

作成したら「open dashboard」リンクを開くと下記のような画面になるので、先ほどのパスワードでログインしてください

するとこのような画面となりデータベースサーバーが操作できるようになります

データベースはかなり雑に捉えれば、データベースサーバー＞データベース＞テーブル＞レコード（ここに実データが入っている）
という関係性となっているので、まずはデータベースを作成します。
「データベースを作成」をクリックして「tutorial」という名前のデータベースを作成しましょう。照合順序はutf8_general_ciにしておきましょう。

前項で作成したものに対して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

で実行すると

で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

などで実行すると下記のようにデータベースにデータが入っていることがわかります。
なお、2回実行すると同じデータが追加で入ってしまいます（自動付与されるIDは別になりますが）。
同じ日付を再度実行したい場合はその分のデータをSQLで削除するか、テーブルごと削除して再度実行しましょう。
次項はこのデータをMetabaseというBIツールを用いてグラフ表示してみましょう。