Additional Persistence Techniques

Embedding SQL Insert/Update Expressions into a Flush

This feature allows the value of a database column to be set to a SQL expression instead of a literal value. It’s especially useful for atomic updates, calling stored procedures, etc. All you do is assign an expression to an attribute:

  1. class SomeClass(Base):
  2.     __tablename__ = "some_table"
  4.     # ...
  6.     value = mapped_column(Integer)
  9. someobject = session.get(SomeClass, 5)
  11. # set 'value' attribute to a SQL expression adding one
  12. someobject.value = SomeClass.value + 1
  14. # issues "UPDATE some_table SET value=value+1"
  15. session.commit()

This technique works both for INSERT and UPDATE statements. After the flush/commit operation, the value attribute on someobject above is expired, so that when next accessed the newly generated value will be loaded from the database.

The feature also has conditional support to work in conjunction with primary key columns. For backends that have RETURNING support (including Oracle, SQL Server, MariaDB 10.5, SQLite 3.35) a SQL expression may be assigned to a primary key column as well. This allows both the SQL expression to be evaluated, as well as allows any server side triggers that modify the primary key value on INSERT, to be successfully retrieved by the ORM as part of the object’s primary key:

  1. class Foo(Base):
  2.     __tablename__ = "foo"
  3.     pk = mapped_column(Integer, primary_key=True)
  4.     bar = mapped_column(Integer)
  7. e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test", echo=True)
  8. Base.metadata.create_all(e)
  10. session = Session(e)
  12. foo = Foo(pk=sql.select(sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)))
  13. session.add(foo)
  14. session.commit()

On PostgreSQL, the above Session will emit the following INSERT:

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

New in version 1.3: SQL expressions can now be passed to a primary key column during an ORM flush; if the database supports RETURNING, or if pysqlite is in use, the ORM will be able to retrieve the server-generated value as the value of the primary key attribute.

Using SQL Expressions with Sessions

SQL expressions and strings can be executed via the Session within its transactional context. This is most easily accomplished using the Session.execute() method, which returns a CursorResult in the same manner as an Engine or Connection:

  1. Session = sessionmaker(bind=engine)
  2. session = Session()
  4. # execute a string statement
  5. result = session.execute("select * from table where id=:id", {"id": 7})
  7. # execute a SQL expression construct
  8. result = session.execute(select(mytable).where(mytable.c.id == 7))

The current Connection held by the Session is accessible using the Session.connection() method:

  1. connection = session.connection()

The examples above deal with a Session that’s bound to a single Engine or Connection. To execute statements using a Session which is bound either to multiple engines, or none at all (i.e. relies upon bound metadata), both Session.execute() and Session.connection() accept a dictionary of bind arguments Session.execute.bind_arguments which may include “mapper” which is passed a mapped class or Mapper instance, which is used to locate the proper context for the desired engine:

  1. Session = sessionmaker()
  2. session = Session()
  4. # need to specify mapper or class when executing
  5. result = session.execute(
  6.     text("select * from table where id=:id"),
  7.     {"id": 7},
  8.     bind_arguments={"mapper": MyMappedClass},
  9. )
  11. result = session.execute(
  12.     select(mytable).where(mytable.c.id == 7), bind_arguments={"mapper": MyMappedClass}
  13. )
  15. connection = session.connection(MyMappedClass)

Changed in version 1.4: the mapper and clause arguments to Session.execute() are now passed as part of a dictionary sent as the Session.execute.bind_arguments parameter. The previous arguments are still accepted however this usage is deprecated.

Forcing NULL on a column with a default

The ORM considers any attribute that was never set on an object as a “default” case; the attribute will be omitted from the INSERT statement:

  1. class MyObject(Base):
  2.     __tablename__ = "my_table"
  3.     id = mapped_column(Integer, primary_key=True)
  4.     data = mapped_column(String(50), nullable=True)
  7. obj = MyObject(id=1)
  8. session.add(obj)
  9. session.commit()  # INSERT with the 'data' column omitted; the database
  10. # itself will persist this as the NULL value

Omitting a column from the INSERT means that the column will have the NULL value set, unless the column has a default set up, in which case the default value will be persisted. This holds true both from a pure SQL perspective with server-side defaults, as well as the behavior of SQLAlchemy’s insert behavior with both client-side and server-side defaults:

  1. class MyObject(Base):
  2.     __tablename__ = "my_table"
  3.     id = mapped_column(Integer, primary_key=True)
  4.     data = mapped_column(String(50), nullable=True, server_default="default")
  7. obj = MyObject(id=1)
  8. session.add(obj)
  9. session.commit()  # INSERT with the 'data' column omitted; the database
  10. # itself will persist this as the value 'default'

