２、 MySQLに気象観測のデータベースを作成
２−１、 データベースを作成 ///root権限///
・気象データを扱うデータベース：climateを設定
mysql> create database climate; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | climate | | mysql | | performance_schema | | phpmyadmin | | publications | | sys | | user | +--------------------+ 8 rows in set (0.02 sec)
２−２、 ユーザー”miyasan”に編集権限を与える ///root権限///
mysql> grant all on climate.* to 'miyasan'@'localhost' identified by 'ubu*********'; Query OK, 0 rows affected, 1 warning (0.01 sec)
２−３、 テーブル作成 /// miyasan権限 ///
・データベースの例題としてビニールハウスの温度データを使用
・データベース’climate’に、データテーブル’iida_agri_sensor’を作成する。
// データベース設計 //table name: iida_agri_sensor //
| field | type | コメント |
| date | DATETIME | 時間：ユニークインデックス |
| temp | FLOAT | 気温 |
| humid | FLOAT | 湿度 |
| press | FLOAT | 気圧 |
| t1 | FLOAT | 地温 |
| t2 | FLOAT | 地中温度 |
| soil | FLOAT | 水分濃度 |
| co_ppm | FLOAT | 二酸化炭素濃度 |
| lux | FLOAT | 照度 |
mysql> CREATE TABLE iida_agri_sensor(
-> date DATETIME,
-> temp FLOAT,
-> humid FLOAT,
-> press FLOAT,
-> t1 FLOAT,
-> t2 FLOAT,
-> soil FLOAT,
-> co_ppm FLOAT,
-> lux FLOAT
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+-------------------+
| Tables_in_climate |
+-------------------+
| iida_agri_sensor |
+-------------------+
1 row in set (0.00 sec)
mysql> describe iida_agri_sensor;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| date | datetime | YES | | NULL | |
| temp | float | YES | | NULL | |
| humid | float | YES | | NULL | |
| press | float | YES | | NULL | |
| t1 | float | YES | | NULL | |
| t2 | float | YES | | NULL | |
| soil | float | YES | | NULL | |
| co_ppm | float | YES | | NULL | |
| lux | float | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)
２−４、 dateフィールドをユニークインデックスに設定
これでphpMyAdminで行毎の削除・編集も可能。
mysql> ALTER TABLE iida_agri_sensor ADD UNIQUE(date);
Query OK, 0 rows affected (0.02 sec)
mysql> describe iida_agri_sensor;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| date | datetime | YES | UNI | NULL | |
| temp | float | YES | | NULL | |
| humid | float | YES | | NULL | |
| press | float | YES | | NULL | |
| t1 | float | YES | | NULL | |
| t2 | float | YES | | NULL | |
| soil | float | YES | | NULL | |
| co_ppm | float | YES | | NULL | |
| lux | float | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)
２−５、 データを読み込む 404557行
mysql> LOAD DATA LOCAL INFILE "/home/miyasan/esp12_agri_sensor.csv"
-> INTO TABLE iida_agri_sensor
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"';
Query OK, 404557 rows affected (4.42 sec)
Records: 404557 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM iida_agri_sensor LIMIT 10;
+---------------------+------+-------+-------+-------+------+------+--------+------+
| date | temp | humid | press | t1 | t2 | soil | co_ppm | lux |
+---------------------+------+-------+-------+-------+------+------+--------+------+
| 2020-03-19 09:48:59 | 25 | 55.4 | 960.3 | 16.31 | 8.81 | 388 | 607 | 6611 |
| 2020-03-19 09:50:03 | 25.7 | 55.2 | 960.2 | 16.25 | 8.81 | 388 | 628 | 6577 |
| 2020-03-19 09:51:07 | 26.1 | 55.1 | 960.2 | 17.19 | 8.81 | 388 | 642 | 6606 |
| 2020-03-19 09:52:12 | 26.5 | 54 | 960.1 | 17.94 | 8.88 | 387 | 659 | 6592 |
| 2020-03-19 09:53:26 | 26.9 | 54 | 960.1 | 18 | 8.88 | 387 | 653 | 6506 |
| 2020-03-19 09:54:31 | 27.4 | 53.6 | 960.1 | 17.31 | 8.88 | 387 | 660 | 6505 |
| 2020-03-19 09:55:35 | 27.5 | 48.6 | 960 | 17.12 | 8.94 | 383 | 673 | 6549 |
| 2020-03-19 09:56:39 | 27.6 | 51.1 | 960 | 17.12 | 8.94 | 383 | 677 | 6623 |
| 2020-03-19 09:57:44 | 27.8 | 51.5 | 960 | 16.31 | 8.94 | 383 | 690 | 6630 |
| 2020-03-19 09:58:48 | 28.1 | 51.7 | 960 | 16 | 8.94 | 383 | 696 | 6587 |
+---------------------+------+-------+-------+-------+------+------+--------+------+
10 rows in set (0.00 sec)
２−６、 期間を指定してデータを読み込む
mysql> SELECT * FROM iida_agri_sensor WHERE date BETWEEN '2020-07-01 00:00:00' AND '2020-07-01 23:59:59'; +---------------------+------+-------+-------+-------+-------+------+--------+------+ | date | temp | humid | press | t1 | t2 | soil | co_ppm | lux | +---------------------+------+-------+-------+-------+-------+------+--------+------+ | 2020-07-01 00:00:32 | 20.7 | 95.7 | 949 | 20.62 | 20.69 | 470 | 449 | 0 | | 2020-07-01 00:01:36 | 20.7 | 96 | 949 | 20.62 | 20.69 | 467 | 460 | 0 | | 2020-07-01 00:02:40 | 20.7 | 96.2 | 948.9 | 20.56 | 20.69 | 466 | 462 | 0 | | 2020-07-01 00:03:45 | 20.6 | 96.2 | 948.9 | 20.62 | 20.75 | 464 | 463 | 0 | | 2020-07-01 23:57:34 | 18.4 | 94.1 | 952.3 | 18.37 | 21.44 | 507 | 400 | 0 | | 2020-07-01 23:58:38 | 18.4 | 93.9 | 952.4 | 18.37 | 21.37 | 507 | 403 | 0 | | 2020-07-01 23:59:42 | 18.4 | 93.9 | 952.4 | 18.37 | 21.37 | 506 | 411 | 0 | +---------------------+------+-------+-------+-------+-------+------+--------+------+ 1251 rows in set (0.02 sec)
２−７、 期間を指定してデータを読み込み、csvファイルにエクポートする
mysql> SELECT * FROM iida_agri_sensor
-> WHERE date BETWEEN '2020-07-02 00:00:00' AND '2020-07-02 01:00:00'
-> INTO OUTFILE '/home/miyasan/agri.csv'
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"';
ERROR 1045 (28000): Access denied for user 'miyasan'@'localhost' (using password: YES)
しかしアクセス権限がないと蹴られる。 そこでroot権限でmiyasanの権限を設定し再度実行。
mysql> GRANT FILE ON *.* TO 'miyasan'@'localhost' IDENTIFIED BY '**********';
Query OK, 0 rows affected, 1 warning (0.01 sec)
miyasan権限；
mysql> SELECT * FROM iida_agri_sensor
-> WHERE date BETWEEN '2020-07-02 00:00:00' AND '2020-07-02 01:00:00'
-> INTO OUTFILE '/home/miyasan/agri.csv'
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
ところが今度は別のエラーメッセージが出た。調べてみると、「–secure-file-priv optionがON」になっているので、サーバーホスト上に書き込まれるが場所が指定されているようだ。書き込まれる場所は下記のコマンドで場所を確認できる。
mysql> SELECT @@global.secure_file_priv; +---------------------------+ | @@global.secure_file_priv | +---------------------------+ | /var/lib/mysql-files/ | +---------------------------+ 1 row in set (0.00 sec)
そこでmiyasan権限で/var/lib/mysql-files/agri.csvに出力してみると動作した。
mysql> SELECT * FROM iida_agri_sensor
-> WHERE date BETWEEN '2020-07-02 00:00:00' AND '2020-07-02 01:00:00'
-> INTO OUTFILE '/var/lib/mysql-files/agri.csv'
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"';
Query OK, 53 rows affected (0.03 sec)
/参考 MySQLのマニュアル
///https://dev.mysql.com/doc/refman/5.6/ja/select-into.html
SELECT ... INTO OUTFILE 'file_name' 形式は、選択された行をファイルに書き込みます。
このファイルはサーバーホスト上で作成されるため、この構文を使用するには FILE 権限が必要です。
file_name を既存のファイルにすることはできません。
これにより、特に /etc/passwd などのファイルやデータベーステーブルが破棄されることが回避されます。
character_set_filesystem システム変数は、ファイル名の解釈を制御します。
SELECT ... INTO OUTFILE ステートメントは、主に、テーブルをサーバーマシン上のテキストファイルに
非常にすばやくダンプできるようにすることを目的にしています。
結果として得られるファイルをサーバーホスト以外のホスト上で作成する場合は通常、
そのサーバーホストのファイルシステムを基準にしたファイルへのパスを記述する方法が存在しないため、
SELECT ... INTO OUTFILE は使用できません。
ただし、MySQL クライアントソフトウェアがリモートマシンにインストールされている場合は、
代わりに mysql -e "SELECT ..." > file_name などのクライアントコマンドを使用して
クライアントホスト上にファイルを生成できます。
MySQLマニュアルによると、クライアントコマンドを使用してクライアントホスト上にファイルを生成できるとのことなので、試してみた。
mysql -u miyasan climate -e "SELECT * FROM iida_agri_sensor WHERE date BETWEEN '2020-07-02 00:00:00' AND '2020-07-02 01:00:00'" > /home/miyasan/agri.csv -p
マニュアル通りコマンドは動作し ”/home/miyasan/agri.csv”が出力された。ただし出力ファイルはコンマデリミタではなくてタブデリミタである。データベースの内容に「”,”」や「” ” “」などの表現が含まれることを考えるとタブデリミタが合理的だろう。
XML形式は下記のオプション追加で出力可能
mysql -u miyasan climate -e "SELECT * FROM iida_agri_sensor WHERE date BETWEEN '2020-07-02 00:00:00' AND '2020-07-02 01:00:00'" --xml > /home/miyasan/agri1.xml -p
これらのクラインコマンドは、sqlコマンドをファイルにして読み込ませ、その結果を出力ファイルにリダイレクトすることも可能である。
// dataout.sql SELECT * FROM iida_agri_sensor WHERE date BETWEEN '2020-07-02 05:00:00' AND '2020-07-02 06:00:00'; linux端末から、 mysql -u miyasan climate < dataout.sql > agri2.csv -p
なお、データベースをバックアップと復元を実施するには、mysqldumpコマンドを使用する。
詳細は「初めてのPHP、MySQL、JavaScript＆CSS」の９章２２９ページを参照。
PHP・MySQL・canvasJSを使った動的なチャート生成 関連記事
４、 Pythonを使ったMySQLへのアクセスとクエリ設定
コメント