Wie zeichne ich die Temperaturen meiner Funksensoren auf?

Eine Frage die ich mir die letzten Wochen aufgrund der doch nicht geringen Temperaturen ein paar Mal gestellt habe – und die Antwort ist in der Tat ziemlich simpel…

Man nehme dafür:

einen Raspberry Pi (Version 4 oder 3 – ist egal)
einen Radioempfänger der softwareseitig zu programmieren ist
eine handvoll Temperatursender (ich nutze einen von Dostmann )
eine bisschen MQTT
etwas Python
und eine Datenbank

Auf gehts!

Installation der Software auf dem Pi

Mosquitto MQTT

sudo apt-get install mosquitto mosquitto-clients

testen ob das ganze funktioniert: mosquitto_sub -t rtl_433

MariaDB

sudo apt-get install mariadb-server

rtl_433

git clone  https://github.com/merbanan/rtl_433.git 

https://github.com/merbanan/rtl_433/blob/master/BUILDING.md

Python Logger

#!/usr/bin/env python3
import datetime
import json
import logging
import multiprocessing as mp
import sys
import time
import hashlib

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode

import paho.mqtt.client as mqtt

MQTT_SERVER = "temperaturestation"
MQTT_TOPIC_PREFIX = "rtl_433"
TIMEOUT_STALE_SENSOR = 600  # Seconds before showing a timeout indicator

MYSQL_host = 'localhost'
MYSQL_port = '3306'
MYSQL_user = 'user'
MYSQL_pass = 'pass'
MYSQL_db = 'db'
MYSQL_table = 'mqtt'
MYSQL_table2 = 'temperatures'

# log = logging.getLogger()  # Single process logger
log = mp.log_to_stderr()  # Multiprocessing capable logger
mqtt_client = mqtt.Client("RTL_433_Test")

sensor_state = dict()  # Dictionary containing accumulated sensor state


def print_sensor_state():
    """ Print accumulated sensor state """
    time_now = datetime.datetime.utcnow().replace(microsecond=0)
    print("\nUpdate per {} UTC".format(time_now.isoformat(sep=' ')))
    for model in sensor_state:
        print(model)
        for ID in sensor_state[model]:
            data = sensor_state[model][ID]['data'].copy()
            timestamp = data.pop('time')
            timedelta = (time_now - timestamp).total_seconds()
            # Indicator for new and stale data
            indicator = "*" if (timedelta < 2) else "~" if (timedelta >
                                                            TIMEOUT_STALE_SENSOR) else " "
            print("  ID {:5} {}{} {}".format(
                ID, timestamp.isoformat(sep=' '), indicator, data))
    sys.stdout.flush()  # Print in real-time


def on_connect(client, userdata, flags, rc):
    """ Callback for when the client receives a CONNACK response from the server. """
    log.info("MQTT Connection: " + mqtt.connack_string(rc))
    if rc != 0:
        log.error("Could not connect. RC: " + str(rc))
        exit()
    # Subscribing in on_connect() means that if we lose the connection and reconnect then subscriptions will be renewed.
    client.subscribe(MQTT_TOPIC_PREFIX)


def on_disconnect(client, userdata, rc):
    if rc != 0:
        log.error("Unexpected disconnection. RC: " + str(rc))


def on_message(client, userdata, msg):
	""" Callback for when a PUBLISH message is received from the server. """

	log.info("Message recieved: " + msg.topic)

	try:
		d = json.loads(msg.payload.decode())
	except json.decoder.JSONDecodeError:
		log.warning("JSON decode error: " + msg.payload.decode())

	time_str = d.get('time', "0000-00-00 00:00:00")
	ts = datetime.datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S")

	hash_object = hashlib.md5(msg.payload.decode().encode())
	hash = hash_object.hexdigest()

	temp = 0

	try:
		temp = d.get('temperature_F')
		temp = (temp - 32)  * (5.0/9.0)
	except e2:
		log.warning('no temp F found...')

	if temp == None or temp == 0:
		try:
			temp = d.get('temperature_C')
		except e:
			log.warning('no temp C found...')

	if msg.payload != '':
		active = ', active=1'
	else:
		active = ''

	query = """
		INSERT INTO `{}`
		SET
			`hash` = '{}',
			`ts`='{}',
			`topic`='{}',
			`value`='{}',
			`qos`='{}',
			`retain`='{}'
			{}
		ON DUPLICATE KEY UPDATE
			`hash` = '{}',
			`ts`='{}',
			`value`='{}',
			`qos`='{}',
			`retain`='{}'
			{}
	""".format(MYSQL_table, hash, ts, msg.topic, msg.payload.decode(), msg.qos, msg.retain, active,
		hash, ts, msg.payload.decode(), msg.qos, msg.retain, active)

	query_2 = """
		INSERT INTO `{}`
		SET
			`hash` = '{}',
			`created` = '{}',
			`model` = '{}',
			`device` = '{}',
			`channel` = '{}',
			`battery` = '{}',
			`temperature` = '{}',
			`humidity` = '{}'
		""".format(MYSQL_table2, hash, ts, d.get('model'), d.get('device'), d.get('channel'), d.get('battery'), temp, d.get('humidity'))
	print(query_2)

	db = mysql.connector.connect(host=MYSQL_host,
                             database=MYSQL_db,
                             user=MYSQL_user,
                             password=MYSQL_pass)
	log.info("connected!")

	if db.is_connected():
		cursor = db.cursor()
		print("\nWrite to db...")
		try:
			print("ready to execute")
			result  = cursor.execute(query)
			print(result)
			db.commit()

			result  = cursor.execute(query_2)
			print(result)
			db.commit()
			print(result)
			print (cursor.rowcount, "Record inserted successfully into {} table".format(MYSQL_table))
			print("\nWrite to db... done!")
		except mysql.connector.Error as error :
			db.rollback()
			print("Failed to insert into MySQL table {}".format(error))

	cursor.close()
	db.close()

	log.info("done!")

# Setup MQTT client
mqtt_client.on_connect = on_connect
mqtt_client.on_disconnect = on_disconnect
mqtt_client.on_message = on_message
mqtt_client.connect(MQTT_SERVER)
mqtt_client.loop_start()


def main():
    """MQTT Test Client"""
    log.setLevel(logging.INFO)
    log.info("MQTT RTL_433 Test Client")

    while True:
        time.sleep(1)


if __name__ == "__main__":
    main()

Empfangen der Temperaturdaten und weiterleiten an MQTT Queue

rtl_433 -F json -M utc | mosquitto_pub -t rtl_433 -l

Systemd für den Mqtt zu Datenbank Logger

Systemd service

[Unit]
Description=start python logger for temperatures sensors

[Service]
ExecStart=/home/pi/mqtt_logger/start_sensors_logger.sh
RestartSec=5
StartLimitInterval=10
StartLimitBurst=10
RuntimeMaxSec=3600
Restart=always

[Install]
WantedBy=multi-user.target

start_sensors_logger.sh

#!/bin/sh
python3 /home/pi/mqtt_logger/logger.py

Systemd für den MQTT Server

[Unit]
Description=start mqtt reciever for temperatures sensors

[Service]
ExecStart=/home/pi/mqtt_logger/start_sensors_reciever.sh
RestartSec=5
StartLimitInterval=10
StartLimitBurst=10

[Install]
WantedBy=multi-user.target

 #!/bin/sh
/usr/local/bin/rtl_433 -F json -M utc | /usr/bin/mosquitto_pub -t rtl_433 -l

Christian Zepter