Zusätzliche Persistenztechniken

Einbettung von SQL INSERT/UPDATE-Ausdrücken in einen Flush

Diese Funktion ermöglicht es, den Wert einer Datenbankspalte auf einen SQL-Ausdruck anstatt auf einen Literalwert zu setzen. Dies ist besonders nützlich für atomare Updates, das Aufrufen von Stored Procedures usw. Sie weisen einfach einen Ausdruck einem Attribut zu

class SomeClass(Base):
    __tablename__ = "some_table"

    # ...

    value = mapped_column(Integer)


someobject = session.get(SomeClass, 5)

# set 'value' attribute to a SQL expression adding one
someobject.value = SomeClass.value + 1

# issues "UPDATE some_table SET value=value+1"
session.commit()

Diese Technik funktioniert sowohl für INSERT- als auch für UPDATE-Anweisungen. Nach dem Flush/Commit-Vorgang ist das Attribut value auf someobject abgelaufen, sodass beim nächsten Zugriff der neu generierte Wert aus der Datenbank geladen wird.

Die Funktion unterstützt auch bedingt die Arbeit in Verbindung mit Primärschlüsselspalten. Für Backends, die RETURNING unterstützen (einschließlich Oracle Database, SQL Server, MariaDB 10.5, SQLite 3.35), kann ein SQL-Ausdruck auch einer Primärschlüsselspalte zugewiesen werden. Dies ermöglicht sowohl die Auswertung des SQL-Ausdrucks als auch das erfolgreiche Abrufen von serverseitigen Triggern, die den Primärschlüsselwert bei einem INSERT ändern, durch die ORM als Teil des Primärschlüssels des Objekts.

class Foo(Base):
    __tablename__ = "foo"
    pk = mapped_column(Integer, primary_key=True)
    bar = mapped_column(Integer)


e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)

session = Session(e)

foo = Foo(pk=sql.select(sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)))
session.add(foo)
session.commit()

Unter PostgreSQL gibt die obige Session die folgende INSERT-Anweisung aus

INSERT INTO foo (foopk, bar) VALUES
((SELECT coalesce(max(foo.foopk) + %(max_1)s, %(coalesce_2)s) AS coalesce_1
FROM foo), %(bar)s) RETURNING foo.foopk

Neu in Version 1.3: SQL-Ausdrücke können jetzt einer Primärschlüsselspalte während eines ORM-Flushes übergeben werden; wenn die Datenbank RETURNING unterstützt oder wenn pysqlite verwendet wird, kann die ORM den serverseitig generierten Wert als Wert des Primärschlüsselattributs abrufen.

Verwendung von SQL-Ausdrücken mit Sessions

SQL-Ausdrücke und Zeichenfolgen können über die Session innerhalb ihres transaktionalen Kontexts ausgeführt werden. Dies geschieht am einfachsten über die Methode Session.execute(), die ein CursorResult auf die gleiche Weise zurückgibt wie eine Engine oder Connection

Session = sessionmaker(bind=engine)
session = Session()

# execute a string statement
result = session.execute(text("select * from table where id=:id"), {"id": 7})

# execute a SQL expression construct
result = session.execute(select(mytable).where(mytable.c.id == 7))

Die aktuelle Connection, die von der Session gehalten wird, ist über die Methode Session.connection() zugänglich

connection = session.connection()

Die obigen Beispiele behandeln eine Session, die an eine einzelne Engine oder Connection gebunden ist. Um Anweisungen über eine Session auszuführen, die entweder an mehrere Engines gebunden ist oder gar keine (d. h. auf gebundene Metadaten angewiesen ist), akzeptieren sowohl Session.execute() als auch Session.connection() ein Dictionary von Bindungsargumenten Session.execute.bind_arguments, das "mapper" enthalten kann, das an eine gemappte Klasse oder eine Mapper-Instanz übergeben wird, die verwendet wird, um den richtigen Kontext für die gewünschte Engine zu finden.

Session = sessionmaker()
session = Session()

# need to specify mapper or class when executing
result = session.execute(
    text("select * from table where id=:id"),
    {"id": 7},
    bind_arguments={"mapper": MyMappedClass},
)

result = session.execute(
    select(mytable).where(mytable.c.id == 7), bind_arguments={"mapper": MyMappedClass}
)

connection = session.connection(MyMappedClass)

Geändert in Version 1.4: die Argumente mapper und clause an Session.execute() werden jetzt als Teil eines Dictionarys übergeben, das als Parameter Session.execute.bind_arguments gesendet wird. Die vorherigen Argumente werden immer noch akzeptiert, aber diese Verwendung ist veraltet.

NULL auf einer Spalte mit Standardwert erzwingen