However, in the ORM, even if one assigns the Python value None explicitly to the object, this is treated the same as though the value were never assigned:

  1. class MyObject(Base):
  2.     __tablename__ = "my_table"
  3.     id = mapped_column(Integer, primary_key=True)
  4.     data = mapped_column(String(50), nullable=True, server_default="default")
  7. obj = MyObject(id=1, data=None)
  8. session.add(obj)
  9. session.commit()  # INSERT with the 'data' column explicitly set to None;
  10. # the ORM still omits it from the statement and the
  11. # database will still persist this as the value 'default'

The above operation will persist into the data column the server default value of "default" and not SQL NULL, even though None was passed; this is a long-standing behavior of the ORM that many applications hold as an assumption.

So what if we want to actually put NULL into this column, even though the column has a default value? There are two approaches. One is that on a per-instance level, we assign the attribute using the null SQL construct:

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

The null SQL construct always translates into the SQL NULL value being directly present in the target INSERT statement.

If we’d like to be able to use the Python value None and have this also be persisted as NULL despite the presence of column defaults, we can configure this for the ORM using a Core-level modifier TypeEngine.evaluates_none(), which indicates a type where the ORM should treat the value None the same as any other value and pass it through, rather than omitting it as a “missing” value:

  1. class MyObject(Base):
  2.     __tablename__ = "my_table"
  3.     id = mapped_column(Integer, primary_key=True)
  4.     data = mapped_column(
  5.         String(50).evaluates_none(),  # indicate that None should always be passed
  6.         nullable=True,
  7.         server_default="default",
  8.     )
  11. obj = MyObject(id=1, data=None)
  12. session.add(obj)
  13. session.commit()  # INSERT with the 'data' column explicitly set to None;
  14. # the ORM uses this directly, bypassing all client-
  15. # and server-side defaults, and the database will
  16. # persist this as the NULL value

Evaluating None

The TypeEngine.evaluates_none() modifier is primarily intended to signal a type where the Python value “None” is significant, the primary example being a JSON type which may want to persist the JSON null value rather than SQL NULL. We are slightly repurposing it here in order to signal to the ORM that we’d like None to be passed into the type whenever present, even though no special type-level behaviors are assigned to it.

New in version 1.1: added the TypeEngine.evaluates_none() method in order to indicate that a “None” value should be treated as significant.

Fetching Server-Generated Defaults

As introduced in the sections Server-invoked DDL-Explicit Default Expressions and Marking Implicitly Generated Values, timestamps, and Triggered Columns, the Core supports the notion of database columns for which the database itself generates a value upon INSERT and in less common cases upon UPDATE statements. The ORM features support for such columns regarding being able to fetch these newly generated values upon flush. This behavior is required in the case of primary key columns that are generated by the server, since the ORM has to know the primary key of an object once it is persisted.

In the vast majority of cases, primary key columns that have their value generated automatically by the database are simple integer columns, which are implemented by the database as either a so-called “autoincrement” column, or from a sequence associated with the column. Every database dialect within SQLAlchemy Core supports a method of retrieving these primary key values which is often native to the Python DBAPI, and in general this process is automatic. There is more documentation regarding this at Column.autoincrement.

For server-generating columns that are not primary key columns or that are not simple autoincrementing integer columns, the ORM requires that these columns are marked with an appropriate server_default directive that allows the ORM to retrieve this value. Not all methods are supported on all backends, however, so care must be taken to use the appropriate method. The two questions to be answered are, 1. is this column part of the primary key or not, and 2. does the database support RETURNING or an equivalent, such as “OUTPUT inserted”; these are SQL phrases which return a server-generated value at the same time as the INSERT or UPDATE statement is invoked. RETURNING is currently supported by PostgreSQL, Oracle, MariaDB 10.5, SQLite 3.35, and SQL Server.

Case 1: non primary key, RETURNING or equivalent is supported

