やりたいこと

テーブルに格納されたレコードのうち、指定した件数のレコードを削除したい。

MySQLの場合

LIMIT句を使います。

-- itemsテーブルから10件のレコードを削除する
DELETE FROM items LIMIT 10;

PostgreSQLの場合

PostgreSQLではDELETE文でLIMIT句が使えないので、サブクエリを使います。

-- itemsテーブルから10件のレコードを削除する
-- "id" はテーブル内で一意の値を持つ任意のカラム名
DELETE FROM items WHERE id IN (SELECT id FROM items LIMIT 10);

備考

SQLはORDER BY句で順序を指定しない限り、表示順は担保されません。上記のクエリも、削除順を指定するにはORDER BY句で指定する必要があります。

参考

• PostgreSQL: Re: using limit with delete
• MySQL :: MySQL 8.0 Reference Manual :: 13.2.2 DELETE Statement
• DELETE - PostgreSQL 11.5文書

前回は液晶ディスプレイの省エネ設計にトライしました。
今回は温度計と湿度計を追加して、部屋の温度と湿度をモニタリングします。

温度計は以前、組み込んだことがあります。
【Python】Arduino で温度センサーから室温を取得して画面に表示する（前編）
【Python】Arduino で温度センサーから室温を取得して画面に表示する（後編）

ただ、独立した湿度計が難解でした。
資料も少なく自分にはちょっと敷居が高かったので、
今回は少し難易度を下げてみます。

難易度低めでポピュラーな温湿度センサーがあったので、これを使ってみようと思います。
温度計がセットなので、前回の温度計はお蔵入りにして新しく設計します。

材料

• 温湿度センサ モジュール DHT11
• 10kΩ 抵抗1本

材料はこれだけ。前回使用した Arduino 回路に追加します。
複雑な設計はモジュールに組み込み済みなので、作業は楽ですね。

回路作成

今回参考にさせてもらったページ
DHT11 を用いた温度と湿度の計測 - Arduino - 基礎からの IoT 入門
https://iot.keicode.com/arduino/temperature-dht11.php

上図の DHT11（水色のパーツ）は表裏逆になってます。
左から [GND] [NC] [DATA] [VDD] になるのでご注意ください。

出力は参考ページと同じデジタル 8pin にしました。
オレンジ、イエローの配線は 5V、GND はブラックです。
回路図はシンプルにできたんですが、リアル回路はちょっと混み合ってきました。

この後、気圧計も追加する予定なんですが、どうなることやら。

Arduino プログラムコード

#include <DHT.h>

// Initializing DHT11 sensor
const int PIN_DHT = 8;
DHT dht(PIN_DHT, DHT11);

void setup() {

  //start serial connection
  Serial.begin(9600);

  //configure pin A3 as an input and enable the internal resistor
  pinMode(A3, INPUT);

  // start DHT11 sensor
  dht.begin();

}

void loop() {

  // Whether to display in Fahrenheit
  bool isFahrenheit = false;

  // Get humidity and temperature
  float percentHumidity = dht.readHumidity();
  float temperature = dht.readTemperature(isFahrenheit);

  // Do nothing if not available
  if (isnan(percentHumidity) || isnan(temperature)) {
    return;
  }

  // Get heat index
  float heatIndex = dht.computeHeatIndex(
    temperature,
    percentHumidity,
    isFahrenheit
  );

  //read the photocell value into a variable
  int photocell = analogRead(A3);

  //print out the sensor value in csv format
  String s = "";
  s += String(temperature, 1) + ",";
  s += String(percentHumidity, 1) + ",";
  s += String(heatIndex, 1) + ",";
  s += String(photocell);
  Serial.println(s);

  //delay 2000 ms
  delay(2000);

}

追加したコードはほとんどが DHT11 ライブラリの操作です。
温度は摂氏で出したいので bool isFahrenheit = false; としています。

あと、処理が込み入ってきたので2秒間隔のループとしました。
念のためです。

デジタル 8pin から読み込み、前回の照度センサーの値も含め CSV 形式で出力します。

DHT ライブラリの追加

DHT ライブラリは Arduino IDE のライブラリマネージャーから追加できます。
メニューから「ツール → ライブラリを管理」です。

