Python-Kurs / SQLite-Daten mit Parametern und Transaktionen speichern

Mit sqlite3 speichern Sie SQL-Daten in einer Datei, ohne einen eigenen Datenbankserver zu betreiben. Übergeben Sie Eingabewerte als Parameter und unterscheiden Sie ausdrücklich zwischen dem Bestätigen einer Transaktion, dem Zurückrollen bei Fehlern und dem Schließen der Verbindung.

Verbindung und Transaktionsregel festlegen

SQLite ist eine eingebettete Datenbank-Engine: Die Anwendung arbeitet über eine Bibliothek direkt mit der Datenbankdatei. Das ist für lokale Daten praktisch, unterscheidet sich bei gleichzeitigen Schreibzugriffen und Betrieb aber von einem Serverdienst. Eine geöffnete Verbindung bedeutet noch nicht, dass alle folgenden Änderungen dauerhaft bestätigt sind.

Das Beispiel benötigt Python 3.12 oder neuer, weil es autocommit=False ausdrücklich übergibt. Damit wird die hier verwendete Transaktionssteuerung festgelegt, statt vom Standardwert einer Version abzuhängen. Eine Transaktion bildet eine Grenze für zusammengehörige Änderungen: Entweder werden sie gemeinsam bestätigt oder bei einem Fehler zurückgenommen.

with con: bestätigt die offene Transaktion beim erfolgreichen Verlassen. Verlässt eine Ausnahme den Block, wird zurückgerollt. Der Verbindungs-Kontextmanager schließt die Verbindung jedoch nicht. Deshalb steht zusätzlich con.close() im finally-Block. Die Referenz zum Python-Modul sqlite3 erläutert die Transaktionssteuerung und Verbindungsverwaltung.

Eine Ausgabe speichern, erneut ausführen und abfragen

Verwenden Sie einen neuen Übungsordner. Die darin entstehende ledger-demo.db ist eine eigene Testdatenbank. Speichern Sie die vollständige Datei sqlite_demo.py mit UTF-8 und starten Sie in Ihrer Projektumgebung python sqlite_demo.py. Unter macOS oder Ubuntu kann außerhalb einer virtuellen Umgebung der Befehl python3 heißen; auch dort muss die ausgewählte Version mindestens 3.12 sein.

import sqlite3
MAX_AMOUNT = 1_000_000_000_000
amount = 8000
if type(amount) is not int or not 0 <= amount <= MAX_AMOUNT:
    raise ValueError("amount outside practice range")
con = sqlite3.connect("ledger-demo.db", autocommit=False)
try:
    with con:
        con.execute("CREATE TABLE IF NOT EXISTS expenses (id TEXT PRIMARY KEY, name TEXT NOT NULL, amount INTEGER NOT NULL CHECK(amount BETWEEN 0 AND 1000000000000))")
        con.execute("INSERT INTO expenses VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, amount=excluded.amount", ("e1", "lunch", amount))
    print(con.execute("SELECT name, amount FROM expenses ORDER BY id").fetchall())
finally:
    con.close()

Erwartete Ausgabe im neuen Übungsordner:

[('lunch', 8000)]

Die Tabelle speichert Kennung, Bezeichnung und ganzzahligen Betrag. Der Datensatz erhält die Kennung e1. Die Kombination aus Einfügen und Aktualisieren heißt Upsert: Existiert dieselbe Kennung bereits, werden deren Bezeichnung und Betrag geändert. Beim erneuten Start entstehen deshalb keine weiteren Kopien dieser Ausgabe. Nach zwei unveränderten Durchläufen muss weiterhin genau eine Zeile mit lunch und 8000 vorhanden sein.

Die Abfrage erfolgt nach dem erfolgreichen Schreibblock. fetchall() liest die Ergebniszeilen als Liste von Tupeln. ORDER BY id legt die Reihenfolge fest; eine zufällig beobachtete Reihenfolge ohne ORDER BY sollten Sie nicht als Vertrag verwenden. Relative Datenbankpfade beziehen sich auf das aktuelle Arbeitsverzeichnis: Wechseln Sie dieses, können Sie versehentlich eine andere neue Datei öffnen.

Parameter, Zahlenbereich und Tabellenregeln unterscheiden

