# Wie zeichne ich die Temperaturen meiner Funksensoren auf?

> Temperaturen von Funksensoren mit Raspberry Pi, rtl_433, MQTT, Python und MariaDB aufzeichnen – Schritt für Schritt mit Code.

- Quelle: https://czepter.me/blog/wie-zeichne-ich-die-temperaturen-meiner-funksensoren-auf
- Datum: 2020-01-07

## Teaser

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...

## Inhalt

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](https://amzn.to/2Xlb3FS) (Version [4](https://amzn.to/2Xlb3FS) oder [3](https://amzn.to/2J1Yawg) - ist egal)
- einen [Radioempfänger](https://amzn.to/2YpBATG) der softwareseitig zu programmieren ist
- eine handvoll Temperatursender (ich nutze einen von [Dostmann](https://amzn.to/2QsVviV))
- 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 Bauanleitung: [github.com/merbanan/rtl_433/blob/master/BUILDING.md](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 start_sensors_reciever.sh

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