2号機で設定
１．人がいる時にMariaDBのテーブルに人がいることを書き込む。 kenchi_man.py で保存。
１）本体 Dbのテーブル更新は サブ mariadb_upd.py で更新させる。
2025/1/12 カメラとAIで人を検知させることにした。 で コードは以下ＵＲＬ参照。
#!/usr/bin/ python3
from datetime import datetime
import time
import mariadb_upd
import RPi.GPIO as GPIO
INTERVAL = 3
SLEEPTIME = 20
GPIO_PIN = 18
GPIO.setmode(GPIO.BCM)
GPIO.setup(GPIO_PIN, GPIO.IN)
# 時間計測開始
t_start = time.perf_counter()
if __name__ == '__main__':
try:
while True:
# 現在時刻取得
t_end = time.perf_counter()
# 経過時間計算
elaps = t_end - t_start
# ３分超えたらブレーク
if elaps > 180:
break
print(elaps)
# 人検知
cnt = 1
if(GPIO.input(GPIO_PIN) == GPIO.HIGH):
# query table update run 人検知
updsw = '1'
mariadb_upd.sw(updsw)
print(datetime.now().strftime('%Y/%m/%d %H:%M:%S') +
":" + str("{0:05d}".format(cnt)) + " human look")
time.sleep(SLEEPTIME)
break
else:
# query table update run 人未検知
updsw = '0'
mariadb_upd.sw(updsw)
print(GPIO.input(GPIO_PIN))
print("no look")
time.sleep(INTERVAL)
finally:
GPIO.cleanup()
print("GPIO clean完了")
２）サブ mariadb_upd.py で保存。
#!/usr/bin/ python3
import mysql.connector
def sw(updsw):
# mysql connect
cnt = mysql.connector.connect(
host='miyamodb',
port='3306',
db='miyamodb',
user='miyamo',
password='miyamoXXXX',
charset='utf8'
)
db = cnt.cursor(buffered=True)
# select
sql = 'SELECT * FROM jinkan_kenchi';
db.execute(sql)
# display
rows = db.fetchall()
print(' jinkan_kenchi mae：',rows)
# update sw = 0
if updsw == '1':
sql = 'UPDATE jinkan_kenchi SET kenchisw = 1, upd_datetime = now() where id = 1';
db.execute(sql)
sql = 'commit';
db.execute(sql)
# select
sql = 'SELECT * FROM jinkan_kenchi';
db.execute(sql)
# display
rows = db.fetchall()
print(' jinkan_kenchi upd：',rows)
else:
# query table update run 人未検知
sql = 'UPDATE jinkan_kenchi SET kenchisw = 0, upd_datetime = now() where id = 1';
db.execute(sql)
sql = 'commit';
db.execute(sql)
# select
sql = 'SELECT * FROM jinkan_kenchi';
db.execute(sql)
# display
rows = db.fetchall()
print(' jinkan_kenchi upd：',rows)
# db.close
db.close()
# MySQLclose
cnt.close()
if __name__ == "__main__":
sw()
２．実行
python3 kenchi_man.py
結果
4.114999683224596e-06
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 31, 29))]
jinkan_kenchi upd： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 10))]
0
no look
3.04372678099935
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 10))]
jinkan_kenchi upd： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 13))]
0
no look
6.072160542000347
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 13))]
jinkan_kenchi upd： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 16))]
0
no look
9.098045570999602
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 16))]
jinkan_kenchi upd： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 19))]
0
no look
12.125964961000136
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 19))]
jinkan_kenchi upd： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 22))]
0
no look
15.150481561999186
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 22))]
jinkan_kenchi upd： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 25))]
0
no look
18.177010336999956
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 25))]
jinkan_kenchi upd： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 28))]
0
no look
21.202384484000504
jinkan_kenchi mae： [(1, '0', datetime.datetime(2025, 1, 10, 21, 33, 28))]
jinkan_kenchi upd： [(1, '1', datetime.datetime(2025, 1, 10, 21, 33, 31))]
2025/01/10 21:33:31:00001 human look
GPIO clean完了
更新結果 一応確認
pi@raspberrypi:~ $ mysql -h miyamodb -u miyamo -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 127
Server version: 10.5.26-MariaDB-0+deb11u2 Debian 11
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 miyamodb;
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 [miyamodb]> SELECT * FROM jinkan_kenchi where id = 1;
+----+----------+---------------------+
| id | kenchisw | upd_datetime |
+----+----------+---------------------+
| 1 | 1 | 2025-01-10 21:33:31 |
+----+----------+---------------------+
1 row in set (0.002 sec)
MariaDB [miyamodb]> quit
だいぶ python＆mariaDb理解できてきた。
３．Cron登録。7時に1号機でエアコンオンするので6時50分で実行。
sudo crontab -e
中身は以下
50 6 * * * /usr/bin/python3 /home/pi/kenchi_man.py > /tmp/cronm.log 2>&1