Datenbank: Sensorwerte speichern


26.04.2016  |  Smarthome Server, Tutorial

Es ist praktisch, jederzeit abrufen zu können, wie warm es gerade in einem Raum ist oder wie hoch die Luftfeuchtigkeit ist. Doch manchmal möchte man nicht nur den aktuellen Wert sehen, sondern auch einen Verlauf der letzten Tage, Wochen und sogar Monate. Um dies zu realisieren, wird eine Datenbank benötigt, in die regelmäßig die Werte geschrieben werden. Dann können diese jederzeit einfach per URL-Aufruf abgefragt werden. Wie man so eine Datenbank einrichtet und die Werte eintragen lässt, erfährst du hier. Um dem Tutorial folgen zu können, benötigst du einen fertig eingerichteten Raspberry Pi und solltest außerdem das RaZberry Modul auf deinem Raspberry Pi installiert haben.

Benötigte Teile

Um die Werte zu messen, benötigt man natürlich auch einen Sensor. Ich benutze den folgenden, da er nicht nur die Temperatur angibt, sondern auch die Luftfeuchtigkeit & Helligkeit, und weil er gleichzeitig als Bewegungsmelder fungiert.

Datenbank anlegen

Um eine Datenbank anzulegen und Sensorwerte darin abspeichern zu können, solltest du dir das Tutorial "Datenbank anlegen" ansehen.

Tabellen anlegen

Damit du die Sensorwerte in eine Datenbank schreiben kannst, musst du dir in deiner Datenbank noch ein paar Tabellen anlegen. Die 3 Tabellen sind folgende:
  • ROOMS: Hier werden alle Räume deines Hauses hinterlegt, die ins Smarthome integriert werden sollen
  • ZWAVE_SENSOREN: Hier werden alle Z-Wave Sensorenmit ihrer ID hinterlegt
  • SENSOR_DATA: In diese Tabelle werden regelmäßig die Sensorwerte geschrieben
Um die neuen Tabellen anzulegen, rufst du in deinem Browser die folgende Adresse auf, wobei du für [IP-ADRESSE] die IP deines Raspberry einsetzt: 
http://[IP-ADRESSE]/database/phpliteadmin.php

ROOMS anlegen

In dieser Tabelle sind alle Räume deines Smarthomes hinterlegt. Dies ist nötig, damit das Script die Sensoren aus jedem angelegten Raum abfragen und deren Werte abspeichern kann. Außerdem greift die Smarthome App, die im Laufe dieser Tutorialreihe noch programmiert wird, auf diese Tabelle zu, um dynamisch für jeden angelegten Raum einen Menüpunkt anzulegen.
Die Tabelle ROOMS enthält alle im Smarthome angelegten Räume.
Das Feld NAME enthält den Namen eines Raumes. Dieser wird in der oben erwähnten Smarthome App als Menüpunkt angezeigt. Das Feld LOCATION enthält den KEY eines Raumes, damit der Server beispielsweise erkennt, in welchem Raum ein Befehl ausgeführt werden soll. Die beiden Felder haben den Datentyp TEXT.
Die Tabelle 'ROOMS' hat 2 Felder: ROOM und LOCATION.

ZWAVE_SENSOREN anlegen

Damit der Server alle vorhandenen Sensoren abfragen kann, sind in der Tabelle ZWAVE_SENSOREN alle angelernten Sensoren hinterlegt.
In der Tabelle ZWAVE_SENSOREN werden alle Z-Wave Sensoren hinterlegt.
Die 5 Felder dieser Tabelle stehen für folgendes:
  • RAUM: der Key des Raumes, in dem sich der Sensor befindet
  • SENSORART: die Art des Wertes, der vom Sensor gemessen wird
  • ID: die Z-Wave ID des Sensors
  • SHORTFORM: eine Abkürzung des gemessenen Wertes (zb. Temp statt Temperatur oder Hygro statt Luftfeuchtigkeit) - wird später zur App-Programmierung benötigt
  • EINHEIT: die Einheit, in der der Wert gemessen wird
Die 5 Felder sind: RAUM, SENSORART, ID, SHORTFORM, EINHEIT.

SENSOR_DATA anlegen

