Raspberry PiでPythonからMySQLデータベースを扱う

2016年7月30日2018年8月12日

自分のRaspberry Piは常時通電で、毎朝Philips hueと協力して天気予報装置として働いてくれているのですが、Web APIで取得した天気情報は今のところその場限りで捨ててしまっています。「せっかくだから記録しておいたら後で何かに使えるかも？」と思いたったので、Raspberry Pi上でデータベースを扱ってみることにしました。データベースについてはほぼ初心者なのですが、これについては先人が多くいるハズなので、おそらくそんなに困ることはないでしょう。

データベースもMySQLやらPostgreSQLやらMongoDBやらいろいろあると思いますが、ここでは一番簡単にインストールできそう且つメジャーなMySQLを使うことにします。主に以下のサイトを参考にさせていただいています。

インストールはこんな感じで。

[code language=”bash”]
$ sudo apt-get update
$ sudo apt-get install mysql-server
[/code]

簡単。インストール中にrootユーザ用のパスワードの入力を求められるので、とりあえずRaspberry Piへのログイン用と同じにしておきます（本当はちゃんと考えないといけないのかもしれませんが）。

早速DBにアクセスしてみます。パスワードは先ほど入力したroot用のパスワードを入力します。

[code language=”sql”]
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.5.44-0+deb8u1 (Raspbian)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> status
————–
mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (armv7l) using readline 6.3

Connection id: 44
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.5.44-0+deb8u1 (Raspbian)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 1 hour 25 min 1 sec

Threads: 1 Questions: 584 Slow queries: 0 Opens: 189 Flush tables: 1 Open tables: 41 Queries per second avg: 0.114
————–

mysql> quit
Bye
[/code]

mysqlクライアントに入ってから”status”で設定を確認しています。文字コード(上記でlatin1になっているところ)はUTF-8に統一しておいた方が日本語を扱うときにラクなようなので、UTF-8に変更します。これについては、こちらの手順に従って、”/etc/mysql/my.cnf”に以下のように追記していきます。

[code language=”bash”]
$ sudo vim /etc/mysql/my.cnf

[client]
…
…
default-character-set=utf8

…
[mysqld]
…
…
collation-server = utf8_unicode_ci
init-connect=’SET NAMES utf8′
character-set-server = utf8

…
[mysqldump]
…
…
default-character-set=utf8

[/code]

設定を変更したらMySQLを再起動し、デフォルトの文字コードが変更されていることを確認します。

[code language=”sql”]
$ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.44-0+deb8u1 (Raspbian)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> status
————–
mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (armv7l) using readline 6.3

Connection id: 37
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.5.44-0+deb8u1 (Raspbian)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 2 min 32 sec

Threads: 1 Questions: 111 Slow queries: 0 Opens: 48 Flush tables: 1 Open tables: 41 Queries per second avg: 0.730
————–

mysql>
[/code]

確認できたので、データベースを作っていきます。以下では、現在存在しているデータベースを確認してから、”myhome_db”という名前のデータベースを作成して、確かに追加されているかどうかを確認しています。

[code language=”sql”]
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.5.44-0+deb8u1 (Raspbian)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> CREATE DATABASE myhome_db CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql>
[/code]

次に、使うDBを選択して、テーブルを作成します。今回は”weather_tb”という名称で、以下の項目を用意することにします。

date DATE NOT NULL
dow CHAR(3) NOT NULL
time TIME NOT NULL
weather VARCHAR(20) NOT NULL

良い主キーの設定方法についてはまだ勉強不足でよくわかっていないのですが、ここではdateとtimeの組み合わせで主キーとしたいと思います。

ではでは、実際に作ってみます。

[code language=”sql”]
mysql> USE myhome_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE weather_table;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE weather_tb(
-> date DATE NOT NULL,
-> dow CHAR(3) NOT NULL,
-> time TIME NOT NULL,
-> weather VARCHAR(20) NOT NULL,
-> PRIMARY KEY(date,time));
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;
+———————+
| Tables_in_myhome_db |
+———————+
| weather_tb |
+———————+
1 row in set (0.00 sec)

mysql>
[/code]

うまく作れたようなので、試しに適当なデータを突っ込んでみます。

[code language=”sql”]
mysql> INSERT INTO weather_tb values(‘2016-06-18′,’Sat’,’06:30:00′,’晴’);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM weather_tb;
+————+—–+———-+———+
| date | dow | time | weather |
+————+—–+———-+———+
| 2016-06-18 | Sat | 06:30:00 | 晴 |
+————+—–+———-+———+
1 row in set (0.00 sec)

mysql>
[/code]

続いて、レコードの更新と削除を試します。

[code language=”sql”]
mysql> UPDATE weather_tb SET weather=’雨’ WHERE date=’2016-06-18′;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM weather_tb;
+————+—–+———-+———+
| date | dow | time | weather |
+————+—–+———-+———+
| 2016-06-18 | Sat | 06:30:00 | 雨 |
+————+—–+———-+———+
1 row in set (0.00 sec)

