diff options
| -rw-r--r-- | mqtt_temps.py | 67 | ||||
| -rw-r--r-- | mqtt_temps.service | 19 | ||||
| -rw-r--r-- | requirements.txt | 6 | ||||
| -rwxr-xr-x | run.sh | 7 | ||||
| -rw-r--r-- | schema.sql | 51 |
5 files changed, 142 insertions, 8 deletions
diff --git a/mqtt_temps.py b/mqtt_temps.py new file mode 100644 index 0000000..bda7b0f --- /dev/null +++ b/mqtt_temps.py @@ -0,0 +1,67 @@ +#!/usr/bin/env python3 + +import os +import sys +import json +import psycopg2 +import paho.mqtt.client as mqtt + +mqtt_server = os.environ['el_mqtt_server'] +mqtt_port = int(os.environ['el_mqtt_port']) +keepalive = int(os.environ['el_mqtt_keepalive']) +mqtt_topic = os.environ['el_mqtt_topic'] + +pg_db = os.environ['el_pg_db'] +pg_host = os.environ['el_pg_host'] +pg_user = os.environ['el_pg_user'] +pg_pass = os.environ['el_pg_pass'] +pg_table = "mqtt_temps" + + +# The callback for when the client receives a CONNACK response from the server. +def on_connect(client, userdata, flags, rc): + print("Connected with result code "+str(rc)) + + # Subscribing in on_connect() means that if we lose the connection and + # reconnect then subscriptions will be renewed. + client.subscribe(mqtt_topic) + +# The callback for when a PUBLISH message is received from the server. +def on_message(client, userdata, msg): + if msg.topic.startswith('zigbee2mqtt/tmp'): + #print("TMP!! - " + msg.topic.split('/')[1]) + data = json.loads(msg.payload) + if 'temperature' in data and 'humidity' in data: + values = (msg.topic.split('/')[1], data['temperature'], data['humidity'], data['battery'], data['linkquality'], data['voltage']) + print(values) + todb(values) + +# Write values to database +def todb(values): + conn = psycopg2.connect(database=pg_db, host=pg_host, user=pg_user, password=pg_pass) + cur = conn.cursor() + try: + cur.execute("INSERT INTO " + pg_table + " (name, temperature, humidity, battery, linkquality, voltage) VALUES(%s,%s,%s,%s,%s,%s)", values) + conn.commit() + except (Exception, psycopg2.Error) as error: + conn.rollback() + print("Error while connecting to PostgreSQL", error) + raise error + finally: + cur.close() + conn.close() + + + +# mqtt +client = mqtt.Client() +client.on_connect = on_connect +client.on_message = on_message + +client.connect(mqtt_server, mqtt_port, keepalive) + +# Blocking call that processes network traffic, dispatches callbacks and +# handles reconnecting. +# Other loop*() functions are available that give a threaded interface and a +# manual interface. +client.loop_forever() diff --git a/mqtt_temps.service b/mqtt_temps.service new file mode 100644 index 0000000..d6a859e --- /dev/null +++ b/mqtt_temps.service @@ -0,0 +1,19 @@ +[Unit] +Description=Simple service to start python-listener +StartLimitIntervalSec=100 +StartLimitBurst=5 + +[Service] +Type=simple +EnvironmentFile=%h/energyscripts/.env +WorkingDirectory=%h/energyscripts +ExecStart=%h/energyscripts/venv/bin/python3 -u mqtt_temps.py +Restart=on-failure +TimeoutStopSec=70 +RestartSec=30 +SyslogIdentifier=mqtt_temps +#StandardOutput=journal +#StandardError=journal + +[Install] +WantedBy=default.target diff --git a/requirements.txt b/requirements.txt index 43dc381..aa7e4a9 100644 --- a/requirements.txt +++ b/requirements.txt @@ -3,13 +3,9 @@ certifi==2022.12.7 charset-normalizer==3.0.1 idna==3.4 neohubapi==1.0 +paho-mqtt==1.6.1 psycopg2-binary==2.9.5 -python-dateutil==2.8.2 -pytz-deprecation-shim==0.1.0.post0 requests==2.28.2 -six==1.16.0 -tzdata==2022.7 -tzlocal==4.2 urllib3==1.26.14 websockets==10.4 xmltodict==0.13.0 @@ -2,7 +2,8 @@ 0="${${ZERO:-${0:#$ZSH_ARGZERO}}:-${(%):-%N}}" 0="${${(M)0:#/*}:-$PWD/$0}" - +setopt all_export source ${0:h}/.env -source ${0:h}/venv/bin/activate -python3 ${0:h}/$1 +unsetopt all_export + +${0:h}/venv/bin/python3 ${0:h}/$1 @@ -66,6 +66,42 @@ CREATE TABLE public.entsoe ( -- +-- Name: mqtt_temps; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.mqtt_temps ( + id integer NOT NULL, + name text NOT NULL, + temperature real, + humidity real, + battery smallint, + linkquality smallint, + voltage integer, + "time" timestamp with time zone DEFAULT now() NOT NULL +); + + +-- +-- Name: mqtt_temps_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE public.mqtt_temps_id_seq + AS integer + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: mqtt_temps_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE public.mqtt_temps_id_seq OWNED BY public.mqtt_temps.id; + + +-- -- Name: nbex; Type: TABLE; Schema: public; Owner: - -- @@ -164,6 +200,13 @@ CREATE TABLE public.yr ( -- +-- Name: mqtt_temps id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.mqtt_temps ALTER COLUMN id SET DEFAULT nextval('public.mqtt_temps_id_seq'::regclass); + + +-- -- Name: neohub id; Type: DEFAULT; Schema: public; Owner: - -- @@ -195,6 +238,14 @@ ALTER TABLE ONLY public.entsoe -- +-- Name: mqtt_temps mqtt_temps_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.mqtt_temps + ADD CONSTRAINT mqtt_temps_pkey PRIMARY KEY (id); + + +-- -- Name: nbex nbexchange_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- |