Sensor Daten in externer MySQL DB speichern

Heute ist Stammtischzeit:
Jeden Donnerstag 20:30 Uhr hier im Chat.
Wer Lust hat, kann sich gerne beteiligen. ;)
  • Hallo zusammen:helpnew::helpnew:,

    unsere Aufgabe ist es eine Wetterstation mit dem Pi zu bauen die dabei erfassten Sensordaten (von BMP085, HYT221) sollen in einer externen MySQL Datenbank gespeichert werden. Bisher haben wir den Sensor BMP085 erfolgreich zum laufen gebracht und erhalten hier auch Werte (Adafruit Skript). Desweiteren wurde mit Xampp die Datenbank inkl PHP und Apache2 auf einem Rechner installiert. Die benötigten Tabellen und Datenbank wurden mittels PHPmyAdmin bereits erstellt.
    Nun haben wir (die leider keinerlei Ahnung von Programmieren haben) folgendes Problem:

    Wie können wir die augelesenen Daten des PI in den erstellten Tabellen der Datenbank speichern ?
    BZW wie können wir auf die SQL Datenbank zugreifen ?

    LG und Danke im Voraus !


  • unsere Aufgabe ist es eine Wetterstation mit dem Pi zu bauen

    "Unsere Aufgabe" :huh:

    Das hört sich irgendwie nach einer Hausaufgabe an? *hust* imho wäre es in dem Fall sinnvoller ihr findet das selber heraus oder bemüht euch mal ein bisschen, so schwer is das nämlich nicht wirklich :D

    Ansonsten wäre es für Helfer wichtig zu wissen womit ihr bisher die Sensordaten ermittelt...

  • ich schreibe mit einem Python(2.7)-Skript bei jeden Aufruf die Messwerte in ein Logfile und 2 mySQL-DBs

    (ist im wesentlichen mein erstes Python-Skript.. und hat sicher noch Verbesserungspotential.. :)

    Code
    import os, sys, time, MySQLdb, datetime


    diese Module werden benötigt...


    hier zur besseren Übersicht... nur ein Teil zum Schreiben in eine mySQL-DB.


    den SQL-String muss du natürlich an deine Tabelle anpassen.. oder eine Tabelle mit folgenden SQL erstellen:

    auch hier ist zu überlegen, ob es sinnvoll ist die Zeit in mehreren Spalten zu speichern, damit wird natürlich viel "Müll" in die DB geschrieben... aber es ist so für mich einfacher später auf die Daten zu zugreifen...

    Nachtrag:

    funktioniert so nicht mit Python3 !!!

    und so sieht die Bildschirmausgabe aus:

    Suche Gleichgesinnte für mein Projekt!
    jamesmatik_200.png
    soll ein Baukasten (Software und Hardwaremodule) für Temperatur-Logging, kleine Steuerungsaufgaben und Smart-Home werden.

    Einmal editiert, zuletzt von RasPi-Azubi (25. Mai 2014 um 19:32)

  • Naja wie gesagt, wenn man einfach blind irgendwas vorkaut wird nicht wirklich verstanden Wieso das so gemacht wird.... Wenn man aber etwas tatsächlich selber entwickelt versteht man auch wieso/weshalb/warum ... Und da es sich wirklich extrem wie eine "Aufgabe" anhört, also für die Schule oder wofür auch immer - sollte der TE das lieber mal selber herausfinden und wenns dann Probleme gibt konkretere Fragen stellen.... Meine Signatur zeigt auch viel zu genau diesem Thema...


    Und wenn die Datenbank größer wird solltet ihr euch unbedingt performantere Datenbank Strukturen überlegen und Zeit Werte nicht doppelt speichern - bzw die von SQL bereits zur Verfügung gestellten Funktionen -> timestamp <- verwenden, alles andere ist dann überflüssig

  • nun ja, es war für mich ein blutiger Weg! :)

    daher helfe ich grundsätzlich gerne.. und gleichzeitig bekomme ich von den "PROS" immer wieder wichtige Tipps..

    Wenn ich (für mich selbst) sofort versuche alles mit den optimalsten Methoden zu machen, dann verliere ich schnell den Blick auf das Wesentliche...

    da ich aber beim Auslesen der Daten noch nicht wirklich zielsicher mit den time/date und Timestamp von PHP u. mySQL umgehen kann, habe ich diese (noch) uncoole Variante umgesetzt... natürlich lerne ich täglich was Neues.. (leider vergesse ich schneller als ich lernen kann :)

    Suche Gleichgesinnte für mein Projekt!
    jamesmatik_200.png
    soll ein Baukasten (Software und Hardwaremodule) für Temperatur-Logging, kleine Steuerungsaufgaben und Smart-Home werden.

  • Für zeitdiskrete Werte würde ich zudem PostgreSQL bevorzugen, alleine schon wegen den einsetzbaren Zeitfunktionen, die Möglichkeit des Einsatzes von Zeitzonen sowie Intervallen.
    Wobei Zeitwerte ja nur in den seltensten Fällen für längere Zeit zeitdiskret gehalten werden müssen. Im Normalfall wären Ringpuffer-Speichersysteme die bessere Lösung.

  • also momentan hole ich mir die Daten dann mit PHP (am Webserver) mit Abfragen wie der folgenden aus der DB:
    minimale Temp. eines Tages...
    [code=php] $sql5 = "
    SELECT MIN(temperatur) AS minwert
    FROM tbl_messwerte
    WHERE (sensorID = '".$sensorID1."')
    AND (jahr = '".$jahr."')
    AND (monat = '".$monat."')
    AND (tag = '".$tag."')
    ";
    [/php]
    oder Mittelwert alles Messungen einer halben Stunde (30-59)
    [code=php] $sql2 = "
    SELECT ROUND(AVG (temperatur) ,1) AS mittelwert2
    FROM tbl_messwerte
    WHERE (sensorID = '".$sensorID1."')
    AND (jahr = '".$jahr."')
    AND (monat = '".$monat."')
    AND (tag = '".$tag."')
    AND (stunde = '".$stunde."')
    AND (minute BETWEEN '30' and '59')
    ";
    [/php]

    und da muss ich noch dicke Dokus lesen, bis ich das mit Timestamp & Co umsetzen kann.. zumal ich oft nicht mal die richtigen Suchbegriffe für eine gezielte Suche im Web kenne.. mühsam nährt sich das Eichhörnchen.. :)

    hier muss man dann auch wegen der Performance im Grunde nicht wirklich nachdenken..

    in meinen Fall werden maximal 3-4 Personen einige male pro Tag zugreifen, denke das würde auch der RasPi schaffen.. Aber weil ich meinen RasPi (mangels Fachwissen) nicht übers Internet zugänglich machen will, schreibe ich halt die Logger-Daten zu einem Hoster im Web.

    Suche Gleichgesinnte für mein Projekt!
    jamesmatik_200.png
    soll ein Baukasten (Software und Hardwaremodule) für Temperatur-Logging, kleine Steuerungsaufgaben und Smart-Home werden.

    Einmal editiert, zuletzt von RasPi-Azubi (25. Mai 2014 um 21:01)


  • und da muss ich noch dicke Dokus lesen, bis ich das mit Timestamp & Co umsetzen kann.

    Hallo RasPi-Azubi,

    da ich selbst auch kein SQL Hacker bin, hätte ich das mit dem MySQL timestamp auch nicht auf Anhieb gewusst,
    wäre aber vermutlich auch irgendwann darauf gestossen.

    Prinzipiell ist meigrafd nur zuzustimmen, und man sollte so eine Datenstruktur verwenden, die das RDBMS dafür vorgesehen hat und intern selbst verwendet.

    Ich glaube ich wäre als aller erstes auf die Unix Epoch Seconds als abzuspeichernde Timestamps verfallen,
    so wie sie z.B. die das date Kommando oder die time() Implementierungen diverser Sprachen einem ausgeben.

    Da braucht man sich am wenigsten Gedanken über irgendwelche Formate zu machen,
    da die jeweils benötigte Formatierung und ggf. TZ Konvertierung erst nach dem SQL Fetch ausserhalb der DB vorgenommen wird.

    Code
    $ date;date +%s;perl -le 'print time';python -c 'import time;print time.time()'
    Mon May 26 01:26:13 CEST 2014
    1401060373
    1401060373
    1401060373.7

    Einmal editiert, zuletzt von Life_of_Pi (26. Mai 2014 um 01:33)

  • Mit "wie du die Werte ausliest" meinte ich eigentlich das auslesen des Sensors, nicht die Datenbankabfrage...
    Du mußt ja mit irgendwas die Werte vom Sensor auslesen und in die Datenbank eintragen, und dort müsstest du bereits - jenachdem welche Sprache du verwendest - den timestamp nutzen.

    Diesen timestamp kannst du dann später in der Abfrage in PHP ziemlich einfach mit date(); ins gewünschte Format bringen:
    [code=php]
    $SQL = "SELECT time, MIN(temperatur) AS minwert FROM tbl_messwerte WHERE (sensorID = '".$sensorID1."') ORDER BY time DESC";

    $conid = mysql_connect($server, $user, $password);
    $QUERY = mysql_query($SQL, $conid);

    $i=0;
    while ( $result = mysql_fetch_array($QUERY) ) {
    $i++;
    $datetime[$i] = date('H:i:s d.m.Y', $result['time']);
    $MINTEMP[$i] = $result['minwert'];
    }
    [/php]

    Das einzige was man dabei unter Umständen beachten muss ist die Timezone, die sich aber durch ein einmaliges Ausführen ganz oben in dem PHP File wie folgt lösen lässt:
    [code=php]
    //needed to fix STRICT warnings about timezone issues
    $tz = exec('date +%Z');
    @date_default_timezone_set(timezone_name_from_abbr($tz));
    ini_set('date.timezone',@date_default_timezone_get());
    [/php]

  • zusätzlich zu den einzelnen Werten (jahr, monat,tag....) hab ich ja von der 1.Stunde auch immer einen "Timestamp" in der DB stehen.. erzeugt im logger-Skript am RasPi.

    Code
    timestamp = datetime.datetime.now() # liefert einen formatierten Timestamp von der RasPi-Zeit

    und den schreibe ich dann mit....

    Code
    cursor.execute("""INSERT INTO tbl_messwerte (ID, jahr, monat, tag, stunde, minute, sekunde, sensorID, temperatur, messstelle, time) VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')""" %(jahr, monat, tag, stunde, minute, sekunde, sensorID, temperatur, messstelle, timestamp ))

    somit habe ich einen Wert wie hier beschrieben.. http://dev.mysql.com/doc/refman/5.1/de/datetime.html

    und so sieht ein Datensatz derzeit in PHPmyAdmin aus..

    PHPmyAdmin.png

    es fällt mir beim erlernen leichter, wenn ich auf die diskreten Werte zugreifen kann, hab ich dann halt auch die einzelnen Werte in die DB geschrieben..

    ..und gleichzeitig für alle die so etwas nachbauen wollen dazu geschrieben, dass es eigentlich ein Unfug ist...

    aber ich denke eure Tipps hier helfen mir sicher weiter... DANKE!

    wie in meinen Bweispiel oben..

    [code=php] $sql5 = "
    SELECT MIN(temperatur) AS minwert
    FROM tbl_messwerte
    WHERE (sensorID = '".$sensorID1."')
    AND (jahr = '".$jahr."')
    AND (monat = '".$monat."')
    AND (tag = '".$tag."')
    "; [/php]

    möchte ich in weiter Folge dann die Abfrage so eingrenzen, dass ich mit (php) Date/Time Funktionen auf mein Feld time zugreifen kann..

    also der Teil, in dem ich hier hinter WHERE noch das Jahr, monat und tag einzeln abfrage...

    sollte dann etwa so aussehen:

    [code=php] $sql5 = "
    SELECT MIN(temperatur) AS minwert
    FROM tbl_messwerte
    WHERE (sensorID = '".$sensorID1."')
    AND (time = '"???????????"')
    "; [/php]

    ------------------------------

    könnte hier eventuell AND (time = '"DATE('2014-12-31')"') das gewünschte Ergebnis liefern??

    Zitat

    $SQL = "SELECT time, MIN(temperatur) AS minwert FROM tbl_messwerte WHERE (sensorID = '".$sensorID1."') ORDER BY time DESC";

    in diesem Beispiel bekomme ich (noch ungetestet) dann den Minimalwert aus allen Datensätzen in der DB.
    (aber Respekt!! Beispiel hast an meine App super angepasst.. :)

    Suche Gleichgesinnte für mein Projekt!
    jamesmatik_200.png
    soll ein Baukasten (Software und Hardwaremodule) für Temperatur-Logging, kleine Steuerungsaufgaben und Smart-Home werden.

    Einmal editiert, zuletzt von RasPi-Azubi (26. Mai 2014 um 21:00)

  • rechnen ist nicht mein Problem..

    sondern momentan hänge ich an der SQL-Abrage ...

    hier ist mir der Syntax noch immer ein Rätsel..

    ich habe im Feld "time" ja einen Timestamp stehen.. aber wie muss nun der SQL-String aussehen, wenn ich wie bei der Abfragen nach dem minimal Wert eines Tages zugreifen will..

    da muss ich dann ja mit WHERE SensorID und mit AND time =??????? zugreifen..

    und das kann ich momentan nicht (mit den diskreten Werten jedoch klappt es genau so wie gewünscht..)

    Nachtrag:
    sql5 --> SELECT MIN(temperatur) AS minwert FROM tbl_messwerte WHERE (sensorID = '28-000005293706') AND (time BETWEEN '2014-05-26 00:00:00' AND '2014-05-26 23:59:59')

    scheint mal zu klappen...

    Suche Gleichgesinnte für mein Projekt!
    jamesmatik_200.png
    soll ein Baukasten (Software und Hardwaremodule) für Temperatur-Logging, kleine Steuerungsaufgaben und Smart-Home werden.

    Einmal editiert, zuletzt von RasPi-Azubi (26. Mai 2014 um 19:52)

  • Naja aber auch in Punkto Datenbankgröße ist eure Vorgehensweise nicht sehr gut. Auch wie ich finde die Übersicht und der Aufwand von wegen das was in die Datenbank alles geschrieben werden muss, ist bei euch ziemlich hoch...

    Das einzige was man eigentlich schreiben muss wäre sensorID, timestamp, Ort und die ermittelten Werte - also gerade mal 5 Einträge (mit der ID des Eintrags), ihr habt aber 11 also mehr als das doppelte :-/

    Ihr könnt euch vielleicht auch mal folgendes durchlesen: http://www.epochconverter.com/programming/my…om-unixtime.php
    Google Suche nach: mysql unixepoch

    Damit könnt ihr direkt in der SQL Abfrage den Unixtimestamp in ein Datumsformat umwandeln und damit arbeiten.

    Zum Beispiel so:

    SQL
    SELECT *, FROM_UNIXTIME(timestamp,"%d.%m.%Y %H:%i:%s") AS Date FROM werte WHERE sensorID='28-000005293706' AND (Date BETWEEN '26.05.2014 00:00:00' AND '26.05.2014 00:30:00')

    timestamp ist dabei die Spalte in der Tabelle mit dem unixtimestamp. Damit gibt er alle Datensätze der ersten 30 Minuten des Tages aus.. Das von euch gesetzte FROM_UNIXTIME Format muss aber natürlich auch dem BETWEEN Vergleichsoperatoren entsprechen damit das funktioniert ;)

    Wichtig beim BETWEEN Vergleich ist auch dass erst das niedrige Datum und danach das höhere Datum gesetzt wird. Andersherum funktioniert nicht.


    In der professionellen Programmierung wird vorzugsweise der timestamp, aus gutem Grund, verwendet. Eine Liste für viele verschiedene Programmiersprachen, um den timestamp zu kriegen, findet ihr hier: http://www.epochconverter.com/

    Ausserdem kann man den timestamp problemlos auf verschiedene Zeitzonen umwandeln, was mit eurer Methode aber recht aufwendig wäre (erst umwandeln in den timestamp und anschließend in ein "human readable date" der anderen Zeitzone verwandeln..)

  • ok.. Danke für die Tipps... die nehme ich immer gerne!!!

    ja, auch über die Zeitzonen hab ich schon nachgedacht, aber für meine kleine Heizungssteuerung reicht mir die aktuelle Zeitzone vom RasPi.. der holt die Zeit aus dem WEB (wenn richtig konfiguriert). Da werde ich für dieses Projekt sicher nicht mehr Aufwand treiben.

    und da ich nun bereits einen Lösungsweg ohne die Spalten "jahr, monat, tag...." sehe, werde ich vermutlich in der produktiven Lösung nur mehr den vom RasPi (mit python) generrierten TIMESTAMP arbeiten.. (Danke an des Forum)

    Datenbankgröße: ja, Problem ist mir durchaus bewusst.. zumal ich aktuell ja noch alle 5 min (cronjob) von jedem Sensor am 1-Wirebus einen Datensatz schreibe..
    Denke im produktiven Einsatz wird es reichen, wenn ich alle 15 Minuten die Daten logge..

    ..weiters überlege ich noch ob ich die tbl_messwerte noch etwas erweitere, damit ich auch andere Größen sinnvoll in dieser Tabelle verwalten kann..

    dabei denke ich an Werte aus einem AD-Wandler.. also Spannungen, PT1000, Photowiederstände, Wetterdaten...

    Suche Gleichgesinnte für mein Projekt!
    jamesmatik_200.png
    soll ein Baukasten (Software und Hardwaremodule) für Temperatur-Logging, kleine Steuerungsaufgaben und Smart-Home werden.


  • ..weiters überlege ich noch ob ich die tbl_messwerte noch etwas erweitere, damit ich auch andere Größen sinnvoll in dieser Tabelle verwalten kann..

    dabei denke ich an Werte aus einem AD-Wandler.. also Spannungen, PT1000, Photowiederstände, Wetterdaten...

    Erweitern würde ich hier nicht. Pro Tupel würdest du bei einem Messwert bei Erweiterung der Tabelle um Attribute, die andere Sensoren etc. betreffen viele Null-Werte erzeugen - das ist alles andere als schön und praxistauglich.

    Ab spätestens dann solltest du dir auch Gedanken um die Datenbanstruktur machen. Eine Möglichkeit wäre z.B. eine Generalisierung bei den Messwerten vorzunehmen und pro Sensor einen eigenen Subtyp anlegen. In den Supertyp - sprich deine Tabelle tbl_messwerte, würden dann nur noch die Attribute kommen, die alle Sensoren gemeinsam haben, z.B. ID, evtl. Messtelle usw. Dadurch dürftest du Null-Werte komplett vermeiden können.

    Btw: Das Schema ER-Modell -> relationales Schema -> SQL-Code/Erzeugen der Tabellen im DBMS hat sich sehr bewehrt und sollte bei größeren Projekten mit Datenbanken auch beachtet werden ;)

  • Hallo allerseits,

    ich habe heute einmal verucht den Code von RasPiAzubi auszuführen. Leider bekomme ich an folgender Stelle einen Fehler:

    Code
    File "tempinmysql.py", line 12
        for d in os.listdir("/sys/bus/w1/devices"): # liefert eine Liste aller Sensoren
                                                   ^
    SyntaxError: invalid syntax

    Kann mir jemand auf die Spünge helfen was ich falsch mache?


    Edit. War wohl blind - Hat sich erledigt "peinlich"

    Einmal editiert, zuletzt von shoggo (10. Januar 2015 um 16:09)

  • So nun ist es soweit. Ich bin an einem Punkt wo es nicht weiter geht.

    Bei Verbindung mit dem mysqlserver tritt folgender Fehler auf:

    Code
    Traceback (most recent call last):
      File "tempinmysql.py", line 71, in <module>
        curser = connection.curser()
    AttributeError: 'Connection' object has no attribute 'curser'
    pi@raspberrypi ~ $

    Ist bestimmt nur ein "kleiner" Fehler - aber ich sehe ihn einfach nicht...

  • ich versuche über ein Bash Skript auf eine externe MySQL Datenbank zuzugreifen, jedoch funktioniert das nicht wirklich.

    Wie habt ihr das gemacht bzw. was habt ihr dafür installieren müssen?

    Danke schon mal im Voraus für einen Tipp!

Jetzt mitmachen!

Du hast noch kein Benutzerkonto auf unserer Seite? Registriere dich kostenlos und nimm an unserer Community teil!