In this case, columns should be marked as FetchedValue or with an explicit Column.server_default. The ORM will automatically add these columns to the RETURNING clause when performing INSERT statements, assuming the Mapper.eager_defaults parameter is set to True, or if left at its default setting of "auto", for dialects that support both RETURNING as well as insertmanyvalues:

  1. class MyModel(Base):
  2.     __tablename__ = "my_table"
  4.     id = mapped_column(Integer, primary_key=True)
  6.     # server-side SQL date function generates a new timestamp
  7.     timestamp = mapped_column(DateTime(), server_default=func.now())
  9.     # some other server-side function not named here, such as a trigger,
  10.     # populates a value into this column during INSERT
  11.     special_identifier = mapped_column(String(50), server_default=FetchedValue())
  13.     # set eager defaults to True.  This is usually optional, as if the
  14.     # backend supports RETURNING + insertmanyvalues, eager defaults
  15.     # will take place regardless on INSERT
  16.     __mapper_args__ = {"eager_defaults": True}

Above, an INSERT statement that does not specify explicit values for “timestamp” or “special_identifier” from the client side will include the “timestamp” and “special_identifier” columns within the RETURNING clause so they are available immediately. On the PostgreSQL database, an INSERT for the above table will look like:

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

Changed in version 2.0.0rc1: The Mapper.eager_defaults parameter now defaults to a new setting "auto", which will automatically make use of RETURNING to fetch server-generated default values on INSERT if the backing database supports both RETURNING as well as insertmanyvalues.

Note

The "auto" value for Mapper.eager_defaults only applies to INSERT statements. UPDATE statements will not use RETURNING, even if available, unless Mapper.eager_defaults is set to True. This is because there is no equivalent “insertmanyvalues” feature for UPDATE, so UPDATE RETURNING will require that UPDATE statements are emitted individually for each row being UPDATEd.

Case 2: Table includes trigger-generated values which are not compatible with RETURNING

The "auto" setting of Mapper.eager_defaults means that a backend that supports RETURNING will usually make use of RETURNING with INSERT statements in order to retreive newly generated default values. However there are limitations of server-generated values that are generated using triggers, such that RETURNING can’t be used:

  • SQL Server does not allow RETURNING to be used in an INSERT statement to retrieve a trigger-generated value; the statement will fail.

  • SQLite has limitations in combining the use of RETURNING with triggers, such that the RETURNING clause will not have the INSERTed value available

  • Other backends may have limitations with RETURNING in conjunction with triggers, or other kinds of server-generated values.

To disable the use of RETURNING for such values, including not just for server generated default values but also to ensure that the ORM will never use RETURNING with a particular table, specify Table.implicit_returning as False for the mapped Table. Using a Declarative mapping this looks like:

  1. class MyModel(Base):
  2.     __tablename__ = "my_table"
  4.     id: Mapped[int] = mapped_column(primary_key=True)
  5.     data: Mapped[str] = mapped_column(String(50))
  7.     # assume a database trigger populates a value into this column
  8.     # during INSERT
  9.     special_identifier = mapped_column(String(50), server_default=FetchedValue())
  11.     # disable all use of RETURNING for the table
  12.     __table_args__ = {"implicit_returning": False}

On SQL Server with the pyodbc driver, an INSERT for the above table will not use RETURNING and will use the SQL Server scope_identity() function to retreive the newly generated primary key value:

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

See also

INSERT behavior - background on the SQL Server dialect’s methods of fetching newly generated primary key values

Case 3: non primary key, RETURNING or equivalent is not supported or not needed

This case is the same as case 1 above, except we typically don’t want to use Mapper.eager_defaults, as its current implementation in the absence of RETURNING support is to emit a SELECT-per-row, which is not performant. Therefore the parameter is omitted in the mapping below:

  1. class MyModel(Base):
  2.     __tablename__ = "my_table"
  4.     id = mapped_column(Integer, primary_key=True)
  5.     timestamp = mapped_column(DateTime(), server_default=func.now())
  7.     # assume a database trigger populates a value into this column
  8.     # during INSERT
  9.     special_identifier = mapped_column(String(50), server_default=FetchedValue())

After a record with the above mapping is INSERTed on a backend that does not include RETURNING or “insertmanyvalues” support, the “timestamp” and “special_identifier” columns will remain empty, and will be fetched via a second SELECT statement when they are first accessed after the flush, e.g. they are marked as “expired”.

