Session Basics
What does the Session do ?
In the most general sense, the Session
establishes allconversations with the database and represents a “holding zone” for all theobjects which you’ve loaded or associated with it during its lifespan. Itprovides the entrypoint to acquire a Query
object, which sendsqueries to the database using the Session
object’s current databaseconnection, populating result rows into objects that are then stored in theSession
, inside a structure called the Identity Map - a data structurethat maintains unique copies of each object, where “unique” means “only oneobject with a particular primary key”.
The Session
begins in an essentially stateless form. Once queriesare issued or other objects are persisted with it, it requests a connectionresource from an Engine
that is associated either with theSession
itself or with the mapped Table
objects beingoperated upon. This connection represents an ongoing transaction, whichremains in effect until the Session
is instructed to commit or rollback its pending state.
All changes to objects maintained by a Session
are tracked - beforethe database is queried again or before the current transaction is committed,it flushes all pending changes to the database. This is known as the Unitof Work pattern.
When using a Session
, it’s important to note that the objectswhich are associated with it are proxy objects to the transaction beingheld by the Session
- there are a variety of events that will causeobjects to re-access the database in order to keep synchronized. It ispossible to “detach” objects from a Session
, and to continue usingthem, though this practice has its caveats. It’s intended thatusually, you’d re-associate detached objects with another Session
when youwant to work with them again, so that they can resume their normal task ofrepresenting database state.
Getting a Session
Session
is a regular Python class which canbe directly instantiated. However, to standardize how sessions are configuredand acquired, the sessionmaker
class is normallyused to create a top level Session
configuration which can then be used throughout an application without theneed 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')
- session.add(myobject)
- session.commit()
Above, the sessionmaker
call creates a factory for us,which we assign to the name Session
. This factory, whencalled, will create a new Session
object using the configurationalarguments we’ve given the factory. In this case, as is typical,we’ve configured the factory to specify a particular Engine
forconnection 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 canbe set up as in the example above, using the bind
argument.
When you write your application, place thesessionmaker
factory at the global level. Thisfactory can thenbe used by the rest of the application as the source of new Session
instances, keeping the configuration for how Session
objectsare constructed in one place.
The sessionmaker
factory can also be used in conjunction withother helpers, which are passed a user-defined sessionmaker
thatis then maintained by the helper. Some of these helpers are discussed in thesection When do I construct a Session, when do I commit it, and when do I close it?.
Adding Additional Configuration to an Existing sessionmaker()
A common scenario is where the sessionmaker
is invokedat 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 thesessionmaker.configure()
method, which will place additional configurationdirectives into an existing sessionmaker
that will take placewhen 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
- Session.configure(bind=engine)
- # 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
withspecial arguments that deviate from what is normally used throughout the application,such as a Session
that binds to an alternatesource of connectivity, or a Session
that shouldhave other arguments such as expireoncommit
established differently fromwhat most of the application wants, specific arguments can be passed to thesessionmaker
factory’s [sessionmaker.call()
]($126adff33888b5d5.md#sqlalchemy.orm.session.sessionmaker.__call) method.These arguments will override whateverconfigurations 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 specificConnection
is that of a test fixture that maintains an externaltransaction - see Joining a Session into an External Transaction (such as for test suites) for an example of this.
Session Frequently Asked Questions
By this point, many users already have questions about sessions.This section presents a mini-FAQ (note that we have also a real FAQ)of the most basic issues one is presented with when using a Session
.
When do I make a sessionmaker?
Just one time, somewhere in your application’s global scope. It should belooked upon as part of your application’s configuration. If yourapplication has three .py files in a package, you could, for example,place the sessionmaker
line in your init.py
file; fromthat point on your other modules say “from mypackage import Session”. Thatway, 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 whatdatabase it’s going to be connecting to, you can bind theSession
at the “class” level to theengine later on, using sessionmaker.configure()
.
In the examples in this section, we will frequently show thesessionmaker
being created right above the line where we actuallyinvoke Session
. But that’s just forexample’s sake! In reality, the sessionmaker
would be somewhereat the module level. The calls to instantiate Session
would then be placed at the point in the application where databaseconversations begin.
When do I construct a Session, when do I commit it, and when do I close it?
tl;dr;
As a general rule, keep the lifecycle of the session separate andexternal from functions and objects that access and/or manipulatedatabase data. This will greatly help with achieving a predictableand consistent transactional scope.
Make sure you have a clear notion of where transactionsbegin and end, and keep transactions short, meaning, they endat the series of a sequence of operations, instead of being heldopen indefinitely.
A Session
is typically constructed at the beginning of a logicaloperation 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 defaultof False
, this transaction remains in progress until the Session
is rolled back, committed, or closed. The Session
willbegin a new transaction if it is used again, subsequent to the previoustransaction ending; from this it follows that the Session
is capable of having a lifespan across many transactions, though onlyone at a time. We refer to these two concepts as transaction scopeand session scope.
The implication here is that the SQLAlchemy ORM is encouraging thedeveloper to establish these two scopes in their application,including not only when the scopes begin and end, but also theexpanse of those scopes, for example should a singleSession
instance be local to the execution flow within afunction or method, should it be a global object used by theentire application, or somewhere in between these two.
The burden placed on the developer to determine this scope is onearea where the SQLAlchemy ORM necessarily has a strong opinionabout how the database should be used. The unit of work patternis specifically one of accumulating changes over time and flushingthem periodically, keeping in-memory state in sync with what’sknown to be present in a local transaction. This pattern is onlyeffective when meaningful transaction scopes are in place.
It’s usually not very hard to determine the best points at whichto begin and end the scope of a Session
, though the widevariety of application architectures possible can introducechallenging situations.
A common choice is to tear down the Session
at the sametime the transaction ends, meaning the transaction and session scopesare the same. This is a great choice to start out with as itremoves the need to consider session scope as separate from transactionscope.
While there’s no one-size-fits-all recommendation for how transactionscope should be determined, there are common patterns. Especiallyif one is writing a web application, the choice is pretty much established.
A web application is the easiest case because such an application is alreadyconstructed around a single, consistent scope - this is the request,which represents an incoming request from a browser, the processingof that request to formulate a response, and finally the delivery of thatresponse back to the client. Integrating web applications with theSession
is then the straightforward task of linking thescope of the Session
to that of the request. The Session
can be established as the request begins, or using a lazy initializationpattern which establishes one as soon as it is needed. The requestthen proceeds, with some system in place where application logic can accessthe current Session
in a manner associated with how the actualrequest object is accessed. As the request ends, the Session
is torn down as well, usually through the usage of event hooks providedby the web framework. The transaction used by the Session
may also be committed at this point, or alternatively the application mayopt for an explicit commit pattern, only committing for those requestswhere one is warranted, but still always tearing down the Session
unconditionally at the end.
Some web frameworks include infrastructure to assist in the taskof aligning the lifespan of a Session
with that of a web request.This includes products such as Flask-SQLAlchemy,for usage in conjunction with the Flask web framework,and Zope-SQLAlchemy,typically used with the Pyramid framework.SQLAlchemy recommends that these products be used as available.
In those situations where the integration libraries are notprovided or are insufficient, SQLAlchemy includes its own “helper” class known asscoped_session
. A tutorial on the usage of this objectis at Contextual/Thread-local Sessions. It provides both a quick wayto associate a Session
with the current thread, as well aspatterns to associate Session
objects with other kinds ofscopes.
As mentioned before, for non-web applications there is no one clearpattern, as applications themselves don’t have just one patternof architecture. The best strategy is to attempt to demarcate“operations”, points at which a particular thread begins to performa series of operations for some period of time, which can be committedat the end. Some examples:
A background daemon which spawns off child forkswould want to create a
Session
local to each childprocess, work with thatSession
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 itswork, 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 buttonpush. Or, the scope may correspond to explicit user interaction, such asthe user “opening” a series of records, then “saving” them.
As a general rule, the application should manage the lifecycle of thesession externally to functions that deal with specific data. This is afundamental separation of concerns which keeps data-specific operationsagnostic of the context in which they access and manipulate that data.
E.g. don’t do this:
- ### this is the **wrong way to do it** ###
- class ThingOne(object):
- def go(self):
- session = Session()
- try:
- session.query(FooBar).update({"x": 5})
- session.commit()
- except:
- session.rollback()
- raise
- class ThingTwo(object):
- def go(self):
- session = Session()
- try:
- session.query(Widget).update({"q": 18})
- session.commit()
- except:
- session.rollback()
- raise
- def run_my_program():
- ThingOne().go()
- ThingTwo().go()
Keep the lifecycle of the session (and usually the transaction)separate and external:
- ### this is a **better** (but not the only) way to do it ###
- class ThingOne(object):
- def go(self, session):
- session.query(FooBar).update({"x": 5})
- class ThingTwo(object):
- def go(self, session):
- session.query(Widget).update({"q": 18})
- def run_my_program():
- session = Session()
- try:
- ThingOne().go(session)
- ThingTwo().go(session)
- session.commit()
- except:
- session.rollback()
- raise
- finally:
- session.close()
The most comprehensive approach, recommended for more substantial applications,will try to keep the details of session, transaction and exception managementas far as possible from the details of the program doing its work. Forexample, we can further separate concerns using a context manager:
- ### another way (but again *not the only way*) to do it ###
- from contextlib import contextmanager
- @contextmanager
- def session_scope():
- """Provide a transactional scope around a series of operations."""
- session = Session()
- try:
- yield session
- session.commit()
- except:
- session.rollback()
- raise
- finally:
- session.close()
- def run_my_program():
- with session_scope() as session:
- ThingOne().go(session)
- ThingTwo().go(session)
Is the Session a cache?
Yeee…no. It’s somewhat used as a cache, in that it implements theidentity map pattern, and stores objects keyed to their primary key.However, it doesn’t do any kind of query caching. This means, if you saysession.query(Foo).filterby(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 itsees the primary key in the row, _then it can look in the local identitymap and see that the object is already there. It’s only when you sayquery.get({some primary key})
that theSession
doesn’t have to issue a query.
Additionally, the Session stores object instances using a weak referenceby default. This also defeats the purpose of using the Session as a cache.
The Session
is not designed to be aglobal object from which everyone consults as a “registry” of objects.That’s more the job of a second level cache. SQLAlchemy providesa pattern for implementing second level caching using dogpile.cache,via the Dogpile Caching example.
How can I get the Session for a certain object?
Use the object_session()
classmethodavailable on Session
:
- session = Session.object_session(someobject)
The newer Runtime Inspection API system can also be used:
- from sqlalchemy import inspect
- session = inspect(someobject).session
Is the session thread-safe?
The Session
is very much intended to be used in anon-concurrent fashion, which usually means in only one thread at atime.
The Session
should be used in such a way that oneinstance exists for a single series of operations within a singletransaction. One expedient way to get this effect is by associatinga Session
with the current thread (see Contextual/Thread-local Sessionsfor background). Another is to use a patternwhere the Session
is passed between functions and is otherwisenot shared with other threads.
The bigger point is that you should not want to use the sessionwith multiple concurrent threads. That would be like having everyone at arestaurant all eat from the same plate. The session is a local “workspace”that you use for a specific set of tasks; you don’t want to, or need to,share that session with other threads who are doing some other task.
Making sure the Session
is only used in a single concurrent thread at a timeis called a “share nothing” approach to concurrency. But actually, notsharing the Session
implies a more significant pattern; itmeans not just the Session
object itself, butalso all objects that are associated with that Session, must be kept withinthe scope of a single concurrent thread. The set of mappedobjects associated with a Session
are essentially proxies for datawithin database rows accessed over a database connection, and so just likethe Session
itself, the wholeset of objects is really just a large-scale proxy for a database connection(or connections). Ultimately, it’s mostly the DBAPI connection itself thatwe’re keeping away from concurrent access; but since the Session
and all the objects associated with it are all proxies for that DBAPI connection,the entire graph is essentially not safe for concurrent access.
If there are in fact multiple threads participatingin the same task, then you may consider sharing the session and its objects betweenthose threads; however, in this extremely unusual scenario the application wouldneed to ensure that a proper locking scheme is implemented so that there isn’tconcurrent access to the Session
or its state. A more common approachto this situation is to maintain a single Session
per concurrent thread,but to instead copy objects from one Session
to another, oftenusing the Session.merge()
method to copy the state of an object intoa new object local to a different Session
.
Basics of Using a Session
The most basic Session
use patterns are presented here.
Querying
The query()
function takes one or moreentities and returns a new Query
object whichwill issue mapper queries within the context of this Session. An entity isdefined as a mapped class, a Mapper
object, anorm-enabled descriptor, or an AliasedClass
object:
- # query from a class
- session.query(User).filter_by(name='ed').all()
- # query with multiple classes, returns tuples
- session.query(User, Address).join('addresses').filter_by(name='ed').all()
- # query using orm-enabled descriptors
- session.query(User.name, User.fullname).all()
- # query from a mapper
- user_mapper = class_mapper(User)
- session.query(user_mapper)
When Query
returns results, each objectinstantiated is stored within the identity map. When a row matches an objectwhich is already present, the same object is returned. In the latter case,whether or not the row is populated onto an existing object depends uponwhether the attributes of the instance have been expired or not. Adefault-configured Session
automaticallyexpires all instances along transaction boundaries, so that with a normallyisolated transaction, there shouldn’t be any issue of instances representingdata which is stale with regards to the current transaction.
The Query
object is introduced in great detail inObject Relational Tutorial, and further documented inQuery API.
Adding New or Existing Items
add()
is used to place instances in thesession. For transient (i.e. brand new) instances, this will have the effectof an INSERT taking place for those instances upon the next flush. Forinstances which are persistent (i.e. were loaded by this session), they arealready 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 sessionusing this method:
- user1 = User(name='user1')
- user2 = User(name='user2')
- session.add(user1)
- session.add(user2)
- session.commit() # write changes to the database
To add a list of items to the session at once, useadd_all()
:
- session.add_all([item1, item2, item3])
The add()
operation cascades alongthe save-update
cascade. For more details see the sectionCascades.
Deleting
The delete()
method places an instanceinto the Session’s list of objects to be marked as deleted:
- # mark two objects to be deleted
- session.delete(obj1)
- session.delete(obj2)
- # commit (or flush)
- session.commit()
Deleting Objects Referenced from Collections and Scalar Relationships
The ORM in general never modifies the contents of a collection or scalarrelationship during the flush process. This means, if your class has arelationship()
that refers to a collection of objects, or a referenceto a single object such as many-to-one, the contents of this attribute willnot be modified when the flush process occurs. Instead, if the Session
is expired afterwards, either through the expire-on-commit behavior ofSession.commit()
or through explicit use of Session.expire()
,the referenced object or collection upon a given object associated with thatSession
will be cleared and will re-load itself upon next access.
This behavior is not to be confused with the flush process’ impact on column-bound attributes that refer to foreign key and primary key columns; theseattributes are modified liberally within the flush, since these are theattributes that the flush process intends to manage. Nor should it be confusedwith the behavior of backreferences, as described atLinking Relationships with Backref; a backreference event will modify a collectionor scalar attribute reference, however this behavior takes place duringdirect manipulation of related collections and object references, which isexplicit within the calling application and is outside of the flush process.
A common confusion that arises regarding this behavior involves the use of thedelete()
method. When Session.delete()
is invoked uponan object and the Session
is flushed, the row is deleted from thedatabase. Rows that refer to the target row via foreign key, assuming theyare tracked using a relationship()
between the two mapped object types,will also see their foreign key attributes UPDATED to null, or if deletecascade is set up, the related rows will be deleted as well. However, eventhough rows related to the deleted object might be themselves modified as well,no changes occur to relationship-bound collections or object references onthe objects involved in the operation within the scope of the flushitself. This means if the object was amember of a related collection, it will still be present on the Python sideuntil that collection is expired. Similarly, if the object werereferenced via many-to-one or one-to-one from another object, that referencewill remain present on that object until the object is expired as well.
Below, we illustrate that after an Address
object is markedfor deletion, it’s still present in the collection associated with theparent User
, even after a flush:
- >>> address = user.addresses[1]
- >>> session.delete(address)
- >>> session.flush()
- >>> address in user.addresses
- True
When the above session is committed, all attributes are expired. The nextaccess of user.addresses
will re-load the collection, revealing thedesired state:
- >>> session.commit()
- >>> address in user.addresses
- False
There is a recipe for intercepting Session.delete()
and invoking thisexpiration automatically; see ExpireRelationshipOnFKChange for this. However, the usual practice ofdeleting items within collections is to forego the usage ofdelete()
directly, and instead use cascade behavior toautomatically invoke the deletion as a result of removing the object from theparent collection. The delete-orphan
cascade accomplishes this, asillustrated in the example below:
- class User(Base):
- __tablename__ = 'user'
- # ...
- addresses = relationship(
- "Address", cascade="all, delete, delete-orphan")
- # ...
- del user.addresses[1]
- session.flush()
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()
.
The delete-orphan
cascade can also be applied to a many-to-oneor one-to-one relationship, so that when an object is de-associated from itsparent, it is also automatically marked for deletion. Using delete-orphan
cascade on a many-to-one or one-to-one requires an additional flagrelationship.single_parent
which invokes an assertionthat this related object is not to shared with any other parent simultaneously:
- class User(Base):
- # ...
- preference = relationship(
- "Preference", cascade="all, delete, delete-orphan",
- single_parent=True)
Above, if a hypothetical Preference
object is removed from a User
,it will be deleted on flush:
- some_user.preference = None
- session.flush() # will delete the Preference object
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 handyalready in order to delete. The Query includes adelete()
method which deletes based onfiltering criteria:
- session.query(User).filter(User.id==7).delete()
The Query.delete()
method includes functionality to “expire” objectsalready in the session which match the criteria. However it does have somecaveats, including that “delete” and “delete-orphan” cascades won’t be fullyexpressed for collections which are already loaded. See the API docs fordelete()
for more details.
Flushing
When the Session
is used with its defaultconfiguration, the flush step is nearly always done transparently.Specifically, the flush occurs before any individualQuery
is issued, as well as within thecommit()
call before the transaction iscommitted. It also occurs before a SAVEPOINT is issued whenbegin_nested()
is used.
Regardless of the autoflush setting, a flush can always be forced by issuingflush()
:
- session.flush()
The “flush-on-Query” aspect of the behavior can be disabled by constructingsessionmaker
with the flag autoflush=False
:
- Session = sessionmaker(autoflush=False)
Additionally, autoflush can be temporarily disabled by setting theautoflush
flag at any time:
- mysession = Session()
- mysession.autoflush = False
More conveniently, it can be turned off within a context managed block using Session.no_autoflush
:
- with mysession.no_autoflush:
- mysession.add(some_object)
- mysession.flush()
The flush process always occurs within a transaction, even if theSession
has been configured withautocommit=True
, a setting that disables the session’s persistenttransactional state. If no transaction is present,flush()
creates its own transaction andcommits it. Any failures during flush will always result in a rollback ofwhatever transaction is present. If the Session is not in autocommit=True
mode, an explicit call to rollback()
isrequired after a flush fails, even though the underlying transaction will havebeen rolled back already - this is so that the overall nesting pattern ofso-called “subtransactions” is consistently maintained.
Committing
commit()
is used to commit the currenttransaction. It always issues flush()
beforehand to flush any remaining state to the database; this is independentof 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 bysetting autocommit=True
. In autocommit mode, a transaction can beinitiated by calling the begin()
method.
Note
The term “transaction” here refers to a transactionalconstruct within the Session
itself which may bemaintaining zero or more actual database (DBAPI) transactions. An individualDBAPI connection begins participation in the “transaction” as it is firstused to execute a SQL statement, then remains present until the session-level“transaction” is completed. See Managing Transactions forfurther detail.
Another behavior of commit()
is that bydefault it expires the state of all instances present after the commit iscomplete. This is so that when the instances are next accessed, either throughattribute access or by them being present in aQuery
result set, they receive the most recentstate. To disable this behavior, configuresessionmaker
with expire_on_commit=False
.
Normally, instances loaded into the Session
are never changed by subsequent queries; the assumption is that the currenttransaction is isolated so the state most recently loaded is correct as longas the transaction continues. Setting autocommit=True
works against thismodel to some degree since the Session
behaves in exactly the same way with regard to attribute state, except notransaction is present.
Rolling Back
rollback()
rolls back the currenttransaction. With a default configured session, the post-rollback state of thesession is as follows:
All transactions are rolled back and all connections returned to theconnection pool, unless the Session was bound directly to a Connection, inwhich case the connection is still maintained (but still rolled back).
Objects which were initially in the pending state when they were addedto the
Session
within the lifespan of thetransaction are expunged, corresponding to their INSERT statement beingrolled back. The state of their attributes remains unchanged.Objects which were marked as deleted within the lifespan of thetransaction are promoted back to the persistent state, corresponding totheir DELETE statement being rolled back. Note that if those objects werefirst pending within the transaction, that operation takes precedenceinstead.
All objects not expunged are fully expired.
With that state understood, the Session
maysafely continue usage after a rollback occurs.
When a flush()
fails, typically forreasons like primary key, foreign key, or “not nullable” constraintviolations, a rollback()
is issuedautomatically (it’s currently not possible for a flush to continue after apartial failure). However, the flush process always uses its own transactionaldemarcator called a subtransaction, which is described more fully in thedocstrings for Session
. What it means here isthat even though the database transaction has been rolled back, the end usermust still issue rollback()
to fullyreset the state of the Session
.
Closing
The close()
method issues aexpunge_all()
, and releases anytransactional/connection resources. When connections are returned to theconnection pool, transactional state is rolled back as well.