Verwenden von INSERT-Anweisungen

Sowohl bei der Verwendung von Core als auch bei der Verwendung von ORM für Massenoperationen wird eine SQL INSERT-Anweisung direkt über die insert()-Funktion generiert. Diese Funktion generiert eine neue Instanz von Insert, die eine INSERT-Anweisung in SQL darstellt und neue Daten in eine Tabelle einfügt.

ORM-Leser -

Dieser Abschnitt beschreibt die Core-Methode zur Generierung einer einzelnen SQL INSERT-Anweisung, um neue Zeilen zu einer Tabelle hinzuzufügen. Bei der Verwendung von ORM verwenden wir normalerweise ein anderes Werkzeug, das darauf aufbaut, die Unit of Work, die die Erstellung vieler INSERT-Anweisungen auf einmal automatisiert. Das Verständnis, wie Core Daten erstellt und manipuliert, ist jedoch auch dann sehr nützlich, wenn ORM dies für uns ausführt. Zusätzlich unterstützt ORM die direkte Verwendung von INSERT mit einer Funktion namens Bulk / Multi Row INSERT, Upsert, UPDATE und DELETE.

Um direkt zu erfahren, wie Zeilen mit ORM mithilfe normaler Unit-of-Work-Muster eingefügt werden, siehe Einfügen von Zeilen mit dem ORM Unit of Work-Muster.

Die insert()-SQL-Ausdruckskonstruktion

Ein einfaches Beispiel für Insert, das die Zieltabellen- und die VALUES-Klausel gleichzeitig illustriert

>>> from sqlalchemy import insert
>>> stmt = insert(user_table).values(name="spongebob", fullname="Spongebob Squarepants")

Die obige Variable stmt ist eine Instanz von Insert. Die meisten SQL-Ausdrücke können an Ort und Stelle als Zeichenkette dargestellt werden, um die allgemeine Form dessen zu sehen, was produziert wird

>>> print(stmt)

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)

Die als Zeichenkette dargestellte Form wird durch die Erzeugung einer Compiled-Form des Objekts erstellt, die eine datenbankspezifische SQL-Zeichenkettendarstellung der Anweisung enthält. Wir können dieses Objekt direkt über die Methode ClauseElement.compile() abrufen

>>> compiled = stmt.compile()

Unsere Insert-Konstruktion ist ein Beispiel für eine "parametrisierte" Konstruktion, die zuvor unter Senden von Parametern illustriert wurde. Um die name und fullname der gebundenen Parameter anzuzeigen, sind diese ebenfalls aus der Compiled-Konstruktion verfügbar

