Session Basics — SQLAlchemy 2.0 Documentation (original) (raw)

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 interface where SELECT and other queries are made that will return and modify ORM-mapped objects. The ORM objects themselves are maintained inside theSession, 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 in its most common pattern of use begins in a mostly stateless form. Once queries are issued or other objects are persisted with it, it requests a connection resource from an Engine that is associated with the Session, and then establishes a transaction on that connection. This transaction remains in effect until the Sessionis instructed to commit or roll back the transaction. When the transaction ends, the connection resource associated with the Engineis released to the connection pool managed by the engine. A new transaction then starts with a new connection checkout.

The ORM objects maintained by a Session are instrumentedsuch that whenever an attribute or a collection is modified in the Python program, a change event is generated which is recorded by theSession. Whenever the database is about to be queried, or when the transaction is about to be committed, the Session firstflushes all pending changes stored in memory to the database. This is known as the unit of work pattern.

When using a Session, it’s useful to consider the ORM mapped objects that it maintains as proxy objects to database rows, which are local to the transaction being held by the Session. In order to maintain the state on the objects as matching what’s actually in the database, 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 aSession, and to continue using them, though this practice has its caveats. It’s intended that usually, you’d re-associate detached objects with another Session when you want to work with them again, so that they can resume their normal task of representing database state.

Basics of Using a Session

The most basic Session use patterns are presented here.

Opening and Closing a Session

The Session may be constructed on its own or by using thesessionmaker class. It typically is passed a singleEngine as a source of connectivity up front. A typical use may look like:

from sqlalchemy import create_engine from sqlalchemy.orm import Session

an Engine, which the Session will use for connection

resources

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/")

create session and add objects

with Session(engine) as session: session.add(some_object) session.add(some_other_object) session.commit()

Above, the Session is instantiated with an Engineassociated with a particular database URL. It is then used in a Python context manager (i.e. with: statement) so that it is automatically closed at the end of the block; this is equivalent to calling the Session.close() method.

The call to Session.commit() is optional, and is only needed if the work we’ve done with the Session includes new data to be persisted to the database. If we were only issuing SELECT calls and did not need to write any changes, then the call to Session.commit() would be unnecessary.

Note

Note that after Session.commit() is called, either explicitly or when using a context manager, all objects associated with theSession are expired, meaning their contents are erased to be re-loaded within the next transaction. If these objects are insteaddetached, they will be non-functional until re-associated with a new Session, unless the Session.expire_on_commitparameter is used to disable this behavior. See the section Committing for more detail.

Framing out a begin / commit / rollback block

We may also enclose the Session.commit() call and the overall “framing” of the transaction within a context manager for those cases where we will be committing data to the database. By “framing” we mean that if all operations succeed, the Session.commit() method will be called, but if any exceptions are raised, the Session.rollback() method will be called so that the transaction is rolled back immediately, before propagating the exception outward. In Python this is most fundamentally expressed using a try: / except: / else: block such as:

verbose version of what a context manager will do

with Session(engine) as session: session.begin() try: session.add(some_object) session.add(some_other_object) except: session.rollback() raise else: session.commit()

The long-form sequence of operations illustrated above can be achieved more succinctly by making use of theSessionTransaction object returned by the Session.begin()method, which provides a context manager interface for the same sequence of operations:

create session and add objects

with Session(engine) as session: with session.begin(): session.add(some_object) session.add(some_other_object) # inner context calls session.commit(), if there were no exceptions

outer context calls session.close()

More succinctly, the two contexts may be combined:

create session and add objects

with Session(engine) as session, session.begin(): session.add(some_object) session.add(some_other_object)

inner context calls session.commit(), if there were no exceptions

outer context calls session.close()

Using a sessionmaker

The purpose of sessionmaker is to provide a factory forSession objects with a fixed configuration. As it is typical that an application will have an Engine object in module scope, the sessionmaker can provide a factory forSession objects that are constructed against this engine:

from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker

an Engine, which the Session will use for connection

resources, typically in module scope

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/")

a sessionmaker(), also in the same scope as the engine

