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:
class SomeClass(object):
pass
mapper(SomeClass, some_table)
someobject = session.query(SomeClass).get(5)
# set 'value' attribute to a SQL expression adding one
someobject.value = some_table.c.value + 1
# issues "UPDATE some_table SET value=value+1"
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.
SQL expressions and strings can be executed via the
Session within its transactional context.
This is most easily accomplished using the
execute() method, which returns a
ResultProxy in the same manner as an
Engine or
Connection:
Session = sessionmaker(bind=engine)
session = Session()
# execute a string statement
result = session.execute("select * from table where id=:id", {'id':7})
# execute a SQL expression construct
result = session.execute(select([mytable]).where(mytable.c.id==7))The current Connection held by the
Session is accessible using the
connection() method:
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
execute() and
connection() accept a mapper keyword
argument, which is passed a mapped class or
Mapper instance, which is used to locate the
proper context for the desired engine:
Session = sessionmaker()
session = Session()
# need to specify mapper or class when executing
result = session.execute("select * from table where id=:id", {'id':7}, mapper=MyMappedClass)
result = session.execute(select([mytable], mytable.c.id==7), mapper=MyMappedClass)
connection = session.connection(MyMappedClass)Vertical partitioning places different kinds of objects, or different tables, across multiple databases:
engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db2')
Session = sessionmaker(twophase=True)
# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User:engine1, Account:engine2})
session = Session()Above, operations against either class will make usage of the Engine
linked to that class. Upon a flush operation, similar rules take place
to ensure each class is written to the right database.
The transactions among the multiple databases can optionally be coordinated via two phase commit, if the underlying backend supports it. See Enabling Two-Phase Commit for an example.
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:
master.MyOtherClass all
occur on the other engine.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 partitions the rows of a single table (or a set of tables) across multiple databases.
See the “sharding” example: Horizontal Sharding.