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
ShardedQuery
ShardedSession

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

Members

__init__(), connection_callable(), get_bind()

Class signature

class sqlalchemy.ext.horizontal_shard.ShardedSession (sqlalchemy.orm.session.Session)

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

Construct a ShardedSession.

Parameters:

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

• execute_chooser –

  For a given ORMExecuteState, 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.

  Changed in version 1.4: The execute_chooser parameter supersedes the query_chooser parameter.

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

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

Provide a Connection to use in the unit of work flush process.

method sqlalchemy.ext.horizontal_shard.ShardedSession.get_bind(mapper=None, 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.

Parameters:

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

See also

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

Session.binds

Session.bind_mapper()

Session.bind_table()

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

Members

set_shard()

Class signature

class sqlalchemy.ext.horizontal_shard.ShardedQuery (sqlalchemy.orm.Query)

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.

The shard_id can be passed for a 2.0 style execution to the bind_arguments dictionary of Session.execute():

results = session.execute(
    stmt,
    bind_arguments={"shard_id": "my_shard"}
)