記録データのDB化に挑戦してみたものの

どうも、じぇいかわさきです。

昨日までに、温湿度データをCSV形式のファイルに保存する方法までを確立しました。

しかし、実際にこのCSVを使ってみようとしたときに、問題が発生したんです。

同時に、この問題は、先々記録データをDB化使用としたときにも影響を与えることが分かったんです。

もしかしたら、自分の知識が無いだけで影響は与えないのかもしれませんが、現状の自分レベルで使えるようにすることが先決です。

今回は、まずCSVファイルに記録する方式を変更する事と、MariaDBにデータを保管するまでを実現しました。

前回までに、PHPではMariaDBとの接続はできていましたが、今度はPythonからの接続で、かつデータを流し込む所までです。

実際には、データの入力にかなり手こずりました。これも、PythonとSQL文の使い方が十分理解できていなかった事が大きかったようです。

同じような所で悩む人が居たときに、助けになるよう備忘録で残しておきます。

CSVファイルの何が問題か？

まず、現在保存しているCSVファイルの何が問題かと言いますと、記録のタイムスタンプを一緒に記録していますが、日付と時間までが1つのセルに記録されているため、DBにインポートしようとしたときに、うまくいきませんでした。

また、日付や時間で抽出しようとしたときに、いちいち読みだした値から、何桁目からいくつの数字という感じで、抽出条件が複雑になるのが嫌でした。

やはり、日付と時間を別々のカラムに変更して、各々で抽出ができるようにしたいと考えたのです。

DB化した時も、この日付と時間の2つのペアで主キーに設定すれば、必ずユニークになりますから心配もないと判断しました。

今までは下記のような形で、タイムスタンプを抽出しておりました。

now = str(datetime.datetime.now())[0:19]

この状態では、タイムスタンプは「2021-03-07 17:51:58」のようになっており、一つのデータとなってしまいました。

これを以下のように変更です。

d_today = datetime.date.today()
t_now = datetime.datetime.now().time()
t_now = t_now.strftime('%H:%M:%S')
ds = str(d_today)
ts = str(t_now)

こうすることで、「2021-03-07,17:51:58」のように、日付と時間の間にカンマが入るので、別々こカラムで保管することができるようになります。

ついでに、温湿度の結果を文字列として扱えるように変更をかけて変数を割り当てます。

tm = str(result.temperature)
hm = str(result.humidity)

これでファイルに記録するための変数dataも

data = [now, result.temperature, result.humidity]

から

data = [ds, ts, tm, hm]

に、シンプルになりました。

これで、CSVファイルからDBへの読み込みが簡単にできるようになりました。

しかし、いちいちCSVでファイルに保存したものを呼び出して、再度DBに読み込ませるのは二度手間でもったいないので、そのままDBに書き込めるようにすることを考えました。

DBへの接続を考える

DBへの接続は、前回学んだPHPとあまり変わらないですね。

まずはDBへのコネクションを確立してから、SQL文を実行させるということは変わりません。

まずはDBへのコネクションを確立させましょう。

MariaDBとPythonを接続させるためのモジュールとしては、MySQLdbというモジュールを使用するので、最初にMySQLdbを読み込ませます。

次は実際にPythonコードでMariaDBに接続する方法です。

#DB connection setup
connection = MySQLdb.connect(
   user='pi',
   password='123456789',
   host='127.0.0.1',
   db='iot_data',
   charset='utf8mb4'
   )
   
cursor = connection.cursor()

これだけコードを書き、実際に実行してみたときにエラーが返って来なければDBと接続ができていることになります。

次に実行分を書き、その後にその実行分をコミットしなければいけません。

connection.commit()

というコマンドで実行文をコミットし、完了したらDBを切り離します。

connection.close()

つなげっぱなしでも良いのですが、なんとなくデータの書き込みができなかったり、テーブルが壊れたりしそうなので、今回は1回のデータを読み込んだらDBに接続し、書き込みが終了したら切り離す事にしました。
※これが後で仇になりました。

