Arbeiten mit Transaktionen und dem DBAPI

Mit dem Engine-Objekt, das einsatzbereit ist, können wir uns mit den grundlegenden Operationen einer Engine und ihren primären Endpunkten, der Connection und Result, beschäftigen. Wir werden auch die Facade des ORM für diese Objekte einführen, bekannt als die Session.

Hinweis für ORM-Leser

Bei Verwendung des ORM wird die Engine von der Session verwaltet. Die Session im modernen SQLAlchemy betont ein transaktionales und SQL-Ausführungsmuster, das weitgehend identisch mit dem der unten besprochenen Connection ist. Während dieser Unterabschnitt Core-zentriert ist, sind alle hier genannten Konzepte auch für die ORM-Nutzung relevant und werden allen ORM-Lernenden empfohlen. Das von der Connection verwendete Ausführungsmuster wird am Ende dieses Abschnitts mit der Session verglichen.

Da wir die SQLAlchemy Expression Language, das Hauptmerkmal von SQLAlchemy, noch nicht vorgestellt haben, werden wir in diesem Paket einen einfachen Konstrukt namens text() verwenden, um SQL-Anweisungen als textuelles SQL zu schreiben. Seien Sie versichert, dass textuelles SQL im täglichen Gebrauch von SQLAlchemy eher die Ausnahme als die Regel ist, aber es ist immer verfügbar.

Eine Verbindung erhalten

Der Zweck der Engine ist es, eine Verbindung zur Datenbank herzustellen, indem sie ein Connection-Objekt bereitstellt. Wenn direkt mit dem Core gearbeitet wird, ist das Connection-Objekt der Punkt, über den die gesamte Interaktion mit der Datenbank erfolgt. Da die Connection eine offene Ressource gegenüber der Datenbank erstellt, wollen wir die Nutzung dieses Objekts auf einen bestimmten Kontext beschränken. Der beste Weg, dies zu tun, ist mit einem Python-Kontextmanager, auch bekannt als with-Anweisung. Unten verwenden wir eine textuelle SQL-Anweisung, um "Hello World" anzuzeigen. Textuelles SQL wird mit einem Konstrukt namens text() erstellt, das wir später genauer besprechen werden.

>>> from sqlalchemy import text

>>> with engine.connect() as conn:
...     result = conn.execute(text("select 'hello world'"))
...     print(result.all())

BEGIN (implicit)
select 'hello world'
[...] ()

[('hello world',)]

ROLLBACK

Im obigen Beispiel erstellt der Kontextmanager eine Datenbankverbindung und führt die Operation in einer Transaktion aus. Das Standardverhalten der Python DBAPI ist, dass immer eine Transaktion im Gange ist; wenn die Verbindung freigegeben wird, wird ein ROLLBACK ausgelöst, um die Transaktion zu beenden. Die Transaktion wird nicht automatisch committet; wenn wir Daten committen wollen, müssen wir Connection.commit() aufrufen, wie wir im nächsten Abschnitt sehen werden.

Tipp

"Autocommit"-Modus ist für Sonderfälle verfügbar. Der Abschnitt Festlegen von Transaktionsisolationsstufen, einschließlich DBAPI-Autocommit behandelt dies.

Das Ergebnis unseres SELECT wurde in einem Objekt namens Result zurückgegeben, das später besprochen wird. Vorerst fügen wir hinzu, dass es am besten ist, dieses Objekt innerhalb des "connect"-Blocks zu verwenden und es nicht außerhalb des Geltungsbereichs unserer Verbindung zu verwenden.

Änderungen committen

Wir haben gerade gelernt, dass die DBAPI-Verbindung nicht automatisch committet. Was ist, wenn wir Daten committen wollen? Wir können unser obiges Beispiel ändern, um eine Tabelle zu erstellen, einige Daten einzufügen und dann die Transaktion mit der Connection.commit()-Methode innerhalb des Blocks aufzurufen, in dem wir das Connection-Objekt haben.

# "commit as you go"
>>> with engine.connect() as conn:
...     conn.execute(text("CREATE TABLE some_table (x int, y int)"))
...     conn.execute(
...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
...         [{"x": 1, "y": 1}, {"x": 2, "y": 4}],
...     )
...     conn.commit()

BEGIN (implicit)
CREATE TABLE some_table (x int, y int)
[...] ()
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(1, 1), (2, 4)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

