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

    SQLAlchemy 0.7 Documentation

          VERSION:     '0.7.9',

    <link rel="top" title="SQLAlchemy 0.7 Documentation" href="../index.html" />

        <link rel="next" title="&lt;no title&gt;" href="interfaces.html" />

        <link rel="prev" title="SqlSoup" href="extensions/sqlsoup.html" />

    <h1>SQLAlchemy 0.7 Documentation</h1>

        Release: <span class="version-num">0.7.9</span> | Release Date: October 1, 2012

            <a href="extensions/sqlsoup.html" title="previous chapter">SqlSoup</a>

            <a href="interfaces.html" title="next chapter">&lt;no title&gt;</a>

        <a href="../index.html">SQLAlchemy 0.7 Documentation</a>

<li><a class="reference internal" href="#beaker-caching">Beaker Caching</a></li>

<li><a class="reference internal" href="#declarative-reflection">Declarative Reflection</a></li>

    <a href="extensions/sqlsoup.html" title="previous chapter">SqlSoup</a>

    <a href="interfaces.html" title="next chapter">&lt;no title&gt;</a>

<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.   Each example contains a README in its <tt class="docutils literal"><span class="pre">__init__.py</span></tt> file, each of which are listed below.</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>

<span class="target" id="module-custom_attributes"></span><p>Two examples illustrating modifications to SQLAlchemy&#8217;s attribute management system.</p>

<p><tt class="docutils literal"><span class="pre">listen_for_events.py</span></tt> illustrates the usage of <a class="reference internal" href="deprecated.html#sqlalchemy.orm.interfaces.AttributeExtension" title="sqlalchemy.orm.interfaces.AttributeExtension"><tt class="xref py py-class docutils literal"><span class="pre">AttributeExtension</span></tt></a> to intercept attribute events.  It additionally illustrates a way to automatically attach these listeners to all class attributes using a <a class="reference internal" href="events.html#sqlalchemy.orm.interfaces.InstrumentationManager" title="sqlalchemy.orm.interfaces.InstrumentationManager"><tt class="xref py py-class docutils literal"><span class="pre">InstrumentationManager</span></tt></a>.</p>

<p><tt class="docutils literal"><span class="pre">custom_management.py</span></tt> illustrates much deeper usage of <a class="reference internal" href="events.html#sqlalchemy.orm.interfaces.InstrumentationManager" title="sqlalchemy.orm.interfaces.InstrumentationManager"><tt class="xref py py-class docutils literal"><span class="pre">InstrumentationManager</span></tt></a> as well as collection adaptation, to completely change the underlying method used to store state on an object.   This example was developed to illustrate techniques which would be used by other third party object instrumentation systems to interact with SQLAlchemy&#8217;s event system and is only intended for very intricate framework integrations.</p>

<div class="section" id="beaker-caching">

<span id="examples-caching"></span><h2>Beaker Caching<a class="headerlink" href="#beaker-caching" title="Permalink to this headline">¶</a></h2>

<p>Location: /examples/beaker_caching/</p>

<span class="target" id="module-beaker_caching"></span><p>Illustrates how to embed Beaker cache functionality within

the Query object, allowing full cache control as well as the

<li>Using custom subclasses of Query</li>

<li>Rudimental caching with Beaker, using &#8220;regions&#8221; which allow

<li>Using custom MapperOption objects to configure options on

<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="s">&quot;all_people&quot;</span><span class="p">))</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="s">&quot;default&quot;</span><span class="p">,</span> <span class="s">&quot;by_person&quot;</span><span class="p">,</span> <span class="n">Person</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span>

<p>To run, both SQLAlchemy and Beaker (1.4 or greater) must be

<div class="highlight-python"><pre>python examples/beaker_caching/helloworld.py

 

python examples/beaker_caching/relationship_caching.py

 

python examples/beaker_caching/advanced.py

 

python examples/beaker_caching/local_session_caching.py</pre>

<div><p>environment.py - Establish the Session, the Beaker cache

manager, data / cache file paths, and configurations,

which allow the usage of Beaker caching with SQLAlchemy.

creates a new Beaker container that will persist data in a dictionary

<div class="section" id="declarative-reflection">

<span id="examples-declarative-reflection"></span><h2>Declarative Reflection<a class="headerlink" href="#declarative-reflection" title="Permalink to this headline">¶</a></h2>

<p>Location: /examples/declarative_reflection</p>

