SEITEN

SQLite 3 Datenbank mit Python erstellen und auslesen

Veröffenticht am 27.01.2016 von Raab Wolfgang in der Kategorie Temperatur WebApp bisher 6 Reaktionen Zuletzt überarbeitet am 21.02.2017

Um einen uneingeschränkten und problemlosen Zugriff auf die gespeicherten Daten zu ermöglichen, speichere ich die Daten in einer SQLite 3 Datenbank. Natürlich könnte man die Daten auch in einer einfachen Textdatei abspeichern, allerdings könnte dies eventuell bei gleichzeitigem Zugriff auf die Datei zu einem Problem im Dateimanagement führen. Daher ist die Wahl einer Datenbank immer eine Überlegung wert.

Für die Beitragsreihe, deren Einleitung du im Beitrag “Mobile WebApp für die Temperaturmessung am Raspberry Pi” findest, habe ich folgendes Video mit ca. 57 Minuten erstellt. In diesem erkläre ich das Zusammenspiel und die Funktionen der beiden Programme.

Datenbank erstellen und beschreiben

Als Erstes müssen die notwendigen Bibliotheken importiert werden.

import os, sys, sqlite3

1	import os, sys, sqlite3

Da für die erste Speicherung die Datenbank erst erstellt werden muss, bietet sich eine Abfrage über deren Existenz an. Diese kann bei Programmstart oder bei Funktionsaufruf erfolgen. Beide Varianten verwende ich in dem Beispiel für die WebApp. Für diesen Beitrag verwende ich den Programmcode für die Datei “tempdata.db”, in welcher für das Programm notwendige Temperaturwerte gespeichert werden.

Überprüfung der Existenz

Mit einer if-Abfrage wird geprüft ob die Datei “temp.db” im Ordner des Webservers vorhanden ist.

# Existenz der Datenbank überprüfen und ggf. diese anlegen
if not os.path.exists("/var/www/tempdata.db"):
    print "Datenbank tempdata.db nicht vorhanden - Datenbank wird anglegt."
    userTempWerte_db_anlegen()

# Temperatur-Werte aus Datenbank auslesen
userTempWerte_db_auslesen()

# Existenz der Datenbank überprüfen und ggf. diese anlegen

if not os.path.exists("/var/www/tempdata.db"):

print "Datenbank tempdata.db nicht vorhanden - Datenbank wird anglegt."

userTempWerte_db_anlegen()

# Temperatur-Werte aus Datenbank auslesen

userTempWerte_db_auslesen()

Datenbank anlegen

In der Funktion userTempWerte_db_anlegen() wird die Datei erstellt.

def userTempWerte_db_anlegen():
    # Temperatur-Werte für erste Inbetriebnahme    
    minTempSensor = -50.00 # Messbereich Sensor Untergerenze
    maxTempSensor = 125.00 # Messbereich Sensor Obergrenze
    minTempUser = 20.00 # Benutzerdefinierte Untergrenze der Temperatur
    maxTempUser = 21.00 # Benutzerdefinierte Obergrenze der Temperatur
    connection = sqlite3.connect("/var/www/tempdata.db")
    cursor = connection.cursor()
    # Tabelle erzeugen
    sql = "CREATE TABLE tempWerte("\
        "minTempSensor FLOAT, maxTempSensor FLOAT, minTempUser FLOAT, \
         maxTempUser FLOAT) " 
    cursor.execute(sql)
    # Werte für erste Inbetriebnahme
    sql = "INSERT INTO tempWerte VALUES(" + str(minTempSensor) + ", " \
           + str(maxTempSensor) + ", " \
           + str(minTempUser) + ", " + str(maxTempUser) + ")"
    cursor.execute(sql)
    connection.commit()
    connection.close()
    print "Datenbank tempdata.db mit ", sql ," Inhalt angelegt"

def userTempWerte_db_anlegen():

# Temperatur-Werte für erste Inbetriebnahme

minTempSensor = -50.00 # Messbereich Sensor Untergerenze

maxTempSensor = 125.00 # Messbereich Sensor Obergrenze

minTempUser = 20.00 # Benutzerdefinierte Untergrenze der Temperatur

maxTempUser = 21.00 # Benutzerdefinierte Obergrenze der Temperatur