Oben führen wir zwei SQL-Anweisungen aus, eine "CREATE TABLE"-Anweisung [1] und eine "INSERT"-Anweisung, die parametrisiert ist (wir besprechen die Parametrisierungssyntax später unter Mehrere Parameter senden). Um die in unserem Block geleistete Arbeit zu committen, rufen wir die Connection.commit()-Methode auf, die die Transaktion committet. Danach können wir weitere SQL-Anweisungen ausführen und erneut Connection.commit() für diese Anweisungen aufrufen. SQLAlchemy bezeichnet diesen Stil als commit as you go.

Es gibt auch einen anderen Stil, Daten zu committen. Wir können unseren "connect"-Block von vornherein als Transaktionsblock deklarieren. Dazu verwenden wir die Engine.begin()-Methode, um die Verbindung zu erhalten, anstatt der Engine.connect()-Methode. Diese Methode verwaltet den Geltungsbereich der Connection und schließt alles im Block in eine Transaktion ein, entweder mit einem COMMIT am Ende, wenn der Block erfolgreich war, oder mit einem ROLLBACK, wenn eine Ausnahme ausgelöst wurde. Dieser Stil ist bekannt als begin once.

# "begin once"
>>> with engine.begin() as conn:
...     conn.execute(
...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
...         [{"x": 6, "y": 8}, {"x": 9, "y": 10}],
...     )

BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(6, 8), (9, 10)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

Sie sollten größtenteils den "begin once"-Stil bevorzugen, da er kürzer ist und die Absicht des gesamten Blocks von vornherein zeigt. In diesem Tutorial werden wir jedoch den "commit as you go"-Stil verwenden, da er für Demonstrationszwecke flexibler ist.

Grundlagen der Anweisungsausführung

Wir haben einige Beispiele gesehen, die SQL-Anweisungen gegen eine Datenbank ausführen, wobei wir eine Methode namens Connection.execute() in Verbindung mit einem Objekt namens text() verwenden und ein Objekt namens Result zurückgeben. In diesem Abschnitt werden wir die Mechanik und Interaktionen dieser Komponenten genauer beleuchten.

Die meisten Inhalte dieses Abschnitts gelten gleichermaßen für die moderne ORM-Nutzung bei Verwendung der Session.execute()-Methode, die sehr ähnlich wie die von Connection.execute() funktioniert. Dazu gehört auch, dass ORM-Ergebniszeilen über dieselbe Result-Schnittstelle geliefert werden, die von Core verwendet wird.

Zeilen abrufen

Wir werden das Result-Objekt genauer beleuchten, indem wir die zuvor eingefügten Zeilen verwenden und eine textuelle SELECT-Anweisung auf die erstellte Tabelle anwenden.

>>> with engine.connect() as conn:
...     result = conn.execute(text("SELECT x, y FROM some_table"))
...     for row in result:
...         print(f"x: {row.x}  y: {row.y}")

BEGIN (implicit)
SELECT x, y FROM some_table
[...] ()

x: 1  y: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10

ROLLBACK

Oben hat der ausgeführte "SELECT"-String alle Zeilen aus unserer Tabelle ausgewählt. Das zurückgegebene Objekt heißt Result und repräsentiert ein iterierbares Objekt von Ergebniszeilen.

Result verfügt über viele Methoden zum Abrufen und Transformieren von Zeilen, wie z. B. die zuvor gezeigte Methode Result.all(), die eine Liste aller Row-Objekte zurückgibt. Es implementiert auch die Python-Iterator-Schnittstelle, sodass wir direkt über die Sammlung von Row-Objekten iterieren können.

Die Row-Objekte selbst sollen sich wie Python named tuples verhalten. Unten zeigen wir eine Vielzahl von Möglichkeiten, Zeilen abzurufen.

  • Tupelzuweisung - Dies ist der Python-idiomatischste Stil, bei dem Variablen positional an jede Zeile zugewiesen werden, sobald sie empfangen werden.

    result = conn.execute(text("select x, y from some_table"))

for x, y in result:
    ...

  • Integer-Index - Tupel sind Python-Sequenzen, daher ist auch der reguläre Integer-Zugriff möglich.

    result = conn.execute(text("select x, y from some_table"))

for row in result:
    x = row[0]

  • Attributname - Da es sich um Python named tuples handelt, haben die Tupel dynamische Attributnamen, die den Namen jeder Spalte entsprechen. Diese Namen sind normalerweise die Namen, die die SQL-Anweisung den Spalten in jeder Zeile zuweist. Obwohl sie normalerweise ziemlich vorhersehbar sind und auch durch Labels gesteuert werden können, können sie in weniger definierten Fällen spezifischen Datenbankverhalten unterliegen.

    result = conn.execute(text("select x, y from some_table"))

