Variablen in SQLite

SQLite-SQL kennt keine prozeduralen lokalen Variablen, die Sie deklarieren und über mehrere Anweisungen hinweg zuweisen können. In einer Anwendung übergeben Sie veränderliche Werte normalerweise über gebundene Parameter. Wenn mehrere Anweisungen auf derselben Verbindung Werte wiederverwenden, kann eine temporäre Tabelle den SQL-Zustand speichern. Ein allgemeiner Tabellenausdruck (CTE) benennt Werte für eine einzelne Anweisung. Diese Ansätze haben unterschiedliche Gültigkeitsbereiche. Wählen Sie daher denjenigen, der zur benötigten Lebensdauer des Werts passt.

Unterstützt SQLite Variablen?

SQLite unterstützt Parameter in vorbereiteten Anweisungen, aber die Anwendung oder der Datenbanktreiber liefert deren Werte. Ein Parameter ist ein Platzhalter für einen Wert in einer Anweisung. Er ist keine in SQL deklarierte Variable und kann auch keinen Tabellen- oder Spaltennamen ersetzen. Binden Sie beispielsweise einen Wert in SELECT * FROM products WHERE price >= ?. Verwenden Sie die Parameter-API des Treibers, um den Platzhalter mit der Eingabe zu verknüpfen.

Durch das Binden bleiben Daten und SQL-Syntax getrennt. Für Werte aus Benutzereingaben, der Konfiguration oder anderen Anwendungsdaten ist es die empfohlene Standardlösung. Wenn Sie einen Tabellen- oder Spaltennamen variieren müssen, wählen Sie ihn aus einer von der Anwendung kontrollierten Zulassungsliste aus und setzen Sie diesen Bezeichner separat zusammen. Behandeln Sie ihn nicht wie einen bindbaren Wert.

Gebundene Parameter für Werte verwenden

Das folgende Python-Beispiel verwendet das Standardmodul sqlite3, um Beispieldatensätze anzulegen und einen Mindestpreis als Parameter zu binden. Es ist ein vollständiges Beispiel für den Erfolgsfall: Der SQL-Platzhalter erhält einen Wert aus Python, statt dass Text an die Abfrage angehängt wird.

Führen Sie dieses Beispiel aus:

Der Code und die angezeigten Bezeichnungen oder Ausgaben in diesem Abschnitt bleiben auf Englisch.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE products (name TEXT, price REAL)")
connection.executemany(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    [("Notebook", 4.5), ("Keyboard", 25.0), ("Monitor", 180.0)],
)
minimum_price = 20
rows = connection.execute(
    "SELECT name, price FROM products WHERE price >= ? ORDER BY price",
    (minimum_price,),
).fetchall()
for name, price in rows:
    print(f"{name}: {price:.2f}")
connection.close()

Ein Positionsplatzhalter verwendet ?. Benannte Platzhalter können Formen wie :minimum und eine Zuordnung des Treibers wie {"minimum": 20} verwenden. Nutzen Sie den von Ihrem Treiber unterstützten Platzhalterstil und die dazugehörige Bindungsmethode. Binden Sie jeden Eingabewert, anstatt ihn in die SQL-Zeichenfolge einzufügen.

Eine temporäre Tabelle für wiederverwendbaren SQL-Zustand verwenden

Eine temporäre Tabelle ist nützlich, wenn mehrere Anweisungen auf derselben Verbindung denselben Wertesatz lesen oder ändern müssen. Erstellen Sie sie mit CREATE TEMP TABLE settings (name TEXT, value INTEGER), fügen Sie mit gewöhnlichen INSERT-Anweisungen Zeilen ein und fragen Sie sie wie jede andere Tabelle ab. Zwischen Anweisungen können Sie Zeilen aktualisieren. Wenn Sie fertig sind, löschen Sie die Tabelle mit DROP TABLE settings.

Temporäre Tabellen gehören zu der Verbindung, auf der sie erstellt wurden. Die Anweisungen auf dieser Verbindung können darauf zugreifen, bis die Tabelle gelöscht wird oder die Verbindung geschlossen ist; eine andere Verbindung kann sie nicht gemeinsam nutzen. Eine Transaktion steuert, ob Änderungen übernommen oder zurückgerollt werden. Der Gültigkeitsbereich der temporären Tabelle ist davon unabhängig und an die Verbindung gebunden. Eine temporäre Tabelle bildet SQL-Zustand in Form von Zeilen ab und ist keine prozedurale Variablendeklaration. Sie ist unnötig, wenn ein gebundener Parameter den Wert direkt übergeben kann.

Eine CTE für Werte innerhalb einer Anweisung verwenden

Eine CTE benennt ein Ergebnis, das von der darauffolgenden Anweisung verwendet wird. Zum Beispiel definiert WITH limits AS (SELECT 20 AS minimum_price) SELECT name FROM products, limits WHERE products.price >= limits.minimum_price; einen Wert und verwendet ihn in derselben Abfrage. Die CTE erstellt keine Variable, auf die spätere Anweisungen zugreifen können. Verwenden Sie sie, um Eingaben oder Zwischenergebnisse einer Abfrage übersichtlicher zu machen. Wenn Daten über mehrere Anweisungen auf derselben Verbindung verfügbar bleiben müssen, verwenden Sie eine temporäre Tabelle.

SQLite-Speicherklassen und CAST

SQLite-Werte verwenden fünf Speicherklassen: NULL, INTEGER, REAL, TEXT und BLOB. Sie beschreiben den zur Laufzeit gespeicherten Wert, nicht die Typen deklarierter Variablen. Eine Spalte kann einen deklarierten Typ haben, der ihr eine Affinität verleiht. Diese beeinflusst, wie SQLite Werte speichert und vergleicht. Das Typsystem von SQLite besteht jedoch nicht aus prozeduralen Variablendeklarationen.

CAST(expression AS type) wandelt einen Ausdruck gemäß den Konvertierungsregeln von SQLite um. Die Umwandlung gilt für das Ergebnis dieses Ausdrucks. Sie ändert keinen Parameter, erstellt keine Variable und verändert keinen anderen Wert dauerhaft. Zum Beispiel stellt CAST('42' AS INTEGER) den Textwert als Ganzzahl dar. Das Ergebnis der Konvertierung kann von der Eingabe und dem Zieltyp abhängen. Wenn das zulässige Format wichtig ist, validieren Sie die Anwendungseingabe, statt anzunehmen, dass CAST sie validiert. Eine Umwandlung eines Werts ist auch keine allgemeine Methode, um den ASCII-Code seiner Zeichen zu erhalten.

Den passenden Ansatz wählen

Verwenden Sie gebundene Parameter für Werte, die eine Anwendung bereitstellt, insbesondere für Benutzereingaben. Sie halten Werte vom SQL-Text getrennt und eignen sich für eine einzelne Anweisung, ohne zusätzlichen Zustand verwalten zu müssen. Verwenden Sie eine temporäre Tabelle, wenn mehrere Anweisungen auf derselben Verbindung einen wiederverwendbaren oder veränderlichen Zeilensatz benötigen. Eine CTE eignet sich, wenn ein Wert oder Zwischenergebnis nur in einer Anweisung gebraucht wird. Diese Werkzeuge ergänzen einander und haben unterschiedliche Lebensdauern; sie sind keine drei Möglichkeiten, eine dauerhafte SQLite-Variable zu deklarieren.