<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Object Relational Tutorial
— SQLAlchemy 0.6.8 Documentation</title>
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.6.8',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/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.6.8 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" />
</head>
<body>
<h1>SQLAlchemy 0.6.8 Documentation</h1>
<div id="search">
Search:
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="18" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
<div class="versionheader">
Version: <span class="versionnum">0.6.8</span> Last Updated: 06/05/2011 13:10:26
</div>
<div class="clearboth"></div>
<div id="topnav">
<div id="pagecontrol">
<ul>
<li>Prev:
<a href="index.html" title="previous chapter">SQLAlchemy ORM</a>
</li>
<li>Next:
<a href="mapper_config.html" title="next chapter">Mapper Configuration</a>
</li>
<li>
<a href="../contents.html">Table of Contents</a> |
<a href="../genindex.html">Index</a>
| <a href="../_sources/orm/tutorial.txt">view source
</li>
</ul>
</div>
<div id="navbanner">
<a class="totoc" href="../index.html">SQLAlchemy 0.6.8 Documentation</a>
» <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
»
Object Relational Tutorial
<h2>
Object Relational Tutorial
</h2>
<ul>
<li><a class="reference internal" href="#">Object Relational Tutorial</a><ul>
<li><a class="reference internal" href="#introduction">Introduction</a></li>
<li><a class="reference internal" href="#version-check">Version Check</a></li>
<li><a class="reference internal" href="#connecting">Connecting</a></li>
<li><a class="reference internal" href="#define-and-create-a-table">Define and Create a Table</a></li>
<li><a class="reference internal" href="#define-a-python-class-to-be-mapped">Define a Python Class to be Mapped</a></li>
<li><a class="reference internal" href="#setting-up-the-mapping">Setting up the Mapping</a></li>
<li><a class="reference internal" href="#creating-table-class-and-mapper-all-at-once-declaratively">Creating Table, Class and Mapper All at Once Declaratively</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-join-to-eagerly-load-collections-attributes">Using join() to Eagerly Load Collections/Attributes</a></li>
<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="#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 class="clearboth"></div>
</div>
<div class="document">
<div class="body">
<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>
<div class="section" id="introduction">
<h2>Introduction<a class="headerlink" href="#introduction" title="Permalink to this headline">¶</a></h2>
<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 external" href="http://martinfowler.com/eaaCatalog/unitOfWork.html">unit of work</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 external" href="http://en.wikipedia.org/wiki/Domain_model">domain model</a> which is transparently
persisted and refreshed from its underlying storage model. The other
approaches it from the perspective of literal schema and SQL expression
representations which are explicitly composed into messages consumed
individually by the database.</p>
<p>A successful application may be constructed using the 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">>>></span></tt> line
represents something you can type at a Python command prompt, and the
following text represents the expected return value.</p>
</div>
<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.6</strong> of SQLAlchemy:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">sqlalchemy</span>
<span class="gp">>>> </span><span class="n">sqlalchemy</span><span class="o">.</span><span class="n">__version__</span>
<span class="go">0.6.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">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span>
<span class="gp">>>> </span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///:memory:'</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">echo</span></tt> flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python’s standard <tt class="docutils literal"><span class="pre">logging</span></tt> module. With it enabled, we’ll see all the generated SQL produced. If you are working through this tutorial and want less output generated, set it to <tt class="xref docutils literal"><span class="pre">False</span></tt>. This tutorial will format the SQL behind a popup window so it doesn’t get in our way; just click the “SQL” links to see what’s being generated.</p>
</div>
<div class="section" id="define-and-create-a-table">
<h2>Define and Create a Table<a class="headerlink" href="#define-and-create-a-table" title="Permalink to this headline">¶</a></h2>
<p>Next we want to tell SQLAlchemy about our tables. 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 (lets assume it’s a website). We define our tables within a catalog called <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>, using the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> construct, which is used in a manner similar to SQL’s CREATE TABLE syntax:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">ForeignKey</span>
<span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">users_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'users'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s">'fullname'</span><span class="p">,</span> <span class="n">String</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s">'password'</span><span class="p">,</span> <span class="n">String</span><span class="p">)</span>
<span class="gp">... </span><span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="../core/schema.html"><em>Schema Definition Language</em></a> covers all about how to define <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects, as well as how to load their definition from an existing database (known as <strong>reflection</strong>).</p>
<p>Next, we can issue CREATE TABLE statements derived from our table metadata, by calling <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal"><span class="pre">create_all()</span></tt></a> and passing it the <tt class="docutils literal"><span class="pre">engine</span></tt> instance which points to our database. This will check for the presence of a table first before creating, so it’s safe to call multiple times:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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")
()
CREATE TABLE users (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
password VARCHAR,
PRIMARY KEY (id)
)
()
COMMIT</div></pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p>Users familiar with the syntax of CREATE TABLE may notice that the
VARCHAR columns were generated without a length; on SQLite and Postgresql,
this is a valid datatype, but on others, it’s not allowed. So if running
this tutorial on one of those databases, and you wish to use SQLAlchemy to
issue CREATE TABLE, a “length” may be provided to the <a class="reference internal" href="../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="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span></pre></div>
</div>
<p>The length field on <a class="reference internal" href="../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’t generate or assume these
without being instructed. For that, you use the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal"><span class="pre">Sequence</span></tt></a> construct:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Sequence</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s">'user_id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>A full, foolproof <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> is therefore:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">users_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'users'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s">'user_id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'fullname'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'password'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">12</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p class="last">We include this more verbose <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> construct separately
to highlight the difference between a minimal construct geared primarily
towards in-Python usage only, versus one that will be used to emit CREATE
TABLE statements on a particular set of backends with more stringent
requirements.</p>
</div>
</div>
<div class="section" id="define-a-python-class-to-be-mapped">
<h2>Define a Python Class to be Mapped<a class="headerlink" href="#define-a-python-class-to-be-mapped" title="Permalink to this headline">¶</a></h2>
<p>While the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> object defines information about
our database, it does not say anything about the definition or behavior of the
business objects used by our application; SQLAlchemy views this as a separate
concern. To correspond to our <tt class="docutils literal"><span class="pre">users</span></tt> table, let’s create a rudimentary
<tt class="docutils literal"><span class="pre">User</span></tt> class. It only need subclass Python’s built-in <tt class="docutils literal"><span class="pre">object</span></tt> class (i.e.
it’s a new style class):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="nb">object</span><span class="p">):</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">name</span><span class="p">,</span> <span class="n">fullname</span><span class="p">,</span> <span class="n">password</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="o">=</span> <span class="n">name</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span> <span class="o">=</span> <span class="n">fullname</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="n">password</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">"<User('</span><span class="si">%s</span><span class="s">','</span><span class="si">%s</span><span class="s">', '</span><span class="si">%s</span><span class="s">')>"</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>The class has an <tt class="docutils literal"><span class="pre">__init__()</span></tt> and a <tt class="docutils literal"><span class="pre">__repr__()</span></tt> method for convenience.
These methods are both entirely optional, and can be of any form. SQLAlchemy
never calls <tt class="docutils literal"><span class="pre">__init__()</span></tt> directly.</p>
</div>
<div class="section" id="setting-up-the-mapping">
<h2>Setting up the Mapping<a class="headerlink" href="#setting-up-the-mapping" title="Permalink to this headline">¶</a></h2>
<p>With our <tt class="docutils literal"><span class="pre">users_table</span></tt> and <tt class="docutils literal"><span class="pre">User</span></tt> class, we now want to map the two
together. That’s where the SQLAlchemy ORM package comes in. We’ll use 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 to create a <strong>mapping</strong> between <tt class="docutils literal"><span class="pre">users_table</span></tt> and
<tt class="docutils literal"><span class="pre">User</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">mapper</span>
<span class="gp">>>> </span><span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">)</span>
<span class="go"><Mapper at 0x...; User></span></pre></div>
</div>
<p>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 creates a new <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 and stores it away for future reference, associated with our class.
Let’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">>>> </span><span class="n">ed_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span> <span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'edspassword'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ed_user</span><span class="o">.</span><span class="n">name</span>
<span class="go">'ed'</span>
<span class="gp">>>> </span><span class="n">ed_user</span><span class="o">.</span><span class="n">password</span>
<span class="go">'edspassword'</span>
<span class="gp">>>> </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">'None'</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">id</span></tt> attribute, which while not defined by our <tt class="docutils literal"><span class="pre">__init__()</span></tt> method,
exists due to the <tt class="docutils literal"><span class="pre">id</span></tt> column present within the <tt class="docutils literal"><span class="pre">users_table</span></tt> object. By
default, 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> creates class attributes for all columns present
within the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>. These class attributes exist as
Python descriptors, and define <strong>instrumentation</strong> for the mapped class. The
functionality of this instrumentation is very rich and includes the ability to
track modifications and automatically load new data from the database when
needed.</p>
<p>Since we have not yet told SQLAlchemy to persist <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt> within the
database, its id is <tt class="xref docutils literal"><span class="pre">None</span></tt>. When we persist the object later, this attribute
will be populated with a newly generated value.</p>
</div>
<div class="section" id="creating-table-class-and-mapper-all-at-once-declaratively">
<h2>Creating Table, Class and Mapper All at Once Declaratively<a class="headerlink" href="#creating-table-class-and-mapper-all-at-once-declaratively" title="Permalink to this headline">¶</a></h2>
<p>The preceding approach to configuration involved a
<a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>, a user-defined class, and
a call to <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>. This illustrates classical SQLAlchemy usage, which values
the highest separation of concerns possible.
A large number of applications don’t require this degree of
separation, and for those SQLAlchemy offers an alternate “shorthand”
configurational style called <a class="reference internal" href="extensions/declarative.html#module-sqlalchemy.ext.declarative" title="sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal"><span class="pre">declarative</span></tt></a>.
For many applications, this is the only style of configuration needed.
Our above example using this style is as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </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">>>> </span><span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="gp">>>> </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">'users'</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">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">fullname</span><span class="p">,</span> <span class="n">password</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="o">=</span> <span class="n">name</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span> <span class="o">=</span> <span class="n">fullname</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="n">password</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">"<User('</span><span class="si">%s</span><span class="s">','</span><span class="si">%s</span><span class="s">', '</span><span class="si">%s</span><span class="s">')>"</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>Above, 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 defines a new class which
we name <tt class="docutils literal"><span class="pre">Base</span></tt>, from which all of our ORM-enabled classes will
derive. Note that we define <a class="reference internal" href="../core/schema.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 no “name” field, since it’s inferred from the given
attribute name.</p>
<p>The underlying <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> object created by our
<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> version of <tt class="docutils literal"><span class="pre">User</span></tt> is accessible via the
<tt class="docutils literal"><span class="pre">__table__</span></tt> attribute:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">users_table</span> <span class="o">=</span> <span class="n">User</span><span class="o">.</span><span class="n">__table__</span></pre></div>
</div>
<p>The owning <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a> object is available as well:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span></pre></div>
</div>
<p><a class="reference internal" href="extensions/declarative.html#module-sqlalchemy.ext.declarative" title="sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal"><span class="pre">declarative</span></tt></a> is covered at <a class="reference internal" href="extensions/declarative.html"><em>Declarative</em></a>
as well as throughout <a class="reference internal" href="mapper_config.html"><em>Mapper Configuration</em></a>.</p>
<p>Yet another “declarative” method is available for SQLAlchemy as a third party
library called <a class="reference external" href="http://elixir.ematia.de/">Elixir</a>. This is a full-featured
configurational product which also includes many higher level mapping
configurations built in. Like declarative, once classes and mappings are
defined, ORM usage is the same as with a classical SQLAlchemy configuration.</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’re now ready to start talking to the database. The ORM’s “handle” 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">>>> </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">>>> </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.base.Engine" title="sqlalchemy.engine.base.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">>>> </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
<tt class="xref py py-meth docutils literal"><span class="pre">configure()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </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>
<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-func 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">>>> </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.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>, but it hasn’t opened any connections yet. When it’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.base.Engine" title="sqlalchemy.engine.base.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">>>> </span><span class="n">ed_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span> <span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'edspassword'</span><span class="p">)</span>
<span class="gp">>>> </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 <em>pending</em>; 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 “filter by” 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’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’ve
added:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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">'ed'</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 1 OFFSET 0
('ed',)</div><span class="o">>>></span> <span class="n">our_user</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'edspassword'</span><span class="p">)</span><span class="o">></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">>>> </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 <strong>identity map</strong> 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">>>></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="s">'wendy'</span><span class="p">,</span> <span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">),</span>
<span class="o">...</span> <span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span> <span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">),</span>
<span class="o">...</span> <span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span> <span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)])</span></pre></div>
</div>
<p>Also, Ed has already decided his password isn’t too secure, so lets change it:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">ed_user</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="s">'f8s7ccs'</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">>>></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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></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">>>></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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span><span class="p">,</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span><span class="p">,</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></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’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">>>></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’s <tt class="docutils literal"><span class="pre">id</span></tt> attribute, which earlier was <tt class="xref 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">>>></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’s
accessed within a new transaction, so that the most recent state is available.
The level of reloading is configurable as is described in the chapter on
Sessions.</p>
</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’s make two changes that we’ll revert;
<tt class="docutils literal"><span class="pre">ed_user</span></tt>‘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">>>></span> <span class="n">ed_user</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="s">'Edwardo'</span></pre></div>
</div>
<p>and we’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">>>></span> <span class="n">fake_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'fakeuser'</span><span class="p">,</span> <span class="s">'Invalid'</span><span class="p">,</span> <span class="s">'12345'</span><span class="p">)</span>
<span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">fake_user</span><span class="p">)</span></pre></div>
</div>
<p>Querying the session, we can see that they’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">>>></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">'Edwardo'</span><span class="p">,</span> <span class="s">'fakeuser'</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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'Edwardo'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fakeuser'</span><span class="p">,</span><span class="s">'Invalid'</span><span class="p">,</span> <span class="s">'12345'</span><span class="p">)</span><span class="o">></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>‘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">>>></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">>>></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'ed'</span>
<span class="o">>>></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">>>></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">'ed'</span><span class="p">,</span> <span class="s">'fakeuser'</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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></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> 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> function 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">>>></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">>>></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, and can be treated much like an ordinary Python object. The names are
the same as the attribute’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">>>></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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span> <span class="n">ed</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span> <span class="n">wendy</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span> <span class="n">mary</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span> <span class="n">fred</span></pre></div>
</div>
<p>You can control the names using the <a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression._CompareMixin.label" title="sqlalchemy.sql.expression._CompareMixin.label"><tt class="xref py py-meth docutils literal"><span class="pre">label()</span></tt></a> construct for
scalar attributes and <a class="reference internal" href="query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><tt class="xref py py-class docutils literal"><span class="pre">aliased</span></tt></a> for class constructs:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">>>></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">'user_alias'</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></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="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'name_label'</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><span class="p">,</span> <span class="n">row</span><span class="o">.</span><span class="n">name_label</span>
<div class='popup_sql'>SELECT users_1.id AS users_1_id, users_1.name AS users_1_name, users_1.fullname AS users_1_fullname, users_1.password AS users_1_password, users_1.name AS name_label
FROM users AS users_1
()</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span> <span class="n">ed</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span> <span class="n">wendy</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span> <span class="n">mary</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span> <span class="n">fred</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">>>></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 2 OFFSET 1
()</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></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">>>></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_by</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="s">'Ed Jones'</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">>>></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">User</span><span class="o">.</span><span class="n">fullname</span><span class="o">==</span><span class="s">'Ed Jones'</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 <em>generative</em>, 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 “ed” with a full name of “Ed Jones”, 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">>>></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="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">'ed'</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">'Ed Jones'</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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></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’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">equals:</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">'ed'</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">not equals:</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">'ed'</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">LIKE:</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">'</span><span class="si">%e</span><span class="s">d%'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">IN:</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">'ed'</span><span class="p">,</span> <span class="s">'wendy'</span><span class="p">,</span> <span class="s">'jack'</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">'</span><span class="si">%e</span><span class="s">d%'</span><span class="p">))))</span></pre></div>
</div>
</li>
<li><p class="first">NOT IN:</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">'ed'</span><span class="p">,</span> <span class="s">'wendy'</span><span class="p">,</span> <span class="s">'jack'</span><span class="p">]))</span></pre></div>
</div>
</li>
<li><p class="first">IS NULL:</p>
<div class="highlight-python"><div class="highlight"><pre><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="bp">None</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">IS NOT NULL:</p>
<div class="highlight-python"><div class="highlight"><pre><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="bp">None</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">AND:</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">and_</span>
<span class="nb">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">'ed'</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">'Ed Jones'</span><span class="p">))</span>
<span class="c"># or call filter()/filter_by() multiple times</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">'ed'</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">'Ed Jones'</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">OR:</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="nb">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">'ed'</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">'wendy'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">match:</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">'wendy'</span><span class="p">))</span></pre></div>
</div>
</li>
</ul>
<blockquote>
<div>The contents of the match parameter are database backend specific.</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>The <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>,
<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>, and
<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> methods 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> immediately issue SQL and return a
non-iterator value. <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">>>></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">'</span><span class="si">%e</span><span class="s">d'</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">>>></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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p><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">>>></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 1 OFFSET 0
('%ed',)</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p><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:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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">>>></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>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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">>>></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>
</div>
<div class="section" id="using-literal-sql">
<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>. Most methods accept strings in addition
to SQLAlchemy clause constructs. 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><a href='#' class='sql_link'>sql</a><span class="o">>>></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="n">filter</span><span class="p">(</span><span class="s">"id<224"</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s">"id"</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">>>></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="s">"id<:value and name=:name"</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">'fred'</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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></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">>>></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="s">"SELECT * FROM users where name=:name"</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">'ed'</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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></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 “raw”, 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">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">"id"</span><span class="p">,</span> <span class="s">"name"</span><span class="p">,</span> <span class="s">"thenumber12"</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="s">"SELECT id, name, 12 as "</span>
<span class="o">...</span> <span class="s">"thenumber12 FROM users where name=:name"</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">'ed'</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'ed'</span><span class="p">,</span> <span class="mi">12</span><span class="p">)]</span></pre></div>
</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">>>></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">'</span><span class="si">%e</span><span class="s">d'</span><span class="p">))</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM users
WHERE users.name LIKE ?
('%ed',)</div><span class="mi">2</span></pre></div>
</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, and is mainly intended to return
a simple count of a single type of entity, in this case <tt class="docutils literal"><span class="pre">User</span></tt>. For more
complicated sets of columns or entities where the “thing to be counted” needs
to be indicated more specifically, <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>
is probably not what you want. Below, a query for individual columns does
return the expected result:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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="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">'</span><span class="si">%e</span><span class="s">d'</span><span class="p">))</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM (SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)</div><span class="mi">2</span></pre></div>
</div>
<p>...but if you look at the generated SQL, SQLAlchemy saw that we were placing
individual column expressions and decided to wrap whatever it was we were
doing in a subquery, so as to be assured that it returns the “number of rows”.
This defensive behavior is not really needed here and in other cases is not
what we want at all, such as if we wanted a grouping of counts per name:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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="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">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM (SELECT users.name AS users_name
FROM users GROUP BY users.name) AS anon_1
()</div><span class="mi">4</span></pre></div>
</div>
<p>We don’t want the number <tt class="docutils literal"><span class="pre">4</span></tt>, we wanted some rows back. So for detailed
queries where you need to count something specific, use the <tt class="docutils literal"><span class="pre">func.count()</span></tt>
function as a column expression:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">>>></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="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'ed'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'fred'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'mary'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">)]</span></pre></div>
</div>
</div>
</div>
<div class="section" id="building-a-relationship">
<h2>Building a Relationship<a class="headerlink" href="#building-a-relationship" title="Permalink to this headline">¶</a></h2>
<p>Now let’s consider a second table to be dealt with. Users in our system also
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_table</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">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">ForeignKey</span>
<span class="o">>>></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">>>></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">'addresses'</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">'users.id'</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="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">'addresses'</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">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">email_address</span><span class="p">):</span>
<span class="o">...</span> <span class="bp">self</span><span class="o">.</span><span class="n">email_address</span> <span class="o">=</span> <span class="n">email_address</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">"<Address('</span><span class="si">%s</span><span class="s">')>"</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 a <strong>foreign key</strong> constraint which references the
<tt class="docutils literal"><span class="pre">users</span></tt> table. This defines for SQLAlchemy the relationship between the two
tables at the database level. The relationship between the <tt class="docutils literal"><span class="pre">User</span></tt> and
<tt class="docutils literal"><span class="pre">Address</span></tt> classes is defined separately using the
<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> function, which defines an attribute
<tt class="docutils literal"><span class="pre">user</span></tt> to be placed on the <tt class="docutils literal"><span class="pre">Address</span></tt> class, as well as an <tt class="docutils literal"><span class="pre">addresses</span></tt>
collection to be placed on the <tt class="docutils literal"><span class="pre">User</span></tt> class. Such a relationship is known as
a <strong>bidirectional</strong> relationship. Because of the placement of the foreign key,
from <tt class="docutils literal"><span class="pre">Address</span></tt> to <tt class="docutils literal"><span class="pre">User</span></tt> it is <strong>many to one</strong>, and from <tt class="docutils literal"><span class="pre">User</span></tt> to
<tt class="docutils literal"><span class="pre">Address</span></tt> it is <strong>one to many</strong>. SQLAlchemy is automatically aware of
many-to-one/one-to-many based on foreign keys.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The <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> function has historically
been known as <a class="reference internal" href="relationships.html#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal"><span class="pre">relation()</span></tt></a>, which is the name that’s
available in all versions of SQLAlchemy prior to 0.6beta2, including the 0.5
and 0.4 series. <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> is only available
starting with SQLAlchemy 0.6beta2. <a class="reference internal" href="relationships.html#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal"><span class="pre">relation()</span></tt></a> will
remain available in SQLAlchemy for the foreseeable future to enable
cross-compatibility.</p>
</div>
<p>The <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> function is extremely flexible, and
could just have easily been defined on the <tt class="docutils literal"><span class="pre">User</span></tt> class:</p>
<div class="highlight-python+sql"><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="n">Address</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="n">Address</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span></pre></div>
</div>
<p>We are also free to not define a backref, and to define the
<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> only on one class and not the other. It
is also possible to define two separate <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>
constructs for either direction, which is generally safe for many-to-one and
one-to-many relationships, but not for many-to-many relationships.</p>
<p>When using the <a class="reference internal" href="extensions/declarative.html#module-sqlalchemy.ext.declarative" title="sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal"><span class="pre">declarative</span></tt></a> extension,
<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> gives us the option to use strings for
most arguments that concern the target class, in the case that the target
class has not yet been defined. This <strong>only</strong> works in conjunction with
<a class="reference internal" href="extensions/declarative.html#module-sqlalchemy.ext.declarative" title="sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal"><span class="pre">declarative</span></tt></a>:</p>
<div class="highlight-python+sql"><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="o">....</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="s">"Address.id"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span></pre></div>
</div>
<p>When <a class="reference internal" href="extensions/declarative.html#module-sqlalchemy.ext.declarative" title="sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal"><span class="pre">declarative</span></tt></a> is not in use, you typically define your
<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> well after the target classes and
<a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects have been defined, so string
expressions are not needed.</p>
<p>We’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">>>></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">>>></span> <span class="n">jack</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span> <span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span>
<span class="o">>>></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">>>></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="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">'jack@google.com'</span><span class="p">),</span> <span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">'j25@yahoo.com'</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 is the basic
behavior of the <strong>backref</strong> keyword, which maintains the relationship purely
in memory, without using any SQL:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">Address</span><span class="p">(</span><span class="s">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span>
<span class="o">>>></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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Let’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 his <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">>>></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">>>></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’s addresses:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'jack'</span><span class="p">)</span><span class="o">.</span><span class="n">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">>>></span> <span class="n">jack</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Let’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">>>></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"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></span><span class="p">,</span> <span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></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 <strong>lazy loading relationship</strong>. The <tt class="docutils literal"><span class="pre">addresses</span></tt> collection
is now loaded and behaves just like an ordinary list.</p>
<p>If you want to reduce the number of queries (dramatically, in many cases), we
can apply an <strong>eager load</strong> to the query operation, using the
<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> function. This function is a <strong>query
option</strong> that gives additional instructions to the query on how we would like
it to load, in this case we’d like to indicate that we’d like <tt class="docutils literal"><span class="pre">addresses</span></tt> to
load “eagerly”. SQLAlchemy then constructs an outer join between the <tt class="docutils literal"><span class="pre">users</span></tt>
and <tt class="docutils literal"><span class="pre">addresses</span></tt> tables, and loads them at once, populating the <tt class="docutils literal"><span class="pre">addresses</span></tt>
collection on each <tt class="docutils literal"><span class="pre">User</span></tt> object if it’s not already populated:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">>>></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="s">'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">'jack'</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">>>></span> <span class="n">jack</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span>
<span class="o">>>></span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span>
<span class="p">[</span><span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></span><span class="p">,</span> <span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p>See <a class="reference internal" href="loading.html"><em>Relationship Loading Techniques</em></a> for information on
<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> and its new brother,
<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>. We’ll also see another way to “eagerly”
load in the next section.</p>
</div>
<div class="section" id="querying-with-joins">
<h2>Querying with Joins<a class="headerlink" href="#querying-with-joins" title="Permalink to this headline">¶</a></h2>
<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> created a JOIN specifically to
populate a collection, we can also work explicitly with joins in many ways.
For example, to construct a simple inner 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 just <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> 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">>>></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="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="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">'jack@google.com'</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">u</span><span class="p">,</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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Or we can make a real JOIN construct; the most common way is to use
<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>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">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">'jack@google.com'</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 JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></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">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’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">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 (note the tuple)</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">'addresses'</span><span class="p">)</span> <span class="c"># same, using a string</span></pre></div>
</div>
<p>Note that when <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 called with an explicit target as well as an ON clause, we use a tuple as the argument. This is so that multiple joins can be chained together, as in:</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span>
<span class="n">Foo</span><span class="o">.</span><span class="n">bars</span><span class="p">,</span>
<span class="p">(</span><span class="n">Bat</span><span class="p">,</span> <span class="n">bar</span><span class="o">.</span><span class="n">bats</span><span class="p">),</span>
<span class="p">(</span><span class="n">Widget</span><span class="p">,</span> <span class="n">Bat</span><span class="o">.</span><span class="n">widget_id</span><span class="o">==</span><span class="n">Widget</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>The above would produce SQL something like <tt class="docutils literal"><span class="pre">foo</span> <span class="pre">JOIN</span> <span class="pre">bars</span> <span class="pre">ON</span> <span class="pre"><onclause></span> <span class="pre">JOIN</span>
<span class="pre">bats</span> <span class="pre">ON</span> <span class="pre"><onclause></span> <span class="pre">JOIN</span> <span class="pre">widgets</span> <span class="pre">ON</span> <span class="pre"><onclause></span></tt>.</p>
<p>The general functionality of <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
also available as a standalone function <a class="reference internal" href="query.html#sqlalchemy.orm.join" title="sqlalchemy.orm.join"><tt class="xref py py-func docutils literal"><span class="pre">join()</span></tt></a>, which is
an ORM-enabled version of the same function present in the SQL expression
language. This function accepts two or three arguments (left side, right side,
optional ON clause) and can be used in conjunction with the
<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> method to set an explicit FROM
clause:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">join</span>
<a href='#' class='sql_link'>sql</a><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">select_from</span><span class="p">(</span><span class="n">join</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="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">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'jack@google.com'</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 JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<div class="section" id="using-join-to-eagerly-load-collections-attributes">
<h3>Using join() to Eagerly Load Collections/Attributes<a class="headerlink" href="#using-join-to-eagerly-load-collections-attributes" title="Permalink to this headline">¶</a></h3>
<p>The “eager loading” capabilities of the <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>
function and the join-construction capabilities of
<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> or an equivalent can be combined
together using 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">contains_eager()</span></tt></a> option. This is
typically used for a query that is already joining to some related entity
(more often than not via many-to-one), and you’d like the related entity to
also be loaded onto the resulting objects in one step without the need for
additional queries and without the “automatic” join embedded by the
<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> function:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">>>></span> <span class="k">for</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">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">'jack'</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="k">print</span> <span class="n">address</span><span class="p">,</span> <span class="n">address</span><span class="o">.</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, 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"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span>
<span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Note that above the join was used both to limit the rows to just those
<tt class="docutils literal"><span class="pre">Address</span></tt> objects which had a related <tt class="docutils literal"><span class="pre">User</span></tt> object with the name “jack”.
It’s safe to have the <tt class="docutils literal"><span class="pre">Address.user</span></tt> attribute populated with this user
using an inner join. However, when filtering on a join that is filtering on a
particular member of a collection, 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">contains_eager()</span></tt></a> to populate a related collection may
populate the collection with only part of what it actually references, since
the collection itself is filtered.</p>
</div>
<div class="section" id="using-aliases">
<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 <tt class="docutils literal"><span class="pre">aliased</span></tt> 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">>>></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">>>></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">>>></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">>>></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="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">'jack@google.com'</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">'j25@yahoo.com'</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’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 <tt class="docutils literal"><span class="pre">select()</span></tt>
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">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">func</span>
<span class="gp">>>> </span><span class="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">'*'</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'address_count'</span><span class="p">))</span><span class="o">.</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’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/schema.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">>>></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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></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 “alias” 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">>>></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="n">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">'j25@yahoo.com'</span><span class="p">)</span><span class="o">.</span><span class="n">subquery</span><span class="p">()</span>
<span class="o">>>></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">>>></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="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="p">,</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"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></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">>>></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">>>></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">>>></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 <tt class="docutils literal"><span class="pre">any()</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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">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><tt class="docutils literal"><span class="pre">any()</span></tt> 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">>>></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">'</span><span class="si">%g</span><span class="s">oogle%'</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><tt class="docutils literal"><span class="pre">has()</span></tt> is the same operator as <tt class="docutils literal"><span class="pre">any()</span></tt> for many-to-one relationships (note the <tt class="docutils literal"><span class="pre">~</span></tt> operator here too, which means “NOT”):</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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="n">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">'jack'</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’s all the operators which build on relationships:</p>
<ul>
<li><p class="first">equals (used for many-to-one):</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">not equals (used for many-to-one):</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 (used for many-to-one):</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">contains (used for one-to-many and many-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">any (used for one-to-many and many-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">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">'bar'</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">'bar'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">has (used for many-to-one):</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">'ed'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">with_parent (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">'addresses'</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
</div>
</div>
<div class="section" id="deleting">
<h2>Deleting<a class="headerlink" href="#deleting" title="Permalink to this headline">¶</a></h2>
<p>Let’s try to delete <tt class="docutils literal"><span class="pre">jack</span></tt> and see how that goes. We’ll mark as deleted in
the session, then we’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">>>></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">>>></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">'jack'</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(1) AS count_1
FROM users
WHERE users.name = ?
('jack',)</div><span class="mi">0</span></pre></div>
</div>
<p>So far, so good. How about Jack’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">>>></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">'jack@google.com'</span><span class="p">,</span> <span class="s">'j25@yahoo.com'</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(1) AS count_1
FROM addresses
WHERE addresses.email_address IN (?, ?)
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">2</span></pre></div>
</div>
<p>Uh oh, they’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’t
deleted. SQLAlchemy doesn’t assume that deletes cascade, you have to tell it
to do so.</p>
<div class="section" id="configuring-delete-delete-orphan-cascade">
<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.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Tearing down mappers with <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.clear_mappers" title="sqlalchemy.orm.clear_mappers"><tt class="xref py py-func docutils literal"><span class="pre">clear_mappers()</span></tt></a> is not a typical
operation, and normal applications do not need to use this function. It is
here so that the tutorial code can be executed as a whole.</p>
</div>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> <span class="c"># roll back and close the transaction</span>
<span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">clear_mappers</span>
<span class="o">>>></span> <span class="n">clear_mappers</span><span class="p">()</span> <span class="c"># remove all class mappings</span></pre></div>
</div>
<p>Below, we use <tt class="xref py py-class docutils literal"><span class="pre">mapper</span></tt> to reconfigure an ORM mapping for <tt class="docutils literal"><span class="pre">User</span></tt> and
<tt class="docutils literal"><span class="pre">Address</span></tt>, on our existing but currently un-mapped classes. The
<tt class="docutils literal"><span class="pre">User.addresses</span></tt> relationship now has <tt class="docutils literal"><span class="pre">delete,</span> <span class="pre">delete-orphan</span></tt> cascade on
it, which indicates that DELETE operations will cascade to attached
<tt class="docutils literal"><span class="pre">Address</span></tt> objects as well as <tt class="docutils literal"><span class="pre">Address</span></tt> objects which are removed from
their parent:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">users_table</span> <span class="o">=</span> <span class="n">User</span><span class="o">.</span><span class="n">__table__</span>
<span class="o">>>></span> <span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="o">...</span> <span class="s">'addresses'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'user'</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s">"all, delete, delete-orphan"</span><span class="p">)</span>
<span class="o">...</span> <span class="p">})</span>
<span class="o"><</span><span class="n">Mapper</span> <span class="n">at</span> <span class="mi">0</span><span class="n">x</span><span class="o">...</span><span class="p">;</span> <span class="n">User</span><span class="o">></span>
<span class="o">>>></span> <span class="n">addresses_table</span> <span class="o">=</span> <span class="n">Address</span><span class="o">.</span><span class="n">__table__</span>
<span class="o">>>></span> <span class="n">mapper</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</span>
<span class="o"><</span><span class="n">Mapper</span> <span class="n">at</span> <span class="mi">0</span><span class="n">x</span><span class="o">...</span><span class="p">;</span> <span class="n">Address</span><span class="o">></span></pre></div>
</div>
<p>Now when we load Jack (below using <tt class="xref py py-meth docutils literal"><span class="pre">get()</span></tt>, which loads by primary key),
removing an address from his <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">>>></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">>>></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">>>></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">'jack@google.com'</span><span class="p">,</span> <span class="s">'j25@yahoo.com'</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(1) AS count_1
FROM addresses
WHERE addresses.email_address IN (?, ?)
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">1</span></pre></div>
</div>
<p>Deleting Jack will delete both Jack and his remaining <tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">>>></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">'jack'</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(1) AS count_1
FROM users
WHERE users.name = ?
('jack',)</div><span class="mi">0</span>
<a href='#' class='sql_link'>sql</a><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="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">'jack@google.com'</span><span class="p">,</span> <span class="s">'j25@yahoo.com'</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(1) AS count_1
FROM addresses
WHERE addresses.email_address IN (?, ?)
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">0</span></pre></div>
</div>
</div>
</div>
<div class="section" id="building-a-many-to-many-relationship">
<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’re moving into the bonus round here, but lets show off a many-to-many
relationship. We’ll sneak in some other features too, just to take a tour.
We’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>The declarative setup is as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Text</span>
<span class="o">>>></span> <span class="c"># association table</span>
<span class="o">>>></span> <span class="n">post_keywords</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'post_keywords'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="o">...</span> <span class="n">Column</span><span class="p">(</span><span class="s">'post_id'</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">'posts.id'</span><span class="p">)),</span>
<span class="o">...</span> <span class="n">Column</span><span class="p">(</span><span class="s">'keyword_id'</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">'keywords.id'</span><span class="p">))</span>
<span class="o">...</span> <span class="p">)</span>
<span class="o">>>></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="o">...</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'posts'</span>
<span class="o">...</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">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">'users.id'</span><span class="p">))</span>
<span class="o">...</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="o">...</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="o">...</span>
<span class="o">...</span> <span class="c"># many to many BlogPost<->Keyword</span>
<span class="o">...</span> <span class="n">keywords</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">'Keyword'</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">'posts'</span><span class="p">)</span>
<span class="o">...</span>
<span class="o">...</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="o">...</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="o">...</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="o">...</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="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">"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">)"</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="o">>>></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="o">...</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'keywords'</span>
<span class="o">...</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">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="o">...</span>
<span class="o">...</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="o">...</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>
<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/schema.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 “association object”, described at
<a class="reference internal" href="relationships.html#association-pattern"><em>Association Object</em></a>.</p>
<p>The many-to-many relationship is also bi-directional using the <tt class="docutils literal"><span class="pre">backref</span></tt>
keyword. This is the one case where usage of <tt class="docutils literal"><span class="pre">backref</span></tt> is generally
required, since if a separate <tt class="docutils literal"><span class="pre">posts</span></tt> relationship were added to the
<tt class="docutils literal"><span class="pre">Keyword</span></tt> entity, both relationships would independently add and remove rows
from the <tt class="docutils literal"><span class="pre">post_keywords</span></tt> table and produce conflicts.</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’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’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
“reverse” 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">>>></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">>>></span> <span class="c"># "dynamic" loading relationship to User</span>
<span class="o">>>></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">'posts'</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">'dynamic'</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">>>></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’ve been doing. Let’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">>>></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="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'wendy'</span><span class="p">)</span><span class="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">>>></span> <span class="n">post</span> <span class="o">=</span> <span class="n">BlogPost</span><span class="p">(</span><span class="s">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">"This is a test"</span><span class="p">,</span> <span class="n">wendy</span><span class="p">)</span>
<span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">post</span><span class="p">)</span></pre></div>
</div>
<p>We’re storing keywords uniquely in the database, but we know that we don’t
have any yet, so we can just create them:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">'wendy'</span><span class="p">))</span>
<span class="o">>>></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">'firstpost'</span><span class="p">))</span></pre></div>
</div>
<p>We can now look up all blog posts with the keyword ‘firstpost’. We’ll use the
<tt class="docutils literal"><span class="pre">any</span></tt> operator to locate “blog posts where any of its keywords has the
keyword string ‘firstpost’”:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">BlogPost</span><span class="p">)</span><span class="o">.</span><span class="n">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">'firstpost'</span><span class="p">))</span><span class="o">.</span><span class="n">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">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">'This is a test'</span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span><span class="p">)]</span></pre></div>
</div>
<p>If we want to look up just Wendy’s posts, we can tell the query to narrow down
to her as a parent:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">BlogPost</span><span class="p">)</span><span class="o">.</span><span class="n">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">'firstpost'</span><span class="p">))</span><span class="o">.</span><span class="n">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">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">'This is a test'</span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span><span class="p">)]</span></pre></div>
</div>
<p>Or we can use Wendy’s own <tt class="docutils literal"><span class="pre">posts</span></tt> relationship, which is a “dynamic”
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">>>></span> <span class="n">wendy</span><span class="o">.</span><span class="n">posts</span><span class="o">.</span><span class="n">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">'firstpost'</span><span class="p">))</span><span class="o">.</span><span class="n">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">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">'This is a test'</span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></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 class="bottomnav">
Previous:
<a href="index.html" title="previous chapter">SQLAlchemy ORM</a>
Next:
<a href="mapper_config.html" title="next chapter">Mapper Configuration</a>
<div class="doc_copyright">
© <a href="../copyright.html">Copyright</a> 2007-2011, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.7.
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
0f12b69182fe3d3174a2e2454ef87704
>
files
>
510