DBの基本的なコードは以下のようになります。

#DB connection setup
connection = MySQLdb.connect(
   user='pi',
   password='123456789',
   host='127.0.0.1',
   db='iot_data',
   charset='utf8mb4'
   )
#DB接続
cursor = connection.cursor()

ここに実行するSQL文を書く

connection.commit()

#データベース切断
connection.close()

実際のデータ挿入のSQL文を考える

ネットで調べながら、最初はこんなコードを考えました。

cursor.execute("INSERT INTO temp(date, time, temp, hmdt) VALUES(ds,td,tm,hm)")

いきなりエラー出まくりの世界。

しかもエラーも修正するたびに、違ったエラーSyntax errorはもとよりTypeErrorとか、わけわからないのが多かったですね。

cursor.execute("INSERT INTO temp(date, time, temp, hmdt) VALUES('ds','td','tm','hm')")

に変更したら、エラーは無くなり、やった！と思いしばらく動かしました。

10分間隔にしているので、次の書き込みは10分後ですね。

なんと、10分後に主キーの重複は許されないというような内容のエラーが発生しました。

あれ？時間が変わっているから絶対に重複するはずがないのですが・・・

ターミナルで接続してデータベースのテーブルの中身を見てビックリでした。

なんと、ds,ts,tm,hmという文字が入力されており、実際のデータではありませんでした。

当然、2回目の書き込みをしようとしたときに、おなじようにds,ts,tm,hmを書き込もうとしたから重複だというエラーになったのです。

ある意味、ちゃんと主キーの動作をしてnullを許さない動作をしているってことですね。

ここから考え、ネットを調べまくって出た結論がこちらです。楽に1時間以上格闘していましたね。

sql = "INSERT INTO temp(date, time, temp, hmdt) VALUES(%s,%s,%s,%s)"
val = [ds, ts, tm, hm]
           
cursor.execute(sql, val)

結局、データを代入するにはプレースホルダーという当て字を使い、そこに配列データを引き渡すという方法にすることがわかりました。

確かに、ドットインストールの勉強で習ったような気がしますが、すっかり忘れていますね。

この方式を用いてからは、正しくタイムスタンプと計測データを引き渡すことができるようになりました。

+------------+----------+------+------+
| date | time | temp | hmdt |
+------------+----------+------+------+
| 2021-03-07 | 22:02:20 | 19.4 | 38.0 |
+------------+----------+------+------+

しかし、やはり2回目以降のデータが上手く引き渡されない事が発生しております。

エラーはコードは以下の通りです

mysqldb._exceptions.operationalerror (2006 '')

今度はまた別のエラーが発生ですが、今回はここで力尽きて調査は次回に持ち越しです。毎日が一進一退だぁ〜。

と、思いましたが、気になる点がコード内にありましたので、少し頑張って修正をかけてみました。

気になったのは、以下のコードの部分。

#データベース切断
connection.close()

最初に、1回毎にデータを書き込んだらDBの接続をクローズすると書いたのですが、よくよくコードを眺めていくと、while内で接続をクローズしてしまうと、次のデータのときにオープンしていないことに気がついたのです。

これって、前回に犯した過ちと同じですよね。

Pythonは桁下がりをした部分が、一つの塊として動作するということ。

DBの接続はこのwhileの外で行っていますから、while内で接続を切ってしまうと、再接続ができないってことなんですね。

という事で、

#データベース切断
connection.close()

の部分を一番最後に持ってきました。

そう、tyrのループの後ろ側です。

とりあえず、2回までの書き込みは確認できましたので、それ以降本当に問題ないのかを一晩続けてみました。

結果は以下の通り。

バッチリと、10分毎にデータを採取してDBに書き込んでおります。

昨日数時間を掛けて実施したことが、無事成功した事になります。

1つの問題が発生すると、また次の問題が発生するというビギナーアルアル状態でしたが、一つひとつを解決していくと楽しいですね。

さて、次の使い方を考えていかないと。

また頭を使いそうですね。