>>> compiled.params
{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

Ausführen der Anweisung

Durch Aufrufen der Anweisung können wir eine Zeile in user_table einfügen. Die INSERT-SQL-Anweisung sowie die gebündelten Parameter können im SQL-Logging eingesehen werden.

>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     conn.commit()

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('spongebob', 'Spongebob Squarepants')
COMMIT

In ihrer einfachen Form gibt die INSERT-Anweisung keine Zeilen zurück, und wenn nur eine einzelne Zeile eingefügt wird, enthält sie normalerweise die Möglichkeit, Informationen über spaltenbezogene Standardwerte zurückzugeben, die während des INSERT dieser Zeile generiert wurden, am häufigsten einen ganzzahligen Primärschlüsselwert. Im obigen Fall gibt die erste Zeile in einer SQLite-Datenbank normalerweise 1 für den ersten ganzzahligen Primärschlüsselwert zurück, den wir mit dem Zugriffsattribut CursorResult.inserted_primary_key abrufen können.

>>> result.inserted_primary_key
(1,)

Tipp

CursorResult.inserted_primary_key gibt ein Tupel zurück, da ein Primärschlüssel mehrere Spalten enthalten kann. Dies wird als zusammengesetzter Primärschlüssel bezeichnet. CursorResult.inserted_primary_key soll immer den vollständigen Primärschlüssel des gerade eingefügten Datensatzes enthalten, nicht nur einen Wert vom Typ "cursor.lastrowid", und soll auch dann gefüllt werden, wenn "autoincrement" verwendet wurde oder nicht, daher ist es ein Tupel, um einen vollständigen Primärschlüssel auszudrücken.

Geändert in Version 1.4.8: Das von CursorResult.inserted_primary_key zurückgegebene Tupel ist jetzt ein benanntes Tupel, das als Row-Objekt zurückgegeben wird.

INSERT generiert normalerweise automatisch die "values"-Klausel

Das obige Beispiel verwendete die Methode Insert.values(), um explizit die VALUES-Klausel der SQL INSERT-Anweisung zu erstellen. Wenn wir Insert.values() nicht tatsächlich verwenden und nur eine "leere" Anweisung ausgeben, erhalten wir ein INSERT für jede Spalte in der Tabelle

>>> print(insert(user_table))

INSERT INTO user_account (id, name, fullname) VALUES (:id, :name, :fullname)

Wenn wir eine Insert-Konstruktion nehmen, auf die Insert.values() nicht angewendet wurde, und diese anstelle der Ausgabe ausführen, wird die Anweisung basierend auf den Parametern, die wir an die Methode Connection.execute() übergeben haben, in eine Zeichenkette kompiliert und enthält nur die für die übergebenen Parameter relevanten Spalten. Dies ist tatsächlich die übliche Art und Weise, wie Insert verwendet wird, um Zeilen einzufügen, ohne eine explizite VALUES-Klausel tippen zu müssen. Das folgende Beispiel zeigt eine zspaltige INSERT-Anweisung, die mit einer Liste von Parametern gleichzeitig ausgeführt wird.

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(user_table),
...         [
...             {"name": "sandy", "fullname": "Sandy Cheeks"},
...             {"name": "patrick", "fullname": "Patrick Star"},
...         ],
...     )
...     conn.commit()

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] [('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star')]
COMMIT

Die obige Ausführung zeigt die "executemany"-Form, die zuerst unter Senden mehrerer Parameter gezeigt wurde. Im Gegensatz zur Verwendung der text()-Konstruktion mussten wir jedoch keine SQL-Befehle tippen. Durch die Übergabe eines Wörterbuchs oder einer Liste von Wörterbüchern an die Methode Connection.execute() in Verbindung mit der Insert-Konstruktion stellt die Connection sicher, dass die übergebenen Spaltennamen automatisch in die VALUES-Klausel der Insert-Konstruktion aufgenommen werden.

Deep Alchemy

Hallo, willkommen zur ersten Ausgabe von **Deep Alchemy**. Die Person auf der linken Seite ist bekannt als **Der Alchemist**, und Sie werden feststellen, dass sie **kein** Zauberer ist, da der spitze Hut nicht nach oben ragt. Der Alchemist beschreibt Dinge, die im Allgemeinen **fortgeschrittener und/oder kniffliger** sind und **nicht unbedingt benötigt** werden, aber aus irgendeinem Grund der Meinung ist, dass Sie über diese Funktion von SQLAlchemy Bescheid wissen sollten.

In dieser Ausgabe wird, um auch interessante Daten in der address_table zu haben, ein fortgeschritteneres Beispiel gezeigt, das illustriert, wie die Methode Insert.values() explizit verwendet werden kann und gleichzeitig zusätzliche VALUES aus den Parametern generiert. Eine Skalare Unterabfrage wird konstruiert, wobei die select()-Konstruktion verwendet wird, die im nächsten Abschnitt vorgestellt wird, und die in der Unterabfrage verwendeten Parameter werden mithilfe eines expliziten gebundenen Parameternamens eingerichtet, der mit der bindparam()-Konstruktion festgelegt wird.

Dies ist ein etwas **tiefergehendes** Alchemie-Wissen, damit wir verwandte Zeilen hinzufügen können, ohne die Primärschlüssel-Identifikatoren aus der user_table-Operation in die Anwendung zu laden. Die meisten Alchemisten verwenden einfach ORM, das solche Dinge für uns erledigt.

>>> from sqlalchemy import select, bindparam
>>> scalar_subq = (
...     select(user_table.c.id)
...     .where(user_table.c.name == bindparam("username"))
...     .scalar_subquery()
... )

>>> with engine.connect() as conn:
...     result = conn.execute(
...         insert(address_table).values(user_id=scalar_subq),
...         [
...             {
...                 "username": "spongebob",
...                 "email_address": "spongebob@sqlalchemy.org",
...             },
...             {"username": "sandy", "email_address": "sandy@sqlalchemy.org"},
...             {"username": "sandy", "email_address": "sandy@squirrelpower.org"},
...         ],
...     )
...     conn.commit()

