← Back to branch summary

~ubuntu-branches/debian/jessie/sqlalchemy/jessie

« back to all changes in this revision

Viewing changes to doc/core/tutorial.html

  • Committer: Package Import Robot
  • Author(s): Piotr Ożarowski, Jakub Wilk, Piotr Ożarowski
  • Date: 2013-07-06 20:53:52 UTC
  • mfrom: (1.4.23) (16.1.17 experimental)
  • Revision ID: package-import@ubuntu.com-20130706205352-ryppl1eto3illd79
Tags: 0.8.2-1
[ Jakub Wilk ]
* Use canonical URIs for Vcs-* fields.

[ Piotr Ożarowski ]
* New upstream release
* Upload to unstable
* Build depend on python3-all instead of -dev, extensions are not built for
  Python 3.X 

Show diffs side-by-side

added added

removed removed

Lines of Context:
doc/core/tutorial.html
10
10
    
11
11
                SQL Expression Language Tutorial
12
12
             — 
13
 
    SQLAlchemy 0.7 Documentation
 
13
    SQLAlchemy 0.8 Documentation
14
14
 
15
15
        </title>
16
16
        
20
20
    <script type="text/javascript">
21
21
      var DOCUMENTATION_OPTIONS = {
22
22
          URL_ROOT:    '../',
23
 
          VERSION:     '0.7.9',
 
23
          VERSION:     '0.8.2',
24
24
          COLLAPSE_MODINDEX: false,
25
25
          FILE_SUFFIX: '.html'
26
26
      };
32
32
    <link rel="index" title="Index" href="../genindex.html" />
33
33
    <link rel="search" title="Search" href="../search.html" />
34
34
        <link rel="copyright" title="Copyright" href="../copyright.html" />
35
 
    <link rel="top" title="SQLAlchemy 0.7 Documentation" href="../index.html" />
 
35
    <link rel="top" title="SQLAlchemy 0.8 Documentation" href="../index.html" />
36
36
        <link rel="up" title="SQLAlchemy Core" href="index.html" />
37
37
        <link rel="next" title="SQL Statements and Expressions API" href="expression_api.html" />
38
38
        <link rel="prev" title="SQLAlchemy Core" href="index.html" />
55
55
 
56
56
 
57
57
<div id="docs-header">
58
 
    <h1>SQLAlchemy 0.7 Documentation</h1>
 
58
    <h1>SQLAlchemy 0.8 Documentation</h1>
59
59
 
60
60
    <div id="docs-search">
61
61
    Search:
67
67
    </div>
68
68
 
69
69
    <div id="docs-version-header">
70
 
        Release: <span class="version-num">0.7.9</span> | Release Date: October 1, 2012
 
70
        Release: <span class="version-num">0.8.2</span> | Release Date: July 3, 2013
71
71
 
72
72
 
73
73
    </div>
93
93
    </div>
94
94
 
95
95
    <div id="docs-navigation-banner">
96
 
        <a href="../index.html">SQLAlchemy 0.7 Documentation</a>
 
96
        <a href="../index.html">SQLAlchemy 0.8 Documentation</a>
97
97
                » <a href="index.html" title="SQLAlchemy Core">SQLAlchemy Core</a>
98
98
        » 
99
99
                SQL Expression Language Tutorial
121
121
<li><a class="reference internal" href="#executing">Executing</a></li>
122
122
<li><a class="reference internal" href="#executing-multiple-statements">Executing Multiple Statements</a></li>
123
123
<li><a class="reference internal" href="#selecting">Selecting</a></li>
124
 
<li><a class="reference internal" href="#operators">Operators</a></li>
 
124
<li><a class="reference internal" href="#operators">Operators</a><ul>
 
125
<li><a class="reference internal" href="#operator-customization">Operator Customization</a></li>
 
126
</ul>
 
127
</li>
125
128
<li><a class="reference internal" href="#conjunctions">Conjunctions</a></li>
126
129
<li><a class="reference internal" href="#using-text">Using Text</a></li>
127
130
<li><a class="reference internal" href="#using-aliases">Using Aliases</a></li>
128
131
<li><a class="reference internal" href="#using-joins">Using Joins</a></li>
129
 
<li><a class="reference internal" href="#intro-to-generative-selects">Intro to Generative Selects</a><ul>
130
 
<li><a class="reference internal" href="#transforming-a-statement">Transforming a Statement</a></li>
131
 
</ul>
132
 
</li>
133
132
<li><a class="reference internal" href="#everything-else">Everything Else</a><ul>
134
133
<li><a class="reference internal" href="#bind-parameter-objects">Bind Parameter Objects</a></li>
135
134
<li><a class="reference internal" href="#functions">Functions</a></li>
140
139
<li><a class="reference internal" href="#ordering-grouping-limiting-offset-ing">Ordering, Grouping, Limiting, Offset...ing...</a></li>
141
140
</ul>
142
141
</li>
143
 
<li><a class="reference internal" href="#inserts-and-updates">Inserts and Updates</a><ul>
 
142
<li><a class="reference internal" href="#inserts-updates-and-deletes">Inserts, Updates and Deletes</a><ul>
144
143
<li><a class="reference internal" href="#correlated-updates">Correlated Updates</a></li>
145
144
<li><a class="reference internal" href="#multiple-table-updates">Multiple Table Updates</a></li>
146
 
</ul>
147
 
</li>
148
145
<li><a class="reference internal" href="#deletes">Deletes</a></li>
 
146
<li><a class="reference internal" href="#matched-row-counts">Matched Row Counts</a></li>
 
147
</ul>
 
148
</li>
149
149
<li><a class="reference internal" href="#further-reference">Further Reference</a></li>
150
150
</ul>
151
151
</li>
215
215
prerequisites.</p>
216
216
<div class="section" id="version-check">
217
217
<h2>Version Check<a class="headerlink" href="#version-check" title="Permalink to this headline">¶</a></h2>
218
 
<p>A quick check to verify that we are on at least <strong>version 0.7</strong> of SQLAlchemy:</p>
 
218
<p>A quick check to verify that we are on at least <strong>version 0.8</strong> of SQLAlchemy:</p>
219
219
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">import</span> <span class="nn">sqlalchemy</span>
220
220
<span class="gp">&gt;&gt;&gt; </span><span class="n">sqlalchemy</span><span class="o">.</span><span class="n">__version__</span> 
221
 
<span class="go">0.7.0</span></pre></div>
 
221
<span class="go">0.8.0</span></pre></div>
222
222
</div>
223
223
</div>
224
224
<div class="section" id="connecting">
377
377
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span> 
378
378
<span class="go">&lt;sqlalchemy.engine.base.Connection object at 0x...&gt;</span></pre></div>
379
379
</div>
380
 
<p>The <a class="reference internal" href="connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> object represents an actively
 
380
<p>The <tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt> object represents an actively
381
381
checked out DBAPI connection resource. Lets feed it our
382
382
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> object and see what happens:</p>
383
383
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">ins</span><span class="p">)</span>
388
388
<p>So the INSERT statement was now issued to the database. Although we got
389
389
positional &#8220;qmark&#8221; bind parameters instead of &#8220;named&#8221; bind parameters in the
390
390
output. How come ? Because when executed, the
391
 
<a class="reference internal" href="connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> used the SQLite <strong>dialect</strong> to
 
391
<tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt> used the SQLite <strong>dialect</strong> to
392
392
help generate the statement; when we use the <tt class="docutils literal"><span class="pre">str()</span></tt> function, the statement
393
393
isn&#8217;t aware of this dialect, and falls back onto a default which uses named
394
394
parameters. We can view this manually as follows:</p>
397
397
<span class="go">&#39;INSERT INTO users (name, fullname) VALUES (?, ?)&#39;</span></pre></div>
398
398
</div>
399
399
<p>What about the <tt class="docutils literal"><span class="pre">result</span></tt> variable we got when we called <tt class="docutils literal"><span class="pre">execute()</span></tt> ? As
400
 
the SQLAlchemy <a class="reference internal" href="connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> object references a
 
400
the SQLAlchemy <tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt> object references a
401
401
DBAPI connection, the result, known as a
402
 
<a class="reference internal" href="connections.html#sqlalchemy.engine.base.ResultProxy" title="sqlalchemy.engine.base.ResultProxy"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></a> object, is analogous to the DBAPI
 
402
<tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt> object, is analogous to the DBAPI
403
403
cursor object. In the case of an INSERT, we can get important information from
404
404
it, such as the primary key values which were generated from our statement:</p>
405
405
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">result</span><span class="o">.</span><span class="n">inserted_primary_key</span>
411
411
value would have been used. In either case, SQLAlchemy always knows how to get
412
412
at a newly generated primary key value, even though the method of generating
413
413
them is different across different databases; each database&#8217;s
414
 
<a class="reference internal" href="internals.html#sqlalchemy.engine.base.Dialect" title="sqlalchemy.engine.base.Dialect"><tt class="xref py py-class docutils literal"><span class="pre">Dialect</span></tt></a> knows the specific steps needed to
 
414
<tt class="xref py py-class docutils literal"><span class="pre">Dialect</span></tt> knows the specific steps needed to
415
415
determine the correct value (or values; note that <tt class="docutils literal"><span class="pre">inserted_primary_key</span></tt>
416
416
returns a list so that it supports composite primary keys).</p>
417
417
</div>
421
421
various behaviors of expression language constructs. In the usual case, an
422
422
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> statement is usually compiled
423
423
against the parameters sent to the <tt class="docutils literal"><span class="pre">execute()</span></tt> method on
424
 
<a class="reference internal" href="connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, so that there&#8217;s no need to use
 
424
<tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt>, so that there&#8217;s no need to use
425
425
the <tt class="docutils literal"><span class="pre">values</span></tt> keyword with <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a>. Lets
426
426
create a generic <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> statement again
427
427
and use it in the &#8220;normal&#8221; way:</p>
429
429
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">ins</span><span class="p">,</span> <span class="nb">id</span><span class="o">=</span><span class="mi">2</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Wendy Williams&#39;</span><span class="p">)</span> 
430
430
<div class='show_sql'>INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
431
431
(2, 'wendy', 'Wendy Williams')
432
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span></pre></div>
 
432
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span></pre></div>
433
433
</div>
434
 
<p>Above, because we specified all three columns in the the <tt class="docutils literal"><span class="pre">execute()</span></tt> method,
 
434
<p>Above, because we specified all three columns in the <tt class="docutils literal"><span class="pre">execute()</span></tt> method,
435
435
the compiled <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> included all three
436
436
columns. The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> statement is compiled
437
437
at execution time based on the parameters we specified; if we specified fewer
448
448
<span class="gp">... </span><span class="p">])</span>
449
449
<div class='show_sql'>INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
450
450
((1, 'jack@yahoo.com'), (1, 'jack@msn.com'), (2, 'www@www.org'), (2, 'wendy@aol.com'))
451
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span></pre></div>
 
451
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span></pre></div>
452
452
</div>
453
453
<p>Above, we again relied upon SQLite&#8217;s automatic generation of primary key
454
454
identifiers for each <tt class="docutils literal"><span class="pre">addresses</span></tt> row.</p>
476
476
within the COLUMNS clause of the select, and then executing. SQLAlchemy
477
477
expanded the <tt class="docutils literal"><span class="pre">users</span></tt> table into the set of each of its columns, and also
478
478
generated a FROM clause for us. The result returned is again a
479
 
<a class="reference internal" href="connections.html#sqlalchemy.engine.base.ResultProxy" title="sqlalchemy.engine.base.ResultProxy"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></a> object, which acts much like a
 
479
<tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt> object, which acts much like a
480
480
DBAPI cursor, including methods such as
481
 
<a class="reference internal" href="connections.html#sqlalchemy.engine.base.ResultProxy.fetchone" title="sqlalchemy.engine.base.ResultProxy.fetchone"><tt class="xref py py-func docutils literal"><span class="pre">fetchone()</span></tt></a> and
482
 
<a class="reference internal" href="connections.html#sqlalchemy.engine.base.ResultProxy.fetchall" title="sqlalchemy.engine.base.ResultProxy.fetchall"><tt class="xref py py-func docutils literal"><span class="pre">fetchall()</span></tt></a>. The easiest way to get
 
481
<tt class="xref py py-func docutils literal"><span class="pre">fetchone()</span></tt> and
 
