SQLAlchemy 1.3 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Session Basics
- State Management
- Cascades
- Transactions and Connection Management¶
- Additional Persistence Techniques
- Contextual/Thread-local Sessions
- Tracking Object and Session Changes with Events
- Session API
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
- Previous: Cascades
- Next: Additional Persistence Techniques
- Up: Home
- On this page:
Transactions and Connection Management¶
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 (such as for test suites)
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()
try:
# first query. a Connection is acquired
# from the Engine, and a Transaction
# started.
item1 = session.query(Item).get(1)
# second query. the same Connection/Transaction
# are used.
item2 = session.query(Item).get(2)
# pending changes are created.
item1.foo = 'bar'
item2.bar = 'foo'
# commit. The pending changes above
# are flushed via flush(), the Transaction
# is committed, the Connection object closed
# and discarded, the underlying DBAPI connection
# returned to the connection pool.
session.commit()
except:
# on rollback, the same closure of state
# as that of commit proceeds.
session.rollback()
raise
finally:
# close the Session. This will expunge any remaining
# objects as well as reset any existing SessionTransaction
# state. Neither of these steps are usually essential.
# However, if the commit() or rollback() itself experienced
# an unanticipated internal failure (such as due to a mis-behaved
# user-defined event handler), .close() will ensure that
# invalid state is removed.
session.close()
Using SAVEPOINT¶
SAVEPOINT transactions, if supported by the underlying engine, may be
delineated using the Session.begin_nested()
method:
Session = sessionmaker()
session = Session()
session.add(u1)
session.add(u2)
session.begin_nested() # establish a savepoint
session.add(u3)
session.rollback() # rolls back u3, keeps u1 and u2
session.commit() # commits u1 and u2
Session.begin_nested()
may be called any number
of times, which will issue a new SAVEPOINT with a unique identifier for each
call. For each Session.begin_nested()
call, a
corresponding Session.rollback()
or
Session.commit()
must be issued. (But note that if the return value is
used as a context manager, i.e. in a with-statement, then this rollback/commit
is issued by the context manager upon exiting the context, and so should not be
added explicitly.)
When Session.begin_nested()
is called, a
Session.flush()
is unconditionally issued
(regardless of the autoflush
setting). This is so that when a
Session.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 Session.begin_nested()
was called.
Session.begin_nested()
, in the same manner as the less often
used Session.begin()
method, returns a SessionTransaction
object
which 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:
try:
with session.begin_nested():
session.merge(record)
except:
print("Skipped record %s" % record)
session.commit()
Autocommit Mode¶
Deprecated since version 1.4: “autocommit” mode is a legacy mode of use and should not be considered
for new projects. The feature will be deprecated in SQLAlchemy 1.4 and
removed in version 2.0; both versions provide a more refined
“autobegin” approach that allows the Session.begin()
method
to be used normally. If autocommit mode is used, it is strongly
advised that the application at least ensure that transaction scope is made
present via the Session.begin()
method, rather than using the
session in pure autocommit mode.
The examples of session lifecycle at Managing Transactions refer
to a Session
that runs in its default mode of autocommit=False
.
In this mode, the Session
begins new transactions automatically
as soon as it needs to do work upon a database connection; the transaction
then stays in progress until the Session.commit()
or Session.rollback()
methods are called.
The Session
also features an older legacy mode of use called
autocommit mode, where a transaction is not started implicitly, and unless
the Session.begin()
method is invoked, the Session
will
perform each database operation on a new connection checked out from the
connection pool, which is then released back to the pool immediately
after the operation completes. This refers to
methods like Session.execute()
as well as when executing a query
returned by Session.query()
. For a flush operation, the Session
starts a new transaction for the duration of the flush, and commits it when
complete.
Modern usage of “autocommit mode” tends to be for framework integrations that
wish 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()
session.begin()
try:
item1 = session.query(Item).get(1)
item2 = session.query(Item).get(2)
item1.foo = 'bar'
item2.bar = 'foo'
session.commit()
except:
session.rollback()
raise
The Session.begin()
method also returns a transactional token which is
compatible with the with
statement:
Session = sessionmaker(bind=engine, autocommit=True)
session = Session()
with session.begin():
item1 = session.query(Item).get(1)
item2 = session.query(Item).get(2)
item1.foo = 'bar'
item2.bar = 'foo'
Using Subtransactions with Autocommit¶
Deprecated since version 1.4: The Session.begin.subtransactions
flag will be deprecated in SQLAlchemy 1.4 and removed in SQLAlchemy 2.0.
For background on migrating away from the “subtransactions” pattern
see the next section Migrating from the “subtransaction” pattern.
A subtransaction indicates usage of the Session.begin()
method in
conjunction with the Session.begin.subtransactions
flag set to
True
. This produces a
non-transactional, delimiting construct that allows nesting of calls to
Session.begin()
and Session.commit()
. Its purpose is to allow
the construction of code that can function within a transaction both
independently of any external code that starts a transaction, as well as within
a block that has already demarcated a transaction.
subtransactions=True
is generally only useful in conjunction with
autocommit, and is equivalent to the pattern described at
Nesting of Transaction Blocks, where any number of functions can call
Connection.begin()
and 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.begin(subtransactions=True)
try:
method_b(session)
session.commit() # transaction is committed here
except:
session.rollback() # rolls back the transaction
raise
# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
session.begin(subtransactions=True)
try:
session.add(SomeObject('bat', 'lala'))
session.commit() # transaction is not committed yet
except:
session.rollback() # rolls back the transaction, in this case
# the one that was initiated in method_a().
raise
# create a Session and call method_a
session = Session(autocommit=True)
method_a(session)
session.close()
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.
Migrating from the “subtransaction” pattern¶
The “subtransaction” pattern will be deprecated in SQLAlchemy 1.4 and removed in version 2.0 as a public API. This pattern has been shown to be confusing in real world applications, and it is preferable for an application to ensure that the top-most level of database operations are performed with a single begin/commit pair.
To provide backwards compatibility for applications that make use of this pattern, the following context manager or a similar implementation based on a decorator may be used. It relies on autocommit mode within SQLAlchemy 1.3 but not in SQLAlchemy 1.4:
import contextlib
@contextlib.contextmanager
def transaction(session):
assert session.autocommit, (
"this pattern expects the session to be in autocommit mode. "
"This assertion can be removed for SQLAlchemy 1.4."
)
if not session.transaction:
with session.begin():
yield
else:
yield
The above context manager may be used in the same way the “subtransaction” flag works, such as in the following example:
# method_a starts a transaction and calls method_b
def method_a(session):
with transaction(session):
method_b(session)
# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
with transaction(session):
session.add(SomeObject('bat', 'lala'))
Session = sessionmaker(engine, autocommit=True)
# create a Session and call method_a
session = Session()
try:
method_a(session)
finally:
session.close()
To compare towards the preferred idiomatic pattern, the begin block should be at the outermost level. This removes the need for individual functions or methods to be concerned with the details of transaction demarcation:
def method_a(session):
method_b(session)
def method_b(session):
session.add(SomeObject('bat', 'lala'))
Session = sessionmaker(engine)
# create a Session and call method_a
session = Session()
try:
# Session "begins" the transaction automatically, so the
# .transaction attribute may be used as a context manager.
with session.transaction:
method_a(session)
finally:
session.close()
SQLAlchemy 1.4 will feature an improved API for the above transactional patterns.
See also
Migrating from the “nesting” pattern - similar pattern based on Core only
Enabling Two-Phase Commit¶
For backends which support two-phase operation (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 Session.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
session.commit()
Setting Transaction Isolation Levels / DBAPI AUTOCOMMIT¶
Most DBAPIs support the concept of configurable transaction isolation levels. These are traditionally the four levels “READ UNCOMMITTED”, “READ COMMITTED”, “REPEATABLE READ” and “SERIALIZABLE”. These are usually applied to a DBAPI connection before it begins a new transaction, noting that most DBAPIs will begin this transaction implicitly when SQL statements are first emitted.
DBAPIs that support isolation levels also usually support the concept of true
“autocommit”, which means that the DBAPI connection itself will be placed into
a non-transactional autocommit mode. This usually means that the typical
DBAPI behavior of emitting “BEGIN” to the database automatically no longer
occurs, but it may also include other directives. When using this mode,
the DBAPI does not use a transaction under any circumstances. SQLAlchemy
methods like .begin()
, .commit()
and .rollback()
pass silently.
SQLAlchemy’s dialects support settable isolation modes on a per-Engine
or per-Connection
basis, using flags at both the
create_engine()
level as well as at the Connection.execution_options()
level.
When using the ORM Session
, it acts as a facade for engines and
connections, but does not expose transaction isolation directly. So in
order to affect transaction isolation level, we need to act upon the
Engine
or Connection
as appropriate.
Setting Isolation For A Sessionmaker / Engine Wide¶
To set up a Session
or sessionmaker
with a specific
isolation level globally, the first technique is that an
Engine
can be constructed against a specific isolation level
in all cases, which is then used as the source of connectivity for a
Session
and/or sessionmaker
:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
eng = create_engine(
"postgresql://scott:tiger@localhost/test",
isolation_level='REPEATABLE READ'
)
Session = sessionmaker(eng)
Another option, useful if there are to be two engines with different isolation
levels at once, is to use the Engine.execution_options()
method,
which will produce a shallow copy of the original Engine
which
shares the same connection pool as the parent engine. This is often preferable
when operations will be separated into “transactional” and “autocommit”
operations:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
eng = create_engine("postgresql://scott:tiger@localhost/test")
autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")
transactional_session = sessionmaker(eng)
autocommit_session = sessionmaker(autocommit_engine)
Above, both “eng
” and "autocommit_engine"
share the same dialect and
connection pool. However the “AUTOCOMMIT” mode will be set upon connections
when they are acquired from the autocommit_engine
. The two
sessionmaker
objects “transactional_session
” and “autocommit_session"
then inherit these characteristics when they work with database connections.
The “autocommit_session
” continues to have transactional semantics,
including that
Session.commit()
and Session.rollback()
still consider
themselves to be “committing” and “rolling back” objects, however the
transaction will be silently absent. For this reason, it is typical,
though not strictly required, that a Session with AUTOCOMMIT isolation be
used in a read-only fashion, that is:
session = autocommit_session()
some_objects = session.query(cls1).filter(...).all()
some_other_objects = session.query(cls2).filter(...).all()
session.close() # closes connection
Setting Isolation for Individual Sessions¶
When we make a new Session
, either using the constructor directly
or when we call upon the callable produced by a sessionmaker
,
we can pass the bind
argument directly, overriding the pre-existing bind.
We can for example create our Session
from a default
sessionmaker
and pass an engine set for autocommit:
plain_engine = create_engine("postgresql://scott:tiger@localhost/test")
autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")
# will normally use plain_engine
Session = sessionmaker(plain_engine)
# make a specific Session that will use the "autocommit" engine
with Session(bind=autocommit_engine) as session:
# work with session
For the case where the Session
or sessionmaker
is
configured with multiple “binds”, we can either re-specify the binds
argument fully, or if we want to only replace specific binds, we
can use the Session.bind_mapper()
or Session.bind_table()
methods:
with Session() as session:
session.bind_mapper(User, autocommit_engine)
Setting Isolation for Individual Transactions¶
A key caveat regarding isolation level is that the setting cannot be
safely modified on a Connection
where a transaction has already
started. Databases cannot change the isolation level of a transaction
in progress, and some DBAPIs and SQLAlchemy dialects
have inconsistent behaviors in this area.
Therefore it is preferable to use a Session
that is up front
bound to an engine with the desired isolation level. However, the isolation
level on a per-connection basis can be affected by using the
Session.connection()
method at the start of a transaction:
from sqlalchemy.orm import Session
# assume session just constructed
sess = Session(bind=engine)
# call connection() with options before any other operations proceed.
# this will procure a new connection from the bound engine and begin a real
# database transaction.
sess.connection(execution_options={'isolation_level': 'SERIALIZABLE'})
# ... work with session in SERIALIZABLE isolation level...
# commit transaction. the connection is released
# and reverted to its previous isolation level.
sess.commit()
# here, a new "transaction" is in play and isolation level may be set
# again if another transaction is to be used
Above, we first produce a Session
using either the constructor
or a sessionmaker
. Then we explicitly set up the start of
a transaction by calling upon Session.connection()
, which provides
for execution options that will be passed to the connection before the
transaction is begun.
Tracking Transaction State with Events¶
See the section Transaction Events for an overview of the available event hooks for session transaction state changes.
Joining a Session into an External Transaction (such as for test suites)¶
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 = self.connection.begin()
# bind an individual Session to the connection
self.session = Session(bind=self.connection)
def test_something(self):
# use the session in tests.
self.session.add(Foo())
self.session.commit()
def tearDown(self):
self.session.close()
# rollback - everything that happened with the
# Session above (including calls to commit())
# is rolled back.
self.trans.rollback()
# return connection to the Engine
self.connection.close()
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.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sat 06 Jan 2024 12:16:24 PM