← Back to branch summary

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

« back to all changes in this revision

Viewing changes to doc/core/compiler.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/compiler.html
10
10
    
11
11
                Custom SQL Constructs and Compilation Extension
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
 
        <link rel="next" title="Expression Serializer Extension" href="serializer.html" />
 
37
        <link rel="next" title="Runtime Inspection API" href="inspection.html" />
38
38
        <link rel="prev" title="Core Events" href="events.html" />
39
39
 
40
40
    </head>
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>
81
81
            <a href="events.html" title="previous chapter">Core Events</a>
82
82
            </li>
83
83
            <li>Next:
84
 
            <a href="serializer.html" title="next chapter">Expression Serializer Extension</a>
 
84
            <a href="inspection.html" title="next chapter">Runtime Inspection API</a>
85
85
            </li>
86
86
 
87
87
        <li>
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
                Custom SQL Constructs and Compilation Extension
141
141
    </p>
142
142
    <h4>Next Topic</h4>
143
143
    <p>
144
 
    <a href="serializer.html" title="next chapter">Expression Serializer Extension</a>
 
144
    <a href="inspection.html" title="next chapter">Runtime Inspection API</a>
145
145
    </p>
146
146
 
147
147
 
163
163
<p>Provides an API for creation of custom ClauseElements and compilers.</p>
164
164
<div class="section" id="synopsis">
165
165
<h2>Synopsis<a class="headerlink" href="#synopsis" title="Permalink to this headline">¶</a></h2>
166
 
<p>Usage involves the creation of one or more <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>
167
 
subclasses and one or more callables defining its compilation:</p>
 
166
<p>Usage involves the creation of one or more
 
167
<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> subclasses and one or
 
168
more callables defining its compilation:</p>
168
169
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
169
170
<span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="kn">import</span> <span class="n">ColumnClause</span>
170
171
 
208
209
<span class="k">def</span> <span class="nf">visit_alter_column</span><span class="p">(</span><span class="n">element</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
209
210
    <span class="k">return</span> <span class="s">&quot;ALTER TABLE </span><span class="si">%s</span><span class="s"> ALTER COLUMN </span><span class="si">%s</span><span class="s"> ...&quot;</span> <span class="o">%</span> <span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">table</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">element</span><span class="o">.</span><span class="n">column</span><span class="o">.</span><span class="n">name</span><span class="p">)</span></pre></div>
210
211
</div>
211
 
<p>The second <tt class="docutils literal"><span class="pre">visit_alter_table</span></tt> will be invoked when any <tt class="docutils literal"><span class="pre">postgresql</span></tt> dialect is used.</p>
 
212
<p>The second <tt class="docutils literal"><span class="pre">visit_alter_table</span></tt> will be invoked when any <tt class="docutils literal"><span class="pre">postgresql</span></tt>
 
213
dialect is used.</p>
212
214
</div>
213
215
<div class="section" id="compiling-sub-elements-of-a-custom-expression-construct">
214
216
<h2>Compiling sub-elements of a custom expression construct<a class="headerlink" href="#compiling-sub-elements-of-a-custom-expression-construct" title="Permalink to this headline">¶</a></h2>
215
 
<p>The <tt class="docutils literal"><span class="pre">compiler</span></tt> argument is the <a class="reference internal" href="internals.html#sqlalchemy.engine.base.Compiled" title="sqlalchemy.engine.base.Compiled"><tt class="xref py py-class docutils literal"><span class="pre">Compiled</span></tt></a>
216
 
object in use. This object can be inspected for any information about the
217
 
in-progress compilation, including <tt class="docutils literal"><span class="pre">compiler.dialect</span></tt>,
218
 
<tt class="docutils literal"><span class="pre">compiler.statement</span></tt> etc. The <a class="reference internal" href="internals.html#sqlalchemy.sql.compiler.SQLCompiler" title="sqlalchemy.sql.compiler.SQLCompiler"><tt class="xref py py-class docutils literal"><span class="pre">SQLCompiler</span></tt></a>
219
 
