Horizontal Sharding

Horizontal sharding support.

Defines a rudimental ‘horizontal sharding’ system which allows a Session to distribute queries and persistence operations across multiple databases.

For a usage example, see the Horizontal Sharding example included in the source distribution.

API Documentation

Object Name Description



class sqlalchemy.ext.horizontal_shard.ShardedSession(shard_chooser, id_chooser, query_chooser, shards=None, query_cls=<class 'sqlalchemy.ext.horizontal_shard.ShardedQuery'>, **kwargs)
method sqlalchemy.ext.horizontal_shard.ShardedSession.__init__(shard_chooser, id_chooser, query_chooser, shards=None, query_cls=<class 'sqlalchemy.ext.horizontal_shard.ShardedQuery'>, **kwargs)

Construct a ShardedSession.

  • shard_chooser – A callable which, passed a Mapper, a mapped instance, and possibly a SQL clause, returns a shard ID. This id may be based off of the attributes present within the object, or on some round-robin scheme. If the scheme is based on a selection, it should set whatever state on the instance to mark it in the future as participating in that shard.

  • id_chooser – A callable, passed a query and a tuple of identity values, which should return a list of shard ids where the ID might reside. The databases will be queried in the order of this listing.

  • query_chooser – For a given Query, returns the list of shard_ids where the query should be issued. Results from all shards returned will be combined together into a single listing.

  • shards – A dictionary of string shard names to Engine objects.

method sqlalchemy.ext.horizontal_shard.ShardedSession.connection(mapper=None, instance=None, shard_id=None, **kwargs)

Return a Connection object corresponding to this Session object’s transactional state.

If this Session is configured with autocommit=False, either the Connection corresponding to the current transaction is returned, or if no transaction is in progress, a new one is begun and the Connection returned (note that no transactional state is established with the DBAPI until the first SQL statement is emitted).

Alternatively, if this Session is configured with autocommit=True, an ad-hoc Connection is returned using Engine.connect() on the underlying Engine.

Ambiguity in multi-bind or unbound Session objects can be resolved through any of the optional keyword arguments. This ultimately makes usage of the get_bind() method for resolution.

  • bind – Optional Engine to be used as the bind. If this engine is already involved in an ongoing transaction, that connection will be used. This argument takes precedence over mapper, clause.

  • mapper – Optional mapper() mapped class, used to identify the appropriate bind. This argument takes precedence over clause.

  • clause – A ClauseElement (i.e. select(), text(), etc.) which will be used to locate a bind, if a bind cannot otherwise be identified.

  • close_with_result – Passed to Engine.connect(), indicating the Connection should be considered “single use”, automatically closing when the first result set is closed. This flag only has an effect if this Session is configured with autocommit=True and does not already have a transaction in progress.

  • execution_options

    a dictionary of execution options that will be passed to Connection.execution_options(), when the connection is first procured only. If the connection is already present within the Session, a warning is emitted and the arguments are ignored.

    New in version 0.9.9.

  • **kw – Additional keyword arguments are sent to get_bind(), allowing additional arguments to be passed to custom implementations of get_bind().

method sqlalchemy.ext.horizontal_shard.ShardedSession.get_bind(mapper, shard_id=None, instance=None, clause=None, **kw)

Return a “bind” to which this Session is bound.

The “bind” is usually an instance of Engine, except in the case where the Session has been explicitly bound directly to a Connection.

For a multiply-bound or unbound Session, the mapper or clause arguments are used to determine the appropriate bind to return.

Note that the “mapper” argument is usually present when Session.get_bind() is called via an ORM operation such as a Session.query(), each individual INSERT/UPDATE/DELETE operation within a Session.flush(), call, etc.

The order of resolution is:

  1. if mapper given and Session.binds is present, locate a bind based first on the mapper in use, then on the mapped class in use, then on any base classes that are present in the __mro__ of the mapped class, from more specific superclasses to more general.

  2. if clause given and Session.binds is present, locate a bind based on Table objects found in the given clause present in Session.binds.

  3. if Session.binds is present, return that.

  4. if clause given, attempt to return a bind linked to the MetaData ultimately associated with the clause.

  5. if mapper given, attempt to return a bind linked to the MetaData ultimately associated with the Table or other selectable to which the mapper is mapped.

  6. No bind can be found, UnboundExecutionError is raised.

Note that the Session.get_bind() method can be overridden on a user-defined subclass of Session to provide any kind of bind resolution scheme. See the example at Custom Vertical Partitioning.

  • mapper – Optional mapper() mapped class or instance of Mapper. The bind can be derived from a Mapper first by consulting the “binds” map associated with this Session, and secondly by consulting the MetaData associated with the Table to which the Mapper is mapped for a bind.

  • clause – A ClauseElement (i.e. select(), text(), etc.). If the mapper argument is not present or could not produce a bind, the given expression construct will be searched for a bound element, typically a Table associated with bound MetaData.

class sqlalchemy.ext.horizontal_shard.ShardedQuery(*args, **kwargs)



method sqlalchemy.ext.horizontal_shard.ShardedQuery.set_shard(shard_id)

Return a new query, limited to a single shard ID.

All subsequent operations with the returned query will be against the single shard regardless of other state.