for row in result:
    y = row.y

    # illustrate use with Python f-strings
    print(f"Row: {row.x} {y}")

  • Mapping-Zugriff - Um Zeilen als Python-Mapping-Objekte zu erhalten, was im Wesentlichen eine schreibgeschützte Version der Python-Schnittstelle für das gängige dict-Objekt ist, kann das Result mit dem Modifikator Result.mappings() in ein MappingResult-Objekt transformiert werden. Dies ist ein Ergebnisobjekt, das wörterbuchähnliche RowMapping-Objekte anstelle von Row-Objekten liefert.

    result = conn.execute(text("select x, y from some_table"))

for dict_row in result.mappings():
    x = dict_row["x"]
    y = dict_row["y"]

Parameter senden

SQL-Anweisungen werden normalerweise von Daten begleitet, die mit der Anweisung selbst übergeben werden sollen, wie wir es bereits im INSERT-Beispiel gesehen haben. Die Methode Connection.execute() akzeptiert daher auch Parameter, die als gebundene Parameter bekannt sind. Ein rudimentäres Beispiel wäre, wenn wir unsere SELECT-Anweisung nur auf Zeilen beschränken wollen, die eine bestimmte Bedingung erfüllen, wie z. B. Zeilen, bei denen der "y"-Wert größer als ein bestimmter Wert ist, der an eine Funktion übergeben wird.

Um dies so zu erreichen, dass die SQL-Anweisung fest bleibt und der Treiber den Wert ordnungsgemäß bereinigen kann, fügen wir unserer Anweisung eine WHERE-Bedingung hinzu, die einen neuen Parameter namens "y" benennt. Der text()-Konstrukt akzeptiert diese mit einem Doppelpunktformat ":y". Der tatsächliche Wert für ":y" wird dann als zweites Argument an Connection.execute() in Form eines Wörterbuchs übergeben.

>>> with engine.connect() as conn:
...     result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2})
...     for row in result:
...         print(f"x: {row.x}  y: {row.y}")

BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ?
[...] (2,)

x: 2  y: 4
x: 6  y: 8
x: 9  y: 10

ROLLBACK

In der protokollierten SQL-Ausgabe sehen wir, dass der gebundene Parameter :y in ein Fragezeichen umgewandelt wurde, als er an die SQLite-Datenbank gesendet wurde. Dies liegt daran, dass der SQLite-Datenbanktreiber ein Format namens "qmark parameter style" verwendet, was eines von sechs verschiedenen Formaten ist, die von der DBAPI-Spezifikation erlaubt sind. SQLAlchemy abstrahiert diese Formate in nur eines, nämlich das "named"-Format mit einem Doppelpunkt.

Mehrere Parameter senden

Im Beispiel unter Änderungen committen haben wir eine INSERT-Anweisung ausgeführt, bei der es so aussah, als könnten wir mehrere Zeilen auf einmal in die Datenbank einfügen. Für DML-Anweisungen wie "INSERT", "UPDATE" und "DELETE" können wir mehrere Parametersätze an die Methode Connection.execute() übergeben, indem wir eine Liste von Wörterbüchern anstelle eines einzelnen Wörterbuchs übergeben. Dies bedeutet, dass die einzelne SQL-Anweisung mehrmals aufgerufen werden soll, einmal für jeden Parametersatz. Dieser Ausführungsstil ist als executemany bekannt.

>>> with engine.connect() as conn:
...     conn.execute(
...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
...         [{"x": 11, "y": 12}, {"x": 13, "y": 14}],
...     )
...     conn.commit()

BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] [(11, 12), (13, 14)]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

Die obige Operation entspricht dem mehrmaligen Ausführen der angegebenen INSERT-Anweisung für jeden Parametersatz, außer dass die Operation für bessere Leistung bei vielen Zeilen optimiert wird.

Ein wichtiger Verhaltensunterschied zwischen "execute" und "executemany" ist, dass letzteres die Rückgabe von Ergebniszeilen nicht unterstützt, selbst wenn die Anweisung eine RETURNING-Klausel enthält. Die einzige Ausnahme ist die Verwendung eines Core insert()-Konstrukts, das später in diesem Tutorial unter Verwendung von INSERT-Anweisungen eingeführt wird und die RETURNING mit der Methode Insert.returning() angibt. In diesem Fall verwendet SQLAlchemy spezielle Logik, um die INSERT-Anweisung so zu reorganisieren, dass sie für viele Zeilen aufgerufen werden kann, während RETURNING weiterhin unterstützt wird.

