20. 温湿度ログをCSVからSQLite3に換えてみた

とあるネットの記事を試してみてSQLite3に興味を持ち、comfort2の温湿度ログをcsvからSQLite3のDBに換えてみた。

●１．導入
pythonにはSQLite3のモジュールがデフォルトで有るようなので、その他の導入。


# SQLite3のインストール
$ sudo apt-get install sqlite3

# PHP7.3用のSQLite3のインストール
$ sudo apt-get -y install php7.3-sqlite3

#   /etc/php/7.3/apache2/php.ini内下記のコメントアウトを外す
# 		extension=pdo_sqlite
# 		extension=sqlite3
$ sudo leafpad /etc/php/7.3/apache2/php.ini

#  apache2再起動
$ sudo systemctl restart apache2

# SQLite3導入確認
$ php -r "phpinfo();" | grep sql
/etc/php/7.3/cli/conf.d/20-pdo_sqlite.ini,
/etc/php/7.3/cli/conf.d/20-sqlite3.ini,
PDO drivers => sqlite
pdo_sqlite
sqlite3
sqlite3.defensive => 1 => 1
sqlite3.extension_dir => no value => no value

●２．pythonでcsvファイルからDB作成
現行のcsvログデータをSQLite3のDBへ移行するプログラムをpythonで作成し、過去のログデータを移行した。


#!/usr/bin/python3
# -*- coding: utf-8 -*-
import csv
import sqlite3
import glob

# データベース接続とカーソル生成
conn = sqlite3.connect('./sqlite3.db')
cur = conn.cursor()

try:
    # tableが無い場合は作成する。
    cur.execute("DROP TABLE IF EXISTS airlog")
    cur.execute("""CREATE TABLE IF NOT EXISTS airlog
      (date DATETIME UNIQUE NOT NULL,
      気温 FLOAT(4) NOT NULL,
      湿度 FLOAT(4) NOT NULL,
      気圧 FLOAT(6) NOT NULL);""")

    for csvfile in sorted(glob.glob('./*airlog_*.csv')):
        # ログ(csv)を開く。
        with open(csvfile) as f:
            b = csv.reader((line.replace('\0','') for line in f))	# nullデータは削除
            header = next(b)	# 1行目はヘッダーなのでダミーリード
            for t in b:
                # tableに各行のデータを挿入する。t=[date,temp,humi,press]
                cur.execute('INSERT OR IGNORE INTO airlog VALUES (?,?,?,?);', t)

    # 以降、sqlite3のDBからデータの読み出し確認
    # 2019/01/01 00:00:00 〜 2019/01/02 08:00:00のデータ抽出
    cur.execute("SELECT date, 気温, 湿度,気圧 FROM airlog WHERE date BETWEEN '2019/01/01 00:00:00' AND '2019/01/02 08:00:00';")
    for row in cur: # データ書き出し
        print( row )

except sqlite3.Error as e:
    print('sqlite3.Error occurred:', e.args[0])

# DBの変更を保存する。
conn.commit()
# データベースとの接続を閉じる。
conn.close()

●３．PHPでDBログデータの取得
現行ブラウザのjavascriptで温湿度ログデータをグラフ表示させるため、phpでDBのデータを取得してjavascriptに渡す仕組みを作った。(変数で渡す方法も有ったが、ブラウザの「ソースの表示」で沢山表示されてしまうので、結局csvファイルに戻して渡すことにした)


<?php
try{
	$db = new PDO('sqlite:./sqlite3.db');

	# 直近10件を昇順で得る
	$log = $db->prepare('select * from (SELECT * FROM airlog  ORDER BY date DESC LIMIT 10) as A ORDER BY date');
	$log->execute();
	$result = $log->fetchAll(PDO::FETCH_ASSOC);

	# DBのデータをjavascriptへ渡す為のcsvファイル作成
    $file = "./data.csv";
    # ヘッダー行
    $csv = (implode(",",array("DateTime","気温(℃)","湿度(％)","気圧(hPa)")))."\n";
    foreach($result as $r)
        $csv = $csv.(implode(",",$r))."\n";	# 配列を","カンマで区切り、最後に改行を加える

    file_put_contents($file, $csv);			# ファイルに書き込み

#	$p2j = json_encode($result, JSON_UNESCAPED_UNICODE);
#	最初はこの変数で渡していたが、ブラウザの「ソースの表示」で沢山表示されてしまうので止めた。

} catch (Exception $e) {

    echo $e->getMessage() . PHP_EOL;

}

●ブラウザ画面
新旧温湿度グラフ表示
新温湿度グラフ表示	旧温湿度グラフ表示

新版グラフ表示では、過去の最高値/最低値を表示させたり、過去のデータを開始日を指定して表示できるように変更した。SQLite3を利用して比較的簡単に変更できた。
余談ですが、新版グラフで気圧が急に落ち込んでいる部分は、台風15号の影響です。

【参考】下記サイトの情報を参考にしました。

2019/09/11