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.
Deep Alchemy
The horizontal sharding extension is an advanced feature,
involving a complex statement -> database interaction as well as
use of semi-public APIs for non-trivial cases. Simpler approaches to
refering to multiple database “shards”, most commonly using a distinct
Session
per “shard”, should always be considered first
before using this more complex and less-production-tested system.
API Documentation¶
Object Name | Description |
---|---|
a loader option for statements to apply a specific shard id to the primary query as well as for additional relationship and column loaders. |
|
Query class used with |
|
- class sqlalchemy.ext.horizontal_shard.ShardedSession¶
Members
Class signature
class
sqlalchemy.ext.horizontal_shard.ShardedSession
(sqlalchemy.orm.session.Session
)-
method
sqlalchemy.ext.horizontal_shard.ShardedSession.
__init__(shard_chooser: ShardChooser, identity_chooser: Optional[IdentityChooser] = None, execute_chooser: Optional[Callable[[ORMExecuteState], Iterable[Any]]] = None, shards: Optional[Dict[str, Any]] = None, query_cls: Type[Query[_T]] = <class 'sqlalchemy.ext.horizontal_shard.ShardedQuery'>, *, id_chooser: Optional[Callable[[Query[_T], Iterable[_T]], Iterable[Any]]] = None, query_chooser: Optional[Callable[[Executable], Iterable[Any]]] = None, **kwargs: Any) None ¶ 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.
identity_chooser –
A callable, passed a Mapper and primary key argument, which should return a list of shard ids where this primary key might reside.
Changed in version 2.0: The
identity_chooser
parameter supersedes theid_chooser
parameter.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 thequery_chooser
parameter.shards – A dictionary of string shard names to
Engine
objects.
-
method
sqlalchemy.ext.horizontal_shard.ShardedSession.
connection_callable(mapper: Optional[Mapper[_T]] = None, instance: Optional[Any] = None, shard_id: Optional[ShardIdentifier] = None, **kw: Any) Connection ¶ Provide a
Connection
to use in the unit of work flush process.
-
method
sqlalchemy.ext.horizontal_shard.ShardedSession.
get_bind(mapper: Optional[_EntityBindKey[_O]] = None, *, shard_id: Optional[ShardIdentifier] = None, instance: Optional[Any] = None, clause: Optional[ClauseElement] = None, **kw: Any) _SessionBind ¶ Return a “bind” to which this
Session
is bound.The “bind” is usually an instance of
Engine
, except in the case where theSession
has been explicitly bound directly to aConnection
.For a multiply-bound or unbound
Session
, themapper
orclause
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 aSession.query()
, each individual INSERT/UPDATE/DELETE operation within aSession.flush()
, call, etc.The order of resolution is:
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.if clause given and
Session.binds
is present, locate a bind based onTable
objects found in the given clause present inSession.binds
.if
Session.binds
is present, return that.if clause given, attempt to return a bind linked to the
MetaData
ultimately associated with the clause.if mapper given, attempt to return a bind linked to the
MetaData
ultimately associated with theTable
or other selectable to which the mapper is mapped.No bind can be found,
UnboundExecutionError
is raised.
Note that the
Session.get_bind()
method can be overridden on a user-defined subclass ofSession
to provide any kind of bind resolution scheme. See the example at Custom Vertical Partitioning.- Parameters:
mapper – Optional mapped class or corresponding
Mapper
instance. The bind can be derived from aMapper
first by consulting the “binds” map associated with thisSession
, and secondly by consulting theMetaData
associated with theTable
to which theMapper
is mapped for a bind.clause – A
ClauseElement
(i.e.select()
,text()
, etc.). If themapper
argument is not present or could not produce a bind, the given expression construct will be searched for a bound element, typically aTable
associated with boundMetaData
.
-
method
- class sqlalchemy.ext.horizontal_shard.set_shard_id¶
a loader option for statements to apply a specific shard id to the primary query as well as for additional relationship and column loaders.
The
set_shard_id
option may be applied using theExecutable.options()
method of any executable statement:stmt = ( select(MyObject). where(MyObject.name == 'some name'). options(set_shard_id("shard1")) )
Above, the statement when invoked will limit to the “shard1” shard identifier for the primary query as well as for all relationship and column loading strategies, including eager loaders such as
selectinload()
, deferred column loaders likedefer()
, and the lazy relationship loaderlazyload()
.In this way, the
set_shard_id
option has much wider scope than using the “shard_id” argument within theSession.execute.bind_arguments
dictionary.New in version 2.0.0.
Members
Class signature
class
sqlalchemy.ext.horizontal_shard.set_shard_id
(sqlalchemy.orm.ORMOption
)-
method
sqlalchemy.ext.horizontal_shard.set_shard_id.
__init__(shard_id: str, propagate_to_loaders: bool = True)¶ Construct a
set_shard_id
option.- Parameters:
shard_id – shard identifier
propagate_to_loaders – if left at its default of
True
, the shard option will take place for lazy loaders such aslazyload()
anddefer()
; if False, the option will not be propagated to loaded objects. Note thatdefer()
always limits to the shard_id of the parent row in any case, so the parameter only has a net effect on the behavior of thelazyload()
strategy.
-
attribute
sqlalchemy.ext.horizontal_shard.set_shard_id.
propagate_to_loaders¶ if True, indicate this option should be carried along to “secondary” SELECT statements that occur for relationship lazy loaders as well as attribute load / refresh operations.
-
method
- class sqlalchemy.ext.horizontal_shard.ShardedQuery¶
Query class used with
ShardedSession
.Legacy Feature
The
ShardedQuery
is a subclass of the legacyQuery
class. TheShardedSession
now supports 2.0 style execution via theShardedSession.execute()
method.Members
Class signature
class
sqlalchemy.ext.horizontal_shard.ShardedQuery
(sqlalchemy.orm.Query
)-
method
sqlalchemy.ext.horizontal_shard.ShardedQuery.
set_shard(shard_id: str) Self ¶ 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"} )
-
method