In der Tabelle SENSOR_DATA werden alle gesammelten Sensorwerte abgespeichert.
Alle Sensorwerte werden regelmäßig in die Tabelle SENSOR_DATA geschrieben.
Die 4 Felder werden folgendermaßen verwendet:
  • RAUM: der Key des Raumes, zu dem der Sensorwert gehört
  • VALUE: der gemessene Wert des Sensors
  • SENSORART: die Art des gemessenen Wertes (zb. temperatur, luftfeuchtigkeit, helligkeit, usw.)
  • DATETIME: Datum und Uhrzeit der Messung
Die Tabelle SENSOR_DATA hat die 4 Felder ROOM, VALUE, SENSORDATA und DATETIME.

Tabellen mit Daten füllen

Um Datensätze in die Tabellen einzufügen, klickst du auf der phpLiteAdmin-Startseite bei der entsprechenden Tabelle auf 'Insert'. In der Tabelle ROOMS legst du einen Raum an und gibst diesem Raum einen Key - zum Beispiel den Raumnamen auf Englisch und kleingeschrieben. Mit einem Klick auf 'Insert' wird die Reihe hinzugefügt.
So wird der Raum 'Schlafzimmer' mit dem Key 'sleeproom' angelegt.
Der Tabelle ZWAVE_SENSOREN fügst du eine neue Reihe ein und gibst als RAUM den Key des Raumes an, in dem sich der Sensor befindet. Als SENSORART gibst du einen Key für den gemessenen Wert an (zb. 'temperatur' für den Temperatur-Sensor). Die Z-Wave ID des Sensors findest du heraus, indem du mit deinem Browser den folgenden Link aufrufst und dich mit den admin-Daten einloggst: 
http://[IP DEINES RASPBERRY]:8083/smarthome/
Anschließend wechselst du in die Elementenansicht:
In der Elementenansicht werden alle Werte deiner Geräte aufgelistet.
Nun klickst du beim gewünschten Sensor auf das Zahnrad oben rechts und siehst nun die jeweilige Z-Wave ID neben dem Punkt 'Element ID'. Diese ID fügst du in die neue Reihe der Tabelle ins Feld ID ein. Das Feld SHORTFORM erhält eine Abkürzung des Sensorwerts (zb. Temp für Temperaturoder Hygro für Luftfeuchtigkeit) und das Feld EINHEIT die Einheit des gemessenen Wertes. Anschließend klickst du auf 'Insert', um die neue Reihe der Tabelle hinzuzufügen.
Mit dieser Eingabe wird der Temperatur-Sensor fürs Schlafzimmer angelegt.

Z-Wave API für Scripts verfügbar machen

Damit das PHP-Script auf die Sensorwerte zugreifen kann, muss die Z-Wave API für HTTP-Aufrufe zugänglich gemacht werden. Dazu loggst du dich unter folgender Adresse mit deinen admin-Daten ein und öffnest oben rechts über das Options-Menü den Menüpunkt 'Management': 
http://[IP DEINES RASPBERRY]:8083/smarthome/
Das Benutzer-Menü wird über das Optionsmenü oben rechts aufgerufen.
Dort klickst du auf den Punkt Management, klappst den Bereich 'User management' aus und klickst auf den Button 'Add user'.
Per 'Add user' wird ein neuer Benutzer angelegt.
Jetzt kann ein neuer Nutzer angelegt werden. Name und Login-Daten können frei gewählt werden, wichtig ist jedoch, dass die Rolle des Nutzers beim Punkt 'Settings' als 'Anonymous User' eingestellt wird und du ganz unten Zugriff zu allen Räumen gewährst.
Die Nutzerdaten werden folgendermaßen eingestellt.

Sensorwerte abfragen

Bevor wir die Werte in die Datenbank eintragen können, schreiben wir uns noch ein PHP-Script, dass uns den gewünschten Sensorwert des gewünschten Raumes ausgibt. Um das Script anzulegen, erstellst du im Hauptverzeichnis der Servers eine Datei namens 'getSensorData.php' und fügst den folgenden Quellcode ein: 
<?php
 
