File: examples.html

package info (click to toggle)
sqlalchemy 0.9.8%2Bdfsg-0.1
links: PTS, VCS
area: main
in suites: jessie, jessie-kfreebsd
size: 23,952 kB
ctags: 24,534
sloc: python: 152,282; ansic: 1,346; makefile: 257; xml: 17
file content (680 lines) | stat: -rw-r--r-- 47,355 bytes
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        
        <title>
            
    
                ORM Examples
             &mdash;
    SQLAlchemy 0.9 Documentation

        </title>

        
            <!-- begin iterate through SQLA + sphinx environment css_files -->
                <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
                <link rel="stylesheet" href="../_static/docs.css" type="text/css" />
                <link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
                <link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
            <!-- end iterate through SQLA + sphinx environment css_files -->
        

        

    

    <!-- begin layout.mako headers -->

    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
          URL_ROOT:    '../',
          VERSION:     '0.9.8',
          COLLAPSE_MODINDEX: false,
          FILE_SUFFIX: '.html'
      };
    </script>

    <!-- begin iterate through sphinx environment script_files -->
        <script type="text/javascript" src="../_static/jquery.js"></script>
        <script type="text/javascript" src="../_static/underscore.js"></script>
        <script type="text/javascript" src="../_static/doctools.js"></script>
    <!-- end iterate through sphinx environment script_files -->

    <script type="text/javascript" src="../_static/detectmobile.js"></script>
    <script type="text/javascript" src="../_static/init.js"></script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
        <link rel="copyright" title="Copyright" href="../copyright.html" />
    <link rel="top" title="SQLAlchemy 0.9 Documentation" href="../index.html" />
        <link rel="up" title="SQLAlchemy ORM" href="index.html" />
        <link rel="next" title="ORM Exceptions" href="exceptions.html" />
        <link rel="prev" title="Alternate Class Instrumentation" href="extensions/instrumentation.html" />
    <!-- end layout.mako headers -->


    </head>
    <body>
        















<div id="docs-container">





<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
    <div id="docs-version-header">
        Release: <span class="version-num">0.9.8</span> | Release Date: October 13, 2014
    </div>

    <h1>SQLAlchemy 0.9 Documentation</h1>

</div>
</div>

<div id="docs-body-container">

    <div id="fixed-sidebar" class="withsidebar">


        <div id="docs-sidebar-popout">
            <h3><a href="../index.html">SQLAlchemy 0.9 Documentation</a></h3>

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

                    <a href="extensions/instrumentation.html" title="Alternate Class Instrumentation">Prev</a> |
                    <a href="exceptions.html" title="ORM Exceptions">Next</a>
            </p>

            <p id="sidebar-topnav">
                <a href="../index.html">Contents</a> |
                <a href="../genindex.html">Index</a>
            </p>

            <div id="sidebar-search">
                <form class="search" action="../search.html" method="get">
                  <input type="text" name="q" size="12" /> <input type="submit" value="Search" />
                  <input type="hidden" name="check_keywords" value="yes" />
                  <input type="hidden" name="area" value="default" />
                </form>
            </div>

        </div>

        <div id="docs-sidebar">

        <h3><a href="#">            
                ORM Examples
            
        </a></h3>
        <ul>
<li><a class="reference internal" href="#">ORM Examples</a><ul>
<li><a class="reference internal" href="#mapping-recipes">Mapping Recipes</a><ul>
<li><a class="reference internal" href="#module-examples.adjacency_list">Adjacency List</a></li>
<li><a class="reference internal" href="#module-examples.association">Associations</a></li>
<li><a class="reference internal" href="#module-examples.graphs">Directed Graphs</a></li>
<li><a class="reference internal" href="#module-examples.dynamic_dict">Dynamic Relations as Dictionaries</a></li>
<li><a class="reference internal" href="#module-examples.generic_associations">Generic Associations</a></li>
<li><a class="reference internal" href="#module-examples.large_collection">Large Collections</a></li>
<li><a class="reference internal" href="#module-examples.materialized_paths">Materialized Paths</a></li>
<li><a class="reference internal" href="#module-examples.nested_sets">Nested Sets</a></li>
<li><a class="reference internal" href="#module-examples.join_conditions">Relationship Join Conditions</a></li>
<li><a class="reference internal" href="#module-examples.elementtree">XML Persistence</a></li>
<li><a class="reference internal" href="#versioning-objects">Versioning Objects</a><ul>
<li><a class="reference internal" href="#module-examples.versioned_history">Versioning with a History Table</a></li>
<li><a class="reference internal" href="#module-examples.versioned_rows">Versioning using Temporal Rows</a></li>
</ul>
</li>
<li><a class="reference internal" href="#module-examples.vertical">Vertical Attribute Mapping</a></li>
</ul>
</li>
<li><a class="reference internal" href="#inheritance-mapping-recipes">Inheritance Mapping Recipes</a><ul>
<li><a class="reference internal" href="#module-examples.inheritance">Basic Inheritance Mappings</a></li>
</ul>
</li>
<li><a class="reference internal" href="#special-apis">Special APIs</a><ul>
<li><a class="reference internal" href="#module-examples.custom_attributes">Attribute Instrumentation</a></li>
<li><a class="reference internal" href="#module-examples.sharding">Horizontal Sharding</a></li>
</ul>
</li>
<li><a class="reference internal" href="#extending-the-orm">Extending the ORM</a><ul>
<li><a class="reference internal" href="#module-examples.dogpile_caching">Dogpile Caching</a></li>
<li><a class="reference internal" href="#module-examples.postgis">PostGIS Integration</a></li>
</ul>
</li>
</ul>
</li>
</ul>




        </div>

    </div>

    

    <div id="docs-body" class="withsidebar" >
        
