Suicaなどの交通系ICでバリスタ利用管理 SuiCafe ②

はじめに

前回の記事を書いてから大分経ってしまいましたが，いろいろ更新したので，やったことだけざっと書き残しておきます．

MySQLを導入

前回まではドリンク回数管理をスプレッドシート側で行っており，月ごとにドリンク代を生産する際に手動で計算をしていました．

あまりにも面倒くさい上に，この仕様だとslack側に”誰が飲んだか”を通知できないでいました．

そこでMySQLを導入してちゃんとしたデータ管理をしてみたいと思います．

MySQLインストール

$ sudo apt-get install mysql-server

rootパスワードの入力して完了です．

SuiCafe用DBの準備

$ sudo mysql -uroot
MariaDB [(none)]> use mysql;
MariaDB [(none)]> update user set password=PASSWORD("mypassword") where User='root';
MariaDB [(none)]> update user set plugin="mysql_native_password";
MariaDB [(none)]> quit;
$ sudo /etc/init.d/mysql restart
$ mysql -u root -p
Enter password:
$ sudo mysql -uroot
> create user 'waiwai'@'localhost' identified by 'my_password';
> create database suicafe;
> use suicafe
> grant all privileges on suicafe.* to 'waiwai'@'localhost';
> CREATE TABLE `drink_log` ( `timestamp` timestamp NOT NULL COMMENT "TimeStamp", `id` CHAR(44) NOT NULL COMMENT "UserID");
> CREATE TABLE `users` (`id` CHAR(44) NOT NULL PRIMARY KEY COMMENT "UserID", `name` VARCHAR(20) NOT NULL COMMENT "UserName");

データの移行

スプレッドシートで記録されたドリンクデータをDB側に持ってきます．
なお，だるかったので，直接ぶち込みました．

> INSERT INTO drink_log (timestamp, id) VALUES 
("2018-11-01 19:39:11", "P4hlK======================================="),
("2018-11-02 13:52:59", "P4hlK======================================="),
…(続く) 
("2019-03-18 14:28:27", "44QM=======================================");


> INSERT INTO users (id, name) VALUES
("P4hlK=======================================", "waiwai"),
…(続く) 
("2ytlX=======================================", "roamer7038");

１ユーザあたり複数のidを持てるように調整しました．

月ごとのドリンク回数を表示する

条件に合うSQL文を頑張って作り出す．

> SELECT name, count(name) as count FROM drink_log INNER JOIN users 
ON drink_log.id = users.id where date_format(timestamp, '%Y-%m') = "2019-01"
GROUP BY name ORDER BY count DESC;
+------------+-------+
| name       | count |
+------------+-------+
| waiwai     |    12 |
| ya#######  |     4 |
| roamer7038 |     1 |
+------------+-------+

PythonからMySQLを操作できるようにする

$ sudo apt-get install default-libmysqlclient-dev
$ sudo pip install MySQL-python

成果物

完成したデータはGitHubにあげておきました．

最後に

この記事は30分で書いてます．半年前の実行メモを見ながら適当にコピペしてるだけなので，間違っている点が沢山あると思いますが，あまり突っ込まないでください．