Die ORM betrachtet jedes Attribut, das nie auf einem Objekt gesetzt wurde, als "Standardfall"; das Attribut wird von der INSERT-Anweisung weggelassen.

class MyObject(Base):
    __tablename__ = "my_table"
    id = mapped_column(Integer, primary_key=True)
    data = mapped_column(String(50), nullable=True)


obj = MyObject(id=1)
session.add(obj)
session.commit()  # INSERT with the 'data' column omitted; the database
# itself will persist this as the NULL value

Das Weglassen einer Spalte aus der INSERT-Anweisung bedeutet, dass der Spalte der NULL-Wert zugewiesen wird, *es sei denn*, die Spalte hat einen eingerichteten Standardwert, in welchem Fall der Standardwert beibehalten wird. Dies gilt sowohl aus rein SQL-Perspektive mit serverseitigen Standardwerten als auch für das Verhalten von SQLAlchemys INSERT-Verhalten mit sowohl client- als auch serverseitigen Standardwerten.

class MyObject(Base):
    __tablename__ = "my_table"
    id = mapped_column(Integer, primary_key=True)
    data = mapped_column(String(50), nullable=True, server_default="default")


obj = MyObject(id=1)
session.add(obj)
session.commit()  # INSERT with the 'data' column omitted; the database
# itself will persist this as the value 'default'

Jedoch wird in der ORM selbst die explizite Zuweisung des Python-Werts None zum Objekt *gleich behandelt*, als ob der Wert nie zugewiesen worden wäre.

class MyObject(Base):
    __tablename__ = "my_table"
    id = mapped_column(Integer, primary_key=True)
    data = mapped_column(String(50), nullable=True, server_default="default")


obj = MyObject(id=1, data=None)
session.add(obj)
session.commit()  # INSERT with the 'data' column explicitly set to None;
# the ORM still omits it from the statement and the
# database will still persist this as the value 'default'

Die obige Operation wird den serverseitigen Standardwert "default" in die Spalte data übernehmen und nicht SQL NULL, obwohl None übergeben wurde; dies ist ein langjähriges Verhalten der ORM, das viele Anwendungen als Annahme betrachten.

Was tun wir also, wenn wir tatsächlich NULL in diese Spalte einfügen wollen, obwohl die Spalte einen Standardwert hat? Es gibt zwei Ansätze. Eine Möglichkeit ist, dass auf Instanzebene das Attribut mit dem SQL-Konstrukt null zugewiesen wird.

from sqlalchemy import null

obj = MyObject(id=1, data=null())
session.add(obj)
session.commit()  # INSERT with the 'data' column explicitly set as null();
# the ORM uses this directly, bypassing all client-
# and server-side defaults, and the database will
# persist this as the NULL value

Das SQL-Konstrukt null wird immer in den SQL NULL-Wert übersetzt, der direkt in der Ziel-INSERT-Anweisung vorhanden ist.

Wenn wir den Python-Wert None verwenden und dies trotz vorhandener Spaltenstandards ebenfalls als NULL gespeichert werden soll, können wir dies für die ORM mit einem Core-Level-Modifikator TypeEngine.evaluates_none() konfigurieren. Dies signalisiert, dass eine Spalte, bei der die ORM den Wert None genauso behandeln soll wie jeden anderen Wert und ihn weiterleiten soll, anstatt ihn als "fehlenden" Wert wegzulassen.

class MyObject(Base):
    __tablename__ = "my_table"
    id = mapped_column(Integer, primary_key=True)
    data = mapped_column(
        String(50).evaluates_none(),  # indicate that None should always be passed
        nullable=True,
        server_default="default",
    )


obj = MyObject(id=1, data=None)
session.add(obj)
session.commit()  # INSERT with the 'data' column explicitly set to None;
# the ORM uses this directly, bypassing all client-
# and server-side defaults, and the database will
# persist this as the NULL value

Abrufen serverseitig generierter Standardwerte

Wie in den Abschnitten Von Servern aufgerufene DDL-explizite Standardausdrücke und Implizit generierte Werte, Zeitstempel und Trigger-Spalten kennzeichnen eingeführt, unterstützt Core die Vorstellung von Datenbankspalten, für die die Datenbank selbst einen Wert bei INSERT und in selteneren Fällen bei UPDATE-Anweisungen generiert. Die ORM bietet Unterstützung für solche Spalten, um diese neu generierten Werte nach einem Flush abrufen zu können. Dieses Verhalten ist für Primärschlüsselspalten, die vom Server generiert werden, erforderlich, da die ORM den Primärschlüssel eines Objekts kennen muss, sobald es persistiert ist.

