File: tutorial.html

package info (click to toggle)
sqlalchemy 0.9.8%2Bdfsg-0.1
links: PTS, VCS
area: main
in suites: jessie, jessie-kfreebsd
size: 23,952 kB
ctags: 24,534
sloc: python: 152,282; ansic: 1,346; makefile: 257; xml: 17
file content (2021 lines) | stat: -rw-r--r-- 245,460 bytes
<!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>
            
    
                Object Relational Tutorial
             &mdash;
    SQLAlchemy 0.9 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/sphinx_paramlinks.css" type="text/css" />
                <link rel="stylesheet" href="../_static/changelog.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.9.8',
          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.9 Documentation" href="../index.html" />
        <link rel="up" title="SQLAlchemy ORM" href="index.html" />
        <link rel="next" title="Mapper Configuration" href="mapper_config.html" />
        <link rel="prev" title="SQLAlchemy ORM" 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.9.8</span> | Release Date: October 13, 2014
    </div>

    <h1>SQLAlchemy 0.9 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.9 Documentation</a></h3>

            <p id="sidebar-paginate">
                    <a href="index.html" title="SQLAlchemy ORM">Up</a> |

                    <a href="index.html" title="SQLAlchemy ORM">Prev</a> |
                    <a href="mapper_config.html" title="Mapper Configuration">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="#">            
                Object Relational Tutorial
            
        </a></h3>
        <ul>
<li><a class="reference internal" href="#">Object Relational 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="#declare-a-mapping">Declare a Mapping</a></li>
<li><a class="reference internal" href="#create-a-schema">Create a Schema</a></li>
<li><a class="reference internal" href="#create-an-instance-of-the-mapped-class">Create an Instance of the Mapped Class</a></li>
<li><a class="reference internal" href="#creating-a-session">Creating a Session</a></li>
<li><a class="reference internal" href="#adding-new-objects">Adding New Objects</a></li>
<li><a class="reference internal" href="#rolling-back">Rolling Back</a></li>
<li><a class="reference internal" href="#querying">Querying</a><ul>
<li><a class="reference internal" href="#common-filter-operators">Common Filter Operators</a></li>
<li><a class="reference internal" href="#returning-lists-and-scalars">Returning Lists and Scalars</a></li>
<li><a class="reference internal" href="#using-literal-sql">Using Literal SQL</a></li>
<li><a class="reference internal" href="#counting">Counting</a></li>
</ul>
</li>
<li><a class="reference internal" href="#building-a-relationship">Building a Relationship</a></li>
<li><a class="reference internal" href="#working-with-related-objects">Working with Related Objects</a></li>
<li><a class="reference internal" href="#querying-with-joins">Querying with Joins</a><ul>
<li><a class="reference internal" href="#using-aliases">Using Aliases</a></li>
<li><a class="reference internal" href="#using-subqueries">Using Subqueries</a></li>
<li><a class="reference internal" href="#selecting-entities-from-subqueries">Selecting Entities from Subqueries</a></li>
<li><a class="reference internal" href="#using-exists">Using EXISTS</a></li>
<li><a class="reference internal" href="#common-relationship-operators">Common Relationship Operators</a></li>
</ul>
</li>
<li><a class="reference internal" href="#eager-loading">Eager Loading</a><ul>
<li><a class="reference internal" href="#subquery-load">Subquery Load</a></li>
<li><a class="reference internal" href="#joined-load">Joined Load</a></li>
<li><a class="reference internal" href="#explicit-join-eagerload">Explicit Join + Eagerload</a></li>
</ul>
</li>
<li><a class="reference internal" href="#deleting">Deleting</a><ul>
<li><a class="reference internal" href="#configuring-delete-delete-orphan-cascade">Configuring delete/delete-orphan Cascade</a></li>
</ul>
</li>
<li><a class="reference internal" href="#building-a-many-to-many-relationship">Building a Many To Many Relationship</a></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="object-relational-tutorial">
<span id="ormtutorial-toplevel"></span><h1>Object Relational Tutorial<a class="headerlink" href="#object-relational-tutorial" title="Permalink to this headline">¶</a></h1>
<p>The SQLAlchemy Object Relational Mapper presents a method of associating
user-defined Python classes with database tables, and instances of those
classes (objects) with rows in their corresponding tables. It includes a
system that transparently synchronizes all changes in state between objects
and their related rows, called a <a class="reference internal" href="../glossary.html#term-unit-of-work"><em class="xref std std-term">unit of work</em></a>, as well as a system
for expressing database queries in terms of the user defined classes and their
defined relationships between each other.</p>
<p>The ORM is in contrast to the SQLAlchemy Expression Language, upon which the
ORM is constructed. Whereas the SQL Expression Language, introduced in
<a class="reference internal" href="../core/tutorial.html"><em>SQL Expression Language Tutorial</em></a>, presents a system of representing the primitive
constructs of the relational database directly without opinion, the ORM
presents a high level and abstracted pattern of usage, which itself is an
example of applied usage of the Expression Language.</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 internal" href="../glossary.html#term-domain-model"><em class="xref std std-term">domain model</em></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 Object Relational Mapper
exclusively. In advanced situations, an application constructed with the ORM
may 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">&gt;&gt;&gt;</span></tt> line
represents something you can type at a Python command prompt, and the
following text represents the expected return value.</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.9</strong> of SQLAlchemy:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">import</span> <span class="nn">sqlalchemy</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">sqlalchemy</span><span class="o">.</span><span class="n">__version__</span> 
<span class="go">0.9.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. To connect we
use <a class="reference internal" href="../core/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-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </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">&gt;&gt;&gt; </span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">&#39;sqlite:///:memory:&#39;</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&#8217;s standard <tt class="docutils literal"><span class="pre">logging</span></tt> module. With it enabled, we&#8217;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&#8217;t get in our way; just click the
&#8220;SQL&#8221; links to see what&#8217;s being generated.</p>
<p>The return value of <a class="reference internal" href="../core/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="../core/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="../core/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="../core/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="../core/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="../core/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="../core/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.  When using the ORM, we typically
don&#8217;t use the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> directly once created; instead, it&#8217;s used
behind the scenes by the ORM as we&#8217;ll see shortly.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="../core/engines.html#database-urls"><em>Database Urls</em></a> - includes examples of <a class="reference internal" href="../core/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="declare-a-mapping">
<h2>Declare a Mapping<a class="headerlink" href="#declare-a-mapping" title="Permalink to this headline">¶</a></h2>
<p>When using the ORM, the configurational process starts by describing the database
tables we&#8217;ll be dealing with, and then by defining our own classes which will
be mapped to those tables.   In modern SQLAlchemy,
these two tasks are usually performed together,
using a system known as <a class="reference internal" href="extensions/declarative.html"><em>Declarative</em></a>, which allows us to create
classes that include directives to describe the actual database table they will
be mapped to.</p>
<p>Classes mapped using the Declarative system are defined in terms of a base class which
maintains a catalog of classes and
tables relative to that base - this is known as the <strong>declarative base class</strong>.  Our
application will usually have just one instance of this base in a commonly
imported module.   We create the base class using the <a class="reference internal" href="extensions/declarative.html#sqlalchemy.ext.declarative.declarative_base" title="sqlalchemy.ext.declarative.declarative_base"><tt class="xref py py-func docutils literal"><span class="pre">declarative_base()</span></tt></a>
function, as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>

