前回「外為で勝つために その1」の続きです。
為替レートが取得できたので、データベースに登録したいと思います。
以下のページを参考にしたというか、今回はほとんどパクリです。申し訳ない。
【参考】
ラズベリーパイにMariadbをインストールした時のメモ
Python3からMySQL繋ぐ時は、いろいろあるけどとりあえずPyMySQLにしとこうや
パッケージのインストール
MySQL 環境構築に必要なパッケージをインストールします。
pi@raspberrypi:~ $ sudo apt-get update
pi@raspberrypi:~ $ sudo apt-get install mysql-server
pi@raspberrypi:~ $ pip3 install PyMySQL
結局、MySQL をインストールしたのか、結果的に MariaDB を使わされているのかよく分からなかったんですが、これで MySQL の実行環境が整ったんだと自分を納得させました。
root パスワードの設定
そういえば、ラズパイで root パスワードなんて意識したことなかったです。
データベースを作成するために、サクッと設定しちゃいます。
pi@raspberrypi:~ $ sudo -i
root@raspberrypi:~# sudo /usr/bin/mysql_secure_installation
# rootに対してパスワードを設定していない場合はEnterで進む
Enter current password for root (enter for none):
# rootに対して新しいパスワードを設定する
Set root password? [Y/n] y
New password: (root のパスワードを設定)
Re-enter new password: (root のパスワードを設定)
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
データベースとユーザーの作成
root アカウントで作成します。root パスワードが必要なのはこのためだそうで。
root@raspberrypi:~# mysql
MariaDB [(none)]> USE mysql;
MariaDB [mysql]> SELECT user,password,plugin FROM user;
+------+-------------------------------------------+-------------+
| user | password | plugin |
+------+-------------------------------------------+-------------+
| root | ***************************************** | unix_socket |
+------+-------------------------------------------+-------------+
1 row in set (0.00 sec)
# unix_socket プラグインは外す
MariaDB [mysql]> UPDATE USER SET plugin='' WHERE user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> QUIT;
root@raspberrypi:~# exit
pi@raspberrypi:~ $ mysql -u root -p
Enter password: (root のパスワードを入力)
MariaDB [(none)]> USE mysql;
MariaDB [mysql]> CREATE DATABASE fx;
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> CREATE USER 'fxpi'@'localhost' IDENTIFIED BY '(fxpi のパスワードを設定)';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> GRANT ALL PRIVILEGES ON fx.* TO 'fxpi'@'localhost';
Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SELECT user,password,plugin FROM user;
+------+-------------------------------------------+--------+
| user | password | plugin |
+------+-------------------------------------------+--------+
| root | ***************************************** | |
| fxpi | ***************************************** | |
+------+-------------------------------------------+--------+
2 rows in set (0.00 sec)
MariaDB [mysql]> QUIT;
データベース「fx」と、そのユーザー「fxpi」を作成します。
Query OK, 0 rows affected となってますが、結果はちゃんと反映されてます。
テーブルの作成
為替レートを記録するテーブルを作成します。
- time 日時（日時型）
- instrument 通貨ペア（可変長文字列型）
- open 始値（不動小数点型）
- high 高値（不動小数点型）
- low 安値（不動小数点型）
- close 終値（不動小数点型）
とりあえず全カラム Null 許容とします。
pi@raspberrypi:~ $ mysql -u fxpi -p
Enter password: (fxpi のパスワードを入力)
MariaDB [(none)]> USE fx;
MariaDB [fx]> CREATE TABLE tbl_candle (time DATETIME, instrument VARCHAR(7), open FLOAT, high FLOAT, low FLOAT, close FLOAT);
Query OK, 0 rows affected (0.07 sec)
MariaDB [fx]> DESCRIBE tbl_candle;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| time | datetime | YES | | NULL | |
| instrument | varchar(7) | YES | | NULL | |
| open | float | YES | | NULL | |
| high | float | YES | | NULL | |
| low | float | YES | | NULL | |
| close | float | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
Python コード
差分だけ載せます。
コードの全文は公開中の GitHub をご参照ください。
import pymysql.cursors
データベースに接続するためのモジュールをインポートして、
# MySQL settings
host="localhost"
user="fxpi"
password="## fxpi's password ##"
db="fx"
charset="utf8mb4"
接続に必要な情報を設定しておきます。
# DB connection
conn = pymysql.connect(
host=host,
user=user,
password=password,
db=db,
charset=charset,
cursorclass=pymysql.cursors.DictCursor
)
# Insert DB
try:
for raw in response["candles"]:
with conn.cursor() as cursor:
sql = "INSERT INTO tbl_candle (time, instrument, open, high, low, close) "
sql += "VALUES(%s, %s, %s, %s, %s, %s)"
cursor.execute(sql, (
raw["time"].replace("000Z", "").replace("T", " "),
response["instrument"],
raw[price]["o"],
raw[price]["h"],
raw[price]["l"],
raw[price]["c"]
))
conn.commit()
finally:
conn.close()
実際に接続して INSERT 文を実行します。
実行時にエラーがあっても、今のコードでは無視して finally に進みます。
finally でデータベースから切断します。
1行挿入するごとにデータベースに接続・切断しているので若干非効率的ですが、ある1行で問題が発生して後続行が全て未処理になるよりかは幾分マシかなと考えてます。
raw["time"] を2箇所置き換えしてますが、
MySQL が「日時のフォーマットは適当に解釈しておいたよ」という WARNING を出していたのでその対策です。これで良いのかはよく分かりませんが、ともかく WARNING は消えました。
出力結果
pi@raspberrypi:~ $ mysql -u fxpi -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 284
Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE fx;
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
MariaDB [fx]> SELECT * FROM tbl_candle WHERE instrument="USD_JPY";
+---------------------+------------+---------+---------+---------+---------+
| time | instrument | open | high | low | close |
+---------------------+------------+---------+---------+---------+---------+
| 2019-12-06 14:30:00 | USD_JPY | 108.821 | 108.856 | 108.8 | 108.802 |
| 2019-12-06 14:35:00 | USD_JPY | 108.805 | 108.815 | 108.777 | 108.796 |
| 2019-12-06 14:40:00 | USD_JPY | 108.799 | 108.809 | 108.777 | 108.784 |
+---------------------+------------+---------+---------+---------+---------+
3 rows in set (0.01 sec)
無事にローソク足の情報がデータベースに書き込まれました。
これはもうウェブサイトを作ってローソク足を表示させるしかなさそうですね。
続く。