Raspberry PiでPythonからMySQLデータベースを扱う
自分のRaspberry Piは常時通電で、毎朝Philips hueと協力して天気予報装置として働いてくれているのですが、Web APIで取得した天気情報は今のところその場限りで捨ててしまっています。「せっかくだから記録しておいたら後で何かに使えるかも？」と思いたったので、Raspberry Pi上でデータベースを扱ってみることにしました。データベースについてはほぼ初心者なのですが、これについては先人が多くいるハズなので、おそらくそんなに困ることはないでしょう。
データベースもMySQLやらPostgreSQLやらMongoDBやらいろいろあると思いますが、ここでは一番簡単にインストールできそう且つメジャーなMySQLを使うことにします。主に以下のサイトを参考にさせていただいています。
インストールはこんな感じで。
$ sudo apt-get update $ sudo apt-get install mysql-server
簡単。インストール中にrootユーザ用のパスワードの入力を求められるので、とりあえずRaspberry Piへのログイン用と同じにしておきます（本当はちゃんと考えないといけないのかもしれませんが）。
早速DBにアクセスしてみます。パスワードは先ほど入力したroot用のパスワードを入力します。
$ 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) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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
mysqlクライアントに入ってから”status”で設定を確認しています。文字コード(上記でlatin1になっているところ)はUTF-8に統一しておいた方が日本語を扱うときにラクなようなので、UTF-8に変更します。これについては、こちらの手順に従って、”/etc/mysql/my.cnf”に以下のように追記していきます。
$ 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
設定を変更したらMySQLを再起動し、デフォルトの文字コードが変更されていることを確認します。
$ 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) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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>
確認できたので、データベースを作っていきます。以下では、現在存在しているデータベースを確認してから、”myhome_db”という名前のデータベースを作成して、確かに追加されているかどうかを確認しています。
$ 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) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql> CREATE DATABASE myhome_db CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | myhome_db | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql>
次に、使うDBを選択して、テーブルを作成します。今回は”weather_tb”という名称で、以下の項目を用意することにします。
- date DATE NOT NULL
- dow CHAR(3) NOT NULL
- time TIME NOT NULL
- weather VARCHAR(20) NOT NULL
良い主キーの設定方法についてはまだ勉強不足でよくわかっていないのですが、ここではdateとtimeの組み合わせで主キーとしたいと思います。
ではでは、実際に作ってみます。
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> SHOW COLUMNS FROM weather_tb;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| date | date | NO | PRI | NULL | |
| dow | char(3) | NO | | NULL | |
| time | time | NO | PRI | NULL | |
| weather | varchar(20) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql>
うまく作れたようなので、試しに適当なデータを突っ込んでみます。
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>
続いて、レコードの更新と削除を試します。
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>
更新と削除自体はうまくいったのですが、改めて結果を見てみると、テーブル名は”weather_tb”より”weather_forecast_tb”の方が良い気がしてきたので、テーブル名を変更します。
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>
これでとりあえずデータベースの準備と基本操作の確認できました。ちなみにデータベース用のコンソールを終了するときは、
mysql> quit;
でOKです。
次に、PythonからのMySQLデータベースへのアクセスを試してみます。RaspbianのデフォルトのPython（2.7系）ならMySQLdbを使うのが定番だったようですが、これは自分のPython環境である3.5系には対応していないので、代わりにPyMySQLを使います。
$ pip install PyMySQL
PyMySQLのページのサンプルをベースに、とりあえずテスト用に以下を作ってみました。
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()
実行すると、こんな感じになります。
$ python sample_mysql.py Data commited. 2016-06-19,Sun,10:44:56,雨
一応、MySQLクライアントの方でも確認してみます。
$ 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) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 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>
うん、OKです。
最後に、前に作ったhue天気予報のプログラムに上記のサンプルソースをマージします。本当に足しているだけです。
# 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")
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_today))
connection.commit()
print("Data commited.")
except:
print("DB Access Error!")
finally:
connection.close()
ちなみに、上記はPython 3.5系想定なので、Python2.7系のときは20行目の”weather_data[‘forecasts’][0][‘telop’]”の後に”.encode(‘utf-8′)”をつける必要があるところだけ注意です。
これで、毎回の天気予報の後にその日の天気予報の情報を残すことができるようになりました。せっかくなので、ここ一ヶ月ぐらい記録してきた結果を表示すると、こんな感じになります。
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)
こうやってみると、梅雨は確かに雨が多いなあというのはよくわかります。
とりあえず、DBを扱えるようになると工作の幅が広がると思いますので、覚えておいて損はないと思います。