Session = sessionmaker(engine)

we can now construct a Session() without needing to pass the

engine each time

with Session() as session: session.add(some_object) session.add(some_other_object) session.commit()

closes the session

The sessionmaker is analogous to the Engineas a module-level factory for function-level sessions / connections. As such it also has its own sessionmaker.begin() method, analogous to Engine.begin(), which returns a Session object and also maintains a begin/commit/rollback block:

from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker

an Engine, which the Session will use for connection

resources

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/")

a sessionmaker(), also in the same scope as the engine

Session = sessionmaker(engine)

we can now construct a Session() and include begin()/commit()/rollback()

at once

with Session.begin() as session: session.add(some_object) session.add(some_other_object)

commits the transaction, closes the session

Where above, the Session will both have its transaction committed as well as that the Session will be closed, when the abovewith: block ends.

When you write your application, thesessionmaker factory should be scoped the same as theEngine object created by create_engine(), which is typically at module-level or global scope. As these objects are both factories, they can be used by any number of functions and threads simultaneously.

Querying

The primary means of querying is to make use of the select()construct to create a Select object, which is then executed to return a result using methods such as Session.execute() andSession.scalars(). Results are then returned in terms ofResult objects, including sub-variants such asScalarResult.

A complete guide to SQLAlchemy ORM querying can be found atORM Querying Guide. Some brief examples follow:

from sqlalchemy import select from sqlalchemy.orm import Session

with Session(engine) as session: # query for User objects statement = select(User).filter_by(name="ed")

# list of ``User`` objects
user_obj = session.scalars(statement).all()

# query for individual columns
statement = select(User.name, User.fullname)

# list of Row objects
rows = session.execute(statement).all()

Changed in version 2.0: “2.0” style querying is now standard. See2.0 Migration - ORM Usage for migration notes from the 1.x series.

Adding New or Existing Items

Session.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.add(user1) session.add(user2)

session.commit() # write changes to the database

To add a list of items to the session at once, useSession.add_all():

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

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

Deleting

The Session.delete() method places an instance into 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()

Session.delete() marks an object for deletion, which will result in a DELETE statement emitted for each primary key affected. Before the pending deletes are flushed, objects marked by “delete” are present in the Session.deleted collection. After the DELETE, they are expunged from the Session, which becomes permanent after the transaction is committed.

There are various important behaviors related to theSession.delete() operation, particularly in how relationships to other objects and collections are handled. There’s more information on how this works in the section Cascades, but in general the rules are:

Flushing

When the Session is used with its default configuration, the flush step is nearly always done transparently. Specifically, the flush occurs before any individual SQL statement is issued as a result of a Query or a 2.0-style Session.execute() call, as well as within theSession.commit() call before the transaction is committed. It also occurs before a SAVEPOINT is issued whenSession.begin_nested() is used.

A Session flush can be forced at any time by calling theSession.flush() method:

The flush which occurs automatically within the scope of certain methods is known as autoflush. Autoflush is defined as a configurable, automatic flush call which occurs at the beginning of methods including:

There are also points at which flushes occur unconditionally; these points are within key transactional boundaries which include:

The autoflush behavior, as applied to the previous list of items, can be disabled by constructing a Session orsessionmaker passing the Session.autoflush parameter asFalse:

Session = sessionmaker(autoflush=False)

Additionally, autoflush can be temporarily disabled within the flow of using a Session using theSession.no_autoflush context manager:

with mysession.no_autoflush: mysession.add(some_object) mysession.flush()

To reiterate: The flush process always occurs when transactional methods such as Session.commit() and Session.begin_nested() are called, regardless of any “autoflush” settings, when the Session has remaining pending changes to process.

As the Session only invokes SQL to the database within the context of a DBAPI transaction, all “flush” operations themselves only occur within a database transaction (subject to theisolation level of the database transaction), provided that the DBAPI is not indriver level autocommit mode. This means that assuming the database connection is providing for atomicity within its transactional settings, if any individual DML statement inside the flush fails, the entire operation will be rolled back.