function getSensorData($room, $val, $show_einheit, $db){
    $ipAddress = $_SERVER['SERVER_ADDR'];
     
    if($val == null){   //Alle Werte werden abgefragt
        //R&auml;ume laden
        if($room == "all"){
            $results = $db->prepare("SELECT * FROM 'ROOMS'");
            $results->execute();
        }
        else{
            $results = $db->prepare("SELECT * FROM 'ROOMS' WHERE LOCATION == :room");
            $results->execute(array('room' => $room));
        }
         
        //Ausgabearray erzeugen
        $values = array();
         
        //Alle R&auml;ume durchlaufen
        foreach($results->fetchAll(PDO::FETCH_ASSOC) as $row){
            //Wertearray erzeugen
            $value_array = array();
             
            //Sensoren im aktuellen Raum laden
            $ergebnisse = $db->prepare("SELECT * FROM 'ZWAVE_SENSOREN' WHERE RAUM == :location");
            $ergebnisse->execute(array('location' => $row['LOCATION']));
             
            //Alle Sensoren im Raum durchlaufen
            foreach($ergebnisse->fetchAll(PDO::FETCH_ASSOC) as $reihe){
                //Wert f&uuml;r jeden Sensor zusammen mit Sensorart in Wertearray schreiben
                $value = array('shortform' => $reihe['SHORTFORM'], 'sensorart' => $reihe['SENSORART'], 'wert' => getSensorData($reihe['RAUM'], $reihe['SENSORART'], 1, $db));
                array_push($value_array, $value);
            }
             
            //Daten f&uuml;r aktuellen Raum in Ausgabearray schreiben
            $value_item = array('name' => $row['NAME'], 'location' => $row['LOCATION'], 'value_array' => $value_array);
            array_push($values, $value_item);
        }
         
        //JSON-Objekt zur&uuml;ckgeben
        return json_encode(array('values' => $values));
    }
    else{   //Ein spezieller Wert wird abgefragt
        //ID des gesuchten Sensors im gesuchten Raum ermitteln
        $id = getZwaveId($room, $val, $db);
         
        if($id !== "N/A"){
            //Z-Wave API aufrufen
            $link = "http://".$ipAddress.":8083/ZAutomation/api/v1/devices/".$id;
             
            //curl mit URL initialisieren
            $cURL = curl_init($link);
             
            //Port setzen
            curl_setopt($cURL, CURLOPT_PORT, 8083);
             
            //Ausgabe einstellen
            curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true);
             
            //Abfrage ausf&uuml;hren
            $result = curl_exec($cURL);
             
            //wert je nach Wunsch mit/ohne Einheit ausgeben
            $array = json_decode($result, true);
             
            if($show_einheit == "1"){
                return $array['data']['metrics']['level']." ".$array['data']['metrics']['scaleTitle'];
            }
            else{
                return $array['data']['metrics']['level'];
            }
        }
        else return "N/A";  //Sensor-ID nicht gefunden?
    }
}
 
function getZwaveId($room, $val, $db){
    //Z-Wave ID des gesuchten Sensors im gesuchten Raum laden
    $query = $db->prepare("SELECT * FROM 'ZWAVE_SENSOREN' WHERE RAUM == :room AND SENSORART == :sensorart");
    $query->execute(array('room' => $room, 'sensorart' => $val));
     
    //ID zur&uuml;ckgeben, wenn gefunden
    if($result = $query->fetch(PDO::FETCH_ASSOC)){
        return $result['ID'];
    }
    else return "N/A";  //ID nicht gefunden?
}
 
?>

Erklärung des PHP-Scripts

Das Script besteht aus den Methoden getSensorData() und getZwaveID(). Die Methode getSensorData() erhält als Parameter 3 Strings und eine Referenz auf das Datenbank-Objekt:
  • $room: der Key des Raumes, dessen Sensorwert ausgegeben werden soll ("all", wenn alle Daten zu allen Räumen als JSON-Objekt ausgegeben werden soll)
  • $val: Die Sensorart die ausgegeben werden soll
  • $show_einheit: 1, wenn Sensorwert mit Einheit ausgegeben werden soll, ansonsten beliebiger anderer Wert (auch nicht definiert)
  • $db: Referenz auf das Datenbank-Objekt
'getSensorData()' gibt den gesuchten Sensorwert des gesuchten Raumes zurück, je nach dem Wert von $show_einheit mit oder ohne Einheit. Der Sensorwert wird direkt über die Z-Wave API abgefragt. Wird als Raum "all" angegeben, so können die Variablen $value und $show_einheit weggelassen werden. Dann wird ein JSON-Objekt von allen Sensorwerten aus jedem Raum mit zugehöriger Einheit ausgegeben. Die Methode getZwaveID() liefert die Sensor-Id des Sensors zurück, der in der Datenbank dem Raum $room und der Sensorart $val zugeordnet wurde.