If the Mapper.eager_defaults is explicitly provided with a value of True, and the backend database does not support RETURNING or an equivalent, the ORM will emit a SELECT statement immediately following the INSERT statement in order to fetch newly generated values; the ORM does not currently have the ability to SELECT many newly inserted rows in batch if RETURNING was not available. This is usually undesirable as it adds additional SELECT statements to the flush process that may not be needed. Using the above mapping with the Mapper.eager_defaults flag set to True against MySQL (not MariaDB) results in SQL like this upon flush:

  1. INSERT INTO my_table () VALUES ()
  3. -- when eager_defaults **is** used, but RETURNING is not supported
  4. SELECT my_table.timestamp AS my_table_timestamp, my_table.special_identifier AS my_table_special_identifier
  5. FROM my_table WHERE my_table.id = %s

A future release of SQLAlchemy may seek to improve the efficiency of eager defaults in the abcense of RETURNING to batch many rows within a single SELECT statement.

Case 4: primary key, RETURNING or equivalent is supported

A primary key column with a server-generated value must be fetched immediately upon INSERT; the ORM can only access rows for which it has a primary key value, so if the primary key is generated by the server, the ORM needs a way to retrieve that new value immediately upon INSERT.

As mentioned above, for integer “autoincrement” columns, as well as columns marked with Identity and special constructs such as PostgreSQL SERIAL, these types are handled automatically by the Core; databases include functions for fetching the “last inserted id” where RETURNING is not supported, and where RETURNING is supported SQLAlchemy will use that.

For example, using Oracle with a column marked as Identity, RETURNING is used automatically to fetch the new primary key value:

  1. class MyOracleModel(Base):
  2.     __tablename__ = "my_table"
  4.     id: Mapped[int] = mapped_column(Identity(), primary_key=True)
  5.     data: Mapped[str] = mapped_column(String(50))

The INSERT for a model as above on Oracle looks like:

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

SQLAlchemy renders an INSERT for the “data” field, but only includes “id” in the RETURNING clause, so that server-side generation for “id” will take place and the new value will be returned immediately.

For non-integer values generated by server side functions or triggers, as well as for integer values that come from constructs outside the table itself, including explicit sequences and triggers, the server default generation must be marked in the table metadata. Using Oracle as the example again, we can illustrate a similar table as above naming an explicit sequence using the Sequence construct:

  1. class MyOracleModel(Base):
  2.     __tablename__ = "my_table"
  4.     id: Mapped[int] = mapped_column(Sequence("my_oracle_seq"), primary_key=True)
  5.     data: Mapped[str] = mapped_column(String(50))

An INSERT for this version of the model on Oracle would look like:

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

Where above, SQLAlchemy renders my_sequence.nextval for the primary key column so that it is used for new primary key generation, and also uses RETURNING to get the new value back immediately.

If the source of data is not represented by a simple SQL function or Sequence, such as when using triggers or database-specific datatypes that produce new values, the presence of a value-generating default may be indicated by using FetchedValue within the column definition. Below is a model that uses a SQL Server TIMESTAMP column as the primary key; on SQL Server, this datatype generates new values automatically, so this is indicated in the table metadata by indicating FetchedValue for the Column.server_default parameter:

  1. class MySQLServerModel(Base):
  2.     __tablename__ = "my_table"
  4.     timestamp: Mapped[datetime.datetime] = mapped_column(
  5.         TIMESTAMP(), server_default=FetchedValue(), primary_key=True
  6.     )
  7.     data: Mapped[str] = mapped_column(String(50))

An INSERT for the above table on SQL Server looks like:

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

Case 5: primary key, RETURNING or equivalent is not supported

In this area we are generating rows for a database such as MySQL where some means of generating a default is occurring on the server, but is outside of the database’s usual autoincrement routine. In this case, we have to make sure SQLAlchemy can “pre-execute” the default, which means it has to be an explicit SQL expression.

Note

This section will illustrate multiple recipes involving datetime values for MySQL, since the datetime datatypes on this backend has additional idiosyncratic requirements that are useful to illustrate. Keep in mind however that MySQL requires an explicit “pre-executed” default generator for any auto-generated datatype used as the primary key other than the usual single-column autoincrementing integer value.

MySQL with DateTime primary key

Using the example of a DateTime column for MySQL, we add an explicit pre-execute-supported default using the “NOW()” SQL function:

  1. class MyModel(Base):
  2.     __tablename__ = "my_table"
  4.     timestamp = mapped_column(DateTime(), default=func.now(), primary_key=True)

Where above, we select the “NOW()” function to deliver a datetime value to the column. The SQL generated by the above is:

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

MySQL with TIMESTAMP primary key

