<!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 0.8 Documentation </title> <!-- begin iterate through SQLA + 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 SQLA + sphinx environment css_files --> <!-- begin layout.mako headers --> <script type="text/javascript"> var DOCUMENTATION_OPTIONS = { URL_ROOT: '../', VERSION: '0.8.7', COLLAPSE_MODINDEX: false, FILE_SUFFIX: '.html' }; </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> <!-- 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> <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 0.8 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">0.8.7</span> | Release Date: July 22, 2014 </div> <h1>SQLAlchemy 0.8 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 0.8 Documentation</a></h3> <p id="sidebar-paginate"> <a href="index.html" title="SQLAlchemy Core">Up</a> | <a href="index.html" title="SQLAlchemy Core">Prev</a> | <a href="expression_api.html" title="SQL Statements and Expressions API">Next</a> </p> <p id="sidebar-topnav"> <a href="../index.html">Contents</a> | <a href="../genindex.html">Index</a> </p> <div id="sidebar-search"> <form class="search" action="../search.html" method="get"> <input type="text" name="q" size="12" /> <input type="submit" value="Search" /> <input type="hidden" name="check_keywords" value="yes" /> <input type="hidden" name="area" value="default" /> </form> </div> </div> <div id="docs-sidebar"> <h3><a href="#"> SQL Expression Language Tutorial </a></h3> <ul> <li><a class="reference internal" href="#">SQL Expression Language Tutorial</a><ul> <li><a class="reference internal" href="#version-check">Version Check</a></li> <li><a class="reference internal" href="#connecting">Connecting</a></li> <li><a class="reference internal" href="#define-and-create-tables">Define and Create Tables</a></li> <li><a class="reference internal" href="#insert-expressions">Insert Expressions</a></li> <li><a class="reference internal" href="#executing">Executing</a></li> <li><a class="reference internal" href="#executing-multiple-statements">Executing Multiple Statements</a></li> <li><a class="reference internal" href="#selecting">Selecting</a></li> <li><a class="reference internal" href="#operators">Operators</a><ul> <li><a class="reference internal" href="#operator-customization">Operator Customization</a></li> </ul> </li> <li><a class="reference internal" href="#conjunctions">Conjunctions</a></li> <li><a class="reference internal" href="#using-text">Using Text</a></li> <li><a class="reference internal" href="#using-aliases">Using Aliases</a></li> <li><a class="reference internal" href="#using-joins">Using Joins</a></li> <li><a class="reference internal" href="#everything-else">Everything Else</a><ul> <li><a class="reference internal" href="#bind-parameter-objects">Bind Parameter Objects</a></li> <li><a class="reference internal" href="#functions">Functions</a></li> <li><a class="reference internal" href="#window-functions">Window Functions</a></li> <li><a class="reference internal" href="#unions-and-other-set-operations">Unions and Other Set Operations</a></li> <li><a class="reference internal" href="#scalar-selects">Scalar Selects</a></li> <li><a class="reference internal" href="#correlated-subqueries">Correlated Subqueries</a></li> <li><a class="reference internal" href="#ordering-grouping-limiting-offset-ing">Ordering, Grouping, Limiting, Offset...ing...</a></li> </ul> </li> <li><a class="reference internal" href="#inserts-updates-and-deletes">Inserts, Updates and Deletes</a><ul> <li><a class="reference internal" href="#correlated-updates">Correlated Updates</a></li> <li><a class="reference internal" href="#multiple-table-updates">Multiple Table Updates</a></li> <li><a class="reference internal" href="#deletes">Deletes</a></li> <li><a class="reference internal" href="#matched-row-counts">Matched Row Counts</a></li> </ul> </li> <li><a class="reference internal" href="#further-reference">Further Reference</a></li> </ul> </li> </ul> </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"><em>Object Relational Tutorial</em></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 <tt class="docutils literal"><span class="pre">>>></span></tt> 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 0.8</strong> of SQLAlchemy:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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">0.8.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"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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 <tt class="docutils literal"><span class="pre">echo</span></tt> flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python’s standard <tt class="docutils literal"><span class="pre">logging</span></tt> 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 <tt class="docutils literal"><span class="pre">False</span></tt>. 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"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> is an instance of <a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>, and it represents the core interface to the database, adapted through a <em class="xref std std-term">dialect</em> that handles the details of the database and <a class="reference internal" href="../glossary.html#term-dbapi"><em class="xref std std-term">DBAPI</em></a> in use. In this case the SQLite dialect will interpret instructions to the Python built-in <tt class="docutils literal"><span class="pre">sqlite3</span></tt> module.</p> <div class="sidebar"> <p class="first sidebar-title">Lazy Connecting</p> <p class="last">The <a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>, when first returned by <a class="reference internal" href="engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">Engine.execute()</span></tt></a> or <a class="reference internal" href="connections.html#sqlalchemy.engine.Engine.connect" title="sqlalchemy.engine.Engine.connect"><tt class="xref py py-meth docutils literal"><span class="pre">Engine.connect()</span></tt></a> is called, the <a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> establishes a real <a class="reference internal" href="../glossary.html#term-dbapi"><em class="xref std std-term">DBAPI</em></a> connection to the database, which is then used to emit the SQL.</p> <div class="admonition seealso"> <p class="first admonition-title">See also</p> <p class="last"><a class="reference internal" href="engines.html#database-urls"><em>Database Urls</em></a> - includes examples of <a class="reference internal" href="engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>, and in all cases a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> is associated with a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>. A collection of <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>, using the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></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"><div class="highlight"><pre><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="s">'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="s">'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="s">'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="s">'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="s">'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="s">'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="s">'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="s">'users.id'</span><span class="p">)),</span> <span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s">'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"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects, as well as how to create them from an existing database automatically, is described in <a class="reference internal" href="metadata.html"><em>Describing Databases with MetaData</em></a>.</p> <p>Next, to tell the <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></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"><tt class="xref py py-func docutils literal"><span class="pre">create_all()</span></tt></a>, passing it the <tt class="docutils literal"><span class="pre">engine</span></tt> 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"><div class="highlight"><pre><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> <div class='popup_sql'>PRAGMA table_info("users") () PRAGMA table_info("addresses") () 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="first 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="types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a> type as below:</p> <div class="highlight-python"><div class="highlight"><pre><span class="n">Column</span><span class="p">(</span><span class="s">'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="types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a>, as well as similar precision/scale fields available on <a class="reference internal" href="types.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><tt class="xref py py-class docutils literal"><span class="pre">Integer</span></tt></a>, <a class="reference internal" href="types.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><tt class="xref py py-class docutils literal"><span class="pre">Numeric</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Sequence</span></tt></a> construct:</p> <div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Sequence</span> <span class="n">Column</span><span class="p">(</span><span class="s">'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="s">'user_id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">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"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> is therefore:</p> <div class="highlight-python"><div class="highlight"><pre><span class="n">users</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'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="s">'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="s">'user_id_seq'</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="n">Column</span><span class="p">(</span><span class="s">'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="s">'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="s">'password'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">12</span><span class="p">))</span> <span class="p">)</span></pre></div> </div> <p class="last">We include this more verbose <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> construct, which represents an INSERT statement. This is typically created relative to its target table:</p> <div class="highlight-python"><div class="highlight"><pre><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 <tt class="docutils literal"><span class="pre">str()</span></tt> function:</p> <div class="highlight-python"><div class="highlight"><pre><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 <tt class="docutils literal"><span class="pre">users</span></tt> table. This can be limited by using the <tt class="docutils literal"><span class="pre">values()</span></tt> method, which establishes the VALUES clause of the INSERT explicitly:</p> <div class="highlight-python"><div class="highlight"><pre><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="s">'jack'</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">'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 <tt class="docutils literal"><span class="pre">values</span></tt> method limited the VALUES clause to just two columns, the actual data we placed in <tt class="docutils literal"><span class="pre">values</span></tt> 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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></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-python"><div class="highlight"><pre><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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></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 <tt class="docutils literal"><span class="pre">engine</span></tt> object we created is a repository for database connections capable of issuing SQL to the database. To acquire a connection, we use the <tt class="docutils literal"><span class="pre">connect()</span></tt> method:</p> <div class="highlight-python"><div class="highlight"><pre><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"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> object and see what happens:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> used the SQLite <strong>dialect</strong> to help generate the statement; when we use the <tt class="docutils literal"><span class="pre">str()</span></tt> 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"><div class="highlight"><pre><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 <tt class="docutils literal"><span class="pre">result</span></tt> variable we got when we called <tt class="docutils literal"><span class="pre">execute()</span></tt> ? As the SQLAlchemy <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></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:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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 <tt class="docutils literal"><span class="pre">1</span></tt> was automatically generated by SQLite, but only because we did not specify the <tt class="docutils literal"><span class="pre">id</span></tt> column in our <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></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 <tt class="xref py py-class docutils literal"><span class="pre">Dialect</span></tt> knows the specific steps needed to determine the correct value (or values; note that <tt class="docutils literal"><span class="pre">inserted_primary_key</span></tt> returns a list so that it supports composite primary keys).</p> </div> <div class="section" id="executing-multiple-statements"> <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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> statement is usually compiled against the parameters sent to the <tt class="docutils literal"><span class="pre">execute()</span></tt> method on <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, so that there’s no need to use the <tt class="docutils literal"><span class="pre">values</span></tt> keyword with <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a>. Lets create a generic <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> statement again and use it in the “normal” way:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'wendy'</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">'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 <tt class="docutils literal"><span class="pre">execute()</span></tt> method, the compiled <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> included all three columns. The <a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> would have fewer entries in its VALUES clause.</p> <p>To issue many inserts using DBAPI’s <tt class="docutils literal"><span class="pre">executemany()</span></tt> 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"><div class="highlight"><pre><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="s">'user_id'</span><span class="p">:</span> <span class="mi">1</span><span class="p">,</span> <span class="s">'email_address'</span> <span class="p">:</span> <span class="s">'jack@yahoo.com'</span><span class="p">},</span> <span class="gp">... </span> <span class="p">{</span><span class="s">'user_id'</span><span class="p">:</span> <span class="mi">1</span><span class="p">,</span> <span class="s">'email_address'</span> <span class="p">:</span> <span class="s">'jack@msn.com'</span><span class="p">},</span> <span class="gp">... </span> <span class="p">{</span><span class="s">'user_id'</span><span class="p">:</span> <span class="mi">2</span><span class="p">,</span> <span class="s">'email_address'</span> <span class="p">:</span> <span class="s">'www@www.org'</span><span class="p">},</span> <span class="gp">... </span> <span class="p">{</span><span class="s">'user_id'</span><span class="p">:</span> <span class="mi">2</span><span class="p">,</span> <span class="s">'email_address'</span> <span class="p">:</span> <span class="s">'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 <tt class="docutils literal"><span class="pre">addresses</span></tt> 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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></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> </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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> function:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> call, placing the <tt class="docutils literal"><span class="pre">users</span></tt> table within the COLUMNS clause of the select, and then executing. SQLAlchemy expanded the <tt class="docutils literal"><span class="pre">users</span></tt> 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"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></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"><tt class="xref py py-func docutils literal"><span class="pre">fetchone()</span></tt></a> and <a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.fetchall" title="sqlalchemy.engine.ResultProxy.fetchall"><tt class="xref py py-func docutils literal"><span class="pre">fetchall()</span></tt></a>. The easiest way to get rows from it is to just iterate:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="n">row</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"><div class="highlight"><pre><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="s">"name:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s">'name'</span><span class="p">],</span> <span class="s">"; fullname:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s">'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"><div class="highlight"><pre><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="s">"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="s">"; 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"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects directly as keys:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">"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="s">"; 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"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></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"><div class="highlight"><pre><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"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects from our <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>. These are available as named attributes off the <tt class="docutils literal"><span class="pre">c</span></tt> attribute of the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> object:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="n">row</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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> statement:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="n">row</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 <tt class="docutils literal"><span class="pre">users</span></tt> table is produced against each row from the <tt class="docutils literal"><span class="pre">addresses</span></tt> 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"><tt class="xref py py-meth docutils literal"><span class="pre">Select.where()</span></tt></a>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="n">row</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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> which had the effect of adding <tt class="docutils literal"><span class="pre">WHERE</span> <span class="pre">users.id</span> <span class="pre">=</span> <span class="pre">addresses.user_id</span></tt> to our statement, and our results were managed down so that the join of <tt class="docutils literal"><span class="pre">users</span></tt> and <tt class="docutils literal"><span class="pre">addresses</span></tt> 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"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects. It should be clear that something is up. Saying <tt class="docutils literal"><span class="pre">1</span> <span class="pre">==</span> <span class="pre">1</span></tt> produces <tt class="docutils literal"><span class="pre">True</span></tt>, and <tt class="docutils literal"><span class="pre">1</span> <span class="pre">==</span> <span class="pre">2</span></tt> produces <tt class="docutils literal"><span class="pre">False</span></tt>, not a WHERE clause. So lets see exactly what that expression is doing:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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.expression.BinaryExpression object at 0x...></span></pre></div> </div> <p>Wow, surprise ! This is neither a <tt class="docutils literal"><span class="pre">True</span></tt> nor a <tt class="docutils literal"><span class="pre">False</span></tt>. Well what is it ?</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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 <tt class="docutils literal"><span class="pre">==</span></tt> 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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> objects we’ve made so far, thanks to Python’s <tt class="docutils literal"><span class="pre">__eq__()</span></tt> builtin; you call <tt class="docutils literal"><span class="pre">str()</span></tt> 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"><tt class="xref py py-class docutils literal"><span class="pre">ColumnElement</span></tt></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"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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">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"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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="go">users.id = :id_1</span></pre></div> </div> <p>The <tt class="docutils literal"><span class="pre">7</span></tt> literal is embedded the resulting <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">ColumnElement</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> object to see it:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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="go">users.id != :id_1</span> <span class="gp">>>> </span><span class="c"># None converts to IS NULL</span> <span class="gp">>>> </span><span class="k">print</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="go">users.name IS NULL</span> <span class="gp">>>> </span><span class="c"># reverse works too</span> <span class="gp">>>> </span><span class="k">print</span> <span class="s">'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="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"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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="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"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> is important! If we use <tt class="docutils literal"><span class="pre">+</span></tt> with two string based columns (recall we put types like <a class="reference internal" href="types.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><tt class="xref py py-class docutils literal"><span class="pre">Integer</span></tt></a> and <a class="reference internal" href="types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a> on our <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects at the beginning), we get something different:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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="go">users.name || users.fullname</span></pre></div> </div> <p>Where <tt class="docutils literal"><span class="pre">||</span></tt> is the string concatenation operator used on most databases. But not all of them. MySQL users, fear not:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> that’s connected to a MySQL database; the <tt class="docutils literal"><span class="pre">||</span></tt> operator now compiles as MySQL’s <tt class="docutils literal"><span class="pre">concat()</span></tt> 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.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal"><span class="pre">ColumnOperators.op()</span></tt></a> method; this generates whatever operator you need:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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="s">'tiddlywinks'</span><span class="p">)(</span><span class="s">'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-python"><div class="highlight"><pre><span class="n">somecolumn</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s">'&'</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 <cite>somecolumn</cite>.</p> <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.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal"><span class="pre">ColumnOperators.op()</span></tt></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="types.html#types-operators"><em>Redefining and Creating New Operators</em></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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></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_"><tt class="xref py py-func docutils literal"><span class="pre">and_()</span></tt></a>, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.or_" title="sqlalchemy.sql.expression.or_"><tt class="xref py py-func docutils literal"><span class="pre">or_()</span></tt></a>, and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.not_" title="sqlalchemy.sql.expression.not_"><tt class="xref py py-func docutils literal"><span class="pre">not_()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">like()</span></tt></a>):</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="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="s">'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="s">'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="s">'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="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"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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="s">'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="s">'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="s">'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="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"><tt class="xref py py-meth docutils literal"><span class="pre">between()</span></tt></a> and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal"><span class="pre">label()</span></tt></a>. <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><tt class="xref py py-meth docutils literal"><span class="pre">between()</span></tt></a> produces a BETWEEN clause, and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal"><span class="pre">label()</span></tt></a> is used in a column expression to produce labels using the <tt class="docutils literal"><span class="pre">AS</span></tt> keyword; it’s recommended when selecting from expressions that otherwise would not have a name:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">", "</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="s">'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="s">'m'</span><span class="p">,</span> <span class="s">'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="s">'%@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="s">'%@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_"><tt class="xref py py-func docutils literal"><span class="pre">and_()</span></tt></a> is to chain together multiple <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal"><span class="pre">where()</span></tt></a> clauses. The above can also be written as:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">", "</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="s">'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="s">'m'</span><span class="p">,</span> <span class="s">'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="s">'%@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="s">'%@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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> construct through successive method calls is called <a class="reference internal" href="../glossary.html#term-method-chaining"><em class="xref std std-term">method chaining</em></a>.</p> </div> <div class="section" id="using-text"> <span id="sqlexpression-text"></span><h2>Using Text<a class="headerlink" href="#using-text" 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 too. The <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> construct represents any textual statement, in a backend-agnostic way. To use bind parameters with <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a>, always use the named colon format. Such as below, we create a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> and execute it, feeding in the bind parameters to the <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><tt class="xref py py-meth docutils literal"><span class="pre">execute()</span></tt></a> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">"SELECT users.fullname || ', ' || addresses.email_address AS title "</span> <span class="gp">... </span> <span class="s">"FROM users, addresses "</span> <span class="gp">... </span> <span class="s">"WHERE users.id = addresses.user_id "</span> <span class="gp">... </span> <span class="s">"AND users.name BETWEEN :x AND :y "</span> <span class="gp">... </span> <span class="s">"AND (addresses.email_address LIKE :e1 "</span> <span class="gp">... </span> <span class="s">"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="s">'m'</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s">'z'</span><span class="p">,</span> <span class="n">e1</span><span class="o">=</span><span class="s">'%@aol.com'</span><span class="p">,</span> <span class="n">e2</span><span class="o">=</span><span class="s">'%@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>To gain a “hybrid” approach, the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> construct accepts strings for most of its arguments. Below we combine the usage of strings with our constructed <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> object, by using the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> object to structure the statement, and strings to provide all the content within the structure. For this example, SQLAlchemy is not given any <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> or <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects in any of its expressions, so it cannot generate a FROM clause. So we also use the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><tt class="xref py py-meth docutils literal"><span class="pre">select_from()</span></tt></a> method, which accepts a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><tt class="xref py py-class docutils literal"><span class="pre">FromClause</span></tt></a> or string expression to be placed within the FROM clause:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">"users.fullname || ', ' || addresses.email_address AS title"</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="s">"users.id = addresses.user_id"</span><span class="p">,</span> <span class="gp">... </span> <span class="s">"users.name BETWEEN 'm' AND 'z'"</span><span class="p">,</span> <span class="gp">... </span> <span class="s">"(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"</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="s">'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="s">'%@aol.com'</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s">'%@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> <p>Going from constructed SQL to text, we lose some capabilities. We lose the capability for SQLAlchemy to compile our expression to a specific target database; above, our expression won’t work with MySQL since it has no <tt class="docutils literal"><span class="pre">||</span></tt> construct. It also becomes more tedious for SQLAlchemy to be made aware of the datatypes in use; for example, if our bind parameters required UTF-8 encoding before going in, or conversion from a Python <tt class="docutils literal"><span class="pre">datetime</span></tt> into a string (as is required with SQLite), we would have to add extra information to our <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> construct. Similar issues arise on the result set side, where SQLAlchemy also performs type-specific data conversion in some cases; still more information can be added to <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> to work around this. But what we really lose from our statement is the ability to manipulate it, transform it, and analyze it. These features are critical when using the ORM, which makes heavy usage of relational transformations. To show off what we mean, we’ll first introduce the ALIAS construct and the JOIN construct, just so we have some juicier bits to play with.</p> </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 <tt class="docutils literal"><span class="pre">AS</span></tt> 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"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>, <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">FromClause.alias()</span></tt></a> method, which produces a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal"><span class="pre">Alias</span></tt></a> construct. As an example, suppose we know that our user <tt class="docutils literal"><span class="pre">jack</span></tt> 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 <tt class="docutils literal"><span class="pre">addresses</span></tt> table, once for each address. We create two <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal"><span class="pre">Alias</span></tt></a> constructs against <tt class="docutils literal"><span class="pre">addresses</span></tt>, and then use them both within a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> construct:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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="s">'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"><tt class="xref py py-class docutils literal"><span class="pre">Alias</span></tt></a> construct generated the names <tt class="docutils literal"><span class="pre">addresses_1</span></tt> and <tt class="docutils literal"><span class="pre">addresses_2</span></tt> 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 <tt class="docutils literal"><span class="pre">a2</span></tt> alias, the name would come out as <tt class="docutils literal"><span class="pre">addresses_1</span></tt>. 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"><tt class="xref py py-class docutils literal"><span class="pre">Alias</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">FromClause.alias()</span></tt></a> method:</p> <div class="highlight-python"><div class="highlight"><pre><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="s">'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 <tt class="docutils literal"><span class="pre">users</span></tt> table back to the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> we’ve created by making an alias of the entire statement. The <tt class="docutils literal"><span class="pre">correlate(None)</span></tt> directive is to avoid SQLAlchemy’s attempt to “correlate” the inner <tt class="docutils literal"><span class="pre">users</span></tt> table with the outer one:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><tt class="xref py py-meth docutils literal"><span class="pre">outerjoin()</span></tt></a> methods, most commonly accessed from the left table in the join:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> object which we placed on the <tt class="docutils literal"><span class="pre">addresses</span></tt> table way at the beginning of this tutorial. Already the <tt class="docutils literal"><span class="pre">join()</span></tt> 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"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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="s">'%'</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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">select_from()</span></tt></a> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'%'</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"><tt class="xref py py-meth docutils literal"><span class="pre">outerjoin()</span></tt></a> method creates <tt class="docutils literal"><span class="pre">LEFT</span> <span class="pre">OUTER</span> <span class="pre">JOIN</span></tt> 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"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="n">s</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 <tt class="docutils literal"><span class="pre">outerjoin()</span></tt> 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 <tt class="docutils literal"><span class="pre">OracleDialect</span></tt>) to use Oracle-specific SQL:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="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="first admonition-title">See also</p> <p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.join" title="sqlalchemy.sql.expression.join"><tt class="xref py py-func docutils literal"><span class="pre">expression.join()</span></tt></a></p> <p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.outerjoin" title="sqlalchemy.sql.expression.outerjoin"><tt class="xref py py-func docutils literal"><span class="pre">expression.outerjoin()</span></tt></a></p> <p class="last"><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Join" title="sqlalchemy.sql.expression.Join"><tt class="xref py py-class docutils literal"><span class="pre">Join</span></tt></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"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></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"><div class="highlight"><pre><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="s">'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="s">'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"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></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"><div class="highlight"><pre><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="s">'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="s">"'%'"</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="s">'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"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></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"><div class="highlight"><pre><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="s">'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="s">"'%'"</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="s">'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="s">"'@%'"</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="s">'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="first admonition-title">See also</p> <p class="last"><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></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"><tt class="xref py py-data docutils literal"><span class="pre">func</span></tt></a> keyword, which generates functions using attribute access:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="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="n">func</span><span class="o">.</span><span class="n">concat</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="s">'y'</span><span class="p">)</span> <span class="go">concat(:param_1, :param_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-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</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 <tt class="docutils literal"><span class="pre">scalar()</span></tt> 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"><div class="highlight"><pre><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="s">'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 <tt class="docutils literal"><span class="pre">calculate()</span></tt> which takes the parameters <tt class="docutils literal"><span class="pre">x</span></tt> and <tt class="docutils literal"><span class="pre">y</span></tt>, and returns three columns which we’d like to name <tt class="docutils literal"><span class="pre">q</span></tt>, <tt class="docutils literal"><span class="pre">z</span></tt> and <tt class="docutils literal"><span class="pre">r</span></tt>, we can construct using “lexical” column objects as well as bind parameters:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'q'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s">'z'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s">'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="s">'x'</span><span class="p">),</span> <span class="gp">... </span> <span class="n">bindparam</span><span class="p">(</span><span class="s">'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="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 <tt class="docutils literal"><span class="pre">calculate</span></tt> 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"><tt class="xref py py-func docutils literal"><span class="pre">unique_params()</span></tt></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"><div class="highlight"><pre><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="s">'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="s">'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="n">s</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="first admonition-title">See also</p> <p class="last"><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal"><span class="pre">func</span></tt></a></p> </div> </div> <div class="section" id="window-functions"> <h3>Window Functions<a class="headerlink" href="#window-functions" title="Permalink to this headline">¶</a></h3> <p>Any <tt class="xref py py-class docutils literal"><span class="pre">FunctionElement</span></tt>, including functions generated by <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal"><span class="pre">func</span></tt></a>, can be turned into a “window function”, that is an OVER clause, using the <tt class="xref py py-meth docutils literal"><span class="pre">FunctionElement.over()</span></tt> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="k">print</span> <span class="n">s</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> <div class="admonition seealso"> <p class="first admonition-title">See also</p> <p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.over" title="sqlalchemy.sql.expression.over"><tt class="xref py py-func docutils literal"><span class="pre">over()</span></tt></a></p> <p class="last"><tt class="xref py py-meth docutils literal"><span class="pre">FunctionElement.over()</span></tt></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"><tt class="xref py py-func docutils literal"><span class="pre">union()</span></tt></a> and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union_all" title="sqlalchemy.sql.expression.union_all"><tt class="xref py py-func docutils literal"><span class="pre">union_all()</span></tt></a>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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="s">'%@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"><tt class="xref py py-func docutils literal"><span class="pre">intersect()</span></tt></a>, <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect_all" title="sqlalchemy.sql.expression.intersect_all"><tt class="xref py py-func docutils literal"><span class="pre">intersect_all()</span></tt></a>, <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_" title="sqlalchemy.sql.expression.except_"><tt class="xref py py-func docutils literal"><span class="pre">except_()</span></tt></a>, and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_all" title="sqlalchemy.sql.expression.except_all"><tt class="xref py py-func docutils literal"><span class="pre">except_all()</span></tt></a>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'%@%.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="s">'%@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 <tt class="docutils literal"><span class="pre">.alias().select()</span></tt> 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"><div class="highlight"><pre><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="s">'%@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="s">'%@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="c"># 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="s">'%@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="first admonition-title">See also</p> <p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union" title="sqlalchemy.sql.expression.union"><tt class="xref py py-func docutils literal"><span class="pre">union()</span></tt></a></p> <p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.union_all" title="sqlalchemy.sql.expression.union_all"><tt class="xref py py-func docutils literal"><span class="pre">union_all()</span></tt></a></p> <p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect" title="sqlalchemy.sql.expression.intersect"><tt class="xref py py-func docutils literal"><span class="pre">intersect()</span></tt></a></p> <p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.intersect_all" title="sqlalchemy.sql.expression.intersect_all"><tt class="xref py py-func docutils literal"><span class="pre">intersect_all()</span></tt></a></p> <p><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_" title="sqlalchemy.sql.expression.except_"><tt class="xref py py-func docutils literal"><span class="pre">except_()</span></tt></a></p> <p class="last"><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.except_all" title="sqlalchemy.sql.expression.except_all"><tt class="xref py py-func docutils literal"><span class="pre">except_all()</span></tt></a></p> </div> </div> <div class="section" id="scalar-selects"> <span id="id1"></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"><em class="xref std std-term">correlated subquery</em></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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">as_scalar()</span></tt></a> or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><tt class="xref py py-meth docutils literal"><span class="pre">label()</span></tt></a> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-class docutils literal"><span class="pre">ScalarSelect</span></tt></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"><tt class="xref py py-class docutils literal"><span class="pre">FromClause</span></tt></a> hierarchy; it instead is within the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">ColumnElement</span></tt></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"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-meth docutils literal"><span class="pre">SelectBase.label()</span></tt></a> instead:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">"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="first 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"><tt class="xref py py-meth docutils literal"><span class="pre">Select.as_scalar()</span></tt></a></p> <p class="last"><a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.label" title="sqlalchemy.sql.expression.Select.label"><tt class="xref py py-meth docutils literal"><span class="pre">Select.label()</span></tt></a></p> </div> </div> <div class="section" id="correlated-subqueries"> <span id="id2"></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"><em>Scalar Selects</em></a>, the FROM clause of each embedded select did not contain the <tt class="docutils literal"><span class="pre">users</span></tt> table in its FROM clause. This is because SQLAlchemy automatically <a class="reference internal" href="../glossary.html#term-correlates"><em class="xref std std-term">correlates</em></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"><div class="highlight"><pre><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="s">'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 <tt class="docutils literal"><span class="pre">addresses</span></tt> table but not the <tt class="docutils literal"><span class="pre">users</span></tt> 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"><tt class="xref py py-meth docutils literal"><span class="pre">correlate()</span></tt></a> method to specify those FROM clauses that may be correlated:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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 <tt class="docutils literal"><span class="pre">None</span></tt> as the argument:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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"><tt class="xref py py-meth docutils literal"><span class="pre">Select.correlate_except()</span></tt></a> method. Such as, we can write our SELECT for the <tt class="docutils literal"><span class="pre">users</span></tt> table by telling it to correlate all FROM clauses except for <tt class="docutils literal"><span class="pre">users</span></tt>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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> <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 <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.order_by" title="sqlalchemy.sql.expression.SelectBase.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">order_by()</span></tt></a> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-meth docutils literal"><span class="pre">asc()</span></tt></a> and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.desc" title="sqlalchemy.sql.expression.ColumnElement.desc"><tt class="xref py py-meth docutils literal"><span class="pre">desc()</span></tt></a> modifiers:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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 <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.SelectBase.group_by" title="sqlalchemy.sql.expression.SelectBase.group_by"><tt class="xref py py-meth docutils literal"><span class="pre">group_by()</span></tt></a> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-meth docutils literal"><span class="pre">having()</span></tt></a> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-meth docutils literal"><span class="pre">Select.distinct()</span></tt></a> method:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-meth docutils literal"><span class="pre">limit()</span></tt></a> and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Select.offset" title="sqlalchemy.sql.expression.Select.offset"><tt class="xref py py-meth docutils literal"><span class="pre">offset()</span></tt></a> methods provide an easy abstraction into the current backend’s methodology:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></a> prodces INSERT, the <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">values()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">values()</span></tt></a> method accommodates any column expression as a value:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">"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"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></a> or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></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"><tt class="xref py py-meth docutils literal"><span class="pre">execute()</span></tt></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"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a> construct. When using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a> with <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></a> or <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></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"><div class="highlight"><pre><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="s">'_name'</span><span class="p">)</span> <span class="o">+</span> <span class="s">" .. 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="s">'id'</span><span class="p">:</span><span class="mi">4</span><span class="p">,</span> <span class="s">'_name'</span><span class="p">:</span><span class="s">'name1'</span><span class="p">},</span> <span class="gp">... </span> <span class="p">{</span><span class="s">'id'</span><span class="p">:</span><span class="mi">5</span><span class="p">,</span> <span class="s">'_name'</span><span class="p">:</span><span class="s">'name2'</span><span class="p">},</span> <span class="gp">... </span> <span class="p">{</span><span class="s">'id'</span><span class="p">:</span><span class="mi">6</span><span class="p">,</span> <span class="s">'_name'</span><span class="p">:</span><span class="s">'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"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></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"><div class="highlight"><pre><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="s">'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="s">'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"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a> in an “execute many” 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"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a> is the construct used to achieve this:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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="s">'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="s">'oldname'</span><span class="p">:</span><span class="s">'jack'</span><span class="p">,</span> <span class="s">'newname'</span><span class="p">:</span><span class="s">'ed'</span><span class="p">},</span> <span class="gp">... </span> <span class="p">{</span><span class="s">'oldname'</span><span class="p">:</span><span class="s">'wendy'</span><span class="p">,</span> <span class="s">'newname'</span><span class="p">:</span><span class="s">'mary'</span><span class="p">},</span> <span class="gp">... </span> <span class="p">{</span><span class="s">'oldname'</span><span class="p">:</span><span class="s">'jim'</span><span class="p">,</span> <span class="s">'newname'</span><span class="p">:</span><span class="s">'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"><div class="highlight"><pre><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> <div class="versionadded"> <p><span>New in version 0.7.4.</span></p> </div> <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"><tt class="xref py py-func docutils literal"><span class="pre">update()</span></tt></a> construct supports both of these modes implicitly, by specifying multiple tables in the WHERE clause:</p> <div class="highlight-python"><div class="highlight"><pre><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="s">'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="s">'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-python"><pre>UPDATE users SET name=:name FROM addresses WHERE users.id = addresses.id AND addresses.email_address LIKE :email_address_1 || '%%'</pre> </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"><tt class="xref py py-meth docutils literal"><span class="pre">Update.values()</span></tt></a>:</p> <div class="highlight-python"><div class="highlight"><pre><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="s">'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="s">'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="s">'ed%'</span><span class="p">))</span></pre></div> </div> <p>The tables are referenced explicitly in the SET clause:</p> <div class="highlight-python"><pre>UPDATE users, addresses SET addresses.email_address=%s, users.name=%s WHERE users.id = addresses.id AND addresses.email_address LIKE concat(%s, '%%')</pre> </div> <p>SQLAlchemy doesn’t do anything special when these constructs are used on a non-supporting database. The <tt class="docutils literal"><span class="pre">UPDATE</span> <span class="pre">FROM</span></tt> syntax generates by default when multiple tables are present, and the statement will be rejected by the database if this syntax is not supported.</p> </div> <div class="section" id="deletes"> <span id="id3"></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"><tt class="xref py py-meth docutils literal"><span class="pre">delete()</span></tt></a> construct:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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="s">'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="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"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a> and <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><tt class="xref py py-meth docutils literal"><span class="pre">delete()</span></tt></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"><tt class="xref py py-attr docutils literal"><span class="pre">rowcount</span></tt></a>:</p> <div class="highlight-pycon+sql"><div class="highlight"><pre><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"><em>SQL Statements and Expressions API</em></a></p> <p>Database Metadata Reference: <a class="reference internal" href="metadata.html"><em>Describing Databases with MetaData</em></a></p> <p>Engine Reference: <a class="reference internal" href="engines.html"><em>Engine Configuration</em></a></p> <p>Connection Reference: <a class="reference internal" href="connections.html"><em>Working with Engines and Connections</em></a></p> <p>Types Reference: <a class="reference internal" href="types.html"><em>Column and Data Types</em></a></p> </div> </div> </div> </div> <div id="docs-bottom-navigation" class="docs-navigation-links"> 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-2014, the SQLAlchemy authors and contributors. Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1. </div> </div> </div> </body> </html>