<div class="section" id="orm-examples">
<span id="examples-toplevel"></span><h1>ORM Examples<a class="headerlink" href="#orm-examples" title="Permalink to this headline">¶</a></h1>
<p>The SQLAlchemy distribution includes a variety of code examples illustrating
a select set of patterns, some typical and some not so typical.   All are
runnable and can be found in the <tt class="docutils literal"><span class="pre">/examples</span></tt> directory of the
distribution.   Descriptions and source code for all can be found here.</p>
<p>Additional SQLAlchemy examples, some user contributed, are available on the
wiki at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes">http://www.sqlalchemy.org/trac/wiki/UsageRecipes</a>.</p>
<div class="section" id="mapping-recipes">
<h2>Mapping Recipes<a class="headerlink" href="#mapping-recipes" title="Permalink to this headline">¶</a></h2>
<div class="section" id="module-examples.adjacency_list">
<span id="adjacency-list"></span><span id="examples-adjacencylist"></span><h3>Adjacency List<a class="headerlink" href="#module-examples.adjacency_list" title="Permalink to this headline">¶</a></h3>
<p>An example of a dictionary-of-dictionaries structure mapped using
an adjacency list model.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">node</span> <span class="o">=</span> <span class="n">TreeNode</span><span class="p">(</span><span class="s">&#39;rootnode&#39;</span><span class="p">)</span>
<span class="n">node</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="s">&#39;node1&#39;</span><span class="p">)</span>
<span class="n">node</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="s">&#39;node3&#39;</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">node</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>

