File: //opt/rh/python27/root/usr/share/doc/python27-python-sqlalchemy-0.7.9/doc/orm/tutorial.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Object Relational Tutorial
—
SQLAlchemy 0.7 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.7.9',
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.7 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>
<div id="docs-container">
<div id="docs-header">
<h1>SQLAlchemy 0.7 Documentation</h1>
<div id="docs-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 id="docs-version-header">
Release: <span class="version-num">0.7.9</span> | Release Date: October 1, 2012
</div>
</div>
<div id="docs-top-navigation">
<div id="docs-top-page-control" class="docs-navigation-links">
<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="docs-navigation-banner">
<a href="../index.html">SQLAlchemy 0.7 Documentation</a>
» <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
»
Object Relational Tutorial
<h2>
Object Relational Tutorial
</h2>
</div>
</div>
<div id="docs-body-container">
<div id="docs-sidebar">
<h3><a href="../index.html">Table of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">Object Relational Tutorial</a><ul>
<li><a class="reference internal" href="#version-check">Version Check</a></li>
<li><a class="reference internal" href="#connecting">Connecting</a></li>
<li><a class="reference internal" href="#declare-a-mapping">Declare a Mapping</a></li>
<li><a class="reference internal" href="#create-an-instance-of-the-mapped-class">Create an Instance of the Mapped Class</a></li>
<li><a class="reference internal" href="#creating-a-session">Creating a Session</a></li>
<li><a class="reference internal" href="#adding-new-objects">Adding New Objects</a></li>
<li><a class="reference internal" href="#rolling-back">Rolling Back</a></li>
<li><a class="reference internal" href="#querying">Querying</a><ul>
<li><a class="reference internal" href="#common-filter-operators">Common Filter Operators</a></li>
<li><a class="reference internal" href="#returning-lists-and-scalars">Returning Lists and Scalars</a></li>
<li><a class="reference internal" href="#using-literal-sql">Using Literal SQL</a></li>
<li><a class="reference internal" href="#counting">Counting</a></li>
</ul>
</li>
<li><a class="reference internal" href="#building-a-relationship">Building a Relationship</a></li>
<li><a class="reference internal" href="#working-with-related-objects">Working with Related Objects</a></li>
<li><a class="reference internal" href="#querying-with-joins">Querying with Joins</a><ul>
<li><a class="reference internal" href="#using-aliases">Using Aliases</a></li>
<li><a class="reference internal" href="#using-subqueries">Using Subqueries</a></li>
<li><a class="reference internal" href="#selecting-entities-from-subqueries">Selecting Entities from Subqueries</a></li>
<li><a class="reference internal" href="#using-exists">Using EXISTS</a></li>
<li><a class="reference internal" href="#common-relationship-operators">Common Relationship Operators</a></li>
</ul>
</li>
<li><a class="reference internal" href="#eager-loading">Eager Loading</a><ul>
<li><a class="reference internal" href="#subquery-load">Subquery Load</a></li>
<li><a class="reference internal" href="#joined-load">Joined Load</a></li>
<li><a class="reference internal" href="#explicit-join-eagerload">Explicit Join + Eagerload</a></li>
</ul>
</li>
<li><a class="reference internal" href="#deleting">Deleting</a><ul>
<li><a class="reference internal" href="#configuring-delete-delete-orphan-cascade">Configuring delete/delete-orphan Cascade</a></li>
</ul>
</li>
<li><a class="reference internal" href="#building-a-many-to-many-relationship">Building a Many To Many Relationship</a></li>
<li><a class="reference internal" href="#further-reference">Further Reference</a></li>
</ul>
</li>
</ul>
<h4>Previous Topic</h4>
<p>
<a href="index.html" title="previous chapter">SQLAlchemy ORM</a>
</p>
<h4>Next Topic</h4>
<p>
<a href="mapper_config.html" title="next chapter">Mapper Configuration</a>
</p>
<h4>Quick Search</h4>
<p>
<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>
</p>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="object-relational-tutorial">
<span id="ormtutorial-toplevel"></span><h1>Object Relational Tutorial<a class="headerlink" href="#object-relational-tutorial" title="Permalink to this headline">¶</a></h1>
<p>The SQLAlchemy Object Relational Mapper presents a method of associating
user-defined Python classes with database tables, and instances of those
classes (objects) with rows in their corresponding tables. It includes a
system that transparently synchronizes all changes in state between objects
and their related rows, called a <a class="reference 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 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.7</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.7.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="docutils literal"><span class="pre">False</span></tt>. This tutorial will format
the SQL behind a popup window so it doesn’t get in our way; just click the
“SQL” links to see what’s being generated.</p>
<p>The return value of <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> is an instance of <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>, and it represents
the core interface to the database, adapted through a <strong>dialect</strong> that handles the details
of the database and DBAPI in use. In this case the SQLite dialect will interpret instructions
to the Python built-in <tt class="docutils literal"><span class="pre">sqlite3</span></tt> module.</p>
<p>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> has not actually tried to connect to the database yet; that happens
only the first time it is asked to perform a task against the database. We can illustrate
this by asking it to perform a simple SELECT statement:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select 1"</span><span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span>
<div class='popup_sql'>select 1
()</div><span class="mi">1</span></pre></div>
</div>
<p>As the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.base.Engine.execute" title="sqlalchemy.engine.base.Engine.execute"><tt class="xref py py-meth docutils literal"><span class="pre">Engine.execute()</span></tt></a> method is called, 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> establishes a connection to the
SQLite database, which is then used to emit the SQL. The connection is then returned to an internal
connection pool where it will be reused on subsequent statement executions. While we illustrate direct usage of 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> here, this isn’t typically necessary when using the ORM, where 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>,
once created, is used behind the scenes by the ORM as we’ll see shortly.</p>
</div>
<div class="section" id="declare-a-mapping">
<h2>Declare a Mapping<a class="headerlink" href="#declare-a-mapping" title="Permalink to this headline">¶</a></h2>
<p>When using the ORM, the configurational process starts by describing the database
tables we’ll be dealing with, and then by defining our own classes which will
be mapped to those tables. In modern SQLAlchemy,
these two tasks are usually performed together,
using a system known as <a class="reference internal" href="extensions/declarative.html"><em>Declarative</em></a>, which allows us to create
classes that include directives to describe the actual database table they will
be mapped to.</p>
<p>Classes mapped using the Declarative system are defined in terms of a base class which
maintains a catalog of classes and
tables relative to that base - this is known as the <strong>declarative base class</strong>. Our
application will usually have just one instance of this base in a commonly
imported module. We create the base class using the <a class="reference internal" href="extensions/declarative.html#sqlalchemy.ext.declarative.declarative_base" title="sqlalchemy.ext.declarative.declarative_base"><tt class="xref py py-func docutils literal"><span class="pre">declarative_base()</span></tt></a>
function, as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </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></pre></div>
</div>
<p>Now that we have a “base”, we can define any number of mapped classes in terms
of it. We will start with just a single table called <tt class="docutils literal"><span class="pre">users</span></tt>, which will store
records for the end-users using our application.
A new class called <tt class="docutils literal"><span class="pre">User</span></tt> will be the class to which we map this table. The
imports we’ll need to accomplish this include objects that represent the components
of our table, including the <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> class which represents a database column,
as well as the <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> and <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> classes that
represent basic datatypes used in columns:</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">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">String</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>The above <tt class="docutils literal"><span class="pre">User</span></tt> class establishes details about the table being mapped, including the name of the table denoted
by the <tt class="docutils literal"><span class="pre">__tablename__</span></tt> attribute, a set of columns <tt class="docutils literal"><span class="pre">id</span></tt>, <tt class="docutils literal"><span class="pre">name</span></tt>, <tt class="docutils literal"><span class="pre">fullname</span></tt> and <tt class="docutils literal"><span class="pre">password</span></tt>,
where the <tt class="docutils literal"><span class="pre">id</span></tt> column will also be the primary key of the table. While its certainly possible
that some database tables don’t have primary key columns (as is also the case with views, which can
also be mapped), the ORM in order to actually map to a particular table needs there
to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys
are of course entirely feasible as well.</p>
<p>We define a constructor via <tt class="docutils literal"><span class="pre">__init__()</span></tt> and also a <tt class="docutils literal"><span class="pre">__repr__()</span></tt> method - both are optional. The
class of course can have any number of other methods and attributes as required by the application,
as it’s basically just a plain Python class. Inheriting from <tt class="docutils literal"><span class="pre">Base</span></tt> is also only a requirement
of the declarative configurational system, which itself is optional and relatively open ended; at its
core, the SQLAlchemy ORM only requires that a class be a so-called “new style class”, that is, it inherits
from <tt class="docutils literal"><span class="pre">object</span></tt> in Python 2, in order to be mapped. All classes in Python 3 are “new style” classes.</p>
<div class="topic">
<p class="topic-title first">The Non Opinionated Philosophy</p>
<p>In our <tt class="docutils literal"><span class="pre">User</span></tt> mapping example, it was required that we identify the name of the table
in use, as well as the names and characteristics of all columns which we care about,
including which column or columns
represent the primary key, as well as some basic information about the types in use.
SQLAlchemy never makes assumptions about these decisions - the developer must
always be explicit about specific conventions in use. However, that doesn’t mean the
task can’t be automated. While this tutorial will keep things explicit, developers are
encouraged to make use of helper functions as well as “Declarative Mixins” to
automate their tasks in large scale applications. The section <a class="reference internal" href="extensions/declarative.html#declarative-mixins"><em>Mixin and Custom Base Classes</em></a>
introduces many of these techniques.</p>
</div>
<p>With our <tt class="docutils literal"><span class="pre">User</span></tt> class constructed via the Declarative system, we have defined information about
our table, known as <strong>table metadata</strong>, as well as a user-defined class which is linked to this
table, known as a <strong>mapped class</strong>. Declarative has provided for us a shorthand system for what in SQLAlchemy is
called a “Classical Mapping”, which specifies these two units separately and is discussed
in <a class="reference internal" href="mapper_config.html#classical-mapping"><em>Classical Mappings</em></a>. The table
is actually represented by a datastructure known as <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>, and the mapping represented
by a <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> object generated by a function called <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>. Declarative performs both of
these steps for us, making available 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> it has created 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">User</span><span class="o">.</span><span class="n">__table__</span>
<span class="go">Table('users', MetaData(None),</span>
<span class="go"> Column('id', Integer(), table=<users>, primary_key=True, nullable=False),</span>
<span class="go"> Column('name', String(), table=<users>),</span>
<span class="go"> Column('fullname', String(), table=<users>),</span>
<span class="go"> Column('password', String(), table=<users>), schema=None)</span></pre></div>
</div>
<p>and while rarely needed, making available the <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 via the <tt class="docutils literal"><span class="pre">__mapper__</span></tt> attribute:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">User</span><span class="o">.</span><span class="n">__mapper__</span>
<span class="go"><Mapper at 0x...; User></span></pre></div>
</div>
<p>The Declarative base class also contains a catalog of all 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> objects
that have been defined 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>, available via the <tt class="docutils literal"><span class="pre">.metadata</span></tt>
attribute. In this example, we are defining
new tables that have yet to be created in our SQLite database, so one helpful feature
the <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 offers is the ability to issue CREATE TABLE statements
to the database for all tables that don’t yet exist. We illustrate this
by calling the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-meth docutils literal"><span class="pre">MetaData.create_all()</span></tt></a> method, passing in our <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>
as a source of database connectivity. We will see that special commands are
first emitted to check for the presence of the <tt class="docutils literal"><span class="pre">users</span></tt> table, and following that
the actual <tt class="docutils literal"><span class="pre">CREATE</span> <span class="pre">TABLE</span></tt> statement:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='show_sql'>PRAGMA table_info("users")
()
CREATE TABLE users (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
password VARCHAR,
PRIMARY KEY (id)
)
()
COMMIT</div></pre></div>
</div>
<div class="topic">
<p class="topic-title first">Minimal Table Descriptions vs. Full Descriptions</p>
<p>Users familiar with the syntax of CREATE TABLE may notice that the
VARCHAR columns were generated without a length; on SQLite and Postgresql,
this is a valid datatype, but on others, it’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="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="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> generated via our declarative
mapping is therefore:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'users'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s">'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">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="n">fullname</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="n">password</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">12</span><span class="p">))</span>
<span class="k">def</span> <span class="nf">__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="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</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="bp">self</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="n">password</span>
<span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="s">"<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>We include this more verbose table definition separately
to highlight the difference between a minimal construct geared primarily
towards in-Python usage only, versus one that will be used to emit CREATE
TABLE statements on a particular set of backends with more stringent
requirements.</p>
</div>
</div>
<div class="section" id="create-an-instance-of-the-mapped-class">
<h2>Create an Instance of the Mapped Class<a class="headerlink" href="#create-an-instance-of-the-mapped-class" title="Permalink to this headline">¶</a></h2>
<p>With mappings complete, let’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 with a value of <tt class="docutils literal"><span class="pre">None</span></tt> on our <tt class="docutils literal"><span class="pre">User</span></tt> instance due to the <tt class="docutils literal"><span class="pre">id</span></tt>
column we declared in our mapping. By
default, the ORM creates class attributes for all columns present
in the table being mapped. These class attributes exist as
<a class="reference external" href="http://docs.python.org/howto/descriptor.html">Python descriptors</a>, and
define <strong>instrumentation</strong> for the mapped class. The
functionality of this instrumentation includes the ability to fire on change
events, track modifications, and to 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="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 class="topic">
<p class="topic-title first">The default <tt class="docutils literal"><span class="pre">__init__()</span></tt> method</p>
<p>Note that in our <tt class="docutils literal"><span class="pre">User</span></tt> example we supplied an <tt class="docutils literal"><span class="pre">__init__()</span></tt> method,
which receives <tt class="docutils literal"><span class="pre">name</span></tt>, <tt class="docutils literal"><span class="pre">fullname</span></tt> and <tt class="docutils literal"><span class="pre">password</span></tt> as positional arguments.
The Declarative system supplies for us a default constructor if one is
not already present, which accepts keyword arguments of the same name
as that of the mapped attributes. Below we define <tt class="docutils literal"><span class="pre">User</span></tt> without
specifying a constructor:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'users'</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="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="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="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></pre></div>
</div>
<p>Our <tt class="docutils literal"><span class="pre">User</span></tt> class above will make usage of the default constructor, and provide
<tt class="docutils literal"><span class="pre">id</span></tt>, <tt class="docutils literal"><span class="pre">name</span></tt>, <tt class="docutils literal"><span class="pre">fullname</span></tt>, and <tt class="docutils literal"><span class="pre">password</span></tt> as keyword arguments:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">u1</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</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="n">password</span><span class="o">=</span><span class="s">'foobar'</span><span class="p">)</span></pre></div>
</div>
</div>
</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 class="topic">
<p class="topic-title first">Session Creational Patterns</p>
<p>The business of acquiring 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> has a good deal of variety based
on the variety of types of applications and frameworks out there.
Keep in mind the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is just a workspace for your objects,
local to a particular database connection - if you think of
an application thread as a guest at a dinner party, the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>
is the guest’s plate and the objects it holds are the food
(and the database...the kitchen?)! Hints on
how <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 integrated into an application are at
<a class="reference internal" href="session.html#session-faq"><em>Session Frequently Asked Questions</em></a>.</p>
</div>
</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 <strong>pending</strong>; no SQL has yet been issued
and the object is not yet represented by a row in the database. The
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> will issue the SQL to persist <tt class="docutils literal"><span class="pre">Ed</span>
<span class="pre">Jones</span></tt> as soon as is needed, using a process known as a <strong>flush</strong>. If we
query the database for <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt>, all pending information will first be
flushed, and the query is issued immediately thereafter.</p>
<p>For example, below we create a new <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object
which loads instances of <tt class="docutils literal"><span class="pre">User</span></tt>. We “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 ? OFFSET ?
('ed', 1, 0)</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 <a class="reference external" href="http://martinfowler.com/eaaCatalog/identityMap.html">identity map</a>
and ensures that
all operations upon a particular row within a
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> operate upon the same set of data.
Once an object with a particular primary key is present in the
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>, all SQL queries on that
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> will always return the same Python
object for that particular primary key; it also will raise an error if an
attempt is made to place a second, already-persisted object with the same
primary key within the session.</p>
<p>We can add more <tt class="docutils literal"><span class="pre">User</span></tt> objects at once using
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.add_all" title="sqlalchemy.orm.session.Session.add_all"><tt class="xref py py-func docutils literal"><span class="pre">add_all()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="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 <a class="reference internal" href="session.html"><em>Using the Session</em></a>.</p>
<div class="topic">
<p class="topic-title first">Session Object States</p>
<p>As our <tt class="docutils literal"><span class="pre">User</span></tt> object moved from being outside the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>, to
inside the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> without a primary key, to actually being
inserted, it moved between three out of four
available “object states” - <strong>transient</strong>, <strong>pending</strong>, and <strong>persistent</strong>.
Being aware of these states and what they mean is always a good idea -
be sure to read <a class="reference internal" href="session.html#session-object-states"><em>Quickie Intro to Object States</em></a> for a quick overview.</p>
</div>
</div>
<div class="section" id="rolling-back">
<h2>Rolling Back<a class="headerlink" href="#rolling-back" title="Permalink to this headline">¶</a></h2>
<p>Since the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> works within a transaction,
we can roll back changes made too. Let’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> object is created using the
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-class docutils literal"><span class="pre">query()</span></tt></a> method on
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>. This function takes a variable
number of arguments, which can be any combination of classes and
class-instrumented descriptors. Below, we indicate a
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> which loads <tt class="docutils literal"><span class="pre">User</span></tt> instances. When
evaluated in an iterative context, the list of <tt class="docutils literal"><span class="pre">User</span></tt> objects present is
returned:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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 of individual column expressions 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, which is available from
any <a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">ColumnElement</span></tt></a>-derived object, as well as any class attribute which
is mapped to one (such as <tt class="docutils literal"><span class="pre">User.name</span></tt>):</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'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="p">(</span><span class="n">row</span><span class="o">.</span><span class="n">name_label</span><span class="p">)</span>
<div class='popup_sql'>SELECT users.name AS name_label
FROM users
()</div><span class="n">ed</span>
<span class="n">wendy</span>
<span class="n">mary</span>
<span class="n">fred</span></pre></div>
</div>
<p>The name given to a full entity such as <tt class="docutils literal"><span class="pre">User</span></tt>, assuming that multiple
entities are present in the call to <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-meth docutils literal"><span class="pre">query()</span></tt></a>, can be controlled using
<a class="reference internal" href="query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><tt class="xref py py-class docutils literal"><span class="pre">aliased</span></tt></a> :</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="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">row</span><span class="o">.</span><span class="n">user_alias</span>
<div class='popup_sql'>SELECT user_alias.id AS user_alias_id,
user_alias.name AS user_alias_name,
user_alias.fullname AS user_alias_fullname,
user_alias.password AS user_alias_password
FROM users AS user_alias
()</div><span class="o"><</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="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>
<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>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 ? OFFSET ?
(2, 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="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="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="o">==</span><span class="s">'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 <strong>generative</strong>, meaning
that most method calls return a new <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>
object upon which further criteria may be added. For example, to query for
users named “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="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">'ed'</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="o">==</span><span class="s">'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 ? OFFSET ?
('%ed', 1, 0)</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="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="s">"id<224"</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">order_by</span><span class="p">(</span><span class="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 class="topic">
<p class="topic-title first">Pros and Cons of Literal SQL</p>
<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> is constructed like the rest of SQLAlchemy, in that it tries
to always allow “falling back” to a less automated, lower level approach to things.
Accepting strings for all SQL fragments is a big part of that, so that
you can bypass the need to organize SQL constructs if you know specifically
what string output you’d like.
But when using literal strings, 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> no longer knows anything about
that part of the SQL construct being emitted, and has no ability to
<strong>transform</strong> it to adapt to new contexts.</p>
<p>For example, suppose we selected <tt class="docutils literal"><span class="pre">User</span></tt> objects and ordered by the <tt class="docutils literal"><span class="pre">name</span></tt>
column, using a string to indicate <tt class="docutils literal"><span class="pre">name</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">q</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">q</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s">"name"</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
FROM users ORDER BY name
()</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="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s">u'fred'</span><span class="p">),</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s">u'mary'</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">)]</span></pre></div>
</div>
<p>Perfectly fine. But suppose, before we got a hold of 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>,
some sophisticated transformations were applied to it, such as below
where we use <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.from_self" title="sqlalchemy.orm.query.Query.from_self"><tt class="xref py py-meth docutils literal"><span class="pre">from_self()</span></tt></a>, a particularly advanced
method, to retrieve pairs of user names with
different numbers of characters:</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">func</span>
<span class="gp">>>> </span><span class="n">ua</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="gp">>>> </span><span class="n">q</span> <span class="o">=</span> <span class="n">q</span><span class="o">.</span><span class="n">from_self</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="n">ua</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </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="n">ua</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="nb">filter</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">length</span><span class="p">(</span><span class="n">ua</span><span class="o">.</span><span class="n">name</span><span class="p">)</span> <span class="o">!=</span> <span class="n">func</span><span class="o">.</span><span class="n">length</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">))</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> now represents a select from a subquery, where
<tt class="docutils literal"><span class="pre">User</span></tt> is represented twice both inside and outside of the subquery.
Telling 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> to order by “name” doesn’t really give
us much guarantee which “name” it’s going to order on. In this
case it assumes “name” is against the outer “aliased” <tt class="docutils literal"><span class="pre">User</span></tt> construct:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">q</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s">"name"</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT anon_1.users_id AS anon_1_users_id,
anon_1.users_name AS anon_1_users_name,
users_1.name AS users_1_name
FROM (SELECT users.id AS users_id, users.name AS users_name
FROM users) AS anon_1, users AS users_1
WHERE anon_1.users_name < users_1.name
AND length(users_1.name) != length(anon_1.users_name)
ORDER BY name
()</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="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'ed'</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'ed'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">),</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s">u'mary'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">),</span> <span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s">u'fred'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">)]</span></pre></div>
</div>
<p>Only if we use the SQL element directly, in this case <tt class="docutils literal"><span class="pre">User.name</span></tt>
or <tt class="docutils literal"><span class="pre">ua.name</span></tt>, do we give <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> enough information to know
for sure which “name” we’d like to order on, where we can see we get different results
for each:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">q</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">ua</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 anon_1.users_id AS anon_1_users_id,
anon_1.users_name AS anon_1_users_name,
users_1.name AS users_1_name
FROM (SELECT users.id AS users_id, users.name AS users_name
FROM users) AS anon_1, users AS users_1
WHERE anon_1.users_name < users_1.name
AND length(users_1.name) != length(anon_1.users_name)
ORDER BY users_1.name
()</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="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'ed'</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'ed'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">),</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s">u'mary'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">),</span> <span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s">u'fred'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">)]</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">q</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">name</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT anon_1.users_id AS anon_1_users_id,
anon_1.users_name AS anon_1_users_name,
users_1.name AS users_1_name
FROM (SELECT users.id AS users_id, users.name AS users_name
FROM users) AS anon_1, users AS users_1
WHERE anon_1.users_name < users_1.name
AND length(users_1.name) != length(anon_1.users_name)
ORDER BY anon_1.users_name
()</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="s">u'wendy'</span><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="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'ed'</span><span class="p">,</span> <span class="s">u'fred'</span><span class="p">),</span> <span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s">u'fred'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">),</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s">u'mary'</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">)]</span></pre></div>
</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(*) AS count_1
FROM (SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)</div><span class="mi">2</span></pre></div>
</div>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal"><span class="pre">count()</span></tt></a> method is used to determine
how many rows the SQL statement would return. Looking
at the generated SQL above, SQLAlchemy always places whatever it is we are
querying into a subquery, then counts the rows from that. In some cases
this can be reduced to a simpler <tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">count(*)</span> <span class="pre">FROM</span> <span class="pre">table</span></tt>, however
modern versions of SQLAlchemy don’t try to guess when this is appropriate,
as the exact SQL can be emitted using more explicit means.</p>
<p>For situations where the “thing to be counted” needs
to be indicated specifically, we can specify the “count” function
directly using the expression <tt class="docutils literal"><span class="pre">func.count()</span></tt>, available from the
<a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-attr docutils literal"><span class="pre">func</span></tt></a> construct. Below we
use it to return the count of each distinct user name:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="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>
<p>To achieve our simple <tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">count(*)</span> <span class="pre">FROM</span> <span class="pre">table</span></tt>, we can apply it as:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="s">'*'</span><span class="p">))</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(?) AS count_1
FROM users
('*',)</div><span class="mi">4</span></pre></div>
</div>
<p>The usage of <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><tt class="xref py py-meth docutils literal"><span class="pre">select_from()</span></tt></a> can be removed if we express the count in terms
of the <tt class="docutils literal"><span class="pre">User</span></tt> primary key directly:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">))</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(users.id) AS count_1
FROM users
()</div><span class="mi">4</span></pre></div>
</div>
</div>
</div>
<div class="section" id="building-a-relationship">
<h2>Building a Relationship<a class="headerlink" href="#building-a-relationship" title="Permalink to this headline">¶</a></h2>
<p>Let’s consider how a second table, related to <tt class="docutils literal"><span class="pre">User</span></tt>, can be mapped and
queried. Users in our system
can store any number of email addresses associated with their username. This
implies a basic one to many association from the <tt class="docutils literal"><span class="pre">users</span></tt> to a new
table which stores email addresses, which we will call <tt class="docutils literal"><span class="pre">addresses</span></tt>. Using
declarative, we define this table along with its mapped class, <tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="s">"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 the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> construct, which is a
directive applied to <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> that indicates that values in this
column should be <strong>constrained</strong> to be values present in the named remote
column. This is a core feature of relational databases, and is the “glue” that
transforms an otherwise unconnected collection of tables to have rich
overlapping relationships. The <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> above expresses that
values in the <tt class="docutils literal"><span class="pre">addresses.user_id</span></tt> column should be constrained to
those values in the <tt class="docutils literal"><span class="pre">users.id</span></tt> column, i.e. its primary key.</p>
<p>A second directive, known as <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>,
tells the ORM that the <tt class="docutils literal"><span class="pre">Address</span></tt> class itself should be linked
to the <tt class="docutils literal"><span class="pre">User</span></tt> class, using the attribute <tt class="docutils literal"><span class="pre">Address.user</span></tt>.
<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> uses the foreign key
relationships between the two tables to determine the nature of
this linkage, determining that <tt class="docutils literal"><span class="pre">Address.user</span></tt> will be <strong>many-to-one</strong>.
A subdirective of <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> called <a class="reference internal" href="relationships.html#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> is
placed inside of <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, providing details about
the relationship as expressed in reverse, that of a collection of <tt class="docutils literal"><span class="pre">Address</span></tt>
objects on <tt class="docutils literal"><span class="pre">User</span></tt> referenced by <tt class="docutils literal"><span class="pre">User.addresses</span></tt>. The reverse
side of a many-to-one relationship is always <strong>one-to-many</strong>.
A full catalog of available <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> configurations
is at <a class="reference internal" href="relationships.html#relationship-patterns"><em>Basic Relational Patterns</em></a>.</p>
<p>The two complementing relationships <tt class="docutils literal"><span class="pre">Address.user</span></tt> and <tt class="docutils literal"><span class="pre">User.addresses</span></tt>
are referred to as a <strong>bidirectional relationship</strong>, and is a key
feature of the SQLAlchemy ORM. The section <a class="reference internal" href="relationships.html#relationships-backref"><em>Linking Relationships with Backref</em></a>
discusses the “backref” feature in detail.</p>
<p>Arguments to <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> which concern the remote class
can be specified using strings, assuming the Declarative system is in
use. Once all mappings are complete, these strings are evaluated
as Python expressions in order to produce the actual argument, in the
above case the <tt class="docutils literal"><span class="pre">User</span></tt> class. The names which are allowed during
this evaluation include, among other things, the names of all classes
which have been created in terms of the declared base. Below we illustrate creation
of the same “addresses/user” bidirectional relationship in terms of <tt class="docutils literal"><span class="pre">User</span></tt> instead of
<tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c"># ....</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"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>See the docstring for <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> for more detail on argument style.</p>
<div class="topic">
<p class="topic-title first">Did you know ?</p>
<ul class="simple">
<li>a FOREIGN KEY constraint in most (though not all) relational databases can
only link to a primary key column, or a column that has a UNIQUE constraint.</li>
<li>a FOREIGN KEY constraint that refers to a multiple column primary key, and itself
has multiple columns, is known as a “composite foreign key”. It can also
reference a subset of those columns.</li>
<li>FOREIGN KEY columns can automatically update themselves, in response to a change
in the referenced column or row. This is known as the CASCADE <em>referential action</em>,
and is a built in function of the relational database.</li>
<li>FOREIGN KEY can refer to its own table. This is referred to as a “self-referential”
foreign key.</li>
<li>Read more about foreign keys at <a class="reference external" href="http://en.wikipedia.org/wiki/Foreign_key">Foreign Key - Wikipedia</a>.</li>
</ul>
</div>
<p>We’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">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>PRAGMA table_info("users")
()
PRAGMA table_info("addresses")
()
CREATE TABLE addresses (
id INTEGER NOT NULL,
email_address VARCHAR NOT NULL,
user_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT</div></pre></div>
</div>
</div>
<div class="section" id="working-with-related-objects">
<h2>Working with Related Objects<a class="headerlink" href="#working-with-related-objects" title="Permalink to this headline">¶</a></h2>
<p>Now when we create a <tt class="docutils literal"><span class="pre">User</span></tt>, a blank <tt class="docutils literal"><span class="pre">addresses</span></tt> collection will be
present. Various collection types, such as sets and dictionaries, are possible
here (see <a class="reference internal" href="collections.html#custom-collections"><em>Customizing Collection Access</em></a> for details), but by
default, the collection is a Python list.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="o">...</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="o">...</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 behavior occurs
based on attribute on-change events and is evaluated in Python, without
using any SQL:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="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. We’ll cover ways
to optimize the loading of this collection in a bit.</p>
</div>
<div class="section" id="querying-with-joins">
<span id="ormtutorial-joins"></span><h2>Querying with Joins<a class="headerlink" href="#querying-with-joins" title="Permalink to this headline">¶</a></h2>
<p>Now that we have two tables, we can show some more features of <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>,
specifically how to create queries that deal with both tables at the same time.
The <a class="reference external" href="http://en.wikipedia.org/wiki/Join_%28SQL%29">Wikipedia page on SQL JOIN</a> offers a good introduction to
join techniques, several of which we’ll illustrate here.</p>
<p>To construct a simple implicit join between <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt>,
we can use <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal"><span class="pre">Query.filter()</span></tt></a> to equate their related columns together.
Below we load the <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt> entities at once using this method:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">u</span><span class="p">,</span> <span class="n">a</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">all</span><span class="p">():</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">u</span><span class="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>The actual SQL JOIN syntax, on the other hand, is most easily achieved using the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a>
method:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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="o">...</span> <span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)</div><span class="p">[</span><span class="o"><</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">Query.join()</span></tt></a> knows how to join between <tt class="docutils literal"><span class="pre">User</span></tt>
and <tt class="docutils literal"><span class="pre">Address</span></tt> because there’s only one foreign key between them. If there
were no foreign keys, or several, <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a>
works better when one of the following forms are used:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span> <span class="c"># explicit condition</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span> <span class="c"># specify relationship from left to right</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span> <span class="c"># same, with explicit target</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">)</span> <span class="c"># same, using a string</span></pre></div>
</div>
<p>As you would expect, the same idea is used for “outer” joins, using the
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.outerjoin" title="sqlalchemy.orm.query.Query.outerjoin"><tt class="xref py py-meth docutils literal"><span class="pre">outerjoin()</span></tt></a> function:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span> <span class="c"># LEFT OUTER JOIN</span></pre></div>
</div>
<p>The reference documentation for <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> contains detailed information
and examples of the calling styles accepted by this method; <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a>
is an important method at the center of usage for any SQL-fluent application.</p>
<div class="section" id="using-aliases">
<span id="ormtutorial-aliases"></span><h3>Using Aliases<a class="headerlink" href="#using-aliases" title="Permalink to this headline">¶</a></h3>
<p>When querying across multiple tables, if the same table needs to be referenced
more than once, SQL typically requires that the table be <em>aliased</em> with
another name, so that it can be distinguished against other occurrences of
that table. The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> supports this most
explicitly using the <a class="reference internal" href="query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><tt class="xref py py-attr docutils literal"><span class="pre">aliased</span></tt></a> construct. Below we join to the <tt class="docutils literal"><span class="pre">Address</span></tt>
entity twice, to locate a user who has two distinct email addresses at the
same time:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="o">.</span>\
<span class="o">...</span> <span class="n">join</span><span class="p">(</span><span class="n">adalias2</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">adalias1</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'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 <a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a>
construct, which are described in <a class="reference internal" href="../core/tutorial.html"><em>SQL Expression Language Tutorial</em></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </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="gp">... </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="gp">... </span> <span class="n">group_by</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">subquery</span><span class="p">()</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">func</span></tt> keyword generates SQL functions, and the <tt class="docutils literal"><span class="pre">subquery()</span></tt> method on
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> produces a SQL expression construct
representing a SELECT statement embedded within an alias (it’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="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">'j25@yahoo.com'</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">subquery</span><span class="p">()</span>
<span class="o">>>></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="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 <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">()):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id)
()</div><span class="n">jack</span></pre></div>
</div>
<p><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a> takes criterion as well, to limit the rows matched:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></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><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.has" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.has"><tt class="xref py py-meth docutils literal"><span class="pre">has()</span></tt></a> is the same operator as
<a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a> for many-to-one relationships
(note the <tt class="docutils literal"><span class="pre">~</span></tt> operator here too, which means “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="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="o">~</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'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 - each one
is linked to its API documentation which includes full details on usage
and behavior:</p>
<ul>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__"><tt class="xref py py-meth docutils literal"><span class="pre">__eq__()</span></tt></a> (many-to-one “equals” comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">==</span> <span class="n">someuser</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__ne__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__ne__"><tt class="xref py py-meth docutils literal"><span class="pre">__ne__()</span></tt></a> (many-to-one “not equals” comparison):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">!=</span> <span class="n">someuser</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">IS NULL (many-to-one comparison, also uses <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.__eq__"><tt class="xref py py-meth docutils literal"><span class="pre">__eq__()</span></tt></a>):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">==</span> <span class="bp">None</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains"><tt class="xref py py-meth docutils literal"><span class="pre">contains()</span></tt></a> (used for one-to-many collections):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="n">someaddress</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.any"><tt class="xref py py-meth docutils literal"><span class="pre">any()</span></tt></a> (used for collections):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">'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"><a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.has" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator.has"><tt class="xref py py-meth docutils literal"><span class="pre">has()</span></tt></a> (used for scalar references):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_parent" title="sqlalchemy.orm.query.Query.with_parent"><tt class="xref py py-meth docutils literal"><span class="pre">Query.with_parent()</span></tt></a> (used for any relationship):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">with_parent</span><span class="p">(</span><span class="n">someuser</span><span class="p">,</span> <span class="s">'addresses'</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
</div>
</div>
<div class="section" id="eager-loading">
<h2>Eager Loading<a class="headerlink" href="#eager-loading" title="Permalink to this headline">¶</a></h2>
<p>Recall earlier that we illustrated a <strong>lazy loading</strong> operation, when
we accessed the <tt class="docutils literal"><span class="pre">User.addresses</span></tt> collection of a <tt class="docutils literal"><span class="pre">User</span></tt> and SQL
was emitted. If you want to reduce the number of queries (dramatically, in many cases),
we can apply an <strong>eager load</strong> to the query operation. SQLAlchemy
offers three types of eager loading, two of which are automatic, and a third
which involves custom criterion. All three are usually invoked via functions known
as <strong>query options</strong> which give additional instructions to the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> on how
we would like various attributes to be loaded, via the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.options" title="sqlalchemy.orm.query.Query.options"><tt class="xref py py-meth docutils literal"><span class="pre">Query.options()</span></tt></a> method.</p>
<div class="section" id="subquery-load">
<h3>Subquery Load<a class="headerlink" href="#subquery-load" title="Permalink to this headline">¶</a></h3>
<p>In this case we’d like to indicate that <tt class="docutils literal"><span class="pre">User.addresses</span></tt> should load eagerly.
A good choice for loading a set of objects as well as their related collections
is the <a class="reference internal" href="loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">orm.subqueryload()</span></tt></a> option, which emits a second SELECT statement
that fully loads the collections associated with the results just loaded.
The name “subquery” originates from the fact that the SELECT statement
constructed directly via the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> is re-used, embedded as a subquery
into a SELECT against the related table. This is a little elaborate but
very easy to use:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">subqueryload</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'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
FROM users
WHERE users.name = ?
('jack',)
SELECT addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id,
anon_1.users_id AS anon_1_users_id
FROM (SELECT users.id AS users_id
FROM users WHERE users.name = ?) AS anon_1
JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id, addresses.id
('jack',)</div><span class="o">>>></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>
</div>
<div class="section" id="joined-load">
<h3>Joined Load<a class="headerlink" href="#joined-load" title="Permalink to this headline">¶</a></h3>
<p>The other automatic eager loading function is more well known and is called
<a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">orm.joinedload()</span></tt></a>. This style of loading emits a JOIN, by default
a LEFT OUTER JOIN, so that the lead object as well as the related object
or collection is loaded in one step. We illustrate loading the same
<tt class="docutils literal"><span class="pre">addresses</span></tt> collection in this way - note that even though the <tt class="docutils literal"><span class="pre">User.addresses</span></tt>
collection on <tt class="docutils literal"><span class="pre">jack</span></tt> is actually populated right now, the query
will emit the extra join regardless:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'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>Note that even though the OUTER JOIN resulted in two rows, we still only got
one instance of <tt class="docutils literal"><span class="pre">User</span></tt> back. This is because <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> applies a “uniquing”
strategy, based on object identity, to the returned entities. This is specifically
so that joined eager loading can be applied without affecting the query results.</p>
<p>While <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> has been around for a long time, <a class="reference internal" href="loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a>
is a newer form of eager loading. <a class="reference internal" href="loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a> tends to be more appropriate
for loading related collections while <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> tends to be better suited
for many-to-one relationships, due to the fact that only one row is loaded
for both the lead and the related object.</p>
<div class="topic">
<p class="topic-title first"><tt class="docutils literal"><span class="pre">joinedload()</span></tt> is not a replacement for <tt class="docutils literal"><span class="pre">join()</span></tt></p>
<p>The join created by <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> is anonymously aliased such that
it <strong>does not affect the query results</strong>. An <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.order_by" title="sqlalchemy.orm.query.Query.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">Query.order_by()</span></tt></a>
or <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal"><span class="pre">Query.filter()</span></tt></a> call <strong>cannot</strong> reference these aliased
tables - so-called “user space” joins are constructed using
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a>. The rationale for this is that <a class="reference internal" href="loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> is only
applied in order to affect how related objects or collections are loaded
as an optimizing detail - it can be added or removed with no impact
on actual results. See the section <a class="reference internal" href="loading.html#zen-of-eager-loading"><em>The Zen of Eager Loading</em></a> for
a detailed description of how this is used.</p>
</div>
</div>
<div class="section" id="explicit-join-eagerload">
<h3>Explicit Join + Eagerload<a class="headerlink" href="#explicit-join-eagerload" title="Permalink to this headline">¶</a></h3>
<p>A third style of eager loading is when we are constructing a JOIN explicitly in
order to locate the primary rows, and would like to additionally apply the extra
table to a related object or collection on the primary object. This feature
is supplied via the <a class="reference internal" href="loading.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">orm.contains_eager()</span></tt></a> function, and is most
typically useful for pre-loading the many-to-one object on a query that needs
to filter on that same object. Below we illustrate loading an <tt class="docutils literal"><span class="pre">Address</span></tt>
row as well as the related <tt class="docutils literal"><span class="pre">User</span></tt> object, filtering on the <tt class="docutils literal"><span class="pre">User</span></tt> named
“jack” and using <a class="reference internal" href="loading.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">orm.contains_eager()</span></tt></a> to apply the “user” columns to the <tt class="docutils literal"><span class="pre">Address.user</span></tt>
attribute:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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="n">jacks_addresses</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'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="o">...</span> <span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password,
addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id
FROM addresses JOIN users ON users.id = addresses.user_id
WHERE users.name = ?
('jack',)</div><span class="o">>>></span> <span class="n">jacks_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>
<span class="o">>>></span> <span class="n">jacks_addresses</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span><span class="o">.</span><span class="n">user</span>
<span class="o"><</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>For more information on eager loading, including how to configure various forms
of loading by default, see the section <a class="reference internal" href="loading.html"><em>Relationship Loading Techniques</em></a>.</p>
</div>
</div>
<div class="section" id="deleting">
<h2>Deleting<a class="headerlink" href="#deleting" title="Permalink to this headline">¶</a></h2>
<p>Let’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(*) AS count_1
FROM (SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name = ?) AS anon_1
('jack',)</div><span class="mi">0</span></pre></div>
</div>
<p>So far, so good. How about Jack’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(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">2</span></pre></div>
</div>
<p>Uh oh, they’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 - we’ll close the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>and use a new <a class="reference internal" href="extensions/declarative.html#sqlalchemy.ext.declarative.declarative_base" title="sqlalchemy.ext.declarative.declarative_base"><tt class="xref py py-func docutils literal"><span class="pre">declarative_base()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span></pre></div>
</div>
<p>Next we’ll declare the <tt class="docutils literal"><span class="pre">User</span></tt> class, adding in the <tt class="docutils literal"><span class="pre">addresses</span></tt> relationship
including the cascade configuration (we’ll leave the constructor out too):</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="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="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">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="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>Then we recreate <tt class="docutils literal"><span class="pre">Address</span></tt>, noting that in this case we’ve created the <tt class="docutils literal"><span class="pre">Address.user</span></tt> relationship
via the <tt class="docutils literal"><span class="pre">User</span></tt> class already:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'addresses'</span>
<span class="gp">... </span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">email_address</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'users.id'</span><span class="p">))</span>
<span class="gp">...</span>
<span class="gp">... </span> <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="gp">... </span> <span class="k">return</span> <span class="s">"<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>Now when we load Jack (below using <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.get" title="sqlalchemy.orm.query.Query.get"><tt class="xref py py-meth docutils literal"><span class="pre">get()</span></tt></a>, which loads by primary key),
removing an address from 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(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">1</span></pre></div>
</div>
<p>Deleting Jack will delete both Jack and 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(*) AS count_1
FROM (SELECT users.id AS users_id,
users.name AS users_name,
users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name = ?) AS anon_1
('jack',)</div><span class="mi">0</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></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(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">0</span></pre></div>
</div>
<div class="topic">
<p class="topic-title first">More on Cascades</p>
<p>Further detail on configuration of cascades is at <a class="reference internal" href="session.html#unitofwork-cascades"><em>Cascades</em></a>.
The cascade functionality can also integrate smoothly with
the <tt class="docutils literal"><span class="pre">ON</span> <span class="pre">DELETE</span> <span class="pre">CASCADE</span></tt> functionality of the relational database.
See <a class="reference internal" href="collections.html#passive-deletes"><em>Using Passive Deletes</em></a> for details.</p>
</div>
</div>
</div>
<div class="section" id="building-a-many-to-many-relationship">
<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>For a plain many-to-many, we need to create an un-mapped <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
to serve as the association table. This looks like the following:</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">Text</span>
<span class="gp">>>> </span><span class="c"># association table</span>
<span class="gp">>>> </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">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s">'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="gp">... </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="gp">... </span><span class="p">)</span></pre></div>
</div>
<p>Above, we can see declaring 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> directly is a little different
than declaring a mapped class. <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 a constructor function, so
each individual <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> argument is separated by a comma. The
<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> object is also given its name explicitly, rather than it being
taken from an assigned attribute name.</p>
<p>Next we define <tt class="docutils literal"><span class="pre">BlogPost</span></tt> and <tt class="docutils literal"><span class="pre">Keyword</span></tt>, with a <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> linked
via the <tt class="docutils literal"><span class="pre">post_keywords</span></tt> table:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">class</span> <span class="nc">BlogPost</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'posts'</span>
<span class="gp">...</span>
<span class="gp">... </span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'users.id'</span><span class="p">))</span>
<span class="gp">... </span> <span class="n">headline</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">255</span><span class="p">),</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">body</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Text</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span> <span class="c"># many to many BlogPost<->Keyword</span>
<span class="gp">... </span> <span class="n">keywords</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">'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="gp">...</span>
<span class="gp">... </span> <span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">headline</span><span class="p">,</span> <span class="n">body</span><span class="p">,</span> <span class="n">author</span><span class="p">):</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">author</span> <span class="o">=</span> <span class="n">author</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">headline</span> <span class="o">=</span> <span class="n">headline</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">body</span> <span class="o">=</span> <span class="n">body</span>
<span class="gp">...</span>
<span class="gp">... </span> <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="gp">... </span> <span class="k">return</span> <span class="s">"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="gp">>>> </span><span class="k">class</span> <span class="nc">Keyword</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'keywords'</span>
<span class="gp">...</span>
<span class="gp">... </span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">keyword</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">unique</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </span> <span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">keyword</span><span class="p">):</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">keyword</span> <span class="o">=</span> <span class="n">keyword</span></pre></div>
</div>
<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>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">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>PRAGMA table_info("users")
()
PRAGMA table_info("addresses")
()
PRAGMA table_info("posts")
()
PRAGMA table_info("keywords")
()
PRAGMA table_info("post_keywords")
()
CREATE TABLE posts (
id INTEGER NOT NULL,
user_id INTEGER,
headline VARCHAR(255) NOT NULL,
body TEXT,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT
CREATE TABLE keywords (
id INTEGER NOT NULL,
keyword VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE (keyword)
)
()
COMMIT
CREATE TABLE post_keywords (
post_id INTEGER,
keyword_id INTEGER,
FOREIGN KEY(post_id) REFERENCES posts (id),
FOREIGN KEY(keyword_id) REFERENCES keywords (id)
)
()
COMMIT</div></pre></div>
</div>
<p>Usage is not too different from what we’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="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="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="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="o">...</span> <span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>INSERT INTO keywords (keyword) VALUES (?)
('wendy',)
INSERT INTO keywords (keyword) VALUES (?)
('firstpost',)
INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)
(2, "Wendy's Blog Post", 'This is a test')
INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)
((1, 1), (1, 2))
SELECT posts.id AS posts_id,
posts.user_id AS posts_user_id,
posts.headline AS posts_headline,
posts.body AS posts_body
FROM posts
WHERE EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id
AND keywords.id = post_keywords.keyword_id
AND keywords.keyword = ?)
('firstpost',)</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">"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="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">author</span><span class="o">==</span><span class="n">wendy</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="s">'firstpost'</span><span class="p">))</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT posts.id AS posts_id,
posts.user_id AS posts_user_id,
posts.headline AS posts_headline,
posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id
AND keywords.id = post_keywords.keyword_id
AND keywords.keyword = ?))
(2, 'firstpost')</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">"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="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="o">...</span> <span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT posts.id AS posts_id,
posts.user_id AS posts_user_id,
posts.headline AS posts_headline,
posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id
AND keywords.id = post_keywords.keyword_id
AND keywords.keyword = ?))
(2, 'firstpost')</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">"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 id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="index.html" title="previous chapter">SQLAlchemy ORM</a>
Next:
<a href="mapper_config.html" title="next chapter">Mapper Configuration</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2012, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
</div>
</div>
</div>
</body>
</html>