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.mapper.eager_defaults flag may be used to indicate that these columns should be fetched immediately upon INSERT and sometimes UPDATE:

class MyModel(Base):
    __tablename__ = 'my_table'

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

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

    __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:

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

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

This case is the same as case 1 above, except we don’t specify orm.mapper.eager_defaults:

class MyModel(Base):
    __tablename__ = 'my_table'

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

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

After a record with the above mapping is INSERTed, 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 orm.mapper.eager_defaults is still used, and the backend database does not support RETURNING or an equivalent, the ORM will emit this SELECT statement immediately following the INSERT statement. This is often undesirable as it adds additional SELECT statements to the flush process that may not be needed. Using the above mapping with the orm.mapper.eager_defaults flag set to True against MySQL results in SQL like this upon flush (minus the comment, which is for clarification only):

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

Case 3: 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 for the database to give us that new value immediately upon INSERT.

As mentioned above, for integer “autoincrement” columns as well 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.

However, for non-integer values, as well as for integer values that must be explicitly linked to a sequence or other triggered routine, the server default generation must be marked in the table metadata.

For an explicit sequence as we use with Oracle, this just means we are using the Sequence construct:

class MyOracleModel(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, Sequence("my_sequence"), primary_key=True)
    data = Column(String(50))

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

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

Where above, SQLAlchemy renders my_sequence.nextval for the primary key column and also uses RETURNING to get the new value back immediately.

For datatypes that generate values automatically, or columns that are populated by a trigger, we use FetchedValue. Below is a model that uses a SQL Server TIMESTAMP column as the primary key, which generates values automatically:

class MyModel(Base):
    __tablename__ = 'my_table'

    timestamp = Column(TIMESTAMP(), server_default=FetchedValue(), primary_key=True)

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

INSERT INTO my_table OUTPUT inserted.timestamp DEFAULT VALUES

Case 4: primary key, RETURNING or equivalent is not supported¶

In this area we are generating rows for a database such as SQLite or 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.

class MyModel(Base):
    __tablename__ = 'my_table'

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

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

from sqlalchemy import cast, Binary

class MyModel(Base):
    __tablename__ = 'my_table'

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

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

class MyModel(Base):
    __tablename__ = 'my_table'

    timestamp = Column(
        DateTime,
        default=func.datetime('now', 'localtime', type_=DateTime),
        primary_key=True)

SELECT datetime(?, ?) AS datetime_1
('now', 'localtime')
INSERT INTO my_table (timestamp) VALUES (?)
('2018-10-02 13:37:33.000000',)

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:

engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db2')

Session = sessionmaker()

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

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:

BaseA = declarative_base()

BaseB = declarative_base()

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})

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.

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 are delivered to the engine named master.
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 slave1 or slave2 database.

engines = {
    'master':create_engine("sqlite:///master.db"),
    'other':create_engine("sqlite:///other.db"),
    'slave1':create_engine("sqlite:///slave1.db"),
    'slave2':create_engine("sqlite:///slave2.db"),
}

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:
            return engines['master']
        else:
            return engines[
                random.choice(['slave1','slave2'])
            ]

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

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__.

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.

Usage¶

The methods each work in the context of the Session object’s transaction, like any other:

s = Session()
objects = [
    User(name="u1"),
    User(name="u2"),
    User(name="u3")
]
s.bulk_save_objects(objects)

For Session.bulk_insert_mappings(), and Session.bulk_update_mappings(), dictionaries are passed:

s.bulk_insert_mappings(User,
  [dict(name="u1"), dict(name="u2"), dict(name="u3")]
)

Comparison to Core Insert / Update Constructs¶

The bulk methods offer performance that under particular circumstances can be close to that of using the core Insert and Update constructs in an “executemany” context (for a description of “executemany”, see Executing Multiple Statements in the Core tutorial). In order to achieve this, the Session.bulk_insert_mappings.return_defaults flag should be disabled so that rows can be batched together. The example suite in Performance should be carefully studied in order to gain familiarity with how fast bulk performance can be achieved.

ORM Compatibility¶

The bulk insert / update methods lose a significant amount of functionality versus traditional ORM use. The following is a listing of features that are not available when using these methods:

• persistence along relationship() linkages
• sorting of rows within order of dependency; rows are inserted or updated directly in the order in which they are passed to the methods
• Session-management on the given objects, including attachment to the session, identity map management.
• Functionality related to primary key mutation, ON UPDATE cascade
• SQL expression inserts / updates (e.g. Embedding SQL Insert/Update Expressions into a Flush)
• ORM events such as MapperEvents.before_insert(), etc. The bulk session methods have no event support.

Features that are available include:

• INSERTs and UPDATEs of mapped objects
• Version identifier support
• Multi-table mappings, such as joined-inheritance - however, an object to be inserted across multiple tables either needs to have primary key identifiers fully populated ahead of time, else the Session.bulk_save_objects.return_defaults flag must be used, which will greatly reduce the performance benefits