<!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>
SQL Expression Language Tutorial
—
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="SQLAlchemy Core" href="index.html" />
<link rel="next" title="SQL Statements and Expressions API" href="expression_api.html" />
<link rel="prev" title="SQLAlchemy Core" href="index.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 class="selected"><span class="link-container"><strong>SQL Expression Language Tutorial</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#version-check">Version Check</a></span></li>
<li><span class="link-container"><a class="reference external" href="#connecting">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#define-and-create-tables">Define and Create Tables</a></span></li>
<li><span class="link-container"><a class="reference external" href="#insert-expressions">Insert Expressions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#executing">Executing</a></span></li>
<li><span class="link-container"><a class="reference external" href="#executing-multiple-statements">Executing Multiple Statements</a></span></li>
<li><span class="link-container"><a class="reference external" href="#selecting">Selecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#operators">Operators</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#operator-customization">Operator Customization</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#conjunctions">Conjunctions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-textual-sql">Using Textual SQL</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#specifying-bound-parameter-behaviors">Specifying Bound Parameter Behaviors</a></span></li>
<li><span class="link-container"><a class="reference external" href="#specifying-result-column-behaviors">Specifying Result-Column Behaviors</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-text-fragments-inside-bigger-statements">Using text() fragments inside bigger statements</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-more-specific-text-with-table-literal-column-and-column">Using More Specific Text with <code class="xref py py-func docutils literal notranslate"><span class="pre">table()</span></code>, <code class="xref py py-func docutils literal notranslate"><span class="pre">literal_column()</span></code>, and <code class="xref py py-func docutils literal notranslate"><span class="pre">column()</span></code></a></span></li>
<li><span class="link-container"><a class="reference external" href="#ordering-or-grouping-by-a-label">Ordering or Grouping by a Label</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#using-aliases">Using Aliases</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-joins">Using Joins</a></span></li>
<li><span class="link-container"><a class="reference external" href="#everything-else">Everything Else</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#bind-parameter-objects">Bind Parameter Objects</a></span></li>
<li><span class="link-container"><a class="reference external" href="#functions">Functions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#window-functions">Window Functions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#unions-and-other-set-operations">Unions and Other Set Operations</a></span></li>
<li><span class="link-container"><a class="reference external" href="#scalar-selects">Scalar Selects</a></span></li>
<li><span class="link-container"><a class="reference external" href="#correlated-subqueries">Correlated Subqueries</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#lateral-correlation">LATERAL correlation</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#ordering-grouping-limiting-offset-ing">Ordering, Grouping, Limiting, Offset…ing…</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#inserts-updates-and-deletes">Inserts, Updates and Deletes</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#correlated-updates">Correlated Updates</a></span></li>
<li><span class="link-container"><a class="reference external" href="#multiple-table-updates">Multiple Table Updates</a></span></li>
<li><span class="link-container"><a class="reference external" href="#parameter-ordered-updates">Parameter-Ordered Updates</a></span></li>
<li><span class="link-container"><a class="reference external" href="#deletes">Deletes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#multiple-table-deletes">Multiple Table Deletes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#matched-row-counts">Matched Row Counts</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#further-reference">Further Reference</a></span></li>
</ul>
</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></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="sql-expression-language-tutorial">
<span id="sqlexpression-toplevel"></span><h1>SQL Expression Language Tutorial<a class="headerlink" href="#sql-expression-language-tutorial" title="Permalink to this headline">¶</a></h1>
<p>The SQLAlchemy Expression Language presents a system of representing
relational database structures and expressions using Python constructs. These
constructs are modeled to resemble those of the underlying database as closely
as possible, while providing a modicum of abstraction of the various
implementation differences between database backends. While the constructs
attempt to represent equivalent concepts between backends with consistent
structures, they do not conceal useful concepts that are unique to particular
subsets of backends. The Expression Language therefore presents a method of
writing backend-neutral SQL expressions, but does not attempt to enforce that
expressions are backend-neutral.</p>
<p>The Expression Language is in contrast to the Object Relational Mapper, which
is a distinct API that builds on top of the Expression Language. Whereas the
ORM, introduced in <a class="reference internal" href="../orm/tutorial.html"><span class="std std-ref">Object Relational Tutorial</span></a>, presents a high level and
abstracted pattern of usage, which itself is an example of applied usage of
the Expression Language, the Expression Language presents a system of
representing the primitive constructs of the relational database directly
without opinion.</p>
<p>While there is overlap among the usage patterns of the ORM and the Expression
Language, the similarities are more superficial than they may at first appear.
One approaches the structure and content of data from the perspective of a
user-defined <a class="reference external" href="http://en.wikipedia.org/wiki/Domain_model">domain model</a> which is transparently
persisted and refreshed from its underlying storage model. The other
approaches it from the perspective of literal schema and SQL expression
representations which are explicitly composed into messages consumed
individually by the database.</p>
<p>A successful application may be constructed using the Expression Language
exclusively, though the application will need to define its own system of
translating application concepts into individual database messages and from
individual database result sets. Alternatively, an application constructed
with the ORM may, in advanced scenarios, make occasional usage of the
Expression Language directly in certain areas where specific database
interactions are required.</p>
<p>The following tutorial is in doctest format, meaning each <code class="docutils literal notranslate"><span class="pre">>>></span></code> line
represents something you can type at a Python command prompt, and the
following text represents the expected return value. The tutorial has no
prerequisites.</p>
<div class="section" id="version-check">
<h2>Version Check<a class="headerlink" href="#version-check" title="Permalink to this headline">¶</a></h2>
<p>A quick check to verify that we are on at least <strong>version 1.2</strong> of SQLAlchemy:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">sqlalchemy</span>
<span class="gp">>>> </span><span class="n">sqlalchemy</span><span class="o">.</span><span class="n">__version__</span>
<span class="go">1.2.0</span></pre></div>
</div>
</div>
<div class="section" id="connecting">
<h2>Connecting<a class="headerlink" href="#connecting" title="Permalink to this headline">¶</a></h2>
<p>For this tutorial we will use an in-memory-only SQLite database. This is an
easy way to test things without needing to have an actual database defined
anywhere. To connect we use <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="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span>
<span class="gp">>>> </span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'sqlite:///:memory:'</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">echo</span></code> flag is a shortcut to setting up SQLAlchemy logging, which is
accomplished via Python’s standard <code class="docutils literal notranslate"><span class="pre">logging</span></code> module. With it enabled, we’ll
see all the generated SQL produced. If you are working through this tutorial
and want less output generated, set it to <code class="docutils literal notranslate"><span class="pre">False</span></code>. This tutorial will format
the SQL behind a popup window so it doesn’t get in our way; just click the
“SQL” links to see what’s being generated.</p>
<p>The return value 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 an instance of
<a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>, and it represents the core interface to the
database, adapted through a <span class="xref std std-term">dialect</span> that handles the details
of the database and <a class="reference internal" href="../glossary.html#term-dbapi"><span class="xref std std-term">DBAPI</span></a> in use. In this case the SQLite
dialect will interpret instructions to the Python built-in <code class="docutils literal notranslate"><span class="pre">sqlite3</span></code>
module.</p>
<div class="sidebar">
<p class="sidebar-title">Lazy Connecting</p>
<p>The <a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>, when first returned by <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>,
has not actually tried to connect to the database yet; that happens
only the first time it is asked to perform a task against the database.</p>
</div>
<p>The first time a method like <a class="reference internal" href="connections.html#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="connections.html#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>
is called, the <a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> establishes a real <a class="reference internal" href="../glossary.html#term-dbapi"><span class="xref std std-term">DBAPI</span></a> connection to the
database, which is then used to emit the SQL.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="engines.html#database-urls"><span class="std std-ref">Database Urls</span></a> - includes examples 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>
connecting to several kinds of databases with links to more information.</p>
</div>
</div>
<div class="section" id="define-and-create-tables">
<h2>Define and Create Tables<a class="headerlink" href="#define-and-create-tables" title="Permalink to this headline">¶</a></h2>
<p>The SQL Expression Language constructs its expressions in most cases against
table columns. In SQLAlchemy, a column is most often represented by an object
called <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 in all cases a
<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> is associated with 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>. A collection 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 their associated child objects
is referred to as <strong>database metadata</strong>. In this tutorial we will explicitly
lay out several <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, but note that SA
can also “import” whole sets 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
automatically from an existing database (this process is called <strong>table
reflection</strong>).</p>
<p>We define our tables all within a catalog called
<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>, using 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> construct, which resembles regular SQL
CREATE TABLE statements. We’ll make two tables, one of which represents
“users” in an application, and another which represents zero or more “email
addresses” for each row in the “users” table:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</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="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">ForeignKey</span>
<span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">users</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">metadata</span><span class="p">,</span>
<span class="gp">... </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="bp">True</span><span class="p">),</span>
<span class="gp">... </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="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s1">'fullname'</span><span class="p">,</span> <span class="n">String</span><span class="p">),</span>
<span class="gp">... </span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">addresses</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'addresses'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="gp">... </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="bp">True</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s1">'user_id'</span><span class="p">,</span> <span class="bp">None</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'users.id'</span><span class="p">)),</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s1">'email_address'</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span></pre></div>
</div>
<p>All about how to define <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, as well as
how to create them from an existing database automatically, is described in
<a class="reference internal" href="metadata.html"><span class="std std-ref">Describing Databases with MetaData</span></a>.</p>
<p>Next, to tell the <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> we’d actually like to
create our selection of tables for real inside the SQLite database, we use
<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_all()</span></code></a>, passing it the <code class="docutils literal notranslate"><span class="pre">engine</span></code>
instance which points to our database. This will check for the presence of
each table first before creating, so it’s safe to call multiple times:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="go">SE...</span>
<div class='popup_sql'>CREATE TABLE users (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
PRIMARY KEY (id)
)
()
COMMIT
CREATE TABLE addresses (
id INTEGER NOT NULL,
user_id INTEGER,
email_address VARCHAR NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT</div></pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Users familiar with the syntax of CREATE TABLE may notice that the
VARCHAR columns were generated without a length; on SQLite and PostgreSQL,
this is a valid datatype, but on others, it’s not allowed. So if running
this tutorial on one of those databases, and you wish to use SQLAlchemy to
issue CREATE TABLE, a “length” may be provided to the <a class="reference internal" href="type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> type as
below:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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></pre></div>
</div>
<p>The length field on <a class="reference internal" href="type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a>, as well as similar precision/scale fields
available on <a class="reference internal" href="type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">Integer</span></code></a>, <a class="reference internal" href="type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a>, etc. are not referenced by
SQLAlchemy other than when creating tables.</p>
<p>Additionally, Firebird and Oracle require sequences to generate new
primary key identifiers, and SQLAlchemy doesn’t generate or assume these
without being instructed. For that, you use the <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> construct:</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">Sequence</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">Sequence</span><span class="p">(</span><span class="s1">'user_id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>A full, foolproof <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> is therefore:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">users</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">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">Sequence</span><span class="p">(</span><span class="s1">'user_id_seq'</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="n">Column</span><span class="p">(</span><span class="s1">'fullname'</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="n">Column</span><span class="p">(</span><span class="s1">'nickname'</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>We include this more verbose <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> construct separately
to highlight the difference between a minimal construct geared primarily
towards in-Python usage only, versus one that will be used to emit CREATE
TABLE statements on a particular set of backends with more stringent
requirements.</p>
</div>
</div>
<div class="section" id="insert-expressions">
<span id="coretutorial-insert-expressions"></span><h2>Insert Expressions<a class="headerlink" href="#insert-expressions" title="Permalink to this headline">¶</a></h2>
<p>The first SQL expression we’ll create is the
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> construct, which represents an
INSERT statement. This is typically created relative to its target table:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">ins</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span></pre></div>
</div>
<p>To see a sample of the SQL this construct produces, use the <code class="docutils literal notranslate"><span class="pre">str()</span></code>
function:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="nb">str</span><span class="p">(</span><span class="n">ins</span><span class="p">)</span>
<span class="go">'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'</span></pre></div>
</div>
<p>Notice above that the INSERT statement names every column in the <code class="docutils literal notranslate"><span class="pre">users</span></code>
table. This can be limited by using the <code class="docutils literal notranslate"><span class="pre">values()</span></code> method, which establishes
the VALUES clause of the INSERT explicitly:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">ins</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'jack'</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s1">'Jack Jones'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="nb">str</span><span class="p">(</span><span class="n">ins</span><span class="p">)</span>
<span class="go">'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'</span></pre></div>
</div>
<p>Above, while the <code class="docutils literal notranslate"><span class="pre">values</span></code> method limited the VALUES clause to just two
columns, the actual data we placed in <code class="docutils literal notranslate"><span class="pre">values</span></code> didn’t get rendered into the
string; instead we got named bind parameters. As it turns out, our data <em>is</em>
stored within our <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> construct, but it
typically only comes out when the statement is actually executed; since the
data consists of literal values, SQLAlchemy automatically generates bind
parameters for them. We can peek at this data for now by looking at the
compiled form of the statement:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">ins</span><span class="o">.</span><span class="n">compile</span><span class="p">()</span><span class="o">.</span><span class="n">params</span>
<span class="go">{'fullname': 'Jack Jones', 'name': 'jack'}</span></pre></div>
</div>
</div>
<div class="section" id="executing">
<h2>Executing<a class="headerlink" href="#executing" title="Permalink to this headline">¶</a></h2>
<p>The interesting part of an <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> is
executing it. In this tutorial, we will generally focus on the most explicit
method of executing a SQL construct, and later touch upon some “shortcut” ways
to do it. The <code class="docutils literal notranslate"><span class="pre">engine</span></code> object we created is a repository for database
connections capable of issuing SQL to the database. To acquire a connection,
we use the <code class="docutils literal notranslate"><span class="pre">connect()</span></code> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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="gp">>>> </span><span class="n">conn</span>
<span class="go"><sqlalchemy.engine.base.Connection object at 0x...></span></pre></div>
</div>
<p>The <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object represents an actively
checked out DBAPI connection resource. Lets feed it our
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> object and see what happens:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">ins</span><span class="p">)</span>
<div class='show_sql'>INSERT INTO users (name, fullname) VALUES (?, ?)
('jack', 'Jack Jones')
COMMIT</div></pre></div>
</div>
<p>So the INSERT statement was now issued to the database. Although we got
positional “qmark” bind parameters instead of “named” bind parameters in the
output. How come ? Because when executed, the
<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> used the SQLite <strong>dialect</strong> to
help generate the statement; when we use the <code class="docutils literal notranslate"><span class="pre">str()</span></code> function, the statement
isn’t aware of this dialect, and falls back onto a default which uses named
parameters. We can view this manually as follows:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">ins</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">engine</span>
<span class="gp">>>> </span><span class="nb">str</span><span class="p">(</span><span class="n">ins</span><span class="p">)</span>
<span class="go">'INSERT INTO users (name, fullname) VALUES (?, ?)'</span></pre></div>
</div>
<p>What about the <code class="docutils literal notranslate"><span class="pre">result</span></code> variable we got when we called <code class="docutils literal notranslate"><span class="pre">execute()</span></code> ? As
the SQLAlchemy <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> object references a
DBAPI connection, the result, known as a
<a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> object, is analogous to the DBAPI
cursor object. In the case of an INSERT, we can get important information from
it, such as the primary key values which were generated from our statement
using <a class="reference internal" href="connections.html#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">ResultProxy.inserted_primary_key</span></code></a>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">result</span><span class="o">.</span><span class="n">inserted_primary_key</span>
<span class="go">[1]</span></pre></div>
</div>
<p>The value of <code class="docutils literal notranslate"><span class="pre">1</span></code> was automatically generated by SQLite, but only because we
did not specify the <code class="docutils literal notranslate"><span class="pre">id</span></code> column in our
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> statement; otherwise, our explicit
value would have been used. In either case, SQLAlchemy always knows how to get
at a newly generated primary key value, even though the method of generating
them is different across different databases; each database’s
<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> knows the specific steps needed to
determine the correct value (or values; note that
<a class="reference internal" href="connections.html#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">ResultProxy.inserted_primary_key</span></code></a>
returns a list so that it supports composite primary keys). Methods here
range from using <code class="docutils literal notranslate"><span class="pre">cursor.lastrowid</span></code>, to selecting from a database-specific
function, to using <code class="docutils literal notranslate"><span class="pre">INSERT..RETURNING</span></code> syntax; this all occurs transparently.</p>
</div>
<div class="section" id="executing-multiple-statements">
<span id="execute-multiple"></span><h2>Executing Multiple Statements<a class="headerlink" href="#executing-multiple-statements" title="Permalink to this headline">¶</a></h2>
<p>Our insert example above was intentionally a little drawn out to show some
various behaviors of expression language constructs. In the usual case, an
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> statement is usually compiled
against the parameters sent to the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method on
<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a>, so that there’s no need to use
the <code class="docutils literal notranslate"><span class="pre">values</span></code> keyword with <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a>. Lets
create a generic <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> statement again
and use it in the “normal” way:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">ins</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">ins</span><span class="p">,</span> <span class="nb">id</span><span class="o">=</span><span class="mi">2</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s1">'wendy'</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s1">'Wendy Williams'</span><span class="p">)</span>
<div class='show_sql'>INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
(2, 'wendy', 'Wendy Williams')
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span></pre></div>
</div>
<p>Above, because we specified all three columns in the <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method,
the compiled <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> included all three
columns. The <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> statement is compiled
at execution time based on the parameters we specified; if we specified fewer
parameters, the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> would have fewer
entries in its VALUES clause.</p>
<p>To issue many inserts using DBAPI’s <code class="docutils literal notranslate"><span class="pre">executemany()</span></code> method, we can send in a
list of dictionaries each containing a distinct set of parameters to be
inserted, as we do here to add some email addresses:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="p">[</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'user_id'</span><span class="p">:</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'email_address'</span> <span class="p">:</span> <span class="s1">'jack@yahoo.com'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'user_id'</span><span class="p">:</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'email_address'</span> <span class="p">:</span> <span class="s1">'jack@msn.com'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'user_id'</span><span class="p">:</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'email_address'</span> <span class="p">:</span> <span class="s1">'www@www.org'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'user_id'</span><span class="p">:</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'email_address'</span> <span class="p">:</span> <span class="s1">'wendy@aol.com'</span><span class="p">},</span>
<span class="gp">... </span><span class="p">])</span>
<div class='show_sql'>INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
((1, 'jack@yahoo.com'), (1, 'jack@msn.com'), (2, 'www@www.org'), (2, 'wendy@aol.com'))
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span></pre></div>
</div>
<p>Above, we again relied upon SQLite’s automatic generation of primary key
identifiers for each <code class="docutils literal notranslate"><span class="pre">addresses</span></code> row.</p>
<p>When executing multiple sets of parameters, each dictionary must have the
<strong>same</strong> set of keys; i.e. you cant have fewer keys in some dictionaries than
others. This is because the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a>
statement is compiled against the <strong>first</strong> dictionary in the list, and it’s
assumed that all subsequent argument dictionaries are compatible with that
statement.</p>
<p>The “executemany” style of invocation is available for each of the
<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>, <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.update" title="sqlalchemy.sql.expression.update"><code class="xref py py-func docutils literal notranslate"><span class="pre">update()</span></code></a> and <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.delete" title="sqlalchemy.sql.expression.delete"><code class="xref py py-func docutils literal notranslate"><span class="pre">delete()</span></code></a> constructs.</p>
</div>
<div class="section" id="selecting">
<span id="coretutorial-selecting"></span><h2>Selecting<a class="headerlink" href="#selecting" title="Permalink to this headline">¶</a></h2>
<p>We began with inserts just so that our test database had some data in it. The
more interesting part of the data is selecting it! We’ll cover UPDATE and
DELETE statements later. The primary construct used to generate SELECT
statements is 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> function:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">select</span>
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">])</span>
<span class="gp">>>> </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span>
<div class='show_sql'>SELECT users.id, users.name, users.fullname
FROM users
()</div></pre></div>
</div>
<p>Above, we issued a basic <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> call, placing the <code class="docutils literal notranslate"><span class="pre">users</span></code> table
within the COLUMNS clause of the select, and then executing. SQLAlchemy
expanded the <code class="docutils literal notranslate"><span class="pre">users</span></code> table into the set of each of its columns, and also
generated a FROM clause for us. The result returned is again a
<a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> object, which acts much like a
DBAPI cursor, including methods such as
<a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.fetchone" title="sqlalchemy.engine.ResultProxy.fetchone"><code class="xref py py-func docutils literal notranslate"><span class="pre">fetchone()</span></code></a> and
<a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.fetchall" title="sqlalchemy.engine.ResultProxy.fetchall"><code class="xref py py-func docutils literal notranslate"><span class="pre">fetchall()</span></code></a>. The easiest way to get
rows from it is to just iterate:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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="gp">... </span> <span class="k">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
<span class="go">(1, u'jack', u'Jack Jones')</span>
<span class="go">(2, u'wendy', u'Wendy Williams')</span></pre></div>
</div>
<p>Above, we see that printing each row produces a simple tuple-like result. We
have more options at accessing the data in each row. One very common way is
through dictionary access, using the string names of columns:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
FROM users
()
</div><span class="gp">>>> </span><span class="n">row</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="s2">"name:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s1">'name'</span><span class="p">],</span> <span class="s2">"; fullname:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s1">'fullname'</span><span class="p">])</span>
<span class="go">name: jack ; fullname: Jack Jones</span></pre></div>
</div>
<p>Integer indexes work as well:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">row</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="s2">"name:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="mi">1</span><span class="p">],</span> <span class="s2">"; fullname:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="mi">2</span><span class="p">])</span>
<span class="go">name: wendy ; fullname: Wendy Williams</span></pre></div>
</div>
<p>But another way, whose usefulness will become apparent later on, is to use the
<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> objects directly as keys:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">):</span>
<span class="gp">... </span> <span class="k">print</span><span class="p">(</span><span class="s2">"name:"</span><span class="p">,</span> <span class="n">row</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">name</span><span class="p">],</span> <span class="s2">"; fullname:"</span><span class="p">,</span> <span class="n">row</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">fullname</span><span class="p">])</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
FROM users
()
</div><span class="go">name: jack ; fullname: Jack Jones</span>
<span class="go">name: wendy ; fullname: Wendy Williams</span></pre></div>
</div>
<p>Result sets which have pending rows remaining should be explicitly closed
before discarding. While the cursor and connection resources referenced by the
<a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">ResultProxy</span></code></a> will be respectively closed and
returned to the connection pool when the object is garbage collected, it’s
better to make it explicit as some database APIs are very picky about such
things:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">result</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>If we’d like to more carefully control the columns which are placed in the
COLUMNS clause of the select, we reference individual
<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> objects from our
<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>. These are available as named attributes off
the <code class="docutils literal notranslate"><span class="pre">c</span></code> attribute of 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:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</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">name</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">fullname</span><span class="p">])</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span>
<div class='popup_sql'>SELECT users.name, users.fullname
FROM users
()
</div><span class="gp">>>> </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="gp">... </span> <span class="k">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
<span class="go">(u'jack', u'Jack Jones')</span>
<span class="go">(u'wendy', u'Wendy Williams')</span></pre></div>
</div>
<p>Lets observe something interesting about the FROM clause. Whereas the
generated statement contains two distinct sections, a “SELECT columns” part
and a “FROM table” part, our <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 only has a list
containing columns. How does this work ? Let’s try putting <em>two</em> tables into
our <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> statement:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">,</span> <span class="n">addresses</span><span class="p">])):</span>
<span class="gp">... </span> <span class="k">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
FROM users, addresses
()
</div><span class="go">(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com')</span>
<span class="go">(1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')</span>
<span class="go">(1, u'jack', u'Jack Jones', 3, 2, u'www@www.org')</span>
<span class="go">(1, u'jack', u'Jack Jones', 4, 2, u'wendy@aol.com')</span>
<span class="go">(2, u'wendy', u'Wendy Williams', 1, 1, u'jack@yahoo.com')</span>
<span class="go">(2, u'wendy', u'Wendy Williams', 2, 1, u'jack@msn.com')</span>
<span class="go">(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')</span>
<span class="go">(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')</span></pre></div>
</div>
<p>It placed <strong>both</strong> tables into the FROM clause. But also, it made a real mess.
Those who are familiar with SQL joins know that this is a <strong>Cartesian
product</strong>; each row from the <code class="docutils literal notranslate"><span class="pre">users</span></code> table is produced against each row from
the <code class="docutils literal notranslate"><span class="pre">addresses</span></code> table. So to put some sanity into this statement, we need a
WHERE clause. We do that using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.where()</span></code></a>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">,</span> <span class="n">addresses</span><span class="p">])</span><span class="o">.</span><span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">):</span>
<span class="gp">... </span> <span class="k">print</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id,
addresses.user_id, addresses.email_address
FROM users, addresses
WHERE users.id = addresses.user_id
()
</div><span class="go">(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com')</span>
<span class="go">(1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')</span>
<span class="go">(2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')</span>
<span class="go">(2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')</span></pre></div>
</div>
<p>So that looks a lot better, we added an expression to our <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>
which had the effect of adding <code class="docutils literal notranslate"><span class="pre">WHERE</span> <span class="pre">users.id</span> <span class="pre">=</span> <span class="pre">addresses.user_id</span></code> to our
statement, and our results were managed down so that the join of <code class="docutils literal notranslate"><span class="pre">users</span></code> and
<code class="docutils literal notranslate"><span class="pre">addresses</span></code> rows made sense. But let’s look at that expression? It’s using
just a Python equality operator between two different
<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> objects. It should be clear that something
is up. Saying <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">==</span> <span class="pre">1</span></code> produces <code class="docutils literal notranslate"><span class="pre">True</span></code>, and <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">==</span> <span class="pre">2</span></code> produces <code class="docutils literal notranslate"><span class="pre">False</span></code>, not
a WHERE clause. So lets see exactly what that expression is doing:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span>
<span class="go"><sqlalchemy.sql.elements.BinaryExpression object at 0x...></span></pre></div>
</div>
<p>Wow, surprise ! This is neither a <code class="docutils literal notranslate"><span class="pre">True</span></code> nor a <code class="docutils literal notranslate"><span class="pre">False</span></code>. Well what is it ?</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="nb">str</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
<span class="go">'users.id = addresses.user_id'</span></pre></div>
</div>
<p>As you can see, the <code class="docutils literal notranslate"><span class="pre">==</span></code> operator is producing an object that is very much
like the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> and <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>
objects we’ve made so far, thanks to Python’s <code class="docutils literal notranslate"><span class="pre">__eq__()</span></code> builtin; you call
<code class="docutils literal notranslate"><span class="pre">str()</span></code> on it and it produces SQL. By now, one can see that everything we
are working with is ultimately the same type of object. SQLAlchemy terms the
base class of all of these expressions as <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnElement</span></code></a>.</p>
</div>
<div class="section" id="operators">
<h2>Operators<a class="headerlink" href="#operators" title="Permalink to this headline">¶</a></h2>
<p>Since we’ve stumbled upon SQLAlchemy’s operator paradigm, let’s go through
some of its capabilities. We’ve seen how to equate two columns to each other:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
<span class="go">users.id = addresses.user_id</span></pre></div>
</div>
<p>If we use a literal value (a literal meaning, not a SQLAlchemy clause object),
we get a bind parameter:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">id</span> <span class="o">==</span> <span class="mi">7</span><span class="p">)</span>
<span class="go">users.id = :id_1</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">7</span></code> literal is embedded the resulting
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnElement</span></code></a>; we can use the same trick
we did with the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> object to see it:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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">id</span> <span class="o">==</span> <span class="mi">7</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">()</span><span class="o">.</span><span class="n">params</span>
<span class="go">{u'id_1': 7}</span></pre></div>
</div>
<p>Most Python operators, as it turns out, produce a SQL expression here, like
equals, not equals, etc.:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">id</span> <span class="o">!=</span> <span class="mi">7</span><span class="p">)</span>
<span class="go">users.id != :id_1</span>
<span class="gp">>>> </span><span class="c1"># None converts to IS NULL</span>
<span class="gp">>>> </span><span class="k">print</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">name</span> <span class="o">==</span> <span class="bp">None</span><span class="p">)</span>
<span class="go">users.name IS NULL</span>
<span class="gp">>>> </span><span class="c1"># reverse works too</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="s1">'fred'</span> <span class="o">></span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="go">users.name < :name_1</span></pre></div>
</div>
<p>If we add two integer columns together, we get an addition expression:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">id</span> <span class="o">+</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="go">users.id + addresses.id</span></pre></div>
</div>
<p>Interestingly, the type of the <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> is important!
If we use <code class="docutils literal notranslate"><span class="pre">+</span></code> with two string based columns (recall we put types like
<a class="reference internal" href="type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">Integer</span></code></a> and <a class="reference internal" href="type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> on
our <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> objects at the beginning), we get
something different:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">name</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">)</span>
<span class="go">users.name || users.fullname</span></pre></div>
</div>
<p>Where <code class="docutils literal notranslate"><span class="pre">||</span></code> is the string concatenation operator used on most databases. But
not all of them. MySQL users, fear not:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">name</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">)</span><span class="o">.</span>
<span class="gp">... </span> <span class="nb">compile</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">create_engine</span><span class="p">(</span><span class="s1">'mysql://'</span><span class="p">)))</span>
<span class="go">concat(users.name, users.fullname)</span></pre></div>
</div>
<p>The above illustrates the SQL that’s generated for an
<a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> that’s connected to a MySQL database;
the <code class="docutils literal notranslate"><span class="pre">||</span></code> operator now compiles as MySQL’s <code class="docutils literal notranslate"><span class="pre">concat()</span></code> function.</p>
<p>If you have come across an operator which really isn’t available, you can
always use the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a> method; this generates whatever operator you need:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">name</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'tiddlywinks'</span><span class="p">)(</span><span class="s1">'foo'</span><span class="p">))</span>
<span class="go">users.name tiddlywinks :name_1</span></pre></div>
</div>
<p>This function can also be used to make bitwise operators explicit. For example:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'&'</span><span class="p">)(</span><span class="mh">0xff</span><span class="p">)</span></pre></div>
</div>
<p>is a bitwise AND of the value in <code class="docutils literal notranslate"><span class="pre">somecolumn</span></code>.</p>
<p>When using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a>, the return type of the expression may be important,
especially when the operator is used in an expression that will be sent as a result
column. For this case, be sure to make the type explicit, if not what’s
normally expected, using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.type_coerce" title="sqlalchemy.sql.expression.type_coerce"><code class="xref py py-func docutils literal notranslate"><span class="pre">type_coerce()</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">type_coerce</span>
<span class="n">expr</span> <span class="o">=</span> <span class="n">type_coerce</span><span class="p">(</span><span class="n">somecolumn</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">)(</span><span class="s1">'foo'</span><span class="p">),</span> <span class="n">MySpecialType</span><span class="p">())</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">expr</span><span class="p">])</span></pre></div>
</div>
<p>For boolean operators, use the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.bool_op" title="sqlalchemy.sql.operators.Operators.bool_op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.bool_op()</span></code></a> method, which
will ensure that the return type of the expression is handled as boolean:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">bool_op</span><span class="p">(</span><span class="s1">'-->'</span><span class="p">)(</span><span class="s1">'some value'</span><span class="p">)</span></pre></div>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.0b3: </span>Added the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.bool_op" title="sqlalchemy.sql.operators.Operators.bool_op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.bool_op()</span></code></a> method.</p>
</div>
<div class="section" id="operator-customization">
<h3>Operator Customization<a class="headerlink" href="#operator-customization" title="Permalink to this headline">¶</a></h3>
<p>While <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a> is handy to get at a custom operator in a hurry,
the Core supports fundamental customization and extension of the operator system at
the type level. The behavior of existing operators can be modified on a per-type
basis, and new operations can be defined which become available for all column
expressions that are part of that particular type. See the section <a class="reference internal" href="custom_types.html#types-operators"><span class="std std-ref">Redefining and Creating New Operators</span></a>
for a description.</p>
</div>
</div>
<div class="section" id="conjunctions">
<h2>Conjunctions<a class="headerlink" href="#conjunctions" title="Permalink to this headline">¶</a></h2>
<p>We’d like to show off some of our operators inside of <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>
constructs. But we need to lump them together a little more, so let’s first
introduce some conjunctions. Conjunctions are those little words like AND and
OR that put things together. We’ll also hit upon NOT. <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><code class="xref py py-func docutils literal notranslate"><span class="pre">and_()</span></code></a>, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.or_" title="sqlalchemy.sql.expression.or_"><code class="xref py py-func docutils literal notranslate"><span class="pre">or_()</span></code></a>,
and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.not_" title="sqlalchemy.sql.expression.not_"><code class="xref py py-func docutils literal notranslate"><span class="pre">not_()</span></code></a> can work
from the corresponding functions SQLAlchemy provides (notice we also throw in
a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">like()</span></code></a>):</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">and_</span><span class="p">,</span> <span class="n">or_</span><span class="p">,</span> <span class="n">not_</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'j%'</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">or_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'wendy@aol.com'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'jack@yahoo.com'</span>
<span class="gp">... </span> <span class="p">),</span>
<span class="gp">... </span> <span class="n">not_</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">id</span> <span class="o">></span> <span class="mi">5</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="go">users.name LIKE :name_1 AND users.id = addresses.user_id AND</span>
<span class="go">(addresses.email_address = :email_address_1</span>
<span class="go"> OR addresses.email_address = :email_address_2)</span>
<span class="go">AND users.id <= :id_1</span></pre></div>
</div>
<p>And you can also use the re-jiggered bitwise AND, OR and NOT operators,
although because of Python operator precedence you have to watch your
parenthesis:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</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">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'j%'</span><span class="p">)</span> <span class="o">&</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span> <span class="o">&</span>
<span class="gp">... </span> <span class="p">(</span>
<span class="gp">... </span> <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'wendy@aol.com'</span><span class="p">)</span> <span class="o">|</span> \
<span class="gp">... </span> <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'jack@yahoo.com'</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span> \
<span class="gp">... </span> <span class="o">&</span> <span class="o">~</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">id</span><span class="o">></span><span class="mi">5</span><span class="p">)</span>
<span class="gp">... </span><span class="p">)</span>
<span class="go">users.name LIKE :name_1 AND users.id = addresses.user_id AND</span>
<span class="go">(addresses.email_address = :email_address_1</span>
<span class="go"> OR addresses.email_address = :email_address_2)</span>
<span class="go">AND users.id <= :id_1</span></pre></div>
</div>
<p>So with all of this vocabulary, let’s select all users who have an email
address at AOL or MSN, whose name starts with a letter between “m” and “z”,
and we’ll also generate a column containing their full name combined with
their email address. We will add two new constructs to this statement,
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><code class="xref py py-meth docutils literal notranslate"><span class="pre">between()</span></code></a> and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><code class="xref py py-meth docutils literal notranslate"><span class="pre">label()</span></code></a>.
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><code class="xref py py-meth docutils literal notranslate"><span class="pre">between()</span></code></a> produces a BETWEEN clause, and
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><code class="xref py py-meth docutils literal notranslate"><span class="pre">label()</span></code></a> is used in a column expression to produce labels using the <code class="docutils literal notranslate"><span class="pre">AS</span></code>
keyword; it’s recommended when selecting from expressions that otherwise would
not have a name:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</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">fullname</span> <span class="o">+</span>
<span class="gp">... </span> <span class="s2">", "</span> <span class="o">+</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">label</span><span class="p">(</span><span class="s1">'title'</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">and_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">between</span><span class="p">(</span><span class="s1">'m'</span><span class="p">,</span> <span class="s1">'z'</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">or_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@aol.com'</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@msn.com'</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="go">SELECT users.fullname || ? || addresses.email_address AS title</span>
<span class="go">FROM users, addresses</span>
<span class="go">WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND</span>
<span class="go">(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)</span>
<span class="go">(', ', 'm', 'z', '%@aol.com', '%@msn.com')</span>
<span class="go">[(u'Wendy Williams, wendy@aol.com',)]</span></pre></div>
</div>
<p>Once again, SQLAlchemy figured out the FROM clause for our statement. In fact
it will determine the FROM clause based on all of its other bits; the columns
clause, the where clause, and also some other elements which we haven’t
covered yet, which include ORDER BY, GROUP BY, and HAVING.</p>
<p>A shortcut to using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><code class="xref py py-func docutils literal notranslate"><span class="pre">and_()</span></code></a> is to chain together multiple
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><code class="xref py py-meth docutils literal notranslate"><span class="pre">where()</span></code></a> clauses. The above can also be written as:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</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">fullname</span> <span class="o">+</span>
<span class="gp">... </span> <span class="s2">", "</span> <span class="o">+</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">label</span><span class="p">(</span><span class="s1">'title'</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">name</span><span class="o">.</span><span class="n">between</span><span class="p">(</span><span class="s1">'m'</span><span class="p">,</span> <span class="s1">'z'</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">or_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@aol.com'</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@msn.com'</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="go">SELECT users.fullname || ? || addresses.email_address AS title</span>
<span class="go">FROM users, addresses</span>
<span class="go">WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND</span>
<span class="go">(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)</span>
<span class="go">(', ', 'm', 'z', '%@aol.com', '%@msn.com')</span>
<span class="go">[(u'Wendy Williams, wendy@aol.com',)]</span></pre></div>
</div>
<p>The way that we can build up 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 through successive
method calls is called <a class="reference internal" href="../glossary.html#term-method-chaining"><span class="xref std std-term">method chaining</span></a>.</p>
</div>
<div class="section" id="using-textual-sql">
<span id="sqlexpression-text"></span><h2>Using Textual SQL<a class="headerlink" href="#using-textual-sql" title="Permalink to this headline">¶</a></h2>
<p>Our last example really became a handful to type. Going from what one
understands to be a textual SQL expression into a Python construct which
groups components together in a programmatic style can be hard. That’s why
SQLAlchemy lets you just use strings, for those cases when the SQL
is already known and there isn’t a strong need for the statement to support
dynamic features. 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 is used
to compose a textual statement that is passed to the database mostly
unchanged. Below, we create 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> object and execute it:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">text</span>
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span>
<span class="gp">... </span> <span class="s2">"SELECT users.fullname || ', ' || addresses.email_address AS title "</span>
<span class="gp">... </span> <span class="s2">"FROM users, addresses "</span>
<span class="gp">... </span> <span class="s2">"WHERE users.id = addresses.user_id "</span>
<span class="gp">... </span> <span class="s2">"AND users.name BETWEEN :x AND :y "</span>
<span class="gp">... </span> <span class="s2">"AND (addresses.email_address LIKE :e1 "</span>
<span class="gp">... </span> <span class="s2">"OR addresses.email_address LIKE :e2)"</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="o">=</span><span class="s1">'m'</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s1">'z'</span><span class="p">,</span> <span class="n">e1</span><span class="o">=</span><span class="s1">'%@aol.com'</span><span class="p">,</span> <span class="n">e2</span><span class="o">=</span><span class="s1">'%@msn.com'</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.fullname || ', ' || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
('m', 'z', '%@aol.com', '%@msn.com')
</div><span class="go">[(u'Wendy Williams, wendy@aol.com',)]</span></pre></div>
</div>
<p>Above, we can see that bound parameters are specified in
<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> using the named colon format; this format is
consistent regardless of database backend. To send values in for the
parameters, we passed them into the <a class="reference internal" href="connections.html#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
as additional arguments.</p>
<div class="section" id="specifying-bound-parameter-behaviors">
<h3>Specifying Bound Parameter Behaviors<a class="headerlink" href="#specifying-bound-parameter-behaviors" title="Permalink to this headline">¶</a></h3>
<p>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 supports pre-established bound values
using the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.bindparams" title="sqlalchemy.sql.expression.TextClause.bindparams"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.bindparams()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT * FROM users WHERE users.name BETWEEN :x AND :y"</span><span class="p">)</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">bindparams</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="s2">"m"</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s2">"z"</span><span class="p">)</span></pre></div>
</div>
<p>The parameters can also be explicitly typed:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">bindparams</span><span class="p">(</span><span class="n">bindparam</span><span class="p">(</span><span class="s2">"x"</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">),</span> <span class="n">bindparam</span><span class="p">(</span><span class="s2">"y"</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">))</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">,</span> <span class="p">{</span><span class="s2">"x"</span><span class="p">:</span> <span class="s2">"m"</span><span class="p">,</span> <span class="s2">"y"</span><span class="p">:</span> <span class="s2">"z"</span><span class="p">})</span></pre></div>
</div>
<p>Typing for bound parameters is necessary when the type requires Python-side
or special SQL-side processing provided by the datatype.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.bindparams" title="sqlalchemy.sql.expression.TextClause.bindparams"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.bindparams()</span></code></a> - full method description</p>
</div>
</div>
<div class="section" id="specifying-result-column-behaviors">
<span id="sqlexpression-text-columns"></span><h3>Specifying Result-Column Behaviors<a class="headerlink" href="#specifying-result-column-behaviors" title="Permalink to this headline">¶</a></h3>
<p>We may also specify information about the result columns using the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.columns" title="sqlalchemy.sql.expression.TextClause.columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.columns()</span></code></a> method; this method can be used to specify
the return types, based on name:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">columns</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="n">Integer</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="n">String</span><span class="p">)</span></pre></div>
</div>
<p>or it can be passed full column expressions positionally, either typed
or untyped. In this case it’s a good idea to list out the columns
explicitly within our textual SQL, since the correlation of our column
expressions to the SQL will be done positionally:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT id, name FROM users"</span><span class="p">)</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">columns</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">id</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">name</span><span class="p">)</span></pre></div>
</div>
<p>When we call the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.columns" title="sqlalchemy.sql.expression.TextClause.columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.columns()</span></code></a> method, we get back a
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TextAsFrom" title="sqlalchemy.sql.expression.TextAsFrom"><code class="xref py py-class docutils literal notranslate"><span class="pre">TextAsFrom</span></code></a> object that supports the full suite of
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TextAsFrom.c" title="sqlalchemy.sql.expression.TextAsFrom.c"><code class="xref py py-attr docutils literal notranslate"><span class="pre">TextAsFrom.c</span></code></a> and other “selectable” operations:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">j</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span> <span class="n">stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
<span class="n">new_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">])</span><span class="o">.</span>\
<span class="n">select_from</span><span class="p">(</span><span class="n">j</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s1">'x'</span><span class="p">)</span></pre></div>
</div>
<p>The positional form of <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.columns" title="sqlalchemy.sql.expression.TextClause.columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.columns()</span></code></a> is particularly useful
when relating textual SQL to existing Core or ORM models, because we can use
column expressions directly without worrying about name conflicts or other issues with the
result column names in the textual SQL:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT users.id, addresses.id, users.id, "</span>
<span class="gp">... </span> <span class="s2">"users.name, addresses.email_address AS email "</span>
<span class="gp">... </span> <span class="s2">"FROM users JOIN addresses ON users.id=addresses.user_id "</span>
<span class="gp">... </span> <span class="s2">"WHERE users.id = 1"</span><span class="p">)</span><span class="o">.</span><span class="n">columns</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span>
<span class="gp">... </span> <span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span>
<div class='popup_sql'>SELECT users.id, addresses.id, users.id, users.name,
addresses.email_address AS email
FROM users JOIN addresses ON users.id=addresses.user_id WHERE users.id = 1
()
</div></pre></div>
</div>
<p>Above, there’s three columns in the result that are named “id”, but since
we’ve associated these with column expressions positionally, the names aren’t an issue
when the result-columns are fetched using the actual column object as a key.
Fetching the <code class="docutils literal notranslate"><span class="pre">email_address</span></code> column would be:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">row</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">row</span><span class="p">[</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">]</span>
<span class="go">'jack@yahoo.com'</span></pre></div>
</div>
<p>If on the other hand we used a string column key, the usual rules of name-
based matching still apply, and we’d get an ambiguous column error for
the <code class="docutils literal notranslate"><span class="pre">id</span></code> value:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">row</span><span class="p">[</span><span class="s2">"id"</span><span class="p">]</span>
<span class="gt">Traceback (most recent call last):</span>
<span class="c">...</span>
<span class="gr">InvalidRequestError</span>: <span class="n">Ambiguous column name 'id' in result set column descriptions</span></pre></div>
</div>
<p>It’s important to note that while accessing columns from a result set using
<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> objects may seem unusual, it is in fact the only system
used by the ORM, which occurs transparently beneath the facade of the
<a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><code class="xref py py-class docutils literal notranslate"><span class="pre">Query</span></code></a> object; in this way, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.columns" title="sqlalchemy.sql.expression.TextClause.columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.columns()</span></code></a> method
is typically very applicable to textual statements to be used in an ORM
context. The example at <a class="reference internal" href="../orm/tutorial.html#orm-tutorial-literal-sql"><span class="std std-ref">Using Textual SQL</span></a> illustrates
a simple usage.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1: </span>The <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.columns" title="sqlalchemy.sql.expression.TextClause.columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.columns()</span></code></a> method now accepts column expressions
which will be matched positionally to a plain text SQL result set,
eliminating the need for column names to match or even be unique in the
SQL statement when matching table metadata or ORM models to textual SQL.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.TextClause.columns" title="sqlalchemy.sql.expression.TextClause.columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.columns()</span></code></a> - full method description</p>
<p><a class="reference internal" href="../orm/tutorial.html#orm-tutorial-literal-sql"><span class="std std-ref">Using Textual SQL</span></a> - integrating ORM-level queries with
<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></p>
</div>
</div>
<div class="section" id="using-text-fragments-inside-bigger-statements">
<h3>Using text() fragments inside bigger statements<a class="headerlink" href="#using-text-fragments-inside-bigger-statements" title="Permalink to this headline">¶</a></h3>
<p><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> can also be used to produce fragments of SQL
that can be freely within 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> object, which accepts <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>
objects as an argument for most of its builder functions.
Below, we combine the usage of <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> within 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> object. 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 provides the “geometry”
of the statement, and 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 provides the
textual content within this form. We can build a statement without the
need to refer to any pre-established <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> metadata:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">text</span><span class="p">(</span><span class="s2">"users.fullname || ', ' || addresses.email_address AS title"</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">and_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">text</span><span class="p">(</span><span class="s2">"users.id = addresses.user_id"</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">text</span><span class="p">(</span><span class="s2">"users.name BETWEEN 'm' AND 'z'"</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">text</span><span class="p">(</span>
<span class="gp">... </span> <span class="s2">"(addresses.email_address LIKE :x "</span>
<span class="gp">... </span> <span class="s2">"OR addresses.email_address LIKE :y)"</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s1">'users, addresses'</span><span class="p">))</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="o">=</span><span class="s1">'%@aol.com'</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s1">'%@msn.com'</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.fullname || ', ' || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z'
AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
('%@aol.com', '%@msn.com')
</div><span class="go">[(u'Wendy Williams, wendy@aol.com',)]</span></pre></div>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.0.0: </span>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 emits warnings when string SQL
fragments are coerced to <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>, and <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> should
be used explicitly. See <a class="reference internal" href="../changelog/migration_10.html#migration-2992"><span class="std std-ref">Warnings emitted when coercing full SQL fragments into text()</span></a> for background.</p>
</div>
</div>
<div class="section" id="using-more-specific-text-with-table-literal-column-and-column">
<span id="sqlexpression-literal-column"></span><h3>Using More Specific Text with <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.table" title="sqlalchemy.sql.expression.table"><code class="xref py py-func docutils literal notranslate"><span class="pre">table()</span></code></a>, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.literal_column" title="sqlalchemy.sql.expression.literal_column"><code class="xref py py-func docutils literal notranslate"><span class="pre">literal_column()</span></code></a>, and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.column" title="sqlalchemy.sql.expression.column"><code class="xref py py-func docutils literal notranslate"><span class="pre">column()</span></code></a><a class="headerlink" href="#using-more-specific-text-with-table-literal-column-and-column" title="Permalink to this headline">¶</a></h3>
<p>We can move our level of structure back in the other direction too,
by using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.column" title="sqlalchemy.sql.expression.column"><code class="xref py py-func docutils literal notranslate"><span class="pre">column()</span></code></a>, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.literal_column" title="sqlalchemy.sql.expression.literal_column"><code class="xref py py-func docutils literal notranslate"><span class="pre">literal_column()</span></code></a>,
and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.table" title="sqlalchemy.sql.expression.table"><code class="xref py py-func docutils literal notranslate"><span class="pre">table()</span></code></a> for some of the
key elements of our statement. Using these constructs, we can get
some more expression capabilities than if we used <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>
directly, as they provide to the Core more information about how the strings
they store are to be used, but still without the need to get into full
<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> based metadata. Below, we also specify the <a class="reference internal" href="type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a>
datatype for two of the key <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.literal_column" title="sqlalchemy.sql.expression.literal_column"><code class="xref py py-func docutils literal notranslate"><span class="pre">literal_column()</span></code></a> objects,
so that the string-specific concatenation operator becomes available.
We also use <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.literal_column" title="sqlalchemy.sql.expression.literal_column"><code class="xref py py-func docutils literal notranslate"><span class="pre">literal_column()</span></code></a> in order to use table-qualified
expressions, e.g. <code class="docutils literal notranslate"><span class="pre">users.fullname</span></code>, that will be rendered as is;
using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.column" title="sqlalchemy.sql.expression.column"><code class="xref py py-func docutils literal notranslate"><span class="pre">column()</span></code></a> implies an individual column name that may
be quoted:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">and_</span><span class="p">,</span> <span class="n">text</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">table</span><span class="p">,</span> <span class="n">literal_column</span>
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">literal_column</span><span class="p">(</span><span class="s2">"users.fullname"</span><span class="p">,</span> <span class="n">String</span><span class="p">)</span> <span class="o">+</span>
<span class="gp">... </span> <span class="s1">', '</span> <span class="o">+</span>
<span class="gp">... </span> <span class="n">literal_column</span><span class="p">(</span><span class="s2">"addresses.email_address"</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s2">"title"</span><span class="p">)</span>
<span class="gp">... </span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">and_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">literal_column</span><span class="p">(</span><span class="s2">"users.id"</span><span class="p">)</span> <span class="o">==</span> <span class="n">literal_column</span><span class="p">(</span><span class="s2">"addresses.user_id"</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">text</span><span class="p">(</span><span class="s2">"users.name BETWEEN 'm' AND 'z'"</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">text</span><span class="p">(</span>
<span class="gp">... </span> <span class="s2">"(addresses.email_address LIKE :x OR "</span>
<span class="gp">... </span> <span class="s2">"addresses.email_address LIKE :y)"</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">table</span><span class="p">(</span><span class="s1">'users'</span><span class="p">))</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">table</span><span class="p">(</span><span class="s1">'addresses'</span><span class="p">))</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="o">=</span><span class="s1">'%@aol.com'</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s1">'%@msn.com'</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.fullname || ? || addresses.email_address AS anon_1
FROM users, addresses
WHERE users.id = addresses.user_id
AND users.name BETWEEN 'm' AND 'z'
AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
(', ', '%@aol.com', '%@msn.com')
</div><span class="go">[(u'Wendy Williams, wendy@aol.com',)]</span></pre></div>
</div>
</div>
<div class="section" id="ordering-or-grouping-by-a-label">
<h3>Ordering or Grouping by a Label<a class="headerlink" href="#ordering-or-grouping-by-a-label" title="Permalink to this headline">¶</a></h3>
<p>One place where we sometimes want to use a string as a shortcut is when
our statement has some labeled column element that we want to refer to in
a place such as the “ORDER BY” or “GROUP BY” clause; other candidates include
fields within an “OVER” or “DISTINCT” clause. If we have such a label
in our <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, we can refer to it directly by passing the
string straight into <code class="xref py py-meth docutils literal notranslate"><span class="pre">select.order_by()</span></code> or <code class="xref py py-meth docutils literal notranslate"><span class="pre">select.group_by()</span></code>,
among others. This will refer to the named label and also prevent the
expression from being rendered twice. Label names that resolve to columns
are rendered fully:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">func</span>
<span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s1">'num_addresses'</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">group_by</span><span class="p">(</span><span class="s2">"user_id"</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s2">"user_id"</span><span class="p">,</span> <span class="s2">"num_addresses"</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT addresses.user_id, count(addresses.id) AS num_addresses
FROM addresses GROUP BY addresses.user_id ORDER BY addresses.user_id, num_addresses
()
</div><span class="go">[(1, 2), (2, 2)]</span></pre></div>
</div>
<p>We can use modifiers like <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.asc" title="sqlalchemy.sql.expression.asc"><code class="xref py py-func docutils literal notranslate"><span class="pre">asc()</span></code></a> or <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.desc" title="sqlalchemy.sql.expression.desc"><code class="xref py py-func docutils literal notranslate"><span class="pre">desc()</span></code></a> by passing the string
name:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">func</span><span class="p">,</span> <span class="n">desc</span>
<span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s1">'num_addresses'</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">group_by</span><span class="p">(</span><span class="s2">"user_id"</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s2">"user_id"</span><span class="p">,</span> <span class="n">desc</span><span class="p">(</span><span class="s2">"num_addresses"</span><span class="p">))</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT addresses.user_id, count(addresses.id) AS num_addresses
FROM addresses GROUP BY addresses.user_id ORDER BY addresses.user_id, num_addresses DESC
()
</div><span class="go">[(1, 2), (2, 2)]</span></pre></div>
</div>
<p>Note that the string feature here is very much tailored to when we have
already used the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><code class="xref py py-meth docutils literal notranslate"><span class="pre">label()</span></code></a> method to create a
specifically-named label. In other cases, we always want to refer to the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnElement</span></code></a> object directly so that the expression system can
make the most effective choices for rendering. Below, we illustrate how using
the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnElement</span></code></a> eliminates ambiguity when we want to order
by a column name that appears more than once:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">u1a</span><span class="p">,</span> <span class="n">u1b</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">alias</span><span class="p">(),</span> <span class="n">users</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">u1a</span><span class="p">,</span> <span class="n">u1b</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">u1a</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">></span> <span class="n">u1b</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">order_by</span><span class="p">(</span><span class="n">u1a</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span> <span class="c1"># using "name" here would be ambiguous</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users_1.id, users_1.name, users_1.fullname, users_2.id,
users_2.name, users_2.fullname
FROM users AS users_1, users AS users_2
WHERE users_1.name > users_2.name ORDER BY users_1.name
()
</div><span class="go">[(2, u'wendy', u'Wendy Williams', 1, u'jack', u'Jack Jones')]</span></pre></div>
</div>
</div>
</div>
<div class="section" id="using-aliases">
<h2>Using Aliases<a class="headerlink" href="#using-aliases" title="Permalink to this headline">¶</a></h2>
<p>The alias in SQL corresponds to a “renamed” version of a table or SELECT
statement, which occurs anytime you say “SELECT .. FROM sometable AS
someothername”. The <code class="docutils literal notranslate"><span class="pre">AS</span></code> creates a new name for the table. Aliases are a key
construct as they allow any table or subquery to be referenced by a unique
name. In the case of a table, this allows the same table to be named in the
FROM clause multiple times. In the case of a SELECT statement, it provides a
parent name for the columns represented by the statement, allowing them to be
referenced relative to this name.</p>
<p>In SQLAlchemy, any <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="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, or
other selectable can be turned into an alias using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.alias" title="sqlalchemy.sql.expression.FromClause.alias"><code class="xref py py-meth docutils literal notranslate"><span class="pre">FromClause.alias()</span></code></a>
method, which produces a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><code class="xref py py-class docutils literal notranslate"><span class="pre">Alias</span></code></a> construct. As an example, suppose we know that our user <code class="docutils literal notranslate"><span class="pre">jack</span></code> has two
particular email addresses. How can we locate jack based on the combination of those two
addresses? To accomplish this, we’d use a join to the <code class="docutils literal notranslate"><span class="pre">addresses</span></code> table,
once for each address. We create two <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><code class="xref py py-class docutils literal notranslate"><span class="pre">Alias</span></code></a> constructs against
<code class="docutils literal notranslate"><span class="pre">addresses</span></code>, and then use them both within 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>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">a1</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">a2</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">a2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'jack@msn.com'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">a2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'jack@yahoo.com'</span>
<span class="gp">... </span> <span class="p">))</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
FROM users, addresses AS addresses_1, addresses AS addresses_2
WHERE users.id = addresses_1.user_id
AND users.id = addresses_2.user_id
AND addresses_1.email_address = ?
AND addresses_2.email_address = ?
('jack@msn.com', 'jack@yahoo.com')
</div><span class="go">[(1, u'jack', u'Jack Jones')]</span></pre></div>
</div>
<p>Note that the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><code class="xref py py-class docutils literal notranslate"><span class="pre">Alias</span></code></a> construct generated the names <code class="docutils literal notranslate"><span class="pre">addresses_1</span></code> and
<code class="docutils literal notranslate"><span class="pre">addresses_2</span></code> in the final SQL result. The generation of these names is determined
by the position of the construct within the statement. If we created a query using
only the second <code class="docutils literal notranslate"><span class="pre">a2</span></code> alias, the name would come out as <code class="docutils literal notranslate"><span class="pre">addresses_1</span></code>. The
generation of the names is also <em>deterministic</em>, meaning the same SQLAlchemy
statement construct will produce the identical SQL string each time it is
rendered for a particular dialect.</p>
<p>Since on the outside, we refer to the alias using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><code class="xref py py-class docutils literal notranslate"><span class="pre">Alias</span></code></a> construct
itself, we don’t need to be concerned about the generated name. However, for
the purposes of debugging, it can be specified by passing a string name
to the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.alias" title="sqlalchemy.sql.expression.FromClause.alias"><code class="xref py py-meth docutils literal notranslate"><span class="pre">FromClause.alias()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">a1</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s1">'a1'</span><span class="p">)</span></pre></div>
</div>
<p>Aliases can of course be used for anything which you can SELECT from,
including SELECT statements themselves. We can self-join the <code class="docutils literal notranslate"><span class="pre">users</span></code> table
back to 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> we’ve created by making an alias of the entire
statement. The <code class="docutils literal notranslate"><span class="pre">correlate(None)</span></code> directive is to avoid SQLAlchemy’s attempt
to “correlate” the inner <code class="docutils literal notranslate"><span class="pre">users</span></code> table with the outer one:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">a1</span> <span class="o">=</span> <span class="n">s</span><span class="o">.</span><span class="n">correlate</span><span class="p">(</span><span class="bp">None</span><span class="p">)</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">])</span><span class="o">.</span><span class="n">where</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">id</span> <span class="o">==</span> <span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.name
FROM users,
(SELECT users.id AS id, users.name AS name, users.fullname AS fullname
FROM users, addresses AS addresses_1, addresses AS addresses_2
WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id
AND addresses_1.email_address = ?
AND addresses_2.email_address = ?) AS anon_1
WHERE users.id = anon_1.id
('jack@msn.com', 'jack@yahoo.com')
</div><span class="go">[(u'jack',)]</span></pre></div>
</div>
</div>
<div class="section" id="using-joins">
<h2>Using Joins<a class="headerlink" href="#using-joins" title="Permalink to this headline">¶</a></h2>
<p>We’re halfway along to being able to construct any SELECT expression. The next
cornerstone of the SELECT is the JOIN expression. We’ve already been doing
joins in our examples, by just placing two tables in either the columns clause
or the where clause of 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. But if we want to make a
real “JOIN” or “OUTERJOIN” construct, we use the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><code class="xref py py-meth docutils literal notranslate"><span class="pre">join()</span></code></a> and
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">outerjoin()</span></code></a> methods, most commonly accessed from the left table in the
join:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span>
<span class="go">users JOIN addresses ON users.id = addresses.user_id</span></pre></div>
</div>
<p>The alert reader will see more surprises; SQLAlchemy figured out how to JOIN
the two tables ! The ON condition of the join, as it’s called, was
automatically generated based on the <a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a>
object which we placed on the <code class="docutils literal notranslate"><span class="pre">addresses</span></code> table way at the beginning of this
tutorial. Already the <code class="docutils literal notranslate"><span class="pre">join()</span></code> construct is looking like a much better way
to join tables.</p>
<p>Of course you can join on whatever expression you want, such as if we want to
join on all users who use the same name in their email address as their
username:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</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">name</span> <span class="o">+</span> <span class="s1">'%'</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="go">users JOIN addresses ON addresses.email_address LIKE users.name || :name_1</span></pre></div>
</div>
<p>When we create 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, SQLAlchemy looks around at the
tables we’ve mentioned and then places them in the FROM clause of the
statement. When we use JOINs however, we know what FROM clause we want, so
here we make use of the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">select_from()</span></code></a> method:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</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">fullname</span><span class="p">])</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</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">name</span> <span class="o">+</span> <span class="s1">'%'</span><span class="p">))</span>
<span class="gp">... </span> <span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.fullname
FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
('%',)
</div><span class="go">[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]</span></pre></div>
</div>
<p>The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">outerjoin()</span></code></a> method creates <code class="docutils literal notranslate"><span class="pre">LEFT</span> <span class="pre">OUTER</span> <span class="pre">JOIN</span></code> constructs,
and is used in the same way as <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><code class="xref py py-meth docutils literal notranslate"><span class="pre">join()</span></code></a>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</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">fullname</span><span class="p">])</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">s</span><span class="p">)</span>
<span class="go">SELECT users.fullname</span>
<span class="go"> FROM users</span>
<span class="go"> LEFT OUTER JOIN addresses ON users.id = addresses.user_id</span></pre></div>
</div>
<p>That’s the output <code class="docutils literal notranslate"><span class="pre">outerjoin()</span></code> produces, unless, of course, you’re stuck in
a gig using Oracle prior to version 9, and you’ve set up your engine (which
would be using <code class="docutils literal notranslate"><span class="pre">OracleDialect</span></code>) to use Oracle-specific SQL:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.oracle</span> <span class="kn">import</span> <span class="n">dialect</span> <span class="k">as</span> <span class="n">OracleDialect</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">s</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">OracleDialect</span><span class="p">(</span><span class="n">use_ansi</span><span class="o">=</span><span class="bp">False</span><span class="p">)))</span>
<span class="go">SELECT users.fullname</span>
<span class="go">FROM users, addresses</span>
<span class="go">WHERE users.id = addresses.user_id(+)</span></pre></div>
</div>
<p>If you don’t know what that SQL means, don’t worry ! The secret tribe of
Oracle DBAs don’t want their black magic being found out ;).</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.join" title="sqlalchemy.sql.expression.join"><code class="xref py py-func docutils literal notranslate"><span class="pre">expression.join()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.outerjoin" title="sqlalchemy.sql.expression.outerjoin"><code class="xref py py-func docutils literal notranslate"><span class="pre">expression.outerjoin()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Join" title="sqlalchemy.sql.expression.Join"><code class="xref py py-class docutils literal notranslate"><span class="pre">Join</span></code></a></p>
</div>
</div>
<div class="section" id="everything-else">
<h2>Everything Else<a class="headerlink" href="#everything-else" title="Permalink to this headline">¶</a></h2>
<p>The concepts of creating SQL expressions have been introduced. What’s left are
more variants of the same themes. So now we’ll catalog the rest of the
important things we’ll need to know.</p>
<div class="section" id="bind-parameter-objects">
<span id="coretutorial-bind-param"></span><h3>Bind Parameter Objects<a class="headerlink" href="#bind-parameter-objects" title="Permalink to this headline">¶</a></h3>
<p>Throughout all these examples, SQLAlchemy is busy creating bind parameters
wherever literal expressions occur. You can also specify your own bind
parameters with your own names, and use the same statement repeatedly.
The <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> construct is used to produce a bound parameter
with a given name. While SQLAlchemy always refers to bound parameters by
name on the API side, the
database dialect converts to the appropriate named or positional style
at execution time, as here where it converts to positional for SQLite:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">bindparam</span>
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">select</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">name</span> <span class="o">==</span> <span class="n">bindparam</span><span class="p">(</span><span class="s1">'username'</span><span class="p">))</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">username</span><span class="o">=</span><span class="s1">'wendy'</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name = ?
('wendy',)
</div><span class="go">[(2, u'wendy', u'Wendy Williams')]</span></pre></div>
</div>
<p>Another important aspect of <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> is that it may be assigned a
type. The type of the bind parameter will determine its behavior within
expressions and also how the data bound to it is processed before being sent
off to the database:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">select</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">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="n">bindparam</span><span class="p">(</span><span class="s1">'username'</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span> <span class="o">+</span> <span class="n">text</span><span class="p">(</span><span class="s2">"'%'"</span><span class="p">)))</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">username</span><span class="o">=</span><span class="s1">'wendy'</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
FROM users
WHERE users.name LIKE ? || '%'
('wendy',)
</div><span class="go">[(2, u'wendy', u'Wendy Williams')]</span></pre></div>
</div>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> constructs of the same name can also be used multiple times, where only a
single named value is needed in the execute parameters:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">,</span> <span class="n">addresses</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">or_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">bindparam</span><span class="p">(</span><span class="s1">'name'</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span> <span class="o">+</span> <span class="n">text</span><span class="p">(</span><span class="s2">"'%'"</span><span class="p">)),</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">bindparam</span><span class="p">(</span><span class="s1">'name'</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span> <span class="o">+</span> <span class="n">text</span><span class="p">(</span><span class="s2">"'@%'"</span><span class="p">))</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">order_by</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s1">'jack'</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id,
addresses.user_id, addresses.email_address
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'
ORDER BY addresses.id
('jack', 'jack')
</div><span class="go">[(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')]</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a></p>
</div>
</div>
<div class="section" id="functions">
<h3>Functions<a class="headerlink" href="#functions" title="Permalink to this headline">¶</a></h3>
<p>SQL functions are created using the <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> keyword, which
generates functions using attribute access:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">func</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">now</span><span class="p">())</span>
<span class="go">now()</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">concat</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="go">concat(:concat_1, :concat_2)</span></pre></div>
</div>
<p>By “generates”, we mean that <strong>any</strong> SQL function is created based on the word
you choose:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">xyz_my_goofy_function</span><span class="p">())</span>
<span class="go">xyz_my_goofy_function()</span></pre></div>
</div>
<p>Certain function names are known by SQLAlchemy, allowing special behavioral
rules to be applied. Some for example are “ANSI” functions, which mean they
don’t get the parenthesis added after them, such as CURRENT_TIMESTAMP:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">current_timestamp</span><span class="p">())</span>
<span class="go">CURRENT_TIMESTAMP</span></pre></div>
</div>
<p>Functions are most typically used in the columns clause of a select statement,
and can also be labeled as well as given a type. Labeling a function is
recommended so that the result can be targeted in a result row based on a
string name, and assigning it a type is required when you need result-set
processing to occur, such as for Unicode conversion and date conversions.
Below, we use the result function <code class="docutils literal notranslate"><span class="pre">scalar()</span></code> to just read the first column
of the first row and then close the result; the label, even though present, is
not important in this case:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">select</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">func</span><span class="o">.</span><span class="n">max</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">label</span><span class="p">(</span><span class="s1">'maxemail'</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">])</span>
<span class="gp">... </span> <span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span>
<div class='show_sql'>SELECT max(addresses.email_address) AS maxemail
FROM addresses
()
</div><span class="go">u'www@www.org'</span></pre></div>
</div>
<p>Databases such as PostgreSQL and Oracle which support functions that return
whole result sets can be assembled into selectable units, which can be used in
statements. Such as, a database function <code class="docutils literal notranslate"><span class="pre">calculate()</span></code> which takes the
parameters <code class="docutils literal notranslate"><span class="pre">x</span></code> and <code class="docutils literal notranslate"><span class="pre">y</span></code>, and returns three columns which we’d like to name
<code class="docutils literal notranslate"><span class="pre">q</span></code>, <code class="docutils literal notranslate"><span class="pre">z</span></code> and <code class="docutils literal notranslate"><span class="pre">r</span></code>, we can construct using “lexical” column objects as
well as bind parameters:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">column</span>
<span class="gp">>>> </span><span class="n">calculate</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">column</span><span class="p">(</span><span class="s1">'q'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'z'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'r'</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">select_from</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">func</span><span class="o">.</span><span class="n">calculate</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">bindparam</span><span class="p">(</span><span class="s1">'x'</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">bindparam</span><span class="p">(</span><span class="s1">'y'</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">>>> </span><span class="n">calc</span> <span class="o">=</span> <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">])</span><span class="o">.</span><span class="n">where</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">id</span> <span class="o">></span> <span class="n">calc</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">))</span>
<span class="go">SELECT users.id, users.name, users.fullname</span>
<span class="go">FROM users, (SELECT q, z, r</span>
<span class="go">FROM calculate(:x, :y)) AS anon_1</span>
<span class="go">WHERE users.id > anon_1.z</span></pre></div>
</div>
<p>If we wanted to use our <code class="docutils literal notranslate"><span class="pre">calculate</span></code> statement twice with different bind
parameters, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ClauseElement.unique_params" title="sqlalchemy.sql.expression.ClauseElement.unique_params"><code class="xref py py-func docutils literal notranslate"><span class="pre">unique_params()</span></code></a>
function will create copies for us, and mark the bind parameters as “unique”
so that conflicting names are isolated. Note we also make two separate aliases
of our selectable:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">calc1</span> <span class="o">=</span> <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s1">'c1'</span><span class="p">)</span><span class="o">.</span><span class="n">unique_params</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="mi">17</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="mi">45</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">calc2</span> <span class="o">=</span> <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s1">'c2'</span><span class="p">)</span><span class="o">.</span><span class="n">unique_params</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="mi">5</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="mi">12</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span><span class="o">.</span><span class="n">between</span><span class="p">(</span><span class="n">calc1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">,</span> <span class="n">calc2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">))</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">s</span><span class="p">)</span>
<span class="go">SELECT users.id, users.name, users.fullname</span>
<span class="go">FROM users,</span>
<span class="go"> (SELECT q, z, r FROM calculate(:x_1, :y_1)) AS c1,</span>
<span class="go"> (SELECT q, z, r FROM calculate(:x_2, :y_2)) AS c2</span>
<span class="go">WHERE users.id BETWEEN c1.z AND c2.z</span>
<span class="gp">>>> </span><span class="n">s</span><span class="o">.</span><span class="n">compile</span><span class="p">()</span><span class="o">.</span><span class="n">params</span>
<span class="go">{u'x_2': 5, u'y_2': 12, u'y_1': 45, u'x_1': 17}</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><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></p>
</div>
</div>
<div class="section" id="window-functions">
<span id="id1"></span><h3>Window Functions<a class="headerlink" href="#window-functions" title="Permalink to this headline">¶</a></h3>
<p>Any <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>, including functions 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>, can be turned into a “window function”, that is an
OVER clause, using the <a class="reference internal" href="functions.html#sqlalchemy.sql.functions.FunctionElement.over" title="sqlalchemy.sql.functions.FunctionElement.over"><code class="xref py py-meth docutils literal notranslate"><span class="pre">FunctionElement.over()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">func</span><span class="o">.</span><span class="n">row_number</span><span class="p">()</span><span class="o">.</span><span class="n">over</span><span class="p">(</span><span class="n">order_by</span><span class="o">=</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="gp">... </span> <span class="p">])</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">s</span><span class="p">)</span>
<span class="go">SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1</span>
<span class="go">FROM users</span></pre></div>
</div>
<p><a class="reference internal" href="functions.html#sqlalchemy.sql.functions.FunctionElement.over" title="sqlalchemy.sql.functions.FunctionElement.over"><code class="xref py py-meth docutils literal notranslate"><span class="pre">FunctionElement.over()</span></code></a> also supports range specification using
either the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.over.params.rows" title="sqlalchemy.sql.expression.over"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">expression.over.rows</span></code></a> or
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.over.params.range" title="sqlalchemy.sql.expression.over"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">expression.over.range</span></code></a> parameters:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">func</span><span class="o">.</span><span class="n">row_number</span><span class="p">()</span><span class="o">.</span><span class="n">over</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">order_by</span><span class="o">=</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">rows</span><span class="o">=</span><span class="p">(</span><span class="o">-</span><span class="mi">2</span><span class="p">,</span> <span class="kc">None</span><span class="p">))</span>
<span class="gp">... </span> <span class="p">])</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">s</span><span class="p">)</span>
<span class="go">SELECT users.id, row_number() OVER</span>
<span class="go">(ORDER BY users.name ROWS BETWEEN :param_1 PRECEDING AND UNBOUNDED FOLLOWING) AS anon_1</span>
<span class="go">FROM users</span></pre></div>
</div>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.over.params.rows" title="sqlalchemy.sql.expression.over"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">expression.over.rows</span></code></a> and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.over.params.range" title="sqlalchemy.sql.expression.over"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">expression.over.range</span></code></a> each
accept a two-tuple which contains a combination of negative and positive
integers for ranges, zero to indicate “CURRENT ROW” and <code class="docutils literal notranslate"><span class="pre">None</span></code> to
indicate “UNBOUNDED”. See the examples at <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.over" title="sqlalchemy.sql.expression.over"><code class="xref py py-func docutils literal notranslate"><span class="pre">over()</span></code></a> for more detail.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1: </span>support for “rows” and “range” specification for
window functions</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.over" title="sqlalchemy.sql.expression.over"><code class="xref py py-func docutils literal notranslate"><span class="pre">over()</span></code></a></p>
<p><a class="reference internal" href="functions.html#sqlalchemy.sql.functions.FunctionElement.over" title="sqlalchemy.sql.functions.FunctionElement.over"><code class="xref py py-meth docutils literal notranslate"><span class="pre">FunctionElement.over()</span></code></a></p>
</div>
</div>
<div class="section" id="unions-and-other-set-operations">
<h3>Unions and Other Set Operations<a class="headerlink" href="#unions-and-other-set-operations" title="Permalink to this headline">¶</a></h3>
<p>Unions come in two flavors, UNION and UNION ALL, which are available via
module level functions <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union" title="sqlalchemy.sql.expression.union"><code class="xref py py-func docutils literal notranslate"><span class="pre">union()</span></code></a> and
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union_all" title="sqlalchemy.sql.expression.union_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">union_all()</span></code></a>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">union</span>
<span class="gp">>>> </span><span class="n">u</span> <span class="o">=</span> <span class="n">union</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'foo@bar.com'</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@yahoo.com'</span><span class="p">)),</span>
<span class="gp">... </span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address = ?
UNION
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ? ORDER BY addresses.email_address
('foo@bar.com', '%@yahoo.com')
</div><span class="go">[(1, 1, u'jack@yahoo.com')]</span></pre></div>
</div>
<p>Also available, though not supported on all databases, are
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect" title="sqlalchemy.sql.expression.intersect"><code class="xref py py-func docutils literal notranslate"><span class="pre">intersect()</span></code></a>,
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect_all" title="sqlalchemy.sql.expression.intersect_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">intersect_all()</span></code></a>,
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_" title="sqlalchemy.sql.expression.except_"><code class="xref py py-func docutils literal notranslate"><span class="pre">except_()</span></code></a>, and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_all" title="sqlalchemy.sql.expression.except_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">except_all()</span></code></a>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">except_</span>
<span class="gp">>>> </span><span class="n">u</span> <span class="o">=</span> <span class="n">except_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@%.com'</span><span class="p">)),</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@msn.com'</span><span class="p">))</span>
<span class="gp">... </span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
EXCEPT
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
('%@%.com', '%@msn.com')
</div><span class="go">[(1, 1, u'jack@yahoo.com'), (4, 2, u'wendy@aol.com')]</span></pre></div>
</div>
<p>A common issue with so-called “compound” selectables arises due to the fact
that they nest with parenthesis. SQLite in particular doesn’t like a statement
that starts with parenthesis. So when nesting a “compound” inside a
“compound”, it’s often necessary to apply <code class="docutils literal notranslate"><span class="pre">.alias().select()</span></code> to the first
element of the outermost compound, if that element is also a compound. For
example, to nest a “union” and a “select” inside of “except_”, SQLite will
want the “union” to be stated as a subquery:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">u</span> <span class="o">=</span> <span class="n">except_</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">union</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@yahoo.com'</span><span class="p">)),</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@msn.com'</span><span class="p">))</span>
<span class="gp">... </span> <span class="p">)</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span><span class="o">.</span><span class="n">select</span><span class="p">(),</span> <span class="c1"># apply subquery here</span>
<span class="gp">... </span> <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'%@msn.com'</span><span class="p">))</span>
<span class="gp">... </span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='popup_sql'>SELECT anon_1.id, anon_1.user_id, anon_1.email_address
FROM (SELECT addresses.id AS id, addresses.user_id AS user_id,
addresses.email_address AS email_address
FROM addresses
WHERE addresses.email_address LIKE ?
UNION
SELECT addresses.id AS id,
addresses.user_id AS user_id,
addresses.email_address AS email_address
FROM addresses
WHERE addresses.email_address LIKE ?) AS anon_1
EXCEPT
SELECT addresses.id, addresses.user_id, addresses.email_address
FROM addresses
WHERE addresses.email_address LIKE ?
('%@yahoo.com', '%@msn.com', '%@msn.com')
</div><span class="go">[(1, 1, u'jack@yahoo.com')]</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union" title="sqlalchemy.sql.expression.union"><code class="xref py py-func docutils literal notranslate"><span class="pre">union()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union_all" title="sqlalchemy.sql.expression.union_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">union_all()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect" title="sqlalchemy.sql.expression.intersect"><code class="xref py py-func docutils literal notranslate"><span class="pre">intersect()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect_all" title="sqlalchemy.sql.expression.intersect_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">intersect_all()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_" title="sqlalchemy.sql.expression.except_"><code class="xref py py-func docutils literal notranslate"><span class="pre">except_()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_all" title="sqlalchemy.sql.expression.except_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">except_all()</span></code></a></p>
</div>
</div>
<div class="section" id="scalar-selects">
<span id="id2"></span><h3>Scalar Selects<a class="headerlink" href="#scalar-selects" title="Permalink to this headline">¶</a></h3>
<p>A scalar select is a SELECT that returns exactly one row and one
column. It can then be used as a column expression. A scalar select
is often a <a class="reference internal" href="../glossary.html#term-correlated-subquery"><span class="xref std std-term">correlated subquery</span></a>, which relies upon the enclosing
SELECT statement in order to acquire at least one of its FROM clauses.</p>
<p>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 can be modified to act as a
column expression by calling either the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.as_scalar" title="sqlalchemy.sql.expression.SelectBase.as_scalar"><code class="xref py py-meth docutils literal notranslate"><span class="pre">as_scalar()</span></code></a>
or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><code class="xref py py-meth docutils literal notranslate"><span class="pre">label()</span></code></a> method:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">as_scalar</span><span class="p">()</span></pre></div>
</div>
<p>The above construct is now a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.ScalarSelect" title="sqlalchemy.sql.expression.ScalarSelect"><code class="xref py py-class docutils literal notranslate"><span class="pre">ScalarSelect</span></code></a> object,
and is no longer part of the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><code class="xref py py-class docutils literal notranslate"><span class="pre">FromClause</span></code></a> hierarchy;
it instead is within the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnElement</span></code></a> family of
expression constructs. We can place this construct the same as any
other column within another <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>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</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">name</span><span class="p">,</span> <span class="n">stmt</span><span class="p">]))</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS anon_1
FROM users
()
</div><span class="go">[(u'jack', 2), (u'wendy', 2)]</span></pre></div>
</div>
<p>To apply a non-anonymous column name to our scalar select, we create
it using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><code class="xref py py-meth docutils literal notranslate"><span class="pre">SelectBase.label()</span></code></a> instead:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">label</span><span class="p">(</span><span class="s2">"address_count"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</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">name</span><span class="p">,</span> <span class="n">stmt</span><span class="p">]))</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name, (SELECT count(addresses.id) AS count_1
FROM addresses
WHERE users.id = addresses.user_id) AS address_count
FROM users
()
</div><span class="go">[(u'jack', 2), (u'wendy', 2)]</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.as_scalar" title="sqlalchemy.sql.expression.Select.as_scalar"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.as_scalar()</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.label" title="sqlalchemy.sql.expression.Select.label"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.label()</span></code></a></p>
</div>
</div>
<div class="section" id="correlated-subqueries">
<span id="id3"></span><h3>Correlated Subqueries<a class="headerlink" href="#correlated-subqueries" title="Permalink to this headline">¶</a></h3>
<p>Notice in the examples on <a class="reference internal" href="#scalar-selects"><span class="std std-ref">Scalar Selects</span></a>, the FROM clause of each embedded
select did not contain the <code class="docutils literal notranslate"><span class="pre">users</span></code> table in its FROM clause. This is because
SQLAlchemy automatically <a class="reference internal" href="../glossary.html#term-correlates"><span class="xref std std-term">correlates</span></a> embedded FROM objects to that
of an enclosing query, if present, and if the inner SELECT statement would
still have at least one FROM clause of its own. For example:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</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="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s1">'jack@yahoo.com'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">])</span><span class="o">.</span><span class="n">where</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">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name
FROM users
WHERE users.id = (SELECT addresses.user_id
FROM addresses
WHERE addresses.user_id = users.id
AND addresses.email_address = ?)
('jack@yahoo.com',)
</div><span class="go">[(u'jack',)]</span></pre></div>
</div>
<p>Auto-correlation will usually do what’s expected, however it can also be controlled.
For example, if we wanted a statement to correlate only to the <code class="docutils literal notranslate"><span class="pre">addresses</span></code> table
but not the <code class="docutils literal notranslate"><span class="pre">users</span></code> table, even if both were present in the enclosing SELECT,
we use the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate" title="sqlalchemy.sql.expression.Select.correlate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">correlate()</span></code></a> method to specify those FROM clauses that
may be correlated:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">id</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">name</span> <span class="o">==</span> <span class="s1">'jack'</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">correlate</span><span class="p">(</span><span class="n">addresses</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span>
<span class="gp">... </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">name</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name, addresses.email_address
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE users.id = (SELECT users.id
FROM users
WHERE users.id = addresses.user_id AND users.name = ?)
('jack',)
</div><span class="go">[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]</span></pre></div>
</div>
<p>To entirely disable a statement from correlating, we can pass <code class="docutils literal notranslate"><span class="pre">None</span></code>
as the argument:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">id</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">name</span> <span class="o">==</span> <span class="s1">'wendy'</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">correlate</span><span class="p">(</span><span class="bp">None</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name
FROM users
WHERE users.id = (SELECT users.id
FROM users
WHERE users.name = ?)
('wendy',)
</div><span class="go">[(u'wendy',)]</span></pre></div>
</div>
<p>We can also control correlation via exclusion, using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate_except" title="sqlalchemy.sql.expression.Select.correlate_except"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.correlate_except()</span></code></a>
method. Such as, we can write our SELECT for the <code class="docutils literal notranslate"><span class="pre">users</span></code> table
by telling it to correlate all FROM clauses except for <code class="docutils literal notranslate"><span class="pre">users</span></code>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">id</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">name</span> <span class="o">==</span> <span class="s1">'jack'</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">correlate_except</span><span class="p">(</span><span class="n">users</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span>
<span class="gp">... </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">name</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name, addresses.email_address
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE users.id = (SELECT users.id
FROM users
WHERE users.id = addresses.user_id AND users.name = ?)
('jack',)
</div><span class="go">[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]</span></pre></div>
</div>
<div class="section" id="lateral-correlation">
<span id="lateral-selects"></span><h4>LATERAL correlation<a class="headerlink" href="#lateral-correlation" title="Permalink to this headline">¶</a></h4>
<p>LATERAL correlation is a special sub-category of SQL correlation which
allows a selectable unit to refer to another selectable unit within a
single FROM clause. This is an extremely special use case which, while
part of the SQL standard, is only known to be supported by recent
versions of PostgreSQL.</p>
<p>Normally, if a SELECT statement refers to
<code class="docutils literal notranslate"><span class="pre">table1</span> <span class="pre">JOIN</span> <span class="pre">(some</span> <span class="pre">SELECT)</span> <span class="pre">AS</span> <span class="pre">subquery</span></code> in its FROM clause, the subquery
on the right side may not refer to the “table1” expression from the left side;
correlation may only refer to a table that is part of another SELECT that
entirely encloses this SELECT. The LATERAL keyword allows us to turn this
behavior around, allowing an expression such as:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">people</span><span class="p">.</span><span class="n">people_id</span><span class="p">,</span> <span class="n">people</span><span class="p">.</span><span class="n">age</span><span class="p">,</span> <span class="n">people</span><span class="p">.</span><span class="n">name</span>
<span class="k">FROM</span> <span class="n">people</span> <span class="k">JOIN</span> <span class="k">LATERAL</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">books</span><span class="p">.</span><span class="n">book_id</span> <span class="k">AS</span> <span class="n">book_id</span>
<span class="k">FROM</span> <span class="n">books</span> <span class="k">WHERE</span> <span class="n">books</span><span class="p">.</span><span class="n">owner_id</span> <span class="o">=</span> <span class="n">people</span><span class="p">.</span><span class="n">people_id</span><span class="p">)</span>
<span class="k">AS</span> <span class="n">book_subq</span> <span class="k">ON</span> <span class="k">true</span></pre></div>
</div>
<p>Where above, the right side of the JOIN contains a subquery that refers not
just to the “books” table but also the “people” table, correlating
to the left side of the JOIN. SQLAlchemy Core supports a statement
like the above using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.lateral" title="sqlalchemy.sql.expression.Select.lateral"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.lateral()</span></code></a> method as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">table</span><span class="p">,</span> <span class="n">column</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">true</span>
<span class="gp">>>> </span><span class="n">people</span> <span class="o">=</span> <span class="n">table</span><span class="p">(</span><span class="s1">'people'</span><span class="p">,</span> <span class="n">column</span><span class="p">(</span><span class="s1">'people_id'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'age'</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="gp">>>> </span><span class="n">books</span> <span class="o">=</span> <span class="n">table</span><span class="p">(</span><span class="s1">'books'</span><span class="p">,</span> <span class="n">column</span><span class="p">(</span><span class="s1">'book_id'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'owner_id'</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">subq</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">books</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">book_id</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">books</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">owner_id</span> <span class="o">==</span> <span class="n">people</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">people_id</span><span class="p">)</span><span class="o">.</span><span class="n">lateral</span><span class="p">(</span><span class="s2">"book_subq"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">people</span><span class="p">])</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">people</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">subq</span><span class="p">,</span> <span class="n">true</span><span class="p">())))</span>
<span class="go">SELECT people.people_id, people.age, people.name</span>
<span class="go">FROM people JOIN LATERAL (SELECT books.book_id AS book_id</span>
<span class="go">FROM books WHERE books.owner_id = people.people_id)</span>
<span class="go">AS book_subq ON true</span></pre></div>
</div>
<p>Above, we can see that the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.lateral" title="sqlalchemy.sql.expression.Select.lateral"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.lateral()</span></code></a> method acts a lot like
the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.alias" title="sqlalchemy.sql.expression.Select.alias"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.alias()</span></code></a> method, including that we can specify an optional
name. However the construct is the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Lateral" title="sqlalchemy.sql.expression.Lateral"><code class="xref py py-class docutils literal notranslate"><span class="pre">Lateral</span></code></a> construct instead of
an <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><code class="xref py py-class docutils literal notranslate"><span class="pre">Alias</span></code></a> which provides for the LATERAL keyword as well as special
instructions to allow correlation from inside the FROM clause of the
enclosing statement.</p>
<p>The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.lateral" title="sqlalchemy.sql.expression.Select.lateral"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.lateral()</span></code></a> method interacts normally with the
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate" title="sqlalchemy.sql.expression.Select.correlate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.correlate()</span></code></a> and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate_except" title="sqlalchemy.sql.expression.Select.correlate_except"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.correlate_except()</span></code></a> methods, except
that the correlation rules also apply to any other tables present in the
enclosing statement’s FROM clause. Correlation is “automatic” to these
tables by default, is explicit if the table is specified to
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate" title="sqlalchemy.sql.expression.Select.correlate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.correlate()</span></code></a>, and is explicit to all tables except those
specified to <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.correlate_except" title="sqlalchemy.sql.expression.Select.correlate_except"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.correlate_except()</span></code></a>.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1: </span>Support for the LATERAL keyword and lateral correlation.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Lateral" title="sqlalchemy.sql.expression.Lateral"><code class="xref py py-class docutils literal notranslate"><span class="pre">Lateral</span></code></a></p>
<p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.lateral" title="sqlalchemy.sql.expression.Select.lateral"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.lateral()</span></code></a></p>
</div>
</div>
</div>
<div class="section" id="ordering-grouping-limiting-offset-ing">
<h3>Ordering, Grouping, Limiting, Offset…ing…<a class="headerlink" href="#ordering-grouping-limiting-offset-ing" title="Permalink to this headline">¶</a></h3>
<p>Ordering is done by passing column expressions to the
<code class="xref py py-meth docutils literal notranslate"><span class="pre">order_by()</span></code> method:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">])</span><span class="o">.</span><span class="n">order_by</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">name</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name
FROM users ORDER BY users.name
()
</div><span class="go">[(u'jack',), (u'wendy',)]</span></pre></div>
</div>
<p>Ascending or descending can be controlled using the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.asc" title="sqlalchemy.sql.expression.ColumnElement.asc"><code class="xref py py-meth docutils literal notranslate"><span class="pre">asc()</span></code></a>
and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.desc" title="sqlalchemy.sql.expression.ColumnElement.desc"><code class="xref py py-meth docutils literal notranslate"><span class="pre">desc()</span></code></a> modifiers:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">])</span><span class="o">.</span><span class="n">order_by</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">name</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name
FROM users ORDER BY users.name DESC
()
</div><span class="go">[(u'wendy',), (u'jack',)]</span></pre></div>
</div>
<p>Grouping refers to the GROUP BY clause, and is usually used in conjunction
with aggregate functions to establish groups of rows to be aggregated.
This is provided via the <code class="xref py py-meth docutils literal notranslate"><span class="pre">group_by()</span></code> method:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">group_by</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">name</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name, count(addresses.id) AS count_1
FROM users JOIN addresses
ON users.id = addresses.user_id
GROUP BY users.name
()
</div><span class="go">[(u'jack', 2), (u'wendy', 2)]</span></pre></div>
</div>
<p>HAVING can be used to filter results on an aggregate value, after GROUP BY has
been applied. It’s available here via the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.having" title="sqlalchemy.sql.expression.Select.having"><code class="xref py py-meth docutils literal notranslate"><span class="pre">having()</span></code></a>
method:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">group_by</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">name</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">having</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">length</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">name</span><span class="p">)</span> <span class="o">></span> <span class="mi">4</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name, count(addresses.id) AS count_1
FROM users JOIN addresses
ON users.id = addresses.user_id
GROUP BY users.name
HAVING length(users.name) > ?
(4,)
</div><span class="go">[(u'wendy', 2)]</span></pre></div>
</div>
<p>A common system of dealing with duplicates in composed SELECT statements
is the DISTINCT modifier. A simple DISTINCT clause can be added using the
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.distinct" title="sqlalchemy.sql.expression.Select.distinct"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Select.distinct()</span></code></a> method:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span>
<span class="gp">... </span> <span class="n">contains</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">name</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">distinct</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT DISTINCT users.name
FROM users, addresses
WHERE (addresses.email_address LIKE '%' || users.name || '%')
()
</div><span class="go">[(u'jack',), (u'wendy',)]</span></pre></div>
</div>
<p>Most database backends support a system of limiting how many rows
are returned, and the majority also feature a means of starting to return
rows after a given “offset”. While common backends like PostgreSQL,
MySQL and SQLite support LIMIT and OFFSET keywords, other backends
need to refer to more esoteric features such as “window functions”
and row ids to achieve the same effect. The <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.limit" title="sqlalchemy.sql.expression.Select.limit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">limit()</span></code></a>
and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.offset" title="sqlalchemy.sql.expression.Select.offset"><code class="xref py py-meth docutils literal notranslate"><span class="pre">offset()</span></code></a> methods provide an easy abstraction
into the current backend’s methodology:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</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">name</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">limit</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span><span class="o">.</span><span class="n">offset</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<div class='show_sql'>SELECT users.name, addresses.email_address
FROM users JOIN addresses ON users.id = addresses.user_id
LIMIT ? OFFSET ?
(1, 1)
</div><span class="go">[(u'jack', u'jack@msn.com')]</span></pre></div>
</div>
</div>
</div>
<div class="section" id="inserts-updates-and-deletes">
<span id="inserts-and-updates"></span><h2>Inserts, Updates and Deletes<a class="headerlink" href="#inserts-updates-and-deletes" title="Permalink to this headline">¶</a></h2>
<p>We’ve seen <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><code class="xref py py-meth docutils literal notranslate"><span class="pre">insert()</span></code></a> demonstrated
earlier in this tutorial. Where <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><code class="xref py py-meth docutils literal notranslate"><span class="pre">insert()</span></code></a>
produces INSERT, the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a>
method produces UPDATE. Both of these constructs feature
a method called <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">values()</span></code></a> which specifies
the VALUES or SET clause of the statement.</p>
<p>The <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">values()</span></code></a> method accommodates any column expression
as a value:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">values</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="s2">"Fullname: "</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span>
<div class='show_sql'>UPDATE users SET fullname=(? || users.name)
('Fullname: ',)
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span></pre></div>
</div>
<p>When using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><code class="xref py py-meth docutils literal notranslate"><span class="pre">insert()</span></code></a> or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a>
in an “execute many” context, we may also want to specify named
bound parameters which we can refer to in the argument list.
The two constructs will automatically generate bound placeholders
for any column names passed in the dictionaries sent to
<a class="reference internal" href="connections.html#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> at execution time. However, if we
wish to use explicitly targeted named parameters with composed expressions,
we need to use the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> construct.
When using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> with
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><code class="xref py py-meth docutils literal notranslate"><span class="pre">insert()</span></code></a> or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a>,
the names of the table’s columns themselves are reserved for the
“automatic” generation of bind names. We can combine the usage
of implicitly available bind names and explicitly named parameters
as in the example below:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s1">'_name'</span><span class="p">)</span> <span class="o">+</span> <span class="s2">" .. name"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">,</span> <span class="p">[</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'id'</span><span class="p">:</span><span class="mi">4</span><span class="p">,</span> <span class="s1">'_name'</span><span class="p">:</span><span class="s1">'name1'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'id'</span><span class="p">:</span><span class="mi">5</span><span class="p">,</span> <span class="s1">'_name'</span><span class="p">:</span><span class="s1">'name2'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'id'</span><span class="p">:</span><span class="mi">6</span><span class="p">,</span> <span class="s1">'_name'</span><span class="p">:</span><span class="s1">'name3'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">])</span>
<div class='show_sql'>INSERT INTO users (id, name) VALUES (?, (? || ?))
((4, 'name1', ' .. name'), (5, 'name2', ' .. name'), (6, 'name3', ' .. name'))
COMMIT
<sqlalchemy.engine.result.ResultProxy object at 0x...></div></pre></div>
</div>
<p>An UPDATE statement is emitted using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a> construct. This
works much like an INSERT, except there is an additional WHERE clause
that can be specified:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">name</span> <span class="o">==</span> <span class="s1">'jack'</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">values</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="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span>
<div class='show_sql'>UPDATE users SET name=? WHERE users.name = ?
('ed', 'jack')
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span></pre></div>
</div>
<p>When using <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a> in an “executemany” context,
we may wish to also use explicitly named bound parameters in the
WHERE clause. Again, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> is the construct
used to achieve this:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</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">name</span> <span class="o">==</span> <span class="n">bindparam</span><span class="p">(</span><span class="s1">'oldname'</span><span class="p">))</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s1">'newname'</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">,</span> <span class="p">[</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'oldname'</span><span class="p">:</span><span class="s1">'jack'</span><span class="p">,</span> <span class="s1">'newname'</span><span class="p">:</span><span class="s1">'ed'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'oldname'</span><span class="p">:</span><span class="s1">'wendy'</span><span class="p">,</span> <span class="s1">'newname'</span><span class="p">:</span><span class="s1">'mary'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">{</span><span class="s1">'oldname'</span><span class="p">:</span><span class="s1">'jim'</span><span class="p">,</span> <span class="s1">'newname'</span><span class="p">:</span><span class="s1">'jake'</span><span class="p">},</span>
<span class="gp">... </span> <span class="p">])</span>
<div class='show_sql'>UPDATE users SET name=? WHERE users.name = ?
(('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim'))
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span></pre></div>
</div>
<div class="section" id="correlated-updates">
<h3>Correlated Updates<a class="headerlink" href="#correlated-updates" title="Permalink to this headline">¶</a></h3>
<p>A correlated update lets you update a table using selection from another
table, or the same table:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">where</span><span class="p">(</span><span class="n">addresses</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="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="n">limit</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</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="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="n">stmt</span><span class="p">))</span>
<div class='show_sql'>UPDATE users SET fullname=(SELECT addresses.email_address
FROM addresses
WHERE addresses.user_id = users.id
LIMIT ? OFFSET ?)
(1, 0)
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span></pre></div>
</div>
</div>
<div class="section" id="multiple-table-updates">
<span id="multi-table-updates"></span><h3>Multiple Table Updates<a class="headerlink" href="#multiple-table-updates" title="Permalink to this headline">¶</a></h3>
<p>The PostgreSQL, Microsoft SQL Server, and MySQL backends all support UPDATE statements
that refer to multiple tables. For PG and MSSQL, this is the “UPDATE FROM” syntax,
which updates one table at a time, but can reference additional tables in an additional
“FROM” clause that can then be referenced in the WHERE clause directly. On MySQL,
multiple tables can be embedded into a single UPDATE statement separated by a comma.
The SQLAlchemy <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.update" title="sqlalchemy.sql.expression.update"><code class="xref py py-func docutils literal notranslate"><span class="pre">update()</span></code></a> construct supports both of these modes
implicitly, by specifying multiple tables in the WHERE clause:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
<span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'ed wood'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s1">'ed%'</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">stmt</span><span class="p">)</span></pre></div>
</div>
<p>The resulting SQL from the above statement would render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">UPDATE</span> <span class="n">users</span> <span class="n">SET</span> <span class="n">name</span><span class="o">=</span><span class="p">:</span><span class="n">name</span> <span class="n">FROM</span> <span class="n">addresses</span>
<span class="n">WHERE</span> <span class="n">users</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">id</span> <span class="n">AND</span>
<span class="n">addresses</span><span class="o">.</span><span class="n">email_address</span> <span class="n">LIKE</span> <span class="p">:</span><span class="n">email_address_1</span> <span class="o">||</span> <span class="s1">'%'</span></pre></div>
</div>
<p>When using MySQL, columns from each table can be assigned to in the
SET clause directly, using the dictionary form passed to <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Update.values()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
<span class="n">values</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">name</span><span class="p">:</span><span class="s1">'ed wood'</span><span class="p">,</span>
<span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">:</span><span class="s1">'ed.wood@foo.com'</span>
<span class="p">})</span><span class="o">.</span>\
<span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s1">'ed%'</span><span class="p">))</span></pre></div>
</div>
<p>The tables are referenced explicitly in the SET clause:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">UPDATE</span> <span class="n">users</span><span class="p">,</span> <span class="n">addresses</span> <span class="n">SET</span> <span class="n">addresses</span><span class="o">.</span><span class="n">email_address</span><span class="o">=%</span><span class="n">s</span><span class="p">,</span>
<span class="n">users</span><span class="o">.</span><span class="n">name</span><span class="o">=%</span><span class="n">s</span> <span class="n">WHERE</span> <span class="n">users</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">id</span>
<span class="n">AND</span> <span class="n">addresses</span><span class="o">.</span><span class="n">email_address</span> <span class="n">LIKE</span> <span class="n">concat</span><span class="p">(</span><span class="o">%</span><span class="n">s</span><span class="p">,</span> <span class="s1">'%'</span><span class="p">)</span></pre></div>
</div>
<p>When the construct is used on a non-supporting database, the compiler
will raise <code class="docutils literal notranslate"><span class="pre">NotImplementedError</span></code>. For convenience, when a statement
is printed as a string without specification of a dialect, the “string SQL”
compiler will be invoked which provides a non-working SQL representation of the
construct.</p>
</div>
<div class="section" id="parameter-ordered-updates">
<span id="updates-order-parameters"></span><h3>Parameter-Ordered Updates<a class="headerlink" href="#parameter-ordered-updates" title="Permalink to this headline">¶</a></h3>
<p>The default behavior of the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.update" title="sqlalchemy.sql.expression.update"><code class="xref py py-func docutils literal notranslate"><span class="pre">update()</span></code></a> construct when rendering the SET
clauses is to render them using the column ordering given in the
originating <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.
This is an important behavior, since it means that the rendering of a
particular UPDATE statement with particular columns
will be rendered the same each time, which has an impact on query caching systems
that rely on the form of the statement, either client side or server side.
Since the parameters themselves are passed to the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Update.values()</span></code></a>
method as Python dictionary keys, there is no other fixed ordering
available.</p>
<p>However in some cases, the order of parameters rendered in the SET clause of an
UPDATE statement can be significant. The main example of this is when using
MySQL and providing updates to column values based on that of other
column values. The end result of the following statement:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">UPDATE</span> <span class="n">some_table</span> <span class="n">SET</span> <span class="n">x</span> <span class="o">=</span> <span class="n">y</span> <span class="o">+</span> <span class="mi">10</span><span class="p">,</span> <span class="n">y</span> <span class="o">=</span> <span class="mi">20</span></pre></div>
</div>
<p>Will have a different result than:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">UPDATE</span> <span class="n">some_table</span> <span class="n">SET</span> <span class="n">y</span> <span class="o">=</span> <span class="mi">20</span><span class="p">,</span> <span class="n">x</span> <span class="o">=</span> <span class="n">y</span> <span class="o">+</span> <span class="mi">10</span></pre></div>
</div>
<p>This because on MySQL, the individual SET clauses are fully evaluated on
a per-value basis, as opposed to on a per-row basis, and as each SET clause
is evaluated, the values embedded in the row are changing.</p>
<p>To suit this specific use case, the
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.update.params.preserve_parameter_order" title="sqlalchemy.sql.expression.update"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">preserve_parameter_order</span></code></a>
flag may be used. When using this flag, we supply a <strong>Python list of 2-tuples</strong>
as the argument to the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Update.values()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">some_table</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">preserve_parameter_order</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span><span class="o">.</span>\
<span class="n">values</span><span class="p">([(</span><span class="n">some_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span><span class="p">,</span> <span class="mi">20</span><span class="p">),</span> <span class="p">(</span><span class="n">some_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">some_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span> <span class="o">+</span> <span class="mi">10</span><span class="p">)])</span></pre></div>
</div>
<p>The list of 2-tuples is essentially the same structure as a Python dictionary
except it is ordered. Using the above form, we are assured that the
“y” column’s SET clause will render first, then the “x” column’s SET clause.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.10: </span>Added support for explicit ordering of UPDATE
parameters using the <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.update.params.preserve_parameter_order" title="sqlalchemy.sql.expression.update"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">preserve_parameter_order</span></code></a> flag.</p>
</div>
</div>
<div class="section" id="deletes">
<span id="id4"></span><h3>Deletes<a class="headerlink" href="#deletes" title="Permalink to this headline">¶</a></h3>
<p>Finally, a delete. This is accomplished easily enough using the
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><code class="xref py py-meth docutils literal notranslate"><span class="pre">delete()</span></code></a> construct:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">delete</span><span class="p">())</span>
<div class='show_sql'>DELETE FROM addresses
()
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span>
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span><span class="n">where</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">name</span> <span class="o">></span> <span class="s1">'m'</span><span class="p">))</span>
<div class='show_sql'>DELETE FROM users WHERE users.name > ?
('m',)
COMMIT
</div><span class="go"><sqlalchemy.engine.result.ResultProxy object at 0x...></span></pre></div>
</div>
</div>
<div class="section" id="multiple-table-deletes">
<span id="multi-table-deletes"></span><h3>Multiple Table Deletes<a class="headerlink" href="#multiple-table-deletes" title="Permalink to this headline">¶</a></h3>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<p>The PostgreSQL, Microsoft SQL Server, and MySQL backends all support DELETE
statements that refer to multiple tables within the WHERE criteria. For PG
and MySQL, this is the “DELETE USING” syntax, and for SQL Server, it’s a
“DELETE FROM” that refers to more than one table. The SQLAlchemy
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.delete" title="sqlalchemy.sql.expression.delete"><code class="xref py py-func docutils literal notranslate"><span class="pre">delete()</span></code></a> construct supports both of these modes
implicitly, by specifying multiple tables in the WHERE clause:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span>\
<span class="n">where</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">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s1">'ed%'</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">stmt</span><span class="p">)</span></pre></div>
</div>
<p>On a PostgreSQL backend, the resulting SQL from the above statement would render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DELETE</span> <span class="n">FROM</span> <span class="n">users</span> <span class="n">USING</span> <span class="n">addresses</span>
<span class="n">WHERE</span> <span class="n">users</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">id</span>
<span class="n">AND</span> <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">email_address</span> <span class="n">LIKE</span> <span class="o">%</span><span class="p">(</span><span class="n">email_address_1</span><span class="p">)</span><span class="n">s</span> <span class="o">||</span> <span class="s1">'</span><span class="si">%%</span><span class="s1">'</span><span class="p">)</span></pre></div>
</div>
<p>When the construct is used on a non-supporting database, the compiler
will raise <code class="docutils literal notranslate"><span class="pre">NotImplementedError</span></code>. For convenience, when a statement
is printed as a string without specification of a dialect, the “string SQL”
compiler will be invoked which provides a non-working SQL representation of the
construct.</p>
</div>
<div class="section" id="matched-row-counts">
<h3>Matched Row Counts<a class="headerlink" href="#matched-row-counts" title="Permalink to this headline">¶</a></h3>
<p>Both of <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">update()</span></code></a> and
<a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><code class="xref py py-meth docutils literal notranslate"><span class="pre">delete()</span></code></a> are associated with <em>matched row counts</em>. This is a
number indicating the number of rows that were matched by the WHERE clause.
Note that by “matched”, this includes rows where no UPDATE actually took place.
The value is available as <a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">rowcount</span></code></a>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">delete</span><span class="p">())</span>
<div class='show_sql'>DELETE FROM users
()
COMMIT
</div><span class="gp">>>> </span><span class="n">result</span><span class="o">.</span><span class="n">rowcount</span>
<span class="go">1</span></pre></div>
</div>
</div>
</div>
<div class="section" id="further-reference">
<h2>Further Reference<a class="headerlink" href="#further-reference" title="Permalink to this headline">¶</a></h2>
<p>Expression Language Reference: <a class="reference internal" href="expression_api.html"><span class="std std-ref">SQL Statements and Expressions API</span></a></p>
<p>Database Metadata Reference: <a class="reference internal" href="metadata.html"><span class="std std-ref">Describing Databases with MetaData</span></a></p>
<p>Engine Reference: <a class="reference internal" href="engines.html"><span class="doc">Engine Configuration</span></a></p>
<p>Connection Reference: <a class="reference internal" href="connections.html"><span class="std std-ref">Working with Engines and Connections</span></a></p>
<p>Types Reference: <a class="reference internal" href="types.html"><span class="std std-ref">Column and Data Types</span></a></p>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="index.html" title="previous chapter">SQLAlchemy Core</a>
Next:
<a href="expression_api.html" title="next chapter">SQL Statements and Expressions API</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>