and <a class="reference internal" href="internals.html#sqlalchemy.sql.compiler.DDLCompiler" title="sqlalchemy.sql.compiler.DDLCompiler"><tt class="xref py py-class docutils literal"><span class="pre">DDLCompiler</span></tt></a> both include a <tt class="docutils literal"><span class="pre">process()</span></tt>
 
217
<p>The <tt class="docutils literal"><span class="pre">compiler</span></tt> argument is the
 
218
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Compiled" title="sqlalchemy.engine.interfaces.Compiled"><tt class="xref py py-class docutils literal"><span class="pre">Compiled</span></tt></a> object in use. This object
 
219
can be inspected for any information about the in-progress compilation,
 
220
including <tt class="docutils literal"><span class="pre">compiler.dialect</span></tt>, <tt class="docutils literal"><span class="pre">compiler.statement</span></tt> etc. The
 
221
<a class="reference internal" href="internals.html#sqlalchemy.sql.compiler.SQLCompiler" title="sqlalchemy.sql.compiler.SQLCompiler"><tt class="xref py py-class docutils literal"><span class="pre">SQLCompiler</span></tt></a> and
 
222
<a class="reference internal" href="internals.html#sqlalchemy.sql.compiler.DDLCompiler" title="sqlalchemy.sql.compiler.DDLCompiler"><tt class="xref py py-class docutils literal"><span class="pre">DDLCompiler</span></tt></a> both include a <tt class="docutils literal"><span class="pre">process()</span></tt>
220
223
method which can be used for compilation of embedded attributes:</p>
221
224
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="kn">import</span> <span class="n">Executable</span><span class="p">,</span> <span class="n">ClauseElement</span>
222
225
 
245
248
</div>
246
249
<div class="section" id="cross-compiling-between-sql-and-ddl-compilers">
247
250
<h3>Cross Compiling between SQL and DDL compilers<a class="headerlink" href="#cross-compiling-between-sql-and-ddl-compilers" title="Permalink to this headline">¶</a></h3>
248
 
<p>SQL and DDL constructs are each compiled using different base compilers - <tt class="docutils literal"><span class="pre">SQLCompiler</span></tt>
249
 
and <tt class="docutils literal"><span class="pre">DDLCompiler</span></tt>.   A common need is to access the compilation rules of SQL expressions
250
 
from within a DDL expression. The <tt class="docutils literal"><span class="pre">DDLCompiler</span></tt> includes an accessor <tt class="docutils literal"><span class="pre">sql_compiler</span></tt> for this reason, such as below where we generate a CHECK
251
 
constraint that embeds a SQL expression:</p>
 
251
<p>SQL and DDL constructs are each compiled using different base compilers -
 
252
<tt class="docutils literal"><span class="pre">SQLCompiler</span></tt> and <tt class="docutils literal"><span class="pre">DDLCompiler</span></tt>.   A common need is to access the
 
253
compilation rules of SQL expressions from within a DDL expression. The
 
254
<tt class="docutils literal"><span class="pre">DDLCompiler</span></tt> includes an accessor <tt class="docutils literal"><span class="pre">sql_compiler</span></tt> for this reason, such as
 