When using the TIMESTAMP datatype with MySQL, MySQL ordinarily associates a server-side default with this datatype automatically. However when we use one as a primary key, the Core cannot retrieve the newly generated value unless we execute the function ourselves. As TIMESTAMP on MySQL actually stores a binary value, we need to add an additional “CAST” to our usage of “NOW()” so that we retrieve a binary value that can be persisted into the column:

  1. from sqlalchemy import cast, Binary
  4. class MyModel(Base):
  5.     __tablename__ = "my_table"
  7.     timestamp = mapped_column(
  8.         TIMESTAMP(), default=cast(func.now(), Binary), primary_key=True
  9.     )

Above, in addition to selecting the “NOW()” function, we additionally make use of the Binary datatype in conjunction with cast() so that the returned value is binary. SQL rendered from the above within an INSERT looks like:

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

See also

Column INSERT/UPDATE Defaults

Notes on eagerly fetching client invoked SQL expressions used for INSERT or UPDATE

The preceding examples indicate the use of Column.server_default to create tables that include default-generation functions within their DDL.

SQLAlchemy also supports non-DDL server side defaults, as documented at Client-Invoked SQL Expressions; these “client invoked SQL expressions” are set up using the Column.default and Column.onupdate parameters.

These SQL expressions currently are subject to the same limitations within the ORM as occurs for true server-side defaults; they won’t be eagerly fetched with RETURNING when Mapper.eager_defaults is set to "auto" or True unless the FetchedValue directive is associated with the Column, even though these expressions are not DDL server defaults and are actively rendered by SQLAlchemy itself. This limitation may be addressed in future SQLAlchemy releases.

The FetchedValue construct can be applied to Column.server_default or Column.server_onupdate at the same time that a SQL expression is used with Column.default and Column.onupdate, such as in the example below where the func.now() construct is used as a client-invoked SQL expression for Column.default and Column.onupdate. In order for the behavior of Mapper.eager_defaults to include that it fetches these values using RETURNING when available, Column.server_default and Column.server_onupdate are used with FetchedValue to ensure that the fetch occurs:

  1. class MyModel(Base):
  2.     __tablename__ = "my_table"
  4.     id = mapped_column(Integer, primary_key=True)
  6.     created = mapped_column(
  7.         DateTime(), default=func.now(), server_default=FetchedValue()
  8.     )
  9.     updated = mapped_column(
  10.         DateTime(),
  11.         onupdate=func.now(),
  12.         server_default=FetchedValue(),
  13.         server_onupdate=FetchedValue(),
  14.     )
  16.     __mapper_args__ = {"eager_defaults": True}

With a mapping similar to the above, the SQL rendered by the ORM for INSERT and UPDATE will include created and updated in the RETURNING clause:

  1. INSERT INTO my_table (created) VALUES (now()) RETURNING my_table.id, my_table.created, my_table.updated
  3. UPDATE my_table SET updated=now() WHERE my_table.id = %(my_table_id)s RETURNING my_table.updated

Using INSERT, UPDATE and ON CONFLICT (i.e. upsert) to return ORM Objects

SQLAlchemy 2.0 includes enhanced capabilities for emitting several varieties of ORM-enabled INSERT, UPDATE, and upsert statements. See the document at ORM-Enabled INSERT, UPDATE, and DELETE statements for documentation. For upsert, see ORM “upsert” Statements.

Using PostgreSQL ON CONFLICT with RETURNING to return upserted ORM objects

This section has moved to ORM “upsert” Statements.

Partitioning Strategies (e.g. multiple database backends per Session)

Simple Vertical Partitioning

Vertical partitioning places different classes, class hierarchies, or mapped tables, across multiple databases, by configuring the Session with the Session.binds argument. This argument receives a dictionary that contains any combination of ORM-mapped classes, arbitrary classes within a mapped hierarchy (such as declarative base classes or mixins), Table objects, and Mapper objects as keys, which then refer typically to Engine or less typically Connection objects as targets. The dictionary is consulted whenever the Session needs to emit SQL on behalf of a particular kind of mapped class in order to locate the appropriate source of database connectivity:

  1. engine1 = create_engine("postgresql+psycopg2://db1")
  2. engine2 = create_engine("postgresql+psycopg2://db2")
  4. Session = sessionmaker()
  6. # bind User operations to engine 1, Account operations to engine 2
  7. Session.configure(binds={User: engine1, Account: engine2})
  9. session = Session()

Above, SQL operations against either class will make usage of the Engine linked to that class. The functionality is comprehensive across both read and write operations; a Query that is against entities mapped to engine1 (determined by looking at the first entity in the list of items requested) will make use of engine1 to run the query. A flush operation will make use of both engines on a per-class basis as it flushes objects of type User and Account.

