diff options
author | dennis <d@ennis.no> | 2023-02-06 20:27:30 +0100 |
---|---|---|
committer | dennis <d@ennis.no> | 2023-02-06 20:27:30 +0100 |
commit | b1eab0da8b09397785692b1b0387e5157ad3d663 (patch) | |
tree | 2a4bcda4e38e57d06827484736e38ca431e3d36a | |
parent | remove extra whitespace (diff) | |
download | energyscripts-b1eab0da8b09397785692b1b0387e5157ad3d663.tar.gz |
insert more temperatures
-rw-r--r-- | scripts/mqtt_listener.py | 56 | ||||
-rw-r--r-- | scripts/mqtt_watch.py | 3 | ||||
-rw-r--r-- | scripts/neohub.py | 15 |
3 files changed, 69 insertions, 5 deletions
diff --git a/scripts/mqtt_listener.py b/scripts/mqtt_listener.py index 85d27a1..1920824 100644 --- a/scripts/mqtt_listener.py +++ b/scripts/mqtt_listener.py @@ -27,12 +27,60 @@ def on_message(client, userdata, msg): data = json.loads(msg.payload) if name.startswith('tmp') and 'temperature' in data and 'humidity' in data: - sql = "INSERT INTO mqtt_temps (name, temperature, humidity, battery, linkquality, voltage, time) VALUES(%s,%s,%s,%s,%s,%s,%s)" - values = (name, data['temperature'], data['humidity'], data['battery'], data['linkquality'], data['voltage'], datetime.utcnow()) + sql = """INSERT INTO mqtt_temps + (name, + temperature, + humidity, + battery, + linkquality, + voltage, + time, + room) + SELECT %s, %s, %s, %s, %s, %s, %s, sensors.room + FROM sensors + WHERE sensors.name = %s""" + values = (name, + data['temperature'], + data['humidity'], + data['battery'], + data['linkquality'], + data['voltage'], + datetime.utcnow(), + name) + elif name == 'HAN' and 'current' in data: - sql = "INSERT INTO mqtt_han (name, current, power, voltage, linkquality, time) VALUES(%s,%s,%s,%s,%s,%s)" - values = (name, data['current'], data['power'], data['voltage'], data['linkquality'], datetime.utcnow()) + sql = """INSERT INTO mqtt_han + (name, + current, + power, + voltage, + linkquality, + time) + VALUES(%s,%s,%s,%s,%s,%s)""" + values = (name, + data['current'], + data['power'], + data['voltage'], + data['linkquality'], + datetime.utcnow()) + + elif name == 'toiletmotion' and 'temperature' in data: + sql = """INSERT INTO mqtt_temps + (name, + temperature, + battery, + linkquality, + time, + room) + SELECT %s, %s, %s, %s, %s, sensors.room + FROM sensors + WHERE sensors.name = %s""" + values = (name, data['temperature'], + data['battery'], + data['linkquality'], + datetime.utcnow(), + name) else: return diff --git a/scripts/mqtt_watch.py b/scripts/mqtt_watch.py index 775b84a..371debd 100644 --- a/scripts/mqtt_watch.py +++ b/scripts/mqtt_watch.py @@ -2,6 +2,7 @@ ''' Listen for mqtt-events, and trigger for some ''' import os +from datetime import datetime import paho.mqtt.client as mqtt import common @@ -22,7 +23,7 @@ def on_connect(client, userdata, flags, rc): # The callback for when a PUBLISH message is received from the server. def on_message(client, userdata, msg): - print(msg.topic, msg.payload) + print(msg.topic, datetime.utcnow(), msg.payload) #if name.startswith('tmp') and 'temperature' in data and 'humidity' in data: # sql = "INSERT INTO mqtt_temps (name, temperature, humidity, battery, linkquality, voltage, time) VALUES(%s,%s,%s,%s,%s,%s,%s)" diff --git a/scripts/neohub.py b/scripts/neohub.py index e5c1b16..4fd3a16 100644 --- a/scripts/neohub.py +++ b/scripts/neohub.py @@ -46,4 +46,19 @@ async def run(): common.dbi(sql, values) + + sql = """INSERT INTO mqtt_temps + (name, + temperature, + time, + room) + SELECT 'neohub', %s, %s, sensors.room + FROM sensors + WHERE sensors.name = 'neohub'""" + values = (device.temperature, datetime.utcnow()) + + common.dbi(sql, values) + + + asyncio.run(run()) |