255
below where we generate a CHECK constraint that embeds a SQL expression:</p>
252
256
<div class="highlight-python"><div class="highlight"><pre><span class="nd">@compiles</span><span class="p">(</span><span class="n">MyConstraint</span><span class="p">)</span>
253
257
<span class="k">def</span> <span class="nf">compile_my_constraint</span><span class="p">(</span><span class="n">constraint</span><span class="p">,</span> <span class="n">ddlcompiler</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
254
258
    <span class="k">return</span> <span class="s">&quot;CONSTRAINT </span><span class="si">%s</span><span class="s"> CHECK (</span><span class="si">%s</span><span class="s">)&quot;</span> <span class="o">%</span> <span class="p">(</span>
260
264
</div>
261
265
<div class="section" id="enabling-autocommit-on-a-construct">
262
266
<span id="enabling-compiled-autocommit"></span><h2>Enabling Autocommit on a Construct<a class="headerlink" href="#enabling-autocommit-on-a-construct" title="Permalink to this headline">¶</a></h2>
263
 
<p>Recall from the section <a class="reference internal" href="connections.html#autocommit"><em>Understanding Autocommit</em></a> that the <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>, when asked to execute
264
 
a construct in the absence of a user-defined transaction, detects if the given
265
 
construct represents DML or DDL, that is, a data modification or data definition statement, which
266
 
requires (or may require, in the case of DDL) that the transaction generated by the DBAPI be committed
267
 
(recall that DBAPI always has a transaction going on regardless of what SQLAlchemy does).   Checking
268
 
for this is actually accomplished
269
 
by checking for the &#8220;autocommit&#8221; execution option on the construct.    When building a construct like
270
 
an INSERT derivation, a new DDL type, or perhaps a stored procedure that alters data, the &#8220;autocommit&#8221;
271
 
option needs to be set in order for the statement to function with &#8220;connectionless&#8221; execution
 
267
<p>Recall from the section <a class="reference internal" href="connections.html#autocommit"><em>Understanding Autocommit</em></a> that the <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>, when
 
268
asked to execute a construct in the absence of a user-defined transaction,
 
269
detects if the given construct represents DML or DDL, that is, a data
 
270
modification or data definition statement, which requires (or may require,
 
271
in the case of DDL) that the transaction generated by the DBAPI be committed
 
272
(recall that DBAPI always has a transaction going on regardless of what
 
273
SQLAlchemy does).   Checking for this is actually accomplished by checking for
 
274
the &#8220;autocommit&#8221; execution option on the construct.    When building a
 
275
construct like an INSERT derivation, a new DDL type, or perhaps a stored
 
276
procedure that alters data, the &#8220;autocommit&#8221; option needs to be set in order
 
277
for the statement to function with &#8220;connectionless&#8221; execution
272
278
(as described in <a class="reference internal" href="connections.html#dbengine-implicit"><em>Connectionless Execution, Implicit Execution</em></a>).</p>
273
 
<p>Currently a quick way to do this is to subclass <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><tt class="xref py py-class docutils literal"><span class="pre">Executable</span></tt></a>, then add the &#8220;autocommit&#8221; flag
274
 
to the <tt class="docutils literal"><span class="pre">_execution_options</span></tt> dictionary (note this is a &#8220;frozen&#8221; dictionary which supplies a generative
275
 
<tt class="docutils literal"><span class="pre">union()</span></tt> method):</p>
 
279
<p>Currently a quick way to do this is to subclass <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><tt class="xref py py-class docutils literal"><span class="pre">Executable</span></tt></a>, then
 
280
add the &#8220;autocommit&#8221; flag to the <tt class="docutils literal"><span class="pre">_execution_options</span></tt> dictionary (note this
 
281
is a &#8220;frozen&#8221; dictionary which supplies a generative <tt class="docutils literal"><span class="pre">union()</span></tt> method):</p>
276
282
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="kn">import</span> <span class="n">Executable</span><span class="p">,</span> <span class="n">ClauseElement</span>
277
283
 
278
284
<span class="k">class</span> <span class="nc">MyInsertThing</span><span class="p">(</span><span class="n">Executable</span><span class="p">,</span> <span class="n">ClauseElement</span><span class="p">):</span>
279
285
    <span class="n">_execution_options</span> <span class="o">=</span> \
280
286
        <span class="n">Executable</span><span class="o">.</span><span class="n">_execution_options</span><span class="o">.</span><span class="n">union</span><span class="p">({</span><span class="s">&#39;autocommit&#39;</span><span class="p">:</span> <span class="bp">True</span><span class="p">})</span></pre></div>
281
287
</div>
282
 
<p>More succinctly, if the construct is truly similar to an INSERT, UPDATE, or DELETE, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.UpdateBase" title="sqlalchemy.sql.expression.UpdateBase"><tt class="xref py py-class docutils literal"><span class="pre">UpdateBase</span></tt></a>
283
 
can be used, which already is a subclass of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><tt class="xref py py-class docutils literal"><span class="pre">Executable</span></tt></a>, <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> and includes the
 
288
<p>More succinctly, if the construct is truly similar to an INSERT, UPDATE, or
 
289
DELETE, <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.UpdateBase" title="sqlalchemy.sql.expression.UpdateBase"><tt class="xref py py-class docutils literal"><span class="pre">UpdateBase</span></tt></a> can be used, which already is a subclass
 
290
of <a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><tt class="xref py py-class docutils literal"><span class="pre">Executable</span></tt></a>, <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> and includes the
284
291
<tt class="docutils literal"><span class="pre">autocommit</span></tt> flag:</p>
285
292
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="kn">import</span> <span class="n">UpdateBase</span>
286
293
 
288
295
    <span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="o">...</span><span class="p">):</span>
289
296
        <span class="o">...</span></pre></div>
290
297
</div>
291
 
<p>DDL elements that subclass <a class="reference internal" href="schema.html#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><tt class="xref py py-class docutils literal"><span class="pre">DDLElement</span></tt></a> already have the &#8220;autocommit&#8221; flag turned on.</p>
 
298
<p>DDL elements that subclass <a class="reference internal" href="schema.html#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><tt class="xref py py-class docutils literal"><span class="pre">DDLElement</span></tt></a> already have the
 
299
&#8220;autocommit&#8221; flag turned on.</p>
292
300
</div>
293
301
<div class="section" id="changing-the-default-compilation-of-existing-constructs">
294
302
<h2>Changing the default compilation of existing constructs<a class="headerlink" href="#changing-the-default-compilation-of-existing-constructs" title="Permalink to this headline">¶</a></h2>
295
 
<p>The compiler extension applies just as well to the existing constructs.  When overriding
296
 
the compilation of a built in SQL construct, the &#64;compiles decorator is invoked upon
297
 
the appropriate class (be sure to use the class, i.e. <tt class="docutils literal"><span class="pre">Insert</span></tt> or <tt class="docutils literal"><span class="pre">Select</span></tt>, instead of the creation function such as <tt class="docutils literal"><span class="pre">insert()</span></tt> or <tt class="docutils literal"><span class="pre">select()</span></tt>).</p>
298
 
<p>Within the new compilation function, to get at the &#8220;original&#8221; compilation routine,
299
 
use the appropriate visit_XXX method - this because compiler.process() will call upon the
300
 
overriding routine and cause an endless loop.   Such as, to add &#8220;prefix&#8221; to all insert statements:</p>
 
303
<p>The compiler extension applies just as well to the existing constructs.  When
 
304
overriding the compilation of a built in SQL construct, the &#64;compiles
 
305
decorator is invoked upon the appropriate class (be sure to use the class,
 
306
i.e. <tt class="docutils literal"><span class="pre">Insert</span></tt> or <tt class="docutils literal"><span class="pre">Select</span></tt>, instead of the creation function such
 
307
as <tt class="docutils literal"><span class="pre">insert()</span></tt> or <tt class="docutils literal"><span class="pre">select()</span></tt>).</p>
 
308
<p>Within the new compilation function, to get at the &#8220;original&#8221; compilation
 
309
routine, use the appropriate visit_XXX method - this
 
310
because compiler.process() will call upon the overriding routine and cause
 
311
an endless loop.   Such as, to add &#8220;prefix&#8221; to all insert statements:</p>
301
312
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="kn">import</span> <span class="n">Insert</span>
302
313
 
303
314
<span class="nd">@compiles</span><span class="p">(</span><span class="n">Insert</span><span class="p">)</span>
304
315
<span class="k">def</span> <span class="nf">prefix_inserts</span><span class="p">(</span><span class="n">insert</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
305
316
    <span class="k">return</span> <span class="n">compiler</span><span class="o">.</span><span class="n">visit_insert</span><span class="p">(</span><span class="n">insert</span><span class="o">.</span><span class="n">prefix_with</span><span class="p">(</span><span class="s">&quot;some prefix&quot;</span><span class="p">),</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span></pre></div>
306
317
</div>
307
 
<p>The above compiler will prefix all INSERT statements with &#8220;some prefix&#8221; when compiled.</p>
 
318
<p>The above compiler will prefix all INSERT statements with &#8220;some prefix&#8221; when
 
319
compiled.</p>
308
320
</div>
309
321
<div class="section" id="changing-compilation-of-types">
310
322
<span id="type-compilation-extension"></span><h2>Changing Compilation of Types<a class="headerlink" href="#changing-compilation-of-types" title="Permalink to this headline">¶</a></h2>
311
 
<p><tt class="docutils literal"><span class="pre">compiler</span></tt> works for types, too, such as below where we implement the MS-SQL specific &#8216;max&#8217; keyword for <tt class="docutils literal"><span class="pre">String</span></tt>/<tt class="docutils literal"><span class="pre">VARCHAR</span></tt>:</p>
 
323
<p><tt class="docutils literal"><span class="pre">compiler</span></tt> works for types, too, such as below where we implement the
 
324
MS-SQL specific &#8216;max&#8217; keyword for <tt class="docutils literal"><span class="pre">String</span></tt>/<tt class="docutils literal"><span class="pre">VARCHAR</span></tt>:</p>
312
325
<div class="highlight-python"><div class="highlight"><pre><span class="nd">@compiles</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="s">&#39;mssql&#39;</span><span class="p">)</span>
313
326
<span class="nd">@compiles</span><span class="p">(</span><span class="n">VARCHAR</span><span class="p">,</span> <span class="s">&#39;mssql&#39;</span><span class="p">)</span>
314
327
<span class="k">def</span> <span class="nf">compile_varchar</span><span class="p">(</span><span class="n">element</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
375
388
<tt class="docutils literal"><span class="pre">execute_at()</span></tt> method, allowing the construct to be invoked during CREATE
376
389
TABLE and DROP TABLE sequences.</p>
377
390
</li>
378
 
<li><p class="first"><a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><tt class="xref py py-class docutils literal"><span class="pre">Executable</span></tt></a> - This is a mixin which should be
379
 
used with any expression class that represents a &#8220;standalone&#8221; SQL statement that
380
 
can be passed directly to an <tt class="docutils literal"><span class="pre">execute()</span></tt> method.  It is already implicit
381
 
within <tt class="docutils literal"><span class="pre">DDLElement</span></tt> and <tt class="docutils literal"><span class="pre">FunctionElement</span></tt>.</p>
 
391
<li><p class="first"><a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><tt class="xref py py-class docutils literal"><span class="pre">Executable</span></tt></a> - This is a mixin which
 
392
should be used with any expression class that represents a &#8220;standalone&#8221;
 
393
SQL statement that can be passed directly to an <tt class="docutils literal"><span class="pre">execute()</span></tt> method.  It
 
394
is already implicit within <tt class="docutils literal"><span class="pre">DDLElement</span></tt> and <tt class="docutils literal"><span class="pre">FunctionElement</span></tt>.</p>
382
395
</li>
383
396
</ul>
384
397
</div>
386
399
<h2>Further Examples<a class="headerlink" href="#further-examples" title="Permalink to this headline">¶</a></h2>
387
400
<div class="section" id="utc-timestamp-function">
388
401
<h3>&#8220;UTC timestamp&#8221; function<a class="headerlink" href="#utc-timestamp-function" title="Permalink to this headline">¶</a></h3>
389
 
<p>A function that works like &#8220;CURRENT_TIMESTAMP&#8221; except applies the appropriate conversions
390
 
so that the time is in UTC time.   Timestamps are best stored in relational databases
391
 
as UTC, without time zones.   UTC so that your database doesn&#8217;t think time has gone
392
 
backwards in the hour when daylight savings ends, without timezones because timezones
393
 
are like character encodings - they&#8217;re best applied only at the endpoints of an
394
 
application (i.e. convert to UTC upon user input, re-apply desired timezone upon display).</p>
 
402
<p>A function that works like &#8220;CURRENT_TIMESTAMP&#8221; except applies the
 
403
appropriate conversions so that the time is in UTC time.   Timestamps are best
 
404
stored in relational databases as UTC, without time zones.   UTC so that your
 
405
database doesn&#8217;t think time has gone backwards in the hour when daylight
 
406
savings ends, without timezones because timezones are like character
 
407
encodings - they&#8217;re best applied only at the endpoints of an application
 
408
(i.e. convert to UTC upon user input, re-apply desired timezone upon display).</p>
395
409
<p>For Postgresql and Microsoft SQL Server:</p>
396
410
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">expression</span>
397
411
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
422
436
</div>
423
437
<div class="section" id="greatest-function">
424
438
<h3>&#8220;GREATEST&#8221; function<a class="headerlink" href="#greatest-function" title="Permalink to this headline">¶</a></h3>
425
 
<p>The &#8220;GREATEST&#8221; function is given any number of arguments and returns the one that is
426
 
of the highest value - it&#8217;s equivalent to Python&#8217;s <tt class="docutils literal"><span class="pre">max</span></tt> function.  A SQL
427
 
standard version versus a CASE based version which only accommodates two
428
 
arguments:</p>
 
439
<p>The &#8220;GREATEST&#8221; function is given any number of arguments and returns the one
 
440
that is of the highest value - it&#8217;s equivalent to Python&#8217;s <tt class="docutils literal"><span class="pre">max</span></tt>
 
441
function.  A SQL standard version versus a CASE based version which only
 
442
accommodates two arguments:</p>
429
443
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">expression</span>
430
444
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
431
445
<span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="kn">import</span> <span class="n">Numeric</span>
461
475
</div>
462
476
<div class="section" id="false-expression">
463
477
<h3>&#8220;false&#8221; expression<a class="headerlink" href="#false-expression" title="Permalink to this headline">¶</a></h3>
464
 
<p>Render a &#8220;false&#8221; constant expression, rendering as &#8220;0&#8221; on platforms that don&#8217;t have a &#8220;false&#8221; constant:</p>
 
478
<p>Render a &#8220;false&#8221; constant expression, rendering as &#8220;0&#8221; on platforms that
 
479
don&#8217;t have a &#8220;false&#8221; constant:</p>
465
480
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">expression</span>
466
481
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
467
482
 
486
501
    <span class="n">select</span><span class="p">([</span><span class="n">customers</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">customers</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">enrolled</span><span class="p">])</span>
487
502
<span class="p">)</span></pre></div>
488
503
</div>
 
504
<dl class="function">
 
505
<dt id="sqlalchemy.ext.compiler.compiles">
 
506
<tt class="descclassname">sqlalchemy.ext.compiler.</tt><tt class="descname">compiles</tt><big>(</big><em>class_</em>, <em>*specs</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.compiler.compiles" title="Permalink to this definition">¶</a></dt>
 
507
<dd><p>Register a function as a compiler for a
 
508
given <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> type.</p>
 
509
</dd></dl>
 
510
 
 
511
<dl class="function">
 
512
<dt id="sqlalchemy.ext.compiler.deregister">
 
513
<tt class="descclassname">sqlalchemy.ext.compiler.</tt><tt class="descname">deregister</tt><big>(</big><em>class_</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.compiler.deregister" title="Permalink to this definition">¶</a></dt>
 
514
<dd><p>Remove all custom compilers associated with a given
 
515
<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> type.</p>
 
516
</dd></dl>
 
517
 
489
518
</div>
490
519
</div>
491
520
</div>
498
527
        Previous:
499
528
        <a href="events.html" title="previous chapter">Core Events</a>
500
529
        Next:
501
 
        <a href="serializer.html" title="next chapter">Expression Serializer Extension</a>
 
530
        <a href="inspection.html" title="next chapter">Runtime Inspection API</a>
502
531
 
503
532
    <div id="docs-copyright">
504
 
        &copy; <a href="../copyright.html">Copyright</a> 2007-2012, the SQLAlchemy authors and contributors.
 
533
        &copy; <a href="../copyright.html">Copyright</a> 2007-2013, the SQLAlchemy authors and contributors.
505
534
        Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
506
535
    </div>
507
536
</div>