connection = sqlite3.connect("/var/www/tempdata.db")

cursor = connection.cursor()

# Tabelle erzeugen

sql = "CREATE TABLE tempWerte("\

"minTempSensor FLOAT, maxTempSensor FLOAT, minTempUser FLOAT, \

maxTempUser FLOAT) "

cursor.execute(sql)

# Werte für erste Inbetriebnahme

sql = "INSERT INTO tempWerte VALUES(" + str(minTempSensor) + ", " \

+ str(maxTempSensor) + ", " \

+ str(minTempUser) + ", " + str(maxTempUser) + ")"

cursor.execute(sql)

connection.commit()

connection.close()

print "Datenbank tempdata.db mit ", sql ," Inhalt angelegt"

Der für das Anlegen der Datenbank notwendige Programmcode beginnt nach dem Teil der Festlegung der Variablen mit dem Aufbau einer Verbindung zur Datenbank.

Verbindung zur Datenbank aufbauen

Bevor allerdings die Datei angelegt bzw. beschrieben werden kann, muss die Verbindung zur Datenbank aufgebaut werden. Dies geschieht mit den Zeilen:

connection = sqlite3.connect("/var/www/tempdata.db")
cursor = connection.cursor()

1 2	connection = sqlite3.connect("/var/www/tempdata.db") cursor = connection.cursor()

Erstellung der Tabelle

Da die Zeichenkette sql den Befehl “CREATE TABLE tempWerte” beinhaltet, wird durch den Befehl cursor.execute(sql) eine Tabelle mit dem Namen tempWert erstellt. Diese beinhaltet die vier Felder minTempSensor, maxTempSensor, minTempUser und maxTempUser jeweils vom Feldtyp FLOAT.

Inhalte in Tabelle schreiben

In diesem Beispiel und der dazugehörigen Funktion beschreibe ich die Datei gleich mit den zuvor festgelegten Variablen.

Der für die Speicherung der kompletten Felder der Datenbank notwendige Befehl lautet “INSERT INTO tempWerte VALUES” in der Zeichenkette sql.

Um einzelne Werte der Datenbank zu ändern, siehe hierzu die Funktion userTempWerte_db_schreiben(datenVonApp) von dem gesamten Programmcode, erfolgt zum Beispiel mit dem Befehl “UPDATE tempWerte SET minTempUser”.

Der notwendige Programmbefehl lautet wie beim Anlegen der Tabelle cursor.execute(sql).

Verbindung zur Datenbank trennen

Nachdem die Tabelle und deren Inhalte geschrieben wurde, speichern wir die Datei und trennen die Verbindung. Dies erfolgt mit den Zeilen:

connection.commit()
connection.close()

1 2	connection.commit() connection.close()

Datenbank auslesen

In meinem Programmbeispiel werden die notwendigen Temperaturdaten in der Funktion userTempWerte_db_auslesen() aus der Datei tempdata.db ausgelesen.

def userTempWerte_db_auslesen():
    global minTempSensor, maxTempSensor, minTempUser, maxTempUser
    
    connection = sqlite3.connect("/var/www/tempdata.db")
    cursor = connection.cursor()
    sql = "SELECT * FROM tempWerte"
    cursor.execute(sql)
    for dsatz in cursor:
        minTempSensor = dsatz[0]
        maxTempSensor = dsatz[1]
        minTempUser = dsatz[2]
        maxTempUser = dsatz[3]
    connection.close()
    return ()

def userTempWerte_db_auslesen():

global minTempSensor, maxTempSensor, minTempUser, maxTempUser

connection = sqlite3.connect("/var/www/tempdata.db")

cursor = connection.cursor()

sql = "SELECT * FROM tempWerte"

cursor.execute(sql)

for dsatz in cursor:

minTempSensor = dsatz[0]

maxTempSensor = dsatz[1]

minTempUser = dsatz[2]

maxTempUser = dsatz[3]

connection.close()

return ()

Durch den Befehl global werden die danach festgelegten Variablen (minTempSensor, maxTempSensor, minTempUser, maxTempUser) zur Verwendung auch außerhalb der Funktion deklariert.