Schnittstelle für Python-Script anlegen

Damit das Python-Skript auf die Funktion zugreifen kann, erstellen wir eine Schnittstelle, die später auch von der App verwendet wird. Dazu legst du im Server-Hauptverzeichnis eine Datei namens "api.php" an und fügst dort folgenden Code ein: 
<?php

//Datenbankverbindung herstellen
$SQLITEdb = "database/data.sqlite";
$db = new PDO("sqlite:".$SQLITEdb);

include "getSensorData.php";

$validUser = validateUser($_POST['username'], $_POST['password'], $db);

if($validUser){
	switch($_POST['action']){
		case "getsensordata":
			echo getSensorData($_POST['room'], $_POST['value'], $_POST['showeinheit'], $db);
			break;
	}
}

function validateUser($username, $password, $db){
	//wird noch implementiert
	
	return true;
}

?>
Nun kannst du mit einem HTTP-POST-Aufruf den gewünschten Wert bzw. die gewünschten Werte abfragen.

Das Python-Script

Um die Sensorwerte in die Datenbank zu schreiben, wird nun ein Python-Script erstellt. Dieses Script kann später in gewünschten Abständen oder zu bestimmten Uhrzeiten ausgeführt werden. Um das Script zu erstellen musst du im Ordner 'python' auf deinem Server eine Datei namens 'saveSensorData.py' erstellen und den folgenden Quellcode hineinkopieren: 
#!/usr/bin/python
# -*- coding: utf-8 -*-
  
import sqlite3 as lite	#F&uuml;r die Datenbankverbindung
import datetime			#Um Datum und Uhrzeit zu ermitteln
import urllib			#Um die URL aufzurufen
import httplib			#Um die URL aufzurufen
import sys				#Um Kommandozeilenargumente zu lesen

#Um die IP-Adresse zu ermitteln
import socket
import fcntl
import struct

now = datetime.datetime.now()
  
con = lite.connect('/var/www/html/database/data.sqlite')

#Nutzerdaten, da API im weiteren Verlauf der Tutorials noch gesch&uuml;tzt werden
username = 'client'
password = 'clientpassword'

#IP-Adresse des Servers feststellen und zur&uuml;ckgeben
#Parameter: ifname - 'wlan0' falls per WLAN verbunden und 'eth0' falls per LAN verbunden
def get_ip_address(ifname):
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    return socket.inet_ntoa(fcntl.ioctl(
        s.fileno(),
        0x8915,  # SIOCGIFADDR
        struct.pack('256s', ifname[:15])
    )[20:24])
  
#Daten aus Datenbank laden
def getData():
  
    with con:    
        cur = con.cursor()    
        cur.execute("SELECT * FROM ZWAVE_SENSOREN")
  
        return cur.fetchall()
          
#Sensorwerte abfragen und Wert in Datenbank schreiben
#Parameter:
		#room: Raum, in dem der Wert gesucht werden soll
		#sensor: Art des zu suchenden Sensors
def saveSensorData(room, sensor, ip):
	#entsprechenden Sensorwert abfragen
	
	params = urllib.urlencode({'action': 'getsensordata', 'room': room, 'value': sensor, 'username': username, 'password': password})
	headers = {"Content-type": "application/x-www-form-urlencoded", "Accept": "text/plain"}
	conn = httplib.HTTPConnection(ip)
	conn.request("POST", "/api.php", params, headers)
	response = conn.getresponse()
	sensorwert = response.read()
	conn.close()
	
	#Datum und Uhrzeit ermitteln
	uhrzeit = now.strftime("%d.%m.%y %H:%M") #Datum und Uhrzeit im Format TT.MM.JJ HH:MM
	
	#Daten in Datenbank schreiben
	cur = con.cursor()    
	cur.execute("INSERT INTO SENSOR_DATA (ROOM, VALUE, SENSORART, DATETIME) VALUES ('"+room+"','"+sensorwert+"','"+sensor+"','"+uhrzeit+"')")         
	con.commit()
    
