ゎぃゎぃのおもちゃ箱

一般的な大学生の遊び場所

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

f:id:yy_kuso:20190803032409p:plain

はじめに

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

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");

1ユーザあたり複数の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分で書いてます.半年前の実行メモを見ながら適当にコピペしてるだけなので,間違っている点が沢山あると思いますが,あまり突っ込まないでください.