In den allermeisten Fällen handelt es sich bei Primärschlüsselspalten, deren Werte automatisch von der Datenbank generiert werden, um einfache Integer-Spalten, die von der Datenbank entweder als sogenannte "autoincrement"-Spalte oder aus einer mit der Spalte verbundenen Sequenz implementiert werden. Jedes Datenbankdialekt innerhalb von SQLAlchemy Core unterstützt eine Methode zum Abrufen dieser Primärschlüsselwerte, die oft nativ für Python DBAPI ist, und im Allgemeinen ist dieser Prozess automatisch. Weitere Informationen hierzu finden Sie unter Column.autoincrement.

Für serverseitig generierende Spalten, die keine Primärschlüsselspalten sind oder keine einfachen autoincrementierenden Integer-Spalten sind, erfordert die ORM, dass diese Spalten mit einer entsprechenden server_default-Direktive gekennzeichnet werden, die es der ORM ermöglicht, diesen Wert abzurufen. Nicht alle Methoden werden auf allen Backends unterstützt, daher muss darauf geachtet werden, die geeignete Methode zu verwenden. Die beiden zu beantwortenden Fragen sind: 1. Ist diese Spalte Teil des Primärschlüssels oder nicht, und 2. Unterstützt die Datenbank RETURNING oder ein Äquivalent wie "OUTPUT inserted"? Dies sind SQL-Phrasen, die einen serverseitig generierten Wert zum Zeitpunkt der Ausführung der INSERT- oder UPDATE-Anweisung zurückgeben. RETURNING wird derzeit von PostgreSQL, Oracle Database, MariaDB 10.5, SQLite 3.35 und SQL Server unterstützt.

Fall 1: Kein Primärschlüssel, RETURNING oder Äquivalent wird unterstützt

In diesem Fall sollten Spalten als FetchedValue oder mit einer expliziten Column.server_default gekennzeichnet werden. Die ORM fügt diese Spalten automatisch der RETURNING-Klausel hinzu, wenn INSERT-Anweisungen ausgeführt werden, vorausgesetzt, der Parameter Mapper.eager_defaults ist auf True gesetzt, oder wenn er auf seiner Standardeinstellung von "auto" belassen wird, für Dialekte, die sowohl RETURNING als auch insertmanyvalues unterstützen.

class MyModel(Base):
    __tablename__ = "my_table"

    id = mapped_column(Integer, primary_key=True)

    # server-side SQL date function generates a new timestamp
    timestamp = mapped_column(DateTime(), server_default=func.now())

    # some other server-side function not named here, such as a trigger,
    # populates a value into this column during INSERT
    special_identifier = mapped_column(String(50), server_default=FetchedValue())

    # set eager defaults to True.  This is usually optional, as if the
    # backend supports RETURNING + insertmanyvalues, eager defaults
    # will take place regardless on INSERT
    __mapper_args__ = {"eager_defaults": True}

Oben wird eine INSERT-Anweisung, die keine expliziten Werte für "timestamp" oder "special_identifier" von der Client-Seite angibt, die Spalten "timestamp" und "special_identifier" innerhalb der RETURNING-Klausel enthalten, damit sie sofort verfügbar sind. Auf der PostgreSQL-Datenbank sieht eine INSERT-Anweisung für die obige Tabelle wie folgt aus:

INSERT INTO my_table DEFAULT VALUES RETURNING my_table.id, my_table.timestamp, my_table.special_identifier

Geändert in Version 2.0.0rc1: Der Parameter Mapper.eager_defaults hat jetzt standardmäßig eine neue Einstellung "auto", die RETURNING automatisch verwendet, um serverseitig generierte Standardwerte bei INSERT abzurufen, wenn die zugrundeliegende Datenbank sowohl RETURNING als auch insertmanyvalues unterstützt.

Hinweis

Der Wert "auto" für Mapper.eager_defaults gilt nur für INSERT-Anweisungen. UPDATE-Anweisungen verwenden RETURNING nicht, auch wenn es verfügbar ist, es sei denn, Mapper.eager_defaults ist auf True gesetzt. Dies liegt daran, dass es kein äquivalentes "insertmanyvalues"-Feature für UPDATE gibt, sodass UPDATE RETURNING erfordert, dass UPDATE-Anweisungen einzeln für jede zu aktualisierende Zeile ausgegeben werden.

Fall 2: Tabelle enthält Trigger-generierte Werte, die nicht mit RETURNING kompatibel sind

Die Einstellung "auto" von Mapper.eager_defaults bedeutet, dass ein Backend, das RETURNING unterstützt, in der Regel RETURNING mit INSERT-Anweisungen verwendet, um neu generierte Standardwerte abzurufen. Es gibt jedoch Einschränkungen bei serverseitig generierten Werten, die mithilfe von Triggern generiert werden, so dass RETURNING nicht verwendet werden kann.

  • SQL Server erlaubt nicht die Verwendung von RETURNING in einer INSERT-Anweisung, um einen trigger-generierten Wert abzurufen; die Anweisung schlägt fehl.

  • SQLite hat Einschränkungen bei der Kombination von RETURNING mit Triggern, so dass die RETURNING-Klausel den INSERTed-Wert nicht verfügbar hat.

  • Andere Backends können Einschränkungen bei RETURNING in Verbindung mit Triggern oder anderen Arten von serverseitig generierten Werten aufweisen.