#F&uuml;r jeden Sensor in jedem Raum den Wert in die Datenbank schreiben
for data in getData():
	saveSensorData(data[0], data[1], get_ip_address(sys.argv[1])) #data[0] enth&auml;lt den KEY des Raumes und data[1] die Art des Sensors

Erklärung des Python-Scripts

Dieses Python-Script fragt bei jedem Aufruf alle vorhandenen Z-Wave Sensoren ab und speichert die Sensorwerte in der Datenbnak in der Tabelle SENSOR_DATA ab. Dabei wird außerdem der Key des Raumes, in dem sich der Sensor befindet mit abgespeichert und zusätzlich das Datum der Messung und der Key der Sensorart, damit beispielsweise zwischen Temperaur und Luftfeuchtigkeit unterschieden werden kann. Das Python-Script erhält ein Kommandozeilenargument: 'wlan0', falls euer Pi per WLAN mit dem Netzwerk verbunden ist und 'eth0', fall er per LAN am Netz hängt. Dieses Argument wird an die Methode get_ip_adress(ifname) übergeben, welche die entsprechende IP-Adresse des Netzwerkmoduls zurückgibt.

Regelmäßige Ausführung des Scripts

Damit die Sensorwerte automatisiert regelmäßig in die Datenbank geschrieben werden, muss auf dem Raspberry ein Cronjob angelegt werden. Dieser ruft den angegebenen Befehl in angegebenen Zeitabständen auf (bei mir: alle 3 Stunden). Um einen solchen Cronjob anzulegen, musst du in die Kommandozeile den folgenden Befehl eingeben:

crontab -e
Nun öffnet sich eine Datei, an deren Ende du deinen Cronjob eintragen kannst. Ein Cronjob führt einen Befehl immer zu einer gegebenen Zeit aus (genaue Uhrzeit, bei Systemstart, usw.). Die Form, in der ein Cronjob angegeben wird, lautet: 
* * * * * Befehl
Die Sterne stehen für Folgendes:
  • 1. Stern: Die Minuten von 0 bis 60
  • 2. Stern: Die Stunden von 0 bis 24
  • 3. Stern: Die Tage von 1 bis 31 des Monats
  • 4. Stern: Die Monate von 1 bis 12
  • 5. Stern: Die Wochentage von 0 bis 7 (0 & 7 enstprechen Sonntag)
In meinem Fall läuft das Skript immer zur selben Zeit, nämlich um 0, 3, 6, 9, 12, 15, 18 und 21 Uhr. Dazu wird folgendes ans Ende der Datei geschrieben ([VERBINDUNGSART] musst du mit 'wlan0' ersetzen, wenn dein Pi per WLAN mit deinem Netzwerk verbunden ist und mit 'eth0', falls er per LAN an deinem Netz hängt): 
0 0,3,6,9,12,15,18,21  * * *  sudo python /var/www/html/python/saveSensorData.py [VERBINDUNGSART]
Das Skript wird also täglich alle 3 Stunden jeweils zur vollen Stunde ausgeführt. Mit [STRG] + [X] schließt du die Datei. Bestätige den Speichervorgang mit [J] und drücke anschließend enter. Abschließend wird der Pi noch neu gestartet: 
sudo reboot
Falls du Schwierigkeiten oder Fragen zu diesem Tutorial hast, kannst du gerne einen Komentar hinterlassen.

Das Video zum Tutorial

Du kannst dir das Tutorial auch als Video auf meinem YouTube-Kanal ansehen.

Teil 1 von 2

Teil 2 von 2

Über den Autor


Sascha Huber

Hallo, ich bin Sascha, der Gründer von Smarthome Blogger.

Mit einer Leidenschaft für Technologie und einem Hintergrund als Software Engineer habe ich 2016 Smarthome Blogger gegründet. Mein Ziel war es schon immer, innovative Lösungen zu entdecken, die unser Leben einfacher und intelligenter gestalten können. In meinem beruflichen Leben arbeite ich täglich mit Software und Technik, aber auch in meiner Freizeit bin ich stets auf der Suche nach neuen technischen Spielereien und Möglichkeiten, mein Zuhause zu automatisieren und zu verbessern.

Auf Smarthome Blogger teile ich mein Wissen, meine Erfahrungen und meine Begeisterung für alles rund um das Thema Smarthome.


Dieser Beitrag hat dir gefallen?

Dann abonniere doch unseren Newsletter!