<span class="n">dump_tree</span><span class="p">(</span><span class="n">node</span><span class="p">)</span></pre></div>
</div>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/adjacency_list/adjacency_list.html">adjacency_list.py</a></li>
</ul>
</p>
</div>
<div class="section" id="module-examples.association">
<span id="associations"></span><span id="examples-associations"></span><h3>Associations<a class="headerlink" href="#module-examples.association" title="Permalink to this headline">¶</a></h3>
<p>Examples illustrating the usage of the &#8220;association object&#8221; pattern,
where an intermediary class mediates the relationship between two
classes that are associated in a many-to-many pattern.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/association/basic_association.html">basic_association.py</a> - illustrate a many-to-many relationship between an
&#8220;Order&#8221; and a collection of &#8220;Item&#8221; objects, associating a purchase price
with each via an association object called &#8220;OrderItem&#8221;</li>
<li><a class="reference external" href="../_modules/examples/association/dict_of_sets_with_default.html">dict_of_sets_with_default.py</a> - an advanced association proxy example which
illustrates nesting of association proxies to produce multi-level Python
collections, in this case a dictionary with string keys and sets of integers
as values, which conceal the underlying mapped classes.</li>
<li><a class="reference external" href="../_modules/examples/association/proxied_association.html">proxied_association.py</a> - same example as basic_association, adding in
usage of <a class="reference internal" href="extensions/associationproxy.html#module-sqlalchemy.ext.associationproxy" title="sqlalchemy.ext.associationproxy"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.associationproxy</span></tt></a> to make explicit references
to <tt class="docutils literal"><span class="pre">OrderItem</span></tt> optional.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.graphs">
<span id="directed-graphs"></span><h3>Directed Graphs<a class="headerlink" href="#module-examples.graphs" title="Permalink to this headline">¶</a></h3>
<p>An example of persistence for a directed graph structure.   The
graph is stored as a collection of edges, each referencing both a
&#8220;lower&#8221; and an &#8220;upper&#8221; node in a table of nodes.  Basic persistence
and querying for lower- and upper- neighbors are illustrated:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">n2</span> <span class="o">=</span> <span class="n">Node</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="n">n5</span> <span class="o">=</span> <span class="n">Node</span><span class="p">(</span><span class="mi">5</span><span class="p">)</span>
<span class="n">n2</span><span class="o">.</span><span class="n">add_neighbor</span><span class="p">(</span><span class="n">n5</span><span class="p">)</span>
<span class="k">print</span> <span class="n">n2</span><span class="o">.</span><span class="n">higher_neighbors</span><span class="p">()</span></pre></div>
</div>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/graphs/directed_graph.html">directed_graph.py</a> - a directed graph example.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.dynamic_dict">
<span id="dynamic-relations-as-dictionaries"></span><h3>Dynamic Relations as Dictionaries<a class="headerlink" href="#module-examples.dynamic_dict" title="Permalink to this headline">¶</a></h3>
<p>Illustrates how to place a dictionary-like facade on top of a
&#8220;dynamic&#8221; relation, so that dictionary operations (assuming simple
string keys) can operate upon a large collection without loading the
full collection at once.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/dynamic_dict/dynamic_dict.html">dynamic_dict.py</a></li>
</ul>
</p>
</div>
<div class="section" id="module-examples.generic_associations">
<span id="generic-associations"></span><span id="examples-generic-associations"></span><h3>Generic Associations<a class="headerlink" href="#module-examples.generic_associations" title="Permalink to this headline">¶</a></h3>
<p>Illustrates various methods of associating multiple types of
parents with a particular child object.</p>
<p>The examples all use the declarative extension along with
declarative mixins.   Each one presents the identical use
case at the end - two classes, <tt class="docutils literal"><span class="pre">Customer</span></tt> and <tt class="docutils literal"><span class="pre">Supplier</span></tt>, both
subclassing the <tt class="docutils literal"><span class="pre">HasAddresses</span></tt> mixin, which ensures that the
parent class is provided with an <tt class="docutils literal"><span class="pre">addresses</span></tt> collection
which contains <tt class="docutils literal"><span class="pre">Address</span></tt> objects.</p>
<p>The <a class="reference external" href="../_modules/examples/generic_associations/discriminator_on_association.html">discriminator_on_association.py</a> and <a class="reference external" href="../_modules/examples/generic_associations/generic_fk.html">generic_fk.py</a> scripts
are modernized versions of recipes presented in the 2007 blog post
<a class="reference external" href="http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/">Polymorphic Associations with SQLAlchemy</a>.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/generic_associations/discriminator_on_association.html">discriminator_on_association.py</a> - Illustrates a mixin which provides a generic association
using a single target table and a single association table,
referred to by all parent tables.  The association table
contains a &#8220;discriminator&#8221; column which determines what type of
parent object associates to each particular row in the association
table.</li>
<li><a class="reference external" href="../_modules/examples/generic_associations/generic_fk.html">generic_fk.py</a> - Illustrates a so-called &#8220;generic foreign key&#8221;, in a similar fashion
to that of popular frameworks such as Django, ROR, etc.  This
approach bypasses standard referential integrity
practices, in that the &#8220;foreign key&#8221; column is not actually
constrained to refer to any particular table; instead,
in-application logic is used to determine which table is referenced.</li>
<li><a class="reference external" href="../_modules/examples/generic_associations/table_per_association.html">table_per_association.py</a> - Illustrates a mixin which provides a generic association
via a individually generated association tables for each parent class.
The associated objects themselves are persisted in a single table
shared among all parents.</li>
<li><a class="reference external" href="../_modules/examples/generic_associations/table_per_related.html">table_per_related.py</a> - Illustrates a generic association which persists association
objects within individual tables, each one generated to persist
those objects on behalf of a particular parent class.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.large_collection">
<span id="large-collections"></span><h3>Large Collections<a class="headerlink" href="#module-examples.large_collection" title="Permalink to this headline">¶</a></h3>
<p>Large collection example.</p>
<p>Illustrates the options to use with
<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> when the list of related
objects is very large, including:</p>
<ul class="simple">
<li>&#8220;dynamic&#8221; relationships which query slices of data as accessed</li>
<li>how to use ON DELETE CASCADE in conjunction with
<tt class="docutils literal"><span class="pre">passive_deletes=True</span></tt> to greatly improve the performance of
related collection deletion.</li>
</ul>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/large_collection/large_collection.html">large_collection.py</a></li>
</ul>
</p>
</div>
<div class="section" id="module-examples.materialized_paths">
<span id="materialized-paths"></span><h3>Materialized Paths<a class="headerlink" href="#module-examples.materialized_paths" title="Permalink to this headline">¶</a></h3>
<p>Illustrates the &#8220;materialized paths&#8221; pattern for hierarchical data using the
SQLAlchemy ORM.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/materialized_paths/materialized_paths.html">materialized_paths.py</a> - Illustrates the &#8220;materialized paths&#8221; pattern.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.nested_sets">
<span id="nested-sets"></span><h3>Nested Sets<a class="headerlink" href="#module-examples.nested_sets" title="Permalink to this headline">¶</a></h3>
<p>Illustrates a rudimentary way to implement the &#8220;nested sets&#8221;
pattern for hierarchical data using the SQLAlchemy ORM.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/nested_sets/nested_sets.html">nested_sets.py</a> - Celko&#8217;s &#8220;Nested Sets&#8221; Tree Structure.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.join_conditions">
<span id="relationship-join-conditions"></span><span id="examples-relationships"></span><h3>Relationship Join Conditions<a class="headerlink" href="#module-examples.join_conditions" title="Permalink to this headline">¶</a></h3>
<p>Examples of various <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">orm.relationship()</span></tt></a> configurations,
which make use of the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> argument to compose special types
of join conditions.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/join_conditions/cast.html">cast.py</a> - Illustrate a <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> that joins two columns where those
columns are not of the same type, and a CAST must be used on the SQL
side in order to match them.</li>
<li><a class="reference external" href="../_modules/examples/join_conditions/threeway.html">threeway.py</a> - Illustrate a &#8220;three way join&#8221; - where a primary table joins to a remote
table via an association table, but then the primary table also needs
to refer to some columns in the remote table directly.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.elementtree">
<span id="xml-persistence"></span><span id="examples-xmlpersistence"></span><h3>XML Persistence<a class="headerlink" href="#module-examples.elementtree" title="Permalink to this headline">¶</a></h3>
<p>Illustrates three strategies for persisting and querying XML
documents as represented by ElementTree in a relational
database. The techniques do not apply any mappings to the
ElementTree objects directly, so are compatible with the
native cElementTree as well as lxml, and can be adapted to
suit any kind of DOM representation system. Querying along
xpath-like strings is illustrated as well.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># parse an XML file and persist in the database</span>
<span class="n">doc</span> <span class="o">=</span> <span class="n">ElementTree</span><span class="o">.</span><span class="n">parse</span><span class="p">(</span><span class="s">&quot;test.xml&quot;</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">Document</span><span class="p">(</span><span class="nb">file</span><span class="p">,</span> <span class="n">doc</span><span class="p">))</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>

<span class="c"># locate documents with a certain path/attribute structure</span>
<span class="k">for</span> <span class="n">document</span> <span class="ow">in</span> <span class="n">find_document</span><span class="p">(</span><span class="s">&#39;/somefile/header/field2[@attr=foo]&#39;</span><span class="p">):</span>
    <span class="c"># dump the XML</span>
    <span class="k">print</span> <span class="n">document</span></pre></div>
</div>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/elementtree/pickle.html">pickle.py</a> - illustrates a quick and dirty way to persist an XML document expressed using ElementTree and pickle.</li>
<li><a class="reference external" href="../_modules/examples/elementtree/adjacency_list.html">adjacency_list.py</a> - Illustrates an explicit way to persist an XML document expressed using ElementTree.</li>
<li><a class="reference external" href="../_modules/examples/elementtree/optimized_al.html">optimized_al.py</a> - Uses the same strategy as
  <tt class="docutils literal"><span class="pre">adjacency_list.py</span></tt>, but associates each   DOM row with its owning
  document row, so that a full document of DOM nodes can be loaded
  using O(1) queries - the construction of the &#8220;hierarchy&#8221; is performed
  after the load in a non-recursive fashion and is more
  efficient.</li>
</ul>
</p>
</div>
<div class="section" id="versioning-objects">
<h3>Versioning Objects<a class="headerlink" href="#versioning-objects" title="Permalink to this headline">¶</a></h3>
<div class="section" id="module-examples.versioned_history">
<span id="versioning-with-a-history-table"></span><h4>Versioning with a History Table<a class="headerlink" href="#module-examples.versioned_history" title="Permalink to this headline">¶</a></h4>
<p>Illustrates an extension which creates version tables for entities and stores
records for each change. The given extensions generate an anonymous &#8220;history&#8221; class which
represents historical versions of the target object.</p>
<p>Usage is illustrated via a unit test module <tt class="docutils literal"><span class="pre">test_versioning.py</span></tt>, which can
be run via nose:</p>
<div class="highlight-python"><pre>cd examples/versioning
nosetests -v</pre>
</div>
<p>A fragment of example usage, using declarative:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">history_meta</span> <span class="kn">import</span> <span class="n">Versioned</span><span class="p">,</span> <span class="n">versioned_session</span>

<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>

<span class="k">class</span> <span class="nc">SomeClass</span><span class="p">(</span><span class="n">Versioned</span><span class="p">,</span> <span class="n">Base</span><span class="p">):</span>
    <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;sometable&#39;</span>

    <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
    <span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>

    <span class="k">def</span> <span class="nf">__eq__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
        <span class="k">assert</span> <span class="nb">type</span><span class="p">(</span><span class="n">other</span><span class="p">)</span> <span class="ow">is</span> <span class="n">SomeClass</span> <span class="ow">and</span> <span class="n">other</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="bp">self</span><span class="o">.</span><span class="n">id</span>

<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span>
<span class="n">versioned_session</span><span class="p">(</span><span class="n">Session</span><span class="p">)</span>

<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">sc</span> <span class="o">=</span> <span class="n">SomeClass</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">&#39;sc1&#39;</span><span class="p">)</span>
<span class="n">sess</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">sc</span><span class="p">)</span>
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>

<span class="n">sc</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="s">&#39;sc1modified&#39;</span>
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>

<span class="k">assert</span> <span class="n">sc</span><span class="o">.</span><span class="n">version</span> <span class="o">==</span> <span class="mi">2</span>

<span class="n">SomeClassHistory</span> <span class="o">=</span> <span class="n">SomeClass</span><span class="o">.</span><span class="n">__history_mapper__</span><span class="o">.</span><span class="n">class_</span>

<span class="k">assert</span> <span class="n">sess</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">SomeClassHistory</span><span class="p">)</span><span class="o">.</span>\
            <span class="nb">filter</span><span class="p">(</span><span class="n">SomeClassHistory</span><span class="o">.</span><span class="n">version</span> <span class="o">==</span> <span class="mi">1</span><span class="p">)</span><span class="o">.</span>\
            <span class="nb">all</span><span class="p">()</span> \
            <span class="o">==</span> <span class="p">[</span><span class="n">SomeClassHistory</span><span class="p">(</span><span class="n">version</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">&#39;sc1&#39;</span><span class="p">)]</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">Versioned</span></tt> mixin is designed to work with declarative.  To use
the extension with classical mappers, the <tt class="docutils literal"><span class="pre">_history_mapper</span></tt> function
can be applied:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">history_meta</span> <span class="kn">import</span> <span class="n">_history_mapper</span>

<span class="n">m</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">SomeClass</span><span class="p">,</span> <span class="n">sometable</span><span class="p">)</span>
<span class="n">_history_mapper</span><span class="p">(</span><span class="n">m</span><span class="p">)</span>

<span class="n">SomeHistoryClass</span> <span class="o">=</span> <span class="n">SomeClass</span><span class="o">.</span><span class="n">__history_mapper__</span><span class="o">.</span><span class="n">class_</span></pre></div>
</div>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/versioned_history/history_meta.html">history_meta.py</a> - Versioned mixin class and other utilities.</li>
<li><a class="reference external" href="../_modules/examples/versioned_history/test_versioning.html">test_versioning.py</a> - Unit tests illustrating usage of the <tt class="docutils literal"><span class="pre">history_meta.py</span></tt> module functions.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.versioned_rows">
<span id="versioning-using-temporal-rows"></span><h4>Versioning using Temporal Rows<a class="headerlink" href="#module-examples.versioned_rows" title="Permalink to this headline">¶</a></h4>
<p>Illustrates an extension which versions data by storing new rows for each change;
that is, what would normally be an UPDATE becomes an INSERT.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/versioned_rows/versioned_map.html">versioned_map.py</a> - A variant of the versioned_rows example. Here
we store a dictionary of key/value pairs, storing the k/v&#8217;s in a
&#8220;vertical&#8221; fashion where each key gets a row. The value is split out
into two separate datatypes, string and int - the range of datatype
storage can be adjusted for individual needs.</li>
<li><a class="reference external" href="../_modules/examples/versioned_rows/versioned_rows.html">versioned_rows.py</a> - Illustrates a method to intercept changes on objects, turning
an UPDATE statement on a single row into an INSERT statement, so that a new
row is inserted with the new data, keeping the old row intact.</li>
</ul>
</p>
</div>
</div>
<div class="section" id="module-examples.vertical">
<span id="vertical-attribute-mapping"></span><h3>Vertical Attribute Mapping<a class="headerlink" href="#module-examples.vertical" title="Permalink to this headline">¶</a></h3>
<p>Illustrates &#8220;vertical table&#8221; mappings.</p>
<p>A &#8220;vertical table&#8221; refers to a technique where individual attributes
of an object are stored as distinct rows in a table. The &#8220;vertical
table&#8221; technique is used to persist objects which can have a varied
set of attributes, at the expense of simple query control and brevity.
It is commonly found in content/document management systems in order
to represent user-created structures flexibly.</p>
<p>Two variants on the approach are given.  In the second, each row
references a &#8220;datatype&#8221; which contains information about the type of
information stored in the attribute, such as integer, string, or date.</p>
<p>Example:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">shrew</span> <span class="o">=</span> <span class="n">Animal</span><span class="p">(</span><span class="s">u&#39;shrew&#39;</span><span class="p">)</span>
<span class="n">shrew</span><span class="p">[</span><span class="s">u&#39;cuteness&#39;</span><span class="p">]</span> <span class="o">=</span> <span class="mi">5</span>
<span class="n">shrew</span><span class="p">[</span><span class="s">u&#39;weasel-like&#39;</span><span class="p">]</span> <span class="o">=</span> <span class="bp">False</span>
<span class="n">shrew</span><span class="p">[</span><span class="s">u&#39;poisonous&#39;</span><span class="p">]</span> <span class="o">=</span> <span class="bp">True</span>

<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">shrew</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>

<span class="n">q</span> <span class="o">=</span> <span class="p">(</span><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Animal</span><span class="p">)</span><span class="o">.</span>
     <span class="nb">filter</span><span class="p">(</span><span class="n">Animal</span><span class="o">.</span><span class="n">facts</span><span class="o">.</span><span class="n">any</span><span class="p">(</span>
       <span class="n">and_</span><span class="p">(</span><span class="n">AnimalFact</span><span class="o">.</span><span class="n">key</span> <span class="o">==</span> <span class="s">u&#39;weasel-like&#39;</span><span class="p">,</span>
            <span class="n">AnimalFact</span><span class="o">.</span><span class="n">value</span> <span class="o">==</span> <span class="bp">True</span><span class="p">))))</span>
<span class="k">print</span> <span class="s">&#39;weasel-like animals&#39;</span><span class="p">,</span> <span class="n">q</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/vertical/dictlike-polymorphic.html">dictlike-polymorphic.py</a> - Mapping a polymorphic-valued vertical table as a dictionary.</li>
<li><a class="reference external" href="../_modules/examples/vertical/dictlike.html">dictlike.py</a> - Mapping a vertical table as a dictionary.</li>
</ul>
</p>
</div>
</div>
<div class="section" id="inheritance-mapping-recipes">
<h2>Inheritance Mapping Recipes<a class="headerlink" href="#inheritance-mapping-recipes" title="Permalink to this headline">¶</a></h2>
<div class="section" id="module-examples.inheritance">
<span id="basic-inheritance-mappings"></span><h3>Basic Inheritance Mappings<a class="headerlink" href="#module-examples.inheritance" title="Permalink to this headline">¶</a></h3>
<p>Working examples of single-table, joined-table, and concrete-table
inheritance as described in <em class="xref std std-ref">datamapping_inheritance</em>.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/inheritance/concrete.html">concrete.py</a> - Concrete (table-per-class) inheritance example.</li>
<li><a class="reference external" href="../_modules/examples/inheritance/joined.html">joined.py</a> - Joined-table (table-per-subclass) inheritance example.</li>
<li><a class="reference external" href="../_modules/examples/inheritance/single.html">single.py</a> - Single-table inheritance example.</li>
</ul>
</p>
</div>
</div>
<div class="section" id="special-apis">
<h2>Special APIs<a class="headerlink" href="#special-apis" title="Permalink to this headline">¶</a></h2>
<div class="section" id="module-examples.custom_attributes">
<span id="attribute-instrumentation"></span><span id="examples-instrumentation"></span><h3>Attribute Instrumentation<a class="headerlink" href="#module-examples.custom_attributes" title="Permalink to this headline">¶</a></h3>
<p>Two examples illustrating modifications to SQLAlchemy&#8217;s attribute management
system.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/custom_attributes/custom_management.html">custom_management.py</a> - Illustrates customized class instrumentation, using
the <a class="reference internal" href="extensions/instrumentation.html#module-sqlalchemy.ext.instrumentation" title="sqlalchemy.ext.instrumentation"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.ext.instrumentation</span></tt></a> extension package.</li>
<li><a class="reference external" href="../_modules/examples/custom_attributes/listen_for_events.html">listen_for_events.py</a> - Illustrates how to attach events to all instrumented attributes
and listen for change events.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.sharding">
<span id="horizontal-sharding"></span><span id="examples-sharding"></span><h3>Horizontal Sharding<a class="headerlink" href="#module-examples.sharding" title="Permalink to this headline">¶</a></h3>
<p>A basic example of using the SQLAlchemy Sharding API.
Sharding refers to horizontally scaling data across multiple
databases.</p>
<p>The basic components of a &#8220;sharded&#8221; mapping are:</p>
<ul class="simple">
<li>multiple databases, each assigned a &#8216;shard id&#8217;</li>
<li>a function which can return a single shard id, given an instance
to be saved; this is called &#8220;shard_chooser&#8221;</li>
<li>a function which can return a list of shard ids which apply to a particular
instance identifier; this is called &#8220;id_chooser&#8221;.  If it returns all shard ids,
all shards will be searched.</li>
<li>a function which can return a list of shard ids to try, given a particular
Query (&#8220;query_chooser&#8221;).  If it returns all shard ids, all shards will be
queried and the results joined together.</li>
</ul>
<p>In this example, four sqlite databases will store information about weather
data on a database-per-continent basis. We provide example shard_chooser,
id_chooser and query_chooser functions. The query_chooser illustrates
inspection of the SQL expression element in order to attempt to determine a
single shard being requested.</p>
<p>The construction of generic sharding routines is an ambitious approach
to the issue of organizing instances among multiple databases.   For a
more plain-spoken alternative, the &#8220;distinct entity&#8221; approach
is a simple method of assigning objects to different tables (and potentially
database nodes) in an explicit way - described on the wiki at
<a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName">EntityName</a>.</p>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/sharding/attribute_shard.html">attribute_shard.py</a></li>
</ul>
</p>
</div>
</div>
<div class="section" id="extending-the-orm">
<h2>Extending the ORM<a class="headerlink" href="#extending-the-orm" title="Permalink to this headline">¶</a></h2>
<div class="section" id="module-examples.dogpile_caching">
<span id="dogpile-caching"></span><span id="examples-caching"></span><h3>Dogpile Caching<a class="headerlink" href="#module-examples.dogpile_caching" title="Permalink to this headline">¶</a></h3>
<p>Illustrates how to embed <a class="reference external" href="http://dogpilecache.readthedocs.org/">dogpile.cache</a>
functionality within
the <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object, allowing full cache control as well as the
ability to pull &#8220;lazy loaded&#8221; attributes from long term cache
as well.</p>
<div class="versionchanged">
<p><span>Changed in version 0.8: </span>The example was modernized to use
dogpile.cache, replacing Beaker as the caching library in
use.</p>
</div>
<p>In this demo, the following techniques are illustrated:</p>
<ul class="simple">
<li>Using custom subclasses of <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a></li>
<li>Basic technique of circumventing Query to pull from a
custom cache source instead of the database.</li>
<li>Rudimental caching with dogpile.cache, using &#8220;regions&#8221; which allow
global control over a fixed set of configurations.</li>
<li>Using custom <tt class="xref py py-class docutils literal"><span class="pre">MapperOption</span></tt> objects to configure options on
a Query, including the ability to invoke the options
deep within an object graph when lazy loads occur.</li>
</ul>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># query for Person objects, specifying cache</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">Session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Person</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">FromCache</span><span class="p">(</span><span class="s">&quot;default&quot;</span><span class="p">))</span>

<span class="c"># specify that each Person&#39;s &quot;addresses&quot; collection comes from</span>
<span class="c"># cache too</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">q</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">RelationshipCache</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">addresses</span><span class="p">,</span> <span class="s">&quot;default&quot;</span><span class="p">))</span>

<span class="c"># query</span>
<span class="k">print</span> <span class="n">q</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>To run, both SQLAlchemy and dogpile.cache must be
installed or on the current PYTHONPATH. The demo will create a local
directory for datafiles, insert initial data, and run. Running the
demo a second time will utilize the cache files already present, and
exactly one SQL statement against two tables will be emitted - the
displayed result however will utilize dozens of lazyloads that all
pull from cache.</p>
<p>The demo scripts themselves, in order of complexity, are run as Python
modules so that relative imports work:</p>
<div class="highlight-python"><pre>python -m examples.dogpile_caching.helloworld

python -m examples.dogpile_caching.relationship_caching

python -m examples.dogpile_caching.advanced

python -m examples.dogpile_caching.local_session_caching</pre>
</div>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/dogpile_caching/environment.html">environment.py</a> - Establish data / cache file paths, and configurations,
bootstrap fixture data if necessary.</li>
<li><a class="reference external" href="../_modules/examples/dogpile_caching/caching_query.html">caching_query.py</a> - Represent functions and classes
which allow the usage of Dogpile caching with SQLAlchemy.
Introduces a query option called FromCache.</li>
<li><a class="reference external" href="../_modules/examples/dogpile_caching/model.html">model.py</a> - The datamodel, which represents Person that has multiple
Address objects, each with PostalCode, City, Country.</li>
<li><a class="reference external" href="../_modules/examples/dogpile_caching/fixture_data.html">fixture_data.py</a> - Installs some sample data.   Here we have a handful of postal codes for a few US/
Canadian cities.   Then, 100 Person records are installed, each with a
randomly selected postal code.</li>
<li><a class="reference external" href="../_modules/examples/dogpile_caching/helloworld.html">helloworld.py</a> - Illustrate how to load some data, and cache the results.</li>
<li><a class="reference external" href="../_modules/examples/dogpile_caching/relationship_caching.html">relationship_caching.py</a> - Illustrates how to add cache options on
relationship endpoints, so that lazyloads load from cache.</li>
<li><a class="reference external" href="../_modules/examples/dogpile_caching/advanced.html">advanced.py</a> - Illustrate usage of Query combined with the FromCache option,
including front-end loading, cache invalidation and collection caching.</li>
<li><a class="reference external" href="../_modules/examples/dogpile_caching/local_session_caching.html">local_session_caching.py</a> - Grok everything so far ?   This example
creates a new dogpile.cache backend that will persist data in a dictionary
which is local to the current session.   remove() the session
and the cache is gone.</li>
</ul>
</p>
</div>
<div class="section" id="module-examples.postgis">
<span id="postgis-integration"></span><span id="examples-postgis"></span><h3>PostGIS Integration<a class="headerlink" href="#module-examples.postgis" title="Permalink to this headline">¶</a></h3>
<p>A naive example illustrating techniques to help
embed PostGIS functionality.</p>
<p>This example was originally developed in the hopes that it would be
extrapolated into a comprehensive PostGIS integration layer.  We are
pleased to announce that this has come to fruition as <a class="reference external" href="http://www.geoalchemy.org/">GeoAlchemy</a>.</p>
<p>The example illustrates:</p>
<ul class="simple">
<li>a DDL extension which allows CREATE/DROP to work in
conjunction with AddGeometryColumn/DropGeometryColumn</li>
<li>a Geometry type, as well as a few subtypes, which
convert result row values to a GIS-aware object,
and also integrates with the DDL extension.</li>
<li>a GIS-aware object which stores a raw geometry value
and provides a factory for functions such as AsText().</li>
<li>an ORM comparator which can override standard column
methods on mapped objects to produce GIS operators.</li>
<li>an attribute event listener that intercepts strings
and converts to GeomFromText().</li>
<li>a standalone operator example.</li>
</ul>
<p>The implementation is limited to only public, well known
and simple to use extension points.</p>
<p>E.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">print</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Road</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Road</span><span class="o">.</span><span class="n">road_geom</span><span class="o">.</span><span class="n">intersects</span><span class="p">(</span><span class="n">r1</span><span class="o">.</span><span class="n">road_geom</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>Listing of files:<ul class="simple">
<li><a class="reference external" href="../_modules/examples/postgis/postgis.html">postgis.py</a></li>
</ul>
</p>
</div>
</div>
</div>

    </div>

</div>

<div id="docs-bottom-navigation" class="docs-navigation-links">
        Previous:
        <a href="extensions/instrumentation.html" title="previous chapter">Alternate Class Instrumentation</a>
        Next:
        <a href="exceptions.html" title="next chapter">ORM Exceptions</a>

    <div id="docs-copyright">
        &copy; <a href="../copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
        Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
    </div>
</div>

</div>

        
    </body>
</html>