text:
SQLAlchemy 0.8 Documentation
Using the Session
- Using the Session
- What does the Session do ?
- Getting a Session
- Using the Session
- Quickie Intro to Object States
- Session Frequently Asked Questions
- Querying
- Adding New or Existing Items
- Merging
- Deleting
- Flushing
- Committing
- Rolling Back
- Expunging
- Closing
- Refreshing / Expiring
- Session Attributes
- Cascades
- Managing Transactions
- Embedding SQL Insert/Update Expressions into a Flush
- Using SQL Expressions with Sessions
- Joining a Session into an External Transaction
- Contextual/Thread-local Sessions
- Partitioning Strategies
- Sessions API
Project Versions
Using the Session¶
orm.mapper() function and
declarative extensions
are the primary configurational interface for the ORM. Once mappings are
configured, the primary usage interface for persistence operations is the
What does the Session do ?¶
In the most general sense, the
Session establishes all
conversations with the database and represents a "holding zone" for all the
objects which you've loaded or associated with it during its lifespan. It
provides the entrypoint to acquire a
Query object, which sends
queries to the database using the
Session object's current database
connection, populating result rows into objects that are then stored in the
Session, inside a structure called the Identity Map - a data structure
that maintains unique copies of each object, where "unique" means "only one
object with a particular primary key".
Session begins in an essentially stateless form. Once queries
are issued or other objects are persisted with it, it requests a connection
resource from an
Engine that is associated either with the
Session itself or with the mapped
Table objects being
operated upon. This connection represents an ongoing transaction, which
remains in effect until the
Session is instructed to commit or roll
back its pending state.
All changes to objects maintained by a
Session are tracked - before
the database is queried again or before the current transaction is committed,
it flushes all pending changes to the database. This is known as the Unit
of Work pattern.
When using a
Session, it's important to note that the objects
which are associated with it are proxy objects to the transaction being
held by the
Session - there are a variety of events that will cause
objects to re-access the database in order to keep synchronized. It is
possible to "detach" objects from a
Session, and to continue using
them, though this practice has its caveats. It's intended that
usually, you'd re-associate detached objects with another
Session when you
want to work with them again, so that they can resume their normal task of
representing database state.
Getting a Session¶
Session is a regular Python class which can
be directly instantiated. However, to standardize how sessions are configured
and acquired, the
sessionmaker class is normally
used to create a top level
configuration which can then be used throughout an application without the
need to repeat the configurational arguments.
The usage of
sessionmaker is illustrated below:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# an Engine, which the Session will use for connection
# resources
some_engine = create_engine('postgresql://scott:tiger@localhost/')
# create a configured "Session" class
Session = sessionmaker(bind=some_engine)
# create a Session
session = Session()
# work with sess
myobject = MyObject('foo', 'bar')
Above, the
sessionmaker call creates a factory for us,
which we assign to the name
Session. This factory, when
called, will create a new
Session object using the configurational
arguments we've given the factory. In this case, as is typical,
we've configured the factory to specify a particular
Engine for
connection resources.
A typical setup will associate the
sessionmaker with an
so that each
Session generated will use this
to acquire connection resources. This association can
be set up as in the example above, using the
bind argument.
When you write your application, place the
sessionmaker factory at the global level. This
factory can then
be used by the rest of the applcation as the source of new
instances, keeping the configuration for how
Session objects
are constructed in one place.
sessionmaker factory can also be used in conjunction with
other helpers, which are passed a user-defined
sessionmaker that
is then maintained by the helper. Some of these helpers are discussed in the
section When do I construct a Session, when do I commit it, and when do I close it?.
Adding Additional Configuration to an Existing sessionmaker()¶
A common scenario is where the
sessionmaker is invoked
at module import time, however the generation of one or more
instances to be associated with the
sessionmaker has not yet proceeded.
For this use case, the
sessionmaker construct offers the
sessionmaker.configure() method, which will place additional configuration
directives into an existing
sessionmaker that will take place
when the construct is invoked:
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
# configure Session class with desired options
Session = sessionmaker()
# later, we create the engine
engine = create_engine('postgresql://...')
# associate it with our custom Session class
# work with the session
session = Session()
Creating Ad-Hoc Session Objects with Alternate Arguments¶
For the use case where an application needs to create a new
Session with
special arguments that deviate from what is normally used throughout the application,
such as a
Session that binds to an alternate
source of connectivity, or a
Session that should
have other arguments such as
expire_on_commit established differently from
what most of the application wants, specific arguments can be passed to the
sessionmaker factory's
sessionmaker.__call__() method.
These arguments will override whatever
configurations have already been placed, such as below, where a new
is constructed against a specific
# at the module level, the global sessionmaker,
# bound to a specific Engine
Session = sessionmaker(bind=engine)
# later, some unit of code wants to create a
# Session that is bound to a specific Connection
conn = engine.connect()
session = Session(bind=conn)
The typical rationale for the association of a
Session with a specific
Connection is that of a test fixture that maintains an external
transaction - see Joining a Session into an External Transaction for an example of this.
Using the Session¶
Quickie Intro to Object States¶
It's helpful to know the states which an instance can have within a session:
- Transient - an instance that's not in a session, and is not saved to the
database; i.e. it has no database identity. The only relationship such an
object has to the ORM is that its class has a
mapper()associated with it.
- Pending - when you
add()a transient instance, it becomes pending. It still wasn't actually flushed to the database yet, but it will be when the next flush occurs.
- Persistent - An instance which is present in the session and has a record in the database. You get persistent instances by either flushing so that the pending instances become persistent, or by querying the database for existing instances (or moving persistent instances from other sessions into your local session).
- Detached - an instance which has a record in the database, but is not in any session. There's nothing wrong with this, and you can use objects normally when they're detached, except they will not be able to issue any SQL in order to load collections or attributes which are not yet loaded, or were marked as "expired".
Knowing these states is important, since the
Session tries to be strict about ambiguous
operations (such as trying to save the same object to two different sessions
at the same time).
Getting the Current State of an Object¶
The actual state of any mapped object can be viewed at any time using
inspect() system:
>>> from sqlalchemy import inspect
>>> insp = inspect(my_object)
>>> insp.persistent
Session Frequently Asked Questions¶
When do I make a
Just one time, somewhere in your application's global scope. It should be
looked upon as part of your application's configuration. If your
application has three .py files in a package, you could, for example,
place the
sessionmaker line in your
__init__.py file; from
that point on your other modules say "from mypackage import Session". That
way, everyone else just uses
and the configuration of that session is controlled by that central point.
If your application starts up, does imports, but does not know what
database it's going to be connecting to, you can bind the
Session at the "class" level to the
engine later on, using
In the examples in this section, we will frequently show the
sessionmaker being created right above the line where we actually
Session. But that's just for
example's sake! In reality, the
sessionmaker would be somewhere
at the module level. The calls to instantiate
would then be placed at the point in the application where database
conversations begin.
When do I construct a
Session, when do I commit it, and when do I close it?¶
As a general rule, keep the lifecycle of the session separate and external from functions and objects that access and/or manipulate database data.
Session is typically constructed at the beginning of a logical
operation where database access is potentially anticipated.
Session, whenever it is used to talk to the database,
begins a database transaction as soon as it starts communicating.
Assuming the
autocommit flag is left at its recommended default
False, this transaction remains in progress until the
is rolled back, committed, or closed. The
Session will
begin a new transaction if it is used again, subsequent to the previous
transaction ending; from this it follows that the
is capable of having a lifespan across many transactions, though only
one at a time. We refer to these two concepts as transaction scope
and session scope.
The implication here is that the SQLAlchemy ORM is encouraging the
developer to establish these two scopes in their application,
including not only when the scopes begin and end, but also the
expanse of those scopes, for example should a single
Session instance be local to the execution flow within a
function or method, should it be a global object used by the
entire application, or somewhere in between these two.
The burden placed on the developer to determine this scope is one area where the SQLAlchemy ORM necessarily has a strong opinion about how the database should be used. The unit of work pattern is specifically one of accumulating changes over time and flushing them periodically, keeping in-memory state in sync with what's known to be present in a local transaction. This pattern is only effective when meaningful transaction scopes are in place.
It's usually not very hard to determine the best points at which
to begin and end the scope of a
Session, though the wide
variety of application architectures possible can introduce
challenging situations.
A common choice is to tear down the
Session at the same
time the transaction ends, meaning the transaction and session scopes
are the same. This is a great choice to start out with as it
removes the need to consider session scope as separate from transaction
While there's no one-size-fits-all recommendation for how transaction scope should be determined, there are common patterns. Especially if one is writing a web application, the choice is pretty much established.
A web application is the easiest case because such an appication is already
constructed around a single, consistent scope - this is the request,
which represents an incoming request from a browser, the processing
of that request to formulate a response, and finally the delivery of that
response back to the client. Integrating web applications with the
Session is then the straightforward task of linking the
scope of the
Session to that of the request. The
can be established as the request begins, or using a lazy initialization
pattern which establishes one as soon as it is needed. The request
then proceeds, with some system in place where application logic can access
the current
Session in a manner associated with how the actual
request object is accessed. As the request ends, the
is torn down as well, usually through the usage of event hooks provided
by the web framework. The transaction used by the
may also be committed at this point, or alternatively the application may
opt for an explicit commit pattern, only committing for those requests
where one is warranted, but still always tearing down the
unconditionally at the end.
Some web frameworks include infrastructure to assist in the task
of aligning the lifespan of a
Session with that of a web request.
This includes products such as Flask-SQLAlchemy,
for usage in conjunction with the Flask web framework,
and Zope-SQLAlchemy,
typically used with the Pyramid framework.
SQLAlchemy recommends that these products be used as available.
In those situations where the integration libraries are not
provided or are insufficient, SQLAlchemy includes its own "helper" class known as
scoped_session. A tutorial on the usage of this object
is at Contextual/Thread-local Sessions. It provides both a quick way
to associate a
Session with the current thread, as well as
patterns to associate
Session objects with other kinds of
As mentioned before, for non-web applications there is no one clear pattern, as applications themselves don't have just one pattern of architecture. The best strategy is to attempt to demarcate "operations", points at which a particular thread begins to perform a series of operations for some period of time, which can be committed at the end. Some examples:
- A background daemon which spawns off child forks
would want to create a
Sessionlocal to each child process, work with that
Sessionthrough the life of the "job" that the fork is handling, then tear it down when the job is completed.
- For a command-line script, the application would create a single, global
Sessionthat is established when the program begins to do its work, and commits it right as the program is completing its task.
- For a GUI interface-driven application, the scope of the
Sessionmay best be within the scope of a user-generated event, such as a button push. Or, the scope may correspond to explicit user interaction, such as the user "opening" a series of records, then "saving" them.
As a general rule, the application should manage the lifecycle of the session externally to functions that deal with specific data. This is a fundamental separation of concerns which keeps data-specific operations agnostic of the context in which they access and manipulate that data.
E.g. don't do this:
### this is the **wrong way to do it** ###
class ThingOne(object):
def go(self):
session = Session()
session.query(FooBar).update({"x": 5})
class ThingTwo(object):
def go(self):
session = Session()
session.query(Widget).update({"q": 18})
def run_my_program():
Keep the lifecycle of the session (and usually the transaction) separate and external:
### this is a **better** (but not the only) way to do it ###
class ThingOne(object):
def go(self, session):
session.query(FooBar).update({"x": 5})
class ThingTwo(object):
def go(self, session):
session.query(Widget).update({"q": 18})
def run_my_program():
session = Session()
The advanced developer will try to keep the details of session, transaction and exception management as far as possible from the details of the program doing its work. For example, we can further separate concerns using a context manager:
### another way (but again *not the only way*) to do it ###
from contextlib import contextmanager
def session_scope():
"""Provide a transactional scope around a series of operations."""
session = Session()
yield session
def run_my_program():
with session_scope() as session:
Is the Session a cache?¶
Yeee...no. It's somewhat used as a cache, in that it implements the
identity map pattern, and stores objects keyed to their primary key.
However, it doesn't do any kind of query caching. This means, if you say
session.query(Foo).filter_by(name='bar'), even if
is right there, in the identity map, the session has no idea about that.
It has to issue SQL to the database, get the rows back, and then when it
sees the primary key in the row, then it can look in the local identity
map and see that the object is already there. It's only when you say
query.get({some primary key}) that the
Session doesn't have to issue a query.
Additionally, the Session stores object instances using a weak reference by default. This also defeats the purpose of using the Session as a cache.
Session is not designed to be a
global object from which everyone consults as a "registry" of objects.
That's more the job of a second level cache. SQLAlchemy provides
a pattern for implementing second level caching using dogpile.cache,
via the Dogpile Caching example.
How can I get the
Session for a certain object?¶
Use the
object_session() classmethod
available on
session = Session.object_session(someobject)
The newer Runtime Inspection API system can also be used:
from sqlalchemy import inspect
session = inspect(someobject).session
Is the session thread-safe?¶
Session is very much intended to be used in a
non-concurrent fashion, which usually means in only one thread at a
Session should be used in such a way that one
instance exists for a single series of operations within a single
transaction. One expedient way to get this effect is by associating
Session with the current thread (see Contextual/Thread-local Sessions
for background). Another is to use a pattern
where the
Session is passed between functions and is otherwise
not shared with other threads.
The bigger point is that you should not want to use the session with multiple concurrent threads. That would be like having everyone at a restaurant all eat from the same plate. The session is a local "workspace" that you use for a specific set of tasks; you don't want to, or need to, share that session with other threads who are doing some other task.
Making sure the
Session is only used in a single concurrent thread at a time
is called a "share nothing" approach to concurrency. But actually, not
sharing the
Session implies a more significant pattern; it
means not just the
Session object itself, but
also all objects that are associated with that Session, must be kept within
the scope of a single concurrent thread. The set of mapped
objects associated with a
Session are essentially proxies for data
within database rows accessed over a database connection, and so just like
Session itself, the whole
set of objects is really just a large-scale proxy for a database connection
(or connections). Ultimately, it's mostly the DBAPI connection itself that
we're keeping away from concurrent access; but since the
and all the objects associated with it are all proxies for that DBAPI connection,
the entire graph is essentially not safe for concurrent access.
If there are in fact multiple threads participating
in the same task, then you may consider sharing the session and its objects between
those threads; however, in this extremely unusual scenario the application would
need to ensure that a proper locking scheme is implemented so that there isn't
concurrent access to the
Session or its state. A more common approach
to this situation is to maintain a single
Session per concurrent thread,
but to instead copy objects from one
Session to another, often
using the
Session.merge() method to copy the state of an object into
a new object local to a different
query() function takes one or more
entities and returns a new
Query object which
will issue mapper queries within the context of this Session. An entity is
defined as a mapped class, a
Mapper object, an
orm-enabled descriptor, or an
AliasedClass object:
# query from a class
# query with multiple classes, returns tuples
session.query(User, Address).join('addresses').filter_by(name='ed').all()
# query using orm-enabled descriptors
session.query(User.name, User.fullname).all()
# query from a mapper
user_mapper = class_mapper(User)
Query returns results, each object
instantiated is stored within the identity map. When a row matches an object
which is already present, the same object is returned. In the latter case,
whether or not the row is populated onto an existing object depends upon
whether the attributes of the instance have been expired or not. A
Session automatically
expires all instances along transaction boundaries, so that with a normally
isolated transaction, there shouldn't be any issue of instances representing
data which is stale with regards to the current transaction.
Query object is introduced in great detail in
Object Relational Tutorial, and further documented in
Adding New or Existing Items¶
add() is used to place instances in the
session. For transient (i.e. brand new) instances, this will have the effect
of an INSERT taking place for those instances upon the next flush. For
instances which are persistent (i.e. were loaded by this session), they are
already present and do not need to be added. Instances which are detached
(i.e. have been removed from a session) may be re-associated with a session
using this method:
user1 = User(name='user1')
user2 = User(name='user2')
session.commit() # write changes to the database
To add a list of items to the session at once, use
session.add_all([item1, item2, item3])
add() operation cascades along
save-update cascade. For more details see the section
merge() transfers state from an
outside object into a new or already existing instance within a session. It
also reconciles the incoming data against the state of the
database, producing a history stream which will be applied towards the next
flush, or alternatively can be made to produce a simple "transfer" of
state without producing change history or accessing the database. Usage is as follows:
merged_object = session.merge(existing_object)
When given an instance, it follows these steps:
It examines the primary key of the instance. If it's present, it attempts to locate that instance in the local identity map. If the
load=Trueflag is left at its default, it also checks the database for this primary key if not located locally.
If the given instance has no primary key, or if no instance can be found with the primary key given, a new instance is created.
The state of the given instance is then copied onto the located/newly created instance. For attributes which are present on the source instance, the value is transferred to the target instance. For mapped attributes which aren't present on the source, the attribute is expired on the target instance, discarding its existing value.
If the
load=Trueflag is left at its default, this copy process emits events and will load the target object's unloaded collections for each attribute present on the source object, so that the incoming state can be reconciled against what's present in the database. If
loadis passed as
False, the incoming data is "stamped" directly without producing any history.
The operation is cascaded to related objects and collections, as indicated by the
mergecascade (see Cascades).
The new instance is returned.
merge(), the given "source"
instance is not modified nor is it associated with the target
and remains available to be merged with any number of other
merge() is useful for
taking the state of any kind of object structure without regard for its
origins or current session associations and copying its state into a
new session. Here's some examples:
An application which reads an object structure from a file and wishes to save it to the database might parse the file, build up the structure, and then use
merge()to save it to the database, ensuring that the data within the file is used to formulate the primary key of each element of the structure. Later, when the file has changed, the same process can be re-run, producing a slightly different object structure, which can then be
mergedin again, and the
Sessionwill automatically update the database to reflect those changes, loading each object from the database by primary key and then updating its state with the new state given.
An application is storing objects in an in-memory cache, shared by many
Sessionobjects simultaneously.
merge()is used each time an object is retrieved from the cache to create a local copy of it in each
Sessionwhich requests it. The cached object remains detached; only its state is moved into copies of itself that are local to individual
In the caching use case, it's common to use the
load=Falseflag to remove the overhead of reconciling the object's state with the database. There's also a "bulk" version of
merge_result()that was designed to work with cache-extended
Queryobjects - see the section Dogpile Caching.
An application wants to transfer the state of a series of objects into a
Sessionmaintained by a worker thread or other concurrent system.
merge()makes a copy of each object to be placed into this new
Session. At the end of the operation, the parent thread/process maintains the objects it started with, and the thread/worker can proceed with local copies of those objects.
In the "transfer between threads/processes" use case, the application may want to use the
load=Falseflag as well to avoid overhead and redundant SQL queries as the data is transferred.
Merge Tips¶
merge() is an extremely useful method for many purposes. However,
it deals with the intricate border between objects that are transient/detached and
those that are persistent, as well as the automated transference of state.
The wide variety of scenarios that can present themselves here often require a
more careful approach to the state of objects. Common problems with merge usually involve
some unexpected state regarding the object being passed to
Lets use the canonical example of the User and Address objects:
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False)
addresses = relationship("Address", backref="user")
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email_address = Column(String(50), nullable=False)
user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
Assume a
User object with one
Address, already persistent:
>>> u1 = User(name='ed', addresses=[Address(email@example.com')])
>>> session.add(u1)
>>> session.commit()
We now create
a1, an object outside the session, which we'd like
to merge on top of the existing
>>> existing_a1 = u1.addresses[0]
>>> a1 = Address(id=existing_a1.id)
A surprise would occur if we said this:
>>> a1.user = u1
>>> a1 = session.merge(a1)
>>> session.commit()
sqlalchemy.orm.exc.FlushError: New instance <Address at 0x1298f50>
with identity key (<class '__main__.Address'>, (1,)) conflicts with
persistent instance <Address at 0x12a25d0>
Why is that ? We weren't careful with our cascades. The assignment
a1.user to a persistent object cascaded to the backref of
and made our
a1 object pending, as though we had added it. Now we have
Address objects in the session:
>>> a1 = Address()
>>> a1.user = u1
>>> a1 in session
>>> existing_a1 in session
>>> a1 is existing_a1
Above, our
a1 is already pending in the session. The
merge() operation essentially
does nothing. Cascade can be configured via the
option on
relationship(), although in this case it
would mean removing the
save-update cascade from the
User.addresses relationship - and usually, that behavior
is extremely convenient. The solution here would usually be to not assign
a1.user to an object already persistent in the target
cascade_backrefs=False option of
will also prevent the
Address from
being added to the session via the
a1.user = u1 assignment.
Further detail on cascade operation is at Cascades.
Another example of unexpected state:
>>> a1 = Address(id=existing_a1.id, user_id=u1.id)
>>> assert a1.user is None
>>> True
>>> a1 = session.merge(a1)
>>> session.commit()
sqlalchemy.exc.IntegrityError: (IntegrityError) address.user_id
may not be NULL
Here, we accessed a1.user, which returned its default value
None, which as a result of this access, has been placed in the
__dict__ of
our object
a1. Normally, this operation creates no change event,
so the
user_id attribute takes precedence during a
flush. But when we merge the
Address object into the session, the operation
is equivalent to:
>>> existing_a1.id = existing_a1.id
>>> existing_a1.user_id = u1.id
>>> existing_a1.user = None
Where above, both
user_id and
user are assigned to, and change events
are emitted for both. The
user association
takes precedence, and None is applied to
user_id, causing a failure.
merge() issues can be examined by first checking -
is the object prematurely in the session ?
>>> a1 = Address(id=existing_a1, user_id=user.id)
>>> assert a1 not in session
>>> a1 = session.merge(a1)
Or is there state on the object that we don't want ? Examining
is a quick way to check:
>>> a1 = Address(id=existing_a1, user_id=user.id)
>>> a1.user
>>> a1.__dict__
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1298d10>,
'user_id': 1,
'id': 1,
'user': None}
>>> # we don't want user=None merged, remove it
>>> del a1.user
>>> a1 = session.merge(a1)
>>> # success
>>> session.commit()
delete() method places an instance
into the Session's list of objects to be marked as deleted:
# mark two objects to be deleted
# commit (or flush)
Deleting from Collections¶
A common confusion that arises regarding
delete() is when
objects which are members of a collection are being deleted. While the
collection member is marked for deletion from the database, this does not
impact the collection itself in memory until the collection is expired.
Below, we illustrate that even after an
Address object is marked
for deletion, it's still present in the collection associated with the
User, even after a flush:
>>> address = user.addresses[1]
>>> session.delete(address)
>>> session.flush()
>>> address in user.addresses
When the above session is committed, all attributes are expired. The next
access of
user.addresses will re-load the collection, revealing the
desired state:
>>> session.commit()
>>> address in user.addresses
The usual practice of deleting items within collections is to forego the usage
delete() directly, and instead use cascade behavior to
automatically invoke the deletion as a result of removing the object from
the parent collection. The
delete-orphan cascade accomplishes this,
as illustrated in the example below:
mapper(User, users_table, properties={
'addresses':relationship(Address, cascade="all, delete, delete-orphan")
del user.addresses[1]
Where above, upon removing the
Address object from the
collection, the
delete-orphan cascade has the effect of marking the
object for deletion in the same way as passing it to
See also Cascades for detail on cascades.
Deleting based on Filter Criterion¶
The caveat with
Session.delete() is that you need to have an object handy
already in order to delete. The Query includes a
delete() method which deletes based on
filtering criteria:
Query.delete() method includes functionality to "expire" objects
already in the session which match the criteria. However it does have some
caveats, including that "delete" and "delete-orphan" cascades won't be fully
expressed for collections which are already loaded. See the API docs for
delete() for more details.
When the
Session is used with its default
configuration, the flush step is nearly always done transparently.
Specifically, the flush occurs before any individual
Query is issued, as well as within the
commit() call before the transaction is
committed. It also occurs before a SAVEPOINT is issued when
begin_nested() is used.
Regardless of the autoflush setting, a flush can always be forced by issuing
The "flush-on-Query" aspect of the behavior can be disabled by constructing
sessionmaker with the flag
Session = sessionmaker(autoflush=False)
Additionally, autoflush can be temporarily disabled by setting the
autoflush flag at any time:
mysession = Session()
mysession.autoflush = False
Some autoflush-disable recipes are available at DisableAutoFlush.
The flush process always occurs within a transaction, even if the
Session has been configured with
autocommit=True, a setting that disables the session's persistent
transactional state. If no transaction is present,
flush() creates its own transaction and
commits it. Any failures during flush will always result in a rollback of
whatever transaction is present. If the Session is not in
mode, an explicit call to
rollback() is
required after a flush fails, even though the underlying transaction will have
been rolled back already - this is so that the overall nesting pattern of
so-called "subtransactions" is consistently maintained.
commit() is used to commit the current
transaction. It always issues
beforehand to flush any remaining state to the database; this is independent
of the "autoflush" setting. If no transaction is present, it raises an error.
Note that the default behavior of the
is that a "transaction" is always present; this behavior can be disabled by
autocommit=True. In autocommit mode, a transaction can be
initiated by calling the
begin() method.
The term "transaction" here refers to a transactional
construct within the
Session itself which may be
maintaining zero or more actual database (DBAPI) transactions. An individual
DBAPI connection begins participation in the "transaction" as it is first
used to execute a SQL statement, then remains present until the session-level
"transaction" is completed. See Managing Transactions for
further detail.
Another behavior of
commit() is that by
default it expires the state of all instances present after the commit is
complete. This is so that when the instances are next accessed, either through
attribute access or by them being present in a
Query result set, they receive the most recent
state. To disable this behavior, configure
sessionmaker with
Normally, instances loaded into the
are never changed by subsequent queries; the assumption is that the current
transaction is isolated so the state most recently loaded is correct as long
as the transaction continues. Setting
autocommit=True works against this
model to some degree since the
behaves in exactly the same way with regard to attribute state, except no
transaction is present.
Rolling Back¶
rollback() rolls back the current
transaction. With a default configured session, the post-rollback state of the
session is as follows:
- All transactions are rolled back and all connections returned to the connection pool, unless the Session was bound directly to a Connection, in which case the connection is still maintained (but still rolled back).
- Objects which were initially in the pending state when they were added to the
Sessionwithin the lifespan of the transaction are expunged, corresponding to their INSERT statement being rolled back. The state of their attributes remains unchanged.
- Objects which were marked as deleted within the lifespan of the transaction are promoted back to the persistent state, corresponding to their DELETE statement being rolled back. Note that if those objects were first pending within the transaction, that operation takes precedence instead.
- All objects not expunged are fully expired.
With that state understood, the
Session may
safely continue usage after a rollback occurs.
When a
flush() fails, typically for
reasons like primary key, foreign key, or "not nullable" constraint
violations, a
rollback() is issued
automatically (it's currently not possible for a flush to continue after a
partial failure). However, the flush process always uses its own transactional
demarcator called a subtransaction, which is described more fully in the
docstrings for
Session. What it means here is
that even though the database transaction has been rolled back, the end user
must still issue
rollback() to fully
reset the state of the
Expunge removes an object from the Session, sending persistent instances to the detached state, and pending instances to the transient state:
To remove all items, call
(this method was formerly known as
close() method issues a
expunge_all(), and releases any
transactional/connection resources. When connections are returned to the
connection pool, transactional state is rolled back as well.
Refreshing / Expiring¶
The Session normally works in the context of an ongoing transaction (with the default setting of autoflush=False). Most databases offer "isolated" transactions - this refers to a series of behaviors that allow the work within a transaction to remain consistent as time passes, regardless of the activities outside of that transaction. A key feature of a high degree of transaction isolation is that emitting the same SELECT statement twice will return the same results as when it was called the first time, even if the data has been modified in another transaction.
For this reason, the
Session gains very efficient behavior by
loading the attributes of each instance only once. Subsequent reads of the
same row in the same transaction are assumed to have the same value. The
user application also gains directly from this assumption, that the transaction
is regarded as a temporary shield against concurrent changes - a good application
will ensure that isolation levels are set appropriately such that this assumption
can be made, given the kind of data being worked with.
To clear out the currently loaded state on an instance, the instance or its individual
attributes can be marked as "expired", which results in a reload to
occur upon next access of any of the instance's attrbutes. The instance
can also be immediately reloaded from the database. The
refresh() methods achieve this:
# immediately re-load attributes on obj1, obj2
# expire objects obj1, obj2, attributes will be reloaded
# on the next access:
When an expired object reloads, all non-deferred column-based attributes are
loaded in one query. Current behavior for expired relationship-based
attributes is that they load individually upon access - this behavior may be
enhanced in a future release. When a refresh is invoked on an object, the
ultimate operation is equivalent to a
Query.get(), so any relationships
configured with eager loading should also load within the scope of the refresh
refresh() and
expire() also support being passed a
list of individual attribute names in which to be refreshed. These names can
refer to any attribute, column-based or relationship based:
# immediately re-load the attributes 'hello', 'world' on obj1, obj2
session.refresh(obj1, ['hello', 'world'])
session.refresh(obj2, ['hello', 'world'])
# expire the attributes 'hello', 'world' objects obj1, obj2, attributes will be reloaded
# on the next access:
session.expire(obj1, ['hello', 'world'])
session.expire(obj2, ['hello', 'world'])
The full contents of the session may be expired at once using
Note that
expire_all() is called automatically whenever
commit() or
rollback() are called. If using the
session in its default mode of autocommit=False and with a well-isolated
transactional environment (which is provided by most backends with the notable
exception of MySQL MyISAM), there is virtually no reason to ever call
expire_all() directly - plenty of state will remain on the
current transaction until it is rolled back or committed or otherwise removed.
refresh() and
expire() similarly are usually
only necessary when an UPDATE or DELETE has been issued manually within the
transaction using
Session Attributes¶
Session itself acts somewhat like a
set-like collection. All items present may be accessed using the iterator
for obj in session:
print obj
And presence may be tested for using regular "contains" semantics:
if obj in session:
print "Object is present"
The session is also keeping track of all newly created (i.e. pending) objects, all objects which have had changes since they were last loaded or saved (i.e. "dirty"), and everything that's been marked as deleted:
# pending objects recently added to the Session
# persistent objects which currently have changes detected
# (this collection is now created on the fly each time the property is called)
# persistent objects that have been marked as deleted via session.delete(obj)
# dictionary of all persistent objects, keyed on their
# identity key
Note that objects within the session are by default weakly referenced. This
means that when they are dereferenced in the outside application, they fall
out of scope from within the
Session as well
and are subject to garbage collection by the Python interpreter. The
exceptions to this include objects which are pending, objects which are marked
as deleted, or persistent objects which have pending changes on them. After a
full flush, these collections are all empty, and all objects are again weakly
referenced. To disable the weak referencing behavior and force all objects
within the session to remain until explicitly expunged, configure
sessionmaker with the
Mappers support the concept of configurable cascade behavior on
relationship() constructs. This refers
to how operations performed on a "parent" object relative to a
Session should be propagated to items
referred to by that relationship (e.g. "child" objects), and is
affected by the
relationship.cascade option.
The default behavior of cascade is limited to cascades of the so-called save-update and merge settings. The typical "alternative" setting for cascade is to add the delete and delete-orphan options; these settings are appropriate for related objects which only exist as long as they are attached to their parent, and are otherwise deleted.
Cascade behavior is configured using the by changing the
cascade option on
class Order(Base):
__tablename__ = 'order'
items = relationship("Item", cascade="all, delete-orphan")
customer = relationship("User", cascade="save-update")
To set cascades on a backref, the same flag can be used with the
backref() function, which ultimately feeds
its arguments back into
class Item(Base):
__tablename__ = 'item'
order = relationship("Order",
backref=backref("items", cascade="all, delete-orphan")
The Origins of Cascade
SQLAlchemy's notion of cascading behavior on relationships, as well as the options to configure them, are primarily derived from the similar feature in the Hibernate ORM; Hibernate refers to "cascade" in a few places such as in Example: Parent/Child. If cascades are confusing, we'll refer to their conclusion, stating "The sections we have just covered can be a bit confusing. However, in practice, it all works out nicely."
The default value of
cascade is
save-update, merge.
The typical alternative setting for this parameter is either
all or more commonly
all, delete-orphan. The
all symbol
is a synonym for
save-update, merge, refresh-expire, expunge, delete,
and using it in conjunction with
delete-orphan indicates that the child
object should follow along with its parent in all cases, and be deleted once
it is no longer associated with that parent.
The list of available values which can be specified for
cascade parameter are described in the following subsections.
save-update cascade indicates that when an object is placed into a
Session via
Session.add(), all the objects associated
with it via this
relationship() should also be added to that
Session. Suppose we have an object
user1 with two
related objects
>>> user1 = User()
>>> address1, address2 = Address(), Address()
>>> user1.addresses = [address1, address2]
If we add
user1 to a
Session, it will also add
address2 implicitly:
>>> sess = Session()
>>> sess.add(user1)
>>> address1 in sess
save-update cascade also affects attribute operations for objects
that are already present in a
Session. If we add a third
address3 to the
user1.addresses collection, it
becomes part of the state of that
>>> address3 = Address()
>>> user1.append(address3)
>>> address3 in sess
>>> True
save-update has the possibly surprising behavior which is that
persistent objects which were removed from a collection
or in some cases a scalar attribute
may also be pulled into the
Session of a parent object; this is
so that the flush process may handle that related object appropriately.
This case can usually only arise if an object is removed from one
and added to another:
>>> user1 = sess1.query(User).filter_by(id=1).first()
>>> address1 = user1.addresses[0]
>>> sess1.close() # user1, address1 no longer associated with sess1
>>> user1.addresses.remove(address1) # address1 no longer associated with user1
>>> sess2 = Session()
>>> sess2.add(user1) # ... but it still gets added to the new session,
>>> address1 in sess2 # because it's still "pending" for flush
save-update cascade is on by default, and is typically taken
for granted; it simplifies code by allowing a single call to
Session.add() to register an entire structure of objects within
Session at once. While it can be disabled, there
is usually not a need to do so.
One case where
save-update cascade does sometimes get in the way is in that
it takes place in both directions for bi-directional relationships, e.g.
backrefs, meaning that the association of a child object with a particular parent
can have the effect of the parent object being implicitly associated with that
child object's
Session; this pattern, as well as how to modify its
behavior using the
cascade_backrefs flag,
is discussed in the section Controlling Cascade on Backrefs.
delete cascade indicates that when a "parent" object
is marked for deletion, its related "child" objects should also be marked
for deletion. If for example we we have a relationship
delete cascade configured:
class User(Base):
# ...
addresses = relationship("Address", cascade="save-update, merge, delete")
If using the above mapping, we have a
User object and two
Address objects:
>>> user1 = sess.query(User).filter_by(id=1).first()
>>> address1, address2 = user1.addresses
If we mark
user1 for deletion, after the flush operation proceeds,
address1 and
address2 will also be deleted:
>>> sess.delete(user1)
>>> sess.commit()
DELETE FROM address WHERE address.id = ?
((1,), (2,))
DELETE FROM user WHERE user.id = ?
Alternatively, if our
User.addresses relationship does not have
delete cascade, SQLAlchemy's default behavior is to instead de-associate
address1 and
address2 from
user1 by setting their foreign key
reference to
NULL. Using a mapping as follows:
class User(Base):
# ...
addresses = relationship("Address")
Upon deletion of a parent
User object, the rows in
address are not
deleted, but are instead de-associated:
>>> sess.delete(user1)
>>> sess.commit()
UPDATE address SET user_id=? WHERE address.id = ?
(None, 1)
UPDATE address SET user_id=? WHERE address.id = ?
(None, 2)
DELETE FROM user WHERE user.id = ?
delete cascade is more often than not used in conjunction with
delete-orphan cascade, which will emit a DELETE for the related
row if the "child" object is deassociated from the parent. The combination
delete and
delete-orphan cascade covers both situations where
SQLAlchemy has to decide between setting a foreign key column to NULL versus
deleting the row entirely.
ORM-level "delete" cascade vs. FOREIGN KEY level "ON DELETE" cascade
The behavior of SQLAlchemy's "delete" cascade has a lot of overlap with the
ON DELETE CASCADE feature of a database foreign key, as well
as with that of the
ON DELETE SET NULL foreign key setting when "delete"
cascade is not specified. Database level "ON DELETE" cascades are specific to the
"FOREIGN KEY" construct of the relational database; SQLAlchemy allows
configuration of these schema-level constructs at the DDL level
using options on
ForeignKeyConstraint which are described
at ON UPDATE and ON DELETE.
It is important to note the differences between the ORM and the relational database's notion of "cascade" as well as how they integrate:
A database level
ON DELETEcascade is configured effectively on the many-to-one side of the relationship; that is, we configure it relative to the
FOREIGN KEYconstraint that is the "many" side of a relationship. At the ORM level, this direction is reversed. SQLAlchemy handles the deletion of "child" objects relative to a "parent" from the "parent" side, which means that
delete-orphancascade are configured on the one-to-many side.
Database level foreign keys with no
ON DELETEsetting are often used to prevent a parent row from being removed, as it would necessarily leave an unhandled related row present. If this behavior is desired in a one-to-many relationship, SQLAlchemy's default behavior of setting a foreign key to
NULLcan be caught in one of two ways:
- The easiest and most common is just to set the
foreign-key-holding column to
NOT NULLat the database schema level. An attempt by SQLAlchemy to set the column to NULL will fail with a simple NOT NULL constraint exception.
- The other, more special case way is to set the
passive_deletesflag to the string
"all". This has the effect of entirely disabling SQLAlchemy's behavior of setting the foreign key column to NULL, and a DELETE will be emitted for the parent row without any affect on the child row, even if the child row is present in memory. This may be desirable in the case when database-level foreign key triggers, either special
ON DELETEsettings or otherwise, need to be activated in all cases when a parent row is deleted.
- The easiest and most common is just to set the foreign-key-holding column to
Database level
ON DELETEcascade is vastly more efficient than that of SQLAlchemy. The database can chain a series of cascade operations across many relationships at once; e.g. if row A is deleted, all the related rows in table B can be deleted, and all the C rows related to each of those B rows, and on and on, all within the scope of a single DELETE statement. SQLAlchemy on the other hand, in order to support the cascading delete operation fully, has to individually load each related collection in order to target all rows that then may have further related collections. That is, SQLAlchemy isn't sophisticated enough to emit a DELETE for all those related rows at once within this context.
SQLAlchemy doesn't need to be this sophisticated, as we instead provide smooth integration with the database's own
ON DELETEfunctionality, by using the
passive_deletesoption in conjunction with properly configured foreign key constraints. Under this behavior, SQLAlchemy only emits DELETE for those rows that are already locally present in the
Session; for any collections that are unloaded, it leaves them to the database to handle, rather than emitting a SELECT for them. The section Using Passive Deletes provides an example of this use.
While database-level
ON DELETEfunctionality works only on the "many" side of a relationship, SQLAlchemy's "delete" cascade has limited ability to operate in the reverse direction as well, meaning it can be configured on the "many" side to delete an object on the "one" side when the reference on the "many" side is deleted. However this can easily result in constraint violations if there are other objects referring to this "one" side from the "many", so it typically is only useful when a relationship is in fact a "one to one". The
single_parentflag should be used to establish an in-Python assertion for this case.
When using a
relationship() that also includes a many-to-many
table using the
secondary option, SQLAlchemy's
delete cascade handles the rows in this many-to-many table automatically.
Just like, as described in Deleting Rows from the Many to Many Table,
the addition or removal of an object from a many-to-many collection
results in the INSERT or DELETE of a row in the many-to-many table,
delete cascade, when activated as the result of a parent object
delete operation, will DELETE not just the row in the "child" table but also
in the many-to-many table.
delete-orphan cascade adds behavior to the
delete cascade,
such that a child object will be marked for deletion when it is
de-associated from the parent, not just when the parent is marked
for deletion. This is a common feature when dealing with a related
object that is "owned" by its parent, with a NOT NULL foreign key,
so that removal of the item from the parent collection results
in its deletion.
delete-orphan cascade implies that each child object can only
have one parent at a time, so is configured in the vast majority of cases
on a one-to-many relationship. Setting it on a many-to-one or
many-to-many relationship is more awkward; for this use case,
SQLAlchemy requires that the
be configured with the
single_parent argument,
establishes Python-side validation that ensures the object
is associated with only one parent at a time.
merge cascade indicates that the
operation should be propagated from a parent that's the subject
of the
Session.merge() call down to referred objects.
This cascade is also on by default.
refresh-expire is an uncommon option, indicating that the
Session.expire() operation should be propagated from a parent
down to referred objects. When using
the referred objects are expired only, but not actually refreshed.
expunge cascade indicates that when the parent object is removed
from the
Session using
Session.expunge(), the
operation should be propagated down to referred objects.
Controlling Cascade on Backrefs¶
The save-update cascade by default takes place on attribute change events emitted from backrefs. This is probably a confusing statement more easily described through demonstration; it means that, given a mapping such as this:
mapper(Order, order_table, properties={
'items' : relationship(Item, backref='order')
If an
Order is already in the session, and is assigned to the
attribute of an
Item, the backref appends the
Order to the
collection of that
Order, resulting in the
save-update cascade taking
>>> o1 = Order()
>>> session.add(o1)
>>> o1 in session
>>> i1 = Item()
>>> i1.order = o1
>>> i1 in o1.items
>>> i1 in session
This behavior can be disabled using the
cascade_backrefs flag:
mapper(Order, order_table, properties={
'items' : relationship(Item, backref='order',
So above, the assignment of
i1.order = o1 will append
i1 to the
collection of
o1, but will not add
i1 to the session. You can, of
i1 to the session at a later point. This
option may be helpful for situations where an object needs to be kept out of a
session until it's construction is completed, but still needs to be given
associations to objects which are already persistent in the target session.
Managing Transactions¶
A newly constructed
Session may be said to be in the "begin" state.
In this state, the
Session has not established any connection or
transactional state with any of the
Engine objects that may be associated
with it.
Session then receives requests to operate upon a database connection.
Typically, this means it is called upon to execute SQL statements using a particular
Engine, which may be via
or within a flush operation of pending data, which occurs when such state exists
Session.commit() or
Session.flush() is called.
As these requests are received, each new
Engine encountered is associated
with an ongoing transactional state maintained by the
When the first
Engine is operated upon, the
Session can be said
to have left the "begin" state and entered "transactional" state. For each
Engine encountered, a
Connection is associated with it,
which is acquired via the
Engine.contextual_connect() method. If a
Connection was directly associated with the
Session (see Joining a Session into an External Transaction
for an example of this), it is
added to the transactional state directly.
For each
Connection, the
Session also maintains a
Transaction object,
which is acquired by calling
Connection.begin() on each
or if the
object has been established using the flag
twophase=True, a
object acquired via
Connection.begin_twophase(). These transactions are all committed or
rolled back corresponding to the invocation of the
Session.commit() and
Session.rollback() methods. A commit operation will
also call the
TwoPhaseTransaction.prepare() method on all transactions if applicable.
When the transactional state is completed after a rollback or commit, the
releases all
Transaction and
Connection resources,
and goes back to the "begin" state, which
will again invoke new
Connection and
Transaction objects as new
requests to emit SQL statements are received.
The example below illustrates this lifecycle:
engine = create_engine("...")
Session = sessionmaker(bind=engine)
# new session. no connections are in use.
session = Session()
# first query. a Connection is acquired
# from the Engine, and a Transaction
# started.
item1 = session.query(Item).get(1)
# second query. the same Connection/Transaction
# are used.
item2 = session.query(Item).get(2)
# pending changes are created.
item1.foo = 'bar'
item2.bar = 'foo'
# commit. The pending changes above
# are flushed via flush(), the Transaction
# is committed, the Connection object closed
# and discarded, the underlying DBAPI connection
# returned to the connection pool.
# on rollback, the same closure of state
# as that of commit proceeds.
Using SAVEPOINT¶
SAVEPOINT transactions, if supported by the underlying engine, may be
delineated using the
Session = sessionmaker()
session = Session()
session.begin_nested() # establish a savepoint
session.rollback() # rolls back u3, keeps u1 and u2
session.commit() # commits u1 and u2
begin_nested() may be called any number
of times, which will issue a new SAVEPOINT with a unique identifier for each
call. For each
begin_nested() call, a
rollback() or
commit() must be issued.
begin_nested() is called, a
flush() is unconditionally issued
(regardless of the
autoflush setting). This is so that when a
rollback() occurs, the full state of the
session is expired, thus causing all subsequent attribute/instance access to
reference the full state of the
Session right
begin_nested() was called.
begin_nested(), in the same manner as the less often
begin() method, returns a transactional object
which also works as a context manager.
It can be succinctly used around individual record inserts in order to catch
things like unique constraint exceptions:
for record in records:
with session.begin_nested():
print "Skipped record %s" % record
Autocommit Mode¶
The example of
Session transaction lifecycle illustrated at
the start of Managing Transactions applies to a
Session configured in the
default mode of
autocommit=False. Constructing a
autocommit=True produces a
Session placed into "autocommit" mode, where each SQL statement
invoked by a
Session.query() or
Session.execute() occurs
using a new connection from the connection pool, discarding it after
results have been iterated. The
Session.flush() operation
still occurs within the scope of a single transaction, though this transaction
is closed out after the
Session.flush() operation completes.
"autocommit" mode should not be considered for general use.
If used, it should always be combined with the usage of
Session.begin() and
Session.commit(), to ensure
a transaction demarcation.
Executing queries outside of a demarcated transaction is a legacy mode of usage, and can in some cases lead to concurrent connection checkouts.
In the absence of a demarcated transaction, the
cannot make appropriate decisions as to when autoflush should
occur nor when auto-expiration should occur, so these features
should be disabled with
autoflush=False, expire_on_commit=False.
Modern usage of "autocommit" is for framework integrations that need to control
specifically when the "begin" state occurs. A session which is configured with
autocommit=True may be placed into the "begin" state using the
Session.begin() method.
After the cycle completes upon
Session.commit() or
connection and transaction resources are released and the
goes back into "autocommit" mode, until
Session.begin() is called again:
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
item1 = session.query(Item).get(1)
item2 = session.query(Item).get(2)
item1.foo = 'bar'
item2.bar = 'foo'
Session.begin() method also returns a transactional token which is
compatible with the Python 2.6
with statement:
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
with session.begin():
item1 = session.query(Item).get(1)
item2 = session.query(Item).get(2)
item1.foo = 'bar'
item2.bar = 'foo'
Using Subtransactions with Autocommit¶
A subtransaction indicates usage of the
Session.begin() method in conjunction with
subtransactions=True flag. This produces a non-transactional, delimiting construct that
allows nesting of calls to
begin() and
Its purpose is to allow the construction of code that can function within a transaction
both independently of any external code that starts a transaction,
as well as within a block that has already demarcated a transaction.
subtransactions=True is generally only useful in conjunction with
autocommit, and is equivalent to the pattern described at Nesting of Transaction Blocks,
where any number of functions can call
Connection.begin() and
as though they are the initiator of the transaction, but in fact may be participating
in an already ongoing transaction:
# method_a starts a transaction and calls method_b
def method_a(session):
session.commit() # transaction is committed here
session.rollback() # rolls back the transaction
# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
session.add(SomeObject('bat', 'lala'))
session.commit() # transaction is not committed yet
session.rollback() # rolls back the transaction, in this case
# the one that was initiated in method_a().
# create a Session and call method_a
session = Session(autocommit=True)
Subtransactions are used by the
Session.flush() process to ensure that the
flush operation takes place within a transaction, regardless of autocommit. When
autocommit is disabled, it is still useful in that it forces the
into a "pending rollback" state, as a failed flush cannot be resumed in mid-operation,
where the end user still maintains the "scope" of the transaction overall.
Enabling Two-Phase Commit¶
For backends which support two-phase operaration (currently MySQL and
PostgreSQL), the session can be instructed to use two-phase commit semantics.
This will coordinate the committing of transactions across databases so that
the transaction is either committed or rolled back in all databases. You can
prepare() the session for
interacting with transactions not managed by SQLAlchemy. To use two phase
transactions set the flag
twophase=True on the session:
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()
# .... work with accounts and users
# commit. session will issue a flush to all DBs, and a prepare step to all DBs,
# before committing both transactions
Embedding SQL Insert/Update Expressions into a Flush¶
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):
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"
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.
Using SQL Expressions with Sessions¶
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
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)
Joining a Session into an External Transaction¶
If a
Connection is being used which is already in a transactional
state (i.e. has a
Transaction established), a
Session can
be made to participate within that transaction by just binding the
Session to that
Connection. The usual rationale for this
is a test suite that allows ORM code to work freely with a
including the ability to call
Session.commit(), where afterwards the
entire database interaction is rolled back:
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from unittest import TestCase
# global application scope. create Session class, engine
Session = sessionmaker()
engine = create_engine('postgresql://...')
class SomeTest(TestCase):
def setUp(self):
# connect to the database
self.connection = engine.connect()
# begin a non-ORM transaction
self.trans = self.connection.begin()
# bind an individual Session to the connection
self.session = Session(bind=self.connection)
def test_something(self):
# use the session in tests.
def tearDown(self):
# rollback - everything that happened with the
# Session above (including calls to commit())
# is rolled back.
# return connection to the Engine
Above, we issue
Session.commit() as well as
Transaction.rollback(). This is an example of where we take advantage
of the
Connection object's ability to maintain subtransactions, or
nested begin/commit-or-rollback pairs where only the outermost begin/commit
pair actually commits the transaction, or if the outermost block rolls back,
everything is rolled back.
Contextual/Thread-local Sessions¶
Recall from the section When do I construct a Session, when do I commit it, and when do I close it?, the concept of
"session scopes" was introduced, with an emphasis on web applications
and the practice of linking the scope of a
Session with that
of a web request. Most modern web frameworks include integration tools
so that the scope of the
Session can be managed automatically,
and these tools should be used as they are available.
SQLAlchemy includes its own helper object, which helps with the establishment
of user-defined
Session scopes. It is also used by third-party
integration systems to help construct their integration schemes.
The object is the
scoped_session object, and it represents a
registry of
Session objects. If you're not familiar with the
registry pattern, a good introduction can be found in Patterns of Enterprise
scoped_session object is a very popular and useful object
used by many SQLAlchemy applications. However, it is important to note
that it presents only one approach to the issue of
management. If you're new to SQLAlchemy, and especially if the
term "thread-local variable" seems strange to you, we recommend that
if possible you familiarize first with an off-the-shelf integration
system such as Flask-SQLAlchemy
or zope.sqlalchemy.
scoped_session is constructed by calling it, passing it a
factory which can create new
Session objects. A factory
is just something that produces a new object when called, and in the
case of
Session, the most common factory is the
introduced earlier in this section. Below we illustrate this usage:
>>> from sqlalchemy.orm import scoped_session
>>> from sqlalchemy.orm import sessionmaker
>>> session_factory = sessionmaker(bind=some_engine)
>>> Session = scoped_session(session_factory)
scoped_session object we've created will now call upon the
sessionmaker when we "call" the registry:
>>> some_session = Session()
some_session is an instance of
Session, which we
can now use to talk to the database. This same
Session is also
present within the
scoped_session registry we've created. If
we call upon the registry a second time, we get back the same
>>> some_other_session = Session()
>>> some_session is some_other_session
This pattern allows disparate sections of the application to call upon a global
scoped_session, so that all those areas may share the same session
without the need to pass it explicitly. The
Session we've established
in our registry will remain, until we explicitly tell our registry to dispose of it,
by calling
>>> Session.remove()
scoped_session.remove() method first calls
Session.close() on
the current
Session, which has the effect of releasing any connection/transactional
resources owned by the
Session first, then discarding the
itself. "Releasing" here means that connections are returned to their connection pool and any transactional state is rolled back, ultimately using the
rollback() method of the underlying DBAPI connection.
At this point, the
scoped_session object is "empty", and will create
a new
Session when called again. As illustrated below, this
is not the same
Session we had before:
>>> new_session = Session()
>>> new_session is some_session
The above series of steps illustrates the idea of the "registry" pattern in a nutshell. With that basic idea in hand, we can discuss some of the details of how this pattern proceeds.
Implicit Method Access¶
The job of the
scoped_session is simple; hold onto a
for all who ask for it. As a means of producing more transparent access to this
Session, the
scoped_session also includes proxy behavior,
meaning that the registry itself can be treated just like a
directly; when methods are called on this object, they are proxied to the
Session being maintained by the registry:
Session = scoped_session(some_factory)
# equivalent to:
# session = Session()
# print session.query(MyClass).all()
print Session.query(MyClass).all()
The above code accomplishes the same task as that of acquiring the current
Session by calling upon the registry, then using that
Thread-Local Scope¶
Users who are familiar with multithreaded programming will note that representing
anything as a global variable is usually a bad idea, as it implies that the
global object will be accessed by many threads concurrently. The
object is entirely designed to be used in a non-concurrent fashion, which
in terms of multithreading means "only in one thread at a time". So our
above example of
scoped_session usage, where the same
object is maintained across multiple calls, suggests that some process needs
to be in place such that mutltiple calls across many threads don't actually get
a handle to the same session. We call this notion thread local storage,
which means, a special object is used that will maintain a distinct object
per each application thread. Python provides this via the
construct. The
scoped_session object by default uses this object
as storage, so that a single
Session is maintained for all who call
upon the
scoped_session registry, but only within the scope of a single
thread. Callers who call upon the registry in a different thread get a
Session instance that is local to that other thread.
Using this technique, the
scoped_session provides a quick and relatively
simple (if one is familiar with thread-local storage) way of providing
a single, global object in an application that is safe to be called upon
from multiple threads.
scoped_session.remove() method, as always, removes the current
Session associated with the thread, if any. However, one advantage of the
threading.local() object is that if the application thread itself ends, the
"storage" for that thread is also garbage collected. So it is in fact "safe" to
use thread local scope with an application that spawns and tears down threads,
without the need to call
scoped_session.remove(). However, the scope
of transactions themselves, i.e. ending them via
Session.commit() or
Session.rollback(), will usually still be something that must be explicitly
arranged for at the appropriate time, unless the application actually ties the
lifespan of a thread to the lifespan of a transaction.
Using Thread-Local Scope with Web Applications¶
As discussed in the section When do I construct a Session, when do I commit it, and when do I close it?, a web application
is architected around the concept of a web request, and integrating
such an application with the
Session usually implies that the
will be associated with that request. As it turns out, most Python web frameworks,
with notable exceptions such as the asynchronous frameworks Twisted and
Tornado, use threads in a simple way, such that a particular web request is received,
processed, and completed within the scope of a single worker thread. When
the request ends, the worker thread is released to a pool of workers where it
is available to handle another request.
This simple correspondence of web request and thread means that to associate a
Session with a thread implies it is also associated with the web request
running within that thread, and vice versa, provided that the
Session is
created only after the web request begins and torn down just before the web request ends.
So it is a common practice to use
scoped_session as a quick way
to integrate the
Session with a web application. The sequence
diagram below illustrates this flow:
Web Server Web Framework SQLAlchemy ORM Code
-------------- -------------- ------------------------------
startup -> Web framework # Session registry is established
initializes Session = scoped_session(sessionmaker())
web request -> web request -> # The registry is *optionally*
starts # called upon explicitly to create
# a Session local to the thread and/or request
# the Session registry can otherwise
# be used at any time, creating the
# request-local Session() if not present,
# or returning the existing one
Session.query(MyClass) # ...
Session.add(some_object) # ...
# if data was modified, commit the
# transaction
web request ends -> # the registry is instructed to
# remove the Session
sends output <-
outgoing web <-
Using the above flow, the process of integrating the
Session with the
web application has exactly two requirements:
- Create a single
scoped_sessionregistry when the web application first starts, ensuring that this object is accessible by the rest of the application.
- Ensure that
scoped_session.remove()is called when the web request ends, usually by integrating with the web framework's event system to establish an "on request end" event.
As noted earlier, the above pattern is just one potential way to integrate a
with a web framework, one which in particular makes the significant assumption
that the web framework associates web requests with application threads. It is
however strongly recommended that the integration tools provided with the web framework
itself be used, if available, instead of
In particular, while using a thread local can be convenient, it is preferable that the
Session be
associated directly with the request, rather than with
the current thread. The next section on custom scopes details a more advanced configuration
which can combine the usage of
scoped_session with direct request based scope, or
any kind of scope.
Using Custom Created Scopes¶
scoped_session object's default behavior of "thread local" scope is only
one of many options on how to "scope" a
Session. A custom scope can be defined
based on any existing system of getting at "the current thing we are working with".
Suppose a web framework defines a library function
get_current_request(). An application
built using this framework can call this function at any time, and the result will be
some kind of
Request object that represents the current request being processed.
If the
Request object is hashable, then this function can be easily integrated with
scoped_session to associate the
Session with the request. Below we illustrate
this in conjunction with a hypothetical event marker provided by the web framework
on_request_end, which allows code to be invoked whenever a request ends:
from my_web_framework import get_current_request, on_request_end
from sqlalchemy.orm import scoped_session, sessionmaker
Session = scoped_session(sessionmaker(bind=some_engine), scopefunc=get_current_request)
def remove_session(req):
Above, we instantiate
scoped_session in the usual way, except that we pass
our request-returning function as the "scopefunc". This instructs
to use this function to generate a dictionary key whenever the registry is called upon
to return the current
Session. In this case it is particularly important
that we ensure a reliable "remove" system is implemented, as this dictionary is not
otherwise self-managed.
Contextual Session API¶
scoped_session(session_factory, scopefunc=None)¶
Provides scoped management of
See Contextual/Thread-local Sessions for a tutorial.
Return the current
Session, creating it using the session factory if not present.
Parameters: **kw¶ – Keyword arguments will be passed to the session factory callable, if an existing
Sessionis not present. If the
Sessionis present and keyword arguments have been passed,
InvalidRequestErroris raised.
__init__(session_factory, scopefunc=None)¶
Construct a new
- session_factory¶ – a factory to create new
Sessioninstances. This is usually, but not necessarily, an instance of
- scopefunc¶ – optional function which defines
the current scope. If not passed, the
scoped_sessionobject assumes "thread-local" scope, and will use a Python
threading.local()in order to maintain the current
Session. If passed, the function should return a hashable token; this token will be used as the key in a dictionary in order to store and retrieve the current
- session_factory¶ – a factory to create new
reconfigure the
sessionmakerused by this
return a class property which produces a
Queryobject against the class and the current
Sessionwhen called.
Session = scoped_session(sessionmaker()) class MyClass(object): query = Session.query_property() # after mappers are defined result = MyClass.query.filter(MyClass.name=='foo').all()
Produces instances of the session's configured query class by default. To override and use a custom implementation, provide a
query_clscallable. The callable will be invoked with the class's mapper as a positional argument and a session keyword argument.
There is no limit to the number of query properties placed on a class.
Dispose of the current
Session, if present.
This will first call
Session.close()method on the current
Session, which releases any existing transactional/connection resources still being held; transactions specifically are rolled back. The
Sessionis then discarded. Upon next usage within the same scope, the
scoped_sessionwill produce a new
ScopedRegistry(createfunc, scopefunc)¶
A Registry that can store one or multiple instances of a single class on the basis of a "scope" function.
The object implements
__call__as the "getter", so by calling
myregistry()the contained object is returned for the current scope.
__init__(createfunc, scopefunc)¶
Construct a new
Clear the current scope, if any.
Return True if an object is present in the current scope.
Set the value for the current scope.
ScopedRegistrythat uses a
threading.local()variable for storage.
Partitioning Strategies¶
Simple Vertical Partitioning¶
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
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.
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:
- Flush operations are delivered to the engine named
- Operations on objects that subclass
MyOtherClassall occur on the
- Read operations for all other classes occur on a random
choice of the
engines = {
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']
return engines[
The above
Session class is plugged in using the
argument to
Session = sessionmaker(class_=RoutingSession)
This approach can be combined with multiple
MetaData objects,
using an approach such as that of using the declarative
keyword, described at __abstract__.
Horizontal Partitioning¶
Horizontal partitioning partitions the rows of a single table (or a set of tables) across multiple databases.
See the "sharding" example: Horizontal Sharding.
Sessions API¶
Session and sessionmaker()¶
sessionmaker(bind=None, class_=<class 'sqlalchemy.orm.session.Session'>, autoflush=True, autocommit=False, expire_on_commit=True, **kw)¶
A configurable
sessionmakerfactory generates new
Sessionobjects when called, creating them given the configurational arguments established here.
# global scope Session = sessionmaker(autoflush=False) # later, in a local scope, create and use a session: sess = Session()
Any keyword arguments sent to the constructor itself will override the "configured" keywords:
Session = sessionmaker() # bind an individual session to a connection sess = Session(bind=connection)
The class also includes a method
configure(), which can be used to specify additional keyword arguments to the factory, which will take effect for subsequent
Sessionobjects generated. This is usually used to associate one or more
Engineobjects with an existing
sessionmakerfactory before it is first used:
# application starts Session = sessionmaker() # ... later engine = create_engine('sqlite:///foo.db') Session.configure(bind=engine) sess = Session()
Produce a new
Sessionobject using the configuration established in this
In Python, the
__call__method is invoked on an object when it is "called" in the same way as a function:
Session = sessionmaker() session = Session() # invokes sessionmaker.__call__()
__init__(bind=None, class_=<class 'sqlalchemy.orm.session.Session'>, autoflush=True, autocommit=False, expire_on_commit=True, **kw)¶
Construct a new
All arguments here except for
class_correspond to arguments accepted by
Sessiondirectly. See the
Session.__init__()docstring for more details on parameters.
- bind¶ – a
Engineor other
Connectablewith which newly created
Sessionobjects will be associated.
- class_¶ – class to use in order to create new
Sessionobjects. Defaults to
- autoflush¶ – The autoflush setting to use with newly created
- autocommit¶ – The autocommit setting to use with newly created
- expire_on_commit=True¶ – the expire_on_commit setting to use
with newly created
- **kw¶ – all other keyword arguments are passed to the constructor
of newly created
- bind¶ – a
- inherited from the
close_all()method of
Close all sessions in memory.
(Re)configure the arguments for this sessionmaker.
Session = sessionmaker() Session.configure(bind=create_engine('sqlite://'))
identity_key(*args, **kwargs)¶
- inherited from the
identity_key()method of
Return an identity key.
This is an alias of
- inherited from the
object_session()method of
Return the
Sessionto which an object belongs.
This is an alias of
Session(bind=None, autoflush=True, expire_on_commit=True, _enable_transaction_accounting=True, autocommit=False, twophase=False, weak_identity_map=True, binds=None, extension=None, query_cls=<class 'sqlalchemy.orm.query.Query'>)¶
Manages persistence operations for ORM-mapped objects.
The Session's usage paradigm is described at Using the Session.
__init__(bind=None, autoflush=True, expire_on_commit=True, _enable_transaction_accounting=True, autocommit=False, twophase=False, weak_identity_map=True, binds=None, extension=None, query_cls=<class 'sqlalchemy.orm.query.Query'>)¶
Construct a new Session.
See also the
sessionmakerfunction which is used to generate a
Session-producing callable with a given set of arguments.
- autocommit¶ –
The autocommit flag is not for general use, and if it is used, queries should only be invoked within the span of a
Session.commit()pair. Executing queries outside of a demarcated transaction is a legacy mode of usage, and can in some cases lead to concurrent connection checkouts.
Defaults to
False. When
True, the
Sessiondoes not keep a persistent transaction running, and will acquire connections from the engine on an as-needed basis, returning them immediately after their use. Flushes will begin and commit (or possibly rollback) their own transaction if no transaction is present. When using this mode, the
Session.begin()method is used to explicitly start transactions.
See also
- autoflush¶ – When
True, all query operations will issue a
flush()call to this
Sessionbefore proceeding. This is a convenience feature so that
flush()need not be called repeatedly in order for database queries to retrieve results. It's typical that
autoflushis used in conjunction with
autocommit=False. In this scenario, explicit calls to
flush()are rarely needed; you usually only need to call
commit()(which flushes) to finalize changes.
- bind¶ – An optional
Connectionto which this
Sessionshould be bound. When specified, all SQL operations performed by this session will execute via this connectable.
- binds¶ –
- An optional dictionary which contains more granular
- "bind" information than the
bindparameter provides. This dictionary can map individual :class`.Table` instances as well as
Mapperinstances to individual
Connectionobjects. Operations which proceed relative to a particular
Mapperwill consult this dictionary for the direct
Mapperinstance as well as the mapper's
mapped_tableattribute in order to locate a connectable to use. The full resolution is described in the
Session.get_bind(). Usage looks like:
Session = sessionmaker(binds={ SomeMappedClass: create_engine('postgresql://engine1'), somemapper: create_engine('postgresql://engine2'), some_table: create_engine('postgresql://engine3'), })
Also see the
- class_¶ – Specify an alternate class other than
sqlalchemy.orm.session.Sessionwhich should be used by the returned class. This is the only argument that is local to the
sessionmakerfunction, and is not sent directly to the constructor for
- _enable_transaction_accounting¶ – Defaults to
True. A legacy-only flag which when
Falsedisables all 0.5-style object accounting on transaction boundaries, including auto-expiry of instances on rollback and commit, maintenance of the "new" and "deleted" lists upon rollback, and autoflush of pending changes upon
begin(), all of which are interdependent.
- expire_on_commit¶ – Defaults to
True. When
True, all instances will be fully expired after each
commit(), so that all attribute/object access subsequent to a completed transaction will load from the most recent database state.
- extension¶ – An optional
SessionExtensioninstance, or a list of such instances, which will receive pre- and post- commit and flush events, as well as a post-rollback event. Deprecated. Please see
- query_cls¶ – Class which should be used to create new Query
objects, as returned by the
query()method. Defaults to
- twophase¶ – When
True, all transactions will be started as a "two phase" transaction, i.e. using the "two phase" semantics of the database in use along with an XID. During a
commit(), after
flush()has been issued for all attached databases, the
prepare()method on each database's
TwoPhaseTransactionwill be called. This allows each database to roll back the entire transaction, before each transaction is committed.
- weak_identity_map¶ – Defaults to
True- when set to
False, objects placed in the
Sessionwill be strongly referenced until explicitly removed or the
Sessionis closed. Deprecated - this option is obsolete.
- autocommit¶ –
add(instance, _warn=True)¶
Place an object in the
Its state will be persisted to the database on the next flush operation.
Repeated calls to
add()will be ignored. The opposite of
Add the given collection of instances to this
begin(subtransactions=False, nested=False)¶
Begin a transaction on this
If this Session is already within a transaction, either a plain transaction or nested transaction, an error is raised, unless
nested=Trueis specified.
subtransactions=Trueflag indicates that this
begin()can create a subtransaction if a transaction is already in progress. For documentation on subtransactions, please see Using Subtransactions with Autocommit.
nestedflag begins a SAVEPOINT transaction and is equivalent to calling
begin_nested(). For documentation on SAVEPOINT transactions, please see Using SAVEPOINT.
Begin a nested transaction on this Session.
The target database(s) must support SQL SAVEPOINTs or a SQLAlchemy-supported vendor implementation of the idea.
For documentation on SAVEPOINT transactions, please see Using SAVEPOINT.
bind_mapper(mapper, bind)¶
Bind operations for a mapper to a Connectable.
- mapper
- A mapper instance or mapped class
- bind
- Any Connectable: a
All subsequent operations involving this mapper will use the given bind.
bind_table(table, bind)¶
Bind operations on a Table to a Connectable.
- table
- bind
- Any Connectable: a
All subsequent operations involving this
Tablewill use the given bind.
Close this Session.
This clears all items and ends any transaction in progress.
If this session were created with
autocommit=False, a new transaction is immediately begun. Note that this new transaction does not use any connection resources until they are first needed.
- inherited from the
close_all()method of
Close all sessions in memory.
Flush pending changes and commit the current transaction.
If no transaction is in progress, this method raises an
By default, the
Sessionalso expires all database loaded state on all ORM-managed attributes after transaction commit. This so that subsequent operations load the most recent data from the database. This behavior can be disabled using the
expire_on_commit=Falseoption to
sessionmakeror the
If a subtransaction is in effect (which occurs when begin() is called multiple times), the subtransaction will be closed, and the next call to
commit()will operate on the enclosing transaction.
When using the
Sessionin its default mode of
autocommit=False, a new transaction will be begun immediately after the commit, but note that the newly begun transaction does not use any connection resources until the first SQL is actually emitted.
See also
connection(mapper=None, clause=None, bind=None, close_with_result=False, **kw)¶
Return a
Connectionobject corresponding to this
Sessionobject's transactional state.
If this
Sessionis configured with
autocommit=False, either the
Connectioncorresponding to the current transaction is returned, or if no transaction is in progress, a new one is begun and the
Connectionreturned (note that no transactional state is established with the DBAPI until the first SQL statement is emitted).
Alternatively, if this
Sessionis configured with
autocommit=True, an ad-hoc
Connectionis returned using
Engine.contextual_connect()on the underlying
Ambiguity in multi-bind or unbound
Sessionobjects can be resolved through any of the optional keyword arguments. This ultimately makes usage of the
get_bind()method for resolution.
- bind¶ – Optional
Engineto 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¶ – Optional
mapper()mapped class, used to identify the appropriate bind. This argument takes precedence over
- clause¶ – A
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
Connectionshould be considered "single use", automatically closing when the first result set is closed. This flag only has an effect if this
Sessionis configured with
autocommit=Trueand does not already have a transaction in progress.
- **kw¶ – Additional keyword arguments are sent to
get_bind(), allowing additional arguments to be passed to custom implementations of
- bind¶ – Optional
Mark an instance as deleted.
The database delete operation occurs upon
The set of all instances marked as 'deleted' within this
The set of all persistent instances considered dirty.
some_mapped_object in session.dirty
Instances are considered dirty when they were modified but not deleted.
Note that this 'dirty' calculation is 'optimistic'; most attribute-setting or collection modification operations will mark an instance as 'dirty' and place it in this set, even if there is no net change to the attribute's value. At flush time, the value of each attribute is compared to its previously saved value, and if there's no net change, no SQL operation will occur (this is a more expensive operation so it's only done at flush time).
To check if an instance has actionable net changes to its attributes, use the
Associate an object with this
Sessionfor related object loading.
enable_relationship_loading()exists to serve special use cases and is not recommended for general use.
Accesses of attributes mapped with
relationship()will attempt to load a value from the database using this
Sessionas the source of connectivity. The values will be loaded based on foreign key values present on this object - it follows that this functionality generally only works for many-to-one-relationships.
The object will be attached to this session, but will not participate in any persistence operations; its state for almost all purposes will remain either "transient" or "detached", except for the case of relationship loading.
Also note that backrefs will often not work as expected. Altering a relationship-bound attribute on the target object may not fire off a backref event, if the effective value is what was already loaded from a foreign-key-holding value.
Session.enable_relationship_loading()method is similar to the
load_on_pendingflag on
relationship(). Unlike that flag,
Session.enable_relationship_loading()allows an object to remain transient while still being able to load related items.
To make a transient object associated with a
Session.enable_relationship_loading()pending, add it to the
Session.enable_relationship_loading()does not improve behavior when the ORM is used normally - object references should be constructed at the object level, not at the foreign key level, so that they are present in an ordinary way before flush() proceeds. This method is not intended for general use.
New in version 0.8.
See also
relationship()- this flag allows per-relationship loading of many-to-ones on items that are pending.
execute(clause, params=None, mapper=None, bind=None, **kw)¶
Execute a SQL expression construct or string statement within the current transaction.
Returns a
ResultProxyrepresenting results of the statement execution, in the same manner as that of an
result = session.execute( user_table.select().where(user_table.c.id == 5) )
execute()accepts any executable clause construct, such as
delete(), and
text(). Plain SQL strings can be passed as well, which in the case of
Session.execute()only will be interpreted the same as if it were passed via a
text()construct. That is, the following usage:
result = session.execute( "SELECT * FROM user WHERE id=:param", {"param":5} )
is equivalent to:
from sqlalchemy import text result = session.execute( text("SELECT * FROM user WHERE id=:param"), {"param":5} )
The second positional argument to
Session.execute()is an optional parameter set. Similar to that of
Connection.execute(), whether this is passed as a single dictionary, or a list of dictionaries, determines whether the DBAPI cursor's
executemany()is used to execute the statement. An INSERT construct may be invoked for a single row:
result = session.execute(users.insert(), {"id": 7, "name": "somename"})
or for multiple rows:
result = session.execute(users.insert(), [ {"id": 7, "name": "somename7"}, {"id": 8, "name": "somename8"}, {"id": 9, "name": "somename9"} ])
The statement is executed within the current transactional context of this
Session. The
Connectionwhich is used to execute the statement can also be acquired directly by calling the
Session.connection()method. Both methods use a rule-based resolution scheme in order to determine the
Connection, which in the average case is derived directly from the "bind" of the
Sessionitself, and in other cases can be based on the
Tableobjects passed to the method; see the documentation for
Session.get_bind()for a full description of this scheme.
Session.execute()method does not invoke autoflush.
ResultProxyreturned by the
Session.execute()method is returned with the "close_with_result" flag set to true; the significance of this flag is that if this
Sessionis autocommitting and does not have a transaction-dedicated
Connectionavailable, a temporary
Connectionis established for the statement execution, which is closed (meaning, returned to the connection pool) when the
ResultProxyhas consumed all available data. This applies only when the
Sessionis configured with autocommit=True and no transaction has been started.
- clause¶ – An executable statement (i.e. an
Executableexpression such as
expression.select()) or string SQL statement to be executed.
- params¶ – Optional dictionary, or list of dictionaries, containing bound parameter values. If a single dictionary, single-row execution occurs; if a list of dictionaries, an "executemany" will be invoked. The keys in each dictionary must correspond to parameter names present in the statement.
- mapper¶ – Optional
mapper()or mapped class, used to identify the appropriate bind. This argument takes precedence over
clausewhen locating a bind. See
Session.get_bind()for more details.
- bind¶ – Optional
Engineto 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
clausewhen locating a bind.
- **kw¶ – Additional keyword arguments are sent to
Session.get_bind()to allow extensibility of "bind" schemes.
See also
SQL Expression Language Tutorial - Tutorial on using Core SQL constructs.
Working with Engines and Connections - Further information on direct statement execution.
Connection.execute()- core level statement execution method, which is
Session.execute()ultimately uses in order to execute the statement.
- clause¶ – An executable statement (i.e. an
expire(instance, attribute_names=None)¶
Expire the attributes on an instance.
Marks the attributes of an instance as out of date. When an expired attribute is next accessed, a query will be issued to the
Sessionobject's current transactional context in order to load all expired attributes for the given instance. Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction.
To expire all objects in the
Sessionsimultaneously, use
Sessionobject's default behavior is to expire all state whenever the
Session.commit()methods are called, so that new state can be loaded for the new transaction. For this reason, calling
Session.expire()only makes sense for the specific case that a non-ORM SQL statement was emitted in the current transaction.
Expires all persistent instances within this Session.
When any attributes on a persistent instance is next accessed, a query will be issued using the
Sessionobject's current transactional context in order to load all expired attributes for the given instance. Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction.
To expire individual objects and individual attributes on those objects, use
Sessionobject's default behavior is to expire all state whenever the
Session.commit()methods are called, so that new state can be loaded for the new transaction. For this reason, calling
Session.expire_all()should not be needed when autocommit is
False, assuming the transaction is isolated.
Remove the instance from this
This will free all internal references to the instance. Cascading will be applied according to the expunge cascade rule.
Remove all object instances from this
This is equivalent to calling
expunge(obj)on all objects in this
Flush all the object changes to the database.
Writes out all pending object creations, deletions and modifications to the database as INSERTs, DELETEs, UPDATEs, etc. Operations are automatically ordered by the Session's unit of work dependency solver.
Database operations will be issued in the current transactional context and do not affect the state of the transaction, unless an error occurs, in which case the entire transaction is rolled back. You may flush() as often as you like within a transaction to move changes from Python to the database's transaction buffer.
autocommitSessions with no active manual transaction, flush() will create a transaction on the fly that surrounds the entire set of operations into the flush.
Parameters: objects¶ –
Optional; restricts the flush operation to operate only on elements that are in the given collection.
This feature is for an extremely narrow set of use cases where particular objects may need to be operated upon before the full flush() occurs. It is not intended for general use.
get_bind(mapper=None, clause=None)¶
Return a "bind" to which this
Sessionis bound.
The "bind" is usually an instance of
Engine, except in the case where the
Sessionhas been explicitly bound directly to a
For a multiply-bound or unbound
Session, the
clausearguments 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:
- if mapper given and session.binds is present, locate a bind based on mapper.
- if clause given and session.binds is present,
locate a bind based on
Tableobjects found in the given clause present in session.binds.
- if session.bind is present, return that.
- if clause given, attempt to return a bind
linked to the
MetaDataultimately associated with the clause.
- if mapper given, attempt to return a bind
linked to the
MetaDataultimately associated with the
Tableor other selectable to which the mapper is mapped.
- No bind can be found,
UnboundExecutionErroris raised.
- mapper¶ – Optional
mapper()mapped class or instance of
Mapper. The bind can be derived from a
Mapperfirst by consulting the "binds" map associated with this
Session, and secondly by consulting the
MetaDataassociated with the
Tableto which the
Mapperis mapped for a bind.
- clause¶ – A
text(), etc.). If the
mapperargument is not present or could not produce a bind, the given expression construct will be searched for a bound element, typically a
Tableassociated with bound
identity_key(*args, **kwargs)¶
- inherited from the
identity_key()method of
Return an identity key.
This is an alias of
identity_map= None¶
A mapping of object identities to objects themselves.
Iterating through
Session.identity_map.values()provides access to the full set of persistent objects (i.e., those that have row identity) currently in the session.
See also
identity_key()- helper function to produce the keys used in this dictionary.
True if this
Sessionis in "transaction mode" and is not in "partial rollback" state.
Sessionin its default mode of
autocommit=Falseis essentially always in "transaction mode", in that a
SessionTransactionis associated with it as soon as it is instantiated. This
SessionTransactionis immediately replaced with a new one as soon as it is ended, due to a rollback, commit, or close operation.
"Transaction mode" does not indicate whether or not actual database connection resources are in use; the
SessionTransactionobject coordinates among zero or more actual database transactions, and starts out with none, accumulating individual DBAPI connections as different data sources are used within its scope. The best way to track when a particular
Sessionhas actually begun to use DBAPI resources is to implement a listener using the
SessionEvents.after_begin()method, which will deliver both the
Sessionas well as the target
Connectionto a user-defined event listener.
The "partial rollback" state refers to when an "inner" transaction, typically used during a flush, encounters an error and emits a rollback of the DBAPI connection. At this point, the
Sessionis in "partial rollback" and awaits for the user to call
Session.rollback(), in order to close out the transaction stack. It is in this "partial rollback" period that the
is_activeflag returns False. After the call to
Session.rollback(), the
SessionTransactionis replaced with a new one and
When a
Sessionis used in
autocommit=Truemode, the
SessionTransactionis only instantiated within the scope of a flush call, or when
Session.begin()is called. So
is_activewill always be
Falseoutside of a flush or
Session.begin()block in this mode, and will be
Truewithin the
Session.begin()block as long as it doesn't enter "partial rollback" state.
From all the above, it follows that the only purpose to this flag is for application frameworks that wish to detect is a "rollback" is necessary within a generic error handling routine, for
Sessionobjects that would otherwise be in "partial rollback" mode. In a typical integration case, this is also not necessary as it is standard practice to emit
Session.rollback()unconditionally within the outermost exception catch.
To track the transactional state of a
Sessionfully, use event listeners, primarily the
SessionEvents.after_rollback()and related events.
is_modified(instance, include_collections=True, passive=True)¶
Trueif the given instance has locally modified attributes.
This method retrieves the history for each instrumented attribute on the instance and performs a comparison of the current value to its previously committed value, if any.
It is in effect a more expensive and accurate version of checking for the given instance in the
Session.dirtycollection; a full test for each attribute's net "dirty" status is performed.
return session.is_modified(someobject)
Changed in version 0.8: When using SQLAlchemy 0.7 and earlier, the
passiveflag should always be explicitly set to
True, else SQL loads/autoflushes may proceed which can affect the modified state itself:
session.is_modified(someobject, passive=True). In 0.8 and above, the behavior is corrected and this flag is ignored.
A few caveats to this method apply:
Instances present in the
Session.dirtycollection may report
Falsewhen tested with this method. This is because the object may have received change events via attribute mutation, thus placing it in
Session.dirty, but ultimately the state is the same as that loaded from the database, resulting in no net change here.
Scalar attributes may not have recorded the previously set value when a new value was applied, if the attribute was not loaded, or was expired, at the time the new value was received - in these cases, the attribute is assumed to have a change, even if there is ultimately no net change against its database value. SQLAlchemy in most cases does not need the "old" value when a set event occurs, so it skips the expense of a SQL call if the old value isn't present, based on the assumption that an UPDATE of the scalar value is usually needed, and in those few cases where it isn't, is less expensive on average than issuing a defensive SELECT.
The "old" value is fetched unconditionally upon set only if the attribute container has the
active_historyflag set to
True. This flag is set typically for primary key attributes and scalar object references that are not a simple many-to-one. To set this flag for any arbitrary mapped column, use the
active_historyargument with
- instance¶ – mapped instance to be tested for pending changes.
- include_collections¶ – Indicates if multivalued collections
should be included in the operation. Setting this to
Falseis a way to detect only local-column based properties (i.e. scalar columns or many-to-one foreign keys) that would result in an UPDATE for this instance upon flush.
- passive¶ –
Changed in version 0.8: Ignored for backwards compatibility. When using SQLAlchemy 0.7 and earlier, this flag should always be set to
merge(instance, load=True)¶
Copy the state of a given instance into a corresponding instance within this
Session.merge()examines the primary key attributes of the source instance, and attempts to reconcile it with an instance of the same primary key in the session. If not found locally, it attempts to load the object from the database based on primary key, and if none can be located, creates a new instance. The state of each attribute on the source instance is then copied to the target instance. The resulting target instance is then returned by the method; the original source instance is left unmodified, and un-associated with the
Sessionif not already.
This operation cascades to associated instances if the association is mapped with
See Merging for a detailed discussion of merging.
- instance¶ – Instance to be merged.
- load¶ –
Boolean, when False,
merge()switches into a "high performance" mode which causes it to forego emitting history events as well as all database access. This flag is used for cases such as transferring graphs of objects into a
Sessionfrom a second level cache, or to transfer just-loaded objects into the
Sessionowned by a worker thread or process without re-querying the database.
load=Falseuse case adds the caveat that the given object has to be in a "clean" state, that is, has no pending changes to be flushed - even if the incoming object is detached from any
Session. This is so that when the merge operation populates local attributes and cascades to related objects and collections, the values can be "stamped" onto the target object as is, without generating any history or attribute events, and without the need to reconcile the incoming data with any existing related objects or collections that might not be loaded. The resulting objects from
load=Falseare always produced as "clean", so it is only appropriate that the given objects should be "clean" as well, else this suggests a mis-use of the method.
The set of all instances marked as 'new' within this
Return a context manager that disables autoflush.
with session.no_autoflush: some_object = SomeClass() session.add(some_object) # won't autoflush some_object.related_thing = session.query(SomeRelated).first()
Operations that proceed within the
with:block will not be subject to flushes occurring upon query access. This is useful when initializing a series of objects which involve existing database queries, where the uncompleted object should not yet be flushed.
New in version 0.7.6.
- inherited from the
object_session()method of
Return the
Sessionto which an object belongs.
This is an alias of
Prepare the current transaction in progress for two phase commit.
If no transaction is in progress, this method raises an
Only root transactions of two phase sessions can be prepared. If the current transaction is not such, an
InvalidRequestErroris raised.
Remove unreferenced instances cached in the identity map.
Deprecated since version 0.7: The non-weak-referencing identity map feature is no longer needed.
Note that this method is only meaningful if "weak_identity_map" is set to False. The default weak identity map is self-pruning.
Removes any object in this Session's identity map that is not referenced in user code, modified, new or scheduled for deletion. Returns the number of objects pruned.
refresh(instance, attribute_names=None, lockmode=None)¶
Expire and refresh the attributes on the given instance.
A query will be issued to the database and all attributes will be refreshed with their current database value.
Lazy-loaded relational attributes will remain lazily loaded, so that the instance-wide refresh operation will be followed immediately by the lazy load of that attribute.
Eagerly-loaded relational attributes will eagerly load within the single refresh operation.
Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction - usage of
refresh()usually only makes sense if non-ORM SQL statement were emitted in the ongoing transaction, or if autocommit mode is turned on.
- attribute_names¶ – optional. An iterable collection of string attribute names indicating a subset of attributes to be refreshed.
- lockmode¶ – Passed to the
Queryas used by
Rollback the current transaction in progress.
If no transaction is in progress, this method is a pass-through.
This method rolls back the current transaction or nested transaction regardless of subtransactions being in effect. All subtransactions up to the first real transaction are closed. Subtransactions occur when
begin()is called multiple times.
See also
scalar(clause, params=None, mapper=None, bind=None, **kw)¶
execute()but return a scalar result.
transaction= None¶
The current active or inactive
SessionTransaction(session, parent=None, nested=False)¶
Session-level transaction.
SessionTransactionis a mostly behind-the-scenes object not normally referenced directly by application code. It coordinates among multiple
Connectionobjects, maintaining a database transaction for each one individually, committing or rolling them back all at once. It also provides optional two-phase commit behavior which can augment this coordination operation.
Session.transactionattribute of
Sessionrefers to the current
SessionTransactionobject in use, if any.
SessionTransactionis associated with a
Sessionin its default mode of
autocommit=Falseimmediately, associated with no database connections. As the
Sessionis called upon to emit SQL on behalf of various
Connectionobjects, a corresponding
Connectionand associated
Transactionis added to a collection within the
SessionTransactionobject, becoming one of the connection/transaction pairs maintained by the
The lifespan of the
SessionTransactionends when the
Session.close()methods are called. At this point, the
SessionTransactionremoves its association with its parent
Session. A
Sessionthat is in
autocommit=Falsemode will create a new
SessionTransactionto replace it immediately, whereas a
Sessionthat's in
autocommit=Truemode will remain without a
SessionTransactionuntil the
Session.begin()method is called.
Another detail of
SessionTransactionbehavior is that it is capable of "nesting". This means that the
Session.begin()method can be called while an existing
SessionTransactionis already present, producing a new
SessionTransactionthat temporarily replaces the parent
SessionTransaction. When a
SessionTransactionis produced as nested, it assigns itself to the
Session.transactionattribute. When it is ended via
Session.rollback(), it restores its parent
SessionTransactionback onto the
Session.transactionattribute. The behavior is effectively a stack, where
Session.transactionrefers to the current head of the stack.
The purpose of this stack is to allow nesting of
Session.commit()calls in context with various flavors of
Session.begin(). This nesting behavior applies to when
Session.begin_nested()is used to emit a SAVEPOINT transaction, and is also used to produce a so-called "subtransaction" which allows a block of code to use a begin/rollback/commit sequence regardless of whether or not its enclosing code block has begun a transaction. The
flush()method, whether called explicitly or via autoflush, is the primary consumer of the "subtransaction" feature, in that it wishes to guarantee that it works within in a transaction block regardless of whether or not the
Sessionis in transactional mode when the method is called.
See also:
Session Utilites¶
Make the given instance 'transient'.
This will remove its association with any session and additionally will remove its "identity key", such that it's as though the object were newly constructed, except retaining its values. It also resets the "deleted" flag on the state if this object had been explicitly deleted by its session.
Attributes which were "expired" or deferred at the instance level are reverted to undefined, and will not trigger any loads.
Return the
Sessionto which instance belongs.
If the instance is not a mapped instance, an error is raised.
Attribute and State Management Utilities¶
These functions are provided by the SQLAlchemy attribute instrumentation API to provide a detailed interface for dealing with instances, attribute values, and history. Some of them are useful when constructing event listener functions, such as those described in ORM Events.
Given an object, return the
InstanceStateassociated with the object.
sqlalchemy.orm.exc.UnmappedInstanceErrorif no mapping is configured.
Equivalent functionality is available via the
inspect()function as:
Using the inspection system will raise
sqlalchemy.exc.NoInspectionAvailableif the instance is not part of a mapping.
del_attribute(instance, key)¶
Delete the value of an attribute, firing history events.
This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required. Custom attribute management schemes will need to make usage of this method to establish attribute state as understood by SQLAlchemy.
get_attribute(instance, key)¶
Get the value of an attribute, firing any callables required.
This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required. Custom attribute management schemes will need to make usage of this method to make usage of attribute state as understood by SQLAlchemy.
get_history(obj, key, passive=<symbol 'PASSIVE_OFF>)¶
Return a
Historyrecord for the given object and attribute key.
- obj¶ – an object whose class is instrumented by the attributes package.
- key¶ – string attribute name.
- passive¶ – indicates loading behavior for the attribute
if the value is not already present. This is a
bitflag attribute, which defaults to the symbol
PASSIVE_OFFindicating all necessary SQL should be emitted.
init_collection(obj, key)¶
Initialize a collection attribute and return the collection adapter.
This function is used to provide direct access to collection internals for a previously unloaded attribute. e.g.:
collection_adapter = init_collection(someobject, 'elements') for elem in values: collection_adapter.append_without_event(elem)
For an easier way to do the above, see
obj is an instrumented object instance. An InstanceState is accepted directly for backwards compatibility but this usage is deprecated.
flag_modified(instance, key)¶
Mark an attribute on an instance as 'modified'.
This sets the 'modified' flag on the instance and establishes an unconditional change event for the given attribute.
Return the
InstanceStatefor a given mapped object.
This function is the internal version of
object_state(). The
object_state()and/or the
inspect()function is preferred here as they each emit an informative exception if the given object is not mapped.
is_instrumented(instance, key)¶
Return True if the given attribute on the given instance is instrumented by the attributes package.
This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required.
set_attribute(instance, key, value)¶
Set the value of an attribute, firing history events.
This function may be used regardless of instrumentation applied directly to the class, i.e. no descriptors are required. Custom attribute management schemes will need to make usage of this method to establish attribute state as understood by SQLAlchemy.
set_committed_value(instance, key, value)¶
Set the value of an attribute with no history events.
Cancels any previous history present. The value should be a scalar value for scalar-holding attributes, or an iterable for any collection-holding attribute.
This is the same underlying method used when a lazy loader fires off and loads additional data from the database. In particular, this method can be used by application code which has loaded additional attributes or collections through separate queries, which can then be attached to an instance as though it were part of its original loaded state.
A 3-tuple of added, unchanged and deleted values, representing the changes which have occurred on an instrumented attribute.
The easiest way to get a
Historyobject for a particular attribute on an object is to use the
from sqlalchemy import inspect hist = inspect(myobject).attrs.myattribute.history
Each tuple member is an iterable sequence:
added- the collection of items added to the attribute (the first tuple element).
unchanged- the collection of items that have not changed on the attribute (the second tuple element).
deleted- the collection of items that have been removed from the attribute (the third tuple element).
Return a collection of unchanged + deleted.
Return a collection of added + unchanged.
Return a collection of added + unchanged + deleted.