In the more common case, there are typically base or mixin classes that can be used to distinguish between operations that are destined for different database connections. The Session.binds argument can accommodate any arbitrary Python class as a key, which will be used if it is found to be in the __mro__ (Python method resolution order) for a particular mapped class. Supposing two declarative bases are representing two different database connections:

  1. from sqlalchemy.orm import DeclarativeBase
  2. from sqlalchemy.orm import Session
  5. class BaseA(DeclarativeBase):
  6.     pass
  9. class BaseB(DeclarativeBase):
  10.     pass
  13. class User(BaseA):
  14.     ...
  17. class Address(BaseA):
  18.     ...
  21. class GameInfo(BaseB):
  22.     ...
  25. class GameStats(BaseB):
  26.     ...
  29. Session = sessionmaker()
  31. # all User/Address operations will be on engine 1, all
  32. # Game operations will be on engine 2
  33. Session.configure(binds={BaseA: engine1, BaseB: engine2})

Above, classes which descend from BaseA and BaseB will have their SQL operations routed to one of two engines based on which superclass they descend from, if any. In the case of a class that descends from more than one “bound” superclass, the superclass that is highest in the target class’ hierarchy will be chosen to represent which engine should be used.

See also

Session.binds

Coordination of Transactions for a multiple-engine Session

One caveat to using multiple bound engines is in the case where a commit operation may fail on one backend after the commit has succeeded on another. This is an inconsistency problem that in relational databases is solved using a “two phase transaction”, which adds an additional “prepare” step to the commit sequence that allows for multiple databases to agree to commit before actually completing the transaction.

Due to limited support within DBAPIs, SQLAlchemy has limited support for two- phase transactions across backends. Most typically, it is known to work well with the PostgreSQL backend and to a lesser extent with the MySQL backend. However, the Session is fully capable of taking advantage of the two phase transaction feature when the backend supports it, by setting the Session.use_twophase flag within sessionmaker or Session. See Enabling Two-Phase Commit for an example.

Custom Vertical Partitioning

More comprehensive rule-based class-level partitioning can be built by overriding the Session.get_bind() method. Below we illustrate a custom Session which delivers the following rules:

  1. Flush operations, as well as bulk “update” and “delete” operations, are delivered to the engine named leader.

  2. Operations on objects that subclass MyOtherClass all occur on the other engine.

  3. Read operations for all other classes occur on a random choice of the follower1 or follower2 database.

  1. engines = {
  2.     "leader": create_engine("sqlite:///leader.db"),
  3.     "other": create_engine("sqlite:///other.db"),
  4.     "follower1": create_engine("sqlite:///follower1.db"),
  5.     "follower2": create_engine("sqlite:///follower2.db"),
  6. }
  8. from sqlalchemy.sql import Update, Delete
  9. from sqlalchemy.orm import Session, sessionmaker
  10. import random
  13. class RoutingSession(Session):
  14.     def get_bind(self, mapper=None, clause=None):
  15.         if mapper and issubclass(mapper.class_, MyOtherClass):
  16.             return engines["other"]
  17.         elif self._flushing or isinstance(clause, (Update, Delete)):
  18.             return engines["leader"]
  19.         else:
  20.             return engines[random.choice(["follower1", "follower2"])]

The above Session class is plugged in using the class_ argument to sessionmaker:

  1. Session = sessionmaker(class_=RoutingSession)

This approach can be combined with multiple MetaData objects, using an approach such as that of using the declarative __abstract__ keyword, described at __abstract__.

See also

Django-style Database Routers in SQLAlchemy - blog post on a more comprehensive example of Session.get_bind()

Horizontal Partitioning

Horizontal partitioning partitions the rows of a single table (or a set of tables) across multiple databases. The SQLAlchemy Session contains support for this concept, however to use it fully requires that Session and Query subclasses are used. A basic version of these subclasses are available in the Horizontal Sharding ORM extension. An example of use is at: Horizontal Sharding.

Bulk Operations

Legacy Feature

SQLAlchemy 2.0 has integrated the Session “bulk insert” and “bulk update” capabilities into 2.0 style Session.execute() method, making direct use of Insert and Update constructs. See the document at ORM-Enabled INSERT, UPDATE, and DELETE statements for documentation, including Legacy Session Bulk INSERT Methods which illustrates migration from the older methods to the new methods.