Um die Verwendung von RETURNING für solche Werte zu deaktivieren, einschließlich nicht nur für serverseitig generierte Standardwerte, sondern auch um sicherzustellen, dass die ORM niemals RETURNING mit einer bestimmten Tabelle verwendet, geben Sie Table.implicit_returning als False für die gemappte Table an. Bei einer deklarativen Abbildung sieht dies so aus:

class MyModel(Base):
    __tablename__ = "my_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    data: Mapped[str] = mapped_column(String(50))

    # assume a database trigger populates a value into this column
    # during INSERT
    special_identifier = mapped_column(String(50), server_default=FetchedValue())

    # disable all use of RETURNING for the table
    __table_args__ = {"implicit_returning": False}

Auf SQL Server mit dem pyodbc-Treiber verwendet eine INSERT-Anweisung für die obige Tabelle nicht RETURNING und nutzt die SQL Server-Funktion scope_identity(), um den neu generierten Primärschlüsselwert abzurufen.

INSERT INTO my_table (data) VALUES (?); select scope_identity()

Siehe auch

INSERT-Verhalten - Hintergrund zu den Methoden des SQL Server-Dialekts zum Abrufen neu generierter Primärschlüsselwerte

Fall 3: Kein Primärschlüssel, RETURNING oder Äquivalent wird nicht unterstützt oder ist nicht erforderlich

Dieser Fall ist derselbe wie Fall 1 oben, außer dass wir typischerweise Mapper.eager_defaults nicht verwenden wollen, da seine aktuelle Implementierung in Abwesenheit von RETURNING-Unterstützung einen SELECT pro Zeile ausgibt, was nicht performant ist. Daher wird der Parameter in der folgenden Abbildung weggelassen.

class MyModel(Base):
    __tablename__ = "my_table"

    id = mapped_column(Integer, primary_key=True)
    timestamp = mapped_column(DateTime(), server_default=func.now())

    # assume a database trigger populates a value into this column
    # during INSERT
    special_identifier = mapped_column(String(50), server_default=FetchedValue())

Nachdem ein Datensatz mit der obigen Abbildung auf einem Backend, das RETURNING oder "insertmanyvalues" nicht unterstützt, eingefügt wurde, bleiben die Spalten "timestamp" und "special_identifier" leer und werden über eine zweite SELECT-Anweisung abgerufen, wenn sie zum ersten Mal nach dem Flush zugegriffen werden, d.h. sie werden als "abgelaufen" markiert.

Wenn Mapper.eager_defaults explizit mit dem Wert True angegeben wird und die Backend-Datenbank RETURNING oder ein Äquivalent nicht unterstützt, gibt die ORM unmittelbar nach der INSERT-Anweisung eine SELECT-Anweisung aus, um neu generierte Werte abzurufen; die ORM verfügt derzeit nicht über die Fähigkeit, viele neu eingefügte Zeilen in einem Batch auszuwählen, wenn RETURNING nicht verfügbar war. Dies ist in der Regel unerwünscht, da es zusätzliche SELECT-Anweisungen zum Flush-Prozess hinzufügt, die möglicherweise nicht benötigt werden. Die Verwendung der obigen Abbildung mit der Option Mapper.eager_defaults auf True gesetzt gegen MySQL (nicht MariaDB) führt beim Flush zu SQL wie diesem:

INSERT INTO my_table () VALUES ()

-- when eager_defaults **is** used, but RETURNING is not supported
SELECT my_table.timestamp AS my_table_timestamp, my_table.special_identifier AS my_table_special_identifier
FROM my_table WHERE my_table.id = %s

Eine zukünftige Version von SQLAlchemy könnte versuchen, die Effizienz von eager defaults in Abwesenheit von RETURNING zu verbessern, um viele Zeilen in einer einzigen SELECT-Anweisung zu batchen.

Fall 4: Primärschlüssel, RETURNING oder Äquivalent wird unterstützt

Ein Primärschlüssel, der einen serverseitig generierten Wert hat, muss sofort nach dem INSERT abgerufen werden; die ORM kann nur auf Zeilen zugreifen, für die sie einen Primärschlüsselwert hat, also wenn der Primärschlüssel vom Server generiert wird, benötigt die ORM eine Möglichkeit, diesen neuen Wert sofort nach dem INSERT abzurufen.