When a failure occurs within a flush, in order to continue using that same Session, an explicit call to Session.rollback() is required after a flush fails, even though the underlying transaction will have been rolled back already (even if the database driver is technically in driver-level autocommit mode). This is so that the overall nesting pattern of so-called “subtransactions” is consistently maintained. The FAQ section“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar) contains a more detailed description of this behavior.

Get by Primary Key

As the Session makes use of an identity map which refers to current in-memory objects by primary key, the Session.get()method is provided as a means of locating objects by primary key, first looking within the current identity map and then querying the database for non present values. Such as, to locate a User entity with primary key identity (5, ):

my_user = session.get(User, 5)

The Session.get() also includes calling forms for composite primary key values, which may be passed as tuples or dictionaries, as well as additional parameters which allow for specific loader and execution options. See Session.get() for the complete parameter list.

Expiring / Refreshing

An important consideration that will often come up when using theSession is that of dealing with the state that is present on objects that have been loaded from the database, in terms of keeping them synchronized with the current state of the transaction. The SQLAlchemy ORM is based around the concept of an identity map such that when an object is “loaded” from a SQL query, there will be a unique Python object instance maintained corresponding to a particular database identity. This means if we emit two separate queries, each for the same row, and get a mapped object back, the two queries will have returned the same Python object:

u1 = session.scalars(select(User).where(User.id == 5)).one() u2 = session.scalars(select(User).where(User.id == 5)).one() u1 is u2 True

Following from this, when the ORM gets rows back from a query, it willskip the population of attributes for an object that’s already loaded. The design assumption here is to assume a transaction that’s perfectly isolated, and then to the degree that the transaction isn’t isolated, the application can take steps on an as-needed basis to refresh objects from the database transaction. The FAQ entry at I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewherediscusses this concept in more detail.

When an ORM mapped object is loaded into memory, there are three general ways to refresh its contents with new data from the current transaction:

Further discussion on the refresh / expire concept can be found atRefreshing / Expiring.

UPDATE and DELETE with arbitrary WHERE clause

SQLAlchemy 2.0 includes enhanced capabilities for emitting several varieties of ORM-enabled INSERT, UPDATE and DELETE statements. See the document at ORM-Enabled INSERT, UPDATE, and DELETE statements for documentation.

Auto Begin

The Session object features a behavior known as autobegin. This indicates that the Session will internally consider itself to be in a “transactional” state as soon as any work is performed with theSession, either involving modifications to the internal state of the Session with regards to object state changes, or with operations that require database connectivity.

When the Session is first constructed, there’s no transactional state present. The transactional state is begun automatically, when a method such as Session.add() or Session.execute()is invoked, or similarly if a Query is executed to return results (which ultimately uses Session.execute()), or if an attribute is modified on a persistent object.

The transactional state can be checked by accessing theSession.in_transaction() method, which returns True or Falseindicating if the “autobegin” step has proceeded. While not normally needed, the Session.get_transaction() method will return the actualSessionTransaction object that represents this transactional state.

The transactional state of the Session may also be started explicitly, by invoking the Session.begin() method. When this method is called, the Session is placed into the “transactional” state unconditionally. Session.begin() may be used as a context manager as described at Framing out a begin / commit / rollback block.

Disabling Autobegin to Prevent Implicit Transactions

The “autobegin” behavior may be disabled using theSession.autobegin parameter set to False. By using this parameter, a Session will require that theSession.begin() method is called explicitly. Upon construction, as well as after any of the Session.rollback(),Session.commit(), or Session.close() methods are called, the Session won’t implicitly begin any new transactions and will raise an error if an attempt to use the Session is made without first calling Session.begin():

with Session(engine, autobegin=False) as session: session.begin() # <-- required, else InvalidRequestError raised on next call

session.add(User(name="u1"))
session.commit()

session.begin()  # <-- required, else InvalidRequestError raised on next call

u1 = session.scalar(select(User).filter_by(name="u1"))

New in version 2.0: Added Session.autobegin, allowing “autobegin” behavior to be disabled

Committing