mysql> DELETE FROM weather_tb WHERE date=’2016-06-18′;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM weather_tb;
Empty set (0.00 sec)

mysql>
[/code]

更新と削除自体はうまくいったのですが、改めて結果を見てみると、テーブル名は”weather_tb”より”weather_forecast_tb”の方が良い気がしてきたので、テーブル名を変更します。

[code language=”sql”]
mysql> SHOW TABLES;
+———————+
| Tables_in_myhome_db |
+———————+
| weather_tb |
+———————+
1 row in set (0.00 sec)

mysql> RENAME TABLE weather_tb TO weather_forecast_tb;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+———————+
| Tables_in_myhome_db |
+———————+
| weather_forecast_tb |
+———————+
1 row in set (0.00 sec)

mysql>
[/code]

これでとりあえずデータベースの準備と基本操作の確認できました。ちなみにデータベース用のコンソールを終了するときは、

[code language=”sql”]
mysql> quit;
[/code]

でOKです。

次に、PythonからのMySQLデータベースへのアクセスを試してみます。RaspbianのデフォルトのPython（2.7系）ならMySQLdbを使うのが定番だったようですが、これは自分のPython環境である3.5系には対応していないので、代わりにPyMySQLを使います。

[code language=”bash”]
$ pip install PyMySQL
[/code]

PyMySQLのページのサンプルをベースに、とりあえずテスト用に以下を作ってみました。

[code title=”sample-mysql.py” language=”python”]
import pymysql.cursors
import datetime
import time

connection = pymysql.connect(host="localhost", user="root", password="xxxxxx", db="myhome_db", charset="utf8")

date = datetime.datetime.today()
today = date.strftime(‘%Y-%m-%d’)
dow = date.strftime(‘%a’)
now = date.strftime(‘%H:%M:%S’)
weather = ‘雨’

try:
with connection.cursor() as cursor:
sql = "INSERT INTO weather_forecast_tb (date,dow,time,weather) VALUES(%s,%s,%s,%s)"
cursor.execute(sql,(today,dow,now,weather))
connection.commit()
print("Data commited.")

with connection.cursor() as cursor:
sql = "SELECT * FROM weather_forecast_tb"
cursor.execute(sql)
result = cursor.fetchall()
for data in result:
select_date = data[0]
select_dow = data[1]
select_time = data[2]
select_weather = data[3]
print("{0},{1},{2},{3}".format(select_date,select_dow,select_time,select_weather))
except:
print("DB Access Error!")
finally:
connection.close()
[/code]

実行すると、こんな感じになります。

[code language=”bash”]
$ python sample_mysql.py
Data commited.
2016-06-19,Sun,10:44:56,雨
[/code]

一応、MySQLクライアントの方でも確認してみます。

[code language=”sql”]
$ mysql -u root -p -D myhome_db
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.5.44-0+deb8u1 (Raspbian)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT * FROM weather_forecast_tb;
+————+—–+———-+———+
| date | dow | time | weather |
+————+—–+———-+———+
| 2016-06-19 | Sun | 10:44:56 | 雨 |
+————+—–+———-+———+
1 row in set (0.00 sec)

mysql>
[/code]

うん、OKです。

最後に、前に作ったhue天気予報のプログラムに上記のサンプルソースをマージします。本当に足しているだけです。

[code language=”python”]
# coding: utf-8
import requests
import pymysql.cursors
import datetime
import time

weather_location_id = 270000
WEATHER_SUNNY = ‘{"on":true,"bri":24,"xy":[0.52,0.42],"sat":128,"effect":"none"}’
WEATHER_CLOUDY = ‘{"on":true,"bri":24,"xy":[0.33,0.30],"sat":128,"effect":"none"}’
WEATHER_RAINY = ‘{"on":true,"bri":24,"xy":[0.15,0.15],"sat":128,"effect":"none"}’
WEATHER_SNOWY = ‘{"on":true,"bri":48,"xy":[0.33,0.30],"sat":128,"effect":"colorloop"}’

