<!doctype html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SelectResults: Using Queries — SQLObject 3.7.0 documentation</title>
<link rel="stylesheet" href="_static/bizstyle.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<script type="text/javascript" src="_static/documentation_options.js"></script>
<script type="text/javascript" src="_static/jquery.js"></script>
<script type="text/javascript" src="_static/underscore.js"></script>
<script type="text/javascript" src="_static/doctools.js"></script>
<script type="text/javascript" src="_static/bizstyle.js"></script>
<link rel="index" title="Index" href="genindex.html" />
<link rel="search" title="Search" href="search.html" />
<link rel="next" title="The sqlobject-admin Tool" href="sqlobject-admin.html" />
<link rel="prev" title="SQLBuilder" href="SQLBuilder.html" />
<meta name="viewport" content="width=device-width,initial-scale=1.0">
<!--[if lt IE 9]>
<script type="text/javascript" src="_static/css3-mediaqueries.js"></script>
<![endif]-->
</head><body>
<div class="related" role="navigation" aria-label="related navigation">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
accesskey="I">index</a></li>
<li class="right" >
<a href="py-modindex.html" title="Python Module Index"
>modules</a> |</li>
<li class="right" >
<a href="sqlobject-admin.html" title="The sqlobject-admin Tool"
accesskey="N">next</a> |</li>
<li class="right" >
<a href="SQLBuilder.html" title="SQLBuilder"
accesskey="P">previous</a> |</li>
<li class="nav-item nav-item-0"><a href="index.html">SQLObject 3.7.0 documentation</a> »</li>
</ul>
</div>
<div class="sphinxsidebar" role="navigation" aria-label="main navigation">
<div class="sphinxsidebarwrapper">
<h3><a href="index.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">SelectResults: Using Queries</a><ul>
<li><a class="reference internal" href="#overview">Overview</a></li>
<li><a class="reference internal" href="#retrieval-methods">Retrieval Methods</a><ul>
<li><a class="reference internal" href="#iteration">Iteration</a></li>
<li><a class="reference internal" href="#getone-default-optional"><code class="docutils literal notranslate"><span class="pre">getOne(default=optional)</span></code></a></li>
</ul>
</li>
<li><a class="reference internal" href="#cloning-methods">Cloning Methods</a><ul>
<li><a class="reference internal" href="#orderby-column"><code class="docutils literal notranslate"><span class="pre">orderBy(column)</span></code></a></li>
<li><a class="reference internal" href="#limit-num"><code class="docutils literal notranslate"><span class="pre">limit(num)</span></code></a></li>
<li><a class="reference internal" href="#lazycolumns-v"><code class="docutils literal notranslate"><span class="pre">lazyColumns(v)</span></code></a></li>
<li><a class="reference internal" href="#reversed"><code class="docutils literal notranslate"><span class="pre">reversed()</span></code></a></li>
<li><a class="reference internal" href="#distinct"><code class="docutils literal notranslate"><span class="pre">distinct()</span></code></a></li>
<li><a class="reference internal" href="#filter-expression"><code class="docutils literal notranslate"><span class="pre">filter(expression)</span></code></a></li>
</ul>
</li>
<li><a class="reference internal" href="#aggregate-methods">Aggregate Methods</a><ul>
<li><a class="reference internal" href="#count"><code class="docutils literal notranslate"><span class="pre">count()</span></code></a></li>
<li><a class="reference internal" href="#sum-column"><code class="docutils literal notranslate"><span class="pre">sum(column)</span></code></a></li>
<li><a class="reference internal" href="#min-column"><code class="docutils literal notranslate"><span class="pre">min(column)</span></code></a></li>
<li><a class="reference internal" href="#max-column"><code class="docutils literal notranslate"><span class="pre">max(column)</span></code></a></li>
<li><a class="reference internal" href="#avg-column"><code class="docutils literal notranslate"><span class="pre">avg(column)</span></code></a></li>
</ul>
</li>
<li><a class="reference internal" href="#traversal-to-related-sqlobject-classes">Traversal to related SQLObject classes</a><ul>
<li><a class="reference internal" href="#throughto-join-name-and-throughto-foreign-key-name"><code class="docutils literal notranslate"><span class="pre">throughTo.join_name</span> <span class="pre">and</span> <span class="pre">throughTo.foreign_key_name</span></code></a></li>
</ul>
</li>
</ul>
</li>
</ul>
<h4>Previous topic</h4>
<p class="topless"><a href="SQLBuilder.html"
title="previous chapter">SQLBuilder</a></p>
<h4>Next topic</h4>
<p class="topless"><a href="sqlobject-admin.html"
title="next chapter">The sqlobject-admin Tool</a></p>
<div role="note" aria-label="source link">
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="_sources/SelectResults.rst.txt"
rel="nofollow">Show Source</a></li>
</ul>
</div>
<div id="searchbox" style="display: none" role="search">
<h3>Quick search</h3>
<div class="searchformwrapper">
<form class="search" action="search.html" method="get">
<input type="text" name="q" />
<input type="submit" value="Go" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body" role="main">
<div class="section" id="selectresults-using-queries">
<h1><a class="toc-backref" href="#id1">SelectResults: Using Queries</a><a class="headerlink" href="#selectresults-using-queries" title="Permalink to this headline">¶</a></h1>
<div class="contents topic" id="contents">
<p class="topic-title first">Contents:</p>
<ul class="simple">
<li><a class="reference internal" href="#selectresults-using-queries" id="id1">SelectResults: Using Queries</a><ul>
<li><a class="reference internal" href="#overview" id="id2">Overview</a></li>
<li><a class="reference internal" href="#retrieval-methods" id="id3">Retrieval Methods</a><ul>
<li><a class="reference internal" href="#iteration" id="id4">Iteration</a></li>
<li><a class="reference internal" href="#getone-default-optional" id="id5"><code class="docutils literal notranslate"><span class="pre">getOne(default=optional)</span></code></a></li>
</ul>
</li>
<li><a class="reference internal" href="#cloning-methods" id="id6">Cloning Methods</a><ul>
<li><a class="reference internal" href="#orderby-column" id="id7"><code class="docutils literal notranslate"><span class="pre">orderBy(column)</span></code></a></li>
<li><a class="reference internal" href="#limit-num" id="id8"><code class="docutils literal notranslate"><span class="pre">limit(num)</span></code></a></li>
<li><a class="reference internal" href="#lazycolumns-v" id="id9"><code class="docutils literal notranslate"><span class="pre">lazyColumns(v)</span></code></a></li>
<li><a class="reference internal" href="#reversed" id="id10"><code class="docutils literal notranslate"><span class="pre">reversed()</span></code></a></li>
<li><a class="reference internal" href="#distinct" id="id11"><code class="docutils literal notranslate"><span class="pre">distinct()</span></code></a></li>
<li><a class="reference internal" href="#filter-expression" id="id12"><code class="docutils literal notranslate"><span class="pre">filter(expression)</span></code></a></li>
</ul>
</li>
<li><a class="reference internal" href="#aggregate-methods" id="id13">Aggregate Methods</a><ul>
<li><a class="reference internal" href="#count" id="id14"><code class="docutils literal notranslate"><span class="pre">count()</span></code></a></li>
<li><a class="reference internal" href="#sum-column" id="id15"><code class="docutils literal notranslate"><span class="pre">sum(column)</span></code></a></li>
<li><a class="reference internal" href="#min-column" id="id16"><code class="docutils literal notranslate"><span class="pre">min(column)</span></code></a></li>
<li><a class="reference internal" href="#max-column" id="id17"><code class="docutils literal notranslate"><span class="pre">max(column)</span></code></a></li>
<li><a class="reference internal" href="#avg-column" id="id18"><code class="docutils literal notranslate"><span class="pre">avg(column)</span></code></a></li>
</ul>
</li>
<li><a class="reference internal" href="#traversal-to-related-sqlobject-classes" id="id19">Traversal to related SQLObject classes</a><ul>
<li><a class="reference internal" href="#throughto-join-name-and-throughto-foreign-key-name" id="id20"><code class="docutils literal notranslate"><span class="pre">throughTo.join_name</span> <span class="pre">and</span> <span class="pre">throughTo.foreign_key_name</span></code></a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
<div class="section" id="overview">
<h2><a class="toc-backref" href="#id2">Overview</a><a class="headerlink" href="#overview" title="Permalink to this headline">¶</a></h2>
<p>SelectResults are returned from <code class="docutils literal notranslate"><span class="pre">.select</span></code> and <code class="docutils literal notranslate"><span class="pre">.selectBy</span></code> methods on
SQLObject classes, and from <code class="docutils literal notranslate"><span class="pre">SQLMultipleJoin</span></code>, and <code class="docutils literal notranslate"><span class="pre">SQLRelatedJoin</span></code>
accessors on SQLObject instances.</p>
<p>SelectResults are generators, which are lazily evaluated. The SQL is only
executed when you iterate over the SelectResults, fetching rows one at a
time. This way you can iterate over large results without keeping the
entire result set in memory. You can also do things like <code class="docutils literal notranslate"><span class="pre">.reversed()</span></code>
without fetching and reversing the entire result – instead, SQLObject can
change the SQL that is sent so you get equivalent results.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">To retrieve the results all at once use the python idiom
of calling <code class="docutils literal notranslate"><span class="pre">list()</span></code> on the generator to force execution
and convert the results to a stored list.</p>
</div>
<p>You can also slice SelectResults. This modifies the SQL query, so
<code class="docutils literal notranslate"><span class="pre">peeps[:10]</span></code> will result in <code class="docutils literal notranslate"><span class="pre">LIMIT</span> <span class="pre">10</span></code> being added to the end of
the SQL query. If the slice cannot be performed in the SQL (e.g.,
peeps[:-10]), then the select is executed, and the slice is performed
on the list of results. This will generally only happen when you use
negative indexes.</p>
<p>In certain cases, you may get a SelectResults instance with an object in it
more than once, e.g., in some joins. If you don’t want this, you can add
the keyword argument <code class="docutils literal notranslate"><span class="pre">MyClass.select(...,</span> <span class="pre">distinct=True)</span></code>, which results
in a <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">DISTINCT</span></code> call.</p>
<p>You can get the length of the result without fetching all the results
by calling <code class="docutils literal notranslate"><span class="pre">count</span></code> on the result object, like
<code class="docutils literal notranslate"><span class="pre">MyClass.select().count()</span></code>. A <code class="docutils literal notranslate"><span class="pre">COUNT(*)</span></code> query is used – the
actual objects are not fetched from the database. Together with
slicing, this makes batched queries easy to write:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">start</span> <span class="o">=</span> <span class="mi">20</span>
<span class="n">size</span> <span class="o">=</span> <span class="mi">10</span>
<span class="n">query</span> <span class="o">=</span> <span class="n">Table</span><span class="o">.</span><span class="n">select</span><span class="p">()</span>
<span class="n">results</span> <span class="o">=</span> <span class="n">query</span><span class="p">[</span><span class="n">start</span><span class="p">:</span><span class="n">start</span><span class="o">+</span><span class="n">size</span><span class="p">]</span>
<span class="n">total</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<span class="nb">print</span> <span class="s2">"Showing page </span><span class="si">%i</span><span class="s2"> of </span><span class="si">%i</span><span class="s2">"</span> <span class="o">%</span> <span class="p">(</span><span class="n">start</span><span class="o">/</span><span class="n">size</span> <span class="o">+</span> <span class="mi">1</span><span class="p">,</span> <span class="n">total</span><span class="o">/</span><span class="n">size</span> <span class="o">+</span> <span class="mi">1</span><span class="p">)</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p>There are several factors when considering the efficiency of this
kind of batching, and it depends very much how the batching is
being used. Consider a web application where you are showing an
average of 100 results, 10 at a time, and the results are ordered
by the date they were added to the database. While slicing will
keep the database from returning all the results (and so save some
communication time), the database will still have to scan through
the entire result set to sort the items (so it knows which the
first ten are), and depending on your query may need to scan
through the entire table (depending on your use of indexes).
Indexes are probably the most important way to improve importance
in a case like this, and you may find caching to be more effective
than slicing.</p>
<p class="last">In this case, caching would mean retrieving the <em>complete</em> results.
You can use <code class="docutils literal notranslate"><span class="pre">list(MyClass.select(...))</span></code> to do this. You can save
these results for some limited period of time, as the user looks
through the results page by page. This means the first page in a
search result will be slightly more expensive, but all later pages
will be very cheap.</p>
</div>
</div>
<div class="section" id="retrieval-methods">
<h2><a class="toc-backref" href="#id3">Retrieval Methods</a><a class="headerlink" href="#retrieval-methods" title="Permalink to this headline">¶</a></h2>
<div class="section" id="iteration">
<h3><a class="toc-backref" href="#id4">Iteration</a><a class="headerlink" href="#iteration" title="Permalink to this headline">¶</a></h3>
<p>As mentioned in the overview, the typical way to access the results
is by treating it as a generator and iterating over it (in a loop,
by converting to a list, etc).</p>
</div>
<div class="section" id="getone-default-optional">
<h3><a class="toc-backref" href="#id5"><code class="docutils literal notranslate"><span class="pre">getOne(default=optional)</span></code></a><a class="headerlink" href="#getone-default-optional" title="Permalink to this headline">¶</a></h3>
<p>In cases where your restrictions cause there to always be a single record
in the result set, this method will return it or raise an exception:
SQLObjectIntegrityError if more than one result is found, or
SQLObjectNotFound if there are actually no results, unless you pass in
a default like <code class="docutils literal notranslate"><span class="pre">.getOne(None)</span></code>.</p>
</div>
</div>
<div class="section" id="cloning-methods">
<h2><a class="toc-backref" href="#id6">Cloning Methods</a><a class="headerlink" href="#cloning-methods" title="Permalink to this headline">¶</a></h2>
<p>These methods return a modified copy of the SelectResults instance
they are called on, so successive calls can chained, eg
<code class="docutils literal notranslate"><span class="pre">results</span> <span class="pre">=</span> <span class="pre">MyClass.selectBy(city='Boston').filter(MyClass.q.commute_distance>10).orderBy('vehicle_mileage')</span></code>
or used independently later on.</p>
<div class="section" id="orderby-column">
<h3><a class="toc-backref" href="#id7"><code class="docutils literal notranslate"><span class="pre">orderBy(column)</span></code></a><a class="headerlink" href="#orderby-column" title="Permalink to this headline">¶</a></h3>
<p>Takes a string column name (optionally prefixed with ‘-‘ for DESCending)
or a <a class="reference external" href="SQLBuilder.html">SQLBuilder expression</a>.</p>
</div>
<div class="section" id="limit-num">
<h3><a class="toc-backref" href="#id8"><code class="docutils literal notranslate"><span class="pre">limit(num)</span></code></a><a class="headerlink" href="#limit-num" title="Permalink to this headline">¶</a></h3>
<p>Only return first num many results. Equivalent to results[:num] slicing.</p>
</div>
<div class="section" id="lazycolumns-v">
<h3><a class="toc-backref" href="#id9"><code class="docutils literal notranslate"><span class="pre">lazyColumns(v)</span></code></a><a class="headerlink" href="#lazycolumns-v" title="Permalink to this headline">¶</a></h3>
<p>Only fetch the IDs for the results, the rest of the columns will be
retrieved when attributes of the returned instances are accessed.</p>
</div>
<div class="section" id="reversed">
<h3><a class="toc-backref" href="#id10"><code class="docutils literal notranslate"><span class="pre">reversed()</span></code></a><a class="headerlink" href="#reversed" title="Permalink to this headline">¶</a></h3>
<p>Reverse-order. Alternative to calling orderBy with SQLBuilder.DESC or ‘-‘.</p>
</div>
<div class="section" id="distinct">
<h3><a class="toc-backref" href="#id11"><code class="docutils literal notranslate"><span class="pre">distinct()</span></code></a><a class="headerlink" href="#distinct" title="Permalink to this headline">¶</a></h3>
<p>In SQL, SELECT DISTINCT, removing duplicate rows.</p>
</div>
<div class="section" id="filter-expression">
<h3><a class="toc-backref" href="#id12"><code class="docutils literal notranslate"><span class="pre">filter(expression)</span></code></a><a class="headerlink" href="#filter-expression" title="Permalink to this headline">¶</a></h3>
<p>Add additional expressions to restrict result set.
Takes either a string static SQL expression valid in a WHERE clause,
or a <a class="reference external" href="SQLBuilder.html">SQLBuilder expression</a>. ANDed with any previous expressions.</p>
</div>
</div>
<div class="section" id="aggregate-methods">
<h2><a class="toc-backref" href="#id13">Aggregate Methods</a><a class="headerlink" href="#aggregate-methods" title="Permalink to this headline">¶</a></h2>
<p>These return column values (strings, numbers, etc)
not new SQLResults instances, by making the appropriate
SQL query (the actual result rows are not retrieved).
Any that take a column can also take a SQLBuilder
column instance, e.g. <code class="docutils literal notranslate"><span class="pre">MyClass.q.size</span></code>.</p>
<div class="section" id="count">
<h3><a class="toc-backref" href="#id14"><code class="docutils literal notranslate"><span class="pre">count()</span></code></a><a class="headerlink" href="#count" title="Permalink to this headline">¶</a></h3>
<p>Returns the length of the result set, by a SQL <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">COUNT(...)</span></code>
query.</p>
</div>
<div class="section" id="sum-column">
<h3><a class="toc-backref" href="#id15"><code class="docutils literal notranslate"><span class="pre">sum(column)</span></code></a><a class="headerlink" href="#sum-column" title="Permalink to this headline">¶</a></h3>
<p>The sum of values for <code class="docutils literal notranslate"><span class="pre">column</span></code> in the result set.</p>
</div>
<div class="section" id="min-column">
<h3><a class="toc-backref" href="#id16"><code class="docutils literal notranslate"><span class="pre">min(column)</span></code></a><a class="headerlink" href="#min-column" title="Permalink to this headline">¶</a></h3>
<p>The minimum value for <code class="docutils literal notranslate"><span class="pre">column</span></code> in the result set.</p>
</div>
<div class="section" id="max-column">
<h3><a class="toc-backref" href="#id17"><code class="docutils literal notranslate"><span class="pre">max(column)</span></code></a><a class="headerlink" href="#max-column" title="Permalink to this headline">¶</a></h3>
<p>The maximum value for <code class="docutils literal notranslate"><span class="pre">column</span></code> in the result set.</p>
</div>
<div class="section" id="avg-column">
<h3><a class="toc-backref" href="#id18"><code class="docutils literal notranslate"><span class="pre">avg(column)</span></code></a><a class="headerlink" href="#avg-column" title="Permalink to this headline">¶</a></h3>
<p>The average value for the <code class="docutils literal notranslate"><span class="pre">column</span></code> in the result set.</p>
</div>
</div>
<div class="section" id="traversal-to-related-sqlobject-classes">
<h2><a class="toc-backref" href="#id19">Traversal to related SQLObject classes</a><a class="headerlink" href="#traversal-to-related-sqlobject-classes" title="Permalink to this headline">¶</a></h2>
<div class="section" id="throughto-join-name-and-throughto-foreign-key-name">
<h3><a class="toc-backref" href="#id20"><code class="docutils literal notranslate"><span class="pre">throughTo.join_name</span> <span class="pre">and</span> <span class="pre">throughTo.foreign_key_name</span></code></a><a class="headerlink" href="#throughto-join-name-and-throughto-foreign-key-name" title="Permalink to this headline">¶</a></h3>
<p>This accessor lets you retrieve the objects related to your
SelectResults by either a join or foreign key relationship,
in the same manner as the cloning methods above. For instance:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Schools</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">Schools</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">student_satisfaction</span><span class="o">></span><span class="mi">90</span><span class="p">)</span><span class="o">.</span><span class="n">throughTo</span><span class="o">.</span><span class="n">teachers</span>
</pre></div>
</div>
<p>returns a SelectResults of Teachers of Schools with satisfied students,
assuming Schools has a SQLMultipleJoin or SQLRelatedJoin attribute
named <code class="docutils literal notranslate"><span class="pre">teachers</span></code>. Similarily, with a self-joining foreign key named
<code class="docutils literal notranslate"><span class="pre">father</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Person</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">q</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s1">'Steve'</span><span class="p">)</span><span class="o">.</span><span class="n">throughTo</span><span class="o">.</span><span class="n">father</span><span class="o">.</span><span class="n">throughTo</span><span class="o">.</span><span class="n">father</span>
</pre></div>
</div>
<p>returns a SelectResults of Persons who are the paternal grandfather of
someone named <code class="docutils literal notranslate"><span class="pre">Steve</span></code>.</p>
<a class="reference external image-reference" href="https://sourceforge.net/projects/sqlobject"><img alt="Get SQLObject at SourceForge.net. Fast, secure and Free Open Source software downloads" class="noborder align-center" src="https://sourceforge.net/sflogo.php?group_id=74338&type=10" style="width: 80px; height: 15px;" /></a>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="related" role="navigation" aria-label="related navigation">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
>index</a></li>
<li class="right" >
<a href="py-modindex.html" title="Python Module Index"
>modules</a> |</li>
<li class="right" >
<a href="sqlobject-admin.html" title="The sqlobject-admin Tool"
>next</a> |</li>
<li class="right" >
<a href="SQLBuilder.html" title="SQLBuilder"
>previous</a> |</li>
<li class="nav-item nav-item-0"><a href="index.html">SQLObject 3.7.0 documentation</a> »</li>
</ul>
</div>
<div class="footer" role="contentinfo">
© Copyright 2004-2018, Ian Bicking and contributors.
Created using <a href="http://sphinx-doc.org/">Sphinx</a> 1.7.4.
</div>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-release
>
by-pkgid
>
f1af5ac4c8d0f3f6ef0bca84cf516f3e
>
files
>
651