DHT sensor library by Adafruit
スクリーンショットでは Ver.1.3.8 をインストールしました。

Adafruit Unified Sensor by Adafruit
共通ライブラリです。この時点では Ver.1.1.2 をインストールしています。

データベースとユーザーとテーブルを作成

今回からセンサー情報をデータベースに書き込みます。

シリアル接続は複数同時に行うことができないため、
（何か方法はあるのでしょうが、費用がかからない方法として）
データベースに書き込むプログラムを動かし続けて、他のプログラムがデータベースを参照することで情報を共有します。

$ mysql -u root -praspberry 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
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)]> CREATE DATABASE sensor;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE USER 'sensorpi'@'localhost' IDENTIFIED BY 'raspberry';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON sensor.* TO 'sensorpi'@'localhost';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> USE mysql;
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 [mysql]> SELECT user, password, plugin FROM user;
+------------+-------------------------------------------+--------+
| user       | password                                  | plugin |
+------------+-------------------------------------------+--------+
| root       | ***************************************** |        |
| phpmyadmin | ***************************************** |        |
| fxpi       | ***************************************** |        |
| sensorpi   | ***************************************** |        |
+------------+-------------------------------------------+--------+
4 rows in set (0.01 sec)

MariaDB [mysql]> QUIT;
Bye
pi@raspberrypi:~ $ mysql -u sensorpi -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 34
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 sensor;
Database changed
MariaDB [sensor]> CREATE TABLE tbl_serval (time DATETIME,
    -> max_temperature FLOAT, avg_temperature FLOAT, min_temperature FLOAT,
    -> max_humidity FLOAT, avg_humidity FLOAT, min_humidity FLOAT,
    -> max_heatIndex FLOAT, avg_heatIndex FLOAT, min_heatIndex FLOAT,
    -> max_pressure INT, avg_pressure INT, min_pressure INT,
    -> max_photocell INT, avg_photocell INT, min_photocell INT);
Query OK, 0 rows affected (0.05 sec)

項目	設定値
データベース	sensor
ユーザー	sensorpi
テーブル	tbl_serval

tbl_serval の構造はこんな感じ。
NULL は全カラム許容してますが、そのうち見直します。

serval は SERial VALue の略です。

列名	型	NULL	説明
time	DATETIME	許容	記録時刻
max_temperature	FLOAT	許容	温度（最大）
avg_temperature	FLOAT	許容	温度（平均）
min_temperature	FLOAT	許容	温度（最小）
max_humidity	FLOAT	許容	湿度（最大）
avg_humidity	FLOAT	許容	湿度（平均）
min_humidity	FLOAT	許容	湿度（最小）
max_heatIndex	FLOAT	許容	体感温度（最大）
avg_heatIndex	FLOAT	許容	体感温度（平均）
min_heatIndex	FLOAT	許容	体感温度（最小）
max_pressure	INT	許容	気圧（最大）
avg_pressure	INT	許容	気圧（平均）
min_pressure	INT	許容	気圧（最小）
max_photocell	INT	許容	照度（最大）
avg_photocell	INT	許容	照度（平均）
min_photocell	INT	許容	照度（最小）

Python プログラムコード

あちこち修正したので、概要だけ。
変更点については、ご興味あれば GitHub(mySensor) & GitHub(DesktopClock) を覗いてみてください。

• 2秒ごとにセンサー情報（CSV 形式）を受信して分割
• 1分間で集計して最大・平均・最小値を算出
• データベースに書き込む
• 置き時計プログラム（DesktopClock）側をシリアル接続から DB 接続に変更
• 1分ごとに DB から最新の値を取得して画面に表示する

あともう少しで完成ですね。

小ネタ

照度が 50 以下のときに、バックライトを消灯する仕組みにしていたのですが
今回の変更のあと、部屋を暗くしてもすぐに消えず、しばらく経ってから消灯するなど、やたら反応が遅くなりました。
Python プログラムを再起動すると直りますが、ちょっとするとまた再発するという感じです。

イロイロ調べた結果、受信バッファが原因だったようです。

main.py

# 受信バッファをフラッシュする
self.ser.flushInput()

これをしないと、シリアル接続と同期が取れなくなって
時間経過とともに遅延の幅が大きくなるらしいのです。