Siehe auch

executemany - im Glossar, beschreibt die DBAPI-spezifische Methode cursor.executemany(), die für die meisten "executemany"-Ausführungen verwendet wird.

Verhalten von "Insert Many Values" für INSERT-Anweisungen - in Arbeiten mit Engines und Connections, beschreibt die spezialisierte Logik, die von Insert.returning() verwendet wird, um Ergebnisdatensätze mit "executemany"-Ausführungen zu liefern.

Ausführung mit einer ORM-Sitzung

Wie bereits erwähnt, gelten die meisten der oben genannten Muster und Beispiele auch für die Verwendung mit dem ORM. Daher werden wir hier diese Verwendung einführen, damit wir im weiteren Verlauf des Tutorials jedes Muster sowohl für Core als auch für ORM darstellen können.

Das grundlegende transaktionale / datenbankinteraktive Objekt bei der Verwendung des ORM heißt Session. Im modernen SQLAlchemy wird dieses Objekt auf eine Weise verwendet, die der Connection sehr ähnlich ist. Tatsächlich bezieht sich die Session, wenn sie verwendet wird, intern auf eine Connection, die sie zur Ausgabe von SQL verwendet.

Wenn die Session mit nicht-ORM-Konstrukten verwendet wird, leitet sie die von uns übergebenen SQL-Anweisungen weiter und verhält sich im Allgemeinen nicht viel anders als die Connection direkt. Daher können wir sie hier anhand der einfachen textuellen SQL-Operationen illustrieren, die wir bereits kennengelernt haben.

Die Session hat einige verschiedene Erstellungsmuster, aber hier werden wir das grundlegendste illustrieren, das genau mit der Verwendung der Connection übereinstimmt, nämlich die Konstruktion innerhalb eines Kontextmanagers.

>>> from sqlalchemy.orm import Session

>>> stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y")
>>> with Session(engine) as session:
...     result = session.execute(stmt, {"y": 6})
...     for row in result:
...         print(f"x: {row.x}  y: {row.y}")

BEGIN (implicit)
SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
[...] (6,)

x: 6  y: 8
x: 9  y: 10
x: 11  y: 12
x: 13  y: 14

ROLLBACK

Das obige Beispiel kann mit dem Beispiel im vorhergehenden Abschnitt unter Parameter senden verglichen werden – wir ersetzen den Aufruf von with engine.connect() as conn direkt durch with Session(engine) as session und verwenden dann die Session.execute()-Methode genauso, wie wir es mit der Connection.execute()-Methode tun.

Ebenso wie die Connection verfügt die Session über das "commit as you go"-Verhalten mithilfe der Session.commit()-Methode, die unten mithilfe einer textuellen UPDATE-Anweisung zur Änderung einiger unserer Daten illustriert wird.

>>> with Session(engine) as session:
...     result = session.execute(
...         text("UPDATE some_table SET y=:y WHERE x=:x"),
...         [{"x": 9, "y": 11}, {"x": 13, "y": 15}],
...     )
...     session.commit()

BEGIN (implicit)
UPDATE some_table SET y=? WHERE x=?
[...] [(11, 9), (15, 13)]
COMMIT

Oben haben wir eine UPDATE-Anweisung mit dem gebundenen Parameter, dem unter Mehrere Parameter senden eingeführten "executemany"-Stil ausgeführt und den Block mit einem "commit as you go"-Commit beendet.

Tipp

Die Session hält die Connection-Objekt nicht tatsächlich fest, nachdem sie die Transaktion beendet hat. Sie holt sich jedes Mal eine neue Connection von der Engine, wenn sie das nächste Mal SQL gegen die Datenbank ausführen muss.

Die Session hat offensichtlich noch viele weitere Tricks auf Lager, aber das Verständnis, dass sie über eine Session.execute()-Methode verfügt, die genauso verwendet wird wie Connection.execute(), wird uns für die folgenden Beispiele den Einstieg erleichtern.

Siehe auch

Grundlagen der Sitzungsverwendung - stellt grundlegende Erstellungs- und Verwendungsmuster mit dem Session-Objekt vor.