date = datetime.datetime.today()
today = date.strftime(‘%Y-%m-%d’)
now = date.strftime(‘%H:%M’)
dow = date.strftime(‘%a’)
if ((now == ’06:30′) and (dow in [‘Mon’,’Tue’,’Wed’,’Thu’,’Fri’,’Sat’])) or ((now == ’07:30′) and (dow == ‘Sun’)):
print("Weather Report")
weather_data = requests.get(‘http://weather.livedoor.com/forecast/webservice/json/v1?city=%s’ % weather_location_id).json()
weather_today = weather_data[‘forecasts’][0][‘telop’]
print(weather_today)
if ‘雪’ in weather_today:
print("Snowy")
requests.put(‘http://192.168.24.91/api/f6d3db138fbf2d71b9639e51e517f0b/lights/13/state’,WEATHER_SNOWY)
elif ‘雨’ in weather_today:
print("Rainy")
requests.put(‘http://192.168.24.91/api/f6d3db138fbf2d71b9639e51e517f0b/lights/13/state’,WEATHER_RAINY)
elif ‘晴れ’ == weather_today:
print("Sunny")
requests.put(‘http://192.168.24.91/api/f6d3db138fbf2d71b9639e51e517f0b/lights/13/state’,WEATHER_SUNNY)
else:
print("Cloudy")
requests.put(‘http://192.168.24.91/api/f6d3db138fbf2d71b9639e51e517f0b/lights/13/state’,WEATHER_CLOUDY)

connection = pymysql.connect(host="localhost", user="root", password="xxxxxx", db="myhome_db", charset="utf8")

ちなみに、上記はPython 3.5系想定なので、Python2.7系のときは20行目の”weather_data[‘forecasts’][0][‘telop’]”の後に”.encode(‘utf-8′)”をつける必要があるところだけ注意です。

これで、毎回の天気予報の後にその日の天気予報の情報を残すことができるようになりました。せっかくなので、ここ一ヶ月ぐらい記録してきた結果を表示すると、こんな感じになります。

[code language=”sql”]
mysql> select * from weather_forecast_tb;
+————+—–+———-+————–+
| date | dow | time | weather |
+————+—–+———-+————–+
| 2016-06-20 | Mon | 06:30:00 | 曇り |
| 2016-06-21 | Tue | 06:30:00 | 雨のち曇 |
| 2016-06-22 | Wed | 06:30:00 | 曇時々雨 |
| 2016-06-23 | Thu | 06:30:00 | 雨のち晴 |
| 2016-06-24 | Fri | 06:30:00 | 曇のち雨 |
| 2016-06-25 | Sat | 06:30:00 | 雨のち曇 |
| 2016-06-26 | Sun | 07:30:00 | 晴時々曇 |
| 2016-06-27 | Mon | 06:30:00 | 晴のち雨 |
| 2016-06-28 | Tue | 06:30:00 | 雨のち曇 |
| 2016-06-29 | Wed | 06:30:00 | 雨時々曇 |
| 2016-06-30 | Thu | 06:30:00 | 雨のち曇 |
| 2016-07-01 | Fri | 06:30:00 | 曇のち晴 |
| 2016-07-02 | Sat | 06:30:00 | 曇時々晴 |
| 2016-07-03 | Sun | 07:30:00 | 晴時々曇 |
| 2016-07-04 | Mon | 06:30:00 | 晴時々曇 |
| 2016-07-05 | Tue | 06:30:00 | 晴時々曇 |
| 2016-07-06 | Wed | 06:30:00 | 晴時々曇 |
| 2016-07-07 | Thu | 06:30:00 | 晴れ |
| 2016-07-08 | Fri | 06:30:00 | 曇のち雨 |
| 2016-07-09 | Sat | 06:30:00 | 雨のち曇 |
| 2016-07-10 | Sun | 07:30:00 | 曇時々晴 |
| 2016-07-11 | Mon | 06:30:00 | 晴のち曇 |
| 2016-07-12 | Tue | 06:30:00 | 曇時々雨 |
| 2016-07-13 | Wed | 06:30:00 | 雨 |
| 2016-07-14 | Thu | 06:30:00 | 曇時々晴 |
| 2016-07-15 | Fri | 06:30:00 | 曇り |
| 2016-07-16 | Sat | 06:30:00 | 曇り |
| 2016-07-17 | Sun | 07:30:00 | 曇時々雨 |
| 2016-07-18 | Mon | 06:30:00 | 晴時々曇 |
| 2016-07-19 | Tue | 06:30:00 | 晴れ |
| 2016-07-20 | Wed | 06:30:00 | 晴れ |
| 2016-07-21 | Thu | 06:30:00 | 晴時々曇 |
| 2016-07-22 | Fri | 06:30:00 | 晴のち曇 |
| 2016-07-23 | Sat | 06:30:00 | 晴のち曇 |
| 2016-07-24 | Sun | 07:50:00 | 晴時々曇 |
| 2016-07-25 | Mon | 06:30:00 | 曇のち雨 |
| 2016-07-26 | Tue | 06:30:00 | 雨のち曇 |
| 2016-07-27 | Wed | 06:30:00 | 晴時々曇 |
| 2016-07-28 | Thu | 07:00:00 | 晴時々曇 |
| 2016-07-29 | Fri | 06:30:00 | 晴時々曇 |
| 2016-07-30 | Sat | 06:30:00 | 晴時々曇 |
+————+—–+———-+————–+
41 rows in set (0.00 sec)
[/code]