Wie oben erwähnt, werden für Integer "autoincrement"-Spalten sowie Spalten, die mit Identity und speziellen Konstrukten wie PostgreSQL SERIAL gekennzeichnet sind, diese Typen automatisch vom Core behandelt; Datenbanken enthalten Funktionen zum Abrufen der "zuletzt eingefügten ID", wenn RETURNING nicht unterstützt wird, und wo RETURNING unterstützt wird, wird SQLAlchemy dies nutzen.

Zum Beispiel wird bei der Verwendung von Oracle Database mit einer Spalte, die als Identity gekennzeichnet ist, RETURNING automatisch verwendet, um den neuen Primärschlüsselwert abzurufen.

class MyOracleModel(Base):
    __tablename__ = "my_table"

    id: Mapped[int] = mapped_column(Identity(), primary_key=True)
    data: Mapped[str] = mapped_column(String(50))

Der INSERT für ein Modell wie oben auf Oracle Database sieht wie folgt aus:

INSERT INTO my_table (data) VALUES (:data) RETURNING my_table.id INTO :ret_0

SQLAlchemy rendert einen INSERT für das Feld "data", schließt jedoch nur "id" in die RETURNING-Klausel ein, damit die serverseitige Generierung für "id" stattfindet und der neue Wert sofort zurückgegeben wird.

Für Nicht-Integer-Werte, die von serverseitigen Funktionen oder Triggern generiert werden, sowie für Integer-Werte, die von Konstrukten außerhalb der Tabelle selbst stammen, einschließlich expliziter Sequenzen und Trigger, muss die serverseitige Standardwertgenerierung in den Tabellenmetadaten gekennzeichnet werden. Wiederum Oracle Database als Beispiel, können wir eine ähnliche Tabelle wie oben illustrieren, die eine explizite Sequenz mit dem Konstrukt Sequence benennt.

class MyOracleModel(Base):
    __tablename__ = "my_table"

    id: Mapped[int] = mapped_column(Sequence("my_oracle_seq"), primary_key=True)
    data: Mapped[str] = mapped_column(String(50))

Ein INSERT für diese Version des Modells auf Oracle Database würde wie folgt aussehen:

INSERT INTO my_table (id, data) VALUES (my_oracle_seq.nextval, :data) RETURNING my_table.id INTO :ret_0

Wo oben SQLAlchemy my_sequence.nextval für die Primärschlüsselspalte rendert, damit sie für die Generierung neuer Primärschlüssel verwendet wird, und auch RETURNING verwendet, um den neuen Wert sofort zurückzubekommen.

Wenn die Datenquelle keine einfache SQL-Funktion oder Sequence repräsentiert, z. B. bei Verwendung von Triggern oder datenbankspezifischen Datentypen, die neue Werte erzeugen, kann das Vorhandensein eines werterzeugenden Standards durch die Verwendung von FetchedValue innerhalb der Spaltendefinition angezeigt werden. Nachfolgend ein Modell, das eine SQL Server TIMESTAMP-Spalte als Primärschlüssel verwendet; auf SQL Server generiert dieser Datentyp automatisch neue Werte, so dass dies in den Tabellenmetadaten durch Angabe von FetchedValue für den Parameter Column.server_default angezeigt wird.

class MySQLServerModel(Base):
    __tablename__ = "my_table"

    timestamp: Mapped[datetime.datetime] = mapped_column(
        TIMESTAMP(), server_default=FetchedValue(), primary_key=True
    )
    data: Mapped[str] = mapped_column(String(50))

Ein INSERT für die obige Tabelle auf SQL Server sieht wie folgt aus:

INSERT INTO my_table (data) OUTPUT inserted.timestamp VALUES (?)

Fall 5: Primärschlüssel, RETURNING oder Äquivalent wird nicht unterstützt

In diesem Bereich generieren wir Zeilen für eine Datenbank wie MySQL, bei der einige Mittel zur Generierung eines Standardwerts auf dem Server stattfinden, aber außerhalb der üblichen Autoincrement-Routine der Datenbank liegen. In diesem Fall müssen wir sicherstellen, dass SQLAlchemy den Standardwert "vorab ausführen" kann, was bedeutet, dass es ein expliziter SQL-Ausdruck sein muss.

Hinweis

Dieser Abschnitt wird mehrere Rezepte mit Datetime-Werten für MySQL veranschaulichen, da die Datetime-Datentypen auf diesem Backend zusätzliche eigenwillige Anforderungen haben, die nützlich sind, um sie zu veranschaulichen. Beachten Sie jedoch, dass MySQL für *jeden* automatisch generierten Datentyp, der als Primärschlüssel verwendet wird, außer dem üblichen einzelnen autoincrementierenden Integer-Wert, einen expliziten, im Voraus ausführbaren Standardwertgenerator benötigt.