イマイチ、ピンとこない感じですが遅延は無くなったのでそういうことなのでしょう。

よく忘れがちなコマンドのメモ。

SQLモードの確認

mysql> select @@global.sql_mode \G
*************************** 1. row ***************************
@@global.sql_mode: NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

Hostとユーザ設定の確認

mysql> select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | dev01         |
| %         | dev02         |
| localhost | development   |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
6 rows in set (0.00 sec)

ユーザの権限確認

mysql> SHOW GRANTS FOR 'development'@"%";

mysqldump について個人的なメモをまとめておきます

日本語の公式ドキュメントが 5.6 のバージョンで止まっている

日本語の方はすべて 5.6 にリダイレクトされてしまいます
https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html

英語の方はすべてのバージョンを確認できます！
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

基本的な呼び出し構文

# DB、 DB の Tables
mysqldump [options] db_name [tbl_name ...]

# 複数の DB
mysqldump [options] --databases db_name ...

# すべての DB
mysqldump [options] --all-databases

よく使うコマンド

基本的に DB ユーザーの見れる範囲のバックアップをとることが多いのかなぁ
なんて思ってるので --all-databases にしてます
※ MySQL 5.7 以降はデフォルトで InnoDB が選択されているため、他の DB エンジンのことは考慮してません。

mysqldump -h ${HOST_NAME} -P ${PORT} -u${USER_NAME} -p${USER_PASSWORD} --skip-lock-tables --single-transaction --all-databases > ${OUTPUT_FILE}

デフォルトで --opt が有効になっている

このオプションは、複数のオプションを組み合わせた省略形です。
高速なダンプ操作ができるようになっています。もし無効にしたい場合は、--skip-opt を指定します。
下記は組み合わせられているオプションたちです。

オプション名	説明
--add-drop-table	各 CREATE TABLE の前に DROP TABLE を書く。
--add-locks	LOCK TABLES と UNLOCK TABLES で各テーブルダンプを囲む。これによって、インサートが高速化される。
--create-options	CREATE TABLE にすべての MySQL 固有のテーブルオプションを含める。
--disable-keys	各テーブルで、INSERT を ENABLE KEYS と DISABLE KEYS で囲む。これにより、すべての行が挿入された後にインデックスが作成されるため、ダンプファイルの読み込みが高速になる。
--extended-insert	複数行の INSERT 構文 VALUES を利用して、ダンプファイルが小さくなり、ファイルのリロード時のインサートが高速化される。
--lock-tables	各 DB で、 ダンプされる前にすべてのテーブルをロックする。InnoDB などのトランザクションが使えるものでは --single-transaction の方が優秀。それぞれの DB のテーブルごとにロックするため、一貫性が保証されない(異なる DB の場合はさらに)。無効にしたい場合、--skip-lock-tables を追加する。
--quick	大きなテーブルのダンプに役立つ。record 全体を取得してメモリにバッファリングしてから書き出すのではなく、サーバーからテーブルの行を一度に1行ずつ取得するようにmysqldumpを強制する。
--set-charset	default_character_set を出力に書き込む。無効にしたい場合、--skip-set-charset を追加する。

その他のよく使うオプション

オプション名	説明
--single-transaction	データをダンプする前に、トランザクション分離モードを REPEATABLE READ に設定し、START TRANSACTION SQL ステートメントをサーバーに送信する。MyISAM または MEMORY のテーブルでは一貫性が保証されないことには注意。
--skip-lock-tables	--lock-tables を無効にする。
--skip-opt	--opt を無効にする。
--flush-logs	ダンプを開始する前に MySQL サーバーのログファイルをフラッシュする。
--lock-all-tables	DB 内のテーブル全てをロックする。
--master-data	バイナリログファイルの名前と場所を追記する。自動的に --lock-tables が無効になり、--single-transaction が指定されてない場合は、--lock-all-tables が有効になる。
--no-data	テーブルの中身をダンプしない。定義のみダンプしたい時に指定する。
--no-create-db	CREATE DATABASE を入れない。
--no-create-info	CREATE TABLE を入れない。

おわりに

他に何かご存知の方がいらっしゃったら
コメントなり編集リクエストなりで教えていただけると幸いです