Verwendung von UPDATE- und DELETE-Anweisungen

Bisher haben wir Insert behandelt, damit wir Daten in unsere Datenbank bekommen können, und dann viel Zeit mit Select verbracht, das die breite Palette von Nutzungsmustern für den Abruf von Daten aus der Datenbank handhabt. In diesem Abschnitt behandeln wir die Update- und Delete-Konstrukte, die zum Ändern bestehender Zeilen sowie zum Löschen bestehender Zeilen verwendet werden. Dieser Abschnitt behandelt diese Konstrukte aus einer Core-zentrierten Perspektive.

ORM-Leser - Wie bereits in Verwendung von INSERT-Anweisungen erwähnt, werden die Update- und Delete-Operationen, wenn sie mit dem ORM verwendet werden, normalerweise intern von der Session-Objekt als Teil des Unit of Work-Prozesses aufgerufen.

Im Gegensatz zu Insert können die Update- und Delete-Konstrukte jedoch auch direkt mit dem ORM verwendet werden, unter Verwendung eines Musters, das als "ORM-fähige Aktualisierung und Löschung" bekannt ist; aus diesem Grund ist die Vertrautheit mit diesen Konstrukten für die ORM-Nutzung nützlich. Beide Nutzungsstile werden in den Abschnitten ORM-Objekte mit dem Unit-of-Work-Muster aktualisieren und ORM-Objekte mit dem Unit-of-Work-Muster löschen diskutiert.

Der UPDATE SQL Expression Construct

Die Funktion update() generiert eine neue Instanz von Update, die eine UPDATE-Anweisung in SQL darstellt und vorhandene Daten in einer Tabelle aktualisiert.

Wie der insert()-Konstrukt gibt es eine "traditionelle" Form von update(), die UPDATEs gegen eine einzelne Tabelle auf einmal ausgibt und keine Zeilen zurückgibt. Einige Backends unterstützen jedoch eine UPDATE-Anweisung, die mehrere Tabellen gleichzeitig ändern kann, und die UPDATE-Anweisung unterstützt auch RETURNING, sodass Spalten, die in übereinstimmenden Zeilen enthalten sind, im Ergebnissatz zurückgegeben werden können.

Ein grundlegendes UPDATE sieht so aus:

>>> from sqlalchemy import update
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == "patrick")
...     .values(fullname="Patrick the Star")
... )
>>> print(stmt)

UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1

Die Methode Update.values() steuert den Inhalt der SET-Elemente der UPDATE-Anweisung. Dies ist dieselbe Methode, die auch vom Insert-Konstrukt verwendet wird. Parameter können normalerweise unter Verwendung der Spaltennamen als Schlüsselwortargumente übergeben werden.

UPDATE unterstützt alle wichtigen SQL-Formen von UPDATE, einschließlich Aktualisierungen von Ausdrücken, bei denen wir Column-Ausdrücke verwenden können.

>>> stmt = update(user_table).values(fullname="Username: " + user_table.c.name)
>>> print(stmt)

UPDATE user_account SET fullname=(:name_1 || user_account.name)

Um UPDATE im "executemany"-Kontext zu unterstützen, bei dem viele Parametersätze auf dieselbe Anweisung angewendet werden, kann der bindparam()-Konstrukt verwendet werden, um gebundene Parameter einzurichten; diese ersetzen die Stellen, an denen normalerweise Literalwerte stünden.

>>> from sqlalchemy import bindparam
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == bindparam("oldname"))
...     .values(name=bindparam("newname"))
... )
>>> with engine.begin() as conn:
...     conn.execute(
...         stmt,
...         [
...             {"oldname": "jack", "newname": "ed"},
...             {"oldname": "wendy", "newname": "mary"},
...             {"oldname": "jim", "newname": "jake"},
...         ],
...     )

BEGIN (implicit)
UPDATE user_account SET name=? WHERE user_account.name = ?
[...] [('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')]
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

Andere Techniken, die auf UPDATE angewendet werden können, umfassen:

Korrelierte Updates