MySQL mit DateTime-Primärschlüssel

Am Beispiel einer DateTime-Spalte für MySQL fügen wir einen expliziten, im Voraus ausführbaren Standardwert hinzu, der die SQL-Funktion "NOW()" verwendet.

class MyModel(Base):
    __tablename__ = "my_table"

    timestamp = mapped_column(DateTime(), default=func.now(), primary_key=True)

Wo wir oben die Funktion "NOW()" auswählen, um einen Datetime-Wert für die Spalte zu liefern. Das obige SQL sieht wie folgt aus:

SELECT now() AS anon_1
INSERT INTO my_table (timestamp) VALUES (%s)
('2018-08-09 13:08:46',)

MySQL mit TIMESTAMP-Primärschlüssel

Bei der Verwendung des TIMESTAMP-Datentyps mit MySQL ordnet MySQL diesem Datentyp normalerweise automatisch einen serverseitigen Standardwert zu. Wenn wir ihn jedoch als Primärschlüssel verwenden, kann der Core den neu generierten Wert nicht abrufen, es sei denn, wir führen die Funktion selbst aus. Da TIMESTAMP in MySQL tatsächlich binäre Daten speichert, müssen wir unserer Verwendung von "NOW()" eine zusätzliche "CAST" hinzufügen, damit wir einen binären Wert abrufen können, der in die Spalte geschrieben werden kann.

from sqlalchemy import cast, Binary


class MyModel(Base):
    __tablename__ = "my_table"

    timestamp = mapped_column(
        TIMESTAMP(), default=cast(func.now(), Binary), primary_key=True
    )

Oben nutzen wir zusätzlich zur Auswahl der "NOW()"-Funktion den Datentyp Binary in Verbindung mit cast(), damit der zurückgegebene Wert binär ist. Das daraus resultierende SQL in einem INSERT sieht wie folgt aus:

SELECT CAST(now() AS BINARY) AS anon_1
INSERT INTO my_table (timestamp) VALUES (%s)
(b'2018-08-09 13:08:46',)

Siehe auch

Column INSERT/UPDATE Defaults

Hinweise zum eager-Fetching von clientseitig aufgerufenen SQL-Ausdrücken für INSERT oder UPDATE

Die vorherigen Beispiele zeigen die Verwendung von Column.server_default zur Erstellung von Tabellen, die Standardwertgenerierungsfunktionen in ihrer DDL enthalten.

SQLAlchemy unterstützt auch nicht-DDL-serverseitige Standardwerte, wie unter Client-seitig aufgerufene SQL-Ausdrücke dokumentiert; diese "clientseitig aufgerufenen SQL-Ausdrücke" werden mit den Parametern Column.default und Column.onupdate eingerichtet.

Diese SQL-Ausdrücke unterliegen derzeit den gleichen Einschränkungen innerhalb der ORM wie echte serverseitige Standardwerte; sie werden nicht mit RETURNING eager-gefetched, wenn Mapper.eager_defaults auf "auto" oder True gesetzt ist, es sei denn, die FetchedValue-Direktive ist mit der Column verknüpft, obwohl diese Ausdrücke keine DDL-Server-Defaults sind und aktiv von SQLAlchemy selbst gerendert werden. Diese Einschränkung könnte in zukünftigen SQLAlchemy-Versionen behoben werden.

Der FetchedValue-Konstruktor kann auf Column.server_default oder Column.server_onupdate angewendet werden, gleichzeitig mit einem SQL-Ausdruck, der mit Column.default und Column.onupdate verwendet wird, wie im folgenden Beispiel, wo der func.now()-Konstruktor als clientseitig aufgerufener SQL-Ausdruck für Column.default und Column.onupdate verwendet wird. Damit das Verhalten von Mapper.eager_defaults dies einschließt, dass diese Werte per RETURNING abgerufen werden, wo verfügbar, werden Column.server_default und Column.server_onupdate mit FetchedValue verwendet, um sicherzustellen, dass der Abruf stattfindet.

class MyModel(Base):
    __tablename__ = "my_table"

    id = mapped_column(Integer, primary_key=True)

    created = mapped_column(
        DateTime(), default=func.now(), server_default=FetchedValue()
    )
    updated = mapped_column(
        DateTime(),
        onupdate=func.now(),
        server_default=FetchedValue(),
        server_onupdate=FetchedValue(),
    )

    __mapper_args__ = {"eager_defaults": True}

Mit einem Mapping, das dem obigen ähnlich ist, enthält die vom ORM für INSERT und UPDATE gerenderte SQL-Anweisung created und updated in der RETURNING-Klausel.

INSERT INTO my_table (created) VALUES (now()) RETURNING my_table.id, my_table.created, my_table.updated

UPDATE my_table SET updated=now() WHERE my_table.id = %(my_table_id)s RETURNING my_table.updated