<span class="target" id="module-declarative_reflection"></span><p>Illustrates how to mix table reflection with Declarative, such that

the reflection process itself can take place <strong>after</strong> all classes

are defined.  Declarative classes can also override column

definitions loaded from the database.</p>

<p>At the core of this example is the ability to change how Declarative

assigns mappings to classes.   The <tt class="docutils literal"><span class="pre">__mapper_cls__</span></tt> special attribute

is overridden to provide a function that gathers mapping requirements

as they are established, without actually creating the mapping.

Then, a second class-level method <tt class="docutils literal"><span class="pre">prepare()</span></tt> is used to iterate

through all mapping configurations collected, reflect the tables

named within and generate the actual mappers.</p>

<p class="versionadded">

<span class="versionmodified">New in version 0.7.5: </span>This new example makes usage of the new

<tt class="docutils literal"><span class="pre">autoload_replace</span></tt> flag on <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> to allow declared

classes to override reflected columns.</p>

<p>Usage example:</p>

<div class="highlight-python"><div class="highlight"><pre><span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">(</span><span class="n">cls</span><span class="o">=</span><span class="n">DeclarativeReflectedBase</span><span class="p">)</span>

 

<span class="k">class</span> <span class="nc">Foo</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;foo&#39;</span>

    <span class="n">bars</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Bar&quot;</span><span class="p">)</span>

 

<span class="k">class</span> <span class="nc">Bar</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;bar&#39;</span>

 

    <span class="c"># illustrate overriding of &quot;bar.foo_id&quot; to have</span>

    <span class="c"># a foreign key constraint otherwise not</span>

    <span class="c"># reflected, such as when using MySQL</span>

    <span class="n">foo_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;foo.id&#39;</span><span class="p">))</span>

 

<span class="n">Base</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="n">e</span><span class="p">)</span>

 

<span class="n">s</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">e</span><span class="p">)</span>

 

<span class="n">s</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span>

    <span class="n">Foo</span><span class="p">(</span><span class="n">bars</span><span class="o">=</span><span class="p">[</span><span class="n">Bar</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s">&#39;b1&#39;</span><span class="p">),</span> <span class="n">Bar</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s">&#39;b2&#39;</span><span class="p">)],</span> <span class="n">data</span><span class="o">=</span><span class="s">&#39;f1&#39;</span><span class="p">),</span>

    <span class="n">Foo</span><span class="p">(</span><span class="n">bars</span><span class="o">=</span><span class="p">[</span><span class="n">Bar</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s">&#39;b3&#39;</span><span class="p">),</span> <span class="n">Bar</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s">&#39;b4&#39;</span><span class="p">)],</span> <span class="n">data</span><span class="o">=</span><span class="s">&#39;f2&#39;</span><span class="p">)</span>

<span class="p">])</span>

<span class="n">s</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>

</div>

</div>

<span class="target" id="module-graphs"></span><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>

<span class="target" id="module-dynamic_dict"></span><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>

SQLAlchemy, originally from the blog post at <a class="reference external" href="http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/">http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/</a>.</p>

<span class="target" id="module-inheritance"></span><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>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>

<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>

<span class="target" id="module-nested_sets"></span><p>Illustrates a rudimentary way to implement the &#8220;nested sets&#8221; pattern for hierarchical data using the SQLAlchemy ORM.</p>

<h2>PostGIS Integration<a class="headerlink" href="#postgis-integration" title="Permalink to this headline">¶</a></h2>

<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 <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>

<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>

<li><tt class="docutils literal"><span class="pre">pickle.py</span></tt> - Quick and dirty, serialize the whole DOM into a BLOB column.  While the example

is very brief, it has very limited functionality.</li>

<li><tt class="docutils literal"><span class="pre">adjacency_list.py</span></tt> - Each DOM node is stored in an individual table row, with attributes

represented in a separate table.  The nodes are associated in a hierarchy using an adjacency list

structure.  A query function is introduced which can search for nodes along any path with a given

structure of attributes, basically a (very narrow) subset of xpath.</li>

<li><tt class="docutils literal"><span class="pre">optimized_al.py</span></tt> - 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 much more efficient.</li>

        <a href="extensions/sqlsoup.html" title="previous chapter">SqlSoup</a>

        <a href="interfaces.html" title="next chapter">&lt;no title&gt;</a>

        &copy; <a href="../copyright.html">Copyright</a> 2007-2012, the SQLAlchemy authors and contributors.