Seite auswählen

20140429-123246.jpgDa ich derzeit statt Sudokus zu lösen lieber mit der Pythonista App herumspiele, darf ich mich über den einen oder anderen Aha-Effekt freuen. Ein paar zusammengewürfelte Infos will ich lieber hier ’speichern‘, damit ich später noch darauf zurückgreifen kann. Vielleicht helfen meine Notizen dem einen oder anderen ebenfalls.

Format der SQL-Befehle

Zunächst dachte ich es wäre notwendig die SQL-Befehle alle in Großschrift einzugeben, aber diese können auch problemlos in Kleinbuchstaben geschrieben werden. Allerdings habe ich festgestellt, dass die generelle Großschrift die Übersichtlichkeit für mich erhöht. Daher habe ich die Großschreibung beibehalten.

Die einzelnen SQL-Befehle können sowohl in einer Zeile durchgehend geschrieben werden, als auch durch Zeilenumbrüche in mehrere Zeilen aufgeteilt werden. Während ich in meinen Pythonskripten die Befehlszeilen in der Regel durchgehend schreibe, nutze ich zur Abfrageplanung gerne die über mehrere Zeilen aufgeteilte Schreibweise, weil die Befehle für mich dann besser erfassbar sind. Aus diesem Grund habe ich in diesem Artikel bei den Beispielen auch die mehrzeilige Schreibweise gewählt.

Bei der Zuweisung der SQL-Befehle in Python nutzt man am besten die Schreibweise mit drei Anführungszeichen, so kann man sicher sein, dass es zu keinen Problemen kommt, wenn innerhalb des SQL-Befehlsstring weitere Anführungszeichen vorkommen.

Wie kann ich eine SQLite Datenbank Datei in Pythonista erstellen?


Ich hatte angenommen, dass dies schwieriger werden würde, aber es ist ganz einfach. Man öffnet einfach die Datenbankdatei und falls diese nicht existiert, wird eine entsprechende Datei angelegt. Dabei ist es für Pythonista gleichgültig welche Dateiendung diese Datei hat:

import sqlite3 # das Modul wird natürlich benötigt
conn = sqlite3.connect('NilsDatabase.sqlite') # Öffnet (und erstellt ggf.) die Datenbank
cur = conn.cursor() # Zeigerobjekt für die Datenbank

Wichtig: Am Ende müssen Änderungen ‚commited‘ werden und man sollte die Verbindung zur Datenbank schließen. Doch dazu später mehr …

Wie kann ich mittels Python eine Tabelle in SQLite erzeugen?

Eigentlich ist es ganz einfach. Man übergibt nachdem man die Verbindung zur Datenbank aufgenommen hat die Datenbankbefehle an SQLite mittels ‚.execute‘. ‚CREATE TABLE‘ erzeugt eine neue Tabelle in der Datenbank mit den dann folgenden Spaltennamen und der Angabe, um welche Datentypen es sich handelt:

cur.execute("""CREATE TABLE NilsDaten(
  startTime datetime,
  endTime datetime,
  eventName text,
  eventNotes text);""")

Wie kann ich Daten mittels Python in eine SQLite Tabelle eintragen?

Ist ebenfalls recht einfach:

DBinsert = """INSERT INTO NilsDaten(
  startTime,
  endTime,
  eventName,
  eventNotes) VALUES(?, ?, ?, ?)""";

activityStartTime = datetime.datetime.now()
activityEndTime = activityStartTime + datetime.timedelta(hour=1)
activityName = 'Jogging'
activityNote = 'Schönes Wetter, guter Lauf!'

cur.execute(DBinsert, (activityStartTime, activityEndTime, activityName, activityNote))

Ich schreibe dazu nicht mehr, da es genügend leicht zugängliche Infos zu dem Thema gibt.

Wie kann ich überprüfen, ob der Datensatz bereits in der SQLite Datenbank vorhanden ist?

Hier entstand für mich die Frage, wie kann ich doppelte Einträge vermeiden. Da ich Daten aus anderen Quellen automatisch in meine Datenbank übernehmen wollte, könnte es leicht passieren, dass ich versehentlich zwei Mal die gleichen Daten in die Datenbank schreibe. Zunächst machte ich mich auf die Suche nach einer Variante, die Stück für Stück überprüft, ob bereits ein exakt gleicher Eintrag in der Datenbank existiert bevor ich diesen in die Datenbank schreibe.

Bei meiner Suche entdeckte ich jedoch, dass man bei SQLite nicht nur einzelne Spalten als primären (und damit einzigartigen) Index bestimmen kann, sondern dass es auch möglich ist mehrere Spalten zu solch einem einzigartigen Index bestimmen zu können. Da es in meinem Fall nur stets einen Datensatz mit exakt gleicher Startzeit, Endzeit und Ereignisnamem geben würde, habe ich die drei Tabellenspalten ’startTime‘, ‚endTime‘ und ‚eventName‘ als ‚UNIQUE‘ gekennzeichnet.