Session.commit() is used to commit the current transaction. At its core this indicates that it emits COMMIT on all current database connections that have a transaction in progress; from a DBAPI perspective this means the connection.commit()DBAPI method is invoked on each DBAPI connection.

When there is no transaction in place for the Session, indicating that no operations were invoked on this Session since the previous call to Session.commit(), the method will begin and commit an internal-only “logical” transaction, that does not normally affect the database unless pending flush changes were detected, but will still invoke event handlers and object expiration rules.

The Session.commit() operation unconditionally issuesSession.flush() before emitting COMMIT on relevant database connections. If no pending changes are detected, then no SQL is emitted to the database. This behavior is not configurable and is not affected by theSession.autoflush parameter.

Subsequent to that, assuming the Session is bound to anEngine, Session.commit() will then COMMIT the actual database transaction that is in place, if one was started. After the commit, the Connection object associated with that transaction is closed, causing its underlying DBAPI connection to be released back to the connection pool associated with the Engine to which theSession is bound.

For a Session that’s bound to multiple engines (e.g. as described at Partitioning Strategies), the same COMMIT steps will proceed for each Engine /Connection that is in play within the “logical” transaction being committed. These database transactions are uncoordinated with each other unless two-phase features are enabled.

Other connection-interaction patterns are available as well, by binding theSession to a Connection directly; in this case, it’s assumed that an externally-managed transaction is present, and a real COMMIT will not be emitted automatically in this case; see the sectionJoining a Session into an External Transaction (such as for test suites) for background on this pattern.

Finally, all objects within the Session are expired as the transaction is closed out. This is so that when the instances are next accessed, either through attribute access or by them being present in the result of a SELECT, they receive the most recent state. This behavior may be controlled by the Session.expire_on_commit flag, which may be set to False when this behavior is undesirable.

Rolling Back

Session.rollback() rolls back the current transaction, if any. When there is no transaction in place, the method passes silently.

With a default configured session, the post-rollback state of the session, subsequent to a transaction having been begun either via autobeginor by calling the Session.begin()method explicitly, is as follows:

With that state understood, the Session may safely continue usage after a rollback occurs.

Changed in version 1.4: The Session object now features deferred “begin” behavior, as described in autobegin. If no transaction is begun, methods like Session.commit() andSession.rollback() have no effect. This behavior would not have been observed prior to 1.4 as under non-autocommit mode, a transaction would always be implicitly present.

When a Session.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 Session goes into a state known as “inactive” at this point, and the calling application must always call theSession.rollback() method explicitly so that theSession can go back into a usable state (it can also be simply closed and discarded). See the FAQ entry at “This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar) for further discussion.

Closing

The Session.close() method issues a Session.expunge_all() which removes all ORM-mapped objects from the session, and releases any transactional/connection resources from the Engine object(s) to which it is bound. When connections are returned to the connection pool, transactional state is rolled back as well.

By default, when the Session is closed, it is essentially in the original state as when it was first constructed, and may be used again. In this sense, the Session.close() method is more like a “reset” back to the clean state and not as much like a “database close” method. In this mode of operation the method Session.reset() is an alias toSession.close() and behaves in the same way.

The default behavior of Session.close() can be changed by setting the parameter Session.close_resets_only to False, indicating that the Session cannot be reused after the methodSession.close() has been called. In this mode of operation theSession.reset() method will allow multiple “reset” of the session, behaving like Session.close() whenSession.close_resets_only is set to True.

New in version 2.0.22.

It’s recommended that the scope of a Session be limited by a call to Session.close() at the end, especially if theSession.commit() or Session.rollback() methods are not used. The Session may be used as a context manager to ensure that Session.close() is called:

with Session(engine) as session: result = session.execute(select(User))

closes session automatically

Changed in version 1.4: The Session object features deferred “begin” behavior, as described in autobegin. no longer immediately begins a new transaction after the Session.close() method is called.

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 be looked upon as part of your application’s configuration. If your application has three .py files in a package, you could, for example, place the sessionmaker line in your __init__.py file; from that point on your other modules say “from mypackage import Session”. That way, everyone else just uses 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 theSession at the “class” level to the engine later on, using sessionmaker.configure().

