Release: 0.7.10 | Release Date: February 7, 2013

SQLAlchemy 0.7 Documentation

Using the Session

The 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 Session.

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

The 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 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() function is normally used to create a top level Session 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 Engine, so that each Session generated will use this Engine 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 Session instances, keeping the configuration for how Session objects are constructed in one place.

The 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 Session Frequently Asked Questions.

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 Engine 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 Session is constructed against a specific Connection:

# 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).

Session Frequently Asked Questions

  • When do I make a sessionmaker() ?

    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 file; from that point on your other modules say “from mypackage import Session”. That way, everyone else just uses Session(), 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 sessionmaker.configure().

    In the examples in this section, we will frequently show the sessionmaker() being created right above the line where we actually invoke Session. But that’s just for example’s sake! In reality, the sessionmaker() would be somewhere at the module level. The calls to instantiate Session 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 ?

    A Session is typically constructed at the beginning of a logical operation where database access is potentially anticipated.

    The 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 of False, this transaction remains in progress until the Session 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 Session 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 scope.

    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 Session 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 Session is torn down as well, usually through the usage of event hooks provided by the web framework. The transaction used by the Session 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 Session unconditionally at the end.

    Most web frameworks include infrastructure to establish a single Session, associated with the request, which is correctly constructed and torn down corresponding torn down at the end of a request. Such infrastructure pieces include products such as Flask-SQLAlchemy, for usage in conjunction with the Flask web framework, and Zope-SQLAlchemy, for usage in conjunction with the Pyramid and Zope frameworks. SQLAlchemy strongly recommends that these products be used as available.

    In those situations where integration libraries are not available, SQLAlchemy includes its own “helper” class known as ScopedSession. 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 scopes.

    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 Session local to each child process work with that Session through 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 Session that 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 Session may 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.
  • Is the Session a cache ? 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 Foo(name='bar') 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.

    The 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 Beaker, via the Beaker Caching example.

  • How can I get the Session for a certain object ?

    Use the object_session() classmethod available on Session:

    session = Session.object_session(someobject)
  • Is the session thread-safe?

    The Session is very much intended to be used in a non-concurrent fashion, which usually means in only one thread at a time.

    The 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 a 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.

    If there are in fact multiple threads participating in the same task, then you may consider sharing the session between those threads, though this would be an extremely unusual scenario. In this case it would be necessary to implement a proper locking scheme so that the Session is still not exposed to concurrent access.


The 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.fullname).all()

# query from a mapper
user_mapper = class_mapper(User)

When 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 default-configured 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.

The Query object is introduced in great detail in Object Relational Tutorial, and further documented in Querying.

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 add_all():

session.add_all([item1, item2, item3])

The add() operation cascades along the save-update cascade. For more details see the section Cascades.


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=True flag 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=True flag 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 load is 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 merge cascade (see Cascades).

  • The new instance is returned.

With merge(), the given “source” instance is not modifed nor is it associated with the target Session, and remains available to be merged with any number of other Session objects. 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 merged in again, and the Session will 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 Session objects simultaneously. merge() is used each time an object is retrieved from the cache to create a local copy of it in each Session which requests it. The cached object remains detached; only its state is moved into copies of itself that are local to individual Session objects.

    In the caching use case, it’s common that the load=False flag is used to remove the overhead of reconciling the object’s state with the database. There’s also a “bulk” version of merge() called merge_result() that was designed to work with cache-extended Query objects - see the section Beaker Caching.

  • An application wants to transfer the state of a series of objects into a Session maintained 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=False flag 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 transferrence 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 merge().

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(''), nullable=False)

Assume a User object with one Address, already persistent:

>>> u1 = User(name='ed', addresses=[Address(email_address='')])
>>> 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 Address:

>>> existing_a1 = u1.addresses[0]
>>> a1 = Address(

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 of a1.user to a persistent object cascaded to the backref of User.addresses and made our a1 object pending, as though we had added it. Now we have two 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 subsequent merge() operation essentially does nothing. Cascade can be configured via the cascade 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 session.

The cascade_backrefs=False option of relationship() 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(,
>>> 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 of 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.user_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.

Most merge() issues can be examined by first checking - is the object prematurely in the session ?

>>> a1 = Address(id=existing_a1,
>>> assert a1 not in session
>>> a1 = session.merge(a1)

Or is there state on the object that we don’t want ? Examining __dict__ is a quick way to check:

>>> a1 = Address(id=existing_a1,
>>> 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()


The 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 parent 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 of 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 User.addresses collection, the delete-orphan cascade has the effect of marking the Address object for deletion in the same way as passing it to delete().

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:


The 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 flush():


The “flush-on-Query” aspect of the behavior can be disabled by constructing sessionmaker() with the flag autoflush=False:

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 autocommit=True 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 flush() 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 Session is that a “transaction” is always present; this behavior can be disabled by setting 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 expire_on_commit=False.

Normally, instances loaded into the Session 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 Session 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 Session within 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 Session.


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 expunge_all() (this method was formerly known as clear()).


The 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 expire() and 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 operation.

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 expire_all():


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.execute().

Session Attributes

The Session itself acts somewhat like a set-like collection. All items present may be accessed using the iterator interface:

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

(Documentation:, Session.dirty, Session.deleted, Session.identity_map).

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 weak_identity_map=False setting.


Mappers support the concept of configurable cascade behavior on relationship() constructs. This refers to how operations performed on a parent object relative to a particular Session should be propagated to items referred to by that relationship. The default cascade behavior is usually suitable for most situations, and the option is normally invoked explicitly in order to enable delete and delete-orphan cascades, which refer to how the relationship should be treated when the parent is marked for deletion as well as when a child is de-associated from its parent.

Cascade behavior is configured by setting the cascade keyword argument on relationship():

class Order(Base):
    __tablename__ = 'order'

    items = relationship("Item", cascade="all, delete-orphan")
    customer = relationship("User", secondary=user_orders_table,

To set cascades on a backref, the same flag can be used with the backref() function, which ultimately feeds its arguments back into relationship():

class Item(Base):
    __tablename__ = 'item'

    order = relationship("Order",
                    backref=backref("items", cascade="all, delete-orphan")

The default value of cascade is save-update, merge. The all symbol in the cascade options indicates that all cascade flags should be enabled, with the exception of delete-orphan. Typically, cascade is usually left at its default, or configured as all, delete-orphan, indicating the child objects should be treated as “owned” by the parent.

The list of available values which can be specified in cascade are as follows:

  • save-update - 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 same Session. Additionally, if this object is already present in a Session, child objects will be added to that session as they are associated with this parent, i.e. as they are appended to lists, added to sets, or otherwise associated with the parent.

    save-update cascade also cascades the pending history of the target attribute, meaning that objects which were removed from a scalar or collection attribute whose changes have not yet been flushed are also placed into the target session. This is because they may have foreign key attributes present which will need to be updated to no longer refer to the parent.

    The save-update cascade is on by default, and it’s common to not even be aware of it. It’s customary that only a single call to Session.add() against the lead object of a structure has the effect of placing the full structure of objects into the Session at once.

    However, it can be turned off, which would imply that objects associated with a parent would need to be placed individually using Session.add() calls for each one.

    Another default behavior of save-update cascade is that it will take effect in the reverse direction, that is, associating a child with a parent when a backref is present means both relationships are affected; the parent will be added to the child’s session. To disable this somewhat indirect session addition, use the cascade_backrefs=False option described below in Controlling Cascade on Backrefs.

  • delete - This cascade indicates that when the parent object is marked for deletion, the related objects should also be marked for deletion. Without this cascade present, SQLAlchemy will set the foreign key on a one-to-many relationship to NULL when the parent object is deleted. When enabled, the row is instead deleted.

    delete cascade is often used in conjunction with delete-orphan cascade, as is appropriate for an object whose foreign key is not intended to be nullable. On some backends, it’s also a good idea to set ON DELETE on the foreign key itself; see the section Using Passive Deletes for more details.

    Note that for many-to-many relationships which make usage of the secondary argument to relationship(), SQLAlchemy always emits a DELETE for the association row in between “parent” and “child”, when the parent is deleted or whenever the linkage between a particular parent and child is broken.

  • delete-orphan - This 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 relationship() be configured with the single_parent=True function, which establishes Python-side validation that ensures the object is associated with only one parent at a time.

  • merge - This cascade indicates that the Session.merge() 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 - A less common option, indicates that the Session.expire() operation should be propagated from a parent down to referred objects. When using Session.refresh(), the referred objects are expired only, but not actually refreshed.

  • expunge - Indicate 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 takes place on backrefs by default. This 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 order attribute of an Item, the backref appends the Order to the items collection of that Order, resulting in the save-update cascade taking place:

>>> 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 items collection of o1, but will not add i1 to the session. You can, of course, add() 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.

The 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 Session.query(), Session.execute(), or within a flush operation of pending data, which occurs when such state exists and 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 Session. 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 Connection, or if the Session object has been established using the flag twophase=True, a TwoPhaseTransaction 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 Session 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. = '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.


SAVEPOINT transactions, if supported by the underlying engine, may be delineated using the begin_nested() method:

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 corresponding rollback() or commit() must be issued.

When 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 before begin_nested() was called.

begin_nested(), in the same manner as the less often used 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 Session with 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. While very old versions of SQLAlchemy standardized on this mode, the modern Session benefits highly from being given a clear point of transaction demarcation via Session.rollback() and Session.commit(). The autoflush action can safely emit SQL to the database as needed without implicitly producing permanent effects, the contents of attributes are expired only when a logical series of steps has completed. If the Session were to be used in pure “autocommit” mode without an ongoing transaction, these features should be disabled, that is, 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 Session.rollback(), connection and transaction resources are released and the Session 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) = 'bar' = 'foo'

The 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) = 'bar' = 'foo'

Using Subtransactions with Autocommit

A subtransaction indicates usage of the Session.begin() method in conjunction with the subtransactions=True flag. This produces a a non-transactional, delimiting construct that allows nesting of calls to begin() and commit(). It’s 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 Transaction.commit() 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 Session 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 also 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 Connection:

Session = sessionmaker(bind=engine)
session = Session()

# execute a string statement
result = session.execute("select * from table where id=:id", {'id':7})

# execute a SQL expression construct
result = session.execute(select([mytable]).where(

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],, 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 Session, 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 = 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 Session Frequently Asked Questions, 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 ScopedSession 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 Architecture.


The ScopedSession 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 Session 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.

A ScopedSession is constructed by calling the scoped_session() function, 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 sessionmaker(), 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)

The ScopedSession object we’ve created will now call upon the sessionmaker() when we “call” the registry:

>>> some_session = Session()

Above, 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 ScopedSession registry we’ve created. If we call upon the registry a second time, we get back the same Session:

>>> some_other_session = Session()
>>> some_session is some_other_session

This pattern allows disparate sections of the application to call upon a global ScopedSession, 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 regsitry to dispose of it, by calling ScopedSession.remove():

>>> Session.remove()

The ScopedSession.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 Session itself. “Releasing” here means that any pending transaction will be rolled back using connection.rollback().

At this point, the ScopedSession 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 ScopedSession is simple; hold onto a Session for all who ask for it. As a means of producing more transparent access to this Session, the ScopedSession also includes proxy behavior, meaning that the registry itself can be treated just like a Session directly; when methods are called on this object, they are proxied to the underlying 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 Session.

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 Session 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 ScopedSession usage, where the same Session 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 threading.local() construct. The ScopedSession object by default uses this object as storage, so that a single Session is maintained for all who call upon the ScopedSession 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 ScopedSession 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.

The ScopedSession.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 ScopedSession.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 Session Frequently Asked Questions, a web application is architected around the concept of a web request, and integrating such an application with the Session usually implies that the Session 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 ScopedSession 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:

  1. Create a single ScopedSession registry when the web application first starts, ensuring that this object is accessible by the rest of the application.
  2. Ensure that ScopedSession.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 Session 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 ScopedSession.

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 ScopedSession with direct request based scope, or any kind of scope.

Using Custom Created Scopes

The ScopedSession 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 ScopedSession 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 ScopedSession in the usual way, except that we pass our request-returning function as the “scopefunc”. This instructs ScopedSession 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

sqlalchemy.orm.scoped_session(session_factory, scopefunc=None)

Provides thread-local or scoped management of Session objects.

This is a front-end function to ScopedSession:

Session = scoped_session(sessionmaker(autoflush=True))

To instantiate a Session object which is part of the scoped context, instantiate normally:

session = Session()

Most session methods are available as classmethods from the scoped session:


See also: Contextual/Thread-local Sessions.


a ScopedSession instance

class sqlalchemy.orm.scoping.ScopedSession(session_factory, scopefunc=None)

Provides thread-local management of Sessions.

Typical invocation is via the scoped_session() function:

Session = scoped_session(sessionmaker())

The internal registry is accessible, and by default is an instance of ThreadLocalRegistry.

See also: Contextual/Thread-local Sessions.


reconfigure the sessionmaker used by this ScopedSession.


return a class property which produces a Query object against the class when called.


Session = scoped_session(sessionmaker())

class MyClass(object):
    query = Session.query_property()

# after mappers are defined
result = MyClass.query.filter('foo').all()

Produces instances of the session’s configured query class by default. To override and use a custom implementation, provide a query_cls callable. 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 contextual session.

class sqlalchemy.util.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.

  • createfunc – a callable that returns a new object to be placed in the registry
  • scopefunc – a callable that will return a key to store/retrieve an object.
__init__(createfunc, scopefunc)

Construct a new ScopedRegistry.

  • createfunc – A creation function that will generate a new value for the current scope, if none is present.
  • scopefunc – A function that returns a hashable token representing the current scope (such as, current thread identifier).

Clear the current scope, if any.


Return True if an object is present in the current scope.


Set the value forthe current scope.

class sqlalchemy.util.ThreadLocalRegistry(createfunc)

A ScopedRegistry that 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 Engine linked to that class. Upon a flush operation, similar rules take place to ensure each class is written to the right database.

The transactions among the multiple databases can optionally be coordinated via two phase commit, if the underlying backend supports it. See Enabling Two-Phase Commit for an example.

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:

  1. Flush operations are delivered to the engine named master.
  2. Operations on objects that subclass MyOtherClass all occur on the other engine.
  3. Read operations for all other classes occur on a random choice of the slave1 or slave2 database.
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 class_ argument to sessionmaker():

Session = sessionmaker(class_=RoutingSession)

This approach can be combined with multiple MetaData objects, using an approach such as that of using the declarative __abstract__ keyword, described at __abstract__.

Horizontal Partitioning

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()

sqlalchemy.orm.session.sessionmaker(bind=None, class_=None, autoflush=True, autocommit=False, expire_on_commit=True, **kwargs)

Generate a custom-configured Session class.

The returned object is a subclass of Session, which, when instantiated with no arguments, uses the keyword arguments configured here as its constructor arguments.

It is intended that the sessionmaker() function be called within the global scope of an application, and the returned class be made available to the rest of the application as the single class used to instantiate sessions.


# 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 special classmethod configure(), which allows additional configurational options to take place after the custom Session class has been generated. This is useful particularly for defining the specific Engine (or engines) to which new instances of Session should be bound:

Session = sessionmaker()

sess = Session()

For options, see the constructor options for Session.

class sqlalchemy.orm.session.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 sessionmaker() function which is used to generate a Session-producing callable with a given set of arguments.

  • autocommit

    Defaults to False. When True, the Session does 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 may be used to begin a transaction explicitly.

    Leaving it on its default value of False means that the Session will acquire a connection and begin a transaction the first time it is used, which it will maintain persistently until rollback(), commit(), or close() is called. When the transaction is released by any of these methods, the Session is ready for the next usage, which will again acquire and maintain a new connection/transaction.

  • autoflush – When True, all query operations will issue a flush() call to this Session before 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 autoflush is 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 Engine or Connection to which this Session should 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 bind parameter provides. This dictionary can map individual Table instances as well as Mapper instances to individual Engine or Connection objects. Operations which proceed relative to a particular Mapper will consult this dictionary for the direct Mapper instance as well as the mapper’s mapped_table attribute in order to locate an connectable to use. The full resolution is described in the get_bind() method of Session. 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 Session.bind_mapper() and Session.bind_table() methods.

  • class_ – Specify an alternate class other than sqlalchemy.orm.session.Session which should be used by the returned class. This is the only argument that is local to the sessionmaker() function, and is not sent directly to the constructor for Session.
  • _enable_transaction_accounting – Defaults to True. A legacy-only flag which when False disables 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 SessionExtension instance, 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 SessionEvents.
  • query_cls – Class which should be used to create new Query objects, as returned by the query() method. Defaults to Query.
  • 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 TwoPhaseTransaction will 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 Session will be strongly referenced until explicitly removed or the Session is closed. Deprecated - this option is obsolete.

Place an object in the Session.

Its state will be persisted to the database on the next flush operation.

Repeated calls to add() will be ignored. The opposite of add() is expunge().


Add the given collection of instances to this Session.

begin(subtransactions=False, nested=False)

Begin a transaction on this Session.

If this Session is already within a transaction, either a plain transaction or nested transaction, an error is raised, unless subtransactions=True or nested=True is specified.

The subtransactions=True flag 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.

The nested flag 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.

A mapper instance or mapped class
Any Connectable: a Engine or Connection.

All subsequent operations involving this mapper will use the given bind.

bind_table(table, bind)

Bind operations on a Table to a Connectable.

A Table instance
Any Connectable: a Engine or Connection.

All subsequent operations involving this Table will 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.

classmethod close_all()

Close all sessions in memory.


Flush pending changes and commit the current transaction.

If no transaction is in progress, this method raises an InvalidRequestError.

By default, the Session also 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=False option to sessionmaker() or the Session constructor.

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.

For a session configured with 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.

connection(mapper=None, clause=None, bind=None, close_with_result=False, **kw)

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

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

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

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

  • bind – Optional Engine to be used as the bind. If this engine is already involved in an ongoing transaction, that connection will be used. This argument takes precedence over mapper, clause.
  • mapper – Optional mapper() mapped class, used to identify the appropriate bind. This argument takes precedence over clause.
  • clause – A ClauseElement (i.e. select(), text(), etc.) which will be used to locate a bind, if a bind cannot otherwise be identified.
  • close_with_result – Passed to Engine.connect(), indicating the Connection should be considered “single use”, automatically closing when the first result set is closed. This flag only has an effect if this Session is configured with autocommit=True and does not already have a transaction in progress.
  • **kw – Additional keyword arguments are sent to get_bind(), allowing additional arguments to be passed to custom implementations of get_bind().

Mark an instance as deleted.

The database delete operation occurs upon flush().


The set of all instances marked as ‘deleted’ within this Session


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 Session.is_modified() method.

execute(clause, params=None, mapper=None, bind=None, **kw)

Execute a SQL expression construct or string statement within the current transaction.

Returns a ResultProxy representing results of the statement execution, in the same manner as that of an Engine or Connection.


result = session.execute(
   == 5)

execute() accepts any executable clause construct, such as select(), insert(), update(), 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",

is equivalent to:

from sqlalchemy import text
result = session.execute(
            text("SELECT * FROM user WHERE id=:param"),

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 execute() or 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 Connection which 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 Session itself, and in other cases can be based on the mapper() and Table objects passed to the method; see the documentation for Session.get_bind() for a full description of this scheme.

The Session.execute() method does not invoke autoflush.

The ResultProxy returned 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 Session is autocommitting and does not have a transaction-dedicated Connection available, a temporary Connection is established for the statement execution, which is closed (meaning, returned to the connection pool) when the ResultProxy has consumed all available data. This applies only when the Session is configured with autocommit=True and no transaction has been started.

  • clause – An executable statement (i.e. an Executable expression such as 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 clause when locating a bind. See Session.get_bind() for more details.
  • bind – Optional Engine to be used as the bind. If this engine is already involved in an ongoing transaction, that connection will be used. This argument takes precedence over mapper and clause when 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.

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 Session object’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 Session simultaneously, use Session.expire_all().

The Session object’s default behavior is to expire all state whenever the Session.rollback() or 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.

  • instance – The instance to be refreshed.
  • attribute_names – optional list of string attribute names indicating a subset of attributes to be expired.

Expires all persistent instances within this Session.

When any attributes on a persistent instance is next accessed, a query will be issued using the Session object’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 Session.expire().

The Session object’s default behavior is to expire all state whenever the Session.rollback() or 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 Session.

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

This is equivalent to calling expunge(obj) on all objects in this Session.


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.

For autocommit Sessions with no active manual transaction, flush() will create a transaction on the fly that surrounds the entire set of operations int the flush.


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 Session is bound.

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

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

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

The order of resolution is:

  1. if mapper given and session.binds is present, locate a bind based on mapper.
  2. if clause given and session.binds is present, locate a bind based on Table objects found in the given clause present in session.binds.
  3. if session.bind is present, return that.
  4. if clause given, attempt to return a bind linked to the MetaData ultimately associated with the clause.
  5. if mapper given, attempt to return a bind linked to the MetaData ultimately associated with the Table or other selectable to which the mapper is mapped.
  6. No bind can be found, UnboundExecutionError is raised.
  • mapper – Optional mapper() mapped class or instance of Mapper. The bind can be derived from a Mapper first by consulting the “binds” map associated with this Session, and secondly by consulting the MetaData associated with the Table to which the Mapper is mapped for a bind.
  • clause – A ClauseElement (i.e. select(), text(), etc.). If the mapper argument is not present or could not produce a bind, the given expression construct will be searched for a bound element, typically a Table associated with bound MetaData.
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() - operations involving identity keys.


True if this Session is in “transaction mode” and is not in “partial rollback” state.

The Session in its default mode of autocommit=False is essentially always in “transaction mode”, in that a SessionTransaction is associated with it as soon as it is instantiated. This SessionTransaction is 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 SessionTransaction object 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 Session has actually begun to use DBAPI resources is to implement a listener using the SessionEvents.after_begin() method, which will deliver both the Session as well as the target Connection to 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 Session is in “partial rollback” and awaits for the user to call rollback(), in order to close out the transaction stack. It is in this “partial rollback” period that the is_active flag returns False. After the call to rollback(), the SessionTransaction is replaced with a new one and is_active returns True again.

When a Session is used in autocommit=True mode, the SessionTransaction is only instantiated within the scope of a flush call, or when Session.begin() is called. So is_active will always be False outside of a flush or begin() block in this mode, and will be True within the 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 Session objects 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 Session fully, use event listeners, primarily the SessionEvents.after_begin(), SessionEvents.after_commit(), SessionEvents.after_rollback() and related events.

is_modified(instance, include_collections=True, passive=<symbol 'PASSIVE_OFF>)

Return True if 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.dirty collection; a full test for each attribute’s net “dirty” status is performed.


return session.is_modified(someobject, passive=True)

Changed in version 0.8: In SQLAlchemy 0.7 and earlier, the passive flag should always be explicitly set to True. The current default value of attributes.PASSIVE_OFF for this flag is incorrect, in that it loads unloaded collections and attributes which by definition have no modified state, and furthermore trips off autoflush which then causes all subsequent, possibly modified attributes to lose their modified state. The default value of the flag will be changed in 0.8.

A few caveats to this method apply:

  • Instances present in the Session.dirty collection may report False when 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 only if the attribute container has the active_history flag 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_history argument with column_property().

  • instance – mapped instance to be tested for pending changes.
  • include_collections – Indicates if multivalued collections should be included in the operation. Setting this to False is 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

    Indicates if unloaded attributes and collections should be loaded in the course of performing this test. If set to False, or left at its default value of PASSIVE_OFF, unloaded attributes will be loaded. If set to True or PASSIVE_NO_INITIALIZE, unloaded collections and attributes will remain unloaded. As noted previously, the existence of this flag here is a bug, as unloaded attributes by definition have no changes, and the load operation also triggers an autoflush which then cancels out subsequent changes. This flag should always be set to True.

    Changed in version 0.8: The flag will be deprecated and the default set to True.

merge(instance, load=True, **kw)

Copy the state of a given instance into a corresponding instance within this Session.

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 Session if not already.

This operation cascades to associated instances if the association is mapped with cascade="merge".

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 Session from a second level cache, or to transfer just-loaded objects into the Session owned by a worker thread or process without re-querying the database.

    The load=False use 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=False are 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 Session.


Return a context manager that disables autoflush.


with session.no_autoflush:

    some_object = SomeClass()
    # 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.

classmethod object_session(instance)

Return the Session to which an object belongs.


Prepare the current transaction in progress for two phase commit.

If no transaction is in progress, this method raises an InvalidRequestError.

Only root transactions of two phase sessions can be prepared. If the current transaction is not such, an InvalidRequestError is 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.

query(*entities, **kwargs)

Return a new Query object corresponding to this Session.

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 Query as used by with_lockmode().

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.

scalar(clause, params=None, mapper=None, bind=None, **kw)

Like execute() but return a scalar result.

transaction = None

The current active or inactive SessionTransaction.

class sqlalchemy.orm.session.SessionTransaction(session, parent=None, nested=False)

A Session-level transaction.

SessionTransaction is a mostly behind-the-scenes object not normally referenced directly by application code. It coordinates among multiple Connection objects, 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.

The Session.transaction attribute of Session refers to the current SessionTransaction object in use, if any.

A SessionTransaction is associated with a Session in its default mode of autocommit=False immediately, associated with no database connections. As the Session is called upon to emit SQL on behalf of various Engine or Connection objects, a corresponding Connection and associated Transaction is added to a collection within the SessionTransaction object, becoming one of the connection/transaction pairs maintained by the SessionTransaction.

The lifespan of the SessionTransaction ends when the Session.commit(), Session.rollback() or Session.close() methods are called. At this point, the SessionTransaction removes its association with its parent Session. A Session that is in autocommit=False mode will create a new SessionTransaction to replace it immediately, whereas a Session that’s in autocommit=True mode will remain without a SessionTransaction until the Session.begin() method is called.

Another detail of SessionTransaction behavior is that it is capable of “nesting”. This means that the begin() method can be called while an existing SessionTransaction is already present, producing a new SessionTransaction that temporarily replaces the parent SessionTransaction. When a SessionTransaction is produced as nested, it assigns itself to the Session.transaction attribute. When it is ended via Session.commit() or Session.rollback(), it restores its parent SessionTransaction back onto the Session.transaction attribute. The behavior is effectively a stack, where Session.transaction refers to the current head of the stack.

The purpose of this stack is to allow nesting of rollback() or commit() calls in context with various flavors of 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 Session is 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 Session to 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 events_orm_toplevel.

sqlalchemy.orm.attributes.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.

sqlalchemy.orm.attributes.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.

sqlalchemy.orm.attributes.get_history(obj, key, passive=<symbol 'PASSIVE_OFF>)

Return a History record for the given object and attribute key.

  • obj – an object whose class is instrumented by the attributes package.
  • key – string attribute name.
  • passive – indicates if the attribute should be loaded from the database if not already present (PASSIVE_NO_FETCH), and if the attribute should be not initialized to a blank value otherwise (PASSIVE_NO_INITIALIZE). Default is PASSIVE_OFF.
sqlalchemy.orm.attributes.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:
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.

sqlalchemy.orm.attributes.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 InstanceState for a given object.


Return the ClassManager for a given class.

sqlalchemy.orm.attributes.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.

sqlalchemy.orm.attributes.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.

class sqlalchemy.orm.attributes.History

A 3-tuple of added, unchanged and deleted values, representing the changes which have occurred on an instrumented attribute.

Each tuple member is an iterable sequence.


Return the collection of items added to the attribute (the first tuple element).


Return the collection of items that have been removed from the attribute (the third tuple element).


Return True if this History has no changes and no existing, unchanged state.


Return True if this History has changes.


Return a collection of unchanged + deleted.


Return a collection of added + unchanged.


Return a collection of added + unchanged + deleted.


Return the collection of items that have not changed on the attribute (the second tuple element).

sqlalchemy.orm.attributes.PASSIVE_NO_INITIALIZE = <symbol 'PASSIVE_NO_INITIALIZE>

Symbol indicating that loader callables should not be fired off, and a non-initialized attribute should remain that way.

sqlalchemy.orm.attributes.PASSIVE_NO_FETCH = <symbol 'PASSIVE_NO_FETCH>

Symbol indicating that loader callables should not emit SQL, but a value can be fetched from the current session.

Non-initialized attributes should be initialized to an empty value.

Symbol indicating that loader callables should not emit SQL for loading a related object, but can refresh the attributes of the local instance in order to locate a related object in the current session.

Non-initialized attributes should be initialized to an empty value.

The unit of work uses this mode to check if history is present on many-to-one attributes with minimal SQL emitted.

sqlalchemy.orm.attributes.PASSIVE_ONLY_PERSISTENT = <symbol 'PASSIVE_ONLY_PERSISTENT>

Symbol indicating that loader callables should only fire off for parent objects which are persistent (i.e., have a database identity).

Load operations for the “previous” value of an attribute make use of this flag during change events.

sqlalchemy.orm.attributes.PASSIVE_OFF = <symbol 'PASSIVE_OFF>

Symbol indicating that loader callables should be executed normally.