<!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>
Working with Engines and Connections
—
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="Engine and Connection Use" href="engines_connections.html" />
<link rel="next" title="Connection Pooling" href="pooling.html" />
<link rel="prev" title="Engine Configuration" href="engines.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 Core">SQLAlchemy Core</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="tutorial.html">SQL Expression Language Tutorial</a></span></li>
<li><span class="link-container"><a class="reference external" href="expression_api.html">SQL Statements and Expressions API</a></span></li>
<li><span class="link-container"><a class="reference external" href="schema.html">Schema Definition Language</a></span></li>
<li><span class="link-container"><a class="reference external" href="types.html">Column and Data Types</a></span></li>
<li><span class="link-container"><a class="reference external" href="engines_connections.html">Engine and Connection Use</a></span><ul>
<li><span class="link-container"><a class="reference external" href="engines.html">Engine Configuration</a></span></li>
<li class="selected"><span class="link-container"><strong>Working with Engines and Connections</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#basic-usage">Basic Usage</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-transactions">Using Transactions</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#nesting-of-transaction-blocks">Nesting of Transaction Blocks</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#understanding-autocommit">Understanding Autocommit</a></span></li>
<li><span class="link-container"><a class="reference external" href="#connectionless-execution-implicit-execution">Connectionless Execution, Implicit Execution</a></span></li>
<li><span class="link-container"><a class="reference external" href="#translation-of-schema-names">Translation of Schema Names</a></span></li>
<li><span class="link-container"><a class="reference external" href="#engine-disposal">Engine Disposal</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-the-threadlocal-execution-strategy">Using the Threadlocal Execution Strategy</a></span></li>
<li><span class="link-container"><a class="reference external" href="#working-with-raw-dbapi-connections">Working with Raw DBAPI Connections</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#calling-stored-procedures">Calling Stored Procedures</a></span></li>
<li><span class="link-container"><a class="reference external" href="#multiple-result-sets">Multiple Result Sets</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#registering-new-dialects">Registering New Dialects</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#registering-dialects-in-process">Registering Dialects In-Process</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#connection-engine-api">Connection / Engine API</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="pooling.html">Connection Pooling</a></span></li>
<li><span class="link-container"><a class="reference external" href="events.html">Core Events</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="api_basics.html">Core API Basics</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.engine">
<span id="working-with-engines-and-connections"></span><span id="connections-toplevel"></span><h1>Working with Engines and Connections<a class="headerlink" href="#module-sqlalchemy.engine" title="Permalink to this headline">¶</a></h1>
<p>This section details direct usage of the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>,
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, and related objects. Its important to note that when
using the SQLAlchemy ORM, these objects are not generally accessed; instead,
the <a class="reference internal" href="../orm/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 is used as the interface to the database.
However, for applications that are built around direct usage of textual SQL
statements and/or SQL expression constructs without involvement by the ORM’s
higher level management services, the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> and
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> are king (and queen?) - read on.</p>
<div class="section" id="basic-usage">
<h2>Basic Usage<a class="headerlink" href="#basic-usage" title="Permalink to this headline">¶</a></h2>
<p>Recall from <a class="reference internal" href="engines.html"><span class="doc">Engine Configuration</span></a> that an <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> is created via
the <a class="reference internal" href="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> call:</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="s1">'mysql://scott:tiger@localhost/test'</span><span class="p">)</span></pre></div>
</div>
<p>The typical usage of <a class="reference internal" href="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> is once per particular database
URL, held globally for the lifetime of a single application process. A single
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> manages many individual DBAPI connections on behalf of the
process and is intended to be called upon in a concurrent fashion. The
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> is <strong>not</strong> synonymous to the DBAPI <code class="docutils literal notranslate"><span class="pre">connect</span></code> function,
which represents just one connection resource - the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> is most
efficient when created just once at the module level of an application, not
per-object or per-function call.</p>
<p>For a multiple-process application that uses the <code class="docutils literal notranslate"><span class="pre">os.fork</span></code> system call, or
for example the Python <code class="docutils literal notranslate"><span class="pre">multiprocessing</span></code> module, it’s usually required that a
separate <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> be used for each child process. This is because the
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> maintains a reference to a connection pool that ultimately
references DBAPI connections - these tend to not be portable across process
boundaries. An <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> that is configured not to use pooling (which
is achieved via the usage of <a class="reference internal" href="pooling.html#sqlalchemy.pool.NullPool" title="sqlalchemy.pool.NullPool"><code class="xref py py-class docutils literal notranslate"><span class="pre">NullPool</span></code></a>) does not have this
requirement.</p>
<p>The engine can be used directly to issue SQL to the database. The most generic
way is first procure a connection resource, which you get via the
<a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.connect()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select username from users"</span><span class="p">)</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"username:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s1">'username'</span><span class="p">])</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>The connection is an instance of <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>,
which is a <strong>proxy</strong> object for an actual DBAPI connection. The DBAPI
connection is retrieved from the connection pool at the point at which
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is created.</p>
<p>The returned result is an instance of <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>, which
references a DBAPI cursor and provides a largely compatible interface
with that of the DBAPI cursor. The DBAPI cursor will be closed
by the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> when all of its result rows (if any) are
exhausted. A <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> that returns no rows, such as that of
an UPDATE statement (without any returned rows),
releases cursor resources immediately upon construction.</p>
<p>When the <a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">close()</span></code></a> method is called, the referenced DBAPI
connection is <a class="reference internal" href="../glossary.html#term-released"><span class="xref std std-term">released</span></a> to the connection pool. From the perspective
of the database itself, nothing is actually “closed”, assuming pooling is
in use. The pooling mechanism issues a <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> call on the DBAPI
connection so that any transactional state or locks are removed, and
the connection is ready for its next usage.</p>
<p>The above procedure can be performed in a shorthand way by using the
<a class="reference internal" href="#sqlalchemy.engine.Engine.execute" title="sqlalchemy.engine.Engine.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method of <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> itself:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">result</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select username from users"</span><span class="p">)</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"username:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s1">'username'</span><span class="p">])</span></pre></div>
</div>
<p>Where above, the <a class="reference internal" href="#sqlalchemy.engine.Engine.execute" title="sqlalchemy.engine.Engine.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method acquires a new
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> on its own, executes the statement with that object,
and returns the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>. In this case, the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>
contains a special flag known as <code class="docutils literal notranslate"><span class="pre">close_with_result</span></code>, which indicates
that when its underlying DBAPI cursor is closed, the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
object itself is also closed, which again returns the DBAPI connection
to the connection pool, releasing transactional resources.</p>
<p>If the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> potentially has rows remaining, it can be
instructed to close out its resources explicitly:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">result</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>If the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> has pending rows remaining and is dereferenced by
the application without being closed, Python garbage collection will
ultimately close out the cursor as well as trigger a return of the pooled
DBAPI connection resource to the pool (SQLAlchemy achieves this by the usage
of weakref callbacks - <em>never</em> the <code class="docutils literal notranslate"><span class="pre">__del__</span></code> method) - however it’s never a
good idea to rely upon Python garbage collection to manage resources.</p>
<p>Our example above illustrated the execution of a textual SQL string.
The <a class="reference internal" href="#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method can of course accommodate more than
that, including the variety of SQL expression constructs described
in <a class="reference internal" href="tutorial.html"><span class="std std-ref">SQL Expression Language Tutorial</span></a>.</p>
</div>
<div class="section" id="using-transactions">
<h2>Using Transactions<a class="headerlink" href="#using-transactions" title="Permalink to this headline">¶</a></h2>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>This section describes how to use transactions when working directly
with <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> and <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> objects. When using the
SQLAlchemy ORM, the public API for transaction control is via the
<a class="reference internal" href="../orm/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, which makes usage of the <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>
object internally. See <a class="reference internal" href="../orm/session_transaction.html#unitofwork-transaction"><span class="std std-ref">Managing Transactions</span></a> for further
information.</p>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object provides a <a class="reference internal" href="#sqlalchemy.engine.Connection.begin" title="sqlalchemy.engine.Connection.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin()</span></code></a>
method which returns a <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object.
This object is usually used within a try/except clause so that it is
guaranteed to invoke <a class="reference internal" href="#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> or <a class="reference internal" href="#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>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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="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="k">try</span><span class="p">:</span>
<span class="n">r1</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">col1</span><span class="o">=</span><span class="mi">7</span><span class="p">,</span> <span class="n">col2</span><span class="o">=</span><span class="s1">'this is some data'</span><span class="p">)</span>
<span class="n">trans</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">trans</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">raise</span></pre></div>
</div>
<p>The above block can be created more succinctly using context
managers, either given an <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># runs a transaction</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">connection</span><span class="p">:</span>
<span class="n">r1</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">col1</span><span class="o">=</span><span class="mi">7</span><span class="p">,</span> <span class="n">col2</span><span class="o">=</span><span class="s1">'this is some data'</span><span class="p">)</span></pre></div>
</div>
<p>Or from the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, in which case the <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object
is available as well:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">with</span> <span class="n">connection</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">trans</span><span class="p">:</span>
<span class="n">r1</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">col1</span><span class="o">=</span><span class="mi">7</span><span class="p">,</span> <span class="n">col2</span><span class="o">=</span><span class="s1">'this is some data'</span><span class="p">)</span></pre></div>
</div>
<div class="section" id="nesting-of-transaction-blocks">
<span id="connections-nested-transactions"></span><h3>Nesting of Transaction Blocks<a class="headerlink" href="#nesting-of-transaction-blocks" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object also handles “nested”
behavior by keeping track of the outermost begin/commit pair. In this example,
two functions both issue a transaction on a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, but only the outermost
<a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object actually takes effect when it is committed.</p>
<div class="highlight-python+sql 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">connection</span><span class="p">):</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"># open a transaction</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">method_b</span><span class="p">(</span><span class="n">connection</span><span class="p">)</span>
<span class="n">trans</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">trans</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c1"># this rolls back the transaction unconditionally</span>
<span class="k">raise</span>
<span class="c1"># method_b also starts a transaction</span>
<span class="k">def</span> <span class="nf">method_b</span><span class="p">(</span><span class="n">connection</span><span class="p">):</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"># open a transaction - this runs in the context of method_a's transaction</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into mytable values ('bat', 'lala')"</span><span class="p">)</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">mytable</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">col1</span><span class="o">=</span><span class="s1">'bat'</span><span class="p">,</span> <span class="n">col2</span><span class="o">=</span><span class="s1">'lala'</span><span class="p">)</span>
<span class="n">trans</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">trans</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c1"># this rolls back the transaction unconditionally</span>
<span class="k">raise</span>
<span class="c1"># open a Connection and call method_a</span>
<span class="n">conn</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="n">method_a</span><span class="p">(</span><span class="n">conn</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Above, <code class="docutils literal notranslate"><span class="pre">method_a</span></code> is called first, which calls <code class="docutils literal notranslate"><span class="pre">connection.begin()</span></code>. Then
it calls <code class="docutils literal notranslate"><span class="pre">method_b</span></code>. When <code class="docutils literal notranslate"><span class="pre">method_b</span></code> calls <code class="docutils literal notranslate"><span class="pre">connection.begin()</span></code>, it just
increments a counter that is decremented when it calls <code class="docutils literal notranslate"><span class="pre">commit()</span></code>. If either
<code class="docutils literal notranslate"><span class="pre">method_a</span></code> or <code class="docutils literal notranslate"><span class="pre">method_b</span></code> calls <code class="docutils literal notranslate"><span class="pre">rollback()</span></code>, the whole transaction is
rolled back. The transaction is not committed until <code class="docutils literal notranslate"><span class="pre">method_a</span></code> calls the
<code class="docutils literal notranslate"><span class="pre">commit()</span></code> method. This “nesting” behavior allows the creation of functions
which “guarantee” that a transaction will be used if one was not already
available, but will automatically participate in an enclosing transaction if
one exists.</p>
</div>
</div>
<div class="section" id="understanding-autocommit">
<span id="autocommit"></span><span id="index-0"></span><h2>Understanding Autocommit<a class="headerlink" href="#understanding-autocommit" title="Permalink to this headline">¶</a></h2>
<p>The previous transaction example illustrates how to use <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>
so that several executions can take part in the same transaction. What happens
when we issue an INSERT, UPDATE or DELETE call without using
<a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>? While some DBAPI
implementations provide various special “non-transactional” modes, the core
behavior of DBAPI per PEP-0249 is that a <em>transaction is always in progress</em>,
providing only <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> and <code class="docutils literal notranslate"><span class="pre">commit()</span></code> methods but no <code class="docutils literal notranslate"><span class="pre">begin()</span></code>.
SQLAlchemy assumes this is the case for any given DBAPI.</p>
<p>Given this requirement, SQLAlchemy implements its own “autocommit” feature which
works completely consistently across all backends. This is achieved by
detecting statements which represent data-changing operations, i.e. INSERT,
UPDATE, DELETE, as well as data definition language (DDL) statements such as
CREATE TABLE, ALTER TABLE, and then issuing a COMMIT automatically if no
transaction is in progress. The detection is based on the presence of the
<code class="docutils literal notranslate"><span class="pre">autocommit=True</span></code> execution option on the statement. If the statement
is a text-only statement and the flag is not set, a regular expression is used
to detect INSERT, UPDATE, DELETE, as well as a variety of other commands
for a particular backend:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">conn</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="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"INSERT INTO users VALUES (1, 'john')"</span><span class="p">)</span> <span class="c1"># autocommits</span></pre></div>
</div>
<p>The “autocommit” feature is only in effect when no <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> has
otherwise been declared. This means the feature is not generally used with
the ORM, as the <a class="reference internal" href="../orm/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 by default always maintains an
ongoing <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.</p>
<p>Full control of the “autocommit” behavior is available using the generative
<a class="reference internal" href="#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> method provided on <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>,
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>, <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Executable</span></code></a>, using the “autocommit” flag which will
turn on or off the autocommit for the selected scope. For example, a
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><code class="xref py py-func docutils literal notranslate"><span class="pre">text()</span></code></a> construct representing a stored procedure that commits might use
it so that a SELECT statement will issue a COMMIT:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s2">"SELECT my_mutating_procedure()"</span><span class="p">)</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span><span class="n">autocommit</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span></pre></div>
</div>
</div>
<div class="section" id="connectionless-execution-implicit-execution">
<span id="dbengine-implicit"></span><h2>Connectionless Execution, Implicit Execution<a class="headerlink" href="#connectionless-execution-implicit-execution" title="Permalink to this headline">¶</a></h2>
<p>Recall from the first section we mentioned executing with and without explicit
usage of <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>. “Connectionless” execution
refers to the usage of the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method on an object which is not a
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>. This was illustrated using the <a class="reference internal" href="#sqlalchemy.engine.Engine.execute" title="sqlalchemy.engine.Engine.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method
of <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">result</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select username from users"</span><span class="p">)</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"username:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s1">'username'</span><span class="p">])</span></pre></div>
</div>
<p>In addition to “connectionless” execution, it is also possible
to use the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable.execute" title="sqlalchemy.sql.expression.Executable.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method of
any <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Executable</span></code></a> construct, which is a marker for SQL expression objects
that support execution. The SQL expression object itself references an
<a class="reference internal" href="#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="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> known as the <strong>bind</strong>, which it uses
in order to provide so-called “implicit” execution services.</p>
<p>Given a table as below:</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">MetaData</span><span class="p">,</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span>
<span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">users_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'users'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>Explicit execution delivers the SQL text or constructed SQL expression to the
<a class="reference internal" href="#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method of <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>:</p>
<div class="highlight-python+sql 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="s1">'sqlite:///file.db'</span><span class="p">)</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="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="c1"># ....</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Explicit, connectionless execution delivers the expression to the
<a class="reference internal" href="#sqlalchemy.engine.Engine.execute" title="sqlalchemy.engine.Engine.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method of <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>:</p>
<div class="highlight-python+sql 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="s1">'sqlite:///file.db'</span><span class="p">)</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="c1"># ....</span>
<span class="n">result</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Implicit execution is also connectionless, and makes usage of the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable.execute" title="sqlalchemy.sql.expression.Executable.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a> method
on the expression itself. This method is provided as part of the
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Executable</span></code></a> class, which refers to a SQL statement that is sufficient
for being invoked against the database. The method makes usage of
the assumption that either an
<a class="reference internal" href="#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="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> has been <strong>bound</strong> to the expression
object. By “bound” we mean that the special attribute <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.bind" title="sqlalchemy.schema.MetaData.bind"><code class="xref py py-attr docutils literal notranslate"><span class="pre">MetaData.bind</span></code></a>
has been used to associate a series of
<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> objects and all SQL constructs derived from them with a specific
engine:</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="s1">'sqlite:///file.db'</span><span class="p">)</span>
<span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">engine</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">users_table</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">execute</span><span class="p">()</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="c1"># ....</span>
<span class="n">result</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Above, we associate an <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> with a <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a> object using
the special attribute <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.bind" title="sqlalchemy.schema.MetaData.bind"><code class="xref py py-attr docutils literal notranslate"><span class="pre">MetaData.bind</span></code></a>. The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> construct produced
from the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> object has a method <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable.execute" title="sqlalchemy.sql.expression.Executable.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a>, which will
search for an <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> that’s “bound” to the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>.</p>
<p>Overall, the usage of “bound metadata” has three general effects:</p>
<ul class="simple">
<li><p>SQL statement objects gain an <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable.execute" title="sqlalchemy.sql.expression.Executable.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Executable.execute()</span></code></a> method which automatically
locates a “bind” with which to execute themselves.</p></li>
<li><p>The ORM <a class="reference internal" href="../orm/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 supports using “bound metadata” in order
to establish which <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> should be used to invoke SQL statements
on behalf of a particular mapped class, though the <a class="reference internal" href="../orm/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 its own explicit system of establishing complex <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>/
mapped class configurations.</p></li>
<li><p>The <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.create_all()</span></code></a>, <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.drop_all()</span></code></a>, <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.create" title="sqlalchemy.schema.Table.create"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.create()</span></code></a>,
<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.drop" title="sqlalchemy.schema.Table.drop"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.drop()</span></code></a>, and “autoload” features all make usage of the bound
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> automatically without the need to pass it explicitly.</p></li>
</ul>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The concepts of “bound metadata” and “implicit execution” are not emphasized in modern SQLAlchemy.
While they offer some convenience, they are no longer required by any API and
are never necessary.</p>
<p>In applications where multiple <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> objects are present, each one logically associated
with a certain set of tables (i.e. <em>vertical sharding</em>), the “bound metadata” technique can be used
so that individual <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> can refer to the appropriate <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> automatically;
in particular this is supported within the ORM via the <a class="reference internal" href="../orm/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
as a means to associate <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> objects with an appropriate <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>,
as an alternative to using the bind arguments accepted directly by the <a class="reference internal" href="../orm/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>.</p>
<p>However, the “implicit execution” technique is not at all appropriate for use with the
ORM, as it bypasses the transactional context maintained by the <a class="reference internal" href="../orm/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>.</p>
<p>Overall, in the <em>vast majority</em> of cases, “bound metadata” and “implicit execution”
are <strong>not useful</strong>. While “bound metadata” has a marginal level of usefulness with regards to
ORM configuration, “implicit execution” is a very old usage pattern that in most
cases is more confusing than it is helpful, and its usage is discouraged.
Both patterns seem to encourage the overuse of expedient “short cuts” in application design
which lead to problems later on.</p>
<p>Modern SQLAlchemy usage, especially the ORM, places a heavy stress on working within the context
of a transaction at all times; the “implicit execution” concept makes the job of
associating statement execution with a particular transaction much more difficult.
The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable.execute" title="sqlalchemy.sql.expression.Executable.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Executable.execute()</span></code></a> method on a particular SQL statement
usually implies that the execution is not part of any particular transaction, which is
usually not the desired effect.</p>
</div>
<p>In both “connectionless” examples, the
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is created behind the scenes; the
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> returned by the <code class="docutils literal notranslate"><span class="pre">execute()</span></code>
call references the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> used to issue
the SQL statement. When the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> is closed, the underlying
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is closed for us, resulting in the
DBAPI connection being returned to the pool with transactional resources removed.</p>
</div>
<div class="section" id="translation-of-schema-names">
<span id="schema-translating"></span><h2>Translation of Schema Names<a class="headerlink" href="#translation-of-schema-names" title="Permalink to this headline">¶</a></h2>
<p>To support multi-tenancy applications that distribute common sets of tables
into multiple schemas, the
<a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.schema_translate_map" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.schema_translate_map</span></code></a>
execution option may be used to repurpose a set of <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> objects
to render under different schema names without any changes.</p>
<p>Given a table:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">user_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span>
<span class="s1">'user'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>The “schema” of this <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> as defined by the
<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.params.schema" title="sqlalchemy.schema.Table"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Table.schema</span></code></a> attribute is <code class="docutils literal notranslate"><span class="pre">None</span></code>. The
<a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.schema_translate_map" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.schema_translate_map</span></code></a> can specify
that all <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> objects with a schema of <code class="docutils literal notranslate"><span class="pre">None</span></code> would instead
render the schema as <code class="docutils literal notranslate"><span class="pre">user_schema_one</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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="o">.</span><span class="n">execution_options</span><span class="p">(</span>
<span class="n">schema_translate_map</span><span class="o">=</span><span class="p">{</span><span class="kc">None</span><span class="p">:</span> <span class="s2">"user_schema_one"</span><span class="p">})</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">user_table</span><span class="o">.</span><span class="n">select</span><span class="p">())</span></pre></div>
</div>
<p>The above code will invoke SQL on the database of the form:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">user_schema_one</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">user_schema_one</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">name</span> <span class="n">FROM</span>
<span class="n">user_schema</span><span class="o">.</span><span class="n">user</span></pre></div>
</div>
<p>That is, the schema name is substituted with our translated name. The
map can specify any number of target->destination schemas:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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="o">.</span><span class="n">execution_options</span><span class="p">(</span>
<span class="n">schema_translate_map</span><span class="o">=</span><span class="p">{</span>
<span class="kc">None</span><span class="p">:</span> <span class="s2">"user_schema_one"</span><span class="p">,</span> <span class="c1"># no schema name -> "user_schema_one"</span>
<span class="s2">"special"</span><span class="p">:</span> <span class="s2">"special_schema"</span><span class="p">,</span> <span class="c1"># schema="special" becomes "special_schema"</span>
<span class="s2">"public"</span><span class="p">:</span> <span class="kc">None</span> <span class="c1"># Table objects with schema="public" will render with no schema</span>
<span class="p">})</span></pre></div>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.schema_translate_map" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.schema_translate_map</span></code></a> parameter
affects all DDL and SQL constructs generated from the SQL expression language,
as derived from the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> or <a class="reference internal" href="defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><code class="xref py py-class docutils literal notranslate"><span class="pre">Sequence</span></code></a> objects.
It does <strong>not</strong> impact literal string SQL used via the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><code class="xref py py-func docutils literal notranslate"><span class="pre">expression.text()</span></code></a>
construct nor via plain strings passed to <a class="reference internal" href="#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execute()</span></code></a>.</p>
<p>The feature takes effect <strong>only</strong> in those cases where the name of the
schema is derived directly from that of a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> or <a class="reference internal" href="defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><code class="xref py py-class docutils literal notranslate"><span class="pre">Sequence</span></code></a>;
it does not impact methods where a string schema name is passed directly.
By this pattern, it takes effect within the “can create” / “can drop” checks
performed by methods such as <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.create_all()</span></code></a> or
<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.drop_all()</span></code></a> are called, and it takes effect when
using table reflection given a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> object. However it does
<strong>not</strong> affect the operations present on the <a class="reference internal" href="reflection.html#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><code class="xref py py-class docutils literal notranslate"><span class="pre">Inspector</span></code></a> object,
as the schema name is passed to these methods explicitly.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
</div>
<div class="section" id="engine-disposal">
<span id="id1"></span><h2>Engine Disposal<a class="headerlink" href="#engine-disposal" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> refers to a connection pool, which means under normal
circumstances, there are open database connections present while the
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object is still resident in memory. When an <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>
is garbage collected, its connection pool is no longer referred to by
that <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>, and assuming none of its connections are still checked
out, the pool and its connections will also be garbage collected, which has the
effect of closing out the actual database connections as well. But otherwise,
the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> will hold onto open database connections assuming
it uses the normally default pool implementation of <a class="reference internal" href="pooling.html#sqlalchemy.pool.QueuePool" title="sqlalchemy.pool.QueuePool"><code class="xref py py-class docutils literal notranslate"><span class="pre">QueuePool</span></code></a>.</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> is intended to normally be a permanent
fixture established up-front and maintained throughout the lifespan of an
application. It is <strong>not</strong> intended to be created and disposed on a
per-connection basis; it is instead a registry that maintains both a pool
of connections as well as configurational information about the database
and DBAPI in use, as well as some degree of internal caching of per-database
resources.</p>
<p>However, there are many cases where it is desirable that all connection resources
referred to by the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> be completely closed out. It’s
generally not a good idea to rely on Python garbage collection for this
to occur for these cases; instead, the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> can be explicitly disposed using
the <a class="reference internal" href="#sqlalchemy.engine.Engine.dispose" title="sqlalchemy.engine.Engine.dispose"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.dispose()</span></code></a> method. This disposes of the engine’s
underlying connection pool and replaces it with a new one that’s empty.
Provided that the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>
is discarded at this point and no longer used, all <strong>checked-in</strong> connections
which it refers to will also be fully closed.</p>
<p>Valid use cases for calling <a class="reference internal" href="#sqlalchemy.engine.Engine.dispose" title="sqlalchemy.engine.Engine.dispose"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.dispose()</span></code></a> include:</p>
<ul class="simple">
<li><p>When a program wants to release any remaining checked-in connections
held by the connection pool and expects to no longer be connected
to that database at all for any future operations.</p></li>
<li><p>When a program uses multiprocessing or <code class="docutils literal notranslate"><span class="pre">fork()</span></code>, and an
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object is copied to the child process,
<a class="reference internal" href="#sqlalchemy.engine.Engine.dispose" title="sqlalchemy.engine.Engine.dispose"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.dispose()</span></code></a> should be called so that the engine creates
brand new database connections local to that fork. Database connections
generally do <strong>not</strong> travel across process boundaries.</p></li>
<li><p>Within test suites or multitenancy scenarios where many
ad-hoc, short-lived <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> objects may be created and disposed.</p></li>
</ul>
<p>Connections that are <strong>checked out</strong> are <strong>not</strong> discarded when the
engine is disposed or garbage collected, as these connections are still
strongly referenced elsewhere by the application.
However, after <a class="reference internal" href="#sqlalchemy.engine.Engine.dispose" title="sqlalchemy.engine.Engine.dispose"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.dispose()</span></code></a> is called, those
connections are no longer associated with that <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>; when they
are closed, they will be returned to their now-orphaned connection pool
which will ultimately be garbage collected, once all connections which refer
to it are also no longer referenced anywhere.
Since this process is not easy to control, it is strongly recommended that
<a class="reference internal" href="#sqlalchemy.engine.Engine.dispose" title="sqlalchemy.engine.Engine.dispose"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.dispose()</span></code></a> is called only after all checked out connections
are checked in or otherwise de-associated from their pool.</p>
<p>An alternative for applications that are negatively impacted by the
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object’s use of connection pooling is to disable pooling
entirely. This typically incurs only a modest performance impact upon the
use of new connections, and means that when a connection is checked in,
it is entirely closed out and is not held in memory. See <a class="reference internal" href="pooling.html#pool-switching"><span class="std std-ref">Switching Pool Implementations</span></a>
for guidelines on how to disable pooling.</p>
</div>
<div class="section" id="using-the-threadlocal-execution-strategy">
<span id="threadlocal-strategy"></span><h2>Using the Threadlocal Execution Strategy<a class="headerlink" href="#using-the-threadlocal-execution-strategy" title="Permalink to this headline">¶</a></h2>
<p>The “threadlocal” engine strategy is an optional feature which
can be used by non-ORM applications to associate transactions
with the current thread, such that all parts of the
application can participate in that transaction implicitly without the need to
explicitly reference a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The “threadlocal” feature is generally discouraged. It’s
designed for a particular pattern of usage which is generally
considered as a legacy pattern. It has <strong>no impact</strong> on the “thread safety”
of SQLAlchemy components
or one’s application. It also should not be used when using an ORM
<a class="reference internal" href="../orm/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, as the
<a class="reference internal" href="../orm/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> itself represents an ongoing
transaction and itself handles the job of maintaining connection and
transactional resources.</p>
</div>
<p>Enabling <code class="docutils literal notranslate"><span class="pre">threadlocal</span></code> is achieved as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'mysql://localhost/test'</span><span class="p">,</span> <span class="n">strategy</span><span class="o">=</span><span class="s1">'threadlocal'</span><span class="p">)</span></pre></div>
</div>
<p>The above <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> will now acquire a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> using
connection resources derived from a thread-local variable whenever
<a class="reference internal" href="#sqlalchemy.engine.Engine.execute" title="sqlalchemy.engine.Engine.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.execute()</span></code></a> or <a class="reference internal" href="#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> is called. This
connection resource is maintained as long as it is referenced, which allows
multiple points of an application to share a transaction while using
connectionless execution:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">call_operation1</span><span class="p">():</span>
<span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into users values (?, ?)"</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s2">"john"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">call_operation2</span><span class="p">():</span>
<span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="mi">5</span><span class="p">)</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'ed'</span><span class="p">)</span>
<span class="n">db</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">call_operation1</span><span class="p">()</span>
<span class="n">call_operation2</span><span class="p">()</span>
<span class="n">db</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">db</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span></pre></div>
</div>
<p>Explicit execution can be mixed with connectionless execution by
using the <a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.connect()</span></code></a> method to acquire a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
that is not part of the threadlocal scope:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">db</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">log_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">message</span><span class="o">=</span><span class="s2">"Operation started"</span><span class="p">)</span>
<span class="n">call_operation1</span><span class="p">()</span>
<span class="n">call_operation2</span><span class="p">()</span>
<span class="n">db</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">log_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">message</span><span class="o">=</span><span class="s2">"Operation succeeded"</span><span class="p">)</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">db</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">log_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">message</span><span class="o">=</span><span class="s2">"Operation failed"</span><span class="p">)</span>
<span class="k">finally</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>To access the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> that is bound to the threadlocal scope,
call <a class="reference internal" href="#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>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">conn</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">contextual_connect</span><span class="p">()</span>
<span class="n">call_operation3</span><span class="p">(</span><span class="n">conn</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Calling <a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">close()</span></code></a> on the “contextual” connection does not <a class="reference internal" href="../glossary.html#term-release"><span class="xref std std-term">release</span></a>
its resources until all other usages of that resource are closed as well, including
that any ongoing transactions are rolled back or committed.</p>
</div>
<div class="section" id="working-with-raw-dbapi-connections">
<span id="dbapi-connections"></span><h2>Working with Raw DBAPI Connections<a class="headerlink" href="#working-with-raw-dbapi-connections" title="Permalink to this headline">¶</a></h2>
<p>There are some cases where SQLAlchemy does not provide a genericized way
at accessing some <a class="reference internal" href="../glossary.html#term-dbapi"><span class="xref std std-term">DBAPI</span></a> functions, such as calling stored procedures as well
as dealing with multiple result sets. In these cases, it’s just as expedient
to deal with the raw DBAPI connection directly.</p>
<p>The most common way to access the raw DBAPI connection is to get it
from an already present <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object directly. It is
present using the <a class="reference internal" href="#sqlalchemy.engine.Connection.connection" title="sqlalchemy.engine.Connection.connection"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Connection.connection</span></code></a> attribute:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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="n">dbapi_conn</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">connection</span></pre></div>
</div>
<p>The DBAPI connection here is actually a “proxied” in terms of the
originating connection pool, however this is an implementation detail
that in most cases can be ignored. As this DBAPI connection is still
contained within the scope of an owning <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object, it is
best to make use of the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object for most features such
as transaction control as well as calling the <a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.close()</span></code></a>
method; if these operations are performed on the DBAPI connection directly,
the owning <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> will not be aware of these changes in state.</p>
<p>To overcome the limitations imposed by the DBAPI connection that is
maintained by an owning <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, a DBAPI connection is also
available without the need to procure a
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> first, using the <a class="reference internal" href="#sqlalchemy.engine.Engine.raw_connection" title="sqlalchemy.engine.Engine.raw_connection"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.raw_connection()</span></code></a> method
of <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">dbapi_conn</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">raw_connection</span><span class="p">()</span></pre></div>
</div>
<p>This DBAPI connection is again a “proxied” form as was the case before.
The purpose of this proxying is now apparent, as when we call the <code class="docutils literal notranslate"><span class="pre">.close()</span></code>
method of this connection, the DBAPI connection is typically not actually
closed, but instead <a class="reference internal" href="../glossary.html#term-released"><span class="xref std std-term">released</span></a> back to the
engine’s connection pool:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">dbapi_conn</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>While SQLAlchemy may in the future add built-in patterns for more DBAPI
use cases, there are diminishing returns as these cases tend to be rarely
needed and they also vary highly dependent on the type of DBAPI in use,
so in any case the direct DBAPI calling pattern is always there for those
cases where it is needed.</p>
<p>Some recipes for DBAPI connection use follow.</p>
<div class="section" id="calling-stored-procedures">
<span id="stored-procedures"></span><h3>Calling Stored Procedures<a class="headerlink" href="#calling-stored-procedures" title="Permalink to this headline">¶</a></h3>
<p>For stored procedures with special syntactical or parameter concerns,
DBAPI-level <a class="reference external" href="http://legacy.python.org/dev/peps/pep-0249/#callproc">callproc</a>
may be used:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">raw_connection</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">cursor</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">callproc</span><span class="p">(</span><span class="s2">"my_procedure"</span><span class="p">,</span> <span class="p">[</span><span class="s1">'x'</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">,</span> <span class="s1">'z'</span><span class="p">])</span>
<span class="n">results</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span><span class="n">cursor</span><span class="o">.</span><span class="n">fetchall</span><span class="p">())</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
<span class="n">connection</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">finally</span><span class="p">:</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="multiple-result-sets">
<h3>Multiple Result Sets<a class="headerlink" href="#multiple-result-sets" title="Permalink to this headline">¶</a></h3>
<p>Multiple result set support is available from a raw DBAPI cursor using the
<a class="reference external" href="http://legacy.python.org/dev/peps/pep-0249/#nextset">nextset</a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">raw_connection</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">cursor</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"select * from table1; select * from table2"</span><span class="p">)</span>
<span class="n">results_one</span> <span class="o">=</span> <span class="n">cursor</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">nextset</span><span class="p">()</span>
<span class="n">results_two</span> <span class="o">=</span> <span class="n">cursor</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
<span class="k">finally</span><span class="p">:</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
</div>
</div>
<div class="section" id="registering-new-dialects">
<h2>Registering New Dialects<a class="headerlink" href="#registering-new-dialects" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="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> function call locates the given dialect
using setuptools entrypoints. These entry points can be established
for third party dialects within the setup.py script. For example,
to create a new dialect “foodialect://”, the steps are as follows:</p>
<ol class="arabic">
<li><p>Create a package called <code class="docutils literal notranslate"><span class="pre">foodialect</span></code>.</p></li>
<li><p>The package should have a module containing the dialect class,
which is typically a subclass of <a class="reference internal" href="internals.html#sqlalchemy.engine.default.DefaultDialect" title="sqlalchemy.engine.default.DefaultDialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.engine.default.DefaultDialect</span></code></a>.
In this example let’s say it’s called <code class="docutils literal notranslate"><span class="pre">FooDialect</span></code> and its module is accessed
via <code class="docutils literal notranslate"><span class="pre">foodialect.dialect</span></code>.</p></li>
<li><p>The entry point can be established in setup.py as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">entry_points</span><span class="o">=</span><span class="s2">"""</span>
<span class="s2">[sqlalchemy.dialects]</span>
<span class="s2">foodialect = foodialect.dialect:FooDialect</span>
<span class="s2">"""</span></pre></div>
</div>
</li>
</ol>
<p>If the dialect is providing support for a particular DBAPI on top of
an existing SQLAlchemy-supported database, the name can be given
including a database-qualification. For example, if <code class="docutils literal notranslate"><span class="pre">FooDialect</span></code>
were in fact a MySQL dialect, the entry point could be established like this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">entry_points</span><span class="o">=</span><span class="s2">"""</span>
<span class="s2">[sqlalchemy.dialects]</span>
<span class="s2">mysql.foodialect = foodialect.dialect:FooDialect</span>
<span class="s2">"""</span></pre></div>
</div>
<p>The above entrypoint would then be accessed as <code class="docutils literal notranslate"><span class="pre">create_engine("mysql+foodialect://")</span></code>.</p>
<div class="section" id="registering-dialects-in-process">
<h3>Registering Dialects In-Process<a class="headerlink" href="#registering-dialects-in-process" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy also allows a dialect to be registered within the current process, bypassing
the need for separate installation. Use the <code class="docutils literal notranslate"><span class="pre">register()</span></code> function as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects</span> <span class="k">import</span> <span class="n">registry</span>
<span class="n">registry</span><span class="o">.</span><span class="n">register</span><span class="p">(</span><span class="s2">"mysql.foodialect"</span><span class="p">,</span> <span class="s2">"myapp.dialect"</span><span class="p">,</span> <span class="s2">"MyMySQLDialect"</span><span class="p">)</span></pre></div>
</div>
<p>The above will respond to <code class="docutils literal notranslate"><span class="pre">create_engine("mysql+foodialect://")</span></code> and load the
<code class="docutils literal notranslate"><span class="pre">MyMySQLDialect</span></code> class from the <code class="docutils literal notranslate"><span class="pre">myapp.dialect</span></code> module.</p>
</div>
</div>
<div class="section" id="connection-engine-api">
<h2>Connection / Engine API<a class="headerlink" href="#connection-engine-api" title="Permalink to this headline">¶</a></h2>
<dl class="class">
<dt id="sqlalchemy.engine.Connection">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">Connection</code><span class="sig-paren">(</span><em>engine</em>, <em>connection=None</em>, <em>close_with_result=False</em>, <em>_branch_from=None</em>, <em>_execution_options=None</em>, <em>_dispatch=None</em>, <em>_has_events=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.engine.Connectable</span></code></a></p>
<p>Provides high-level functionality for a wrapped DB-API connection.</p>
<p>Provides execution support for string-based SQL statements as well as
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ClauseElement</span></code></a>, <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><code class="xref py py-class docutils literal notranslate"><span class="pre">Compiled</span></code></a> and <a class="reference internal" href="defaults.html#sqlalchemy.schema.DefaultGenerator" title="sqlalchemy.schema.DefaultGenerator"><code class="xref py py-class docutils literal notranslate"><span class="pre">DefaultGenerator</span></code></a>
objects. Provides a <a class="reference internal" href="#sqlalchemy.engine.Connection.begin" title="sqlalchemy.engine.Connection.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin()</span></code></a> method to return <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>
objects.</p>
<p>The Connection object is <strong>not</strong> thread-safe. While a Connection can be
shared among threads using properly synchronized access, it is still
possible that the underlying DBAPI connection may not support shared
access between threads. Check the DBAPI documentation for details.</p>
<p>The Connection object represents a single dbapi connection checked out
from the connection pool. In this state, the connection pool has no affect
upon the connection, including its expiration or timeout state. For the
connection pool to properly manage connections, connections should be
returned to the connection pool (i.e. <code class="docutils literal notranslate"><span class="pre">connection.close()</span></code>) whenever the
connection is not in use.</p>
<span class="target" id="index-1"></span><dl class="method">
<dt id="sqlalchemy.engine.Connection.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>engine</em>, <em>connection=None</em>, <em>close_with_result=False</em>, <em>_branch_from=None</em>, <em>_execution_options=None</em>, <em>_dispatch=None</em>, <em>_has_events=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a new Connection.</p>
<p>The constructor here is not public and is only called only by an
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>. See <a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.connect()</span></code></a> and
<a class="reference internal" href="#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> methods.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.begin">
<code class="descname">begin</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.begin" title="Permalink to this definition">¶</a></dt>
<dd><p>Begin a transaction and return a transaction handle.</p>
<p>The returned object is an instance of <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.
This object represents the “scope” of the transaction,
which completes when either the <a class="reference internal" href="#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>
or <a class="reference internal" href="#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> method is called.</p>
<p>Nested calls to <a class="reference internal" href="#sqlalchemy.engine.Connection.begin" title="sqlalchemy.engine.Connection.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin()</span></code></a> on the same <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
will return new <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> objects that represent
an emulated transaction within the scope of the enclosing
transaction, that is:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">trans</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="c1"># outermost transaction</span>
<span class="n">trans2</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="c1"># "nested"</span>
<span class="n">trans2</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c1"># does nothing</span>
<span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c1"># actually commits</span></pre></div>
</div>
<p>Calls to <a class="reference internal" href="#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> only have an effect
when invoked via the outermost <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object, though the
<a class="reference internal" href="#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> method of any of the
<a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> objects will roll back the
transaction.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.begin_nested" title="sqlalchemy.engine.Connection.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.begin_nested()</span></code></a> - use a SAVEPOINT</p>
<p><a class="reference internal" href="#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> -
use a two phase /XID transaction</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Engine.begin" title="sqlalchemy.engine.Engine.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.begin()</span></code></a> - context manager available from
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.begin_nested">
<code class="descname">begin_nested</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.begin_nested" title="Permalink to this definition">¶</a></dt>
<dd><p>Begin a nested transaction and return a transaction handle.</p>
<p>The returned object is an instance of <a class="reference internal" href="#sqlalchemy.engine.NestedTransaction" title="sqlalchemy.engine.NestedTransaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">NestedTransaction</span></code></a>.</p>
<p>Nested transactions require SAVEPOINT support in the
underlying database. Any transaction in the hierarchy may
<code class="docutils literal notranslate"><span class="pre">commit</span></code> and <code class="docutils literal notranslate"><span class="pre">rollback</span></code>, however the outermost transaction
still controls the overall <code class="docutils literal notranslate"><span class="pre">commit</span></code> or <code class="docutils literal notranslate"><span class="pre">rollback</span></code> of the
transaction of a whole.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#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></p>
<p><a class="reference internal" href="#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></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.begin_twophase">
<code class="descname">begin_twophase</code><span class="sig-paren">(</span><em>xid=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.begin_twophase" title="Permalink to this definition">¶</a></dt>
<dd><p>Begin a two-phase or XA transaction and return a transaction
handle.</p>
<p>The returned object is an instance of <a class="reference internal" href="#sqlalchemy.engine.TwoPhaseTransaction" title="sqlalchemy.engine.TwoPhaseTransaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">TwoPhaseTransaction</span></code></a>,
which in addition to the methods provided by
<a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>, also provides a
<a class="reference internal" href="#sqlalchemy.engine.TwoPhaseTransaction.prepare" title="sqlalchemy.engine.TwoPhaseTransaction.prepare"><code class="xref py py-meth docutils literal notranslate"><span class="pre">prepare()</span></code></a> method.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.engine.Connection.begin_twophase.params.xid"></span><strong>xid</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.begin_twophase.params.xid">¶</a> – the two phase transaction id. If not supplied, a
random id will be generated.</p>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#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></p>
<p><a class="reference internal" href="#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></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.close">
<code class="descname">close</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.close" title="Permalink to this definition">¶</a></dt>
<dd><p>Close this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<p>This results in a release of the underlying database
resources, that is, the DBAPI connection referenced
internally. The DBAPI connection is typically restored
back to the connection-holding <a class="reference internal" href="pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><code class="xref py py-class docutils literal notranslate"><span class="pre">Pool</span></code></a> referenced
by the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> that produced this
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>. Any transactional state present on
the DBAPI connection is also unconditionally released via
the DBAPI connection’s <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> method, regardless
of any <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object that may be
outstanding with regards to this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<p>After <a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">close()</span></code></a> is called, the
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is permanently in a closed state,
and will allow no further operations.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Connection.closed">
<code class="descname">closed</code><a class="headerlink" href="#sqlalchemy.engine.Connection.closed" title="Permalink to this definition">¶</a></dt>
<dd><p>Return True if this connection is closed.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.connect">
<code class="descname">connect</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.connect" title="Permalink to this definition">¶</a></dt>
<dd><p>Returns a branched version of this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.close()</span></code></a> method on the returned
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> can be called and this
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> will remain open.</p>
<p>This method provides usage symmetry with
<a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.connect()</span></code></a>, including for usage
with context managers.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Connection.connection">
<code class="descname">connection</code><a class="headerlink" href="#sqlalchemy.engine.Connection.connection" title="Permalink to this definition">¶</a></dt>
<dd><p>The underlying DB-API connection managed by this Connection.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#dbapi-connections"><span class="std std-ref">Working with Raw DBAPI Connections</span></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.contextual_connect">
<code class="descname">contextual_connect</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.contextual_connect" title="Permalink to this definition">¶</a></dt>
<dd><p>Returns a branched version of this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.close()</span></code></a> method on the returned
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> can be called and this
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> will remain open.</p>
<p>This method provides usage symmetry with
<a class="reference internal" href="#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>, including for usage
with context managers.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Connection.default_isolation_level">
<code class="descname">default_isolation_level</code><a class="headerlink" href="#sqlalchemy.engine.Connection.default_isolation_level" title="Permalink to this definition">¶</a></dt>
<dd><p>The default isolation level assigned to this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<p>This is the isolation level setting that the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
has when first procured via the <a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.connect()</span></code></a> method.
This level stays in place until the
<a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.isolation_level" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.isolation_level</span></code></a> is used
to change the setting on a per-<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> basis.</p>
<p>Unlike <a class="reference internal" href="#sqlalchemy.engine.Connection.get_isolation_level" title="sqlalchemy.engine.Connection.get_isolation_level"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.get_isolation_level()</span></code></a>, this attribute is set
ahead of time from the first connection procured by the dialect,
so SQL query is not invoked when this accessor is called.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.9.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.get_isolation_level" title="sqlalchemy.engine.Connection.get_isolation_level"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.get_isolation_level()</span></code></a> - view current level</p>
<p><a class="reference internal" href="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>
- set per <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> isolation level</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.isolation_level" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.isolation_level</span></code></a>
- set per <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> isolation level</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.detach">
<code class="descname">detach</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.detach" title="Permalink to this definition">¶</a></dt>
<dd><p>Detach the underlying DB-API connection from its connection pool.</p>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">detach</span><span class="p">()</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"SET search_path TO schema1, schema2"</span><span class="p">)</span>
<span class="c1"># work with connection</span>
<span class="c1"># connection is fully closed (since we used "with:", can</span>
<span class="c1"># also call .close())</span></pre></div>
</div>
<p>This <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> instance will remain usable. When closed
(or exited from a context manager context as above),
the DB-API connection will be literally closed and not
returned to its originating pool.</p>
<p>This method can be used to insulate the rest of an application
from a modified state on a connection (such as a transaction
isolation level or similar).</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.execute">
<code class="descname">execute</code><span class="sig-paren">(</span><em>object_</em>, <em>*multiparams</em>, <em>**params</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.execute" title="Permalink to this definition">¶</a></dt>
<dd><p>Executes a SQL statement construct and returns a
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.engine.Connection.execute.params.object"></span><strong>object</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execute.params.object">¶</a> – <p>The statement to be executed. May be
one of:</p>
<ul>
<li><p>a plain string</p></li>
<li><p>any <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ClauseElement</span></code></a> construct that is also
a subclass of <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Executable</span></code></a>, such as a
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> construct</p></li>
<li><p>a <a class="reference internal" href="functions.html#sqlalchemy.sql.functions.FunctionElement" title="sqlalchemy.sql.functions.FunctionElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">FunctionElement</span></code></a>, such as that generated
by <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><code class="xref py py-data docutils literal notranslate"><span class="pre">func</span></code></a>, will be automatically wrapped in
a SELECT statement, which is then executed.</p></li>
<li><p>a <a class="reference internal" href="ddl.html#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLElement</span></code></a> object</p></li>
<li><p>a <a class="reference internal" href="defaults.html#sqlalchemy.schema.DefaultGenerator" title="sqlalchemy.schema.DefaultGenerator"><code class="xref py py-class docutils literal notranslate"><span class="pre">DefaultGenerator</span></code></a> object</p></li>
<li><p>a <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><code class="xref py py-class docutils literal notranslate"><span class="pre">Compiled</span></code></a> object</p></li>
</ul>
</p></li>
<li><p><span class="target" id="sqlalchemy.engine.Connection.execute.params.*multiparams/**params"></span><strong>*multiparams/**params</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execute.params.*multiparams/**params">¶</a> – <p>represent bound parameter
values to be used in the execution. Typically,
the format is either a collection of one or more
dictionaries passed to *multiparams:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="p">{</span><span class="s2">"id"</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span> <span class="s2">"value"</span><span class="p">:</span><span class="s2">"v1"</span><span class="p">},</span>
<span class="p">{</span><span class="s2">"id"</span><span class="p">:</span><span class="mi">2</span><span class="p">,</span> <span class="s2">"value"</span><span class="p">:</span><span class="s2">"v2"</span><span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p>…or individual key/values interpreted by **params:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="nb">id</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span> <span class="n">value</span><span class="o">=</span><span class="s2">"v1"</span>
<span class="p">)</span></pre></div>
</div>
<p>In the case that a plain SQL string is passed, and the underlying
DBAPI accepts positional bind parameters, a collection of tuples
or individual values in *multiparams may be passed:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="s2">"INSERT INTO table (id, value) VALUES (?, ?)"</span><span class="p">,</span>
<span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s2">"v1"</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s2">"v2"</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="s2">"INSERT INTO table (id, value) VALUES (?, ?)"</span><span class="p">,</span>
<span class="mi">1</span><span class="p">,</span> <span class="s2">"v1"</span>
<span class="p">)</span></pre></div>
</div>
<p>Note above, the usage of a question mark “?” or other
symbol is contingent upon the “paramstyle” accepted by the DBAPI
in use, which may be any of “qmark”, “named”, “pyformat”, “format”,
“numeric”. See <a class="reference external" href="http://www.python.org/dev/peps/pep-0249/">pep-249</a>
for details on paramstyle.</p>
<p>To execute a textual SQL statement which uses bound parameters in a
DBAPI-agnostic way, use the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><code class="xref py py-func docutils literal notranslate"><span class="pre">text()</span></code></a> construct.</p>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.execution_options">
<code class="descname">execution_options</code><span class="sig-paren">(</span><em>**opt</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.execution_options" title="Permalink to this definition">¶</a></dt>
<dd><p>Set non-SQL options for the connection which take effect
during execution.</p>
<p>The method returns a copy of this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> which references
the same underlying DBAPI connection, but also defines the given
execution options which will take effect for a call to
<a class="reference internal" href="#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">execute()</span></code></a>. As the new <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> references the same
underlying resource, it’s usually a good idea to ensure that the copies
will be discarded immediately, which is implicit if used as in:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span><span class="n">stream_results</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span><span class="o">.</span>\
<span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span></pre></div>
</div>
<p>Note that any key/value can be passed to
<a class="reference internal" href="#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>, and it will be stored in the
<code class="docutils literal notranslate"><span class="pre">_execution_options</span></code> dictionary of the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>. It
is suitable for usage by end-user schemes to communicate with
event listeners, for example.</p>
<p>The keywords that are currently recognized by SQLAlchemy itself
include all those listed under <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable.execution_options" title="sqlalchemy.sql.expression.Executable.execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Executable.execution_options()</span></code></a>,
as well as others that are specific to <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.engine.Connection.execution_options.params.autocommit"></span><strong>autocommit</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.autocommit">¶</a> – Available on: Connection, statement.
When True, a COMMIT will be invoked after execution
when executed in ‘autocommit’ mode, i.e. when an explicit
transaction is not begun on the connection. Note that DBAPI
connections by default are always in a transaction - SQLAlchemy uses
rules applied to different kinds of statements to determine if
COMMIT will be invoked in order to provide its “autocommit” feature.
Typically, all INSERT/UPDATE/DELETE statements as well as
CREATE/DROP statements have autocommit behavior enabled; SELECT
constructs do not. Use this option when invoking a SELECT or other
specific SQL construct where COMMIT is desired (typically when
calling stored procedures and such), and an explicit
transaction is not in progress.</p></li>
<li><p><span class="target" id="sqlalchemy.engine.Connection.execution_options.params.compiled_cache"></span><strong>compiled_cache</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.compiled_cache">¶</a> – <p>Available on: Connection.
A dictionary where <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><code class="xref py py-class docutils literal notranslate"><span class="pre">Compiled</span></code></a> objects
will be cached when the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> compiles a clause
expression into a <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><code class="xref py py-class docutils literal notranslate"><span class="pre">Compiled</span></code></a> object.
It is the user’s responsibility to
manage the size of this dictionary, which will have keys
corresponding to the dialect, clause element, the column
names within the VALUES or SET clause of an INSERT or UPDATE,
as well as the “batch” mode for an INSERT or UPDATE statement.
The format of this dictionary is not guaranteed to stay the
same in future releases.</p>
<p>Note that the ORM makes use of its own “compiled” caches for
some operations, including flush operations. The caching
used by the ORM internally supersedes a cache dictionary
specified here.</p>
</p></li>
<li><p><span class="target" id="sqlalchemy.engine.Connection.execution_options.params.isolation_level"></span><strong>isolation_level</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.isolation_level">¶</a> – <p>Available on: <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.
Set the transaction isolation level for
the lifespan of this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object (<em>not</em> the
underlying DBAPI connection, for which the level is reset
to its original setting upon termination of this
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object).</p>
<p>Valid values include
those string values accepted by the
<a class="reference internal" href="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 passed to <a class="reference internal" href="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>. These levels are
semi-database specific; see individual dialect documentation for
valid levels.</p>
<p>Note that this option necessarily affects the underlying
DBAPI connection for the lifespan of the originating
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, and is not per-execution. This
setting is not removed until the underlying DBAPI connection
is returned to the connection pool, i.e.
the <a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.close()</span></code></a> method is called.</p>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>The <code class="docutils literal notranslate"><span class="pre">isolation_level</span></code> execution option should
<strong>not</strong> be used when a transaction is already established, that
is, the <a class="reference internal" href="#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> method or similar has been
called. A database cannot change the isolation level on a
transaction in progress, and different DBAPIs and/or
SQLAlchemy dialects may implicitly roll back or commit
the transaction, or not affect the connection at all.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 0.9.9: </span>A warning is emitted when the
<code class="docutils literal notranslate"><span class="pre">isolation_level</span></code> execution option is used after a
transaction has been started with <a class="reference internal" href="#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>
or similar.</p>
</div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The <code class="docutils literal notranslate"><span class="pre">isolation_level</span></code> execution option is implicitly
reset if the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is invalidated, e.g. via
the <a class="reference internal" href="#sqlalchemy.engine.Connection.invalidate" title="sqlalchemy.engine.Connection.invalidate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.invalidate()</span></code></a> method, or if a
disconnection error occurs. The new connection produced after
the invalidation will not have the isolation level re-applied
to it automatically.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="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>
- set per <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> isolation level</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.get_isolation_level" title="sqlalchemy.engine.Connection.get_isolation_level"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.get_isolation_level()</span></code></a> - view current level</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 Transaction Isolation</span></a></p>
<p><a class="reference internal" href="../dialects/mysql.html#mysql-isolation-level"><span class="std std-ref">MySQL Transaction Isolation</span></a></p>
<p><a class="reference internal" href="../dialects/mssql.html#mssql-isolation-level"><span class="std std-ref">SQL Server Transaction Isolation</span></a></p>
<p><a class="reference internal" href="../orm/session_transaction.html#session-transaction-isolation"><span class="std std-ref">Setting Transaction Isolation Levels</span></a> - for the ORM</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.engine.Connection.execution_options.params.no_parameters"></span><strong>no_parameters</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.no_parameters">¶</a> – When <code class="docutils literal notranslate"><span class="pre">True</span></code>, if the final parameter
list or dictionary is totally empty, will invoke the
statement on the cursor as <code class="docutils literal notranslate"><span class="pre">cursor.execute(statement)</span></code>,
not passing the parameter collection at all.
Some DBAPIs such as psycopg2 and mysql-python consider
percent signs as significant only when parameters are
present; this option allows code to generate SQL
containing percent signs (and possibly other characters)
that is neutral regarding whether it’s executed by the DBAPI
or piped into a script that’s later invoked by
command line tools.</p></li>
<li><p><span class="target" id="sqlalchemy.engine.Connection.execution_options.params.stream_results"></span><strong>stream_results</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.stream_results">¶</a> – Available on: Connection, statement.
Indicate to the dialect that results should be
“streamed” and not pre-buffered, if possible. This is a limitation
of many DBAPIs. The flag is currently understood only by the
psycopg2, mysqldb and pymysql dialects.</p></li>
<li><p><span class="target" id="sqlalchemy.engine.Connection.execution_options.params.schema_translate_map"></span><strong>schema_translate_map</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.schema_translate_map">¶</a> – <p>Available on: Connection, Engine.
A dictionary mapping schema names to schema names, that will be
applied to the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.params.schema" title="sqlalchemy.schema.Table"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Table.schema</span></code></a> element of each
<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> encountered when SQL or DDL expression elements
are compiled into strings; the resulting schema name will be
converted based on presence in the map of the original name.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#schema-translating"><span class="std std-ref">Translation of Schema Names</span></a></p>
</div>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.get_isolation_level">
<code class="descname">get_isolation_level</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.get_isolation_level" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the current isolation level assigned to this
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<p>This will typically be the default isolation level as determined
by the dialect, unless if the
<a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.isolation_level" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.isolation_level</span></code></a>
feature has been used to alter the isolation level on a
per-<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> basis.</p>
<p>This attribute will typically perform a live SQL operation in order
to procure the current isolation level, so the value returned is the
actual level on the underlying DBAPI connection regardless of how
this state was set. Compare to the
<a class="reference internal" href="#sqlalchemy.engine.Connection.default_isolation_level" title="sqlalchemy.engine.Connection.default_isolation_level"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Connection.default_isolation_level</span></code></a> accessor
which returns the dialect-level setting without performing a SQL
query.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.9.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.default_isolation_level" title="sqlalchemy.engine.Connection.default_isolation_level"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Connection.default_isolation_level</span></code></a> - view default level</p>
<p><a class="reference internal" href="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>
- set per <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> isolation level</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.isolation_level" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.isolation_level</span></code></a>
- set per <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> isolation level</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.in_transaction">
<code class="descname">in_transaction</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.in_transaction" title="Permalink to this definition">¶</a></dt>
<dd><p>Return True if a transaction is in progress.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Connection.info">
<code class="descname">info</code><a class="headerlink" href="#sqlalchemy.engine.Connection.info" title="Permalink to this definition">¶</a></dt>
<dd><p>Info dictionary associated with the underlying DBAPI connection
referred to by this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, allowing user-defined
data to be associated with the connection.</p>
<p>The data here will follow along with the DBAPI connection including
after it is returned to the connection pool and used again
in subsequent instances of <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.invalidate">
<code class="descname">invalidate</code><span class="sig-paren">(</span><em>exception=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.invalidate" title="Permalink to this definition">¶</a></dt>
<dd><p>Invalidate the underlying DBAPI connection associated with
this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
<p>The underlying DBAPI connection is literally closed (if
possible), and is discarded. Its source connection pool will
typically lazily create a new connection to replace it.</p>
<p>Upon the next use (where “use” typically means using the
<a class="reference internal" href="#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execute()</span></code></a> method or similar),
this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> will attempt to
procure a new DBAPI connection using the services of the
<a class="reference internal" href="pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><code class="xref py py-class docutils literal notranslate"><span class="pre">Pool</span></code></a> as a source of connectivity (e.g. a “reconnection”).</p>
<p>If a transaction was in progress (e.g. the
<a class="reference internal" href="#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> method has been called) when
<a class="reference internal" href="#sqlalchemy.engine.Connection.invalidate" title="sqlalchemy.engine.Connection.invalidate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.invalidate()</span></code></a> method is called, at the DBAPI
level all state associated with this transaction is lost, as
the DBAPI connection is closed. The <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
will not allow a reconnection to proceed until the
<a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object is ended, by calling the
<a class="reference internal" href="#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> method; until that point, any attempt at
continuing to use the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> will raise an
<a class="reference internal" href="exceptions.html#sqlalchemy.exc.InvalidRequestError" title="sqlalchemy.exc.InvalidRequestError"><code class="xref py py-class docutils literal notranslate"><span class="pre">InvalidRequestError</span></code></a>.
This is to prevent applications from accidentally
continuing an ongoing transactional operations despite the
fact that the transaction has been lost due to an
invalidation.</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Connection.invalidate" title="sqlalchemy.engine.Connection.invalidate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.invalidate()</span></code></a> method, just like auto-invalidation,
will at the connection pool level invoke the
<a class="reference internal" href="events.html#sqlalchemy.events.PoolEvents.invalidate" title="sqlalchemy.events.PoolEvents.invalidate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">PoolEvents.invalidate()</span></code></a> event.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="pooling.html#pool-connection-invalidation"><span class="std std-ref">More on Invalidation</span></a></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Connection.invalidated">
<code class="descname">invalidated</code><a class="headerlink" href="#sqlalchemy.engine.Connection.invalidated" title="Permalink to this definition">¶</a></dt>
<dd><p>Return True if this connection was invalidated.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.run_callable">
<code class="descname">run_callable</code><span class="sig-paren">(</span><em>callable_</em>, <em>*args</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.run_callable" title="Permalink to this definition">¶</a></dt>
<dd><p>Given a callable object or function, execute it, passing
a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> as the first argument.</p>
<p>The given *args and **kwargs are passed subsequent
to the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> argument.</p>
<p>This function, along with <a class="reference internal" href="#sqlalchemy.engine.Engine.run_callable" title="sqlalchemy.engine.Engine.run_callable"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.run_callable()</span></code></a>,
allows a function to be run with a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
or <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object without the need to know
which one is being dealt with.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.scalar">
<code class="descname">scalar</code><span class="sig-paren">(</span><em>object_</em>, <em>*multiparams</em>, <em>**params</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.scalar" title="Permalink to this definition">¶</a></dt>
<dd><p>Executes and returns the first column of the first row.</p>
<p>The underlying result/cursor is closed after execution.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Connection.schema_for_object">
<code class="descname">schema_for_object</code><em class="property"> = <sqlalchemy.sql.schema._SchemaTranslateMap object></em><a class="headerlink" href="#sqlalchemy.engine.Connection.schema_for_object" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the “.schema” attribute for an object.</p>
<p>Used for <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>, <a class="reference internal" href="defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><code class="xref py py-class docutils literal notranslate"><span class="pre">Sequence</span></code></a> and similar objects,
and takes into account
the <a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.schema_translate_map" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.schema_translate_map</span></code></a>
parameter.</p>
<blockquote>
<div><div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#schema-translating"><span class="std std-ref">Translation of Schema Names</span></a></p>
</div>
</div></blockquote>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connection.transaction">
<code class="descname">transaction</code><span class="sig-paren">(</span><em>callable_</em>, <em>*args</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connection.transaction" title="Permalink to this definition">¶</a></dt>
<dd><p>Execute the given function within a transaction boundary.</p>
<p>The function is passed this <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
as the first argument, followed by the given *args and **kwargs,
e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">do_something</span><span class="p">(</span><span class="n">conn</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"some statement"</span><span class="p">,</span> <span class="p">{</span><span class="s1">'x'</span><span class="p">:</span><span class="n">x</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">:</span><span class="n">y</span><span class="p">})</span>
<span class="n">conn</span><span class="o">.</span><span class="n">transaction</span><span class="p">(</span><span class="n">do_something</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">10</span><span class="p">)</span></pre></div>
</div>
<p>The operations inside the function are all invoked within the
context of a single <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.
Upon success, the transaction is committed. If an
exception is raised, the transaction is rolled back
before propagating the exception.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Connection.transaction" title="sqlalchemy.engine.Connection.transaction"><code class="xref py py-meth docutils literal notranslate"><span class="pre">transaction()</span></code></a> method is superseded by
the usage of the Python <code class="docutils literal notranslate"><span class="pre">with:</span></code> statement, which can
be used with <a class="reference internal" href="#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>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">with</span> <span class="n">conn</span><span class="o">.</span><span class="n">begin</span><span class="p">():</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"some statement"</span><span class="p">,</span> <span class="p">{</span><span class="s1">'x'</span><span class="p">:</span><span class="mi">5</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">:</span><span class="mi">10</span><span class="p">})</span></pre></div>
</div>
<p>As well as with <a class="reference internal" href="#sqlalchemy.engine.Engine.begin" title="sqlalchemy.engine.Engine.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.begin()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"some statement"</span><span class="p">,</span> <span class="p">{</span><span class="s1">'x'</span><span class="p">:</span><span class="mi">5</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">:</span><span class="mi">10</span><span class="p">})</span></pre></div>
</div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Engine.begin" title="sqlalchemy.engine.Engine.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.begin()</span></code></a> - engine-level transactional
context</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Engine.transaction" title="sqlalchemy.engine.Engine.transaction"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.transaction()</span></code></a> - engine-level version of
<a class="reference internal" href="#sqlalchemy.engine.Connection.transaction" title="sqlalchemy.engine.Connection.transaction"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.transaction()</span></code></a></p>
</div>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.Connectable">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">Connectable</code><a class="headerlink" href="#sqlalchemy.engine.Connectable" title="Permalink to this definition">¶</a></dt>
<dd><p>Interface for an object which supports execution of SQL constructs.</p>
<p>The two implementations of <a class="reference internal" href="#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connectable</span></code></a> are
<a class="reference internal" href="#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="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
<p>Connectable must also implement the ‘dialect’ member which references a
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a> instance.</p>
<dl class="method">
<dt id="sqlalchemy.engine.Connectable.connect">
<code class="descname">connect</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connectable.connect" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object.</p>
<p>Depending on context, this may be <code class="docutils literal notranslate"><span class="pre">self</span></code> if this object
is already an instance of <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, or a newly
procured <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> if this object is an instance
of <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connectable.contextual_connect">
<code class="descname">contextual_connect</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connectable.contextual_connect" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object which may be part of an ongoing
context.</p>
<p>Depending on context, this may be <code class="docutils literal notranslate"><span class="pre">self</span></code> if this object
is already an instance of <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, or a newly
procured <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> if this object is an instance
of <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connectable.create">
<code class="descname">create</code><span class="sig-paren">(</span><em>entity</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connectable.create" title="Permalink to this definition">¶</a></dt>
<dd><p>Emit CREATE statements for the given schema entity.</p>
<div class="deprecated">
<p><span class="versionmodified deprecated">Deprecated since version 0.7: </span>The <a class="reference internal" href="#sqlalchemy.engine.Connectable.create" title="sqlalchemy.engine.Connectable.create"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connectable.create()</span></code></a> method is deprecated and will be removed in a future release. Please use the <code class="docutils literal notranslate"><span class="pre">.create()</span></code> method on specific schema objects to emit DDL sequences, including <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.create" title="sqlalchemy.schema.Table.create"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.create()</span></code></a>, <a class="reference internal" href="constraints.html#sqlalchemy.schema.Index.create" title="sqlalchemy.schema.Index.create"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Index.create()</span></code></a>, and <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.create_all()</span></code></a>.</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connectable.drop">
<code class="descname">drop</code><span class="sig-paren">(</span><em>entity</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connectable.drop" title="Permalink to this definition">¶</a></dt>
<dd><p>Emit DROP statements for the given schema entity.</p>
<div class="deprecated">
<p><span class="versionmodified deprecated">Deprecated since version 0.7: </span>The <a class="reference internal" href="#sqlalchemy.engine.Connectable.drop" title="sqlalchemy.engine.Connectable.drop"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connectable.drop()</span></code></a> method is deprecated and will be removed in a future release. Please use the <code class="docutils literal notranslate"><span class="pre">.drop()</span></code> method on specific schema objects to emit DDL sequences, including <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.drop" title="sqlalchemy.schema.Table.drop"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.drop()</span></code></a>, <a class="reference internal" href="constraints.html#sqlalchemy.schema.Index.drop" title="sqlalchemy.schema.Index.drop"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Index.drop()</span></code></a>, and <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.drop_all()</span></code></a>.</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connectable.execute">
<code class="descname">execute</code><span class="sig-paren">(</span><em>object_</em>, <em>*multiparams</em>, <em>**params</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connectable.execute" title="Permalink to this definition">¶</a></dt>
<dd><p>Executes the given construct and returns a <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Connectable.scalar">
<code class="descname">scalar</code><span class="sig-paren">(</span><em>object_</em>, <em>*multiparams</em>, <em>**params</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Connectable.scalar" title="Permalink to this definition">¶</a></dt>
<dd><p>Executes and returns the first column of the first row.</p>
<p>The underlying cursor is closed after execution.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.CreateEnginePlugin">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">CreateEnginePlugin</code><span class="sig-paren">(</span><em>url</em>, <em>kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.CreateEnginePlugin" title="Permalink to this definition">¶</a></dt>
<dd><p>A set of hooks intended to augment the construction of an
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object based on entrypoint names in a URL.</p>
<p>The purpose of <a class="reference internal" href="#sqlalchemy.engine.CreateEnginePlugin" title="sqlalchemy.engine.CreateEnginePlugin"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateEnginePlugin</span></code></a> is to allow third-party
systems to apply engine, pool and dialect level event listeners without
the need for the target application to be modified; instead, the plugin
names can be added to the database URL. Target applications for
<a class="reference internal" href="#sqlalchemy.engine.CreateEnginePlugin" title="sqlalchemy.engine.CreateEnginePlugin"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateEnginePlugin</span></code></a> include:</p>
<ul class="simple">
<li><p>connection and SQL performance tools, e.g. which use events to track
number of checkouts and/or time spent with statements</p></li>
<li><p>connectivity plugins such as proxies</p></li>
</ul>
<p>Plugins are registered using entry points in a similar way as that
of dialects:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">entry_points</span><span class="o">=</span><span class="p">{</span>
<span class="s1">'sqlalchemy.plugins'</span><span class="p">:</span> <span class="p">[</span>
<span class="s1">'myplugin = myapp.plugins:MyPlugin'</span>
<span class="p">]</span></pre></div>
</div>
<p>A plugin that uses the above names would be invoked from a database
URL as in:</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="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mysql+pymysql://scott:tiger@localhost/test?plugin=myplugin"</span><span class="p">)</span></pre></div>
</div>
<p>Alternatively, the <a class="reference internal" href="engines.html#sqlalchemy.create_engine.params.plugins" argument may be passed as a list to :func:" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.plugins"</span> <span class="pre">argument</span> <span class="pre">may</span> <span class="pre">be</span>
<span class="pre">passed</span> <span class="pre">as</span> <span class="pre">a</span> <span class="pre">list</span> <span class="pre">to</span> <span class="pre">:func:</span></code></a>.create_engine`:</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">"mysql+pymysql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">plugins</span><span class="o">=</span><span class="p">[</span><span class="s2">"myplugin"</span><span class="p">])</span></pre></div>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.3: </span>plugin names can also be specified
to <a class="reference internal" href="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> as a list</p>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">plugin</span></code> argument supports multiple instances, so that a URL
may specify multiple plugins; they are loaded in the order stated
in the URL:</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">"mysql+pymysql://scott:tiger@localhost/"</span>
<span class="s2">"test?plugin=plugin_one&plugin=plugin_twp&plugin=plugin_three"</span><span class="p">)</span></pre></div>
</div>
<p>A plugin can receive additional arguments from the URL string as
well as from the keyword arguments passed to <a class="reference internal" href="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>.
The <a class="reference internal" href="engines.html#sqlalchemy.engine.url.URL" title="sqlalchemy.engine.url.URL"><code class="xref py py-class docutils literal notranslate"><span class="pre">URL</span></code></a> object and the keyword dictionary are passed to the
constructor so that these arguments can be extracted from the url’s
<code class="xref py py-attr docutils literal notranslate"><span class="pre">URL.query</span></code> collection as well as from the dictionary:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyPlugin</span><span class="p">(</span><span class="n">CreateEnginePlugin</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">url</span><span class="p">,</span> <span class="n">kwargs</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">my_argument_one</span> <span class="o">=</span> <span class="n">url</span><span class="o">.</span><span class="n">query</span><span class="o">.</span><span class="n">pop</span><span class="p">(</span><span class="s1">'my_argument_one'</span><span class="p">)</span>
<span class="bp">self</span><span class="o">.</span><span class="n">my_argument_two</span> <span class="o">=</span> <span class="n">url</span><span class="o">.</span><span class="n">query</span><span class="o">.</span><span class="n">pop</span><span class="p">(</span><span class="s1">'my_argument_two'</span><span class="p">)</span>
<span class="bp">self</span><span class="o">.</span><span class="n">my_argument_three</span> <span class="o">=</span> <span class="n">kwargs</span><span class="o">.</span><span class="n">pop</span><span class="p">(</span><span class="s1">'my_argument_three'</span><span class="p">,</span> <span class="kc">None</span><span class="p">)</span></pre></div>
</div>
<p>Arguments like those illustrated above would be consumed from the
following:</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="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mysql+pymysql://scott:tiger@localhost/"</span>
<span class="s2">"test?plugin=myplugin&my_argument_one=foo&my_argument_two=bar"</span><span class="p">,</span>
<span class="n">my_argument_three</span><span class="o">=</span><span class="s1">'bat'</span><span class="p">)</span></pre></div>
</div>
<p>The URL and dictionary are used for subsequent setup of the engine
as they are, so the plugin can modify their arguments in-place.
Arguments that are only understood by the plugin should be popped
or otherwise removed so that they aren’t interpreted as erroneous
arguments afterwards.</p>
<p>When the engine creation process completes and produces the
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object, it is again passed to the plugin via the
<a class="reference internal" href="#sqlalchemy.engine.CreateEnginePlugin.engine_created" title="sqlalchemy.engine.CreateEnginePlugin.engine_created"><code class="xref py py-meth docutils literal notranslate"><span class="pre">CreateEnginePlugin.engine_created()</span></code></a> hook. In this hook, additional
changes can be made to the engine, most typically involving setup of
events (e.g. those defined in <a class="reference internal" href="events.html"><span class="std std-ref">Core Events</span></a>).</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
<dl class="method">
<dt id="sqlalchemy.engine.CreateEnginePlugin.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>url</em>, <em>kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.CreateEnginePlugin.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a new <a class="reference internal" href="#sqlalchemy.engine.CreateEnginePlugin" title="sqlalchemy.engine.CreateEnginePlugin"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateEnginePlugin</span></code></a>.</p>
<p>The plugin object is instantiated individually for each call
to <a class="reference internal" href="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>. A single <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> will be
passed to the <a class="reference internal" href="#sqlalchemy.engine.CreateEnginePlugin.engine_created" title="sqlalchemy.engine.CreateEnginePlugin.engine_created"><code class="xref py py-meth docutils literal notranslate"><span class="pre">CreateEnginePlugin.engine_created()</span></code></a> method
corresponding to this URL.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.engine.CreateEnginePlugin.params.url"></span><strong>url</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.CreateEnginePlugin.params.url">¶</a> – the <a class="reference internal" href="engines.html#sqlalchemy.engine.url.URL" title="sqlalchemy.engine.url.URL"><code class="xref py py-class docutils literal notranslate"><span class="pre">URL</span></code></a> object. The plugin should inspect
what it needs here as well as remove its custom arguments from the
<code class="xref py py-attr docutils literal notranslate"><span class="pre">URL.query</span></code> collection. The URL can be modified in-place
in any other way as well.</p></li>
<li><p><span class="target" id="sqlalchemy.engine.CreateEnginePlugin.params.kwargs"></span><strong>kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.CreateEnginePlugin.params.kwargs">¶</a> – The keyword arguments passed to :func`.create_engine`.
The plugin can read and modify this dictionary in-place, to affect
the ultimate arguments used to create the engine. It should
remove its custom arguments from the dictionary as well.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.CreateEnginePlugin.engine_created">
<code class="descname">engine_created</code><span class="sig-paren">(</span><em>engine</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.CreateEnginePlugin.engine_created" title="Permalink to this definition">¶</a></dt>
<dd><p>Receive the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object when it is fully constructed.</p>
<p>The plugin may make additional changes to the engine, such as
registering engine or connection pool events.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.CreateEnginePlugin.handle_dialect_kwargs">
<code class="descname">handle_dialect_kwargs</code><span class="sig-paren">(</span><em>dialect_cls</em>, <em>dialect_args</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.CreateEnginePlugin.handle_dialect_kwargs" title="Permalink to this definition">¶</a></dt>
<dd><p>parse and modify dialect kwargs</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.CreateEnginePlugin.handle_pool_kwargs">
<code class="descname">handle_pool_kwargs</code><span class="sig-paren">(</span><em>pool_cls</em>, <em>pool_args</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.CreateEnginePlugin.handle_pool_kwargs" title="Permalink to this definition">¶</a></dt>
<dd><p>parse and modify pool kwargs</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.Engine">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">Engine</code><span class="sig-paren">(</span><em>pool</em>, <em>dialect</em>, <em>url</em>, <em>logging_name=None</em>, <em>echo=None</em>, <em>proxy=None</em>, <em>execution_options=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.engine.Connectable</span></code></a>, <a class="reference internal" href="internals.html#sqlalchemy.log.Identified" title="sqlalchemy.log.Identified"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.log.Identified</span></code></a></p>
<p>Connects a <a class="reference internal" href="pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><code class="xref py py-class docutils literal notranslate"><span class="pre">Pool</span></code></a> and
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a> together to provide a
source of database connectivity and behavior.</p>
<p>An <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object is instantiated publicly using the
<a class="reference internal" href="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> function.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="engines.html"><span class="doc">Engine Configuration</span></a></p>
<p><a class="reference internal" href="#"><span class="std std-ref">Working with Engines and Connections</span></a></p>
</div>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.begin">
<code class="descname">begin</code><span class="sig-paren">(</span><em>close_with_result=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.begin" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a context manager delivering a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
with a <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> established.</p>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into table (x, y, z) values (1, 2, 3)"</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"my_special_procedure(5)"</span><span class="p">)</span></pre></div>
</div>
<p>Upon successful operation, the <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>
is committed. If an error is raised, the <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>
is rolled back.</p>
<p>The <code class="docutils literal notranslate"><span class="pre">close_with_result</span></code> flag is normally <code class="docutils literal notranslate"><span class="pre">False</span></code>, and indicates
that the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> will be closed when the operation
is complete. When set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, it indicates the
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is in “single use” mode, where the
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> returned by the first call to
<a class="reference internal" href="#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execute()</span></code></a> will close the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> when
that <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> has exhausted all result rows.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.connect()</span></code></a> - procure a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> from
an <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
<p><a class="reference internal" href="#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> - start a <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>
for a particular <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>.</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.connect">
<code class="descname">connect</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.connect" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a new <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object.</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object is a facade that uses a DBAPI
connection internally in order to communicate with the database. This
connection is procured from the connection-holding <a class="reference internal" href="pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><code class="xref py py-class docutils literal notranslate"><span class="pre">Pool</span></code></a>
referenced by this <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>. When the
<a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">close()</span></code></a> method of the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object
is called, the underlying DBAPI connection is then returned to the
connection pool, where it may be used again in a subsequent call to
<a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">connect()</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.contextual_connect">
<code class="descname">contextual_connect</code><span class="sig-paren">(</span><em>close_with_result=False</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.contextual_connect" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object which may be part of some
ongoing context.</p>
<p>By default, this method does the same thing as <a class="reference internal" href="#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.connect()</span></code></a>.
Subclasses of <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> may override this method
to provide contextual behavior.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.engine.Engine.contextual_connect.params.close_with_result"></span><strong>close_with_result</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Engine.contextual_connect.params.close_with_result">¶</a> – When True, the first <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>
created by the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> will call the
<a class="reference internal" href="#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.close()</span></code></a> method of that connection as soon as any
pending result rows are exhausted. This is used to supply the
“connectionless execution” behavior provided by the
<a class="reference internal" href="#sqlalchemy.engine.Engine.execute" title="sqlalchemy.engine.Engine.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.execute()</span></code></a> method.</p>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.dispose">
<code class="descname">dispose</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.dispose" title="Permalink to this definition">¶</a></dt>
<dd><p>Dispose of the connection pool used by this <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
<p>This has the effect of fully closing all <strong>currently checked in</strong>
database connections. Connections that are still checked out
will <strong>not</strong> be closed, however they will no longer be associated
with this <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>, so when they are closed individually,
eventually the <a class="reference internal" href="pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><code class="xref py py-class docutils literal notranslate"><span class="pre">Pool</span></code></a> which they are associated with will
be garbage collected and they will be closed out fully, if
not already closed on checkin.</p>
<p>A new connection pool is created immediately after the old one has
been disposed. This new pool, like all SQLAlchemy connection pools,
does not make any actual connections to the database until one is
first requested, so as long as the <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> isn’t used again,
no new connections will be made.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#engine-disposal"><span class="std std-ref">Engine Disposal</span></a></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Engine.driver">
<code class="descname">driver</code><a class="headerlink" href="#sqlalchemy.engine.Engine.driver" title="Permalink to this definition">¶</a></dt>
<dd><p>Driver name of the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a>
in use by this <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.execute">
<code class="descname">execute</code><span class="sig-paren">(</span><em>statement</em>, <em>*multiparams</em>, <em>**params</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.execute" title="Permalink to this definition">¶</a></dt>
<dd><p>Executes the given construct and returns a <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>.</p>
<p>The arguments are the same as those used by
<a class="reference internal" href="#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execute()</span></code></a>.</p>
<p>Here, a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> is acquired using the
<a class="reference internal" href="#sqlalchemy.engine.Engine.contextual_connect" title="sqlalchemy.engine.Engine.contextual_connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">contextual_connect()</span></code></a> method, and the statement executed
with that connection. The returned <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> is flagged
such that when the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> is exhausted and its
underlying cursor is closed, the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> created here
will also be closed, which allows its associated DBAPI connection
resource to be returned to the connection pool.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.execution_options">
<code class="descname">execution_options</code><span class="sig-paren">(</span><em>**opt</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.execution_options" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a new <a class="reference internal" href="#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 provide
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> objects with the given execution options.</p>
<p>The returned <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> remains related to the original
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> in that it shares the same connection pool and
other state:</p>
<ul class="simple">
<li><p>The <a class="reference internal" href="pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><code class="xref py py-class docutils literal notranslate"><span class="pre">Pool</span></code></a> used by the new <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> is the
same instance. The <a class="reference internal" href="#sqlalchemy.engine.Engine.dispose" title="sqlalchemy.engine.Engine.dispose"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.dispose()</span></code></a> method will replace
the connection pool instance for the parent engine as well
as this one.</p></li>
<li><p>Event listeners are “cascaded” - meaning, the new <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>
inherits the events of the parent, and new events can be associated
with the new <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> individually.</p></li>
<li><p>The logging configuration and logging_name is copied from the parent
<a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p></li>
</ul>
<p>The intent of the <a class="reference internal" href="#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 is
to implement “sharding” schemes where multiple <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>
objects refer to the same connection pool, but are differentiated
by options that would be consumed by a custom event:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">primary_engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mysql://"</span><span class="p">)</span>
<span class="n">shard1</span> <span class="o">=</span> <span class="n">primary_engine</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span><span class="n">shard_id</span><span class="o">=</span><span class="s2">"shard1"</span><span class="p">)</span>
<span class="n">shard2</span> <span class="o">=</span> <span class="n">primary_engine</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span><span class="n">shard_id</span><span class="o">=</span><span class="s2">"shard2"</span><span class="p">)</span></pre></div>
</div>
<p>Above, the <code class="docutils literal notranslate"><span class="pre">shard1</span></code> engine serves as a factory for
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> objects that will contain the execution option
<code class="docutils literal notranslate"><span class="pre">shard_id=shard1</span></code>, and <code class="docutils literal notranslate"><span class="pre">shard2</span></code> will produce <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
objects that contain the execution option <code class="docutils literal notranslate"><span class="pre">shard_id=shard2</span></code>.</p>
<p>An event handler can consume the above execution option to perform
a schema switch or other operation, given a connection. Below
we emit a MySQL <code class="docutils literal notranslate"><span class="pre">use</span></code> statement to switch databases, at the same
time keeping track of which database we’ve established using the
<a class="reference internal" href="#sqlalchemy.engine.Connection.info" title="sqlalchemy.engine.Connection.info"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Connection.info</span></code></a> dictionary, which gives us a persistent
storage space that follows the DBAPI connection:</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="kn">from</span> <span class="nn">sqlalchemy.engine</span> <span class="k">import</span> <span class="n">Engine</span>
<span class="n">shards</span> <span class="o">=</span> <span class="p">{</span><span class="s2">"default"</span><span class="p">:</span> <span class="s2">"base"</span><span class="p">,</span> <span class="n">shard_1</span><span class="p">:</span> <span class="s2">"db1"</span><span class="p">,</span> <span class="s2">"shard_2"</span><span class="p">:</span> <span class="s2">"db2"</span><span class="p">}</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">Engine</span><span class="p">,</span> <span class="s2">"before_cursor_execute"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">_switch_shard</span><span class="p">(</span><span class="n">conn</span><span class="p">,</span> <span class="n">cursor</span><span class="p">,</span> <span class="n">stmt</span><span class="p">,</span>
<span class="n">params</span><span class="p">,</span> <span class="n">context</span><span class="p">,</span> <span class="n">executemany</span><span class="p">):</span>
<span class="n">shard_id</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">_execution_options</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s1">'shard_id'</span><span class="p">,</span> <span class="s2">"default"</span><span class="p">)</span>
<span class="n">current_shard</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">info</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"current_shard"</span><span class="p">,</span> <span class="kc">None</span><span class="p">)</span>
<span class="k">if</span> <span class="n">current_shard</span> <span class="o">!=</span> <span class="n">shard_id</span><span class="p">:</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"use </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="n">shards</span><span class="p">[</span><span class="n">shard_id</span><span class="p">])</span>
<span class="n">conn</span><span class="o">.</span><span class="n">info</span><span class="p">[</span><span class="s2">"current_shard"</span><span class="p">]</span> <span class="o">=</span> <span class="n">shard_id</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#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> - update execution options
on a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object.</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Engine.update_execution_options" title="sqlalchemy.engine.Engine.update_execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.update_execution_options()</span></code></a> - update the execution
options for a given <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> in place.</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.has_table">
<code class="descname">has_table</code><span class="sig-paren">(</span><em>table_name</em>, <em>schema=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.has_table" title="Permalink to this definition">¶</a></dt>
<dd><p>Return True if the given backend has a table of the given name.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="reflection.html#metadata-reflection-inspector"><span class="std std-ref">Fine Grained Reflection with Inspector</span></a> - detailed schema inspection
using the <a class="reference internal" href="reflection.html#sqlalchemy.engine.reflection.Inspector" title="sqlalchemy.engine.reflection.Inspector"><code class="xref py py-class docutils literal notranslate"><span class="pre">Inspector</span></code></a> interface.</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.elements.quoted_name" title="sqlalchemy.sql.elements.quoted_name"><code class="xref py py-class docutils literal notranslate"><span class="pre">quoted_name</span></code></a> - used to pass quoting information along
with a schema identifier.</p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Engine.name">
<code class="descname">name</code><a class="headerlink" href="#sqlalchemy.engine.Engine.name" title="Permalink to this definition">¶</a></dt>
<dd><p>String name of the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a>
in use by this <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.raw_connection">
<code class="descname">raw_connection</code><span class="sig-paren">(</span><em>_connection=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.raw_connection" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a “raw” DBAPI connection from the connection pool.</p>
<p>The returned object is a proxied version of the DBAPI
connection object used by the underlying driver in use.
The object will have all the same behavior as the real DBAPI
connection, except that its <code class="docutils literal notranslate"><span class="pre">close()</span></code> method will result in the
connection being returned to the pool, rather than being closed
for real.</p>
<p>This method provides direct DBAPI connection access for
special situations when the API provided by <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
is not needed. When a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object is already
present, the DBAPI connection is available using
the <a class="reference internal" href="#sqlalchemy.engine.Connection.connection" title="sqlalchemy.engine.Connection.connection"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Connection.connection</span></code></a> accessor.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#dbapi-connections"><span class="std std-ref">Working with Raw DBAPI Connections</span></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.run_callable">
<code class="descname">run_callable</code><span class="sig-paren">(</span><em>callable_</em>, <em>*args</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.run_callable" title="Permalink to this definition">¶</a></dt>
<dd><p>Given a callable object or function, execute it, passing
a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> as the first argument.</p>
<p>The given *args and **kwargs are passed subsequent
to the <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> argument.</p>
<p>This function, along with <a class="reference internal" href="#sqlalchemy.engine.Connection.run_callable" title="sqlalchemy.engine.Connection.run_callable"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.run_callable()</span></code></a>,
allows a function to be run with a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>
or <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> object without the need to know
which one is being dealt with.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.scalar">
<code class="descname">scalar</code><span class="sig-paren">(</span><em>statement</em>, <em>*multiparams</em>, <em>**params</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.scalar" title="Permalink to this definition">¶</a></dt>
<dd><p>Executes and returns the first column of the first row.</p>
<p>The underlying cursor is closed after execution.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.Engine.schema_for_object">
<code class="descname">schema_for_object</code><em class="property"> = <sqlalchemy.sql.schema._SchemaTranslateMap object></em><a class="headerlink" href="#sqlalchemy.engine.Engine.schema_for_object" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the “.schema” attribute for an object.</p>
<p>Used for <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>, <a class="reference internal" href="defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><code class="xref py py-class docutils literal notranslate"><span class="pre">Sequence</span></code></a> and similar objects,
and takes into account
the <a class="reference internal" href="#sqlalchemy.engine.Connection.execution_options.params.schema_translate_map" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.schema_translate_map</span></code></a>
parameter.</p>
<blockquote>
<div><div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#schema-translating"><span class="std std-ref">Translation of Schema Names</span></a></p>
</div>
</div></blockquote>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.table_names">
<code class="descname">table_names</code><span class="sig-paren">(</span><em>schema=None</em>, <em>connection=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.table_names" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a list of all table names available in the database.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.engine.Engine.table_names.params.schema"></span><strong>schema</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Engine.table_names.params.schema">¶</a> – Optional, retrieve names from a non-default schema.</p></li>
<li><p><span class="target" id="sqlalchemy.engine.Engine.table_names.params.connection"></span><strong>connection</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.engine.Engine.table_names.params.connection">¶</a> – Optional, use a specified connection. Default is
the <code class="docutils literal notranslate"><span class="pre">contextual_connect</span></code> for this <code class="docutils literal notranslate"><span class="pre">Engine</span></code>.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.transaction">
<code class="descname">transaction</code><span class="sig-paren">(</span><em>callable_</em>, <em>*args</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.transaction" title="Permalink to this definition">¶</a></dt>
<dd><p>Execute the given function within a transaction boundary.</p>
<p>The function is passed a <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> newly procured
from <a class="reference internal" href="#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> as the first argument,
followed by the given *args and **kwargs.</p>
<p>e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">do_something</span><span class="p">(</span><span class="n">conn</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"some statement"</span><span class="p">,</span> <span class="p">{</span><span class="s1">'x'</span><span class="p">:</span><span class="n">x</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">:</span><span class="n">y</span><span class="p">})</span>
<span class="n">engine</span><span class="o">.</span><span class="n">transaction</span><span class="p">(</span><span class="n">do_something</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">10</span><span class="p">)</span></pre></div>
</div>
<p>The operations inside the function are all invoked within the
context of a single <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.
Upon success, the transaction is committed. If an
exception is raised, the transaction is rolled back
before propagating the exception.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Engine.transaction" title="sqlalchemy.engine.Engine.transaction"><code class="xref py py-meth docutils literal notranslate"><span class="pre">transaction()</span></code></a> method is superseded by
the usage of the Python <code class="docutils literal notranslate"><span class="pre">with:</span></code> statement, which can
be used with <a class="reference internal" href="#sqlalchemy.engine.Engine.begin" title="sqlalchemy.engine.Engine.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.begin()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"some statement"</span><span class="p">,</span> <span class="p">{</span><span class="s1">'x'</span><span class="p">:</span><span class="mi">5</span><span class="p">,</span> <span class="s1">'y'</span><span class="p">:</span><span class="mi">10</span><span class="p">})</span></pre></div>
</div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Engine.begin" title="sqlalchemy.engine.Engine.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.begin()</span></code></a> - engine-level transactional
context</p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.transaction" title="sqlalchemy.engine.Connection.transaction"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.transaction()</span></code></a> - connection-level version of
<a class="reference internal" href="#sqlalchemy.engine.Engine.transaction" title="sqlalchemy.engine.Engine.transaction"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.transaction()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Engine.update_execution_options">
<code class="descname">update_execution_options</code><span class="sig-paren">(</span><em>**opt</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Engine.update_execution_options" title="Permalink to this definition">¶</a></dt>
<dd><p>Update the default execution_options dictionary
of this <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>.</p>
<p>The given keys/values in **opt are added to the
default execution options that will be used for
all connections. The initial contents of this dictionary
can be sent via the <code class="docutils literal notranslate"><span class="pre">execution_options</span></code> parameter
to <a class="reference internal" href="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>.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#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></p>
<p><a class="reference internal" href="#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></p>
</div>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.ExceptionContext">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">ExceptionContext</code><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext" title="Permalink to this definition">¶</a></dt>
<dd><p>Encapsulate information about an error condition in progress.</p>
<p>This object exists solely to be passed to the
<a class="reference internal" href="events.html#sqlalchemy.events.ConnectionEvents.handle_error" title="sqlalchemy.events.ConnectionEvents.handle_error"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ConnectionEvents.handle_error()</span></code></a> event, supporting an interface that
can be extended without backwards-incompatibility.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.7.</span></p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.chained_exception">
<code class="descname">chained_exception</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.chained_exception" title="Permalink to this definition">¶</a></dt>
<dd><p>The exception that was returned by the previous handler in the
exception chain, if any.</p>
<p>If present, this exception will be the one ultimately raised by
SQLAlchemy unless a subsequent handler replaces it.</p>
<p>May be None.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.connection">
<code class="descname">connection</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.connection" title="Permalink to this definition">¶</a></dt>
<dd><p>The <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> in use during the exception.</p>
<p>This member is present, except in the case of a failure when
first connecting.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.ExceptionContext.engine" title="sqlalchemy.engine.ExceptionContext.engine"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ExceptionContext.engine</span></code></a></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.cursor">
<code class="descname">cursor</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.cursor" title="Permalink to this definition">¶</a></dt>
<dd><p>The DBAPI cursor object.</p>
<p>May be None.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.engine">
<code class="descname">engine</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.engine" title="Permalink to this definition">¶</a></dt>
<dd><p>The <a class="reference internal" href="#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> in use during the exception.</p>
<p>This member should always be present, even in the case of a failure
when first connecting.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.execution_context">
<code class="descname">execution_context</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.execution_context" title="Permalink to this definition">¶</a></dt>
<dd><p>The <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a> corresponding to the execution
operation in progress.</p>
<p>This is present for statement execution operations, but not for
operations such as transaction begin/end. It also is not present when
the exception was raised before the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a>
could be constructed.</p>
<p>Note that the <a class="reference internal" href="#sqlalchemy.engine.ExceptionContext.statement" title="sqlalchemy.engine.ExceptionContext.statement"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ExceptionContext.statement</span></code></a> and
<a class="reference internal" href="#sqlalchemy.engine.ExceptionContext.parameters" title="sqlalchemy.engine.ExceptionContext.parameters"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ExceptionContext.parameters</span></code></a> members may represent a
different value than that of the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a>,
potentially in the case where a
<a class="reference internal" href="events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute" title="sqlalchemy.events.ConnectionEvents.before_cursor_execute"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ConnectionEvents.before_cursor_execute()</span></code></a> event or similar
modified the statement/parameters to be sent.</p>
<p>May be None.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect">
<code class="descname">invalidate_pool_on_disconnect</code><em class="property"> = True</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect" title="Permalink to this definition">¶</a></dt>
<dd><p>Represent whether all connections in the pool should be invalidated
when a “disconnect” condition is in effect.</p>
<p>Setting this flag to False within the scope of the
<a class="reference internal" href="events.html#sqlalchemy.events.ConnectionEvents.handle_error" title="sqlalchemy.events.ConnectionEvents.handle_error"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ConnectionEvents.handle_error()</span></code></a> event will have the effect such
that the full collection of connections in the pool will not be
invalidated during a disconnect; only the current connection that is the
subject of the error will actually be invalidated.</p>
<p>The purpose of this flag is for custom disconnect-handling schemes where
the invalidation of other connections in the pool is to be performed
based on other conditions, or even on a per-connection basis.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.3.</span></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.is_disconnect">
<code class="descname">is_disconnect</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.is_disconnect" title="Permalink to this definition">¶</a></dt>
<dd><p>Represent whether the exception as occurred represents a “disconnect”
condition.</p>
<p>This flag will always be True or False within the scope of the
<a class="reference internal" href="events.html#sqlalchemy.events.ConnectionEvents.handle_error" title="sqlalchemy.events.ConnectionEvents.handle_error"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ConnectionEvents.handle_error()</span></code></a> handler.</p>
<p>SQLAlchemy will defer to this flag in order to determine whether or not
the connection should be invalidated subsequently. That is, by
assigning to this flag, a “disconnect” event which then results in
a connection and pool invalidation can be invoked or prevented by
changing this flag.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.original_exception">
<code class="descname">original_exception</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.original_exception" title="Permalink to this definition">¶</a></dt>
<dd><p>The exception object which was caught.</p>
<p>This member is always present.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.parameters">
<code class="descname">parameters</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.parameters" title="Permalink to this definition">¶</a></dt>
<dd><p>Parameter collection that was emitted directly to the DBAPI.</p>
<p>May be None.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.sqlalchemy_exception">
<code class="descname">sqlalchemy_exception</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.sqlalchemy_exception" title="Permalink to this definition">¶</a></dt>
<dd><p>The <a class="reference internal" href="exceptions.html#sqlalchemy.exc.StatementError" title="sqlalchemy.exc.StatementError"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.exc.StatementError</span></code></a> which wraps the original,
and will be raised if exception handling is not circumvented by the event.</p>
<p>May be None, as not all exception types are wrapped by SQLAlchemy.
For DBAPI-level exceptions that subclass the dbapi’s Error class, this
field will always be present.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ExceptionContext.statement">
<code class="descname">statement</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.engine.ExceptionContext.statement" title="Permalink to this definition">¶</a></dt>
<dd><p>String SQL statement that was emitted directly to the DBAPI.</p>
<p>May be None.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.NestedTransaction">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">NestedTransaction</code><span class="sig-paren">(</span><em>connection</em>, <em>parent</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.NestedTransaction" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.engine.Transaction</span></code></a></p>
<p>Represent a ‘nested’, or SAVEPOINT transaction.</p>
<p>A new <a class="reference internal" href="#sqlalchemy.engine.NestedTransaction" title="sqlalchemy.engine.NestedTransaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">NestedTransaction</span></code></a> object may be procured
using the <a class="reference internal" href="#sqlalchemy.engine.Connection.begin_nested" title="sqlalchemy.engine.Connection.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.begin_nested()</span></code></a> method.</p>
<p>The interface is the same as that of <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.ResultProxy">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">ResultProxy</code><span class="sig-paren">(</span><em>context</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy" title="Permalink to this definition">¶</a></dt>
<dd><p>Wraps a DB-API cursor object to provide easier access to row columns.</p>
<p>Individual columns may be accessed by their integer position,
case-insensitive column name, or by <code class="docutils literal notranslate"><span class="pre">schema.Column</span></code>
object. e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">row</span> <span class="o">=</span> <span class="n">fetchone</span><span class="p">()</span>
<span class="n">col1</span> <span class="o">=</span> <span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span> <span class="c1"># access via integer position</span>
<span class="n">col2</span> <span class="o">=</span> <span class="n">row</span><span class="p">[</span><span class="s1">'col2'</span><span class="p">]</span> <span class="c1"># access via name</span>
<span class="n">col3</span> <span class="o">=</span> <span class="n">row</span><span class="p">[</span><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">mycol</span><span class="p">]</span> <span class="c1"># access via Column object.</span></pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">ResultProxy</span></code> also handles post-processing of result column
data using <code class="docutils literal notranslate"><span class="pre">TypeEngine</span></code> objects, which are referenced from
the originating SQL statement that produced this result set.</p>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy._cursor_description">
<code class="descname">_cursor_description</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy._cursor_description" title="Permalink to this definition">¶</a></dt>
<dd><p>May be overridden by subclasses.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ResultProxy._process_row">
<code class="descname">_process_row</code><a class="headerlink" href="#sqlalchemy.engine.ResultProxy._process_row" title="Permalink to this definition">¶</a></dt>
<dd><p>alias of <a class="reference internal" href="#sqlalchemy.engine.RowProxy" title="sqlalchemy.engine.RowProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">RowProxy</span></code></a></p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy._soft_close">
<code class="descname">_soft_close</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy._soft_close" title="Permalink to this definition">¶</a></dt>
<dd><p>Soft close this <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>.</p>
<p>This releases all DBAPI cursor resources, but leaves the
ResultProxy “open” from a semantic perspective, meaning the
fetchXXX() methods will continue to return empty results.</p>
<p>This method is called automatically when:</p>
<ul class="simple">
<li><p>all result rows are exhausted using the fetchXXX() methods.</p></li>
<li><p>cursor.description is None.</p></li>
</ul>
<p>This method is <strong>not public</strong>, but is documented in order to clarify
the “autoclose” process used.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.close">
<code class="descname">close</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.close" title="Permalink to this definition">¶</a></dt>
<dd><p>Close this ResultProxy.</p>
<p>This closes out the underlying DBAPI cursor corresponding
to the statement execution, if one is still present. Note that the
DBAPI cursor is automatically released when the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>
exhausts all available rows. <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> is generally
an optional method except in the case when discarding a
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> that still has additional rows pending for fetch.</p>
<p>In the case of a result that is the product of
<a class="reference internal" href="#dbengine-implicit"><span class="std std-ref">connectionless execution</span></a>,
the underlying <a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object is also closed, which
<a class="reference internal" href="../glossary.html#term-releases"><span class="xref std std-term">releases</span></a> DBAPI connection resources.</p>
<p>After this method is called, it is no longer valid to call upon
the fetch methods, which will raise a <a class="reference internal" href="exceptions.html#sqlalchemy.exc.ResourceClosedError" title="sqlalchemy.exc.ResourceClosedError"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResourceClosedError</span></code></a>
on subsequent use.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.0.0: </span>- the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method
has been separated out from the process that releases the underlying
DBAPI cursor resource. The “auto close” feature of the
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> now performs a so-called “soft close”, which
releases the underlying DBAPI cursor, but allows the
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> to still behave as an open-but-exhausted
result set; the actual <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method is never
called. It is still safe to discard a <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a>
that has been fully exhausted without calling this method.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#"><span class="std std-ref">Working with Engines and Connections</span></a></p>
<p><a class="reference internal" href="#sqlalchemy.engine.ResultProxy._soft_close" title="sqlalchemy.engine.ResultProxy._soft_close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy._soft_close()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.fetchall">
<code class="descname">fetchall</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.fetchall" title="Permalink to this definition">¶</a></dt>
<dd><p>Fetch all rows, just like DB-API <code class="docutils literal notranslate"><span class="pre">cursor.fetchall()</span></code>.</p>
<p>After all rows have been exhausted, the underlying DBAPI
cursor resource is released, and the object may be safely
discarded.</p>
<p>Subsequent calls to <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.fetchall" title="sqlalchemy.engine.ResultProxy.fetchall"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.fetchall()</span></code></a> will return
an empty list. After the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method is
called, the method will raise <a class="reference internal" href="exceptions.html#sqlalchemy.exc.ResourceClosedError" title="sqlalchemy.exc.ResourceClosedError"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResourceClosedError</span></code></a>.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.0.0: </span>- Added “soft close” behavior which
allows the result to be used in an “exhausted” state prior to
calling the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method.</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.fetchmany">
<code class="descname">fetchmany</code><span class="sig-paren">(</span><em>size=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.fetchmany" title="Permalink to this definition">¶</a></dt>
<dd><p>Fetch many rows, just like DB-API
<code class="docutils literal notranslate"><span class="pre">cursor.fetchmany(size=cursor.arraysize)</span></code>.</p>
<p>After all rows have been exhausted, the underlying DBAPI
cursor resource is released, and the object may be safely
discarded.</p>
<p>Calls to <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.fetchmany" title="sqlalchemy.engine.ResultProxy.fetchmany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.fetchmany()</span></code></a> after all rows have been
exhausted will return
an empty list. After the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method is
called, the method will raise <a class="reference internal" href="exceptions.html#sqlalchemy.exc.ResourceClosedError" title="sqlalchemy.exc.ResourceClosedError"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResourceClosedError</span></code></a>.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.0.0: </span>- Added “soft close” behavior which
allows the result to be used in an “exhausted” state prior to
calling the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method.</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.fetchone">
<code class="descname">fetchone</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.fetchone" title="Permalink to this definition">¶</a></dt>
<dd><p>Fetch one row, just like DB-API <code class="docutils literal notranslate"><span class="pre">cursor.fetchone()</span></code>.</p>
<p>After all rows have been exhausted, the underlying DBAPI
cursor resource is released, and the object may be safely
discarded.</p>
<p>Calls to <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.fetchone" title="sqlalchemy.engine.ResultProxy.fetchone"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.fetchone()</span></code></a> after all rows have
been exhausted will return <code class="docutils literal notranslate"><span class="pre">None</span></code>.
After the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method is
called, the method will raise <a class="reference internal" href="exceptions.html#sqlalchemy.exc.ResourceClosedError" title="sqlalchemy.exc.ResourceClosedError"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResourceClosedError</span></code></a>.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.0.0: </span>- Added “soft close” behavior which
allows the result to be used in an “exhausted” state prior to
calling the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method.</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.first">
<code class="descname">first</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.first" title="Permalink to this definition">¶</a></dt>
<dd><p>Fetch the first row and then close the result set unconditionally.</p>
<p>Returns None if no row is present.</p>
<p>After calling this method, the object is fully closed,
e.g. the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method will have been called.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ResultProxy.inserted_primary_key">
<code class="descname">inserted_primary_key</code><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.inserted_primary_key" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the primary key for the row just inserted.</p>
<p>The return value is a list of scalar values
corresponding to the list of primary key columns
in the target table.</p>
<p>This only applies to single row <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><code class="xref py py-func docutils literal notranslate"><span class="pre">insert()</span></code></a>
constructs which did not explicitly specify
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert.returning" title="sqlalchemy.sql.expression.Insert.returning"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Insert.returning()</span></code></a>.</p>
<p>Note that primary key columns which specify a
server_default clause,
or otherwise do not qualify as “autoincrement”
columns (see the notes at <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a>), and were
generated using the database-side default, will
appear in this list as <code class="docutils literal notranslate"><span class="pre">None</span></code> unless the backend
supports “returning” and the insert statement executed
with the “implicit returning” enabled.</p>
<p>Raises <a class="reference internal" href="exceptions.html#sqlalchemy.exc.InvalidRequestError" title="sqlalchemy.exc.InvalidRequestError"><code class="xref py py-class docutils literal notranslate"><span class="pre">InvalidRequestError</span></code></a> if the executed
statement is not a compiled expression construct
or is not an insert() construct.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ResultProxy.is_insert">
<code class="descname">is_insert</code><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.is_insert" title="Permalink to this definition">¶</a></dt>
<dd><p>True if this <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> is the result
of a executing an expression language compiled
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><code class="xref py py-func docutils literal notranslate"><span class="pre">expression.insert()</span></code></a> construct.</p>
<p>When True, this implies that the
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy.inserted_primary_key" title="sqlalchemy.engine.ResultProxy.inserted_primary_key"><code class="xref py py-attr docutils literal notranslate"><span class="pre">inserted_primary_key</span></code></a> attribute is accessible,
assuming the statement did not include
a user defined “returning” construct.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.keys">
<code class="descname">keys</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.keys" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the current set of string keys for rows.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.last_inserted_params">
<code class="descname">last_inserted_params</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.last_inserted_params" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the collection of inserted parameters from this
execution.</p>
<p>Raises <a class="reference internal" href="exceptions.html#sqlalchemy.exc.InvalidRequestError" title="sqlalchemy.exc.InvalidRequestError"><code class="xref py py-class docutils literal notranslate"><span class="pre">InvalidRequestError</span></code></a> if the executed
statement is not a compiled expression construct
or is not an insert() construct.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.last_updated_params">
<code class="descname">last_updated_params</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.last_updated_params" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the collection of updated parameters from this
execution.</p>
<p>Raises <a class="reference internal" href="exceptions.html#sqlalchemy.exc.InvalidRequestError" title="sqlalchemy.exc.InvalidRequestError"><code class="xref py py-class docutils literal notranslate"><span class="pre">InvalidRequestError</span></code></a> if the executed
statement is not a compiled expression construct
or is not an update() construct.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.lastrow_has_defaults">
<code class="descname">lastrow_has_defaults</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.lastrow_has_defaults" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <code class="docutils literal notranslate"><span class="pre">lastrow_has_defaults()</span></code> from the underlying
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a>.</p>
<p>See <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a> for details.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ResultProxy.lastrowid">
<code class="descname">lastrowid</code><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.lastrowid" title="Permalink to this definition">¶</a></dt>
<dd><p>return the ‘lastrowid’ accessor on the DBAPI cursor.</p>
<p>This is a DBAPI specific method and is only functional
for those backends which support it, for statements
where it is appropriate. It’s behavior is not
consistent across backends.</p>
<p>Usage of this method is normally unnecessary when
using insert() expression constructs; the
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy.inserted_primary_key" title="sqlalchemy.engine.ResultProxy.inserted_primary_key"><code class="xref py py-attr docutils literal notranslate"><span class="pre">inserted_primary_key</span></code></a> attribute provides a
tuple of primary key values for a newly inserted row,
regardless of database backend.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.next">
<code class="descname">next</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.next" title="Permalink to this definition">¶</a></dt>
<dd><p>Implement the next() protocol.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.postfetch_cols">
<code class="descname">postfetch_cols</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.postfetch_cols" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <code class="docutils literal notranslate"><span class="pre">postfetch_cols()</span></code> from the underlying
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a>.</p>
<p>See <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a> for details.</p>
<p>Raises <a class="reference internal" href="exceptions.html#sqlalchemy.exc.InvalidRequestError" title="sqlalchemy.exc.InvalidRequestError"><code class="xref py py-class docutils literal notranslate"><span class="pre">InvalidRequestError</span></code></a> if the executed
statement is not a compiled expression construct
or is not an insert() or update() construct.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.prefetch_cols">
<code class="descname">prefetch_cols</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.prefetch_cols" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <code class="docutils literal notranslate"><span class="pre">prefetch_cols()</span></code> from the underlying
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a>.</p>
<p>See <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.ExecutionContext" title="sqlalchemy.engine.interfaces.ExecutionContext"><code class="xref py py-class docutils literal notranslate"><span class="pre">ExecutionContext</span></code></a> for details.</p>
<p>Raises <a class="reference internal" href="exceptions.html#sqlalchemy.exc.InvalidRequestError" title="sqlalchemy.exc.InvalidRequestError"><code class="xref py py-class docutils literal notranslate"><span class="pre">InvalidRequestError</span></code></a> if the executed
statement is not a compiled expression construct
or is not an insert() or update() construct.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ResultProxy.returned_defaults">
<code class="descname">returned_defaults</code><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.returned_defaults" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the values of default columns that were fetched using
the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.ValuesBase.return_defaults" title="sqlalchemy.sql.expression.ValuesBase.return_defaults"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ValuesBase.return_defaults()</span></code></a> feature.</p>
<p>The value is an instance of <a class="reference internal" href="#sqlalchemy.engine.RowProxy" title="sqlalchemy.engine.RowProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">RowProxy</span></code></a>, or <code class="docutils literal notranslate"><span class="pre">None</span></code>
if <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.ValuesBase.return_defaults" title="sqlalchemy.sql.expression.ValuesBase.return_defaults"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ValuesBase.return_defaults()</span></code></a> was not used or if the
backend does not support RETURNING.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="dml.html#sqlalchemy.sql.expression.ValuesBase.return_defaults" title="sqlalchemy.sql.expression.ValuesBase.return_defaults"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ValuesBase.return_defaults()</span></code></a></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ResultProxy.returns_rows">
<code class="descname">returns_rows</code><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.returns_rows" title="Permalink to this definition">¶</a></dt>
<dd><p>True if this <a class="reference internal" href="#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> returns rows.</p>
<p>I.e. if it is legal to call the methods
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy.fetchone" title="sqlalchemy.engine.ResultProxy.fetchone"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchone()</span></code></a>,
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy.fetchmany" title="sqlalchemy.engine.ResultProxy.fetchmany"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchmany()</span></code></a>
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy.fetchall" title="sqlalchemy.engine.ResultProxy.fetchall"><code class="xref py py-meth docutils literal notranslate"><span class="pre">fetchall()</span></code></a>.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.engine.ResultProxy.rowcount">
<code class="descname">rowcount</code><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.rowcount" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the ‘rowcount’ for this result.</p>
<p>The ‘rowcount’ reports the number of rows <em>matched</em>
by the WHERE criterion of an UPDATE or DELETE statement.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Notes regarding <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ResultProxy.rowcount</span></code></a>:</p>
<ul class="simple">
<li><p>This attribute returns the number of rows <em>matched</em>,
which is not necessarily the same as the number of rows
that were actually <em>modified</em> - an UPDATE statement, for example,
may have no net change on a given row if the SET values
given are the same as those present in the row already.
Such a row would be matched but not modified.
On backends that feature both styles, such as MySQL,
rowcount is configured by default to return the match
count in all cases.</p></li>
<li><p><a class="reference internal" href="#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ResultProxy.rowcount</span></code></a> is <em>only</em> useful in conjunction
with an UPDATE or DELETE statement. Contrary to what the Python
DBAPI says, it does <em>not</em> return the
number of rows available from the results of a SELECT statement
as DBAPIs cannot support this functionality when rows are
unbuffered.</p></li>
<li><p><a class="reference internal" href="#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ResultProxy.rowcount</span></code></a> may not be fully implemented by
all dialects. In particular, most DBAPIs do not support an
aggregate rowcount result from an executemany call.
The <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.supports_sane_rowcount" title="sqlalchemy.engine.ResultProxy.supports_sane_rowcount"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.supports_sane_rowcount()</span></code></a> and
<a class="reference internal" href="#sqlalchemy.engine.ResultProxy.supports_sane_multi_rowcount" title="sqlalchemy.engine.ResultProxy.supports_sane_multi_rowcount"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.supports_sane_multi_rowcount()</span></code></a> methods
will report from the dialect if each usage is known to be
supported.</p></li>
<li><p>Statements that use RETURNING may not return a correct
rowcount.</p></li>
</ul>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.scalar">
<code class="descname">scalar</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.scalar" title="Permalink to this definition">¶</a></dt>
<dd><p>Fetch the first column of the first row, and close the result set.</p>
<p>Returns None if no row is present.</p>
<p>After calling this method, the object is fully closed,
e.g. the <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.close" title="sqlalchemy.engine.ResultProxy.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ResultProxy.close()</span></code></a> method will have been called.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.supports_sane_multi_rowcount">
<code class="descname">supports_sane_multi_rowcount</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.supports_sane_multi_rowcount" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <code class="docutils literal notranslate"><span class="pre">supports_sane_multi_rowcount</span></code> from the dialect.</p>
<p>See <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ResultProxy.rowcount</span></code></a> for background.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.ResultProxy.supports_sane_rowcount">
<code class="descname">supports_sane_rowcount</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.ResultProxy.supports_sane_rowcount" title="Permalink to this definition">¶</a></dt>
<dd><p>Return <code class="docutils literal notranslate"><span class="pre">supports_sane_rowcount</span></code> from the dialect.</p>
<p>See <a class="reference internal" href="#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ResultProxy.rowcount</span></code></a> for background.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.RowProxy">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">RowProxy</code><span class="sig-paren">(</span><em>parent</em>, <em>row</em>, <em>processors</em>, <em>keymap</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.RowProxy" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.engine.BaseRowProxy</span></code></p>
<p>Proxy values from a single cursor row.</p>
<p>Mostly follows “ordered dictionary” behavior, mapping result
values to the string-based column name, the integer position of
the result in the row, as well as Column instances which can be
mapped to the original Columns that produced this result set (for
results that correspond to constructed SQL expressions).</p>
<dl class="method">
<dt id="sqlalchemy.engine.RowProxy.has_key">
<code class="descname">has_key</code><span class="sig-paren">(</span><em>key</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.RowProxy.has_key" title="Permalink to this definition">¶</a></dt>
<dd><p>Return True if this RowProxy contains the given key.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.RowProxy.items">
<code class="descname">items</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.RowProxy.items" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a list of tuples, each tuple containing a key/value pair.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.RowProxy.keys">
<code class="descname">keys</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.RowProxy.keys" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the list of keys as strings represented by this RowProxy.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.Transaction">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">Transaction</code><span class="sig-paren">(</span><em>connection</em>, <em>parent</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Transaction" title="Permalink to this definition">¶</a></dt>
<dd><p>Represent a database transaction in progress.</p>
<p>The <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a> object is procured by
calling the <a class="reference internal" href="#sqlalchemy.engine.Connection.begin" title="sqlalchemy.engine.Connection.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">begin()</span></code></a> method of
<a class="reference internal" href="#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>:</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="n">engine</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">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="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="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into x (a, b) values (1, 2)"</span><span class="p">)</span>
<span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>The object provides <a class="reference internal" href="#sqlalchemy.engine.Transaction.rollback" title="sqlalchemy.engine.Transaction.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">rollback()</span></code></a> and <a class="reference internal" href="#sqlalchemy.engine.Transaction.commit" title="sqlalchemy.engine.Transaction.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">commit()</span></code></a>
methods in order to control transaction boundaries. It
also implements a context manager interface so that
the Python <code class="docutils literal notranslate"><span class="pre">with</span></code> statement can be used with the
<a class="reference internal" href="#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> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">with</span> <span class="n">connection</span><span class="o">.</span><span class="n">begin</span><span class="p">():</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"insert into x (a, b) values (1, 2)"</span><span class="p">)</span></pre></div>
</div>
<p>The Transaction object is <strong>not</strong> threadsafe.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#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></p>
<p><a class="reference internal" href="#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></p>
<p><a class="reference internal" href="#sqlalchemy.engine.Connection.begin_nested" title="sqlalchemy.engine.Connection.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.begin_nested()</span></code></a></p>
</div>
<span class="target" id="index-2"></span><dl class="method">
<dt id="sqlalchemy.engine.Transaction.close">
<code class="descname">close</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Transaction.close" title="Permalink to this definition">¶</a></dt>
<dd><p>Close this <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.</p>
<p>If this transaction is the base transaction in a begin/commit
nesting, the transaction will rollback(). Otherwise, the
method returns.</p>
<p>This is used to cancel a Transaction without affecting the scope of
an enclosing transaction.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Transaction.commit">
<code class="descname">commit</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Transaction.commit" title="Permalink to this definition">¶</a></dt>
<dd><p>Commit this <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.engine.Transaction.rollback">
<code class="descname">rollback</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.Transaction.rollback" title="Permalink to this definition">¶</a></dt>
<dd><p>Roll back this <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.engine.TwoPhaseTransaction">
<em class="property">class </em><code class="descclassname">sqlalchemy.engine.</code><code class="descname">TwoPhaseTransaction</code><span class="sig-paren">(</span><em>connection</em>, <em>xid</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.TwoPhaseTransaction" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.engine.Transaction</span></code></a></p>
<p>Represent a two-phase transaction.</p>
<p>A new <a class="reference internal" href="#sqlalchemy.engine.TwoPhaseTransaction" title="sqlalchemy.engine.TwoPhaseTransaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">TwoPhaseTransaction</span></code></a> object may be procured
using the <a class="reference internal" href="#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> method.</p>
<p>The interface is the same as that of <a class="reference internal" href="#sqlalchemy.engine.Transaction" title="sqlalchemy.engine.Transaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">Transaction</span></code></a>
with the addition of the <a class="reference internal" href="#sqlalchemy.engine.TwoPhaseTransaction.prepare" title="sqlalchemy.engine.TwoPhaseTransaction.prepare"><code class="xref py py-meth docutils literal notranslate"><span class="pre">prepare()</span></code></a> method.</p>
<dl class="method">
<dt id="sqlalchemy.engine.TwoPhaseTransaction.prepare">
<code class="descname">prepare</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.engine.TwoPhaseTransaction.prepare" title="Permalink to this definition">¶</a></dt>
<dd><p>Prepare this <a class="reference internal" href="#sqlalchemy.engine.TwoPhaseTransaction" title="sqlalchemy.engine.TwoPhaseTransaction"><code class="xref py py-class docutils literal notranslate"><span class="pre">TwoPhaseTransaction</span></code></a>.</p>
<p>After a PREPARE, the transaction can be committed.</p>
</dd></dl>
</dd></dl>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="engines.html" title="previous chapter">Engine Configuration</a>
Next:
<a href="pooling.html" title="next chapter">Connection Pooling</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
>
757