Verwendung von INSERT, UPDATE und ON CONFLICT (d. h. Upsert) zur Rückgabe von ORM-Objekten

SQLAlchemy 2.0 bietet erweiterte Funktionen zur Erstellung verschiedener Arten von ORM-fähigen INSERT-, UPDATE- und Upsert-Anweisungen. Die Dokumentation finden Sie im Dokument ORM-fähige INSERT-, UPDATE- und DELETE-Anweisungen. Für Upsert siehe ORM „Upsert“-Anweisungen.

Verwendung von PostgreSQL ON CONFLICT mit RETURNING zur Rückgabe von upserted ORM-Objekten

Dieser Abschnitt wurde nach ORM „Upsert“-Anweisungen verschoben.

Partitionierungsstrategien (z. B. mehrere Datenbank-Backends pro Session)

Einfache vertikale Partitionierung

Die vertikale Partitionierung platziert verschiedene Klassen, Klassenhierarchien oder gemappte Tabellen über mehrere Datenbanken, indem die Session mit dem Argument Session.binds konfiguriert wird. Dieses Argument empfängt ein Wörterbuch, das jede Kombination von ORM-gemappten Klassen, beliebigen Klassen innerhalb einer gemappten Hierarchie (wie deklarative Basisklassen oder Mixins), Table-Objekten und Mapper-Objekten als Schlüssel enthält, die dann typischerweise auf Engine- oder weniger typisch Connection-Objekte als Ziele verweisen. Das Wörterbuch wird konsultiert, wann immer die Session SQL im Namen einer bestimmten Art von gemappter Klasse ausgeben muss, um die entsprechende Quelle für die Datenbankkonnektivität zu lokalisieren.

engine1 = create_engine("postgresql+psycopg2://db1")
engine2 = create_engine("postgresql+psycopg2://db2")

Session = sessionmaker()

# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User: engine1, Account: engine2})

session = Session()

Oben führen SQL-Operationen auf beiden Klassen zur Nutzung der Engine, die mit dieser Klasse verknüpft ist. Die Funktionalität ist umfassend für Lese- und Schreibvorgänge; eine Query, die sich auf Entitäten bezieht, die auf engine1 gemappt sind (bestimmt durch Betrachtung der ersten Entität in der Liste der angeforderten Elemente), verwendet engine1 zur Ausführung der Abfrage. Eine Flush-Operation verwendet **beide** Engines pro Klasse, wenn sie Objekte vom Typ User und Account flusht.

Im häufigeren Fall gibt es typischerweise Basis- oder Mixin-Klassen, die verwendet werden können, um zwischen Operationen zu unterscheiden, die für verschiedene Datenbankverbindungen bestimmt sind. Das Argument Session.binds kann jede beliebige Python-Klasse als Schlüssel aufnehmen, die verwendet wird, wenn sie im __mro__ (Python Method Resolution Order) einer bestimmten gemappten Klasse gefunden wird. Angenommen, zwei deklarative Basen repräsentieren zwei verschiedene Datenbankverbindungen.

from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Session


class BaseA(DeclarativeBase):
    pass


class BaseB(DeclarativeBase):
    pass


class User(BaseA): ...


class Address(BaseA): ...


class GameInfo(BaseB): ...


class GameStats(BaseB): ...


Session = sessionmaker()

# all User/Address operations will be on engine 1, all
# Game operations will be on engine 2
Session.configure(binds={BaseA: engine1, BaseB: engine2})

Oben werden Klassen, die von BaseA und BaseB abstammen, ihre SQL-Operationen an eine von zwei Engines weiterleiten, basierend darauf, von welcher Oberklasse sie abstammen, falls vorhanden. Im Falle einer Klasse, die von mehr als einer „gebundenen“ Oberklasse abstammt, wird die Oberklasse, die in der Hierarchie der Zielklasse am höchsten steht, ausgewählt, um zu repräsentieren, welche Engine verwendet werden soll.

Siehe auch

Session.binds

Koordination von Transaktionen für eine Mehr-Engine-Session

Ein Vorbehalt bei der Verwendung mehrerer gebundener Engines ist der Fall, dass eine Commit-Operation auf einem Backend fehlschlagen kann, nachdem der Commit auf einem anderen erfolgreich war. Dies ist ein Inkonsistenzproblem, das in relationalen Datenbanken mit einer „Zwei-Phasen-Transaktion“ gelöst wird, die einen zusätzlichen „Vorbereitungs“-Schritt zur Commit-Sequenz hinzufügt, der es mehreren Datenbanken ermöglicht, sich vor der eigentlichen Transaktionsabwicklung auf den Commit zu einigen.