<span class="gp">&gt;&gt;&gt; </span><span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span></pre></div>
</div>
<p>Now that we have a &#8220;base&#8221;, we can define any number of mapped classes in terms
of it.  We will start with just a single table called <tt class="docutils literal"><span class="pre">users</span></tt>, which will store
records for the end-users using our application.
A new class called <tt class="docutils literal"><span class="pre">User</span></tt> will be the class to which we map this table.  Within
the class, we define details about the table to which we&#8217;ll be mapping, primarily
the table name, and names and datatypes of columns:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</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="gp">&gt;&gt;&gt; </span><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span>    <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;users&#39;</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">... </span>    <span class="n">fullname</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">... </span>    <span class="n">password</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="gp">... </span>       <span class="k">return</span> <span class="s">&quot;&lt;User(name=&#39;</span><span class="si">%s</span><span class="s">&#39;, fullname=&#39;</span><span class="si">%s</span><span class="s">&#39;, password=&#39;</span><span class="si">%s</span><span class="s">&#39;)&gt;&quot;</span> <span class="o">%</span> <span class="p">(</span>
<span class="gp">... </span>                            <span class="bp">self</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span><span class="p">)</span></pre></div>
</div>
<div class="sidebar">
<p class="first sidebar-title">Tip</p>
<p class="last">The <tt class="docutils literal"><span class="pre">User</span></tt> class defines a <tt class="docutils literal"><span class="pre">__repr__()</span></tt> method,
but note that is <strong>optional</strong>; we only implement it in
this tutorial so that our examples show nicely
formatted <tt class="docutils literal"><span class="pre">User</span></tt> objects.</p>
</div>
<p>A class using Declarative at a minimum
needs a <tt class="docutils literal"><span class="pre">__tablename__</span></tt> attribute, and at least one
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> which is part of a primary key <a class="footnote-reference" href="#id2" id="id1">[1]</a>.  SQLAlchemy never makes any
assumptions by itself about the table to which
a class refers, including that it has no built-in conventions for names,
datatypes, or constraints.   But this doesn&#8217;t mean
boilerplate is required; instead, you&#8217;re encouraged to create your
own automated conventions using helper functions and mixin classes, which
is described in detail at <a class="reference internal" href="extensions/declarative.html#declarative-mixins"><em>Mixin and Custom Base Classes</em></a>.</p>
<p>When our class is constructed, Declarative replaces all the <a class="reference internal" href="../core/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 with special Python accessors known as <a class="reference internal" href="../glossary.html#term-descriptors"><em class="xref std std-term">descriptors</em></a>; this is a
process known as <a class="reference internal" href="../glossary.html#term-instrumentation"><em class="xref std std-term">instrumentation</em></a>.   The &#8220;instrumented&#8221; mapped class
will provide us with the means to refer to our table in a SQL context as well
as to persist and load the values of columns from the database.</p>
<p>Outside of what the mapping process does to our class, the class remains
otherwise mostly a normal Python class, to which we can define any
number of ordinary attributes and methods needed by our application.</p>
<table class="docutils footnote" frame="void" id="id2" rules="none">
<colgroup><col class="label" /><col /></colgroup>
<tbody valign="top">
<tr><td class="label"><a class="fn-backref" href="#id1">[1]</a></td><td>For information on why a primary key is required, see
<a class="reference internal" href="../faq.html#faq-mapper-primary-key"><em>How do I map a table that has no primary key?</em></a>.</td></tr>
</tbody>
</table>
</div>
<div class="section" id="create-a-schema">
<h2>Create a Schema<a class="headerlink" href="#create-a-schema" title="Permalink to this headline">¶</a></h2>
<p>With our <tt class="docutils literal"><span class="pre">User</span></tt> class constructed via the Declarative system, we have defined information about
our table, known as <em class="xref std std-term">table metadata</em>.   The object used by SQLAlchemy to represent
this information for a specific table is called the <a class="reference internal" href="../core/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, and here Declarative has made
one for us.  We can see this object by inspecting the <tt class="docutils literal"><span class="pre">__table__</span></tt> attribute:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">User</span><span class="o">.</span><span class="n">__table__</span> 
<span class="go">Table(&#39;users&#39;, MetaData(bind=None),</span>
<span class="go">            Column(&#39;id&#39;, Integer(), table=&lt;users&gt;, primary_key=True, nullable=False),</span>
<span class="go">            Column(&#39;name&#39;, String(), table=&lt;users&gt;),</span>
<span class="go">            Column(&#39;fullname&#39;, String(), table=&lt;users&gt;),</span>
<span class="go">            Column(&#39;password&#39;, String(), table=&lt;users&gt;), schema=None)</span></pre></div>
</div>
<div class="sidebar">
<p class="first sidebar-title">Classical Mappings</p>
<p class="last">The Declarative system, though highly recommended,
is not required in order to use SQLAlchemy&#8217;s ORM.
Outside of Declarative, any
plain Python class can be mapped to any <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>
using the <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">mapper()</span></tt></a> function directly; this
less common usage is described at <a class="reference internal" href="mapper_config.html#classical-mapping"><em>Classical Mappings</em></a>.</p>
</div>
<p>When we declared our class, Declarative used a Python metaclass in order to
perform additional activities once the class declaration was complete; within
this phase, it then created a <a class="reference internal" href="../core/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 according to our
specifications, and associated it with the class by constructing
a <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> object.  This object is a behind-the-scenes object we normally
don&#8217;t need to deal with directly (though it can provide plenty of information
about our mapping when we need it).</p>
<p>The <a class="reference internal" href="../core/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 is a member of a larger collection
known as <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>.  When using Declarative,
this object is available using the <tt class="docutils literal"><span class="pre">.metadata</span></tt>
attribute of our declarative base class.</p>
<p>The <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>
is a <em class="xref std std-term">registry</em> which includes the ability to emit a limited set
of schema generation commands to the database.  As our SQLite database
does not actually have a <tt class="docutils literal"><span class="pre">users</span></tt> table present, we can use <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>
to issue CREATE TABLE statements to the database for all tables that don&#8217;t yet exist.
Below, we call the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-meth docutils literal"><span class="pre">MetaData.create_all()</span></tt></a> method, passing in our <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>
as a source of database connectivity.  We will see that special commands are
first emitted to check for the presence of the <tt class="docutils literal"><span class="pre">users</span></tt> table, and following that
the actual <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> statement:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">Base</span><span class="o">.</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='show_sql'>PRAGMA table_info("users")
()
CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    password VARCHAR,
    PRIMARY KEY (id)
)
()
COMMIT</div></pre></div>
</div>
<div class="topic">
<p class="topic-title first">Minimal Table Descriptions vs. Full Descriptions</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&#8217;s not allowed. So if running
this tutorial on one of those databases, and you wish to use SQLAlchemy to
issue CREATE TABLE, a &#8220;length&#8221; may be provided to the <a class="reference internal" href="../core/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="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="../core/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="../core/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="../core/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&#8217;t generate or assume these
without being instructed. For that, you use the <a class="reference internal" href="../core/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="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s">&#39;user_id_seq&#39;</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="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> generated via our declarative
mapping is therefore:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;users&#39;</span>
    <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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">&#39;user_id_seq&#39;</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">name</span> <span class="o">=</span> <span class="n">Column</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">fullname</span> <span class="o">=</span> <span class="n">Column</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">password</span> <span class="o">=</span> <span class="n">Column</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="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
        <span class="k">return</span> <span class="s">&quot;&lt;User(name=&#39;</span><span class="si">%s</span><span class="s">&#39;, fullname=&#39;</span><span class="si">%s</span><span class="s">&#39;, password=&#39;</span><span class="si">%s</span><span class="s">&#39;)&gt;&quot;</span> <span class="o">%</span> <span class="p">(</span>
                                <span class="bp">self</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span><span class="p">)</span></pre></div>
</div>
<p>We include this more verbose table definition 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="create-an-instance-of-the-mapped-class">
<h2>Create an Instance of the Mapped Class<a class="headerlink" href="#create-an-instance-of-the-mapped-class" title="Permalink to this headline">¶</a></h2>
<p>With mappings complete, let&#8217;s now create and inspect a <tt class="docutils literal"><span class="pre">User</span></tt> object:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">ed_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;edspassword&#39;</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">ed_user</span><span class="o">.</span><span class="n">name</span>
<span class="go">&#39;ed&#39;</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">ed_user</span><span class="o">.</span><span class="n">password</span>
<span class="go">&#39;edspassword&#39;</span>
<span class="gp">&gt;&gt;&gt; </span><span class="nb">str</span><span class="p">(</span><span class="n">ed_user</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="go">&#39;None&#39;</span></pre></div>
</div>
<div class="sidebar">
<p class="first sidebar-title">the <tt class="docutils literal"><span class="pre">__init__()</span></tt> method</p>
<p class="last">Our <tt class="docutils literal"><span class="pre">User</span></tt> class, as defined using the Declarative system, has
been provided with a constructor (e.g. <tt class="docutils literal"><span class="pre">__init__()</span></tt> method) which automatically
accepts keyword names that match the columns we&#8217;ve mapped.    We are free
to define any explicit <tt class="docutils literal"><span class="pre">__init__()</span></tt> method we prefer on our class, which
will override the default method provided by Declarative.</p>
</div>
<p>Even though we didn&#8217;t specify it in the constructor, the <tt class="docutils literal"><span class="pre">id</span></tt> attribute
still produces a value of <tt class="docutils literal"><span class="pre">None</span></tt> when we access it (as opposed to Python&#8217;s
usual behavior of raising <tt class="docutils literal"><span class="pre">AttributeError</span></tt> for an undefined attribute).
SQLAlchemy&#8217;s <a class="reference internal" href="../glossary.html#term-instrumentation"><em class="xref std std-term">instrumentation</em></a> normally produces this default value for
column-mapped attributes when first accessed.    For those attributes where
we&#8217;ve actually assigned a value, the instrumentation system is tracking
those assignments for use within an eventual INSERT statement to be emitted to the
database.</p>
</div>
<div class="section" id="creating-a-session">
<h2>Creating a Session<a class="headerlink" href="#creating-a-session" title="Permalink to this headline">¶</a></h2>
<p>We&#8217;re now ready to start talking to the database. The ORM&#8217;s &#8220;handle&#8221; to the
database is the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>. When we first set up
the application, at the same level as our <a class="reference internal" href="../core/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>
statement, we define a <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> class which
will serve as a factory for new <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>
objects:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">sessionmaker</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<p>In the case where your application does not yet have an
<a class="reference internal" href="../core/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 you define your module-level
objects, just set it up like this:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span></pre></div>
</div>
<p>Later, when you create your engine with <a class="reference internal" href="../core/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>,
connect it to the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> using
<a class="reference internal" href="session.html#sqlalchemy.orm.session.sessionmaker.configure" title="sqlalchemy.orm.session.sessionmaker.configure"><tt class="xref py py-meth docutils literal"><span class="pre">configure()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span>  <span class="c"># once engine is available</span></pre></div>
</div>
<div class="sidebar">
<p class="first sidebar-title">Session Lifecycle Patterns</p>
<p class="last">The question of when to make a <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> depends a lot on what
kind of application is being built.  Keep in mind,
the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is just a workspace for your objects,
local to a particular database connection - if you think of
an application thread as a guest at a dinner party, the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>
is the guest&#8217;s plate and the objects it holds are the food
(and the database...the kitchen?)!  More on this topic
available at <a class="reference internal" href="session.html#session-faq-whentocreate"><em>When do I construct a Session, when do I commit it, and when do I close it?</em></a>.</p>
</div>
<p>This custom-made <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> class will create
new <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> objects which are bound to our
database. Other transactional characteristics may be defined when calling
<a class="reference internal" href="session.html#sqlalchemy.orm.session.sessionmaker" title="sqlalchemy.orm.session.sessionmaker"><tt class="xref py py-class docutils literal"><span class="pre">sessionmaker</span></tt></a> as well; these are described in a later
chapter. Then, whenever you need to have a conversation with the database, you
instantiate a <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span></pre></div>
</div>
<p>The above <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is associated with our
SQLite-enabled <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>, but it hasn&#8217;t opened any connections yet. When it&#8217;s first
used, it retrieves a connection from a pool of connections maintained by the
<a class="reference internal" href="../core/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 holds onto it until we commit all changes and/or close the
session object.</p>
</div>
<div class="section" id="adding-new-objects">
<h2>Adding New Objects<a class="headerlink" href="#adding-new-objects" title="Permalink to this headline">¶</a></h2>
<p>To persist our <tt class="docutils literal"><span class="pre">User</span></tt> object, we <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-meth docutils literal"><span class="pre">add()</span></tt></a> it to our <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">ed_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;edspassword&#39;</span><span class="p">)</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">ed_user</span><span class="p">)</span></pre></div>
</div>
<p>At this point, we say that the instance is <strong>pending</strong>; no SQL has yet been issued
and the object is not yet represented by a row in the database.  The
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> will issue the SQL to persist <tt class="docutils literal"><span class="pre">Ed</span>
<span class="pre">Jones</span></tt> as soon as is needed, using a process known as a <strong>flush</strong>. If we
query the database for <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt>, all pending information will first be
flushed, and the query is issued immediately thereafter.</p>
<p>For example, below we create a new <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object
which loads instances of <tt class="docutils literal"><span class="pre">User</span></tt>. We &#8220;filter by&#8221; the <tt class="docutils literal"><span class="pre">name</span></tt> attribute of
<tt class="docutils literal"><span class="pre">ed</span></tt>, and indicate that we&#8217;d like only the first result in the full list of
rows. A <tt class="docutils literal"><span class="pre">User</span></tt> instance is returned which is equivalent to that which we&#8217;ve
added:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">our_user</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span> 
<div class='popup_sql'>BEGIN (implicit)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('ed', 'Ed Jones', 'edspassword')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?
 LIMIT ? OFFSET ?
('ed', 1, 0)</div><span class="o">&gt;&gt;&gt;</span> <span class="n">our_user</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;edspassword&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>In fact, the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> has identified that the
row returned is the <strong>same</strong> row as one already represented within its
internal map of objects, so we actually got back the identical instance as
that which we just added:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">ed_user</span> <span class="ow">is</span> <span class="n">our_user</span>
<span class="go">True</span></pre></div>
</div>
<p>The ORM concept at work here is known as an <a class="reference internal" href="../glossary.html#term-identity-map"><em class="xref std std-term">identity map</em></a>
and ensures that
all operations upon a particular row within a
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> operate upon the same set of data.
Once an object with a particular primary key is present in the
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>, all SQL queries on that
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> will always return the same Python
object for that particular primary key; it also will raise an error if an
attempt is made to place a second, already-persisted object with the same
primary key within the session.</p>
<p>We can add more <tt class="docutils literal"><span class="pre">User</span></tt> objects at once using
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.add_all" title="sqlalchemy.orm.session.Session.add_all"><tt class="xref py py-func docutils literal"><span class="pre">add_all()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span>
<span class="o">...</span>     <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">),</span>
<span class="o">...</span>     <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;mary&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Mary Contrary&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;xxg527&#39;</span><span class="p">),</span>
<span class="o">...</span>     <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Fred Flinstone&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;blah&#39;</span><span class="p">)])</span></pre></div>
</div>
<p>Also, we&#8217;ve decided the password for Ed isn&#8217;t too secure, so lets change it:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">ed_user</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="s">&#39;f8s7ccs&#39;</span></pre></div>
</div>
<p>The <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is paying attention. It knows,
for example, that <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt> has been modified:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">dirty</span>
<span class="n">IdentitySet</span><span class="p">([</span><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">])</span></pre></div>
</div>
<p>and that three new <tt class="docutils literal"><span class="pre">User</span></tt> objects are pending:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">new</span>  
<span class="n">IdentitySet</span><span class="p">([</span><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;mary&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Mary Contrary&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;xxg527&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Fred Flinstone&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;blah&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">])</span></pre></div>
</div>
<p>We tell the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> that we&#8217;d like to issue
all remaining changes to the database and commit the transaction, which has
been in progress throughout. We do this via <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal"><span class="pre">commit()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<div class='popup_sql'>UPDATE users SET password=? WHERE users.id = ?
('f8s7ccs', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('wendy', 'Wendy Williams', 'foobar')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('mary', 'Mary Contrary', 'xxg527')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fred', 'Fred Flinstone', 'blah')
COMMIT</div></pre></div>
</div>
<p><a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal"><span class="pre">commit()</span></tt></a> flushes whatever remaining changes remain to the
database, and commits the transaction. The connection resources referenced by
the session are now returned to the connection pool. Subsequent operations
with this session will occur in a <strong>new</strong> transaction, which will again
re-acquire connection resources when first needed.</p>
<p>If we look at Ed&#8217;s <tt class="docutils literal"><span class="pre">id</span></tt> attribute, which earlier was <tt class="docutils literal"><span class="pre">None</span></tt>, it now has a value:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">ed_user</span><span class="o">.</span><span class="n">id</span> 
<div class='popup_sql'>BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.id = ?
(1,)</div><span class="mi">1</span></pre></div>
</div>
<p>After the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> inserts new rows in the
database, all newly generated identifiers and database-generated defaults
become available on the instance, either immediately or via
load-on-first-access. In this case, the entire row was re-loaded on access
because a new transaction was begun after we issued <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal"><span class="pre">commit()</span></tt></a>. SQLAlchemy
by default refreshes data from a previous transaction the first time it&#8217;s
accessed within a new transaction, so that the most recent state is available.
The level of reloading is configurable as is described in <a class="reference internal" href="session.html"><em>Using the Session</em></a>.</p>
<div class="topic">
<p class="topic-title first">Session Object States</p>
<p>As our <tt class="docutils literal"><span class="pre">User</span></tt> object moved from being outside the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>, to
inside the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> without a primary key, to actually being
inserted, it moved between three out of four
available &#8220;object states&#8221; - <strong>transient</strong>, <strong>pending</strong>, and <strong>persistent</strong>.
Being aware of these states and what they mean is always a good idea -
be sure to read <a class="reference internal" href="session.html#session-object-states"><em>Quickie Intro to Object States</em></a> for a quick overview.</p>
</div>
</div>
<div class="section" id="rolling-back">
<h2>Rolling Back<a class="headerlink" href="#rolling-back" title="Permalink to this headline">¶</a></h2>
<p>Since the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> works within a transaction,
we can roll back changes made too. Let&#8217;s make two changes that we&#8217;ll revert;
<tt class="docutils literal"><span class="pre">ed_user</span></tt>&#8216;s user name gets set to <tt class="docutils literal"><span class="pre">Edwardo</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">ed_user</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="s">&#39;Edwardo&#39;</span></pre></div>
</div>
<p>and we&#8217;ll add another erroneous user, <tt class="docutils literal"><span class="pre">fake_user</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">fake_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fakeuser&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Invalid&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;12345&#39;</span><span class="p">)</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">fake_user</span><span class="p">)</span></pre></div>
</div>
<p>Querying the session, we can see that they&#8217;re flushed into the current transaction:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">&#39;Edwardo&#39;</span><span class="p">,</span> <span class="s">&#39;fakeuser&#39;</span><span class="p">]))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> 
<div class='popup_sql'>UPDATE users SET name=? WHERE users.id = ?
('Edwardo', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fakeuser', 'Invalid', '12345')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name IN (?, ?)
('Edwardo', 'fakeuser')</div><span class="p">[</span><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;Edwardo&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">user</span><span class="o">=</span><span class="s">&#39;fakeuser&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Invalid&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;12345&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
<p>Rolling back, we can see that <tt class="docutils literal"><span class="pre">ed_user</span></tt>&#8216;s name is back to <tt class="docutils literal"><span class="pre">ed</span></tt>, and
<tt class="docutils literal"><span class="pre">fake_user</span></tt> has been kicked out of the session:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<div class='popup_sql'>ROLLBACK</div>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">ed_user</span><span class="o">.</span><span class="n">name</span> 
<div class='popup_sql'>BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.id = ?
(1,)</div><span class="s">u&#39;ed&#39;</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">fake_user</span> <span class="ow">in</span> <span class="n">session</span>
<span class="bp">False</span></pre></div>
</div>
<p>issuing a SELECT illustrates the changes made to the database:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="s">&#39;fakeuser&#39;</span><span class="p">]))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name IN (?, ?)
('ed', 'fakeuser')</div><span class="p">[</span><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
</div>
<div class="section" id="querying">
<span id="ormtutorial-querying"></span><h2>Querying<a class="headerlink" href="#querying" title="Permalink to this headline">¶</a></h2>
<p>A <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object is created using the
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-class docutils literal"><span class="pre">query()</span></tt></a> method on
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>. This function takes a variable
number of arguments, which can be any combination of classes and
class-instrumented descriptors. Below, we indicate a
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> which loads <tt class="docutils literal"><span class="pre">User</span></tt> instances. When
evaluated in an iterative context, the list of <tt class="docutils literal"><span class="pre">User</span></tt> objects present is
returned:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">instance</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">):</span> 
<span class="o">...</span>     <span class="k">print</span> <span class="n">instance</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">instance</span><span class="o">.</span><span class="n">fullname</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users ORDER BY users.id
()</div><span class="n">ed</span> <span class="n">Ed</span> <span class="n">Jones</span>
<span class="n">wendy</span> <span class="n">Wendy</span> <span class="n">Williams</span>
<span class="n">mary</span> <span class="n">Mary</span> <span class="n">Contrary</span>
<span class="n">fred</span> <span class="n">Fred</span> <span class="n">Flinstone</span></pre></div>
</div>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> also accepts ORM-instrumented
descriptors as arguments. Any time multiple class entities or column-based
entities are expressed as arguments to the
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-class docutils literal"><span class="pre">query()</span></tt></a> function, the return result
is expressed as tuples:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="n">fullname</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="p">):</span> 
<span class="o">...</span>     <span class="k">print</span> <span class="n">name</span><span class="p">,</span> <span class="n">fullname</span>
<div class='popup_sql'>SELECT users.name AS users_name,
        users.fullname AS users_fullname
FROM users
()</div><span class="n">ed</span> <span class="n">Ed</span> <span class="n">Jones</span>
<span class="n">wendy</span> <span class="n">Wendy</span> <span class="n">Williams</span>
<span class="n">mary</span> <span class="n">Mary</span> <span class="n">Contrary</span>
<span class="n">fred</span> <span class="n">Fred</span> <span class="n">Flinstone</span></pre></div>
</div>
<p>The tuples returned by <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> are <em>named</em>
tuples, supplied by the <a class="reference internal" href="query.html#sqlalchemy.util.KeyedTuple" title="sqlalchemy.util.KeyedTuple"><tt class="xref py py-class docutils literal"><span class="pre">KeyedTuple</span></tt></a> class, and can be treated much like an
ordinary Python object. The names are
the same as the attribute&#8217;s name for an attribute, and the class name for a
class:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span> 
<span class="o">...</span>    <span class="k">print</span> <span class="n">row</span><span class="o">.</span><span class="n">User</span><span class="p">,</span> <span class="n">row</span><span class="o">.</span><span class="n">name</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
()</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="n">ed</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="n">wendy</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;mary&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Mary Contrary&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;xxg527&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="n">mary</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Fred Flinstone&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;blah&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="n">fred</span></pre></div>
</div>
<p>You can control the names of individual column expressions using the
<a class="reference internal" href="../core/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> construct, which is available from
any <a class="reference internal" href="../core/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>-derived object, as well as any class attribute which
is mapped to one (such as <tt class="docutils literal"><span class="pre">User.name</span></tt>):</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">&#39;name_label&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">():</span> 
<span class="o">...</span>    <span class="k">print</span><span class="p">(</span><span class="n">row</span><span class="o">.</span><span class="n">name_label</span><span class="p">)</span>
<div class='popup_sql'>SELECT users.name AS name_label
FROM users
()</div><span class="n">ed</span>
<span class="n">wendy</span>
<span class="n">mary</span>
<span class="n">fred</span></pre></div>
</div>
<p>The name given to a full entity such as <tt class="docutils literal"><span class="pre">User</span></tt>, assuming that multiple
entities are present in the call to <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-meth docutils literal"><span class="pre">query()</span></tt></a>, can be controlled using
<a class="reference internal" href="query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><tt class="xref py py-func docutils literal"><span class="pre">aliased()</span></tt></a> :</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">aliased</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">user_alias</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">&#39;user_alias&#39;</span><span class="p">)</span>

<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">user_alias</span><span class="p">,</span> <span class="n">user_alias</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span> 
<span class="o">...</span>    <span class="k">print</span> <span class="n">row</span><span class="o">.</span><span class="n">user_alias</span>
<div class='popup_sql'>SELECT user_alias.id AS user_alias_id,
        user_alias.name AS user_alias_name,
        user_alias.fullname AS user_alias_fullname,
        user_alias.password AS user_alias_password
FROM users AS user_alias
()</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;mary&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Mary Contrary&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;xxg527&#39;</span><span class="p">)</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Fred Flinstone&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;blah&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>Basic operations with <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> include issuing
LIMIT and OFFSET, most conveniently using Python array slices and typically in
conjunction with ORDER BY:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">u</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)[</span><span class="mi">1</span><span class="p">:</span><span class="mi">3</span><span class="p">]:</span> 
<span class="o">...</span>    <span class="k">print</span> <span class="n">u</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users ORDER BY users.id
LIMIT ? OFFSET ?
(2, 1)</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;mary&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Mary Contrary&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;xxg527&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>and filtering results, which is accomplished either with
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter_by" title="sqlalchemy.orm.query.Query.filter_by"><tt class="xref py py-func docutils literal"><span class="pre">filter_by()</span></tt></a>, which uses keyword arguments:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>             <span class="n">filter_by</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">):</span> 
<span class="o">...</span>    <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)</div><span class="n">ed</span></pre></div>
</div>
<p>...or <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-func docutils literal"><span class="pre">filter()</span></tt></a>, which uses more flexible SQL
expression language constructs. These allow you to use regular Python
operators with the class-level attributes on your mapped class:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>             <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="o">==</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">):</span> 
<span class="o">...</span>    <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)</div><span class="n">ed</span></pre></div>
</div>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object is fully <strong>generative</strong>, meaning
that most method calls return a new <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>
object upon which further criteria may be added. For example, to query for
users named &#8220;ed&#8221; with a full name of &#8220;Ed Jones&#8221;, you can call
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-func docutils literal"><span class="pre">filter()</span></tt></a> twice, which joins criteria using
<tt class="docutils literal"><span class="pre">AND</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">user</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>          <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">&#39;ed&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>          <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="o">==</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">):</span> 
<span class="o">...</span>    <span class="k">print</span> <span class="n">user</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ? AND users.fullname = ?
('ed', 'Ed Jones')</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<div class="section" id="common-filter-operators">
<h3>Common Filter Operators<a class="headerlink" href="#common-filter-operators" title="Permalink to this headline">¶</a></h3>
<p>Here&#8217;s a rundown of some of the most common operators used in
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-func docutils literal"><span class="pre">filter()</span></tt></a>:</p>
<ul>
<li><p class="first"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__eq__" title="sqlalchemy.sql.operators.ColumnOperators.__eq__"><tt class="xref py py-meth docutils literal"><span class="pre">equals</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;ed&#39;</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__ne__" title="sqlalchemy.sql.operators.ColumnOperators.__ne__"><tt class="xref py py-meth docutils literal"><span class="pre">not</span> <span class="pre">equals</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">!=</span> <span class="s">&#39;ed&#39;</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/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-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</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">&#39;</span><span class="si">%e</span><span class="s">d%&#39;</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><tt class="xref py py-meth docutils literal"><span class="pre">IN</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="s">&#39;jack&#39;</span><span class="p">]))</span>

<span class="c"># works with query objects too:</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">(</span>
        <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</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">&#39;</span><span class="si">%e</span><span class="s">d%&#39;</span><span class="p">))</span>
<span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.notin_" title="sqlalchemy.sql.operators.ColumnOperators.notin_"><tt class="xref py py-meth docutils literal"><span class="pre">NOT</span> <span class="pre">IN</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">~</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="s">&#39;jack&#39;</span><span class="p">]))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.is_" title="sqlalchemy.sql.operators.ColumnOperators.is_"><tt class="xref py py-meth docutils literal"><span class="pre">IS</span> <span class="pre">NULL</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="bp">None</span><span class="p">)</span>

<span class="c"># alternatively, if pep8/linters are a concern</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">is_</span><span class="p">(</span><span class="bp">None</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.isnot" title="sqlalchemy.sql.operators.ColumnOperators.isnot"><tt class="xref py py-meth docutils literal"><span class="pre">IS</span> <span class="pre">NOT</span> <span class="pre">NULL</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">!=</span> <span class="bp">None</span><span class="p">)</span>

<span class="c"># alternatively, if pep8/linters are a concern</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">isnot</span><span class="p">(</span><span class="bp">None</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/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>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># use and_()</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">and_</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">fullname</span> <span class="o">==</span> <span class="s">&#39;Ed Jones&#39;</span><span class="p">))</span>

<span class="c"># or send multiple expressions to .filter()</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">fullname</span> <span class="o">==</span> <span class="s">&#39;Ed Jones&#39;</span><span class="p">)</span>

<span class="c"># or chain multiple filter()/filter_by() calls</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;ed&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span> <span class="o">==</span> <span class="s">&#39;Ed Jones&#39;</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/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>:</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">or_</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">or_</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;wendy&#39;</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.match" title="sqlalchemy.sql.operators.ColumnOperators.match"><tt class="xref py py-meth docutils literal"><span class="pre">MATCH</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">match</span><span class="p">(</span><span class="s">&#39;wendy&#39;</span><span class="p">))</span></pre></div>
</div>
</li>
</ul>
<blockquote>
<div><div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last"><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.match" title="sqlalchemy.sql.operators.ColumnOperators.match"><tt class="xref py py-meth docutils literal"><span class="pre">match()</span></tt></a> uses a database-specific <tt class="docutils literal"><span class="pre">MATCH</span></tt>
or <tt class="docutils literal"><span class="pre">CONTAINS</span></tt> function; its behavior will vary by backend and is not
available on some backends such as SQLite.</p>
</div>
</div></blockquote>
</div>
<div class="section" id="returning-lists-and-scalars">
<h3>Returning Lists and Scalars<a class="headerlink" href="#returning-lists-and-scalars" title="Permalink to this headline">¶</a></h3>
<p>A number of methods on <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>
immediately issue SQL and return a value containing loaded
database results.  Here&#8217;s a brief tour:</p>
<ul>
<li><p class="first"><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.all" title="sqlalchemy.orm.query.Query.all"><tt class="xref py py-meth docutils literal"><span class="pre">all()</span></tt></a> returns a list:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</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">&#39;</span><span class="si">%e</span><span class="s">d&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">query</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
('%ed',)</div><span class="p">[</span><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span>
      <span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Fred Flinstone&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;blah&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.first" title="sqlalchemy.orm.query.Query.first"><tt class="xref py py-meth docutils literal"><span class="pre">first()</span></tt></a> applies a limit of one and returns
the first result as a scalar:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">query</span><span class="o">.</span><span class="n">first</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
 LIMIT ? OFFSET ?
('%ed', 1, 0)</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.one" title="sqlalchemy.orm.query.Query.one"><tt class="xref py py-meth docutils literal"><span class="pre">one()</span></tt></a>, fully fetches all rows, and if not
exactly one object identity or composite row is present in the result, raises
an error.  With multiple rows found:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm.exc</span> <span class="kn">import</span> <span class="n">MultipleResultsFound</span>
<span class="o">&gt;&gt;&gt;</span> <span class="k">try</span><span class="p">:</span> 
<span class="o">...</span>     <span class="n">user</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="o">...</span> <span class="k">except</span> <span class="n">MultipleResultsFound</span><span class="p">,</span> <span class="n">e</span><span class="p">:</span>
<span class="o">...</span>     <span class="k">print</span> <span class="n">e</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
('%ed',)</div><span class="n">Multiple</span> <span class="n">rows</span> <span class="n">were</span> <span class="n">found</span> <span class="k">for</span> <span class="n">one</span><span class="p">()</span></pre></div>
</div>
<p>With no rows found:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm.exc</span> <span class="kn">import</span> <span class="n">NoResultFound</span>
<span class="o">&gt;&gt;&gt;</span> <span class="k">try</span><span class="p">:</span> 
<span class="o">...</span>     <span class="n">user</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="mi">99</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="o">...</span> <span class="k">except</span> <span class="n">NoResultFound</span><span class="p">,</span> <span class="n">e</span><span class="p">:</span>
<span class="o">...</span>     <span class="k">print</span> <span class="n">e</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name LIKE ? AND users.id = ? ORDER BY users.id
('%ed', 99)</div><span class="n">No</span> <span class="n">row</span> <span class="n">was</span> <span class="n">found</span> <span class="k">for</span> <span class="n">one</span><span class="p">()</span></pre></div>
</div>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.one" title="sqlalchemy.orm.query.Query.one"><tt class="xref py py-meth docutils literal"><span class="pre">one()</span></tt></a> method is great for systems that expect to handle
&#8220;no items found&#8221; versus &#8220;multiple items found&#8221; differently; such as a RESTful
web service, which may want to raise a &#8220;404 not found&#8221; when no results are found,
but raise an application error when multiple results are found.</p>
</li>
<li><p class="first"><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.scalar" title="sqlalchemy.orm.query.Query.scalar"><tt class="xref py py-meth docutils literal"><span class="pre">scalar()</span></tt></a> invokes the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.one" title="sqlalchemy.orm.query.Query.one"><tt class="xref py py-meth docutils literal"><span class="pre">one()</span></tt></a> method, and upon
success returns the first column of the row:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;ed&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>    <span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">query</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id
FROM users
WHERE users.name LIKE ? ORDER BY users.id
 LIMIT ? OFFSET ?
('%ed', 1, 0)</div><span class="mi">7</span></pre></div>
</div>
</li>
</ul>
</div>
<div class="section" id="using-literal-sql">
<span id="orm-tutorial-literal-sql"></span><h3>Using Literal SQL<a class="headerlink" href="#using-literal-sql" title="Permalink to this headline">¶</a></h3>
<p>Literal strings can be used flexibly with
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>, by specifying their use
with the <a class="reference internal" href="../core/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, which is accepted
by most applicable methods.  For example,
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal"><span class="pre">filter()</span></tt></a> and
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.order_by" title="sqlalchemy.orm.query.Query.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">order_by()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">text</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">user</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>             <span class="nb">filter</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s">&quot;id&lt;224&quot;</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>             <span class="n">order_by</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s">&quot;id&quot;</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">():</span> 
<span class="o">...</span>     <span class="k">print</span> <span class="n">user</span><span class="o">.</span><span class="n">name</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE id<224 ORDER BY id
()</div><span class="n">ed</span>
<span class="n">wendy</span>
<span class="n">mary</span>
<span class="n">fred</span></pre></div>
</div>
<p>Bind parameters can be specified with string-based SQL, using a colon. To
specify the values, use the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.params" title="sqlalchemy.orm.query.Query.params"><tt class="xref py py-meth docutils literal"><span class="pre">params()</span></tt></a>
method:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s">&quot;id&lt;:value and name=:name&quot;</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>     <span class="n">params</span><span class="p">(</span><span class="n">value</span><span class="o">=</span><span class="mi">224</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE id<? and name=? ORDER BY users.id
(224, 'fred')</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Fred Flinstone&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;blah&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>To use an entirely string-based statement, using
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.from_statement" title="sqlalchemy.orm.query.Query.from_statement"><tt class="xref py py-meth docutils literal"><span class="pre">from_statement()</span></tt></a>; just ensure that the
columns clause of the statement contains the column names normally used by the
mapper (below illustrated using an asterisk):</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">from_statement</span><span class="p">(</span>
<span class="o">...</span>                     <span class="n">text</span><span class="p">(</span><span class="s">&quot;SELECT * FROM users where name=:name&quot;</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>                     <span class="n">params</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT * FROM users where name=?
('ed',)</div><span class="p">[</span><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
<p>You can use <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.from_statement" title="sqlalchemy.orm.query.Query.from_statement"><tt class="xref py py-meth docutils literal"><span class="pre">from_statement()</span></tt></a> to go
completely &#8220;raw&#8221;, using string names to identify desired columns:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">&quot;id&quot;</span><span class="p">,</span> <span class="s">&quot;name&quot;</span><span class="p">,</span> <span class="s">&quot;thenumber12&quot;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>         <span class="n">from_statement</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s">&quot;SELECT id, name, 12 as &quot;</span>
<span class="o">...</span>                 <span class="s">&quot;thenumber12 FROM users where name=:name&quot;</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>                 <span class="n">params</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT id, name, 12 as thenumber12 FROM users where name=?
('ed',)</div><span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u&#39;ed&#39;</span><span class="p">,</span> <span class="mi">12</span><span class="p">)]</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="../core/tutorial.html#sqlexpression-text"><em>Using Text</em></a> - Core description of textual segments.  The
behavior of the ORM <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object with regards to
<a class="reference internal" href="../core/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 related constructs is very similar to that of the
Core <a class="reference internal" href="../core/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.</p>
</div>
</div>
<div class="section" id="counting">
<h3>Counting<a class="headerlink" href="#counting" title="Permalink to this headline">¶</a></h3>
<p><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> includes a convenience method for
counting called <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal"><span class="pre">count()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</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">&#39;</span><span class="si">%e</span><span class="s">d&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">count</span><span class="p">()</span> 
<div class='popup_sql'>SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
                users.name AS users_name,
                users.fullname AS users_fullname,
                users.password AS users_password
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)</div><span class="mi">2</span></pre></div>
</div>
<div class="sidebar">
<p class="first sidebar-title">Counting on <tt class="docutils literal"><span class="pre">count()</span></tt></p>
<p class="last"><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal"><span class="pre">Query.count()</span></tt></a> used to be a very complicated method
when it would try to guess whether or not a subquery was needed
around the
existing query, and in some exotic cases it wouldn&#8217;t do the right thing.
Now that it uses a simple subquery every time, it&#8217;s only two lines long
and always returns the right answer.  Use <tt class="docutils literal"><span class="pre">func.count()</span></tt> if a
particular statement absolutely cannot tolerate the subquery being present.</p>
</div>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal"><span class="pre">count()</span></tt></a> method is used to determine
how many rows the SQL statement would return.   Looking
at the generated SQL above, SQLAlchemy always places whatever it is we are
querying into a subquery, then counts the rows from that.   In some cases
this can be reduced to a simpler <tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">count(*)</span> <span class="pre">FROM</span> <span class="pre">table</span></tt>, however
modern versions of SQLAlchemy don&#8217;t try to guess when this is appropriate,
as the exact SQL can be emitted using more explicit means.</p>
<p>For situations where the &#8220;thing to be counted&#8221; needs
to be indicated specifically, we can specify the &#8220;count&#8221; function
directly using the expression <tt class="docutils literal"><span class="pre">func.count()</span></tt>, available from the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-attr docutils literal"><span class="pre">func</span></tt></a> construct.  Below we
use it to return the count of each distinct user name:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">func</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</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">User</span><span class="o">.</span><span class="n">name</span><span class="p">),</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">group_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>  
<div class='popup_sql'>SELECT count(users.name) AS count_1, users.name AS users_name
FROM users GROUP BY users.name
()</div><span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u&#39;ed&#39;</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u&#39;fred&#39;</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u&#39;mary&#39;</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u&#39;wendy&#39;</span><span class="p">)]</span></pre></div>
</div>
<p>To achieve our simple <tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">count(*)</span> <span class="pre">FROM</span> <span class="pre">table</span></tt>, we can apply it as:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</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="s">&#39;*&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span> 
<div class='popup_sql'>SELECT count(?) AS count_1
FROM users
('*',)</div><span class="mi">4</span></pre></div>
</div>
<p>The usage of <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><tt class="xref py py-meth docutils literal"><span class="pre">select_from()</span></tt></a> can be removed if we express the count in terms
of the <tt class="docutils literal"><span class="pre">User</span></tt> primary key directly:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</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">User</span><span class="o">.</span><span class="n">id</span><span class="p">))</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span> 
<div class='popup_sql'>SELECT count(users.id) AS count_1
FROM users
()</div><span class="mi">4</span></pre></div>
</div>
</div>
</div>
<div class="section" id="building-a-relationship">
<span id="orm-tutorial-relationship"></span><h2>Building a Relationship<a class="headerlink" href="#building-a-relationship" title="Permalink to this headline">¶</a></h2>
<p>Let&#8217;s consider how a second table, related to <tt class="docutils literal"><span class="pre">User</span></tt>, can be mapped and
queried.  Users in our system
can store any number of email addresses associated with their username. This
implies a basic one to many association from the <tt class="docutils literal"><span class="pre">users</span></tt> to a new
table which stores email addresses, which we will call <tt class="docutils literal"><span class="pre">addresses</span></tt>. Using
declarative, we define this table along with its mapped class, <tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">ForeignKey</span>
<span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span><span class="p">,</span> <span class="n">backref</span>

<span class="o">&gt;&gt;&gt;</span> <span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="o">...</span>     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;addresses&#39;</span>
<span class="o">...</span>     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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="o">...</span>     <span class="n">email_address</span> <span class="o">=</span> <span class="n">Column</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="o">...</span>     <span class="n">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;users.id&#39;</span><span class="p">))</span>
<span class="o">...</span>
<span class="o">...</span>     <span class="n">user</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;User&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">&#39;addresses&#39;</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="nb">id</span><span class="p">))</span>
<span class="o">...</span>
<span class="o">...</span>     <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="o">...</span>         <span class="k">return</span> <span class="s">&quot;&lt;Address(email_address=&#39;</span><span class="si">%s</span><span class="s">&#39;)&gt;&quot;</span> <span class="o">%</span> <span class="bp">self</span><span class="o">.</span><span class="n">email_address</span></pre></div>
</div>
<p>The above class introduces the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> construct, which is a
directive applied to <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> that indicates that values in this
column should be <a class="reference internal" href="../glossary.html#term-constrained"><em class="xref std std-term">constrained</em></a> to be values present in the named remote
column. This is a core feature of relational databases, and is the &#8220;glue&#8221; that
transforms an otherwise unconnected collection of tables to have rich
overlapping relationships. The <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> above expresses that
values in the <tt class="docutils literal"><span class="pre">addresses.user_id</span></tt> column should be constrained to
those values in the <tt class="docutils literal"><span class="pre">users.id</span></tt> column, i.e. its primary key.</p>
<p>A second directive, known as <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>,
tells the ORM that the <tt class="docutils literal"><span class="pre">Address</span></tt> class itself should be linked
to the <tt class="docutils literal"><span class="pre">User</span></tt> class, using the attribute <tt class="docutils literal"><span class="pre">Address.user</span></tt>.
<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> uses the foreign key
relationships between the two tables to determine the nature of
this linkage, determining that <tt class="docutils literal"><span class="pre">Address.user</span></tt> will be <a class="reference internal" href="../glossary.html#term-many-to-one"><em class="xref std std-term">many to one</em></a>.
A subdirective of <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> called <a class="reference internal" href="relationships.html#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> is
placed inside of <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, providing details about
the relationship as expressed in reverse, that of a collection of <tt class="docutils literal"><span class="pre">Address</span></tt>
objects on <tt class="docutils literal"><span class="pre">User</span></tt> referenced by <tt class="docutils literal"><span class="pre">User.addresses</span></tt>.  The reverse
side of a many-to-one relationship is always <a class="reference internal" href="../glossary.html#term-one-to-many"><em class="xref std std-term">one to many</em></a>.
A full catalog of available <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> configurations
is at <a class="reference internal" href="relationships.html#relationship-patterns"><em>Basic Relational Patterns</em></a>.</p>
<p>The two complementing relationships <tt class="docutils literal"><span class="pre">Address.user</span></tt> and <tt class="docutils literal"><span class="pre">User.addresses</span></tt>
are referred to as a <a class="reference internal" href="../glossary.html#term-bidirectional-relationship"><em class="xref std std-term">bidirectional relationship</em></a>, and is a key
feature of the SQLAlchemy ORM.   The section <a class="reference internal" href="relationships.html#relationships-backref"><em>Linking Relationships with Backref</em></a>
discusses the &#8220;backref&#8221; feature in detail.</p>
<p>Arguments to <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> which concern the remote class
can be specified using strings, assuming the Declarative system is in
use.   Once all mappings are complete, these strings are evaluated
as Python expressions in order to produce the actual argument, in the
above case the <tt class="docutils literal"><span class="pre">User</span></tt> class.   The names which are allowed during
this evaluation include, among other things, the names of all classes
which have been created in terms of the declared base.  Below we illustrate creation
of the same &#8220;addresses/user&#8221; bidirectional relationship in terms of <tt class="docutils literal"><span class="pre">User</span></tt> instead of
<tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    <span class="c"># ....</span>
    <span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Address&quot;</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="s">&quot;Address.id&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;user&quot;</span><span class="p">)</span></pre></div>
</div>
<p>See the docstring for <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> for more detail on argument style.</p>
<div class="topic">
<p class="topic-title first">Did you know ?</p>
<ul class="simple">
<li>a FOREIGN KEY constraint in most (though not all) relational databases can
only link to a primary key column, or a column that has a UNIQUE constraint.</li>
<li>a FOREIGN KEY constraint that refers to a multiple column primary key, and itself
has multiple columns, is known as a &#8220;composite foreign key&#8221;.  It can also
reference a subset of those columns.</li>
<li>FOREIGN KEY columns can automatically update themselves, in response to a change
in the referenced column or row.  This is known as the CASCADE <em>referential action</em>,
and is a built in function of the relational database.</li>
<li>FOREIGN KEY can refer to its own table.  This is referred to as a &#8220;self-referential&#8221;
foreign key.</li>
<li>Read more about foreign keys at <a class="reference external" href="http://en.wikipedia.org/wiki/Foreign_key">Foreign Key - Wikipedia</a>.</li>
</ul>
</div>
<p>We&#8217;ll need to create the <tt class="docutils literal"><span class="pre">addresses</span></tt> table in the database, so we will issue
another CREATE from our metadata, which will skip over tables which have
already been created:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">Base</span><span class="o">.</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 addresses (
    id INTEGER NOT NULL,
    email_address VARCHAR NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (id),
     FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT</div></pre></div>
</div>
</div>
<div class="section" id="working-with-related-objects">
<h2>Working with Related Objects<a class="headerlink" href="#working-with-related-objects" title="Permalink to this headline">¶</a></h2>
<p>Now when we create a <tt class="docutils literal"><span class="pre">User</span></tt>, a blank <tt class="docutils literal"><span class="pre">addresses</span></tt> collection will be
present. Various collection types, such as sets and dictionaries, are possible
here (see <a class="reference internal" href="collections.html#custom-collections"><em>Customizing Collection Access</em></a> for details), but by
default, the collection is a Python list.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span>
<span class="p">[]</span></pre></div>
</div>
<p>We are free to add <tt class="docutils literal"><span class="pre">Address</span></tt> objects on our <tt class="docutils literal"><span class="pre">User</span></tt> object. In this case we
just assign a full list directly:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span> <span class="o">=</span> <span class="p">[</span>
<span class="o">...</span>                 <span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">),</span>
<span class="o">...</span>                 <span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">)]</span></pre></div>
</div>
<p>When using a bidirectional relationship, elements added in one direction
automatically become visible in the other direction.  This behavior occurs
based on attribute on-change events and is evaluated in Python, without
using any SQL:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span>
<span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">)</span><span class="o">&gt;</span>

<span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span><span class="o">.</span><span class="n">user</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>Let&#8217;s add and commit <tt class="docutils literal"><span class="pre">Jack</span> <span class="pre">Bean</span></tt> to the database. <tt class="docutils literal"><span class="pre">jack</span></tt> as well
as the two <tt class="docutils literal"><span class="pre">Address</span></tt> members in the corresponding <tt class="docutils literal"><span class="pre">addresses</span></tt>
collection are both added to the session at once, using a process
known as <strong>cascading</strong>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">jack</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<div class='popup_sql'>INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('j25@yahoo.com', 5)
COMMIT</div></pre></div>
</div>
<p>Querying for Jack, we get just Jack back.  No SQL is yet issued for Jack&#8217;s addresses:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span> 
<div class='popup_sql'>BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?
('jack',)</div><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>Let&#8217;s look at the <tt class="docutils literal"><span class="pre">addresses</span></tt> collection.  Watch the SQL:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span> 
<div class='popup_sql'>SELECT addresses.id AS addresses_id,
        addresses.email_address AS
        addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.id
(5,)</div><span class="p">[</span><span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
<p>When we accessed the <tt class="docutils literal"><span class="pre">addresses</span></tt> collection, SQL was suddenly issued. This
is an example of a <a class="reference internal" href="../glossary.html#term-lazy-loading"><em class="xref std std-term">lazy loading</em></a> relationship.  The <tt class="docutils literal"><span class="pre">addresses</span></tt> collection
is now loaded and behaves just like an ordinary list.  We&#8217;ll cover ways
to optimize the loading of this collection in a bit.</p>
</div>
<div class="section" id="querying-with-joins">
<span id="ormtutorial-joins"></span><h2>Querying with Joins<a class="headerlink" href="#querying-with-joins" title="Permalink to this headline">¶</a></h2>
<p>Now that we have two tables, we can show some more features of <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>,
specifically how to create queries that deal with both tables at the same time.
The <a class="reference external" href="http://en.wikipedia.org/wiki/Join_%28SQL%29">Wikipedia page on SQL JOIN</a> offers a good introduction to
join techniques, several of which we&#8217;ll illustrate here.</p>
<p>To construct a simple implicit join between <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt>,
we can use <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal"><span class="pre">Query.filter()</span></tt></a> to equate their related columns together.
Below we load the <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt> entities at once using this method:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">u</span><span class="p">,</span> <span class="n">a</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                     <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                     <span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                     <span class="nb">all</span><span class="p">():</span>   
<span class="o">...</span>     <span class="k">print</span> <span class="n">u</span>
<span class="o">...</span>     <span class="k">print</span> <span class="n">a</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM users, addresses
WHERE users.id = addresses.user_id
        AND addresses.email_address = ?
('jack@google.com',)</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>The actual SQL JOIN syntax, on the other hand, is most easily achieved
using the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a> method:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>         <span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>         <span class="nb">all</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)</div><span class="p">[</span><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
<p><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a> knows how to join between <tt class="docutils literal"><span class="pre">User</span></tt>
and <tt class="docutils literal"><span class="pre">Address</span></tt> because there&#8217;s only one foreign key between them. If there
were no foreign keys, or several, <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a>
works better when one of the following forms are used:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>    <span class="c"># explicit condition</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span>                       <span class="c"># specify relationship from left to right</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span>              <span class="c"># same, with explicit target</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="s">&#39;addresses&#39;</span><span class="p">)</span>                          <span class="c"># same, using a string</span></pre></div>
</div>
<p>As you would expect, the same idea is used for &#8220;outer&#8221; joins, using the
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.outerjoin" title="sqlalchemy.orm.query.Query.outerjoin"><tt class="xref py py-meth docutils literal"><span class="pre">outerjoin()</span></tt></a> function:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span>   <span class="c"># LEFT OUTER JOIN</span></pre></div>
</div>
<p>The reference documentation for <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> contains detailed information
and examples of the calling styles accepted by this method; <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a>
is an important method at the center of usage for any SQL-fluent application.</p>
<div class="section" id="using-aliases">
<span id="ormtutorial-aliases"></span><h3>Using Aliases<a class="headerlink" href="#using-aliases" title="Permalink to this headline">¶</a></h3>
<p>When querying across multiple tables, if the same table needs to be referenced
more than once, SQL typically requires that the table be <em>aliased</em> with
another name, so that it can be distinguished against other occurrences of
that table. The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> supports this most
explicitly using the <a class="reference internal" href="query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><tt class="xref py py-attr docutils literal"><span class="pre">aliased</span></tt></a> construct. Below we join to the <tt class="docutils literal"><span class="pre">Address</span></tt>
entity twice, to locate a user who has two distinct email addresses at the
same time:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">aliased</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">adalias1</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">adalias2</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">username</span><span class="p">,</span> <span class="n">email1</span><span class="p">,</span> <span class="n">email2</span> <span class="ow">in</span> \
<span class="o">...</span>     <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">adalias1</span><span class="o">.</span><span class="n">email_address</span><span class="p">,</span> <span class="n">adalias2</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>     <span class="n">join</span><span class="p">(</span><span class="n">adalias1</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>     <span class="n">join</span><span class="p">(</span><span class="n">adalias2</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>     <span class="nb">filter</span><span class="p">(</span><span class="n">adalias1</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>     <span class="nb">filter</span><span class="p">(</span><span class="n">adalias2</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">):</span>
<span class="o">...</span>     <span class="k">print</span> <span class="n">username</span><span class="p">,</span> <span class="n">email1</span><span class="p">,</span> <span class="n">email2</span>      
<div class='popup_sql'>SELECT users.name AS users_name,
        addresses_1.email_address AS addresses_1_email_address,
        addresses_2.email_address AS addresses_2_email_address
FROM users JOIN addresses AS addresses_1
        ON users.id = addresses_1.user_id
JOIN addresses AS addresses_2
        ON users.id = addresses_2.user_id
WHERE addresses_1.email_address = ?
        AND addresses_2.email_address = ?
('jack@google.com', 'j25@yahoo.com')</div><span class="n">jack</span> <span class="n">jack</span><span class="nd">@google.com</span> <span class="n">j25</span><span class="nd">@yahoo.com</span></pre></div>
</div>
</div>
<div class="section" id="using-subqueries">
<h3>Using Subqueries<a class="headerlink" href="#using-subqueries" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> is suitable for generating statements
which can be used as subqueries. Suppose we wanted to load <tt class="docutils literal"><span class="pre">User</span></tt> objects
along with a count of how many <tt class="docutils literal"><span class="pre">Address</span></tt> records each user has. The best way
to generate SQL like this is to get the count of addresses grouped by user
ids, and JOIN to the parent. In this case we use a LEFT OUTER JOIN so that we
get rows back for those users who don&#8217;t have any addresses, e.g.:</p>
<div class="highlight-python"><pre>SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
    (SELECT user_id, count(*) AS address_count
        FROM addresses GROUP BY user_id) AS adr_count
    ON users.id=adr_count.user_id</pre>
</div>
<p>Using the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>, we build a statement like this
from the inside out. The <tt class="docutils literal"><span class="pre">statement</span></tt> accessor returns a SQL expression
representing the statement generated by a particular
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> - this is an instance of a <a class="reference internal" href="../core/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, which are described in <a class="reference internal" href="../core/tutorial.html"><em>SQL Expression Language Tutorial</em></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </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">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</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="s">&#39;*&#39;</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">&#39;address_count&#39;</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">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">subquery</span><span class="p">()</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">func</span></tt> keyword generates SQL functions, and the <tt class="docutils literal"><span class="pre">subquery()</span></tt> method on
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> produces a SQL expression construct
representing a SELECT statement embedded within an alias (it&#8217;s actually
shorthand for <tt class="docutils literal"><span class="pre">query.statement.alias()</span></tt>).</p>
<p>Once we have our statement, it behaves like a
<a class="reference internal" href="../core/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, such as the one we created for
<tt class="docutils literal"><span class="pre">users</span></tt> at the start of this tutorial. The columns on the statement are
accessible through an attribute called <tt class="docutils literal"><span class="pre">c</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">u</span><span class="p">,</span> <span class="n">count</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">address_count</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>     <span class="n">outerjoin</span><span class="p">(</span><span class="n">stmt</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">stmt</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="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">):</span> 
<span class="o">...</span>     <span class="k">print</span> <span class="n">u</span><span class="p">,</span> <span class="n">count</span>
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password,
        anon_1.address_count AS anon_1_address_count
FROM users LEFT OUTER JOIN
    (SELECT addresses.user_id AS user_id, count(?) AS address_count
    FROM addresses GROUP BY addresses.user_id) AS anon_1
    ON users.id = anon_1.user_id
ORDER BY users.id
('*',)</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Ed Jones&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;f8s7ccs&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="bp">None</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="bp">None</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;mary&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Mary Contrary&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;xxg527&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="bp">None</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;fred&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Fred Flinstone&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;blah&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="bp">None</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span> <span class="mi">2</span></pre></div>
</div>
</div>
<div class="section" id="selecting-entities-from-subqueries">
<h3>Selecting Entities from Subqueries<a class="headerlink" href="#selecting-entities-from-subqueries" title="Permalink to this headline">¶</a></h3>
<p>Above, we just selected a result that included a column from a subquery. What
if we wanted our subquery to map to an entity ? For this we use <tt class="docutils literal"><span class="pre">aliased()</span></tt>
to associate an &#8220;alias&#8221; of a mapped class to a subquery:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                 <span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span> <span class="o">!=</span> <span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                 <span class="n">subquery</span><span class="p">()</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">adalias</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">stmt</span><span class="p">)</span>
<span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">user</span><span class="p">,</span> <span class="n">address</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">adalias</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>         <span class="n">join</span><span class="p">(</span><span class="n">adalias</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">):</span> 
<span class="o">...</span>     <span class="k">print</span> <span class="n">user</span>
<span class="o">...</span>     <span class="k">print</span> <span class="n">address</span>
<div class='popup_sql'>SELECT users.id AS users_id,
            users.name AS users_name,
            users.fullname AS users_fullname,
            users.password AS users_password,
            anon_1.id AS anon_1_id,
            anon_1.email_address AS anon_1_email_address,
            anon_1.user_id AS anon_1_user_id
FROM users JOIN
    (SELECT addresses.id AS id,
            addresses.email_address AS email_address,
            addresses.user_id AS user_id
    FROM addresses
    WHERE addresses.email_address != ?) AS anon_1
    ON users.id = anon_1.user_id
('j25@yahoo.com',)</div><span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
</div>
<div class="section" id="using-exists">
<h3>Using EXISTS<a class="headerlink" href="#using-exists" title="Permalink to this headline">¶</a></h3>
<p>The EXISTS keyword in SQL is a boolean operator which returns True if the
given expression contains any rows. It may be used in many scenarios in place
of joins, and is also useful for locating rows which do not have a
corresponding row in a related table.</p>
<p>There is an explicit EXISTS construct, which looks like this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">exists</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">exists</span><span class="p">()</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">stmt</span><span class="p">):</span>   
<span class="o">...</span>     <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT *
FROM addresses
WHERE addresses.user_id = users.id)
()</div><span class="n">jack</span></pre></div>
</div>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> features several operators which make
usage of EXISTS automatically. Above, the statement can be expressed along the
<tt class="docutils literal"><span class="pre">User.addresses</span></tt> relationship using <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>         <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">()):</span>   
<span class="o">...</span>     <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id)
()</div><span class="n">jack</span></pre></div>
</div>
<p><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a> takes criterion as well, to limit the rows matched:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>     <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Address</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">&#39;</span><span class="si">%g</span><span class="s">oogle%&#39;</span><span class="p">))):</span>   
<span class="o">...</span>     <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id AND addresses.email_address LIKE ?)
('%google%',)</div><span class="n">jack</span></pre></div>
</div>
<p><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.has" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.has"><tt class="xref py py-meth docutils literal"><span class="pre">has()</span></tt></a> is the same operator as
<a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a> for many-to-one relationships
(note the <tt class="docutils literal"><span class="pre">~</span></tt> operator here too, which means &#8220;NOT&#8221;):</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>         <span class="nb">filter</span><span class="p">(</span><span class="o">~</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">&#39;jack&#39;</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> 
<div class='popup_sql'>SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE NOT (EXISTS (SELECT 1
FROM users
WHERE users.id = addresses.user_id AND users.name = ?))
('jack',)</div><span class="p">[]</span></pre></div>
</div>
</div>
<div class="section" id="common-relationship-operators">
<h3>Common Relationship Operators<a class="headerlink" href="#common-relationship-operators" title="Permalink to this headline">¶</a></h3>
<p>Here&#8217;s all the operators which build on relationships - each one
is linked to its API documentation which includes full details on usage
and behavior:</p>
<ul>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__"><tt class="xref py py-meth docutils literal"><span class="pre">__eq__()</span></tt></a> (many-to-one &#8220;equals&#8221; comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">==</span> <span class="n">someuser</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__ne__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__ne__"><tt class="xref py py-meth docutils literal"><span class="pre">__ne__()</span></tt></a> (many-to-one &#8220;not equals&#8221; comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">!=</span> <span class="n">someuser</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">IS NULL (many-to-one comparison, also uses <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__"><tt class="xref py py-meth docutils literal"><span class="pre">__eq__()</span></tt></a>):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">==</span> <span class="bp">None</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains"><tt class="xref py py-meth docutils literal"><span class="pre">contains()</span></tt></a> (used for one-to-many collections):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="n">someaddress</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a> (used for collections):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;bar&#39;</span><span class="p">))</span>

<span class="c"># also takes keyword arguments:</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;bar&#39;</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.has" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.has"><tt class="xref py py-meth docutils literal"><span class="pre">has()</span></tt></a> (used for scalar references):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_parent" title="sqlalchemy.orm.query.Query.with_parent"><tt class="xref py py-meth docutils literal"><span class="pre">Query.with_parent()</span></tt></a> (used for any relationship):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">with_parent</span><span class="p">(</span><span class="n">someuser</span><span class="p">,</span> <span class="s">&#39;addresses&#39;</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
</div>
</div>
<div class="section" id="eager-loading">
<h2>Eager Loading<a class="headerlink" href="#eager-loading" title="Permalink to this headline">¶</a></h2>
<p>Recall earlier that we illustrated a <a class="reference internal" href="../glossary.html#term-lazy-loading"><em class="xref std std-term">lazy loading</em></a> operation, when
we accessed the <tt class="docutils literal"><span class="pre">User.addresses</span></tt> collection of a <tt class="docutils literal"><span class="pre">User</span></tt> and SQL
was emitted.  If you want to reduce the number of queries (dramatically, in many cases),
we can apply an <em class="xref std std-term">eager load</em> to the query operation.   SQLAlchemy
offers three types of eager loading, two of which are automatic, and a third
which involves custom criterion.   All three are usually invoked via functions known
as <em class="xref std std-term">query options</em> which give additional instructions to the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> on how
we would like various attributes to be loaded, via the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.options" title="sqlalchemy.orm.query.Query.options"><tt class="xref py py-meth docutils literal"><span class="pre">Query.options()</span></tt></a> method.</p>
<div class="section" id="subquery-load">
<h3>Subquery Load<a class="headerlink" href="#subquery-load" title="Permalink to this headline">¶</a></h3>
<p>In this case we&#8217;d like to indicate that <tt class="docutils literal"><span class="pre">User.addresses</span></tt> should load eagerly.
A good choice for loading a set of objects as well as their related collections
is the <a class="reference internal" href="loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">orm.subqueryload()</span></tt></a> option, which emits a second SELECT statement
that fully loads the collections associated with the results just loaded.
The name &#8220;subquery&#8221; originates from the fact that the SELECT statement
constructed directly via the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> is re-used, embedded as a subquery
into a SELECT against the related table.   This is a little elaborate but
very easy to use:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">subqueryload</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                 <span class="n">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>                 <span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?
('jack',)
SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id,
        anon_1.users_id AS anon_1_users_id
FROM (SELECT users.id AS users_id
    FROM users WHERE users.name = ?) AS anon_1
JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id, addresses.id
('jack',)</div><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span>

<span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span>
<span class="p">[</span><span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
</div>
<div class="section" id="joined-load">
<h3>Joined Load<a class="headerlink" href="#joined-load" title="Permalink to this headline">¶</a></h3>
<p>The other automatic eager loading function is more well known and is called
<a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">orm.joinedload()</span></tt></a>.   This style of loading emits a JOIN, by default
a LEFT OUTER JOIN, so that the lead object as well as the related object
or collection is loaded in one step.   We illustrate loading the same
<tt class="docutils literal"><span class="pre">addresses</span></tt> collection in this way - note that even though the <tt class="docutils literal"><span class="pre">User.addresses</span></tt>
collection on <tt class="docutils literal"><span class="pre">jack</span></tt> is actually populated right now, the query
will emit the extra join regardless:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">joinedload</span>

<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                        <span class="n">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>                        <span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password,
        addresses_1.id AS addresses_1_id,
        addresses_1.email_address AS addresses_1_email_address,
        addresses_1.user_id AS addresses_1_user_id
FROM users
    LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? ORDER BY addresses_1.id
('jack',)</div><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span>

<span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span>
<span class="p">[</span><span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span></pre></div>
</div>
<p>Note that even though the OUTER JOIN resulted in two rows, we still only got
one instance of <tt class="docutils literal"><span class="pre">User</span></tt> back.  This is because <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> applies a &#8220;uniquing&#8221;
strategy, based on object identity, to the returned entities.  This is specifically
so that joined eager loading can be applied without affecting the query results.</p>
<p>While <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> has been around for a long time, <a class="reference internal" href="loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a>
is a newer form of eager loading.   <a class="reference internal" href="loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a> tends to be more appropriate
for loading related collections while <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> tends to be better suited
for many-to-one relationships, due to the fact that only one row is loaded
for both the lead and the related object.</p>
<div class="topic">
<p class="topic-title first"><tt class="docutils literal"><span class="pre">joinedload()</span></tt> is not a replacement for <tt class="docutils literal"><span class="pre">join()</span></tt></p>
<p>The join created by <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> is anonymously aliased such that
it <strong>does not affect the query results</strong>.   An <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.order_by" title="sqlalchemy.orm.query.Query.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">Query.order_by()</span></tt></a>
or <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal"><span class="pre">Query.filter()</span></tt></a> call <strong>cannot</strong> reference these aliased
tables - so-called &#8220;user space&#8221; joins are constructed using
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a>.   The rationale for this is that <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> is only
applied in order to affect how related objects or collections are loaded
as an optimizing detail - it can be added or removed with no impact
on actual results.   See the section <a class="reference internal" href="loading.html#zen-of-eager-loading"><em>The Zen of Eager Loading</em></a> for
a detailed description of how this is used.</p>
</div>
</div>
<div class="section" id="explicit-join-eagerload">
<h3>Explicit Join + Eagerload<a class="headerlink" href="#explicit-join-eagerload" title="Permalink to this headline">¶</a></h3>
<p>A third style of eager loading is when we are constructing a JOIN explicitly in
order to locate the primary rows, and would like to additionally apply the extra
table to a related object or collection on the primary object.   This feature
is supplied via the <a class="reference internal" href="loading.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">orm.contains_eager()</span></tt></a> function, and is most
typically useful for pre-loading the many-to-one object on a query that needs
to filter on that same object.  Below we illustrate loading an <tt class="docutils literal"><span class="pre">Address</span></tt>
row as well as the related <tt class="docutils literal"><span class="pre">User</span></tt> object, filtering on the <tt class="docutils literal"><span class="pre">User</span></tt> named
&#8220;jack&#8221; and using <a class="reference internal" href="loading.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">orm.contains_eager()</span></tt></a> to apply the &#8220;user&#8221; columns to the <tt class="docutils literal"><span class="pre">Address.user</span></tt>
attribute:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">contains_eager</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">jacks_addresses</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                             <span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                             <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                             <span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>                             <span class="nb">all</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses JOIN users ON users.id = addresses.user_id
WHERE users.name = ?
('jack',)</div><span class="o">&gt;&gt;&gt;</span> <span class="n">jacks_addresses</span>
<span class="p">[</span><span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">]</span>

<span class="o">&gt;&gt;&gt;</span> <span class="n">jacks_addresses</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span><span class="o">.</span><span class="n">user</span>
<span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Bean&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;gjffdd&#39;</span><span class="p">)</span><span class="o">&gt;</span></pre></div>
</div>
<p>For more information on eager loading, including how to configure various forms
of loading by default, see the section <a class="reference internal" href="loading.html"><em>Relationship Loading Techniques</em></a>.</p>
</div>
</div>
<div class="section" id="deleting">
<h2>Deleting<a class="headerlink" href="#deleting" title="Permalink to this headline">¶</a></h2>
<p>Let&#8217;s try to delete <tt class="docutils literal"><span class="pre">jack</span></tt> and see how that goes. We&#8217;ll mark as deleted in
the session, then we&#8217;ll issue a <tt class="docutils literal"><span class="pre">count</span></tt> query to see that no rows remain:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">jack</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span> 
<div class='popup_sql'>UPDATE addresses SET user_id=? WHERE addresses.id = ?
(None, 1)
UPDATE addresses SET user_id=? WHERE addresses.id = ?
(None, 2)
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?) AS anon_1
('jack',)</div><span class="mi">0</span></pre></div>
</div>
<p>So far, so good.  How about Jack&#8217;s <tt class="docutils literal"><span class="pre">Address</span></tt> objects ?</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="o">...</span>     <span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">,</span> <span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">])</span>
<span class="o">...</span>  <span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span> 
<div class='popup_sql'>SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">2</span></pre></div>
</div>
<p>Uh oh, they&#8217;re still there ! Analyzing the flush SQL, we can see that the
<tt class="docutils literal"><span class="pre">user_id</span></tt> column of each address was set to NULL, but the rows weren&#8217;t
deleted. SQLAlchemy doesn&#8217;t assume that deletes cascade, you have to tell it
to do so.</p>
<div class="section" id="configuring-delete-delete-orphan-cascade">
<span id="tutorial-delete-cascade"></span><h3>Configuring delete/delete-orphan Cascade<a class="headerlink" href="#configuring-delete-delete-orphan-cascade" title="Permalink to this headline">¶</a></h3>
<p>We will configure <strong>cascade</strong> options on the <tt class="docutils literal"><span class="pre">User.addresses</span></tt> relationship
to change the behavior. While SQLAlchemy allows you to add new attributes and
relationships to mappings at any point in time, in this case the existing
relationship needs to be removed, so we need to tear down the mappings
completely and start again - we&#8217;ll close the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>and use a new <a class="reference internal" href="extensions/declarative.html#sqlalchemy.ext.declarative.declarative_base" title="sqlalchemy.ext.declarative.declarative_base"><tt class="xref py py-func docutils literal"><span class="pre">declarative_base()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span></pre></div>
</div>
<p>Next we&#8217;ll declare the <tt class="docutils literal"><span class="pre">User</span></tt> class, adding in the <tt class="docutils literal"><span class="pre">addresses</span></tt> relationship
including the cascade configuration (we&#8217;ll leave the constructor out too):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span>    <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;users&#39;</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">... </span>    <span class="n">fullname</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">... </span>    <span class="n">password</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Address&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&#39;user&#39;</span><span class="p">,</span>
<span class="gp">... </span>                    <span class="n">cascade</span><span class="o">=</span><span class="s">&quot;all, delete, delete-orphan&quot;</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="gp">... </span>       <span class="k">return</span> <span class="s">&quot;&lt;User(name=&#39;</span><span class="si">%s</span><span class="s">&#39;, fullname=&#39;</span><span class="si">%s</span><span class="s">&#39;, password&#39;</span><span class="si">%s</span><span class="s">&#39;)&gt;&quot;</span> <span class="o">%</span> <span class="p">(</span>
<span class="gp">... </span>                               <span class="bp">self</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span><span class="p">)</span></pre></div>
</div>
<p>Then we recreate <tt class="docutils literal"><span class="pre">Address</span></tt>, noting that in this case we&#8217;ve created
the <tt class="docutils literal"><span class="pre">Address.user</span></tt> relationship via the <tt class="docutils literal"><span class="pre">User</span></tt> class already:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span>    <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;addresses&#39;</span>
<span class="gp">... </span>    <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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">email_address</span> <span class="o">=</span> <span class="n">Column</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="n">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;users.id&#39;</span><span class="p">))</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="gp">... </span>        <span class="k">return</span> <span class="s">&quot;&lt;Address(email_address=&#39;</span><span class="si">%s</span><span class="s">&#39;)&gt;&quot;</span> <span class="o">%</span> <span class="bp">self</span><span class="o">.</span><span class="n">email_address</span></pre></div>
</div>
<p>Now when we load the user <tt class="docutils literal"><span class="pre">jack</span></tt> (below using <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.get" title="sqlalchemy.orm.query.Query.get"><tt class="xref py py-meth docutils literal"><span class="pre">get()</span></tt></a>,
which loads by primary key), removing an address from the
corresponding <tt class="docutils literal"><span class="pre">addresses</span></tt> collection will result in that <tt class="docutils literal"><span class="pre">Address</span></tt>
being deleted:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># load Jack by primary key</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">5</span><span class="p">)</span>    
<div class='popup_sql'>BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.id = ?
(5,)</div>
<span class="c"># remove one Address (lazy load fires off)</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="k">del</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> 
<div class='popup_sql'>SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id
(5,)</div>
<span class="c"># only one address remains</span>
<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="o">...</span>     <span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">,</span> <span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">])</span>
<span class="o">...</span> <span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span> 
<div class='popup_sql'>DELETE FROM addresses WHERE addresses.id = ?
(2,)
SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">1</span></pre></div>
</div>
<p>Deleting Jack will delete both Jack and the remaining <tt class="docutils literal"><span class="pre">Address</span></tt> associated
with the user:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">jack</span><span class="p">)</span>

<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span> 
<div class='popup_sql'>DELETE FROM addresses WHERE addresses.id = ?
(1,)
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
                users.name AS users_name,
                users.fullname AS users_fullname,
                users.password AS users_password
FROM users
WHERE users.name = ?) AS anon_1
('jack',)</div><span class="mi">0</span>

<a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="o">...</span>    <span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">&#39;jack@google.com&#39;</span><span class="p">,</span> <span class="s">&#39;j25@yahoo.com&#39;</span><span class="p">])</span>
<span class="o">...</span> <span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span> 
<div class='popup_sql'>SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">0</span></pre></div>
</div>
<div class="topic">
<p class="topic-title first">More on Cascades</p>
<p>Further detail on configuration of cascades is at <a class="reference internal" href="session.html#unitofwork-cascades"><em>Cascades</em></a>.
The cascade functionality can also integrate smoothly with
the <tt class="docutils literal"><span class="pre">ON</span> <span class="pre">DELETE</span> <span class="pre">CASCADE</span></tt> functionality of the relational database.
See <a class="reference internal" href="collections.html#passive-deletes"><em>Using Passive Deletes</em></a> for details.</p>
</div>
</div>
</div>
<div class="section" id="building-a-many-to-many-relationship">
<span id="orm-tutorial-many-to-many"></span><h2>Building a Many To Many Relationship<a class="headerlink" href="#building-a-many-to-many-relationship" title="Permalink to this headline">¶</a></h2>
<p>We&#8217;re moving into the bonus round here, but lets show off a many-to-many
relationship. We&#8217;ll sneak in some other features too, just to take a tour.
We&#8217;ll make our application a blog application, where users can write
<tt class="docutils literal"><span class="pre">BlogPost</span></tt> items, which have <tt class="docutils literal"><span class="pre">Keyword</span></tt> items associated with them.</p>
<p>For a plain many-to-many, we need to create an un-mapped <a class="reference internal" href="../core/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
to serve as the association table.  This looks like the following:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </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">Text</span>
<span class="gp">&gt;&gt;&gt; </span><span class="c"># association table</span>
<span class="gp">&gt;&gt;&gt; </span><span class="n">post_keywords</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&#39;post_keywords&#39;</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</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">&#39;post_id&#39;</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;posts.id&#39;</span><span class="p">)),</span>
<span class="gp">... </span>    <span class="n">Column</span><span class="p">(</span><span class="s">&#39;keyword_id&#39;</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;keywords.id&#39;</span><span class="p">))</span>
<span class="gp">... </span><span class="p">)</span></pre></div>
</div>
<p>Above, we can see declaring a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> directly is a little different
than declaring a mapped class.  <a class="reference internal" href="../core/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 a constructor function, so
each individual <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> argument is separated by a comma.  The
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> object is also given its name explicitly, rather than it being
taken from an assigned attribute name.</p>
<p>Next we define <tt class="docutils literal"><span class="pre">BlogPost</span></tt> and <tt class="docutils literal"><span class="pre">Keyword</span></tt>, with a <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> linked
via the <tt class="docutils literal"><span class="pre">post_keywords</span></tt> table:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">class</span> <span class="nc">BlogPost</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span>    <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;posts&#39;</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;users.id&#39;</span><span class="p">))</span>
<span class="gp">... </span>    <span class="n">headline</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">255</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="n">body</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Text</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="c"># many to many BlogPost&lt;-&gt;Keyword</span>
<span class="gp">... </span>    <span class="n">keywords</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&#39;Keyword&#39;</span><span class="p">,</span> <span class="n">secondary</span><span class="o">=</span><span class="n">post_keywords</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&#39;posts&#39;</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">headline</span><span class="p">,</span> <span class="n">body</span><span class="p">,</span> <span class="n">author</span><span class="p">):</span>
<span class="gp">... </span>        <span class="bp">self</span><span class="o">.</span><span class="n">author</span> <span class="o">=</span> <span class="n">author</span>
<span class="gp">... </span>        <span class="bp">self</span><span class="o">.</span><span class="n">headline</span> <span class="o">=</span> <span class="n">headline</span>
<span class="gp">... </span>        <span class="bp">self</span><span class="o">.</span><span class="n">body</span> <span class="o">=</span> <span class="n">body</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="gp">... </span>        <span class="k">return</span> <span class="s">&quot;BlogPost(</span><span class="si">%r</span><span class="s">, </span><span class="si">%r</span><span class="s">, </span><span class="si">%r</span><span class="s">)&quot;</span> <span class="o">%</span> <span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">headline</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">body</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">author</span><span class="p">)</span>


<span class="gp">&gt;&gt;&gt; </span><span class="k">class</span> <span class="nc">Keyword</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span>    <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;keywords&#39;</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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">keyword</span> <span class="o">=</span> <span class="n">Column</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">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">unique</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span>    <span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">keyword</span><span class="p">):</span>
<span class="gp">... </span>        <span class="bp">self</span><span class="o">.</span><span class="n">keyword</span> <span class="o">=</span> <span class="n">keyword</span></pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The above class declarations illustrate explicit <tt class="docutils literal"><span class="pre">__init__()</span></tt> methods.
Remember, when using Declarative, it&#8217;s optional!</p>
</div>
<p>Above, the many-to-many relationship is <tt class="docutils literal"><span class="pre">BlogPost.keywords</span></tt>. The defining
feature of a many-to-many relationship is the <tt class="docutils literal"><span class="pre">secondary</span></tt> keyword argument
which references a <a class="reference internal" href="../core/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 representing the
association table. This table only contains columns which reference the two
sides of the relationship; if it has <em>any</em> other columns, such as its own
primary key, or foreign keys to other tables, SQLAlchemy requires a different
usage pattern called the &#8220;association object&#8221;, described at
<a class="reference internal" href="relationships.html#association-pattern"><em>Association Object</em></a>.</p>
<p>We would also like our <tt class="docutils literal"><span class="pre">BlogPost</span></tt> class to have an <tt class="docutils literal"><span class="pre">author</span></tt> field. We will
add this as another bidirectional relationship, except one issue we&#8217;ll have is
that a single user might have lots of blog posts. When we access
<tt class="docutils literal"><span class="pre">User.posts</span></tt>, we&#8217;d like to be able to filter results further so as not to
load the entire collection. For this we use a setting accepted by
<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> called <tt class="docutils literal"><span class="pre">lazy='dynamic'</span></tt>, which
configures an alternate <strong>loader strategy</strong> on the attribute. To use it on the
&#8220;reverse&#8221; side of a <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, we use the
<a class="reference internal" href="relationships.html#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> function:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">backref</span>
<span class="o">&gt;&gt;&gt;</span> <span class="c"># &quot;dynamic&quot; loading relationship to User</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">BlogPost</span><span class="o">.</span><span class="n">author</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">&#39;posts&#39;</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">&#39;dynamic&#39;</span><span class="p">))</span></pre></div>
</div>
<p>Create new tables:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">Base</span><span class="o">.</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")
()
PRAGMA table_info("posts")
()
PRAGMA table_info("keywords")
()
PRAGMA table_info("post_keywords")
()
CREATE TABLE posts (
    id INTEGER NOT NULL,
    user_id INTEGER,
    headline VARCHAR(255) NOT NULL,
    body TEXT,
    PRIMARY KEY (id),
     FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT
CREATE TABLE keywords (
    id INTEGER NOT NULL,
    keyword VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
     UNIQUE (keyword)
)
()
COMMIT
CREATE TABLE post_keywords (
    post_id INTEGER,
    keyword_id INTEGER,
     FOREIGN KEY(post_id) REFERENCES posts (id),
     FOREIGN KEY(keyword_id) REFERENCES keywords (id)
)
()
COMMIT</div></pre></div>
</div>
<p>Usage is not too different from what we&#8217;ve been doing.  Let&#8217;s give Wendy some blog posts:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">wendy</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                 <span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>                 <span class="n">one</span><span class="p">()</span> 
<div class='popup_sql'>SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.password AS users_password
FROM users
WHERE users.name = ?
('wendy',)</div><span class="o">&gt;&gt;&gt;</span> <span class="n">post</span> <span class="o">=</span> <span class="n">BlogPost</span><span class="p">(</span><span class="s">&quot;Wendy&#39;s Blog Post&quot;</span><span class="p">,</span> <span class="s">&quot;This is a test&quot;</span><span class="p">,</span> <span class="n">wendy</span><span class="p">)</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">post</span><span class="p">)</span></pre></div>
</div>
<p>We&#8217;re storing keywords uniquely in the database, but we know that we don&#8217;t
have any yet, so we can just create them:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">&gt;&gt;&gt;</span> <span class="n">post</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Keyword</span><span class="p">(</span><span class="s">&#39;wendy&#39;</span><span class="p">))</span>
<span class="o">&gt;&gt;&gt;</span> <span class="n">post</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Keyword</span><span class="p">(</span><span class="s">&#39;firstpost&#39;</span><span class="p">))</span></pre></div>
</div>
<p>We can now look up all blog posts with the keyword &#8216;firstpost&#8217;. We&#8217;ll use the
<tt class="docutils literal"><span class="pre">any</span></tt> operator to locate &#8220;blog posts where any of its keywords has the
keyword string &#8216;firstpost&#8217;&#8221;:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">BlogPost</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>             <span class="nb">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="s">&#39;firstpost&#39;</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>             <span class="nb">all</span><span class="p">()</span> 
<div class='popup_sql'>INSERT INTO keywords (keyword) VALUES (?)
('wendy',)
INSERT INTO keywords (keyword) VALUES (?)
('firstpost',)
INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)
(2, "Wendy's Blog Post", 'This is a test')
INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)
((1, 1), (1, 2))
SELECT posts.id AS posts_id,
        posts.user_id AS posts_user_id,
        posts.headline AS posts_headline,
        posts.body AS posts_body
FROM posts
WHERE EXISTS (SELECT 1
    FROM post_keywords, keywords
    WHERE posts.id = post_keywords.post_id
        AND keywords.id = post_keywords.keyword_id
        AND keywords.keyword = ?)
('firstpost',)</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">&quot;Wendy&#39;s Blog Post&quot;</span><span class="p">,</span> <span class="s">&#39;This is a test&#39;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">)]</span></pre></div>
</div>
<p>If we want to look up posts owned by the user <tt class="docutils literal"><span class="pre">wendy</span></tt>, we can tell
the query to narrow down to that <tt class="docutils literal"><span class="pre">User</span></tt> object as a parent:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">BlogPost</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>             <span class="nb">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">author</span><span class="o">==</span><span class="n">wendy</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span>             <span class="nb">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="s">&#39;firstpost&#39;</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>             <span class="nb">all</span><span class="p">()</span> 
<div class='popup_sql'>SELECT posts.id AS posts_id,
        posts.user_id AS posts_user_id,
        posts.headline AS posts_headline,
        posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
    FROM post_keywords, keywords
    WHERE posts.id = post_keywords.post_id
        AND keywords.id = post_keywords.keyword_id
        AND keywords.keyword = ?))
(2, 'firstpost')</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">&quot;Wendy&#39;s Blog Post&quot;</span><span class="p">,</span> <span class="s">&#39;This is a test&#39;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">)]</span></pre></div>
</div>
<p>Or we can use Wendy&#8217;s own <tt class="docutils literal"><span class="pre">posts</span></tt> relationship, which is a &#8220;dynamic&#8221;
relationship, to query straight from there:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">&gt;&gt;&gt;</span> <span class="n">wendy</span><span class="o">.</span><span class="n">posts</span><span class="o">.</span>\
<span class="o">...</span>         <span class="nb">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="s">&#39;firstpost&#39;</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span>         <span class="nb">all</span><span class="p">()</span> 
<div class='popup_sql'>SELECT posts.id AS posts_id,
        posts.user_id AS posts_user_id,
        posts.headline AS posts_headline,
        posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
    FROM post_keywords, keywords
    WHERE posts.id = post_keywords.post_id
        AND keywords.id = post_keywords.keyword_id
        AND keywords.keyword = ?))
(2, 'firstpost')</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">&quot;Wendy&#39;s Blog Post&quot;</span><span class="p">,</span> <span class="s">&#39;This is a test&#39;</span><span class="p">,</span> <span class="o">&lt;</span><span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">&#39;foobar&#39;</span><span class="p">)</span><span class="o">&gt;</span><span class="p">)]</span></pre></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>Query Reference: <a class="reference internal" href="query.html"><em>Querying</em></a></p>
<p>Mapper Reference: <a class="reference internal" href="mapper_config.html"><em>Mapper Configuration</em></a></p>
<p>Relationship Reference: <a class="reference internal" href="relationships.html"><em>Relationship Configuration</em></a></p>
<p>Session Reference: <a class="reference internal" href="session.html"><em>Using the Session</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 ORM</a>
        Next:
        <a href="mapper_config.html" title="next chapter">Mapper Configuration</a>

    <div id="docs-copyright">
        &copy; <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>