ホームページをリニューアルしました。旧HPはこちら
MySQL ソフト・ハードウェア Linux

PHP・MySQL・canvasJSを使った動的なチャート生成（その２）

２、　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を使った動的なチャート生成　関連記事

１、　ローカルサーバの開発環境の設定

２、　MySQLに気象観測データベースを作成

３、　PHPを使ったMySQLへのアクセスとクエリ設定

４、　Pythonを使ったMySQLへのアクセスとクエリ設定

５、　フォームからの送信データ取得と表示・ファイル保存

６、　MySQLデータを用いcanvasによるチャート作成

 

コメント

タイトルとURLをコピーしました