aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authordennis <d@ennis.no>2023-02-06 20:27:30 +0100
committerdennis <d@ennis.no>2023-02-06 20:27:30 +0100
commitb1eab0da8b09397785692b1b0387e5157ad3d663 (patch)
tree2a4bcda4e38e57d06827484736e38ca431e3d36a
parentremove extra whitespace (diff)
downloadenergyscripts-b1eab0da8b09397785692b1b0387e5157ad3d663.tar.gz
insert more temperatures
-rw-r--r--scripts/mqtt_listener.py56
-rw-r--r--scripts/mqtt_watch.py3
-rw-r--r--scripts/neohub.py15
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())