Eine UPDATE-Anweisung kann Zeilen aus anderen Tabellen verwenden, indem sie eine korrelierte Unterabfrage verwendet. Eine Unterabfrage kann überall dort verwendet werden, wo ein Spaltenausdruck platziert werden könnte.

>>> scalar_subq = (
...     select(address_table.c.email_address)
...     .where(address_table.c.user_id == user_table.c.id)
...     .order_by(address_table.c.id)
...     .limit(1)
...     .scalar_subquery()
... )
>>> update_stmt = update(user_table).values(fullname=scalar_subq)
>>> print(update_stmt)

UPDATE user_account SET fullname=(SELECT address.email_address
FROM address
WHERE address.user_id = user_account.id ORDER BY address.id
LIMIT :param_1)

UPDATE..FROM

Einige Datenbanken wie PostgreSQL und MySQL unterstützen eine Syntax "UPDATE FROM", bei der zusätzliche Tabellen direkt in einer speziellen FROM-Klausel angegeben werden können. Diese Syntax wird implizit generiert, wenn zusätzliche Tabellen in der WHERE-Klausel der Anweisung gefunden werden.

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(fullname="Pat")
... )
>>> print(update_stmt)

UPDATE user_account SET fullname=:fullname FROM address
WHERE user_account.id = address.user_id AND address.email_address = :email_address_1

Es gibt auch eine MySQL-spezifische Syntax, die mehrere Tabellen aktualisieren kann. Dies erfordert, dass wir uns auf Table-Objekte in der VALUES-Klausel beziehen, um auf zusätzliche Tabellen zu verweisen.

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(
...         {
...             user_table.c.fullname: "Pat",
...             address_table.c.email_address: "pat@aol.com",
...         }
...     )
... )
>>> from sqlalchemy.dialects import mysql
>>> print(update_stmt.compile(dialect=mysql.dialect()))

UPDATE user_account, address
SET address.email_address=%s, user_account.fullname=%s
WHERE user_account.id = address.user_id AND address.email_address = %s

Parameter-geordnete Updates

Ein weiteres MySQL-spezifisches Verhalten ist, dass die Reihenfolge der Parameter in der SET-Klausel eines UPDATE die Auswertung jedes Ausdrucks beeinflusst. Für diesen Anwendungsfall akzeptiert die Methode Update.ordered_values() eine Sequenz von Tupeln, damit diese Reihenfolge gesteuert werden kann [2].

>>> update_stmt = update(some_table).ordered_values(
...     (some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)
... )
>>> print(update_stmt)

UPDATE some_table SET y=:y, x=(some_table.y + :y_1)

Der DELETE SQL Expression Construct

Die Funktion delete() generiert eine neue Instanz von Delete, die eine DELETE-Anweisung in SQL darstellt und Zeilen aus einer Tabelle löscht.

Die delete()-Anweisung ist aus API-Sicht der update()-Konstruktion sehr ähnlich und gibt traditionell keine Zeilen zurück, erlaubt aber auf einigen Datenbank-Backends eine RETURNING-Variante.

>>> from sqlalchemy import delete
>>> stmt = delete(user_table).where(user_table.c.name == "patrick")
>>> print(stmt)

DELETE FROM user_account WHERE user_account.name = :name_1

Mehrfachtabellen-Löschungen

Wie Update unterstützt Delete die Verwendung von korrelierten Unterabfragen in der WHERE-Klausel sowie Back-end-spezifische Mehrfachtabellen-Syntaxen wie DELETE FROM..USING unter MySQL.

>>> delete_stmt = (
...     delete(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
... )
>>> from sqlalchemy.dialects import mysql
>>> print(delete_stmt.compile(dialect=mysql.dialect()))

DELETE FROM user_account USING user_account, address
WHERE user_account.id = address.user_id AND address.email_address = %s

Abrufen der Anzahl betroffener Zeilen aus UPDATE, DELETE

Sowohl Update als auch Delete unterstützen die Möglichkeit, die Anzahl der übereinstimmenden Zeilen nach Abschluss der Anweisung zurückzugeben, für Anweisungen, die über die Core Connection ausgeführt werden, d.h. Connection.execute(). Gemäß den unten genannten Einschränkungen ist dieser Wert über das Attribut CursorResult.rowcount verfügbar.

>>> with engine.begin() as conn:
...     result = conn.execute(
...         update(user_table)
...         .values(fullname="Patrick McStar")
...         .where(user_table.c.name == "patrick")
...     )
...     print(result.rowcount)

BEGIN (implicit)
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] ('Patrick McStar', 'patrick')