In the examples in this section, we will frequently show thesessionmaker 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 Sessionwould 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. This transaction remains in progress until the Sessionis 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 Sessionis capable of having a lifespan across many transactions, though only one at a time. We refer to these two concepts as transaction scopeand session scope.

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.

Some sample scenarios include:

As a general rule, the application should manage the lifecycle of the session externally to functions that deal with specific data. This is a fundamental separation of concerns which keeps data-specific operations agnostic of the context in which they access and manipulate that data.

E.g. don’t do this:

this is the wrong way to do it

class ThingOne: def go(self): session = Session() try: session.execute(update(FooBar).values(x=5)) session.commit() except: session.rollback() raise

class ThingTwo: def go(self): session = Session() try: session.execute(update(Widget).values(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. The example below illustrates how this might look, and additionally makes use of a Python context manager (i.e. the with:keyword) in order to manage the scope of the Session and its transaction automatically:

this is a better (but not the only) way to do it

class ThingOne: def go(self, session): session.execute(update(FooBar).values(x=5))

class ThingTwo: def go(self, session): session.execute(update(Widget).values(q=18))

def run_my_program(): with Session() as session: with session.begin(): ThingOne().go(session) ThingTwo().go(session)

Changed in version 1.4: The Session may be used as a context manager without the use of external helper functions.

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.scalars(select(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 sayquery.get({some primary key}) that theSession 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 dogpile.cache, via the Dogpile Caching example.

How can I get the Session for a certain object?

Use the Session.object_session() classmethod available 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? Is AsyncSession safe to share in concurrent tasks?

The Session is a mutable, stateful object that represents a single database transaction. An instance of Session therefore cannot be shared among concurrent threads or asyncio tasks without careful synchronization. The Session is intended to be used in anon-concurrent fashion, that is, a particular instance of Sessionshould be used in only one thread or task at a time.

When using the AsyncSession object from SQLAlchemy’sasyncio extension, this object is only a thin proxy on top of a Session, and the same rules apply; it is anunsynchronized, mutable, stateful object, so it is not safe to use a single instance of AsyncSession in multiple asyncio tasks at once.

An instance of Session or AsyncSession represents a single logical database transaction, referencing only a singleConnection at a time for a particular Engine orAsyncEngine to which the object is bound (note that these objects both support being bound to multiple engines at once, however in this case there will still be only one connection per engine in play within the scope of a transaction).

A database connection within a transaction is also a stateful object that is intended to be operated upon in a non-concurrent, sequential fashion. Commands are issued on the connection in a sequence, which are handled by the database server in the exact order in which they are emitted. As theSession emits commands upon this connection and receives results, the Session itself is transitioning through internal state changes that align with the state of commands and data present on this connection; states which include if a transaction were begun, committed, or rolled back, what SAVEPOINTs if any are in play, as well as fine-grained synchronization of the state of individual database rows with local ORM-mapped objects.

When designing database applications for concurrency, the appropriate model is that each concurrent task / thread works with its own database transaction. This is why when discussing the issue of database concurrency, the standard terminology used is multiple, concurrent transactions. Within traditional RDMS there is no analogue for a single database transaction that is receiving and processing multiple commands concurrently.

The concurrency model for SQLAlchemy’s Session andAsyncSession is therefore Session per thread, AsyncSession per task. An application that uses multiple threads, or multiple tasks in asyncio such as when using an API like asyncio.gather() would want to ensure that each thread has its own Session, each asyncio task has its own AsyncSession.

The best way to ensure this use is by using the standard context manager pattern locally within the top level Python function that is inside the thread or task, which will ensure the lifespan of theSession or AsyncSession is maintained within a local scope.

For applications that benefit from having a “global” Sessionwhere it’s not an option to pass the Session object to specific functions and methods which require it, the scoped_sessionapproach can provide for a “thread local” Session object; see the section Contextual/Thread-local Sessions for background. Within the asyncio context, the async_scoped_sessionobject is the asyncio analogue for scoped_session, however is more challenging to configure as it requires a custom “context” function.