Der notwendige Befehl für das Auslesen der Tabelle ist “SELECT * FROM tempWerte” und findet sich wieder in der Zeichenkette sql. In diesem Fall steht “*” für alle Felder in der Tabelle mit dem Namen tempWerte. Ein einzelnes Feld könnte somit durch direkte Benennung ausgelesen werden, zum Beispiel “SELECT minTempSensor FTOM tempWerte”. Das Auslesen und Zuweisen der Inhalte in die zuvor als global deklarierten Variablen erfolgt in der for-Schleife.

Die Verbindungssteuerung zur Datenbank erfolgt mit den zuvor schon erwähnten Befehlen.

Transparenz-Hinweis:

Die auf dieser Seite verwendeten Produktlinks sind sogenannte Affiliate-Links zu einem Partnerprogramm. Für dich entstehen dadurch keine Mehrkosten, ich erhalte durch deinen Kauf über den Link eine kleine Provision.

Wolfgang Raab – Betreiber von webnist.de

Mein Name ist Wolfgang, ich beschäftige mich nun schon seit 1985 mit der EDV und im Besonderen mit der Software- und Webentwicklung.

Auf diesem Blog schreibe ich über Dinge, die mich begeistern, beschäftigen und ich der Meinung bin, es könnte für andere interessant und hilfreich sein.

Als großer Fan des Raspberry Pi finde ich es spannend, wie man den kleinen Computer in Verbindung mit dem IoT und Smart Home nutzen kann. Deshalb behandeln viele Beiträge auch genau dieses Thema.

Ich bin ein leidenschaftlicher Leser und Fotograf. Wenn ich nicht gerade in einem spannenden Roman versinke, bin ich oft unterwegs, um die Schönheit der Welt mit meiner Kamera einzufangen und zu teilen.

Weitere Infos und Fotos findest du auf einem der folgenden Plattformen.

Mehr erfahren?

Besuche die "About"-Seite meines Blogs und finde heraus, wie du mit mir in den Bereichen Web- und Mobile-Programmierung und Raspberry Pi zusammenarbeiten kannst. Hier beginnt deine digitale Reise.

Unterstützen

Dieser Beitrag hat dir gefallen und konnte dir bei deinem Projekt oder Problem behilflich sein? Dann würde ich mich sehr freuen, wenn du dich mit einer noch so kleinen Spende erkenntlich zeigen möchtest.

Jonas Horn schrieb am 14. Juni 2019:

Hallo Wolfgang,
ich habe bei meinem Programm auch ein kleines Problem, und zwar beim Datum und der Zeit. Wenn ich diese jeweils über printf Ausgebe klappt es wunderbar, in die Datenbank schreibt er mir aber nur eine Zahl z.B. 2019-6-14 ergibt 1999, also er rechnet es aus und speichert es dann ab. Bei der Zeit das gleiche. Ich habe natürlich auch statt dem – ein . oder: probiert, dann kommt allerdings Syntax Fehler.
Wenn mir einer auf die Sprünge helfen könnte wäre das super!
Danke schon mal vorab
Gruß
Jonas

import os, sys, sqlite3, datetime

now = datetime.datetime.now()
PERS_NR = “400”
AUFTRAG = “123456789”
DATUM = now.strftime(‘%Y-%m-%d’)
ZEIT = now.strftime(“%H-%M-%S”)
print ZEIT
name = “ID_CPR88”
seq = “84812”

def ID_CPR88_sqlite3_anlegen():
TABLE_ID = 1
connection = sqlite3.connect(“/home/pi/ID_CPR88.sqlite3”)
cursor = connection.cursor()
sql = “CREATE TABLE ID_CPR88(“\
“TABLE_ID integer PRIMARY KEY AUTOINCREMENT, PERS_NR TEXT, AUFTRAG TEXT, \
DATUM TEXT, ZEIT TEXT) ”
cursor.execute(sql)
sql = “INSERT INTO ID_CPR88 VALUES(” + str(TABLE_ID) + “,”\
+ str(PERS_NR) + “,” \
+ str(AUFTRAG) + “,” + str(DATUM) + “,” + str(ZEIT) + “)”
cursor.execute(sql)
print(“2”)
connection.commit()
connection.close()
print “Datenbank ID_CPR88.sqlite3 mit “,sql,”Inhalt angelegt”