Der große Vorteil ist, dass man so gar nicht erst kompliziert prüfen muss, ob es den neuen Datensatz bereits gibt, sondern das erledigt SQLite automatisch. Man schreibt die Daten einfach in die Datenbank und falls SQLite ‚feststellt‘, dass es bereits einen Datensatz mit exakt der gleichen Startzeit, Endzeit und Ereignisnamen gibt, dann wird der neu zu schreibende Datensatz einfach ohne Fehlermeldung übergangen!.

Edit: Tja, da hatte ich etwas missverstanden. Die Kombination der drei Spalten als ‚UNIQUE‘ sorgt wirklich dafür, dass es immer nur einen Eintrag mit drei gleichen Werten in diesen Feldern gibt. Aber der Versuch einen neuen Datensatz mit der gleichen Wertekombination einzutragen, ergibt eine Fehlermeldung und den Abbruch der aktuellen SQL Anweisung.

Zur Lösung dieses Problems gibt es die ‚ON CONFLICT‘ Anweisung, die ich zunächst fälschlicherweise als Möglichkeit verstanden hatte eine Tabelle zu verändern, die bereits besteht. Es ist so, dass man mit ‚ON CONFLICT‘ beim Erstellen der Tabelle einstellen kann wie verfahren werden soll, falls beim Versuch neue Daten einzutragen ein Konflikt mit den bereits bestehenden Daten entsteht, ohne dass es zu einem Abbruch des Vorgangs mit Fehlermeldung kommt. Dabei kann man mit ‚ON CONFLICT IGNORE‘ dafür sorgen, dass im Konfliktfall die bestehenden Daten erhalten bleiben und die neuen Daten nicht eingetragen werden (ohne Fehlermeldung und Abbruch). ‚ON CONFLICT REPLACE‘ bewirkt genau das Gegenteil, d.h. die bestehenden Daten werden gelöscht und die neuen Daten werden eingetragen.

Genau was ich will.

Wie kann ich die Eigenschaften einer bereits bestehenden SQLite Tabelle ändern?

Ich wollte nun die drei Spalten auf UNIQUE setzen, aber nur ungern meine bereits mit einigen Daten aufgefüllte bestehende Tabelle verlieren. Dazu gibt es ‚ON CONFLICT REPLACE‘:

cur.execute('''CREATE TABLE NilsDaten(
  startTime datetime,
  endTime datetime,
  eventName text,
  eventNotes text,
  UNIQUE(startTime, endTime, eventName)
  ON CONFLICT REPLACE);''')

Voilà!

Das klappt so leider nicht. Ich hatte nur zufällig gerade diese Tabelle gelöscht als ich den Befehl ausprobierte. Die Möglichkeiten an Änderungen an einer bereits bestehenden Tabelle in SQLite sind leider sehr beschränkt. In vielen Fällen muss man die in der Tabelle vorhandenen Daten in eine temporäre Tabelle kopieren, eine neue Tabelle mit den gewünschten Eigenschaften erzeugen und dann die Daten aus der temporären Tabelle in die neuerzeugte Tabelle übertragen.

Komisch: keine Einträge in der SQLite Datenbank, aber auch keine Fehlermeldung!?

Das Problem hat mich lange beschäftigt. Ich versuchte Daten in die Datenbank zu schreiben und das Skript lief stets ohne Fehlermeldung durch, aber anschließend waren keine Einträge in der jeweiligen Tabelle der Datenbank zu finden -.- .

Da ich keine Erfahrung hatte, dachte ich, dass meine SQLite Syntax für das Einfügen der Werte fehlerhaft sei. Außerdem hatte ich meine Tabelle ja mit dem ‚UNIQUE‘ Befehl verändert und so nahm ich an, dass vielleicht auch hierbei etwas schief gegangen sein könnte. Bis ich bei meiner Recherche auf den trefflich betitelten Artikel ‚Python sqlite3 gotchas‚ von Matt Godbolt stieß und erst dachte: „Das kann es ja nicht sein, denn den Befehl zum endgültigen Aufnehmen der Änderungen in die Datenbank habe ich ja im Skript drin!“.

Nach dem Öffnen der Datenbank in Python, werden alle Änderungen zunächst zwischengespeichert und erst mit dem commit Befehl endgültig in die Datenbank geschrieben:

conn.commit()

In Pythonista liegt die temporäre Datei im gleichen Ordner wie die Datenbankdatei und so ist eigentlich gut zu erkennen, dass die neuen Änderungen noch nicht in die Datenbank übernommen wurden. Am Ende schließt man die Verbindung zur Datenbank:

conn.close()

Damit wird auch die temporäre Datei gelöscht, ABER ohne dass die Änderungen übernommen werden, falls man vorher keinen commit durchgeführt hat.

Und dann kam ich glücklicherweise über Matt’s Artikel auf die Idee die Sache genau zu überprüfen. Irgendwie hatte ich es geschafft beim Arbeiten im Skript den commit Befehl zu löschen. Das führte dazu, dass alle Datenbankbefehle zwar fehlerfrei durchliefen, aber die Änderungen nicht in die Datenbank übernommen wurden und ich mich fast zwei Stunden lang wunderte, warum es nicht funktioniert.

Python Skript um SQLite Datenbankinhalt zu prüfen

Gerade bei dem erwähnten langwierigen Problem, musste ich dauernd überprüfen, ob in meiner Datenbank eine neue Reihe an Daten in der gewünschten Tabelle eingetragen wurden. Ich war froh als ich das Python Script to Count Tables, Columns and Rows in SQLite Database entdeckte mit dem ich schnell Veränderungen in meiner Datenbank überprüfen konnte. Ich habe ein, zwei kleine Anpassungen an dem Skript vorgenommen und es auf GitHub hochgeladen.

Wie kann ich in Python Daten aus einer SQLite Datenbank auslesen?

Es gibt verschiedene Möglichkeiten Daten auszulesen. Für meinen Fall passt es gut mehrere Datensätze auf einmal auszulesen und nicht Reihe für Reihe einzeln. Das Auslesen wird über den ‚SELECT‘ Befehl eingeleitet:

query = '''SELECT * FROM NilsDaten;'''

cur.execute(query)
rows = cur.fetchall()

Über ‚.fetchall‘ erhält man gleich alle passenden Daten auf einmal, auf die man dann folgendermaßen zugreifen kann:

for row in rows:
  print row

Das Beispiel liest übrigens sämtliche Daten aus der Tabelle NilsDaten aus. In der Regel möchte man aber nur bestimmte Daten auslesen und nicht alle vorhandenen.

Wie kann ich die Datenabfrage in SQLite auf einen zeitlichen Rahmen begrenzen?

Grundsätzlich funktioniert die Abgrenzung von Daten über 'WHERE'. So kann man nur Datensätze auslesen bei denen z.B. der eventTitle= 'Jogging' ist.

Da SQLite viele Daten als String speichert und dies auch bei Zeit- und Datumsangaben der Fall ist1, muss man bei dem Umgang mit entsprechenden Werten aufmerksam sein. Ich habe datetime Variablen in meine SQLite-Datenbank geschrieben (Anfangs- und Endzeit inkl. Datum). Nun wollte ich Daten innerhalb eines bestimmten Zeitbereichs zwischen zwei Zeitpunkten auslesen, die ich ebenfalls als datetime-Objekte definiert hatte. Das funktioniert über strftime ausgezeichnet, da die datetime Daten in SQLite im Format YYYY-MM-TT HH:MM:SS gespeichert werden:

GetDataDateEnd = datetime.datetime.now()
GetDataDate = GetDataDateEnd - datetime.timedelta(month=1)
DataTitle = 'Jogging'

query = '''SELECT * FROM NilsDaten
  WHERE (strftime('%Y-%m-%d %H:%M:%S', startTime)
  BETWEEN '{}' AND '{}')
  AND eventTitle='{}';
  '''.format(GetDataDate, GetDataDateEnd, DataTitle)

cur.execute(query)
rows = cur.fetchall()

Wie kann ich dafür sorgen, dass die SQLite Abfrage die Daten bereits sortiert ausgibt?

Ebenfalls sehr einfach mittels 'ORDER BY':

query = '''SELECT * FROM NilsDaten
  WHERE (strftime('%Y-%m-%d %H:%M:%S', startTime)
  BETWEEN '{}' AND '{}')
  AND eventTitle='{}'
  ORDER BY startTime;
  '''.format(GetDataDate, GetDataDateEnd, DataTitle)

cur.execute(query)
rows = cur.fetchall()

Das wäre es erst einmal mit meinen Entdeckungen bei dem Arbeiten mit Python und SQLite in der Pythonista App. Fortsetzung folgt bestimmt!

Quellen und weitere Informationen:

Hilfreiche Tools:


  1. Irgendwo habe ich gelesen, dass SQLite eigentlich keine datetime Werte kennt und dies in Python erst irgendwie initiiert werden muss. Allerdings hatte ich irgenwoanders auch einen Hinweis auf die Möglichkeit gesehen, Spaltenwerte als datetime-Objekte zu deklarieren. Ich habe das einfach gemacht und die oben geschilderte Vorgehensweise funktioniert für mich – auch wenn ich nicht sicher bin, ob das alles so ‚richtig‘ ist.