<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Transactions and Connection Management
—
SQLAlchemy 1.2 Documentation
</title>
<!-- begin iterate through site-imported + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<!-- end iterate through site-imported + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 1.2 Documentation" href="../index.html" />
<link rel="up" title="Using the Session" href="session.html" />
<link rel="next" title="Additional Persistence Techniques" href="persistence_techniques.html" />
<link rel="prev" title="Cascades" href="cascades.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">1.2.19</span>
| Release Date: April 15, 2019
</div>
<h1>SQLAlchemy 1.2 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 1.2 Documentation</a></h3>
<p id="sidebar-topnav">
<a href="../contents.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<label>
Search terms:
<input type="text" placeholder="search..." name="q" size="12" />
</label>
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<div id="sidebar-banner">
</div>
<div id="docs-sidebar-inner">
<h3>
<a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="tutorial.html">Object Relational Tutorial</a></span></li>
<li><span class="link-container"><a class="reference external" href="mapper_config.html">Mapper Configuration</a></span></li>
<li><span class="link-container"><a class="reference external" href="relationships.html">Relationship Configuration</a></span></li>
<li><span class="link-container"><a class="reference external" href="loading_objects.html">Loading Objects</a></span></li>
<li><span class="link-container"><a class="reference external" href="session.html">Using the Session</a></span><ul>
<li><span class="link-container"><a class="reference external" href="session_basics.html">Session Basics</a></span></li>
<li><span class="link-container"><a class="reference external" href="session_state_management.html">State Management</a></span></li>
<li><span class="link-container"><a class="reference external" href="cascades.html">Cascades</a></span></li>
<li class="selected"><span class="link-container"><strong>Transactions and Connection Management</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#managing-transactions">Managing Transactions</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#using-savepoint">Using SAVEPOINT</a></span></li>
<li><span class="link-container"><a class="reference external" href="#autocommit-mode">Autocommit Mode</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#using-subtransactions-with-autocommit">Using Subtransactions with Autocommit</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#enabling-two-phase-commit">Enabling Two-Phase Commit</a></span></li>
<li><span class="link-container"><a class="reference external" href="#setting-transaction-isolation-levels">Setting Transaction Isolation Levels</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#setting-isolation-engine-wide">Setting Isolation Engine-Wide</a></span></li>
<li><span class="link-container"><a class="reference external" href="#setting-isolation-for-individual-sessions">Setting Isolation for Individual Sessions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#setting-isolation-for-individual-transactions">Setting Isolation for Individual Transactions</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#tracking-transaction-state-with-events">Tracking Transaction State with Events</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#joining-a-session-into-an-external-transaction-such-as-for-test-suites">Joining a Session into an External Transaction (such as for test suites)</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="persistence_techniques.html">Additional Persistence Techniques</a></span></li>
<li><span class="link-container"><a class="reference external" href="contextual.html">Contextual/Thread-local Sessions</a></span></li>
<li><span class="link-container"><a class="reference external" href="session_events.html">Tracking Object and Session Changes with Events</a></span></li>
<li><span class="link-container"><a class="reference external" href="session_api.html">Session API</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="extending.html">Events and Internals</a></span></li>
<li><span class="link-container"><a class="reference external" href="extensions/index.html">ORM Extensions</a></span></li>
<li><span class="link-container"><a class="reference external" href="examples.html">ORM Examples</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="transactions-and-connection-management">
<h1>Transactions and Connection Management<a class="headerlink" href="#transactions-and-connection-management" title="Permalink to this headline">¶</a></h1>
<div class="section" id="managing-transactions">
<span id="unitofwork-transaction"></span><h2>Managing Transactions<a class="headerlink" href="#managing-transactions" title="Permalink to this headline">¶</a></h2>
<p>A newly constructed <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> may be said to be in the “begin” state.
In this state, the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> has not established any connection or
transactional state with any of the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> objects that may be associated
with it.</p>
<p>The <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> then receives requests to operate upon a database connection.
Typically, this means it is called upon to execute SQL statements using a particular
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>, which may be via <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.query()</span></code></a>, <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.execute" title="sqlalchemy.orm.session.Session.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.execute()</span></code></a>,
or within a flush operation of pending data, which occurs when such state exists
and <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a> or <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.flush()</span></code></a> is called.</p>
<p>As these requests are received, each new <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> encountered is associated
with an ongoing transactional state maintained by the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>.
When the first <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> is operated upon, the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> can be said
to have left the “begin” state and entered “transactional” state. For each
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> encountered, a <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is associated with it,
which is acquired via the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine.contextual_connect" title="sqlalchemy.engine.Engine.contextual_connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.contextual_connect()</span></code></a> method. If a
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> was directly associated with the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> (see <a class="reference internal" href="#session-external-transaction"><span class="std std-ref">Joining a Session into an External Transaction (such as for test suites)</span></a>
for an example of this), it is
added to the transactional state directly.</p>
<p>For each <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> also maintains a <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object,
which is acquired by calling <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.begin" title="sqlalchemy.engine.Connection.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.begin()</span></code></a> on each <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>,
or if the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>
object has been established using the flag <code class="docutils literal notranslate"><span class="pre">twophase=True</span></code>, a <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.TwoPhaseTransaction" title="sqlalchemy.engine.TwoPhaseTransaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">TwoPhaseTransaction</span></code></a>
object acquired via <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.begin_twophase" title="sqlalchemy.engine.Connection.begin_twophase"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.begin_twophase()</span></code></a>. These transactions are all committed or
rolled back corresponding to the invocation of the
<a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a> and <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.rollback()</span></code></a> methods. A commit operation will
also call the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.TwoPhaseTransaction.prepare" title="sqlalchemy.engine.TwoPhaseTransaction.prepare"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TwoPhaseTransaction.prepare()</span></code></a> method on all transactions if applicable.</p>
<p>When the transactional state is completed after a rollback or commit, the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>
<a class="reference internal" href="../glossary.html#term-releases"><span class="xref std std-term">releases</span></a> all <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> and <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> resources,
and goes back to the “begin” state, which
will again invoke new <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> and <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> objects as new
requests to emit SQL statements are received.</p>
<p>The example below illustrates this lifecycle:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"..."</span><span class="p">)</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span>
<span class="c1"># new session. no connections are in use.</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="c1"># first query. a Connection is acquired</span>
<span class="c1"># from the Engine, and a Transaction</span>
<span class="c1"># started.</span>
<span class="n">item1</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="c1"># second query. the same Connection/Transaction</span>
<span class="c1"># are used.</span>
<span class="n">item2</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="c1"># pending changes are created.</span>
<span class="n">item1</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="s1">'bar'</span>
<span class="n">item2</span><span class="o">.</span><span class="n">bar</span> <span class="o">=</span> <span class="s1">'foo'</span>
<span class="c1"># commit. The pending changes above</span>
<span class="c1"># are flushed via flush(), the Transaction</span>
<span class="c1"># is committed, the Connection object closed</span>
<span class="c1"># and discarded, the underlying DBAPI connection</span>
<span class="c1"># returned to the connection pool.</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">except</span><span class="p">:</span>
<span class="c1"># on rollback, the same closure of state</span>
<span class="c1"># as that of commit proceeds.</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">raise</span>
<span class="k">finally</span><span class="p">:</span>
<span class="c1"># close the Session. This will expunge any remaining</span>
<span class="c1"># objects as well as reset any existing SessionTransaction</span>
<span class="c1"># state. Neither of these steps are usually essential.</span>
<span class="c1"># However, if the commit() or rollback() itself experienced</span>
<span class="c1"># an unanticipated internal failure (such as due to a mis-behaved</span>
<span class="c1"># user-defined event handler), .close() will ensure that</span>
<span class="c1"># invalid state is removed.</span>
<span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<div class="section" id="using-savepoint">
<span id="session-begin-nested"></span><h3>Using SAVEPOINT<a class="headerlink" href="#using-savepoint" title="Permalink to this headline">¶</a></h3>
<p>SAVEPOINT transactions, if supported by the underlying engine, may be
delineated using the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin_nested()</span></code></a>
method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u1</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u2</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin_nested</span><span class="p">()</span> <span class="c1"># establish a savepoint</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u3</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c1"># rolls back u3, keeps u1 and u2</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c1"># commits u1 and u2</span></pre></div>
</div>
<p><a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin_nested()</span></code></a> may be called any number
of times, which will issue a new SAVEPOINT with a unique identifier for each
call. For each <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin_nested()</span></code></a> call, a
corresponding <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">rollback()</span></code></a> or
<a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">commit()</span></code></a> 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.)</p>
<p>When <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin_nested()</span></code></a> is called, a
<a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">flush()</span></code></a> is unconditionally issued
(regardless of the <code class="docutils literal notranslate"><span class="pre">autoflush</span></code> setting). This is so that when a
<a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">rollback()</span></code></a> occurs, the full state of the
session is expired, thus causing all subsequent attribute/instance access to
reference the full state of the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> right
before <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin_nested()</span></code></a> was called.</p>
<p><a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin_nested()</span></code></a>, in the same manner as the less often
used <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin()</span></code></a> method, returns a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.SessionTransaction" title="sqlalchemy.orm.session.SessionTransaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">SessionTransaction</span></code></a> 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:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">for</span> <span class="n">record</span> <span class="ow">in</span> <span class="n">records</span><span class="p">:</span>
<span class="k">try</span><span class="p">:</span>
<span class="k">with</span> <span class="n">session</span><span class="o">.</span><span class="n">begin_nested</span><span class="p">():</span>
<span class="n">session</span><span class="o">.</span><span class="n">merge</span><span class="p">(</span><span class="n">record</span><span class="p">)</span>
<span class="k">except</span><span class="p">:</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"Skipped record </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="n">record</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="autocommit-mode">
<span id="session-autocommit"></span><h3>Autocommit Mode<a class="headerlink" href="#autocommit-mode" title="Permalink to this headline">¶</a></h3>
<p>The examples of session lifecycle at <a class="reference internal" href="#unitofwork-transaction"><span class="std std-ref">Managing Transactions</span></a> refer
to a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> that runs in its default mode of <code class="docutils literal notranslate"><span class="pre">autocommit=False</span></code>.
In this mode, the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> begins new transactions automatically
as soon as it needs to do work upon a database connection; the transaction
then stays in progress until the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a> or <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.rollback()</span></code></a>
methods are called.</p>
<p>The <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> also features an older legacy mode of use called
<strong>autocommit mode</strong>, where a transaction is not started implicitly, and unless
the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> method is invoked, the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> 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 <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.execute" title="sqlalchemy.orm.session.Session.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.execute()</span></code></a> as well as when executing a query
returned by <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.query()</span></code></a>. For a flush operation, the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>
starts a new transaction for the duration of the flush, and commits it when
complete.</p>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>“autocommit” mode is a <strong>legacy mode of use</strong> and should not be
considered for new projects. If autocommit mode is used, it is strongly
advised that the application at least ensure that transaction scope
is made present via the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> method, rather than
using the session in pure autocommit mode.</p>
<p>If the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> method is not used, and operations are allowed
to proceed using ad-hoc connections with immediate autocommit, then the
application probably should set <code class="docutils literal notranslate"><span class="pre">autoflush=False,</span> <span class="pre">expire_on_commit=False</span></code>,
since these features are intended to be used only within the context
of a database transaction.</p>
</div>
<p>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 <code class="docutils literal notranslate"><span class="pre">autocommit=True</span></code> may be placed into the “begin” state using
the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> method. After the cycle completes upon
<a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a> or <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.rollback()</span></code></a>, connection and
transaction resources are <a class="reference internal" href="../glossary.html#term-released"><span class="xref std std-term">released</span></a> and the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> goes back
into “autocommit” mode, until <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> is called again:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">,</span> <span class="n">autocommit</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">item1</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="n">item2</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="n">item1</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="s1">'bar'</span>
<span class="n">item2</span><span class="o">.</span><span class="n">bar</span> <span class="o">=</span> <span class="s1">'foo'</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">raise</span></pre></div>
</div>
<p>The <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> method also returns a transactional token which is
compatible with the <code class="docutils literal notranslate"><span class="pre">with</span></code> statement:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">,</span> <span class="n">autocommit</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="k">with</span> <span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">():</span>
<span class="n">item1</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="n">item2</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="n">item1</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="s1">'bar'</span>
<span class="n">item2</span><span class="o">.</span><span class="n">bar</span> <span class="o">=</span> <span class="s1">'foo'</span></pre></div>
</div>
<div class="section" id="using-subtransactions-with-autocommit">
<span id="session-subtransactions"></span><h4>Using Subtransactions with Autocommit<a class="headerlink" href="#using-subtransactions-with-autocommit" title="Permalink to this headline">¶</a></h4>
<p>A subtransaction indicates usage of the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> method in conjunction with
the <code class="docutils literal notranslate"><span class="pre">subtransactions=True</span></code> flag. This produces a non-transactional, delimiting construct that
allows nesting of calls to <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin()</span></code></a> and <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">commit()</span></code></a>.
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.</p>
<p><code class="docutils literal notranslate"><span class="pre">subtransactions=True</span></code> is generally only useful in conjunction with
autocommit, and is equivalent to the pattern described at <a class="reference internal" href="../core/connections.html#connections-nested-transactions"><span class="std std-ref">Nesting of Transaction Blocks</span></a>,
where any number of functions can call <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.begin" title="sqlalchemy.engine.Connection.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.begin()</span></code></a> and <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Transaction.commit" title="sqlalchemy.engine.Transaction.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Transaction.commit()</span></code></a>
as though they are the initiator of the transaction, but in fact may be participating
in an already ongoing transaction:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># method_a starts a transaction and calls method_b</span>
<span class="k">def</span> <span class="nf">method_a</span><span class="p">(</span><span class="n">session</span><span class="p">):</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">(</span><span class="n">subtransactions</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">method_b</span><span class="p">(</span><span class="n">session</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c1"># transaction is committed here</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c1"># rolls back the transaction</span>
<span class="k">raise</span>
<span class="c1"># method_b also starts a transaction, but when</span>
<span class="c1"># called from method_a participates in the ongoing</span>
<span class="c1"># transaction.</span>
<span class="k">def</span> <span class="nf">method_b</span><span class="p">(</span><span class="n">session</span><span class="p">):</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">(</span><span class="n">subtransactions</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">SomeObject</span><span class="p">(</span><span class="s1">'bat'</span><span class="p">,</span> <span class="s1">'lala'</span><span class="p">))</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c1"># transaction is not committed yet</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c1"># rolls back the transaction, in this case</span>
<span class="c1"># the one that was initiated in method_a().</span>
<span class="k">raise</span>
<span class="c1"># create a Session and call method_a</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">autocommit</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">method_a</span><span class="p">(</span><span class="n">session</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Subtransactions are used by the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.flush()</span></code></a> 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 <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>
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.</p>
</div>
</div>
<div class="section" id="enabling-two-phase-commit">
<span id="session-twophase"></span><h3>Enabling Two-Phase Commit<a class="headerlink" href="#enabling-two-phase-commit" title="Permalink to this headline">¶</a></h3>
<p>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 <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.prepare" title="sqlalchemy.orm.session.Session.prepare"><code class="xref py py-meth docutils literal notranslate"><span class="pre">prepare()</span></code></a> the session for
interacting with transactions not managed by SQLAlchemy. To use two phase
transactions set the flag <code class="docutils literal notranslate"><span class="pre">twophase=True</span></code> on the session:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine1</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'postgresql://db1'</span><span class="p">)</span>
<span class="n">engine2</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'postgresql://db2'</span><span class="p">)</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">twophase</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="c1"># bind User operations to engine 1, Account operations to engine 2</span>
<span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">binds</span><span class="o">=</span><span class="p">{</span><span class="n">User</span><span class="p">:</span><span class="n">engine1</span><span class="p">,</span> <span class="n">Account</span><span class="p">:</span><span class="n">engine2</span><span class="p">})</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="c1"># .... work with accounts and users</span>
<span class="c1"># commit. session will issue a flush to all DBs, and a prepare step to all DBs,</span>
<span class="c1"># before committing both transactions</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="setting-transaction-isolation-levels">
<span id="session-transaction-isolation"></span><h3>Setting Transaction Isolation Levels<a class="headerlink" href="#setting-transaction-isolation-levels" title="Permalink to this headline">¶</a></h3>
<p><a class="reference internal" href="../glossary.html#term-isolation"><span class="xref std std-term">Isolation</span></a> refers to the behavior of the transaction at the database
level in relation to other transactions occurring concurrently. There
are four well-known modes of isolation, and typically the Python DBAPI
allows these to be set on a per-connection basis, either through explicit
APIs or via database-specific calls.</p>
<p>SQLAlchemy’s dialects support settable isolation modes on a per-<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>
or per-<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> basis, using flags at both the
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> level as well as at the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execution_options()</span></code></a>
level.</p>
<p>When using the ORM <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>, it acts as a <em>facade</em> 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
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> or <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> as appropriate.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.isolation_level" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.isolation_level</span></code></a></p>
<p><a class="reference internal" href="../dialects/sqlite.html#sqlite-isolation-level"><span class="std std-ref">SQLite Transaction Isolation</span></a></p>
<p><a class="reference internal" href="../dialects/postgresql.html#postgresql-isolation-level"><span class="std std-ref">PostgreSQL Isolation Level</span></a></p>
<p><a class="reference internal" href="../dialects/mysql.html#mysql-isolation-level"><span class="std std-ref">MySQL Isolation Level</span></a></p>
</div>
<div class="section" id="setting-isolation-engine-wide">
<h4>Setting Isolation Engine-Wide<a class="headerlink" href="#setting-isolation-engine-wide" title="Permalink to this headline">¶</a></h4>
<p>To set up a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> or <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.sessionmaker" title="sqlalchemy.orm.session.sessionmaker"><code class="xref py py-class docutils literal notranslate"><span class="pre">sessionmaker</span></code></a> with a specific
isolation level globally, use the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.isolation_level" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.isolation_level</span></code></a>
parameter:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">sessionmaker</span>
<span class="n">eng</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"postgresql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s1">'REPEATABLE_READ'</span><span class="p">)</span>
<span class="n">maker</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">eng</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">maker</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="setting-isolation-for-individual-sessions">
<h4>Setting Isolation for Individual Sessions<a class="headerlink" href="#setting-isolation-for-individual-sessions" title="Permalink to this headline">¶</a></h4>
<p>When we make a new <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>, either using the constructor directly
or when we call upon the callable produced by a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.sessionmaker" title="sqlalchemy.orm.session.sessionmaker"><code class="xref py py-class docutils literal notranslate"><span class="pre">sessionmaker</span></code></a>,
we can pass the <code class="docutils literal notranslate"><span class="pre">bind</span></code> argument directly, overriding the pre-existing bind.
We can combine this with the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine.execution_options" title="sqlalchemy.engine.Engine.execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.execution_options()</span></code></a> method
in order to produce a copy of the original <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> that will
add this option:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span> <span class="o">=</span> <span class="n">maker</span><span class="p">(</span>
<span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span><span class="n">isolation_level</span><span class="o">=</span><span class="s1">'SERIALIZABLE'</span><span class="p">))</span></pre></div>
</div>
<p>For the case where the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> or <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.sessionmaker" title="sqlalchemy.orm.session.sessionmaker"><code class="xref py py-class docutils literal notranslate"><span class="pre">sessionmaker</span></code></a> is
configured with multiple “binds”, we can either re-specify the <code class="docutils literal notranslate"><span class="pre">binds</span></code>
argument fully, or if we want to only replace specific binds, we
can use the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.bind_mapper" title="sqlalchemy.orm.session.Session.bind_mapper"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.bind_mapper()</span></code></a> or <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.bind_table" title="sqlalchemy.orm.session.Session.bind_table"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.bind_table()</span></code></a>
methods:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span> <span class="o">=</span> <span class="n">maker</span><span class="p">()</span>
<span class="n">session</span><span class="o">.</span><span class="n">bind_mapper</span><span class="p">(</span>
<span class="n">User</span><span class="p">,</span> <span class="n">user_engine</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span><span class="n">isolation_level</span><span class="o">=</span><span class="s1">'SERIALIZABLE'</span><span class="p">))</span></pre></div>
</div>
<p>We can also use the individual transaction method that follows.</p>
</div>
<div class="section" id="setting-isolation-for-individual-transactions">
<h4>Setting Isolation for Individual Transactions<a class="headerlink" href="#setting-isolation-for-individual-transactions" title="Permalink to this headline">¶</a></h4>
<p>A key caveat regarding isolation level is that the setting cannot be
safely modified on a <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> 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. Some may implicitly emit a
ROLLBACK and some may implicitly emit a COMMIT, others may ignore the setting
until the next transaction. Therefore SQLAlchemy emits a warning if this
option is set when a transaction is already in play. The <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>
object does not provide for us a <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> for use in a transaction
where the transaction is not already begun. So here, we need to pass
execution options to the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> at the start of a transaction
by passing <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection.params.execution_options" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Session.connection.execution_options</span></code></a>
provided by the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.connection()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">Session</span>
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span>
<span class="n">sess</span><span class="o">.</span><span class="n">connection</span><span class="p">(</span><span class="n">execution_options</span><span class="o">=</span><span class="p">{</span><span class="s1">'isolation_level'</span><span class="p">:</span> <span class="s1">'SERIALIZABLE'</span><span class="p">})</span>
<span class="c1"># work with session</span>
<span class="c1"># commit transaction. the connection is released</span>
<span class="c1"># and reverted to its previous isolation level.</span>
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>Above, we first produce a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> using either the constructor
or a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.sessionmaker" title="sqlalchemy.orm.session.sessionmaker"><code class="xref py py-class docutils literal notranslate"><span class="pre">sessionmaker</span></code></a>. Then we explicitly set up the start of
a transaction by calling upon <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.connection()</span></code></a>, which provides
for execution options that will be passed to the connection before the
transaction is begun. If we are working with a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> that
has multiple binds or some other custom scheme for <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.get_bind" title="sqlalchemy.orm.session.Session.get_bind"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.get_bind()</span></code></a>,
we can pass additional arguments to <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.connection()</span></code></a> in order to
affect how the bind is procured:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">sess</span> <span class="o">=</span> <span class="n">my_sesssionmaker</span><span class="p">()</span>
<span class="c1"># set up a transaction for the bind associated with</span>
<span class="c1"># the User mapper</span>
<span class="n">sess</span><span class="o">.</span><span class="n">connection</span><span class="p">(</span>
<span class="n">mapper</span><span class="o">=</span><span class="n">User</span><span class="p">,</span>
<span class="n">execution_options</span><span class="o">=</span><span class="p">{</span><span class="s1">'isolation_level'</span><span class="p">:</span> <span class="s1">'SERIALIZABLE'</span><span class="p">})</span>
<span class="c1"># work with session</span>
<span class="c1"># commit transaction. the connection is released</span>
<span class="c1"># and reverted to its previous isolation level.</span>
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>The <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection.params.execution_options" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Session.connection.execution_options</span></code></a> argument is only
accepted on the <strong>first</strong> call to <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.connection()</span></code></a> for a
particular bind within a transaction. If a transaction is already begun
on the target connection, a warning is emitted:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">eng</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select 1"</span><span class="p">)</span>
<span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x1017a6c50></span>
<span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">connection</span><span class="p">(</span><span class="n">execution_options</span><span class="o">=</span><span class="p">{</span><span class="s1">'isolation_level'</span><span class="p">:</span> <span class="s1">'SERIALIZABLE'</span><span class="p">})</span>
<span class="go">sqlalchemy/orm/session.py:310: SAWarning: Connection is already established</span>
<span class="go">for the given bind; execution_options ignored</span></pre></div>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.9: </span>Added the
<a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection.params.execution_options" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Session.connection.execution_options</span></code></a>
parameter to <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.connection" title="sqlalchemy.orm.session.Session.connection"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.connection()</span></code></a>.</p>
</div>
</div>
</div>
<div class="section" id="tracking-transaction-state-with-events">
<h3>Tracking Transaction State with Events<a class="headerlink" href="#tracking-transaction-state-with-events" title="Permalink to this headline">¶</a></h3>
<p>See the section <a class="reference internal" href="session_events.html#session-transaction-events"><span class="std std-ref">Transaction Events</span></a> for an overview
of the available event hooks for session transaction state changes.</p>
</div>
</div>
<div class="section" id="joining-a-session-into-an-external-transaction-such-as-for-test-suites">
<span id="session-external-transaction"></span><h2>Joining a Session into an External Transaction (such as for test suites)<a class="headerlink" href="#joining-a-session-into-an-external-transaction-such-as-for-test-suites" title="Permalink to this headline">¶</a></h2>
<p>If a <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is being used which is already in a transactional
state (i.e. has a <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> established), a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> can
be made to participate within that transaction by just binding the
<a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> to that <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>. The usual rationale for this
is a test suite that allows ORM code to work freely with a <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>,
including the ability to call <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a>, where afterwards the
entire database interaction is rolled back:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">sessionmaker</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span>
<span class="kn">from</span> <span class="nn">unittest</span> <span class="k">import</span> <span class="n">TestCase</span>
<span class="c1"># global application scope. create Session class, engine</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'postgresql://...'</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">SomeTest</span><span class="p">(</span><span class="n">TestCase</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">setUp</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="c1"># connect to the database</span>
<span class="bp">self</span><span class="o">.</span><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="c1"># begin a non-ORM transaction</span>
<span class="bp">self</span><span class="o">.</span><span class="n">trans</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">connection</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="c1"># bind an individual Session to the connection</span>
<span class="bp">self</span><span class="o">.</span><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">connection</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">test_something</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="c1"># use the session in tests.</span>
<span class="bp">self</span><span class="o">.</span><span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">Foo</span><span class="p">())</span>
<span class="bp">self</span><span class="o">.</span><span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">def</span> <span class="nf">tearDown</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
<span class="c1"># rollback - everything that happened with the</span>
<span class="c1"># Session above (including calls to commit())</span>
<span class="c1"># is rolled back.</span>
<span class="bp">self</span><span class="o">.</span><span class="n">trans</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="c1"># return connection to the Engine</span>
<span class="bp">self</span><span class="o">.</span><span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Above, we issue <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a> as well as
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Transaction.rollback" title="sqlalchemy.engine.Transaction.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Transaction.rollback()</span></code></a>. This is an example of where we take advantage
of the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object’s ability to maintain <em>subtransactions</em>, 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.</p>
<div class="topic">
<p class="topic-title first">Supporting Tests with Rollbacks</p>
<p>The above recipe works well for any kind of database enabled test, except
for a test that needs to actually invoke <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.rollback()</span></code></a> within
the scope of the test itself. The above recipe can be expanded, such
that the <a class="reference internal" href="session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> always runs all operations within the scope
of a SAVEPOINT, which is established at the start of each transaction,
so that tests can also rollback the “transaction” as well while still
remaining in the scope of a larger “transaction” that’s never committed,
using two extra events:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">event</span>
<span class="k">class</span> <span class="nc">SomeTest</span><span class="p">(</span><span class="n">TestCase</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">setUp</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="c1"># connect to the database</span>
<span class="bp">self</span><span class="o">.</span><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="c1"># begin a non-ORM transaction</span>
<span class="bp">self</span><span class="o">.</span><span class="n">trans</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="c1"># bind an individual Session to the connection</span>
<span class="bp">self</span><span class="o">.</span><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="bp">self</span><span class="o">.</span><span class="n">connection</span><span class="p">)</span>
<span class="c1"># start the session in a SAVEPOINT...</span>
<span class="bp">self</span><span class="o">.</span><span class="n">session</span><span class="o">.</span><span class="n">begin_nested</span><span class="p">()</span>
<span class="c1"># then each time that SAVEPOINT ends, reopen it</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">session</span><span class="p">,</span> <span class="s2">"after_transaction_end"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">restart_savepoint</span><span class="p">(</span><span class="n">session</span><span class="p">,</span> <span class="n">transaction</span><span class="p">):</span>
<span class="k">if</span> <span class="n">transaction</span><span class="o">.</span><span class="n">nested</span> <span class="ow">and</span> <span class="ow">not</span> <span class="n">transaction</span><span class="o">.</span><span class="n">_parent</span><span class="o">.</span><span class="n">nested</span><span class="p">:</span>
<span class="c1"># ensure that state is expired the way</span>
<span class="c1"># session.commit() at the top level normally does</span>
<span class="c1"># (optional step)</span>
<span class="n">session</span><span class="o">.</span><span class="n">expire_all</span><span class="p">()</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin_nested</span><span class="p">()</span>
<span class="c1"># ... the tearDown() method stays the same</span></pre></div>
</div>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="cascades.html" title="previous chapter">Cascades</a>
Next:
<a href="persistence_techniques.html" title="next chapter">Additional Persistence Techniques</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1.
</div>
</div>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '1.2.19',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/language_data.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
b0b6ffab06cbeede296e36ce94734bf8
>
files
>
869