482
<tt class="xref py py-func docutils literal"><span class="pre">fetchall()</span></tt>. The easiest way to get
483
483
rows from it is to just iterate:</p>
484
484
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
485
485
<span class="gp">... </span>    <span class="k">print</span> <span class="n">row</span>
486
486
<span class="go">(1, u&#39;jack&#39;, u&#39;Jack Jones&#39;)</span>
487
 
<span class="go">(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;)</span>
488
 
<span class="go">(3, u&#39;fred&#39;, u&#39;Fred Flintstone&#39;)</span>
489
 
<span class="go">(4, u&#39;mary&#39;, u&#39;Mary Contrary&#39;)</span></pre></div>
 
487
<span class="go">(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;)</span></pre></div>
490
488
</div>
491
489
<p>Above, we see that printing each row produces a simple tuple-like result. We
492
490
have more options at accessing the data in each row. One very common way is
510
508
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
511
509
FROM users
512
510
()</div><span class="go">name: jack ; fullname: Jack Jones</span>
513
 
<span class="go">name: wendy ; fullname: Wendy Williams</span>
514
 
<span class="go">name: fred ; fullname: Fred Flintstone</span>
515
 
<span class="go">name: mary ; fullname: Mary Contrary</span></pre></div>
 
511
<span class="go">name: wendy ; fullname: Wendy Williams</span></pre></div>
516
512
</div>
517
513
<p>Result sets which have pending rows remaining should be explicitly closed
518
514
before discarding. While the cursor and connection resources referenced by the
519
 
<a class="reference internal" href="connections.html#sqlalchemy.engine.base.ResultProxy" title="sqlalchemy.engine.base.ResultProxy"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></a> will be respectively closed and
 
515
<tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt> will be respectively closed and
520
516
returned to the connection pool when the object is garbage collected, it&#8217;s
521
517
better to make it explicit as some database APIs are very picky about such
522
518
things:</p>
534
530
()</div><span class="gp">&gt;&gt;&gt; </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>  
535
531
<span class="gp">... </span>    <span class="k">print</span> <span class="n">row</span>
536
532
<span class="go">(u&#39;jack&#39;, u&#39;Jack Jones&#39;)</span>
537
 
<span class="go">(u&#39;wendy&#39;, u&#39;Wendy Williams&#39;)</span>
538
 
<span class="go">(u&#39;fred&#39;, u&#39;Fred Flintstone&#39;)</span>
539
 
<span class="go">(u&#39;mary&#39;, u&#39;Mary Contrary&#39;)</span></pre></div>
 
533
<span class="go">(u&#39;wendy&#39;, u&#39;Wendy Williams&#39;)</span></pre></div>
540
534
</div>
541
535
<p>Lets observe something interesting about the FROM clause. Whereas the
542
536
generated statement contains two distinct sections, a &#8220;SELECT columns&#8221; part
554
548
<span class="go">(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;, 1, 1, u&#39;jack@yahoo.com&#39;)</span>
555
549
<span class="go">(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;, 2, 1, u&#39;jack@msn.com&#39;)</span>
556
550
<span class="go">(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;, 3, 2, u&#39;www@www.org&#39;)</span>
557
 
<span class="go">(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;, 4, 2, u&#39;wendy@aol.com&#39;)</span>
558
 
<span class="go">(3, u&#39;fred&#39;, u&#39;Fred Flintstone&#39;, 1, 1, u&#39;jack@yahoo.com&#39;)</span>
559
 
<span class="go">(3, u&#39;fred&#39;, u&#39;Fred Flintstone&#39;, 2, 1, u&#39;jack@msn.com&#39;)</span>
560
 
<span class="go">(3, u&#39;fred&#39;, u&#39;Fred Flintstone&#39;, 3, 2, u&#39;www@www.org&#39;)</span>
561
 
<span class="go">(3, u&#39;fred&#39;, u&#39;Fred Flintstone&#39;, 4, 2, u&#39;wendy@aol.com&#39;)</span>
562
 
<span class="go">(4, u&#39;mary&#39;, u&#39;Mary Contrary&#39;, 1, 1, u&#39;jack@yahoo.com&#39;)</span>
563
 
<span class="go">(4, u&#39;mary&#39;, u&#39;Mary Contrary&#39;, 2, 1, u&#39;jack@msn.com&#39;)</span>
564
 
<span class="go">(4, u&#39;mary&#39;, u&#39;Mary Contrary&#39;, 3, 2, u&#39;www@www.org&#39;)</span>
565
 
<span class="go">(4, u&#39;mary&#39;, u&#39;Mary Contrary&#39;, 4, 2, u&#39;wendy@aol.com&#39;)</span></pre></div>
 
551
<span class="go">(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;, 4, 2, u&#39;wendy@aol.com&#39;)</span></pre></div>
566
552
</div>
567
553
<p>It placed <strong>both</strong> tables into the FROM clause. But also, it made a real mess.
568
554
Those who are familiar with SQL joins know that this is a <strong>Cartesian
569
555
product</strong>; each row from the <tt class="docutils literal"><span class="pre">users</span></tt> table is produced against each row from
570
556
the <tt class="docutils literal"><span class="pre">addresses</span></tt> table. So to put some sanity into this statement, we need a
571
 
WHERE clause. Which brings us to the second argument of <a class="reference internal" href="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>:</p>
572
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">,</span> <span class="n">addresses</span><span class="p">],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
 
557
WHERE clause.  We do that using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal"><span class="pre">Select.where()</span></tt></a>:</p>
 
558
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">,</span> <span class="n">addresses</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
573
559
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">):</span>
574
560
<span class="gp">... </span>    <span class="k">print</span> <span class="n">row</span>  
575
 
<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
 
561
<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id,
 
562
   addresses.user_id, addresses.email_address
576
563
FROM users, addresses
577
564
WHERE users.id = addresses.user_id
578
565
()</div><span class="go">(1, u&#39;jack&#39;, u&#39;Jack Jones&#39;, 1, 1, u&#39;jack@yahoo.com&#39;)</span>
586
573
<tt class="docutils literal"><span class="pre">addresses</span></tt> rows made sense. But let&#8217;s look at that expression? It&#8217;s using
587
574
just a Python equality operator between two different
588
575
<a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects. It should be clear that something
589
 
is up. Saying <tt class="docutils literal"><span class="pre">1==1</span></tt> produces <tt class="docutils literal"><span class="pre">True</span></tt>, and <tt class="docutils literal"><span class="pre">1==2</span></tt> produces <tt class="docutils literal"><span class="pre">False</span></tt>, not
 
576
is up. Saying <tt class="docutils literal"><span class="pre">1</span> <span class="pre">==</span> <span class="pre">1</span></tt> produces <tt class="docutils literal"><span class="pre">True</span></tt>, and <tt class="docutils literal"><span class="pre">1</span> <span class="pre">==</span> <span class="pre">2</span></tt> produces <tt class="docutils literal"><span class="pre">False</span></tt>, not
590
577
a WHERE clause. So lets see exactly what that expression is doing:</p>
591
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span> 
592
 
<span class="go">&lt;sqlalchemy.sql.expression._BinaryExpression object at 0x...&gt;</span></pre></div>
 
578
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span> 
 
579
<span class="go">&lt;sqlalchemy.sql.expression.BinaryExpression object at 0x...&gt;</span></pre></div>
593
580
</div>
594
581
<p>Wow, surprise ! This is neither a <tt class="docutils literal"><span class="pre">True</span></tt> nor a <tt class="docutils literal"><span class="pre">False</span></tt>. Well what is it ?</p>
595
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="nb">str</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
 
582
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="nb">str</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
596
583
<span class="go">&#39;users.id = addresses.user_id&#39;</span></pre></div>
597
584
</div>
598
585
<p>As you can see, the <tt class="docutils literal"><span class="pre">==</span></tt> operator is producing an object that is very much
600
587
objects we&#8217;ve made so far, thanks to Python&#8217;s <tt class="docutils literal"><span class="pre">__eq__()</span></tt> builtin; you call
601
588
<tt class="docutils literal"><span class="pre">str()</span></tt> on it and it produces SQL. By now, one can see that everything we
602
589
are working with is ultimately the same type of object. SQLAlchemy terms the
603
 
base class of all of these expressions as <tt class="docutils literal"><span class="pre">sqlalchemy.sql.ClauseElement</span></tt>.</p>
 
590
base class of all of these expressions as <a class="reference internal" href="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>.</p>
604
591
</div>
605
592
<div class="section" id="operators">
606
593
<h2>Operators<a class="headerlink" href="#operators" title="Permalink to this headline">¶</a></h2>
607
594
<p>Since we&#8217;ve stumbled upon SQLAlchemy&#8217;s operator paradigm, let&#8217;s go through
608
595
some of its capabilities. We&#8217;ve seen how to equate two columns to each other:</p>
609
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span>
 
596
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span>
610
597
<span class="go">users.id = addresses.user_id</span></pre></div>
611
598
</div>
612
599
<p>If we use a literal value (a literal meaning, not a SQLAlchemy clause object),
613
600
we get a bind parameter:</p>
614
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="mi">7</span>
 
601
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="mi">7</span>
615
602
<span class="go">users.id = :id_1</span></pre></div>
616
603
</div>
617
 
<p>The <tt class="docutils literal"><span class="pre">7</span></tt> literal is embedded in
618
 
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal"><span class="pre">ClauseElement</span></tt></a>; we can use the same trick
 
604
<p>The <tt class="docutils literal"><span class="pre">7</span></tt> literal is embedded the resulting
 
605
<a class="reference internal" href="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>; we can use the same trick
619
606
we did with the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><tt class="xref py py-class docutils literal"><span class="pre">Insert</span></tt></a> object to see it:</p>
620
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="mi">7</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">()</span><span class="o">.</span><span class="n">params</span>
 
607
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="mi">7</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">()</span><span class="o">.</span><span class="n">params</span>
621
608
<span class="go">{u&#39;id_1&#39;: 7}</span></pre></div>
622
609
</div>
623
610
<p>Most Python operators, as it turns out, produce a SQL expression here, like
637
624
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">+</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span>
638
625
<span class="go">users.id + addresses.id</span></pre></div>
639
626
</div>
640
 
<p>Interestingly, the type of the <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> is important
641
 
! If we use <tt class="docutils literal"><span class="pre">+</span></tt> with two string based columns (recall we put types like
 
627
<p>Interestingly, the type of the <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> is important!
 
628
If we use <tt class="docutils literal"><span class="pre">+</span></tt> with two string based columns (recall we put types like
642
629
<a class="reference internal" href="types.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><tt class="xref py py-class docutils literal"><span class="pre">Integer</span></tt></a> and <a class="reference internal" href="types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a> on
643
630
our <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects at the beginning), we get
644
631
something different:</p>
647
634
</div>
648
635
<p>Where <tt class="docutils literal"><span class="pre">||</span></tt> is the string concatenation operator used on most databases. But
649
636
not all of them. MySQL users, fear not:</p>
650
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">create_engine</span><span class="p">(</span><span class="s">&#39;mysql://&#39;</span><span class="p">))</span>
 
637
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">)</span><span class="o">.</span>\
 
638
<span class="gp">... </span>     <span class="nb">compile</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">create_engine</span><span class="p">(</span><span class="s">&#39;mysql://&#39;</span><span class="p">))</span>
651
639
<span class="go">concat(users.name, users.fullname)</span></pre></div>
652
640
</div>
653
641
<p>The above illustrates the SQL that&#8217;s generated for an
654
 
<a class="reference internal" href="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> that&#8217;s connected to a MySQL database;
 
642
<a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> that&#8217;s connected to a MySQL database;
655
643
the <tt class="docutils literal"><span class="pre">||</span></tt> operator now compiles as MySQL&#8217;s <tt class="docutils literal"><span class="pre">concat()</span></tt> function.</p>
656
644
<p>If you have come across an operator which really isn&#8217;t available, you can
657
 
always use the <tt class="docutils literal"><span class="pre">op()</span></tt> method; this generates whatever operator you need:</p>
 
645
always use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal"><span class="pre">ColumnOperators.op()</span></tt></a> method; this generates whatever operator you need:</p>
658
646
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s">&#39;tiddlywinks&#39;</span><span class="p">)(</span><span class="s">&#39;foo&#39;</span><span class="p">)</span>
659
647
<span class="go">users.name tiddlywinks :name_1</span></pre></div>
660
648
</div>
662
650
<div class="highlight-python"><div class="highlight"><pre><span class="n">somecolumn</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s">&#39;&amp;&#39;</span><span class="p">)(</span><span class="mh">0xff</span><span class="p">)</span></pre></div>
663
651
</div>
664
652
<p>is a bitwise AND of the value in <cite>somecolumn</cite>.</p>
 
653
<div class="section" id="operator-customization">
 
654
<h3>Operator Customization<a class="headerlink" href="#operator-customization" title="Permalink to this headline">¶</a></h3>
 
655
<p>While <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.op" title="sqlalchemy.sql.operators.ColumnOperators.op"><tt class="xref py py-meth docutils literal"><span class="pre">ColumnOperators.op()</span></tt></a> is handy to get at a custom operator in a hurry,
 
656
the Core supports fundamental customization and extension of the operator system at
 
657
the type level.   The behavior of existing operators can be modified on a per-type
 
658
basis, and new operations can be defined which become available for all column
 
659
expressions that are part of that particular type.  See the section <a class="reference internal" href="types.html#types-operators"><em>Redefining and Creating New Operators</em></a>
 
660
for a description.</p>
 
661
</div>
665
662
</div>
666
663
<div class="section" id="conjunctions">
667
664
<h2>Conjunctions<a class="headerlink" href="#conjunctions" title="Permalink to this headline">¶</a></h2>
668
665
<p>We&#8217;d like to show off some of our operators inside of <a class="reference internal" href="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>
669
666
constructs. But we need to lump them together a little more, so let&#8217;s first
670
667
introduce some conjunctions. Conjunctions are those little words like AND and
671
 
OR that put things together. We&#8217;ll also hit upon NOT. AND, OR and NOT can work
 
668
OR that put things together. We&#8217;ll also hit upon NOT. <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal"><span class="pre">and_()</span></tt></a>, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.or_" title="sqlalchemy.sql.expression.or_"><tt class="xref py py-func docutils literal"><span class="pre">or_()</span></tt></a>,
 
669
and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.not_" title="sqlalchemy.sql.expression.not_"><tt class="xref py py-func docutils literal"><span class="pre">not_()</span></tt></a> can work
672
670
from the corresponding functions SQLAlchemy provides (notice we also throw in
673
 
a LIKE):</p>
 
671
a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><tt class="xref py py-meth docutils literal"><span class="pre">like()</span></tt></a>):</p>
674
672
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">and_</span><span class="p">,</span> <span class="n">or_</span><span class="p">,</span> <span class="n">not_</span>
675
 
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">and_</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;j%&#39;</span><span class="p">),</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span> 
676
 
<span class="gp">... </span>    <span class="n">or_</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;wendy@aol.com&#39;</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;jack@yahoo.com&#39;</span><span class="p">),</span>
677
 
<span class="gp">... </span>    <span class="n">not_</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">&gt;</span><span class="mi">5</span><span class="p">))</span>
 
673
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">and_</span><span class="p">(</span>
 
674
<span class="gp">... </span>        <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;j%&#39;</span><span class="p">),</span>
 
675
<span class="gp">... </span>        <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span> 
 
676
<span class="gp">... </span>        <span class="n">or_</span><span class="p">(</span>
 
677
<span class="gp">... </span>             <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;wendy@aol.com&#39;</span><span class="p">,</span>
 
678
<span class="gp">... </span>             <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;jack@yahoo.com&#39;</span>
 
679
<span class="gp">... </span>        <span class="p">),</span>
 
680
<span class="gp">... </span>        <span class="n">not_</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">&gt;</span> <span class="mi">5</span><span class="p">)</span>
 
681
<span class="gp">... </span>      <span class="p">)</span>
678
682
<span class="go">users.name LIKE :name_1 AND users.id = addresses.user_id AND</span>
679
 
<span class="go">(addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2)</span>
 
683
<span class="go">(addresses.email_address = :email_address_1</span>
 
684
<span class="go">   OR addresses.email_address = :email_address_2)</span>
680
685
<span class="go">AND users.id &lt;= :id_1</span></pre></div>
681
686
</div>
682
687
<p>And you can also use the re-jiggered bitwise AND, OR and NOT operators,
683
688
although because of Python operator precedence you have to watch your
684
689
parenthesis:</p>
685
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;j%&#39;</span><span class="p">)</span> <span class="o">&amp;</span> <span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span> <span class="o">&amp;</span>  \
686
 
<span class="gp">... </span>    <span class="p">((</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;wendy@aol.com&#39;</span><span class="p">)</span> <span class="o">|</span> <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;jack@yahoo.com&#39;</span><span class="p">))</span> \
 
690
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;j%&#39;</span><span class="p">)</span> <span class="o">&amp;</span> <span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span> <span class="o">&amp;</span>  \
 
691
<span class="gp">... </span>    <span class="p">(</span>
 
692
<span class="gp">... </span>      <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;wendy@aol.com&#39;</span><span class="p">)</span> <span class="o">|</span> \
 
693
<span class="gp">... </span>      <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;jack@yahoo.com&#39;</span><span class="p">)</span>
 
694
<span class="gp">... </span>    <span class="p">)</span> \
687
695
<span class="gp">... </span>    <span class="o">&amp;</span> <span class="o">~</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">&gt;</span><span class="mi">5</span><span class="p">)</span> 
688
696
<span class="go">users.name LIKE :name_1 AND users.id = addresses.user_id AND</span>
689
 
<span class="go">(addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2)</span>
 
697
<span class="go">(addresses.email_address = :email_address_1</span>
 
698
<span class="go">    OR addresses.email_address = :email_address_2)</span>
690
699
<span class="go">AND users.id &lt;= :id_1</span></pre></div>
691
700
</div>
692
701
<p>So with all of this vocabulary, let&#8217;s select all users who have an email
693
702
address at AOL or MSN, whose name starts with a letter between &#8220;m&#8221; and &#8220;z&#8221;,
694
703
and we&#8217;ll also generate a column containing their full name combined with
695
704
their email address. We will add two new constructs to this statement,
696
 
<tt class="docutils literal"><span class="pre">between()</span></tt> and <tt class="docutils literal"><span class="pre">label()</span></tt>. <tt class="docutils literal"><span class="pre">between()</span></tt> produces a BETWEEN clause, and
697
 
<tt class="docutils literal"><span class="pre">label()</span></tt> is used in a column expression to produce labels using the <tt class="docutils literal"><span class="pre">AS</span></tt>
 
705
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><tt class="xref py py-meth docutils literal"><span class="pre">between()</span></tt></a> and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal"><span class="pre">label()</span></tt></a>.
 
706
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><tt class="xref py py-meth docutils literal"><span class="pre">between()</span></tt></a> produces a BETWEEN clause, and
 
707
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.label" title="sqlalchemy.sql.expression.ColumnElement.label"><tt class="xref py py-meth docutils literal"><span class="pre">label()</span></tt></a> is used in a column expression to produce labels using the <tt class="docutils literal"><span class="pre">AS</span></tt>
698
708
keyword; it&#8217;s recommended when selecting from expressions that otherwise would
699
709
not have a name:</p>
700
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span> <span class="o">+</span> <span class="s">&quot;, &quot;</span> <span class="o">+</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">&#39;title&#39;</span><span class="p">)],</span>
701
 
<span class="gp">... </span>       <span class="n">and_</span><span class="p">(</span>
702
 
<span class="gp">... </span>           <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
703
 
<span class="gp">... </span>           <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">between</span><span class="p">(</span><span class="s">&#39;m&#39;</span><span class="p">,</span> <span class="s">&#39;z&#39;</span><span class="p">),</span>
704
 
<span class="gp">... </span>          <span class="n">or_</span><span class="p">(</span>
705
 
<span class="gp">... </span>             <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@aol.com&#39;</span><span class="p">),</span>
706
 
<span class="gp">... </span>             <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">)</span>
 
710
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span> <span class="o">+</span>
 
711
<span class="gp">... </span>              <span class="s">&quot;, &quot;</span> <span class="o">+</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span><span class="o">.</span>
 
712
<span class="gp">... </span>               <span class="n">label</span><span class="p">(</span><span class="s">&#39;title&#39;</span><span class="p">)])</span><span class="o">.</span>\
 
713
<span class="gp">... </span>       <span class="n">where</span><span class="p">(</span>
 
714
<span class="gp">... </span>          <span class="n">and_</span><span class="p">(</span>
 
715
<span class="gp">... </span>              <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
 
716
<span class="gp">... </span>              <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">between</span><span class="p">(</span><span class="s">&#39;m&#39;</span><span class="p">,</span> <span class="s">&#39;z&#39;</span><span class="p">),</span>
 
717
<span class="gp">... </span>              <span class="n">or_</span><span class="p">(</span>
 
718
<span class="gp">... </span>                 <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@aol.com&#39;</span><span class="p">),</span>
 
719
<span class="gp">... </span>                 <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">)</span>
 
720
<span class="gp">... </span>              <span class="p">)</span>
707
721
<span class="gp">... </span>          <span class="p">)</span>
708
722
<span class="gp">... </span>       <span class="p">)</span>
709
 
<span class="gp">... </span>   <span class="p">)</span>
710
 
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
 
723
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
711
724
<span class="go">SELECT users.fullname || ? || addresses.email_address AS title</span>
712
725
<span class="go">FROM users, addresses</span>
713
726
<span class="go">WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND</span>
719
732
it will determine the FROM clause based on all of its other bits; the columns
720
733
clause, the where clause, and also some other elements which we haven&#8217;t
721
734
covered yet, which include ORDER BY, GROUP BY, and HAVING.</p>
 
735
<p>A shortcut to using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal"><span class="pre">and_()</span></tt></a> is to chain together multiple
 
736
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal"><span class="pre">where()</span></tt></a> clauses.   The above can also be written as:</p>
 
737
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span> <span class="o">+</span>
 
738
<span class="gp">... </span>              <span class="s">&quot;, &quot;</span> <span class="o">+</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span><span class="o">.</span>
 
739
<span class="gp">... </span>               <span class="n">label</span><span class="p">(</span><span class="s">&#39;title&#39;</span><span class="p">)])</span><span class="o">.</span>\
 
740
<span class="gp">... </span>       <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
 
741
<span class="gp">... </span>       <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">between</span><span class="p">(</span><span class="s">&#39;m&#39;</span><span class="p">,</span> <span class="s">&#39;z&#39;</span><span class="p">))</span><span class="o">.</span>\
 
742
<span class="gp">... </span>       <span class="n">where</span><span class="p">(</span>
 
743
<span class="gp">... </span>              <span class="n">or_</span><span class="p">(</span>
 
744
<span class="gp">... </span>                 <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@aol.com&#39;</span><span class="p">),</span>
 
745
<span class="gp">... </span>                 <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">)</span>
 
746
<span class="gp">... </span>              <span class="p">)</span>
 
747
<span class="gp">... </span>       <span class="p">)</span>
 
748
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
 
749
<span class="go">SELECT users.fullname || ? || addresses.email_address AS title</span>
 
750
<span class="go">FROM users, addresses</span>
 
751
<span class="go">WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND</span>
 
752
<span class="go">(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)</span>
 
753
<span class="go">(&#39;, &#39;, &#39;m&#39;, &#39;z&#39;, &#39;%@aol.com&#39;, &#39;%@msn.com&#39;)</span>
 
754
<span class="go">[(u&#39;Wendy Williams, wendy@aol.com&#39;,)]</span></pre></div>
 
755
</div>
 
756
<p>The way that we can build up a <a class="reference internal" href="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 through successive
 
757
method calls is called <a class="reference internal" href="../glossary.html#term-method-chaining"><em class="xref std std-term">method chaining</em></a>.</p>
722
758
</div>
723
759
<div class="section" id="using-text">
724
760
<span id="sqlexpression-text"></span><h2>Using Text<a class="headerlink" href="#using-text" title="Permalink to this headline">¶</a></h2>
725
761
<p>Our last example really became a handful to type. Going from what one
726
762
understands to be a textual SQL expression into a Python construct which
727
763
groups components together in a programmatic style can be hard. That&#8217;s why
728
 
SQLAlchemy lets you just use strings too. The <tt class="docutils literal"><span class="pre">text()</span></tt> construct represents
729
 
any textual statement. To use bind parameters with <tt class="docutils literal"><span class="pre">text()</span></tt>, always use the
730
 
named colon format. Such as below, we create a <tt class="docutils literal"><span class="pre">text()</span></tt> and execute it,
731
 
feeding in the bind parameters to the <tt class="docutils literal"><span class="pre">execute()</span></tt> method:</p>
 
764
SQLAlchemy lets you just use strings too. The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> construct represents
 
765
any textual statement, in a backend-agnostic way.
 
766
To use bind parameters with <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a>, always use the
 
767
named colon format. Such as below, we create a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> and execute it,
 
768
feeding in the bind parameters to the <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><tt class="xref py py-meth docutils literal"><span class="pre">execute()</span></tt></a> method:</p>
732
769
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">text</span>
733
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s">&quot;&quot;&quot;SELECT users.fullname || &#39;, &#39; || addresses.email_address AS title</span>
734
 
<span class="gp">... </span>           <span class="n">FROM</span> <span class="n">users</span><span class="p">,</span> <span class="n">addresses</span>
735
 
<span class="gp">... </span>           <span class="n">WHERE</span> <span class="n">users</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">user_id</span> <span class="n">AND</span> <span class="n">users</span><span class="o">.</span><span class="n">name</span> <span class="n">BETWEEN</span> <span class="p">:</span><span class="n">x</span> <span class="n">AND</span> <span class="p">:</span><span class="n">y</span> <span class="n">AND</span>
736
 
<span class="gp">... </span>           <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">email_address</span> <span class="n">LIKE</span> <span class="p">:</span><span class="n">e1</span> <span class="n">OR</span> <span class="n">addresses</span><span class="o">.</span><span class="n">email_address</span> <span class="n">LIKE</span> <span class="p">:</span><span class="n">e2</span><span class="p">)</span>
737
 
<span class="gp">... </span>       <span class="s">&quot;&quot;&quot;)</span>
738
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="o">=</span><span class="s">&#39;m&#39;</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s">&#39;z&#39;</span><span class="p">,</span> <span class="n">e1</span><span class="o">=</span><span class="s">&#39;%@aol.com&#39;</span><span class="p">,</span> <span class="n">e2</span><span class="o">=</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
 
770
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span>
 
771
<span class="gp">... </span>    <span class="s">&quot;SELECT users.fullname || &#39;, &#39; || addresses.email_address AS title &quot;</span>
 
772
<span class="gp">... </span>        <span class="s">&quot;FROM users, addresses &quot;</span>
 
773
<span class="gp">... </span>        <span class="s">&quot;WHERE users.id = addresses.user_id &quot;</span>
 
774
<span class="gp">... </span>        <span class="s">&quot;AND users.name BETWEEN :x AND :y &quot;</span>
 
775
<span class="gp">... </span>        <span class="s">&quot;AND (addresses.email_address LIKE :e1 &quot;</span>
 
776
<span class="gp">... </span>            <span class="s">&quot;OR addresses.email_address LIKE :e2)&quot;</span><span class="p">)</span>
 
777
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="o">=</span><span class="s">&#39;m&#39;</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s">&#39;z&#39;</span><span class="p">,</span> <span class="n">e1</span><span class="o">=</span><span class="s">&#39;%@aol.com&#39;</span><span class="p">,</span> <span class="n">e2</span><span class="o">=</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
739
778
<div class='popup_sql'>SELECT users.fullname || ', ' || addresses.email_address AS title
740
779
FROM users, addresses
741
780
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
742
781
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
743
782
('m', 'z', '%@aol.com', '%@msn.com')</div><span class="go">[(u&#39;Wendy Williams, wendy@aol.com&#39;,)]</span></pre></div>
744
783
</div>
745
 
<p>To gain a &#8220;hybrid&#8221; approach, the <cite>select()</cite> construct accepts strings for most
 
784
<p>To gain a &#8220;hybrid&#8221; approach, the <a class="reference internal" href="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 accepts strings for most
746
785
of its arguments. Below we combine the usage of strings with our constructed
747
786
<a class="reference internal" href="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> object, by using the <a class="reference internal" href="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> object to structure the
748
787
statement, and strings to provide all the content within the structure. For
749
788
this example, SQLAlchemy is not given any <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>
750
789
or <a class="reference internal" href="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 in any of its expressions, so it
751
 
cannot generate a FROM clause. So we also give it the <tt class="docutils literal"><span class="pre">from_obj</span></tt> keyword
752
 
argument, which is a list of <tt class="docutils literal"><span class="pre">ClauseElements</span></tt> (or strings) to be placed
753
 
within the FROM clause:</p>
754
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="s">&quot;users.fullname || &#39;, &#39; || addresses.email_address AS title&quot;</span><span class="p">],</span>
755
 
<span class="gp">... </span>       <span class="n">and_</span><span class="p">(</span>
756
 
<span class="gp">... </span>           <span class="s">&quot;users.id = addresses.user_id&quot;</span><span class="p">,</span>
757
 
<span class="gp">... </span>            <span class="s">&quot;users.name BETWEEN &#39;m&#39; AND &#39;z&#39;&quot;</span><span class="p">,</span>
758
 
<span class="gp">... </span>            <span class="s">&quot;(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)&quot;</span>
759
 
<span class="gp">... </span>       <span class="p">),</span>
760
 
<span class="gp">... </span>        <span class="n">from_obj</span><span class="o">=</span><span class="p">[</span><span class="s">&#39;users&#39;</span><span class="p">,</span> <span class="s">&#39;addresses&#39;</span><span class="p">]</span>
761
 
<span class="gp">... </span>   <span class="p">)</span>
762
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="o">=</span><span class="s">&#39;%@aol.com&#39;</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
 
790
cannot generate a FROM clause. So we also use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><tt class="xref py py-meth docutils literal"><span class="pre">select_from()</span></tt></a>
 
791
method, which accepts a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><tt class="xref py py-class docutils literal"><span class="pre">FromClause</span></tt></a> or string expression
 
792
to be placed within the FROM clause:</p>
 
793
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
 
794
<span class="gp">... </span>           <span class="s">&quot;users.fullname || &#39;, &#39; || addresses.email_address AS title&quot;</span>
 
795
<span class="gp">... </span>         <span class="p">])</span><span class="o">.</span>\
 
796
<span class="gp">... </span>          <span class="n">where</span><span class="p">(</span>
 
797
<span class="gp">... </span>             <span class="n">and_</span><span class="p">(</span>
 
798
<span class="gp">... </span>                <span class="s">&quot;users.id = addresses.user_id&quot;</span><span class="p">,</span>
 
799
<span class="gp">... </span>                <span class="s">&quot;users.name BETWEEN &#39;m&#39; AND &#39;z&#39;&quot;</span><span class="p">,</span>
 
800
<span class="gp">... </span>                <span class="s">&quot;(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)&quot;</span>
 
801
<span class="gp">... </span>            <span class="p">)</span>
 
802
<span class="gp">... </span>          <span class="p">)</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="s">&#39;users, addresses&#39;</span><span class="p">)</span>
 
803
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">x</span><span class="o">=</span><span class="s">&#39;%@aol.com&#39;</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
763
804
<div class='popup_sql'>SELECT users.fullname || ', ' || addresses.email_address AS title
764
805
FROM users, addresses
765
 
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
 
806
WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z'
 
807
AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
766
808
('%@aol.com', '%@msn.com')</div><span class="go">[(u&#39;Wendy Williams, wendy@aol.com&#39;,)]</span></pre></div>
767
809
</div>
768
810
<p>Going from constructed SQL to text, we lose some capabilities. We lose the
772
814
datatypes in use; for example, if our bind parameters required UTF-8 encoding
773
815
before going in, or conversion from a Python <tt class="docutils literal"><span class="pre">datetime</span></tt> into a string (as is
774
816
required with SQLite), we would have to add extra information to our
775
 
<tt class="docutils literal"><span class="pre">text()</span></tt> construct. Similar issues arise on the result set side, where
 
817
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> construct. Similar issues arise on the result set side, where
776
818
SQLAlchemy also performs type-specific data conversion in some cases; still
777
 
more information can be added to <tt class="docutils literal"><span class="pre">text()</span></tt> to work around this. But what we
 
819
more information can be added to <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">text()</span></tt></a> to work around this. But what we
778
820
really lose from our statement is the ability to manipulate it, transform it,
779
821
and analyze it. These features are critical when using the ORM, which makes
780
822
heavy usage of relational transformations. To show off what we mean, we&#8217;ll
800
842
<tt class="docutils literal"><span class="pre">addresses</span></tt>, and then use them both within a <a class="reference internal" href="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:</p>
801
843
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">a1</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
802
844
<span class="gp">&gt;&gt;&gt; </span><span class="n">a2</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
803
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">],</span> <span class="n">and_</span><span class="p">(</span>
804
 
<span class="gp">... </span>       <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
805
 
<span class="gp">... </span>       <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">a2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
806
 
<span class="gp">... </span>       <span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;jack@msn.com&#39;</span><span class="p">,</span>
807
 
<span class="gp">... </span>       <span class="n">a2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;jack@yahoo.com&#39;</span>
808
 
<span class="gp">... </span>  <span class="p">))</span>
809
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
845
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">])</span><span class="o">.</span>\
 
846
<span class="gp">... </span>       <span class="n">where</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span>
 
847
<span class="gp">... </span>           <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
 
848
<span class="gp">... </span>           <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">a2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
 
849
<span class="gp">... </span>           <span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;jack@msn.com&#39;</span><span class="p">,</span>
 
850
<span class="gp">... </span>           <span class="n">a2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;jack@yahoo.com&#39;</span>
 
851
<span class="gp">... </span>       <span class="p">))</span>
 
852
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
810
853
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
811
854
FROM users, addresses AS addresses_1, addresses AS addresses_2
812
 
WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?
 
855
WHERE users.id = addresses_1.user_id
 
856
    AND users.id = addresses_2.user_id
 
857
    AND addresses_1.email_address = ?
 
858
    AND addresses_2.email_address = ?
813
859
('jack@msn.com', 'jack@yahoo.com')</div><span class="go">[(1, u&#39;jack&#39;, u&#39;Jack Jones&#39;)]</span></pre></div>
814
860
</div>
815
861
<p>Note that the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal"><span class="pre">Alias</span></tt></a> construct generated the names <tt class="docutils literal"><span class="pre">addresses_1</span></tt> and
831
877
statement. The <tt class="docutils literal"><span class="pre">correlate(None)</span></tt> directive is to avoid SQLAlchemy&#8217;s attempt
832
878
to &#8220;correlate&#8221; the inner <tt class="docutils literal"><span class="pre">users</span></tt> table with the outer one:</p>
833
879
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">a1</span> <span class="o">=</span> <span class="n">s</span><span class="o">.</span><span class="n">correlate</span><span class="p">(</span><span class="bp">None</span><span class="p">)</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
834
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
835
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
880
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
 
881
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
836
882
<div class='popup_sql'>SELECT users.name
837
 
FROM users, (SELECT users.id AS id, users.name AS name, users.fullname AS fullname
838
 
FROM users, addresses AS addresses_1, addresses AS addresses_2
839
 
WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?) AS anon_1
 
883
FROM users,
 
884
    (SELECT users.id AS id, users.name AS name, users.fullname AS fullname
 
885
        FROM users, addresses AS addresses_1, addresses AS addresses_2
 
886
        WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id
 
887
        AND addresses_1.email_address = ?
 
888
        AND addresses_2.email_address = ?) AS anon_1
840
889
WHERE users.id = anon_1.id
841
890
('jack@msn.com', 'jack@yahoo.com')</div><span class="go">[(u&#39;jack&#39;,)]</span></pre></div>
842
891
</div>
847
896
cornerstone of the SELECT is the JOIN expression. We&#8217;ve already been doing
848
897
joins in our examples, by just placing two tables in either the columns clause
849
898
or the where clause of the <a class="reference internal" href="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. But if we want to make a
850
 
real &#8220;JOIN&#8221; or &#8220;OUTERJOIN&#8221; construct, we use the <tt class="docutils literal"><span class="pre">join()</span></tt> and
851
 
<tt class="docutils literal"><span class="pre">outerjoin()</span></tt> methods, most commonly accessed from the left table in the
 
899
real &#8220;JOIN&#8221; or &#8220;OUTERJOIN&#8221; construct, we use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> and
 
900
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><tt class="xref py py-meth docutils literal"><span class="pre">outerjoin()</span></tt></a> methods, most commonly accessed from the left table in the
852
901
join:</p>
853
902
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">)</span>
854
903
<span class="go">users JOIN addresses ON users.id = addresses.user_id</span></pre></div>
862
911
<p>Of course you can join on whatever expression you want, such as if we want to
863
912
join on all users who use the same name in their email address as their
864
913
username:</p>
865
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">&#39;%&#39;</span><span class="p">))</span>
866
 
<span class="go">users JOIN addresses ON addresses.email_address LIKE users.name || :name_1</span></pre></div>
 
914
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span>
 
915
<span class="gp">... </span>                <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">&#39;%&#39;</span><span class="p">)</span>
 
916
<span class="gp">... </span>            <span class="p">)</span>
 
917
<span class="go">users JOIN addresses ON addresses.email_address LIKE (users.name || :name_1)</span></pre></div>
867
918
</div>
868
919
<p>When we create a <a class="reference internal" href="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, SQLAlchemy looks around at the
869
920
tables we&#8217;ve mentioned and then places them in the FROM clause of the
870
921
statement. When we use JOINs however, we know what FROM clause we want, so
871
 
here we make usage of the <tt class="docutils literal"><span class="pre">from_obj</span></tt> keyword argument:</p>
872
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">],</span> <span class="n">from_obj</span><span class="o">=</span><span class="p">[</span>
873
 
<span class="gp">... </span>   <span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">&#39;%&#39;</span><span class="p">))</span>
874
 
<span class="gp">... </span>   <span class="p">])</span>
875
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
922
here we make use of the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.select_from" title="sqlalchemy.sql.expression.Select.select_from"><tt class="xref py py-meth docutils literal"><span class="pre">select_from()</span></tt></a> method:</p>
 
923
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">])</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span>
 
924
<span class="gp">... </span>   <span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span>
 
925
<span class="gp">... </span>            <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">&#39;%&#39;</span><span class="p">))</span>
 
926
<span class="gp">... </span>   <span class="p">)</span>
 
927
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
876
928
<div class='popup_sql'>SELECT users.fullname
877
 
FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
 
929
FROM users JOIN addresses ON addresses.email_address LIKE (users.name || ?)
878
930
('%',)</div><span class="go">[(u&#39;Jack Jones&#39;,), (u&#39;Jack Jones&#39;,), (u&#39;Wendy Williams&#39;,)]</span></pre></div>
879
931
</div>
880
 
<p>The <tt class="docutils literal"><span class="pre">outerjoin()</span></tt> function just creates <tt class="docutils literal"><span class="pre">LEFT</span> <span class="pre">OUTER</span> <span class="pre">JOIN</span></tt> constructs. It&#8217;s
881
 
used just like <tt class="docutils literal"><span class="pre">join()</span></tt>:</p>
882
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">],</span> <span class="n">from_obj</span><span class="o">=</span><span class="p">[</span><span class="n">users</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">)])</span>
 
932
<p>The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.outerjoin" title="sqlalchemy.sql.expression.FromClause.outerjoin"><tt class="xref py py-meth docutils literal"><span class="pre">outerjoin()</span></tt></a> method creates <tt class="docutils literal"><span class="pre">LEFT</span> <span class="pre">OUTER</span> <span class="pre">JOIN</span></tt> constructs,
 
933
and is used in the same way as <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.join" title="sqlalchemy.sql.expression.FromClause.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a>:</p>
 
934
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="p">])</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span>
883
935
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">s</span>  
884
936
<span class="go">SELECT users.fullname</span>
885
 
<span class="go">FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id</span></pre></div>
 
937
<span class="go">    FROM users</span>
 
938
<span class="go">    LEFT OUTER JOIN addresses ON users.id = addresses.user_id</span></pre></div>
886
939
</div>
887
940
<p>That&#8217;s the output <tt class="docutils literal"><span class="pre">outerjoin()</span></tt> produces, unless, of course, you&#8217;re stuck in
888
941
a gig using Oracle prior to version 9, and you&#8217;ve set up your engine (which
896
949
<p>If you don&#8217;t know what that SQL means, don&#8217;t worry ! The secret tribe of
897
950
Oracle DBAs don&#8217;t want their black magic being found out ;).</p>
898
951
</div>
899
 
<div class="section" id="intro-to-generative-selects">
900
 
<h2>Intro to Generative Selects<a class="headerlink" href="#intro-to-generative-selects" title="Permalink to this headline">¶</a></h2>
901
 
<p>We&#8217;ve now gained the ability to construct very sophisticated statements. We
902
 
can use all kinds of operators, table constructs, text, joins, and aliases.
903
 
The point of all of this, as mentioned earlier, is not that it&#8217;s an &#8220;easier&#8221;
904
 
or &#8220;better&#8221; way to write SQL than just writing a SQL statement yourself; the
905
 
point is that it&#8217;s better for writing <em>programmatically generated</em> SQL which
906
 
can be morphed and adapted as needed in automated scenarios.</p>
907
 
<p>To support this, the <a class="reference internal" href="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 we&#8217;ve been working with
908
 
supports piecemeal construction, in addition to the &#8220;all at once&#8221; method we&#8217;ve
909
 
been doing. Suppose you&#8217;re writing a search function, which receives criterion
910
 
and then must construct a select from it. To accomplish this, upon each
911
 
criterion encountered, you apply &#8220;generative&#8221; criterion to an existing
912
 
<a class="reference internal" href="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 with new elements, one at a time. We start with a
913
 
basic <a class="reference internal" href="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> constructed with the shortcut method available on the
914
 
<tt class="docutils literal"><span class="pre">users</span></tt> table:</p>
915
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">query</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">select</span><span class="p">()</span>
916
 
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">query</span>  
917
 
<span class="go">SELECT users.id, users.name, users.fullname</span>
918
 
<span class="go">FROM users</span></pre></div>
919
 
</div>
920
 
<p>We encounter search criterion of &#8220;name=&#8217;jack&#8217;&#8221;. So we apply WHERE criterion
921
 
stating such:</p>
922
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">query</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">&#39;jack&#39;</span><span class="p">)</span></pre></div>
923
 
</div>
924
 
<p>Next, we encounter that they&#8217;d like the results in descending order by full
925
 
name. We apply ORDER BY, using an extra modifier <tt class="docutils literal"><span class="pre">desc</span></tt>:</p>
926
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">query</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">fullname</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span></pre></div>
927
 
</div>
928
 
<p>We also come across that they&#8217;d like only users who have an address at MSN. A
929
 
quick way to tack this on is by using an EXISTS clause, which we correlate to
930
 
the <tt class="docutils literal"><span class="pre">users</span></tt> table in the enclosing SELECT:</p>
931
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">exists</span>
932
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">query</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
933
 
<span class="gp">... </span>   <span class="n">exists</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">],</span>
934
 
<span class="gp">... </span>       <span class="n">and_</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">))</span>
935
 
<span class="gp">... </span>   <span class="p">)</span><span class="o">.</span><span class="n">correlate</span><span class="p">(</span><span class="n">users</span><span class="p">))</span></pre></div>
936
 
</div>
937
 
<p>And finally, the application also wants to see the listing of email addresses
938
 
at once; so to save queries, we outerjoin the <tt class="docutils literal"><span class="pre">addresses</span></tt> table (using an
939
 
outer join so that users with no addresses come back as well; since we&#8217;re
940
 
programmatic, we might not have kept track that we used an EXISTS clause
941
 
against the <tt class="docutils literal"><span class="pre">addresses</span></tt> table too...). Additionally, since the <tt class="docutils literal"><span class="pre">users</span></tt> and
942
 
<tt class="docutils literal"><span class="pre">addresses</span></tt> table both have a column named <tt class="docutils literal"><span class="pre">id</span></tt>, let&#8217;s isolate their names
943
 
from each other in the COLUMNS clause by using labels:</p>
944
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">query</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">column</span><span class="p">(</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span><span class="n">apply_labels</span><span class="p">()</span></pre></div>
945
 
</div>
946
 
<p>Let&#8217;s bake for .0001 seconds and see what rises:</p>
947
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">query</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
948
 
<div class='show_sql'>SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.email_address AS addresses_email_address
949
 
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
950
 
WHERE users.name = ? AND (EXISTS (SELECT addresses.id
951
 
FROM addresses
952
 
WHERE addresses.user_id = users.id AND addresses.email_address LIKE ?)) ORDER BY users.fullname DESC
953
 
('jack', '%@msn.com')</div><span class="go">[(1, u&#39;jack&#39;, u&#39;Jack Jones&#39;, 1, 1, u&#39;jack@yahoo.com&#39;), (1, u&#39;jack&#39;, u&#39;Jack Jones&#39;, 2, 1, u&#39;jack@msn.com&#39;)]</span></pre></div>
954
 
</div>
955
 
<p>The generative approach is about starting small, adding one thing at a time,
956
 
to arrive with a full statement.</p>
957
 
<div class="section" id="transforming-a-statement">
958
 
<h3>Transforming a Statement<a class="headerlink" href="#transforming-a-statement" title="Permalink to this headline">¶</a></h3>
959
 
<p>We&#8217;ve seen how methods like <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal"><span class="pre">Select.where()</span></tt></a> and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression._SelectBase.order_by" title="sqlalchemy.sql.expression._SelectBase.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">_SelectBase.order_by()</span></tt></a> are
960
 
part of the so-called <em>Generative</em> family of methods on the <a class="reference internal" href="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,
961
 
where one <a class="reference internal" href="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> copies itself to return a new one with modifications.
962
 
SQL constructs also support another form of generative behavior which is
963
 
the <em>transformation</em>.   This is an advanced technique that most core applications
964
 
won&#8217;t use directly; however, it is a system which the ORM relies on heavily,
965
 
and can be useful for any system that deals with generalized behavior of Core SQL
966
 
constructs.</p>
967
 
<p>Using a transformation we can take our <tt class="docutils literal"><span class="pre">users</span></tt>/<tt class="docutils literal"><span class="pre">addresses</span></tt> query and replace
968
 
all occurrences of <tt class="docutils literal"><span class="pre">addresses</span></tt> with an alias of itself.   That is, anywhere
969
 
that <tt class="docutils literal"><span class="pre">addresses</span></tt> is referred to in the original query, the new query will
970
 
refer to <tt class="docutils literal"><span class="pre">addresses_1</span></tt>, which is selected as <tt class="docutils literal"><span class="pre">addresses</span> <span class="pre">AS</span> <span class="pre">addresses_1</span></tt>.
971
 
The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause.replace_selectable" title="sqlalchemy.sql.expression.FromClause.replace_selectable"><tt class="xref py py-meth docutils literal"><span class="pre">FromClause.replace_selectable()</span></tt></a> method can achieve this:</p>
972
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">a1</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
973
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">query</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">replace_selectable</span><span class="p">(</span><span class="n">addresses</span><span class="p">,</span> <span class="n">a1</span><span class="p">)</span>
974
 
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">query</span>  
975
 
<div class='show_sql'>SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address
976
 
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
977
 
WHERE users.name = :name_1 AND (EXISTS (SELECT addresses_1.id
978
 
FROM addresses AS addresses_1
979
 
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :email_address_1)) ORDER BY users.fullname DESC</div></pre></div>
980
 
</div>
981
 
<p>For a query such as the above, we can access the columns referred
982
 
to by the <tt class="docutils literal"><span class="pre">a1</span></tt> alias in a result set using the <a class="reference internal" href="schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects
983
 
present directly on <tt class="docutils literal"><span class="pre">a1</span></tt>:</p>
984
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">query</span><span class="p">):</span>
985
 
<span class="gp">... </span>    <span class="k">print</span> <span class="s">&quot;Name:&quot;</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">],</span> <span class="s">&quot;; Email Address&quot;</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="n">a1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">]</span>  
986
 
<div class='popup_sql'>SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address
987
 
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
988
 
WHERE users.name = ? AND (EXISTS (SELECT addresses_1.id
989
 
FROM addresses AS addresses_1
990
 
WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE ?)) ORDER BY users.fullname DESC
991
 
('jack', '%@msn.com')</div><span class="go">Name: jack ; Email Address jack@yahoo.com</span>
992
 
<span class="go">Name: jack ; Email Address jack@msn.com</span></pre></div>
993
 
</div>
994
 
</div>
995
 
</div>
996
952
<div class="section" id="everything-else">
997
953
<h2>Everything Else<a class="headerlink" href="#everything-else" title="Permalink to this headline">¶</a></h2>
998
954
<p>The concepts of creating SQL expressions have been introduced. What&#8217;s left are
1006
962
database dialect converts to the appropriate named or positional style, as
1007
963
here where it converts to positional for SQLite:</p>
1008
964
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">bindparam</span>
1009
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;username&#39;</span><span class="p">))</span>
 
965
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;username&#39;</span><span class="p">))</span>
1010
966
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">username</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1011
967
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
1012
968
FROM users
1021
977
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">username</span><span class="o">=</span><span class="s">&#39;wendy&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1022
978
<div class='popup_sql'>SELECT users.id, users.name, users.fullname
1023
979
FROM users
1024
 
WHERE users.name LIKE ? || '%'
 
980
WHERE users.name LIKE (? || '%')
1025
981
('wendy',)</div><span class="go">[(2, u&#39;wendy&#39;, u&#39;Wendy Williams&#39;)]</span></pre></div>
1026
982
</div>
1027
983
<p>Bind parameters of the same name can also be used multiple times, where only a
1028
984
single named value is needed in the execute parameters:</p>
1029
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">,</span> <span class="n">addresses</span><span class="p">],</span>
1030
 
<span class="gp">... </span>   <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;name&#39;</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span> <span class="o">+</span> <span class="n">text</span><span class="p">(</span><span class="s">&quot;&#39;%&#39;&quot;</span><span class="p">))</span> <span class="o">|</span>
1031
 
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;name&#39;</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span> <span class="o">+</span> <span class="n">text</span><span class="p">(</span><span class="s">&quot;&#39;@%&#39;&quot;</span><span class="p">)),</span>
1032
 
<span class="gp">... </span>   <span class="n">from_obj</span><span class="o">=</span><span class="p">[</span><span class="n">users</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">)])</span>
 
985
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">,</span> <span class="n">addresses</span><span class="p">])</span><span class="o">.</span>\
 
986
<span class="gp">... </span>    <span class="n">where</span><span class="p">(</span>
 
987
<span class="gp">... </span>       <span class="n">or_</span><span class="p">(</span>
 
988
<span class="gp">... </span>         <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span>
 
989
<span class="gp">... </span>                <span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;name&#39;</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span> <span class="o">+</span> <span class="n">text</span><span class="p">(</span><span class="s">&quot;&#39;%&#39;&quot;</span><span class="p">)),</span>
 
990
<span class="gp">... </span>         <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span>
 
991
<span class="gp">... </span>                <span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;name&#39;</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span> <span class="o">+</span> <span class="n">text</span><span class="p">(</span><span class="s">&quot;&#39;@%&#39;&quot;</span><span class="p">))</span>
 
992
<span class="gp">... </span>       <span class="p">)</span>
 
993
<span class="gp">... </span>    <span class="p">)</span><span class="o">.</span>\
 
994
<span class="gp">... </span>    <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
 
995
<span class="gp">... </span>    <span class="n">order_by</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
1033
996
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1034
 
<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
 
997
<div class='popup_sql'>SELECT users.id, users.name, users.fullname, addresses.id,
 
998
    addresses.user_id, addresses.email_address
1035
999
FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
1036
 
WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'
 
1000
WHERE users.name LIKE (? || '%') OR addresses.email_address LIKE (? || '@%')
 
1001
ORDER BY addresses.id
1037
1002
('jack', 'jack')</div><span class="go">[(1, u&#39;jack&#39;, u&#39;Jack Jones&#39;, 1, 1, u&#39;jack@yahoo.com&#39;), (1, u&#39;jack&#39;, u&#39;Jack Jones&#39;, 2, 1, u&#39;jack@msn.com&#39;)]</span></pre></div>
1038
1003
</div>
1039
1004
</div>
1040
1005
<div class="section" id="functions">
1041
1006
<h3>Functions<a class="headerlink" href="#functions" title="Permalink to this headline">¶</a></h3>
1042
 
<p>SQL functions are created using the <tt class="xref py py-attr docutils literal"><span class="pre">func</span></tt> keyword, which
 
1007
<p>SQL functions are created using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal"><span class="pre">func</span></tt></a> keyword, which
1043
1008
generates functions using attribute access:</p>
1044
1009
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">func</span>
1045
1010
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">func</span><span class="o">.</span><span class="n">now</span><span class="p">()</span>
1067
1032
Below, we use the result function <tt class="docutils literal"><span class="pre">scalar()</span></tt> to just read the first column
1068
1033
of the first row and then close the result; the label, even though present, is
1069
1034
not important in this case:</p>
1070
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
1071
 
<span class="gp">... </span>    <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">max</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">&#39;maxemail&#39;</span><span class="p">)])</span>
1072
 
<span class="gp">... </span><span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span> 
 
1035
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
 
1036
<span class="gp">... </span>    <span class="n">select</span><span class="p">([</span>
 
1037
<span class="gp">... </span>           <span class="n">func</span><span class="o">.</span><span class="n">max</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span><span class="o">.</span>
 
1038
<span class="gp">... </span>               <span class="n">label</span><span class="p">(</span><span class="s">&#39;maxemail&#39;</span><span class="p">)</span>
 
1039
<span class="gp">... </span>          <span class="p">])</span>
 
1040
<span class="gp">... </span>    <span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span> 
1073
1041
<div class='show_sql'>SELECT max(addresses.email_address) AS maxemail
1074
1042
FROM addresses
1075
 
()</div><span class="go">www@www.org</span></pre></div>
 
1043
()</div><span class="go">u&#39;www@www.org&#39;</span></pre></div>
1076
1044
</div>
1077
1045
<p>Databases such as PostgreSQL and Oracle which support functions that return
1078
1046
whole result sets can be assembled into selectable units, which can be used in
1081
1049
<tt class="docutils literal"><span class="pre">q</span></tt>, <tt class="docutils literal"><span class="pre">z</span></tt> and <tt class="docutils literal"><span class="pre">r</span></tt>, we can construct using &#8220;lexical&#8221; column objects as
1082
1050
well as bind parameters:</p>
1083
1051
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">column</span>
1084
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">calculate</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">column</span><span class="p">(</span><span class="s">&#39;q&#39;</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s">&#39;z&#39;</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s">&#39;r&#39;</span><span class="p">)],</span>
1085
 
<span class="gp">... </span>    <span class="n">from_obj</span><span class="o">=</span><span class="p">[</span><span class="n">func</span><span class="o">.</span><span class="n">calculate</span><span class="p">(</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;x&#39;</span><span class="p">),</span> <span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;y&#39;</span><span class="p">))])</span>
1086
 
 
1087
 
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">&gt;</span> <span class="n">calculate</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">)</span> 
 
1052
<span class="gp">&gt;&gt;&gt; </span><span class="n">calculate</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">column</span><span class="p">(</span><span class="s">&#39;q&#39;</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s">&#39;z&#39;</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s">&#39;r&#39;</span><span class="p">)])</span><span class="o">.</span>\
 
1053
<span class="gp">... </span>       <span class="n">select_from</span><span class="p">(</span>
 
1054
<span class="gp">... </span>            <span class="n">func</span><span class="o">.</span><span class="n">calculate</span><span class="p">(</span>
 
1055
<span class="gp">... </span>                   <span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;x&#39;</span><span class="p">),</span>
 
1056
<span class="gp">... </span>                   <span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;y&#39;</span><span class="p">)</span>
 
1057
<span class="gp">... </span>               <span class="p">)</span>
 
1058
<span class="gp">... </span>            <span class="p">)</span>
 
1059
<span class="gp">&gt;&gt;&gt; </span><span class="n">calc</span> <span class="o">=</span> <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
 
1060
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">&gt;</span> <span class="n">calc</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">)</span> 
1088
1061
<span class="go">SELECT users.id, users.name, users.fullname</span>
1089
1062
<span class="go">FROM users, (SELECT q, z, r</span>
1090
 
<span class="go">FROM calculate(:x, :y))</span>
1091
 
<span class="go">WHERE users.id &gt; z</span></pre></div>
 
1063
<span class="go">FROM calculate(:x, :y)) AS anon_1</span>
 
1064
<span class="go">WHERE users.id &gt; anon_1.z</span></pre></div>
1092
1065
</div>
1093
1066
<p>If we wanted to use our <tt class="docutils literal"><span class="pre">calculate</span></tt> statement twice with different bind
1094
1067
parameters, the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ClauseElement.unique_params" title="sqlalchemy.sql.expression.ClauseElement.unique_params"><tt class="xref py py-func docutils literal"><span class="pre">unique_params()</span></tt></a>
1095
1068
function will create copies for us, and mark the bind parameters as &#8220;unique&#8221;
1096
1069
so that conflicting names are isolated. Note we also make two separate aliases
1097
1070
of our selectable:</p>
1098
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">.</span><span class="n">between</span><span class="p">(</span>
1099
 
<span class="gp">... </span>   <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s">&#39;c1&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">unique_params</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="mi">17</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="mi">45</span><span class="p">)</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">,</span>
1100
 
<span class="gp">... </span>   <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s">&#39;c2&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">unique_params</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="mi">5</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="mi">12</span><span class="p">)</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">))</span>
1101
 
 
 
1071
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">calc1</span> <span class="o">=</span> <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s">&#39;c1&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">unique_params</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="mi">17</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="mi">45</span><span class="p">)</span>
 
1072
<span class="gp">&gt;&gt;&gt; </span><span class="n">calc2</span> <span class="o">=</span> <span class="n">calculate</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s">&#39;c2&#39;</span><span class="p">)</span><span class="o">.</span><span class="n">unique_params</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="mi">5</span><span class="p">,</span> <span class="n">y</span><span class="o">=</span><span class="mi">12</span><span class="p">)</span>
 
1073
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="p">])</span><span class="o">.</span>\
 
1074
<span class="gp">... </span>        <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">.</span><span class="n">between</span><span class="p">(</span><span class="n">calc1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">,</span> <span class="n">calc2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">z</span><span class="p">))</span>
1102
1075
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">s</span> 
1103
1076
<span class="go">SELECT users.id, users.name, users.fullname</span>
1104
 
<span class="go">FROM users, (SELECT q, z, r</span>
1105
 
<span class="go">FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r</span>
1106
 
<span class="go">FROM calculate(:x_2, :y_2)) AS c2</span>
 
1077
<span class="go">FROM users,</span>
 
1078
<span class="go">    (SELECT q, z, r FROM calculate(:x_1, :y_1)) AS c1,</span>
 
1079
<span class="go">    (SELECT q, z, r FROM calculate(:x_2, :y_2)) AS c2</span>
1107
1080
<span class="go">WHERE users.id BETWEEN c1.z AND c2.z</span>
1108
1081
 
1109
1082
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span><span class="o">.</span><span class="n">compile</span><span class="p">()</span><span class="o">.</span><span class="n">params</span>
1110
1083
<span class="go">{u&#39;x_2&#39;: 5, u&#39;y_2&#39;: 12, u&#39;y_1&#39;: 45, u&#39;x_1&#39;: 17}</span></pre></div>
1111
1084
</div>
1112
 
<p>See also <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-attr docutils literal"><span class="pre">sqlalchemy.sql.expression.func</span></tt></a>.</p>
1113
1085
</div>
1114
1086
<div class="section" id="window-functions">
1115
1087
<h3>Window Functions<a class="headerlink" href="#window-functions" title="Permalink to this headline">¶</a></h3>
1116
1088
<p>Any <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FunctionElement" title="sqlalchemy.sql.expression.FunctionElement"><tt class="xref py py-class docutils literal"><span class="pre">FunctionElement</span></tt></a>, including functions generated by
1117
 
<tt class="xref py py-attr docutils literal"><span class="pre">func</span></tt>, can be turned into a &#8220;window function&#8221;, that is an
 
1089
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><tt class="xref py py-data docutils literal"><span class="pre">func</span></tt></a>, can be turned into a &#8220;window function&#8221;, that is an
1118
1090
OVER clause, using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FunctionElement.over" title="sqlalchemy.sql.expression.FunctionElement.over"><tt class="xref py py-meth docutils literal"><span class="pre">over()</span></tt></a> method:</p>
1119
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">row_number</span><span class="p">()</span><span class="o">.</span><span class="n">over</span><span class="p">(</span><span class="n">order_by</span><span class="o">=</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)])</span>
 
1091
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
 
1092
<span class="gp">... </span>        <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span>
 
1093
<span class="gp">... </span>        <span class="n">func</span><span class="o">.</span><span class="n">row_number</span><span class="p">()</span><span class="o">.</span><span class="n">over</span><span class="p">(</span><span class="n">order_by</span><span class="o">=</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
 
1094
<span class="gp">... </span>    <span class="p">])</span>
1120
1095
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">s</span> 
1121
1096
<span class="go">SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1</span>
1122
1097
<span class="go">FROM users</span></pre></div>
1125
1100
<div class="section" id="unions-and-other-set-operations">
1126
1101
<h3>Unions and Other Set Operations<a class="headerlink" href="#unions-and-other-set-operations" title="Permalink to this headline">¶</a></h3>
1127
1102
<p>Unions come in two flavors, UNION and UNION ALL, which are available via
1128
 
module level functions:</p>
 
1103
module level functions <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.union" title="sqlalchemy.sql.expression.union"><tt class="xref py py-func docutils literal"><span class="pre">union()</span></tt></a> and
 
1104
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.union_all" title="sqlalchemy.sql.expression.union_all"><tt class="xref py py-func docutils literal"><span class="pre">union_all()</span></tt></a>:</p>
1129
1105
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">union</span>
1130
1106
<span class="gp">&gt;&gt;&gt; </span><span class="n">u</span> <span class="o">=</span> <span class="n">union</span><span class="p">(</span>
1131
 
<span class="gp">... </span>    <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">&#39;foo@bar.com&#39;</span><span class="p">),</span>
1132
 
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@yahoo.com&#39;</span><span class="p">)),</span>
 
1107
<span class="gp">... </span>    <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
 
1108
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;foo@bar.com&#39;</span><span class="p">),</span>
 
1109
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
 
1110
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@yahoo.com&#39;</span><span class="p">)),</span>
1133
1111
<span class="gp">... </span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span>
1134
1112
 
1135
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
 
1113
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1136
1114
<div class='popup_sql'>SELECT addresses.id, addresses.user_id, addresses.email_address
1137
1115
FROM addresses
1138
 
WHERE addresses.email_address = ? UNION SELECT addresses.id, addresses.user_id, addresses.email_address
 
1116
WHERE addresses.email_address = ?
 
1117
UNION
 
1118
SELECT addresses.id, addresses.user_id, addresses.email_address
1139
1119
FROM addresses
1140
1120
WHERE addresses.email_address LIKE ? ORDER BY addresses.email_address
1141
1121
('foo@bar.com', '%@yahoo.com')</div><span class="go">[(1, 1, u&#39;jack@yahoo.com&#39;)]</span></pre></div>
1142
1122
</div>
1143
 
<p>Also available, though not supported on all databases, are <tt class="docutils literal"><span class="pre">intersect()</span></tt>,
1144
 
<tt class="docutils literal"><span class="pre">intersect_all()</span></tt>, <tt class="docutils literal"><span class="pre">except_()</span></tt>, and <tt class="docutils literal"><span class="pre">except_all()</span></tt>:</p>
 
1123
<p>Also available, though not supported on all databases, are
 
1124
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.intersect" title="sqlalchemy.sql.expression.intersect"><tt class="xref py py-func docutils literal"><span class="pre">intersect()</span></tt></a>,
 
1125
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.intersect_all" title="sqlalchemy.sql.expression.intersect_all"><tt class="xref py py-func docutils literal"><span class="pre">intersect_all()</span></tt></a>,
 
1126
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.except_" title="sqlalchemy.sql.expression.except_"><tt class="xref py py-func docutils literal"><span class="pre">except_()</span></tt></a>, and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.except_all" title="sqlalchemy.sql.expression.except_all"><tt class="xref py py-func docutils literal"><span class="pre">except_all()</span></tt></a>:</p>
1145
1127
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">except_</span>
1146
1128
<span class="gp">&gt;&gt;&gt; </span><span class="n">u</span> <span class="o">=</span> <span class="n">except_</span><span class="p">(</span>
1147
 
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@%.com&#39;</span><span class="p">)),</span>
1148
 
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">))</span>
 
1129
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
 
1130
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@%.com&#39;</span><span class="p">)),</span>
 
1131
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
 
1132
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">))</span>
1149
1133
<span class="gp">... </span><span class="p">)</span>
1150
1134
 
1151
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
 
1135
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1152
1136
<div class='popup_sql'>SELECT addresses.id, addresses.user_id, addresses.email_address
1153
1137
FROM addresses
1154
 
WHERE addresses.email_address LIKE ? EXCEPT SELECT addresses.id, addresses.user_id, addresses.email_address
 
1138
WHERE addresses.email_address LIKE ?
 
1139
EXCEPT
 
1140
SELECT addresses.id, addresses.user_id, addresses.email_address
1155
1141
FROM addresses
1156
1142
WHERE addresses.email_address LIKE ?
1157
1143
('%@%.com', '%@msn.com')</div><span class="go">[(1, 1, u&#39;jack@yahoo.com&#39;), (4, 2, u&#39;wendy@aol.com&#39;)]</span></pre></div>
1165
1151
want the &#8220;union&#8221; to be stated as a subquery:</p>
1166
1152
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">u</span> <span class="o">=</span> <span class="n">except_</span><span class="p">(</span>
1167
1153
<span class="gp">... </span>   <span class="n">union</span><span class="p">(</span>
1168
 
<span class="gp">... </span>        <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@yahoo.com&#39;</span><span class="p">)),</span>
1169
 
<span class="gp">... </span>        <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">))</span>
 
1154
<span class="gp">... </span>        <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
 
1155
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@yahoo.com&#39;</span><span class="p">)),</span>
 
1156
<span class="gp">... </span>        <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span>
 
1157
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">))</span>
1170
1158
<span class="gp">... </span>    <span class="p">)</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span><span class="o">.</span><span class="n">select</span><span class="p">(),</span>   <span class="c"># apply subquery here</span>
1171
1159
<span class="gp">... </span>   <span class="n">addresses</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">&#39;%@msn.com&#39;</span><span class="p">))</span>
1172
1160
<span class="gp">... </span><span class="p">)</span>
1173
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>   
 
1161
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>   
1174
1162
<div class='popup_sql'>SELECT anon_1.id, anon_1.user_id, anon_1.email_address
1175
1163
FROM (SELECT addresses.id AS id, addresses.user_id AS user_id,
1176
 
addresses.email_address AS email_address FROM addresses
1177
 
WHERE addresses.email_address LIKE ? UNION SELECT addresses.id AS id,
1178
 
addresses.user_id AS user_id, addresses.email_address AS email_address
1179
 
FROM addresses WHERE addresses.email_address LIKE ?) AS anon_1 EXCEPT
 
1164
    addresses.email_address AS email_address
 
1165
    FROM addresses
 
1166
    WHERE addresses.email_address LIKE ?
 
1167
    UNION
 
1168
    SELECT addresses.id AS id,
 
1169
        addresses.user_id AS user_id,
 
1170
        addresses.email_address AS email_address
 
1171
    FROM addresses
 
1172
    WHERE addresses.email_address LIKE ?) AS anon_1
 
1173
EXCEPT
1180
1174
SELECT addresses.id, addresses.user_id, addresses.email_address
1181
1175
FROM addresses
1182
1176
WHERE addresses.email_address LIKE ?
1184
1178
</div>
1185
1179
</div>
1186
1180
<div class="section" id="scalar-selects">
1187
 
<h3>Scalar Selects<a class="headerlink" href="#scalar-selects" title="Permalink to this headline">¶</a></h3>
1188
 
<p>To embed a SELECT in a column expression, use
1189
 
<tt class="xref py py-func docutils literal"><span class="pre">as_scalar()</span></tt>:</p>
1190
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">([</span>   
1191
 
<span class="gp">... </span>      <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
1192
 
<span class="gp">... </span>      <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">as_scalar</span><span class="p">()</span>
1193
 
<span class="gp">... </span>   <span class="p">]))</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
1194
 
<div class='popup_sql'>SELECT users.name, (SELECT count(addresses.id) AS count_1
 
1181
<span id="id1"></span><h3>Scalar Selects<a class="headerlink" href="#scalar-selects" title="Permalink to this headline">¶</a></h3>
 
1182
<p>A scalar select is a SELECT that returns exactly one row and one
 
1183
column.  It can then be used as a column expression.  A scalar select
 
1184
is often a <a class="reference internal" href="../glossary.html#term-correlated-subquery"><em class="xref std std-term">correlated subquery</em></a>, which relies upon the enclosing
 
1185
SELECT statement in order to acquire at least one of its FROM clauses.</p>
 
1186
<p>The <a class="reference internal" href="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 can be modified to act as a
 
1187
column expression by calling either the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.as_scalar" title="sqlalchemy.sql.expression.SelectBase.as_scalar"><tt class="xref py py-meth docutils literal"><span class="pre">as_scalar()</span></tt></a>
 
1188
or <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><tt class="xref py py-meth docutils literal"><span class="pre">label()</span></tt></a> method:</p>
 
1189
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
 
1190
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
 
1191
<span class="gp">... </span>            <span class="n">as_scalar</span><span class="p">()</span></pre></div>
 
1192
</div>
 
1193
<p>The above construct is now a <tt class="xref py py-class docutils literal"><span class="pre">ScalarSelect</span></tt> object,
 
1194
and is no longer part of the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FromClause" title="sqlalchemy.sql.expression.FromClause"><tt class="xref py py-class docutils literal"><span class="pre">FromClause</span></tt></a> hierarchy;
 
1195
it instead is within the <a class="reference internal" href="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> family of
 
1196
expression constructs.  We can place this construct the same as any
 
1197
other column within another <a class="reference internal" href="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>:</p>
 
1198
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">stmt</span><span class="p">]))</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1199
<div class='show_sql'>SELECT users.name, (SELECT count(addresses.id) AS count_1
1195
1200
FROM addresses
1196
1201
WHERE users.id = addresses.user_id) AS anon_1
1197
1202
FROM users
1198
 
()</div><span class="go">[(u&#39;jack&#39;, 2), (u&#39;wendy&#39;, 2), (u&#39;fred&#39;, 0), (u&#39;mary&#39;, 0)]</span></pre></div>
 
1203
()</div><span class="go">[(u&#39;jack&#39;, 2), (u&#39;wendy&#39;, 2)]</span></pre></div>
1199
1204
</div>
1200
 
<p>Alternatively, applying a <tt class="docutils literal"><span class="pre">label()</span></tt> to a select evaluates it as a scalar as
1201
 
well:</p>
1202
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">([</span>    
1203
 
<span class="gp">... </span>      <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
1204
 
<span class="gp">... </span>      <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">&#39;address_count&#39;</span><span class="p">)</span>
1205
 
<span class="gp">... </span>   <span class="p">]))</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
1206
 
<div class='popup_sql'>SELECT users.name, (SELECT count(addresses.id) AS count_1
 
1205
<p>To apply a non-anonymous column name to our scalar select, we create
 
1206
it using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.label" title="sqlalchemy.sql.expression.SelectBase.label"><tt class="xref py py-meth docutils literal"><span class="pre">SelectBase.label()</span></tt></a> instead:</p>
 
1207
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
 
1208
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
 
1209
<span class="gp">... </span>            <span class="n">label</span><span class="p">(</span><span class="s">&quot;address_count&quot;</span><span class="p">)</span>
 
1210
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">stmt</span><span class="p">]))</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1211
<div class='show_sql'>SELECT users.name, (SELECT count(addresses.id) AS count_1
1207
1212
FROM addresses
1208
1213
WHERE users.id = addresses.user_id) AS address_count
1209
1214
FROM users
1210
 
()</div><span class="go">[(u&#39;jack&#39;, 2), (u&#39;wendy&#39;, 2), (u&#39;fred&#39;, 0), (u&#39;mary&#39;, 0)]</span></pre></div>
 
1215
()</div><span class="go">[(u&#39;jack&#39;, 2), (u&#39;wendy&#39;, 2)]</span></pre></div>
1211
1216
</div>
1212
1217
</div>
1213
1218
<div class="section" id="correlated-subqueries">
1214
 
<h3>Correlated Subqueries<a class="headerlink" href="#correlated-subqueries" title="Permalink to this headline">¶</a></h3>
1215
 
<p>Notice in the examples on &#8220;scalar selects&#8221;, the FROM clause of each embedded
 
1219
<span id="id2"></span><h3>Correlated Subqueries<a class="headerlink" href="#correlated-subqueries" title="Permalink to this headline">¶</a></h3>
 
1220
<p>Notice in the examples on <a class="reference internal" href="#scalar-selects"><em>Scalar Selects</em></a>, the FROM clause of each embedded
1216
1221
select did not contain the <tt class="docutils literal"><span class="pre">users</span></tt> table in its FROM clause. This is because
1217
 
SQLAlchemy automatically attempts to correlate embedded FROM objects to that
1218
 
of an enclosing query. To disable this, or to specify explicit FROM clauses to
1219
 
be correlated, use <tt class="docutils literal"><span class="pre">correlate()</span></tt>:</p>
1220
 
<div class="highlight-python"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">])</span><span class="o">.</span><span class="n">correlate</span><span class="p">(</span><span class="bp">None</span><span class="p">))</span>
1221
 
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">s</span> 
1222
 
<span class="go">SELECT users.name</span>
1223
 
<span class="go">FROM users</span>
1224
 
<span class="go">WHERE users.id = (SELECT users.id</span>
1225
 
<span class="go">FROM users)</span>
1226
 
 
1227
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span>
1228
 
<span class="gp">... </span>       <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">correlate</span><span class="p">(</span><span class="n">addresses</span><span class="p">)</span>
1229
 
<span class="gp">... </span>   <span class="p">)</span>
1230
 
<span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">s</span> 
1231
 
<span class="go">SELECT users.name, addresses.email_address</span>
1232
 
<span class="go">FROM users, addresses</span>
1233
 
<span class="go">WHERE users.id = (SELECT users.id</span>
1234
 
<span class="go">FROM users</span>
1235
 
<span class="go">WHERE users.id = addresses.user_id)</span></pre></div>
 
1222
SQLAlchemy automatically <a class="reference internal" href="../glossary.html#term-correlates"><em class="xref std std-term">correlates</em></a> embedded FROM objects to that
 
1223
of an enclosing query, if present, and if the inner SELECT statement would
 
1224
still have at least one FROM clause of its own.  For example:</p>
 
1225
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">])</span><span class="o">.</span>\
 
1226
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span> <span class="o">==</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
 
1227
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">&#39;jack@yahoo.com&#39;</span><span class="p">)</span>
 
1228
<span class="gp">&gt;&gt;&gt; </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
 
1229
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1230
<div class='show_sql'>SELECT users.name
 
1231
FROM users
 
1232
WHERE users.id = (SELECT addresses.user_id
 
1233
    FROM addresses
 
1234
    WHERE addresses.user_id = users.id
 
1235
    AND addresses.email_address = ?)
 
1236
('jack@yahoo.com',)</div><span class="go">[(u&#39;jack&#39;,)]</span></pre></div>
 
1237
</div>
 
1238
<p>Auto-correlation will usually do what&#8217;s expected, however it can also be controlled.
 
1239
For example, if we wanted a statement to correlate only to the <tt class="docutils literal"><span class="pre">addresses</span></tt> table
 
1240
but not the <tt class="docutils literal"><span class="pre">users</span></tt> table, even if both were present in the enclosing SELECT,
 
1241
we use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.correlate" title="sqlalchemy.sql.expression.Select.correlate"><tt class="xref py py-meth docutils literal"><span class="pre">correlate()</span></tt></a> method to specify those FROM clauses that
 
1242
may be correlated:</p>
 
1243
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">])</span><span class="o">.</span>\
 
1244
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
 
1245
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span>\
 
1246
<span class="gp">... </span>            <span class="n">correlate</span><span class="p">(</span><span class="n">addresses</span><span class="p">)</span>
 
1247
<span class="gp">&gt;&gt;&gt; </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span>
 
1248
<span class="gp">... </span>        <span class="p">[</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
 
1249
<span class="gp">... </span>    <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
 
1250
<span class="gp">... </span>    <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
 
1251
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1252
<div class='show_sql'>SELECT users.name, addresses.email_address
 
1253
 FROM users JOIN addresses ON users.id = addresses.user_id
 
1254
 WHERE users.id = (SELECT users.id
 
1255
 FROM users
 
1256
 WHERE users.id = addresses.user_id AND users.name = ?)
 
1257
 ('jack',)
 
1258
 </div><span class="go">[(u&#39;jack&#39;, u&#39;jack@yahoo.com&#39;), (u&#39;jack&#39;, u&#39;jack@msn.com&#39;)]</span></pre></div>
 
1259
</div>
 
1260
<p>To entirely disable a statement from correlating, we can pass <tt class="docutils literal"><span class="pre">None</span></tt>
 
1261
as the argument:</p>
 
1262
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">])</span><span class="o">.</span>\
 
1263
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;wendy&#39;</span><span class="p">)</span><span class="o">.</span>\
 
1264
<span class="gp">... </span>            <span class="n">correlate</span><span class="p">(</span><span class="bp">None</span><span class="p">)</span>
 
1265
<span class="gp">&gt;&gt;&gt; </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">])</span><span class="o">.</span>\
 
1266
<span class="gp">... </span>    <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
 
1267
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1268
<div class='show_sql'>SELECT users.name
 
1269
 FROM users
 
1270
 WHERE users.id = (SELECT users.id
 
1271
  FROM users
 
1272
  WHERE users.name = ?)
 
1273
('wendy',)</div><span class="go">[(u&#39;wendy&#39;,)]</span></pre></div>
 
1274
</div>
 
1275
<p>We can also control correlation via exclusion, using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.correlate_except" title="sqlalchemy.sql.expression.Select.correlate_except"><tt class="xref py py-meth docutils literal"><span class="pre">Select.correlate_except()</span></tt></a>
 
1276
method.   Such as, we can write our SELECT for the <tt class="docutils literal"><span class="pre">users</span></tt> table
 
1277
by telling it to correlate all FROM clauses except for <tt class="docutils literal"><span class="pre">users</span></tt>:</p>
 
1278
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">])</span><span class="o">.</span>\
 
1279
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
 
1280
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span>\
 
1281
<span class="gp">... </span>            <span class="n">correlate_except</span><span class="p">(</span><span class="n">users</span><span class="p">)</span>
 
1282
<span class="gp">&gt;&gt;&gt; </span><span class="n">enclosing_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">(</span>
 
1283
<span class="gp">... </span>        <span class="p">[</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
 
1284
<span class="gp">... </span>    <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
 
1285
<span class="gp">... </span>    <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">stmt</span><span class="p">)</span>
 
1286
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">enclosing_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1287
<div class='show_sql'>SELECT users.name, addresses.email_address
 
1288
 FROM users JOIN addresses ON users.id = addresses.user_id
 
1289
 WHERE users.id = (SELECT users.id
 
1290
 FROM users
 
1291
 WHERE users.id = addresses.user_id AND users.name = ?)
 
1292
 ('jack',)
 
1293
 </div><span class="go">[(u&#39;jack&#39;, u&#39;jack@yahoo.com&#39;), (u&#39;jack&#39;, u&#39;jack@msn.com&#39;)]</span></pre></div>
1236
1294
</div>
1237
1295
</div>
1238
1296
<div class="section" id="ordering-grouping-limiting-offset-ing">
1239
1297
<h3>Ordering, Grouping, Limiting, Offset...ing...<a class="headerlink" href="#ordering-grouping-limiting-offset-ing" title="Permalink to this headline">¶</a></h3>
1240
 
<p>The <a class="reference internal" href="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> function can take keyword arguments <tt class="docutils literal"><span class="pre">order_by</span></tt>,
1241
 
<tt class="docutils literal"><span class="pre">group_by</span></tt> (as well as <tt class="docutils literal"><span class="pre">having</span></tt>), <tt class="docutils literal"><span class="pre">limit</span></tt>, and <tt class="docutils literal"><span class="pre">offset</span></tt>. There&#8217;s also
1242
 
<tt class="docutils literal"><span class="pre">distinct=True</span></tt>. These are all also available as generative functions.
1243
 
<tt class="docutils literal"><span class="pre">order_by()</span></tt> expressions can use the modifiers <tt class="docutils literal"><span class="pre">asc()</span></tt> or <tt class="docutils literal"><span class="pre">desc()</span></tt> to
1244
 
indicate ascending or descending.</p>
1245
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
1246
 
<span class="gp">... </span>    <span class="n">group_by</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">having</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">&gt;</span><span class="mi">1</span><span class="p">)</span>
1247
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1248
 
<div class='popup_sql'>SELECT addresses.user_id, count(addresses.id) AS count_1
1249
 
FROM addresses GROUP BY addresses.user_id
1250
 
HAVING count(addresses.id) > ?
1251
 
(1,)</div><span class="go">[(1, 2), (2, 2)]</span>
1252
 
 
1253
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">])</span><span class="o">.</span><span class="n">distinct</span><span class="p">()</span><span class="o">.</span>\
1254
 
<span class="gp">... </span>    <span class="n">order_by</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">desc</span><span class="p">(),</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
1255
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1256
 
<div class='popup_sql'>SELECT DISTINCT addresses.email_address, addresses.id
1257
 
FROM addresses ORDER BY addresses.email_address DESC, addresses.id
1258
 
()</div><span class="go">[(u&#39;www@www.org&#39;, 3), (u&#39;wendy@aol.com&#39;, 4), (u&#39;jack@yahoo.com&#39;, 1), (u&#39;jack@msn.com&#39;, 2)]</span>
1259
 
 
1260
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="p">])</span><span class="o">.</span><span class="n">offset</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span><span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
1261
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="k">print</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> 
1262
 
<div class='popup_sql'>SELECT addresses.id, addresses.user_id, addresses.email_address
1263
 
FROM addresses
1264
 
LIMIT 1 OFFSET 1
1265
 
()</div><span class="go">[(2, 1, u&#39;jack@msn.com&#39;)]</span></pre></div>
1266
 
</div>
1267
 
</div>
1268
 
</div>
1269
 
<div class="section" id="inserts-and-updates">
1270
 
<span id="id1"></span><h2>Inserts and Updates<a class="headerlink" href="#inserts-and-updates" title="Permalink to this headline">¶</a></h2>
1271
 
<p>Finally, we&#8217;re back to INSERT for some more detail. The
1272
 
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><tt class="xref py py-func docutils literal"><span class="pre">insert()</span></tt></a> construct provides a <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><tt class="xref py py-meth docutils literal"><span class="pre">values()</span></tt></a>
1273
 
method which can be used to send any value or clause expression to the VALUES
1274
 
portion of the INSERT:</p>
1275
 
<div class="highlight-python"><div class="highlight"><pre><span class="c"># insert from a function</span>
1276
 
<span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="mi">12</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="n">func</span><span class="o">.</span><span class="n">upper</span><span class="p">(</span><span class="s">&#39;jack&#39;</span><span class="p">))</span>
1277
 
 
1278
 
<span class="c"># insert from a concatenation expression</span>
1279
 
<span class="n">addresses</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">email_address</span> <span class="o">=</span> <span class="n">name</span> <span class="o">+</span> <span class="s">&#39;@&#39;</span> <span class="o">+</span> <span class="n">host</span><span class="p">)</span></pre></div>
1280
 
</div>
1281
 
<p><tt class="docutils literal"><span class="pre">values()</span></tt> can be mixed with per-execution values:</p>
1282
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
1283
 
    <span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="n">func</span><span class="o">.</span><span class="n">upper</span><span class="p">(</span><span class="s">&#39;jack&#39;</span><span class="p">)),</span>
1284
 
    <span class="n">fullname</span><span class="o">=</span><span class="s">&#39;Jack Jones&#39;</span>
1285
 
<span class="p">)</span></pre></div>
1286
 
</div>
1287
 
<p><a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a> constructs can be passed, however
1288
 
the names of the table&#8217;s columns are reserved for the &#8220;automatic&#8221; generation
1289
 
of bind names:</p>
1290
 
<div class="highlight-python"><div class="highlight"><pre><span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;_id&#39;</span><span class="p">),</span> <span class="n">name</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;_name&#39;</span><span class="p">))</span>
1291
 
 
1292
 
<span class="c"># insert many rows at once:</span>
1293
 
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
1294
 
    <span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;_id&#39;</span><span class="p">),</span> <span class="n">name</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;_name&#39;</span><span class="p">)),</span>
1295
 
    <span class="p">[</span>
1296
 
        <span class="p">{</span><span class="s">&#39;_id&#39;</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span> <span class="s">&#39;_name&#39;</span><span class="p">:</span><span class="s">&#39;name1&#39;</span><span class="p">},</span>
1297
 
        <span class="p">{</span><span class="s">&#39;_id&#39;</span><span class="p">:</span><span class="mi">2</span><span class="p">,</span> <span class="s">&#39;_name&#39;</span><span class="p">:</span><span class="s">&#39;name2&#39;</span><span class="p">},</span>
1298
 
        <span class="p">{</span><span class="s">&#39;_id&#39;</span><span class="p">:</span><span class="mi">3</span><span class="p">,</span> <span class="s">&#39;_name&#39;</span><span class="p">:</span><span class="s">&#39;name3&#39;</span><span class="p">},</span>
1299
 
    <span class="p">]</span>
1300
 
<span class="p">)</span></pre></div>
1301
 
</div>
1302
 
<p>An UPDATE statement is emitted using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.update" title="sqlalchemy.sql.expression.update"><tt class="xref py py-func docutils literal"><span class="pre">update()</span></tt></a> construct.  These
1303
 
work much like an INSERT, except there is an additional WHERE clause
 
1298
<p>Ordering is done by passing column expressions to the
 
1299
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.order_by" title="sqlalchemy.sql.expression.SelectBase.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">order_by()</span></tt></a> method:</p>
 
1300
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">])</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
 
1301
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1302
<div class='show_sql'>SELECT users.name
 
1303
FROM users ORDER BY users.name
 
1304
()</div><span class="go">[(u&#39;jack&#39;,), (u&#39;wendy&#39;,)]</span></pre></div>
 
1305
</div>
 
1306
<p>Ascending or descending can be controlled using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.asc" title="sqlalchemy.sql.expression.ColumnElement.asc"><tt class="xref py py-meth docutils literal"><span class="pre">asc()</span></tt></a>
 
1307
and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ColumnElement.desc" title="sqlalchemy.sql.expression.ColumnElement.desc"><tt class="xref py py-meth docutils literal"><span class="pre">desc()</span></tt></a> modifiers:</p>
 
1308
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">])</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span>
 
1309
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1310
<div class='show_sql'>SELECT users.name
 
1311
FROM users ORDER BY users.name DESC
 
1312
()</div><span class="go">[(u&#39;wendy&#39;,), (u&#39;jack&#39;,)]</span></pre></div>
 
1313
</div>
 
1314
<p>Grouping refers to the GROUP BY clause, and is usually used in conjunction
 
1315
with aggregate functions to establish groups of rows to be aggregated.
 
1316
This is provided via the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.SelectBase.group_by" title="sqlalchemy.sql.expression.SelectBase.group_by"><tt class="xref py py-meth docutils literal"><span class="pre">group_by()</span></tt></a> method:</p>
 
1317
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
 
1318
<span class="gp">... </span>            <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
 
1319
<span class="gp">... </span>            <span class="n">group_by</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
 
1320
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1321
<div class='show_sql'>SELECT users.name, count(addresses.id) AS count_1
 
1322
FROM users JOIN addresses
 
1323
    ON users.id = addresses.user_id
 
1324
GROUP BY users.name
 
1325
()</div><span class="go">[(u&#39;jack&#39;, 2), (u&#39;wendy&#39;, 2)]</span></pre></div>
 
1326
</div>
 
1327
<p>HAVING can be used to filter results on an aggregate value, after GROUP BY has
 
1328
been applied.  It&#8217;s available here via the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.having" title="sqlalchemy.sql.expression.Select.having"><tt class="xref py py-meth docutils literal"><span class="pre">having()</span></tt></a>
 
1329
method:</p>
 
1330
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)])</span><span class="o">.</span>\
 
1331
<span class="gp">... </span>            <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
 
1332
<span class="gp">... </span>            <span class="n">group_by</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
 
1333
<span class="gp">... </span>            <span class="n">having</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">length</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span> <span class="o">&gt;</span> <span class="mi">4</span><span class="p">)</span>
 
1334
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1335
<div class='show_sql'>SELECT users.name, count(addresses.id) AS count_1
 
1336
FROM users JOIN addresses
 
1337
    ON users.id = addresses.user_id
 
1338
GROUP BY users.name
 
1339
HAVING length(users.name) > ?
 
1340
(4,)</div><span class="go">[(u&#39;wendy&#39;, 2)]</span></pre></div>
 
1341
</div>
 
1342
<p>A common system of dealing with duplicates in composed SELECT statments
 
1343
is the DISTINCT modifier.  A simple DISTINCT clause can be added using the
 
1344
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.distinct" title="sqlalchemy.sql.expression.Select.distinct"><tt class="xref py py-meth docutils literal"><span class="pre">Select.distinct()</span></tt></a> method:</p>
 
1345
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">])</span><span class="o">.</span>\
 
1346
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span>
 
1347
<span class="gp">... </span>                   <span class="n">contains</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">))</span><span class="o">.</span>\
 
1348
<span class="gp">... </span>            <span class="n">distinct</span><span class="p">()</span>
 
1349
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1350
<div class='show_sql'>SELECT DISTINCT users.name
 
1351
FROM users, addresses
 
1352
WHERE addresses.email_address LIKE '%%' || users.name || '%%'
 
1353
()</div><span class="go">[(u&#39;jack&#39;,), (u&#39;wendy&#39;,)]</span></pre></div>
 
1354
</div>
 
1355
<p>Most database backends support a system of limiting how many rows
 
1356
are returned, and the majority also feature a means of starting to return
 
1357
rows after a given &#8220;offset&#8221;.   While common backends like Postgresql,
 
1358
MySQL and SQLite support LIMIT and OFFSET keywords, other backends
 
1359
need to refer to more esoteric features such as &#8220;window functions&#8221;
 
1360
and row ids to achieve the same effect.  The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.limit" title="sqlalchemy.sql.expression.Select.limit"><tt class="xref py py-meth docutils literal"><span class="pre">limit()</span></tt></a>
 
1361
and <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Select.offset" title="sqlalchemy.sql.expression.Select.offset"><tt class="xref py py-meth docutils literal"><span class="pre">offset()</span></tt></a> methods provide an easy abstraction
 
1362
into the current backend&#8217;s methodology:</p>
 
1363
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
 
1364
<span class="gp">... </span>            <span class="n">select_from</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
 
1365
<span class="gp">... </span>            <span class="n">limit</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span><span class="o">.</span><span class="n">offset</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
 
1366
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>  
 
1367
<div class='show_sql'>SELECT users.name, addresses.email_address
 
1368
FROM users JOIN addresses ON users.id = addresses.user_id
 
1369
 LIMIT ? OFFSET ?
 
1370
(1, 1)</div><span class="go">[(u&#39;jack&#39;, u&#39;jack@msn.com&#39;)]</span></pre></div>
 
1371
</div>
 
1372
</div>
 
1373
</div>
 
1374
<div class="section" id="inserts-updates-and-deletes">
 
1375
<span id="inserts-and-updates"></span><h2>Inserts, Updates and Deletes<a class="headerlink" href="#inserts-updates-and-deletes" title="Permalink to this headline">¶</a></h2>
 
1376
<p>We&#8217;ve seen <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></a> demonstrated
 
1377
earlier in this tutorial.   Where <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></a>
 
1378
prodces INSERT, the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a>
 
1379
method produces UPDATE.  Both of these constructs feature
 
1380
a method called <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><tt class="xref py py-meth docutils literal"><span class="pre">values()</span></tt></a> which specifies
 
1381
the VALUES or SET clause of the statement.</p>
 
1382
<p>The <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><tt class="xref py py-meth docutils literal"><span class="pre">values()</span></tt></a> method accommodates any column expression
 
1383
as a value:</p>
 
1384
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
 
1385
<span class="gp">... </span>            <span class="n">values</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="s">&quot;Fullname: &quot;</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
 
1386
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span> 
 
1387
<div class='show_sql'>UPDATE users SET fullname=(? || users.name)
 
1388
('Fullname: ',)
 
1389
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span></pre></div>
 
1390
</div>
 
1391
<p>When using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></a> or <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a>
 
1392
in an &#8220;execute many&#8221; context, we may also want to specify named
 
1393
bound parameters which we can refer to in the argument list.
 
1394
The two constructs will automatically generate bound placeholders
 
1395
for any column names passed in the dictionaries sent to
 
1396
<a class="reference internal" href="connections.html#sqlalchemy.engine.Connection.execute" title="sqlalchemy.engine.Connection.execute"><tt class="xref py py-meth docutils literal"><span class="pre">execute()</span></tt></a> at execution time.  However, if we
 
1397
wish to use explicitly targeted named parameters with composed expressions,
 
1398
we need to use the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a> construct.
 
1399
When using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a> with
 
1400
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.insert" title="sqlalchemy.sql.expression.TableClause.insert"><tt class="xref py py-meth docutils literal"><span class="pre">insert()</span></tt></a> or <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a>,
 
1401
the names of the table&#8217;s columns themselves are reserved for the
 
1402
&#8220;automatic&#8221; generation of bind names.  We can combine the usage
 
1403
of implicitly available bind names and explicitly named parameters
 
1404
as in the example below:</p>
 
1405
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span>\
 
1406
<span class="gp">... </span>        <span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;_name&#39;</span><span class="p">)</span> <span class="o">+</span> <span class="s">&quot; .. name&quot;</span><span class="p">)</span>
 
1407
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">,</span> <span class="p">[</span>               
 
1408
<span class="gp">... </span>       <span class="p">{</span><span class="s">&#39;id&#39;</span><span class="p">:</span><span class="mi">4</span><span class="p">,</span> <span class="s">&#39;_name&#39;</span><span class="p">:</span><span class="s">&#39;name1&#39;</span><span class="p">},</span>
 
1409
<span class="gp">... </span>       <span class="p">{</span><span class="s">&#39;id&#39;</span><span class="p">:</span><span class="mi">5</span><span class="p">,</span> <span class="s">&#39;_name&#39;</span><span class="p">:</span><span class="s">&#39;name2&#39;</span><span class="p">},</span>
 
1410
<span class="gp">... </span>       <span class="p">{</span><span class="s">&#39;id&#39;</span><span class="p">:</span><span class="mi">6</span><span class="p">,</span> <span class="s">&#39;_name&#39;</span><span class="p">:</span><span class="s">&#39;name3&#39;</span><span class="p">},</span>
 
1411
<span class="gp">... </span>    <span class="p">])</span>
 
1412
<div class='show_sql'>INSERT INTO users (id, name) VALUES (?, (? || ?))
 
1413
((4, 'name1', ' .. name'), (5, 'name2', ' .. name'), (6, 'name3', ' .. name'))
 
1414
COMMIT
 
1415
<sqlalchemy.engine.result.ResultProxy object at 0x...></div></pre></div>
 
1416
</div>
 
1417
<p>An UPDATE statement is emitted using the <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a> construct.  This
 
1418
works much like an INSERT, except there is an additional WHERE clause
1304
1419
that can be specified:</p>
1305
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="c"># change &#39;jack&#39; to &#39;ed&#39;</span>
1306
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>
1307
 
<span class="gp">... </span>                   <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span>
1308
 
<span class="gp">... </span>                   <span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">)</span>
1309
 
<span class="gp">... </span>               <span class="p">)</span> 
1310
 
<div class='popup_sql'>UPDATE users SET name=? WHERE users.name = ?
 
1420
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
 
1421
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">&#39;jack&#39;</span><span class="p">)</span><span class="o">.</span>\
 
1422
<span class="gp">... </span>            <span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">)</span>
 
1423
 
 
1424
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span> 
 
1425
<div class='show_sql'>UPDATE users SET name=? WHERE users.name = ?
1311
1426
('ed', 'jack')
1312
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span>
1313
 
 
1314
 
<span class="gp">&gt;&gt;&gt; </span><span class="c"># use bind parameters</span>
1315
 
<span class="gp">&gt;&gt;&gt; </span><span class="n">u</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
1316
 
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;oldname&#39;</span><span class="p">))</span><span class="o">.</span>\
 
1427
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span></pre></div>
 
1428
</div>
 
1429
<p>When using <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a> in an &#8220;execute many&#8221; context,
 
1430
we may wish to also use explicitly named bound parameters in the
 
1431
WHERE clause.  Again, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a> is the construct
 
1432
used to achieve this:</p>
 
1433
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
 
1434
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;oldname&#39;</span><span class="p">))</span><span class="o">.</span>\
1317
1435
<span class="gp">... </span>            <span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="n">bindparam</span><span class="p">(</span><span class="s">&#39;newname&#39;</span><span class="p">))</span>
1318
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">,</span> <span class="n">oldname</span><span class="o">=</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="n">newname</span><span class="o">=</span><span class="s">&#39;ed&#39;</span><span class="p">)</span> 
1319
 
<div class='popup_sql'>UPDATE users SET name=? WHERE users.name = ?
1320
 
('ed', 'jack')
1321
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span>
1322
 
 
1323
 
<span class="gp">&gt;&gt;&gt; </span><span class="c"># with binds, you can also update many rows at once</span>
1324
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">u</span><span class="p">,</span>
 
1436
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">,</span> <span class="p">[</span>
1325
1437
<span class="gp">... </span>    <span class="p">{</span><span class="s">&#39;oldname&#39;</span><span class="p">:</span><span class="s">&#39;jack&#39;</span><span class="p">,</span> <span class="s">&#39;newname&#39;</span><span class="p">:</span><span class="s">&#39;ed&#39;</span><span class="p">},</span>
1326
1438
<span class="gp">... </span>    <span class="p">{</span><span class="s">&#39;oldname&#39;</span><span class="p">:</span><span class="s">&#39;wendy&#39;</span><span class="p">,</span> <span class="s">&#39;newname&#39;</span><span class="p">:</span><span class="s">&#39;mary&#39;</span><span class="p">},</span>
1327
1439
<span class="gp">... </span>    <span class="p">{</span><span class="s">&#39;oldname&#39;</span><span class="p">:</span><span class="s">&#39;jim&#39;</span><span class="p">,</span> <span class="s">&#39;newname&#39;</span><span class="p">:</span><span class="s">&#39;jake&#39;</span><span class="p">},</span>
1328
 
<span class="gp">... </span>    <span class="p">)</span> 
1329
 
<div class='popup_sql'>UPDATE users SET name=? WHERE users.name = ?
1330
 
[('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim')]
1331
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span>
1332
 
 
1333
 
<span class="gp">&gt;&gt;&gt; </span><span class="c"># update a column to an expression.:</span>
1334
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>
1335
 
<span class="gp">... </span>                    <span class="n">values</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="s">&quot;Fullname: &quot;</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
1336
 
<span class="gp">... </span>                <span class="p">)</span> 
1337
 
<div class='popup_sql'>UPDATE users SET fullname=(? || users.name)
1338
 
('Fullname: ',)
1339
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span></pre></div>
 
1440
<span class="gp">... </span>    <span class="p">])</span> 
 
1441
<div class='show_sql'>UPDATE users SET name=? WHERE users.name = ?
 
1442
(('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim'))
 
1443
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span></pre></div>
1340
1444
</div>
1341
1445
<div class="section" id="correlated-updates">
1342
1446
<h3>Correlated Updates<a class="headerlink" href="#correlated-updates" title="Permalink to this headline">¶</a></h3>
1343
1447
<p>A correlated update lets you update a table using selection from another
1344
1448
table, or the same table:</p>
1345
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">],</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
1346
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="n">s</span><span class="p">))</span> 
1347
 
<div class='popup_sql'>UPDATE users SET fullname=(SELECT addresses.email_address
1348
 
FROM addresses
1349
 
WHERE addresses.user_id = users.id
1350
 
LIMIT 1 OFFSET 0)
1351
 
()
1352
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span></pre></div>
 
1449
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">])</span><span class="o">.</span>\
 
1450
<span class="gp">... </span>            <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span> <span class="o">==</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
 
1451
<span class="gp">... </span>            <span class="n">limit</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
 
1452
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="n">stmt</span><span class="p">))</span> 
 
1453
<div class='show_sql'>UPDATE users SET fullname=(SELECT addresses.email_address
 
1454
    FROM addresses
 
1455
    WHERE addresses.user_id = users.id
 
1456
    LIMIT ? OFFSET ?)
 
1457
(1, 0)
 
1458
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span></pre></div>
1353
1459
</div>
1354
1460
</div>
1355
1461
<div class="section" id="multiple-table-updates">
1365
1471
implicitly, by specifying multiple tables in the WHERE clause:</p>
1366
1472
<div class="highlight-python"><div class="highlight"><pre><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
1367
1473
        <span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;ed wood&#39;</span><span class="p">)</span><span class="o">.</span>\
1368
 
        <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
 
1474
        <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
1369
1475
        <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s">&#39;ed%&#39;</span><span class="p">))</span>
1370
1476
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span></pre></div>
1371
1477
</div>
1375
1481
addresses.email_address LIKE :email_address_1 || '%%'</pre>
1376
1482
</div>
1377
1483
<p>When using MySQL, columns from each table can be assigned to in the
1378
 
SET clause directly, using the dictionary form passed to <tt class="xref py py-meth docutils literal"><span class="pre">Update.values()</span></tt>:</p>
 
1484
SET clause directly, using the dictionary form passed to <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Update.values" title="sqlalchemy.sql.expression.Update.values"><tt class="xref py py-meth docutils literal"><span class="pre">Update.values()</span></tt></a>:</p>
1379
1485
<div class="highlight-python"><div class="highlight"><pre><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span>\
1380
1486
        <span class="n">values</span><span class="p">({</span>
1381
1487
            <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">:</span><span class="s">&#39;ed wood&#39;</span><span class="p">,</span>
1382
1488
            <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="p">:</span><span class="s">&#39;ed.wood@foo.com&#39;</span>
1383
1489
        <span class="p">})</span><span class="o">.</span>\
1384
 
        <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
 
1490
        <span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
1385
1491
        <span class="n">where</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s">&#39;ed%&#39;</span><span class="p">))</span></pre></div>
1386
1492
</div>
1387
1493
<p>The tables are referenced explicitly in the SET clause:</p>
1394
1500
when multiple tables are present, and the statement will be rejected
1395
1501
by the database if this syntax is not supported.</p>
1396
1502
</div>
1397
 
</div>
1398
1503
<div class="section" id="deletes">
1399
 
<span id="id2"></span><h2>Deletes<a class="headerlink" href="#deletes" title="Permalink to this headline">¶</a></h2>
 
1504
<span id="id3"></span><h3>Deletes<a class="headerlink" href="#deletes" title="Permalink to this headline">¶</a></h3>
1400
1505
<p>Finally, a delete.  This is accomplished easily enough using the
1401
 
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.delete" title="sqlalchemy.sql.expression.delete"><tt class="xref py py-func docutils literal"><span class="pre">delete()</span></tt></a> construct:</p>
1402
 
<div class="highlight-pycon+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">delete</span><span class="p">())</span> 
1403
 
<div class='popup_sql'>DELETE FROM addresses
 
1506
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><tt class="xref py py-meth docutils literal"><span class="pre">delete()</span></tt></a> construct:</p>
 
1507
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">delete</span><span class="p">())</span> 
 
1508
<div class='show_sql'>DELETE FROM addresses
1404
1509
()
1405
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span>
 
1510
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span>
1406
1511
 
1407
 
<a href='#' class='sql_link'>sql</a><span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">&gt;</span> <span class="s">&#39;m&#39;</span><span class="p">))</span> 
1408
 
<div class='popup_sql'>DELETE FROM users WHERE users.name > ?
 
1512
<span class="gp">&gt;&gt;&gt; </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="o">&gt;</span> <span class="s">&#39;m&#39;</span><span class="p">))</span> 
 
1513
<div class='show_sql'>DELETE FROM users WHERE users.name > ?
1409
1514
('m',)
1410
 
COMMIT</div><span class="go">&lt;sqlalchemy.engine.base.ResultProxy object at 0x...&gt;</span></pre></div>
 
1515
COMMIT</div><span class="go">&lt;sqlalchemy.engine.result.ResultProxy object at 0x...&gt;</span></pre></div>
 
1516
</div>
 
1517
</div>
 
1518
<div class="section" id="matched-row-counts">
 
1519
<h3>Matched Row Counts<a class="headerlink" href="#matched-row-counts" title="Permalink to this headline">¶</a></h3>
 
1520
<p>Both of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.update" title="sqlalchemy.sql.expression.TableClause.update"><tt class="xref py py-meth docutils literal"><span class="pre">update()</span></tt></a> and
 
1521
<a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.TableClause.delete" title="sqlalchemy.sql.expression.TableClause.delete"><tt class="xref py py-meth docutils literal"><span class="pre">delete()</span></tt></a> are associated with <em>matched row counts</em>.  This is a
 
1522
number indicating the number of rows that were matched by the WHERE clause.
 
1523
Note that by &#8220;matched&#8221;, this includes rows where no UPDATE actually took place.
 
1524
The value is available as <a class="reference internal" href="connections.html#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><tt class="xref py py-attr docutils literal"><span class="pre">rowcount</span></tt></a>:</p>
 
1525
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">&gt;&gt;&gt; </span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">delete</span><span class="p">())</span> 
 
1526
<div class='show_sql'>DELETE FROM users
 
1527
()
 
1528
COMMIT</div><span class="gp">&gt;&gt;&gt; </span><span class="n">result</span><span class="o">.</span><span class="n">rowcount</span>
 
1529
<span class="go">1</span></pre></div>
 
1530
</div>
1411
1531
</div>
1412
1532
</div>
1413
1533
<div class="section" id="further-reference">
1431
1551
        <a href="expression_api.html" title="next chapter">SQL Statements and Expressions API</a>
1432
1552
 
1433
1553
    <div id="docs-copyright">
1434
 
        &copy; <a href="../copyright.html">Copyright</a> 2007-2012, the SQLAlchemy authors and contributors.
 
1554
        &copy; <a href="../copyright.html">Copyright</a> 2007-2013, the SQLAlchemy authors and contributors.
1435
1555
        Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
1436
1556
    </div>
1437
1557
</div>