BEGIN (implicit)
INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?)
[...] [('spongebob', 'spongebob@sqlalchemy.org'), ('sandy', 'sandy@sqlalchemy.org'),
('sandy', 'sandy@squirrelpower.org')]
COMMIT

Damit haben wir einige interessantere Daten in unseren Tabellen, die wir in den folgenden Abschnitten verwenden werden.

Tipp

Ein echtes "leeres" INSERT, das nur die "Defaults" für eine Tabelle einfügt, ohne explizite Werte zu übergeben, wird generiert, wenn wir Insert.values() ohne Argumente angeben. Nicht jedes Datenbanksystem unterstützt dies, aber hier ist, was SQLite produziert.

>>> print(insert(user_table).values().compile(engine))

INSERT INTO user_account DEFAULT VALUES

INSERT…RETURNING

Die RETURNING-Klausel wird für unterstützte Backends automatisch verwendet, um den zuletzt eingefügten Primärschlüsselwert sowie die Werte für Server-Defaults abzurufen. Die RETURNING-Klausel kann jedoch auch explizit über die Methode Insert.returning() angegeben werden. In diesem Fall enthält das Result-Objekt, das beim Ausführen der Anweisung zurückgegeben wird, Zeilen, die abgerufen werden können.

>>> insert_stmt = insert(address_table).returning(
...     address_table.c.id, address_table.c.email_address
... )
>>> print(insert_stmt)

INSERT INTO address (id, user_id, email_address)
VALUES (:id, :user_id, :email_address)
RETURNING address.id, address.email_address

Sie kann auch mit Insert.from_select() kombiniert werden, wie im folgenden Beispiel, das auf dem Beispiel in INSERT…FROM SELECT aufbaut.

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))

INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account RETURNING address.id, address.email_address

Tipp

Das RETURNING-Feature wird auch von UPDATE- und DELETE-Anweisungen unterstützt, die später in diesem Tutorial vorgestellt werden.

Für INSERT-Anweisungen kann das RETURNING-Feature sowohl für einzelne Zeilen als auch für Anweisungen verwendet werden, die mehrere Zeilen auf einmal einfügen. Die Unterstützung für das Einfügen mehrerer Zeilen mit RETURNING ist dialektabhängig, wird aber für alle von SQLAlchemy unterstützten Dialekte, die RETURNING unterstützen, unterstützt. Siehe den Abschnitt "Insert Many Values"-Verhalten für INSERT-Anweisungen für Hintergrundinformationen zu diesem Feature.

Siehe auch

Bulk INSERT mit oder ohne RETURNING wird auch von ORM unterstützt. Siehe ORM Bulk INSERT-Anweisungen für Referenzdokumentation.

INSERT…FROM SELECT

Eine weniger genutzte Funktion von Insert, aber der Vollständigkeit halber hier: Die Insert-Konstruktion kann ein INSERT erstellen, das Zeilen direkt aus einem SELECT mithilfe der Methode Insert.from_select() bezieht. Diese Methode akzeptiert eine select()-Konstruktion, die im nächsten Abschnitt besprochen wird, zusammen mit einer Liste von Spaltennamen, die in das eigentliche INSERT aufgenommen werden sollen. Im folgenden Beispiel werden Zeilen zur address-Tabelle hinzugefügt, die aus Zeilen der user_account-Tabelle stammen und jedem Benutzer eine kostenlose E-Mail-Adresse unter aol.com geben.

>>> select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
>>> insert_stmt = insert(address_table).from_select(
...     ["user_id", "email_address"], select_stmt
... )
>>> print(insert_stmt)

INSERT INTO address (user_id, email_address)
SELECT user_account.id, user_account.name || :name_1 AS anon_1
FROM user_account

Diese Konstruktion wird verwendet, wenn Daten aus einem anderen Teil der Datenbank direkt in neue Zeilen kopiert werden sollen, ohne die Daten tatsächlich vom Client abzurufen und neu zu senden.

Siehe auch

Insert – in der Dokumentation der SQL Expression API