Die drei Fragezeichen stehen für Werte. Das zweite Argument von execute liefert dazu ein Tupel; verwenden Sie für genau einen Wert die Form (value,) mit Komma. Ein Name mit Apostrophen oder SQL-ähnlichem Inhalt bleibt dadurch ein Wert, statt als zusätzlicher SQL-Code interpretiert zu werden. Ersetzen Sie die Parameter nicht durch zusammengesetzte SQL-Zeichenfolgen.

Tabellennamen und variable Sortierausdrücke sind dagegen Bestandteile der SQL-Struktur. Sie lassen sich nicht als gewöhnliche Wertparameter einsetzen. Wenn solche Strukturen veränderlich sein müssen, wählen Sie sie aus einer vorher festgelegten erlaubten Auswahl. Behandeln Sie nicht beliebigen Benutzereingabetext als SQL-Bezeichner.

Python prüft hier den genauen Typ int und den Bereich von null bis 1.000.000.000.000 einschließlich. Damit sind auch True, Dezimalzahlen und Zahlen als Text ausgeschlossen. Python-Ganzzahlen können weit größer sein; der SQLite-Speichertyp INTEGER hat dagegen höchstens einen vorzeichenbehafteten 64-Bit-Wert. Eine klare fachliche Grenze verhindert, dass ungeeignete Beträge erst beim Binden oder Speichern scheitern.

Die Datenbank ergänzt eine Bereichsprüfung durch CHECK, eindeutige vorhandene Kennungen durch PRIMARY KEY und Pflichtwerte für Name und Betrag durch NOT NULL. Diese Regeln ersetzen die Python-Prüfung nicht vollständig: Die Tabelle ist keine STRICT-Tabelle. Ein direkt geschriebener Betrag wie 1.5 kann im Bereich liegen und trotz der Spaltendeklaration INTEGER gespeichert werden. Auch id TEXT PRIMARY KEY verbietet in einer solchen gewöhnlichen SQLite-Tabelle nicht automatisch NULL. Wenn Ihre Anwendung dies ausschließen muss, benötigen Sie eine bewusst strengere Tabellendefinition und passende Validierung.

Die Unterschiede erklärt die SQLite-Dokumentation zu Speichertypen und Typaffinität; die Referenz zu CREATE TABLE und Constraints beschreibt die Tabellenregeln. CREATE TABLE IF NOT EXISTS ändert eine bereits vorhandene Tabelle nicht. Ein älterer Testbestand erhält dadurch keinen neuen CHECK. Für eine bestehende Arbeitsdatenbank brauchen Sie nach einer Sicherung eine geplante Schema-Migration, keine unbemerkte Neuerstellung.

Fehler, Wiederholung und Verbindungsende prüfen

1. Starten Sie das unveränderte Programm zweimal im selben Übungsordner. Öffnen Sie die erzeugte Datenbank anschließend über eine gesonderte Verbindung und prüfen Sie die Zeilenanzahl: Sie muss eins sein. Schließen Sie auch diese Prüfverbindung.
2. Ändern Sie nur amount auf -1 und starten Sie erneut. Es muss ein ValueError vor der SQL-Ausführung auftreten. Die bestehende Datenbank muss weiterhin den vorherigen Betrag enthalten. Stellen Sie 8000 anschließend wieder her.
3. Ändern Sie versuchsweise nur die einzufügende Kennung von e1 in e2. Nun entsteht eine zweite Zeile. Eine erneute Ausführung mit derselben Kennung aktualisiert wiederum nur diese Zeile. Wenn Sie die Originaldatei wiederherstellen, verschwindet die zusätzlich gespeicherte Zeile nicht automatisch; prüfen Sie solche Varianten am besten in getrennten Übungsordnern.

Eine direkte SQL-Schreiboperation mit negativem Betrag wird vom gezeigten CHECK abgelehnt, auch wenn sie die Python-Prüfung umgeht. Soll eine ganze Folge von Schreiboperationen bei einem Fehler zurückgerollt werden, lassen Sie die Ausnahme aus dem Transaktionsblock herauslaufen. Fangen und verschweigen Sie sie innerhalb des Blocks, kann dessen normales Ende vorherige Änderungen bestätigen.

Bei Sperrfehlern prüfen Sie lange offene Transaktionen und nicht geschlossene Verbindungen. Fügen Sie kein DROP TABLE hinzu, nur um einen unbekannten Datenbestand für eine Wiederholung passend zu machen. Die Grundlagen zum Weiterreichen von Fehlern und finally finden Sie im Kurs zur Ausnahmebehandlung.