def userWeiter_sqlite3():
connection = sqlite3.connect(“/home/pi/ID_CPR88.sqlite3”)
cursor = connection.cursor()
sql = “INSERT INTO ID_CPR88 VALUES(” + str(TABLE_ID) + “,”\
+ str(PERS_NR) + “,” \
+ str(AUFTRAG) + “,” + str(DATUM) + “,” + str(ZEIT) + “)”
cursor.execute(sql)
connection.commit()
connection.close()
print “Datenbank ID_CPR88.sqlite3 mit “,sql,”Inhalt angelegt”

def table_id_read():
global TABLE_ID
connection = sqlite3.connect(“/home/pi/ID_CPR88.sqlite3”)
cursor = connection.cursor()
sql = “SELECT TABLE_ID FROM ID_CPR88”
cursor.execute(sql)
for dsatz in cursor:
TABLE_ID = dsatz[0]
connection.close()

if not os.path.exists(“/home/pi/ID_CPR88.sqlite3”):
print “Datenbank ID_CPR88.sqlite3 nicht vorhanden – Datenbank wird angelegt.”
ID_CPR88_sqlite3_anlegen()
#test123_sqlite3_anlegen()

table_id_read()
TABLE_ID += 1
PERS_NR = “300”
AUFTRAG = “987654321”
now = datetime.datetime.now()
DATUM = now.strftime(‘%Y-%m-%d’)
ZEIT = now.strftime(‘%H-%M-%S’)
userWeiter_sqlite3()

Antworten

Wolfgang schrieb am 17. Juni 2019:

Hallo Jonas,
ist den der Datentyp in Deiner Datenbank auch vom Typ Datetime?
Viele Grüße
Wolfgang

Antworten

stefan schrieb am 19. Juli 2018:

Hallo Wolfgang, danke für die schnelle Antwort, habe noch weiter recherchiert und probiert.
Hab es dann mit der Datenbank hinbekommen.
Allerdings bekomme ich das überschreiben der Werte nicht hin:(
Da er mir dann erzählt die Tabelle existiert schon, habe mich an Teilen von deinem Code bedient, nur leider klappt es da auch nicht so.
Hier wäre der Code, bei Zeile 77 will er das return() nicht akzeptieren, wenn ich es weg lasse, dann bricht alles nach dem ersten Durchgang ab, da die Tabelle schon existiert. Wie bekomme ich das geändert ?
wäre über deine Hilfe sehr froh.
Danke
Stefan

import RPi.GPIO as GPIO
import time
import sqlite3

GPIO.setmode(GPIO.BOARD)
GPIO.setwarnings(False)
GPIO_TRIGGER = 16
GPIO_ECHO = 18

GPIO.setup(GPIO_TRIGGER, GPIO.OUT)
GPIO.setup(GPIO_ECHO, GPIO.IN)

voll = 0 #Behaelterrand bis Wasseroberflaeche
leer = 7.7 # Behaelterrand bis Wasseroberflaeche
entfernung = 0
gesamt = 0
liter_pro_cm = 3.14*3.5*3.5 #pi * radius * radius * 1cm /1000 sonst milliliter
print (“Milliliter pro cm: %.1f” % liter_pro_cm)

def distance():
# set Trigger High
GPIO.output(GPIO_TRIGGER, True)

# set Trigger after 0.1ms low
time.sleep(0.1)
GPIO.output(GPIO_TRIGGER, False)

startTime = time.time()
endTime = time.time()

# store start time
while GPIO.input(GPIO_ECHO) == 0:
startTime = time.time()

# store arrival
while GPIO.input(GPIO_ECHO) == 1:
endTime = time.time()

# elapsed time
TimeElapsed = endTime – startTime
# multiply with speed of sound (34300 cm/s)
# and division by two
distance = (TimeElapsed * 34300) / 2
#abs = gesamt+distance
volumen = (leer-distance)*liter_pro_cm
return volumen

while True:
dist = distance()
#print (“Volumen= %.1f ml” % dist)

def userVolWerte_db_anlegen():
# Temperatur-Werte für erste Inbetriebnahme
dist = 0
connection = sqlite3.connect(“Fuellmenge.db”)
cursor = connection.cursor()
# Tabelle erzeugen
sql = “CREATE TABLE Füllmenge(Volumen FLOAT);”
cursor.execute(sql)
# Werte für erste Inbetriebnahme
sql = “INSERT INTO Füllmenge VALUES (“+ str(“%.1f” % dist) +”);”
cursor.execute(sql)
connection.commit()
connection.close()
print (“Datenbank Fuellmenge.db angelegt”)

def userVolWerte_db_auslesen():
global dist
connection = sqlite3.connect(“Fuellmenge.db”)
cursor = connection.cursor()
sql = “SELECT * FROM VolWerte”
cursor.execute(sql)
for dsatz in cursor:
dist = dsatz[0]
connection.close()
return()

def userVolWerte_db_schreiben(datenVonApp):
neuerDSatz = datenVonApp.split(‘|’)
connection = sqlite3.connect(“Fuellmenge.db”)
cursor = connection.cursor()

if neuerDSatz[1] == “dist”:
sql = “UPDATE VolWerte SET dist= ‘” + neuerDSatz[2] + “‘”
cursor.execute(sql)
connection.commit()
connection.close()
print (“Datenbank Fuellmenge.db Inhalt geändert.”)
time.sleep(0.5)

Antworten

Wolfgang schrieb am 20. Juli 2018:

Hallo Stefan,
wie ich aus Deinem Code entnehme, verwendest du Python 3. Daher dürfte es sich bei Deinem return-Fehler an der Schreibweise liegen. return() verwendet man in Python 2.7, bei 3 reicht ein return ohne ().
Hoffe ich konnte dir mit diesem Hinweis helfen. 🙂
Viele Grüße
Wolfgang
Wolfgang

Antworten

Stefan schrieb am 17. Juli 2018:

Hallo Wolfgang, habe mir nun all deine Beiträge hier angeschaut und finde diese sehr gut und ausführlich gemacht.
Ich bin noch recht neu in dem Gebiet und habe auch leider nicht so viel Hintergrundwissen fürs Ganze.

Ich habe da aber ein Problem und erhoffte mir durch deine Beiträge hier eine Lösung zu kreieren.
Habe eine Messung mit dem HC_SR4 für Volumen am laufen. Jetzt will ich die Werte die ich messe per Alexa abfragen. Dazu muss ich ja wohl oder über das ganze über AWS laufen lassen und einen Skill erstellen.
Bislang habe ich die Werte in eine txt gespeicher. So nun stoße ich bei dir auf Datenbank.
Habe auf meinem Pi nach deinem Beitrag PHP, Sql usw. eingerichtet.
Aber wie verwende ich diese ?
Wie kann ich denn jetzt die Daten da hineinspeichern ? Wo sehe ich den Ausgangspunkt und die Hinterlegung der Daten ?

Würde mich sehr über deine Unterstützung freuen
Grüße
Stefan

Antworten

Wolfgang schrieb am 19. Juli 2018:

Hallo Stefan,
vielen Dank für deinen Kommentar. Einen Skill für die Alexa-Abfrage musst du auf alle Fälle erstellen. Über AWS bietet sich die Anbindung an, kann aber auf jedem anderen Webserver erfolgen.
Die Speicherung der Daten in die Datenbank kannst du entsprechend deiner txt Sicherung aufbauen. Die in diesem Beispiel verwendete Temperatur kannst du ja durch dein Volumen ersetzen. Auslesen kannst du die Datenbank mit einem entsprechenden Skript. Allerdings möchte ich dich gleich darauf hinweisen, für die Abfrage zur Ausgabe über Alexa oder eine App muss die Tabelle über das Netz erreichbar sein. Sollte hierzu kein eigenes Hostingpaket bestehen, bieten Clouddienste wie Azure, AWS oder auch Googles Firebase die Möglichkeit die gemessenen Daten zu verarbeiten.
Grüße
Wolfgang

Antworten

Schreibe einen Kommentar Antworten abbrechen

Kleiner Hinweis, alle Kommentare werden moderiert.

Dies bedeutet, der Kommentar wird vor der Veröffentlichung durchgelesen und von mir geprüft. Auch behalte ich mir vor, jeden Kommentar zu löschen, der nicht direkt auf das Thema abzielt oder nur den Zweck hat, Leser oder Autoren herabzuwürdigen.