Aufgrund der eingeschränkten Unterstützung in DBAPIs hat SQLAlchemy nur begrenzte Unterstützung für Zwei-Phasen-Transaktionen über Backends hinweg. Am häufigsten funktioniert es gut mit dem PostgreSQL-Backend und in geringerem Maße mit dem MySQL-Backend. Die Session ist jedoch voll in der Lage, die Zwei-Phasen-Transaktionsfunktion zu nutzen, wenn das Backend sie unterstützt, indem das Flag Session.use_twophase innerhalb von sessionmaker oder Session gesetzt wird. Ein Beispiel finden Sie unter Aktivieren von Two-Phase Commit.

Benutzerdefinierte vertikale Partitionierung

Umfassendere regelbasierte klassenbezogene Partitionierung kann durch Überschreiben der Methode Session.get_bind() erstellt werden. Nachfolgend illustrieren wir eine benutzerdefinierte Session, die die folgenden Regeln liefert.

  1. Flush-Operationen sowie Massen-„Update“- und „Delete“-Operationen werden an die Engine namens leader geliefert.

  2. Operationen auf Objekten, die von MyOtherClass abstammen, erfolgen alle auf der Engine other.

  3. Leseoperationen für alle anderen Klassen erfolgen auf einer zufälligen Auswahl der Datenbanken follower1 oder follower2.

engines = {
    "leader": create_engine("sqlite:///leader.db"),
    "other": create_engine("sqlite:///other.db"),
    "follower1": create_engine("sqlite:///follower1.db"),
    "follower2": create_engine("sqlite:///follower2.db"),
}

from sqlalchemy.sql import Update, Delete
from sqlalchemy.orm import Session, sessionmaker
import random


class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None):
        if mapper and issubclass(mapper.class_, MyOtherClass):
            return engines["other"]
        elif self._flushing or isinstance(clause, (Update, Delete)):
            # NOTE: this is for example, however in practice reader/writer
            # splits are likely more straightforward by using two distinct
            # Sessions at the top of a "reader" or "writer" operation.
            # See note below
            return engines["leader"]
        else:
            return engines[random.choice(["follower1", "follower2"])]

Die obige Session-Klasse wird über das Argument class_ an sessionmaker angeschlossen.

Session = sessionmaker(class_=RoutingSession)

Dieser Ansatz kann mit mehreren MetaData-Objekten kombiniert werden, unter Verwendung eines Ansatzes wie dem des deklarativen Schlüsselworts __abstract__, wie unter __abstract__ beschrieben.

Hinweis

Während das obige Beispiel die Weiterleitung spezifischer SQL-Anweisungen an eine sogenannte „Leader“- oder „Follower“-Datenbank basierend darauf illustriert, ob die Anweisung Daten schreiben erwartet oder nicht, ist dies wahrscheinlich kein praktikabler Ansatz, da er zu unkoordinierter Transaktionsverhaltensweise zwischen Lesen und Schreiben innerhalb derselben Operation führt. In der Praxis ist es wahrscheinlich am besten, die Session von vornherein als „Reader“- oder „Writer“-Session zu konstruieren, basierend auf der gesamten Operation/Transaktion, die gerade abläuft. Auf diese Weise gibt eine Operation, die Daten schreiben wird, ihre Leseabfragen auch innerhalb desselben Transaktionsbereichs aus. Siehe das Beispiel unter Festlegen der Isolation für eine Sessionmaker / Engine-weite für ein Rezept, das eine sessionmaker für „nur Lese“-Operationen mit Autocommit-Verbindungen und eine weitere für „Schreib“-Operationen, die DML / COMMIT enthalten, einrichtet.

Siehe auch

Django-style Database Routers in SQLAlchemy - Blogbeitrag zu einem umfassenderen Beispiel für Session.get_bind()

Horizontale Partitionierung

Horizontale Partitionierung partitioniert die Zeilen einer einzelnen Tabelle (oder einer Reihe von Tabellen) über mehrere Datenbanken. Die SQLAlchemy Session unterstützt dieses Konzept, aber um es vollständig zu nutzen, müssen Session- und Query-Unterklassen verwendet werden. Eine Basisversion dieser Unterklassen ist in der ORM-Erweiterung Horizontale Sharding verfügbar. Ein Anwendungsbeispiel finden Sie unter: Horizontale Sharding.

Massenoperationen

Legacy-Funktion

SQLAlchemy 2.0 hat die „Bulk Insert“- und „Bulk Update“-Funktionen der Session in die Session.execute()-Methode im 2.0-Stil integriert und nutzt direkte Insert- und Update-Konstrukte. Die Dokumentation finden Sie im Dokument ORM-fähige INSERT-, UPDATE- und DELETE-Anweisungen, einschließlich Legacy Session Bulk INSERT Methoden, die die Migration von älteren zu neuen Methoden illustriert.