1

COMMIT

Tipp

Die Klasse CursorResult ist eine Unterklasse von Result, die zusätzliche Attribute enthält, die spezifisch für das DBAPI cursor -Objekt sind. Eine Instanz dieser Unterklasse wird zurückgegeben, wenn eine Anweisung über die Methode Connection.execute() aufgerufen wird. Bei Verwendung des ORM gibt die Methode Session.execute() für alle INSERT-, UPDATE- und DELETE-Anweisungen ein Objekt dieses Typs zurück.

Fakten zu CursorResult.rowcount

  • Der zurückgegebene Wert ist die Anzahl der Zeilen, die von der WHERE-Klausel der Anweisung **übereinstimmen**. Es spielt keine Rolle, ob die Zeilen tatsächlich geändert wurden oder nicht.

  • CursorResult.rowcount ist nicht unbedingt verfügbar für eine UPDATE- oder DELETE-Anweisung, die RETURNING verwendet, oder für eine, die eine executemany-Ausführung verwendet. Die Verfügbarkeit hängt vom verwendeten DBAPI-Modul ab.

  • In allen Fällen, in denen das DBAPI den rowcount für einen bestimmten Anweisungstyp nicht ermittelt, ist der zurückgegebene Wert -1.

  • SQLAlchemy speichert den Wert cursor.rowcount des DBAPI vor dem Schließen des Cursors, da einige DBAPIs den Zugriff auf dieses Attribut nachträglich nicht unterstützen. Um cursor.rowcount für eine Anweisung vorab zu speichern, die keine UPDATE- oder DELETE-Anweisung ist, wie z. B. INSERT oder SELECT, kann die Ausführungsoption Connection.execution_options.preserve_rowcount verwendet werden.

  • Einige Treiber, insbesondere Drittanbieter-Dialekte für nichtrelationale Datenbanken, unterstützen CursorResult.rowcount möglicherweise überhaupt nicht. Das Cursor-Attribut CursorResult.supports_sane_rowcount zeigt dies an.

  • "rowcount" wird vom ORM Unit of Work-Prozess verwendet, um zu validieren, dass eine UPDATE- oder DELETE-Anweisung die erwartete Anzahl von Zeilen übereingestimmt hat, und ist auch für die ORM-Versioning-Funktion unerlässlich, die unter Konfigurieren eines Versionszählers dokumentiert ist.

Verwendung von RETURNING mit UPDATE, DELETE

Wie der Insert-Konstrukt unterstützen auch Update und Delete die RETURNING-Klausel, die durch die Verwendung der Methoden Update.returning() und Delete.returning() hinzugefügt wird. Wenn diese Methoden auf einem Backend verwendet werden, das RETURNING unterstützt, werden ausgewählte Spalten aus allen Zeilen, die den WHERE-Kriterien der Anweisung entsprechen, im Result-Objekt als Zeilen zurückgegeben, über die iteriert werden kann.

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.name == "patrick")
...     .values(fullname="Patrick the Star")
...     .returning(user_table.c.id, user_table.c.name)
... )
>>> print(update_stmt)

UPDATE user_account SET fullname=:fullname
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name

>>> delete_stmt = (
...     delete(user_table)
...     .where(user_table.c.name == "patrick")
...     .returning(user_table.c.id, user_table.c.name)
... )
>>> print(delete_stmt)

DELETE FROM user_account
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name

Weiterführende Lektüre zu UPDATE, DELETE

Siehe auch

API-Dokumentation für UPDATE / DELETE

ORM-fähige UPDATE und DELETE

ORM-fähige INSERT-, UPDATE- und DELETE-Anweisungen - im ORM-Abfragehandbuch