ラズパイ　赤外線でエアコンの消し忘れを自動で消す、人がいる時にエアコンの暖房自動ONを実現　ステップ２

# 2号機で設定
１．人がいる時にMariaDBのテーブルに人がいることを書き込む。　kenchi_man.py で保存。
１）本体　Dbのテーブル更新は　サブ　mariadb_upd.py　で更新させる。
# 2025/1/12　カメラとAIで人を検知させることにした。　で　コードは以下ＵＲＬ参照。
https://qiita.com/m_sunafukin77/items/291f101f88b31a3b008b
```
#!/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