ラズパイ3でデータベース(SQLite）

---

RaspbianではMySQLやPostgreSQLなどたいそうなボリュームにも対応できるDBが使えますが、今回はもっと軽量で手軽に扱えるSQLiteを使ってみます。

何らかの情報を事前に保存しておくためと、センサーなどから送られるデータをとにかく都度保存しておくためです。

SQLiteも限定的に使う（セキュリティを気にしない、項目数もそんなに多くなく１レコード100byte程度、データ量も１００万オーダーなど….）なら問題ないようです。

またデータが溜まりすぎてSQLiteではいかんともしがたいという状況になったら、その時MySQLやPostgreSQLへの移行なんぞを考えます。データ移行時のトラブルを少なくしたいので、テーブル作成やデータ挿入時に特殊なデータ型や文字は使わないようにします。

SQLの発行をPythonでやってみます。

Pythonではver2.5からSQLite3が標準ライブラリーにはいっています。

エラーは起きないはず(^^)。

sqlite3 モジュールの詳細はこちらを参照

 

---

データの移動がさせやすいようにUSBメモリーに保存しておきます。

データベースの作成はいたって簡単です。

test.dbがお入用なら、接続を実行します（無ければ勝手に作ってくれます）。

XXXはUSBメモリーの名前（要確認）

＄python3

>>> import sqlite3
>>> con = sqlite3.connect(“/media/pi/XXX/test.db”)

カーソル作成
>>> cur = con.cursor()

id,com,wav,ctrlという4つのカラムを持ったcom_infoというテーブルを作成してみます

>>> sql = u”create table com_info(id integer primary key autoincrement,com text,wav text,ctrl text)”
>>> cur.execute(sql);

テーブルのカラム情報を確認
>>> cur.execute(“pragma table_info(com_info)”)
>>> cols = cur.fetchall()
>>> print(cols)

ちなみに既存のテーブルを削除して新規に作成する場合（AAA -> BBB)
cur.executescript(“””drop table if exists AAA;create table BBB(com,wav,ctrl )”””)

データ挿入
>>> sql = u”insert into com_info values(null,’はい’, ‘hai.wav’, ‘com_yes.sh’)”
>>> cur.execute(sql)

あるいは、複数データをまとめて挿入する場合は

>>> data = [(null,’いいえ’, ‘iie.wav’, ‘com_no.sh’),(null,’さあ’, ‘sa.wav’, ‘com_sa.sh’)]
>>> sql = u”insert into com_info(id,com,wav,ctrl) values(?,?,?,?)”
>>> cur.executemany(sql,data)

コミット

>>> con.commit()

データ確認

>>> sql = “select * from com_info”
>>> cur.execute(sql)
>>> cur.fetchall()

>>> cur.close()
>>> con.close()

 

 

 

---

データを取り出してみます

XXXはUSBメモリーの名前（要確認）

【get_data.py】

 

 

---

事前にUSBメモリーが挿入されているかチェックする場合

import os.path
os.path.isfile(“/media/pi/XXX”)
# => False
os.path.isfile(“/media/pi/XXX/test.db”)
# => True

OR

os.path.isdir(“/media/pi/XXX”)
# => True
os.path.isdir(“/media/pi/XXX/test.db”)
# => False

---

データ削除

2番目のデータを削除してみます。

cur.execute(‘delete from com_info where id=?’, (2,))
cur.execute(‘select * from com_info’)
cur.fetchall()

---

ダンプファイル作成

【dump.py】

 

 

【dump.sql】

BEGIN TRANSACTION;
CREATE TABLE data_info(id integer primary key autoincrement,datetime integer,tempreture real,pressure real,humidity real,temp_mix real,temp_max real,wind_speed real,wind_degree real,cloud real,description text);
INSERT INTO “data_info” VALUES(1,1550640883,14.98,1016.0,72.0,13.0,16.0,6.7,270.0,20.0,’few clouds’);
INSERT INTO “data_info” VALUES(2,1550641031,14.98,1016.0,72.0,13.0,16.0,6.7,270.0,20.0,’few clouds’);
INSERT INTO “data_info” VALUES(3,1550643002,14.76,1015.0,67.0,13.0,17.0,5.1,340.0,75.0,’broken clouds’);
INSERT INTO “data_info” VALUES(4,1550646002,14.5,1015.0,67.0,13.0,16.0,5.1,350.0,75.0,’broken clouds’);
・
・
・
・
DELETE FROM “sqlite_sequence”;
INSERT INTO “sqlite_sequence” VALUES(‘data_info’,65);
COMMIT;

---