File: //usr/share/doc/python-sqlalchemy-0.9.8/doc/core/compiler.html
<!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>
Custom SQL Constructs and Compilation Extension
—
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 Core" href="index.html" />
<link rel="next" title="Runtime Inspection API" href="inspection.html" />
<link rel="prev" title="Core Events" href="events.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 Core">Up</a> |
<a href="events.html" title="Core Events">Prev</a> |
<a href="inspection.html" title="Runtime Inspection API">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="#">
Custom SQL Constructs and Compilation Extension
</a></h3>
<ul>
<li><a class="reference internal" href="#">Custom SQL Constructs and Compilation Extension</a><ul>
<li><a class="reference internal" href="#synopsis">Synopsis</a></li>
<li><a class="reference internal" href="#dialect-specific-compilation-rules">Dialect-specific compilation rules</a></li>
<li><a class="reference internal" href="#compiling-sub-elements-of-a-custom-expression-construct">Compiling sub-elements of a custom expression construct</a><ul>
<li><a class="reference internal" href="#cross-compiling-between-sql-and-ddl-compilers">Cross Compiling between SQL and DDL compilers</a></li>
</ul>
</li>
<li><a class="reference internal" href="#enabling-autocommit-on-a-construct">Enabling Autocommit on a Construct</a></li>
<li><a class="reference internal" href="#changing-the-default-compilation-of-existing-constructs">Changing the default compilation of existing constructs</a></li>
<li><a class="reference internal" href="#changing-compilation-of-types">Changing Compilation of Types</a></li>
<li><a class="reference internal" href="#subclassing-guidelines">Subclassing Guidelines</a></li>
<li><a class="reference internal" href="#further-examples">Further Examples</a><ul>
<li><a class="reference internal" href="#utc-timestamp-function">“UTC timestamp” function</a></li>
<li><a class="reference internal" href="#greatest-function">“GREATEST” function</a></li>
<li><a class="reference internal" href="#false-expression">“false” expression</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.ext.compiler">
<span id="custom-sql-constructs-and-compilation-extension"></span><span id="sqlalchemy-ext-compiler-toplevel"></span><h1>Custom SQL Constructs and Compilation Extension<a class="headerlink" href="#module-sqlalchemy.ext.compiler" title="Permalink to this headline">¶</a></h1>
<p>Provides an API for creation of custom ClauseElements and compilers.</p>
<div class="section" id="synopsis">
<h2>Synopsis<a class="headerlink" href="#synopsis" title="Permalink to this headline">¶</a></h2>
<p>Usage involves the creation of one or more
<a class="reference internal" href="sqlelement.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
more callables defining its compilation:</p>
<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>
<span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="kn">import</span> <span class="n">ColumnClause</span>
<span class="k">class</span> <span class="nc">MyColumn</span><span class="p">(</span><span class="n">ColumnClause</span><span class="p">):</span>
<span class="k">pass</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">MyColumn</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">compile_mycolumn</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>
<span class="k">return</span> <span class="s">"[</span><span class="si">%s</span><span class="s">]"</span> <span class="o">%</span> <span class="n">element</span><span class="o">.</span><span class="n">name</span></pre></div>
</div>
<p>Above, <tt class="docutils literal"><span class="pre">MyColumn</span></tt> extends <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnClause" title="sqlalchemy.sql.expression.ColumnClause"><tt class="xref py py-class docutils literal"><span class="pre">ColumnClause</span></tt></a>,
the base expression element for named column objects. The <tt class="docutils literal"><span class="pre">compiles</span></tt>
decorator registers itself with the <tt class="docutils literal"><span class="pre">MyColumn</span></tt> class so that it is invoked
when the object is compiled to a string:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span>
<span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">MyColumn</span><span class="p">(</span><span class="s">'x'</span><span class="p">),</span> <span class="n">MyColumn</span><span class="p">(</span><span class="s">'y'</span><span class="p">)])</span>
<span class="k">print</span> <span class="nb">str</span><span class="p">(</span><span class="n">s</span><span class="p">)</span></pre></div>
</div>
<p>Produces:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">SELECT</span> <span class="p">[</span><span class="n">x</span><span class="p">],</span> <span class="p">[</span><span class="n">y</span><span class="p">]</span></pre></div>
</div>
</div>
<div class="section" id="dialect-specific-compilation-rules">
<h2>Dialect-specific compilation rules<a class="headerlink" href="#dialect-specific-compilation-rules" title="Permalink to this headline">¶</a></h2>
<p>Compilers can also be made dialect-specific. The appropriate compiler will be
invoked for the dialect in use:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.schema</span> <span class="kn">import</span> <span class="n">DDLElement</span>
<span class="k">class</span> <span class="nc">AlterColumn</span><span class="p">(</span><span class="n">DDLElement</span><span class="p">):</span>
<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="n">column</span><span class="p">,</span> <span class="n">cmd</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">column</span> <span class="o">=</span> <span class="n">column</span>
<span class="bp">self</span><span class="o">.</span><span class="n">cmd</span> <span class="o">=</span> <span class="n">cmd</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">AlterColumn</span><span class="p">)</span>
<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>
<span class="k">return</span> <span class="s">"ALTER COLUMN </span><span class="si">%s</span><span class="s"> ..."</span> <span class="o">%</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="nd">@compiles</span><span class="p">(</span><span class="n">AlterColumn</span><span class="p">,</span> <span class="s">'postgresql'</span><span class="p">)</span>
<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>
<span class="k">return</span> <span class="s">"ALTER TABLE </span><span class="si">%s</span><span class="s"> ALTER COLUMN </span><span class="si">%s</span><span class="s"> ..."</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>
</div>
<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>
</div>
<div class="section" id="compiling-sub-elements-of-a-custom-expression-construct">
<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>
<p>The <tt class="docutils literal"><span class="pre">compiler</span></tt> argument is the
<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
can be inspected for any information about the in-progress compilation,
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
<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
<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>
method which can be used for compilation of embedded attributes:</p>
<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>
<span class="k">class</span> <span class="nc">InsertFromSelect</span><span class="p">(</span><span class="n">Executable</span><span class="p">,</span> <span class="n">ClauseElement</span><span class="p">):</span>
<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="n">table</span><span class="p">,</span> <span class="n">select</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">table</span> <span class="o">=</span> <span class="n">table</span>
<span class="bp">self</span><span class="o">.</span><span class="n">select</span> <span class="o">=</span> <span class="n">select</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">InsertFromSelect</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">visit_insert_from_select</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>
<span class="k">return</span> <span class="s">"INSERT INTO </span><span class="si">%s</span><span class="s"> (</span><span class="si">%s</span><span class="s">)"</span> <span class="o">%</span> <span class="p">(</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">table</span><span class="p">,</span> <span class="n">asfrom</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">select</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">insert</span> <span class="o">=</span> <span class="n">InsertFromSelect</span><span class="p">(</span><span class="n">t1</span><span class="p">,</span> <span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="o">></span><span class="mi">5</span><span class="p">))</span>
<span class="k">print</span> <span class="n">insert</span></pre></div>
</div>
<p>Produces:</p>
<div class="highlight-python"><pre>"INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z
FROM mytable WHERE mytable.x > :x_1)"</pre>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The above <tt class="docutils literal"><span class="pre">InsertFromSelect</span></tt> construct is only an example, this actual
functionality is already available using the
<a class="reference internal" href="dml.html#sqlalchemy.sql.expression.Insert.from_select" title="sqlalchemy.sql.expression.Insert.from_select"><tt class="xref py py-meth docutils literal"><span class="pre">Insert.from_select()</span></tt></a> method.</p>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The above <tt class="docutils literal"><span class="pre">InsertFromSelect</span></tt> construct probably wants to have “autocommit”
enabled. See <a class="reference internal" href="#enabling-compiled-autocommit"><em>Enabling Autocommit on a Construct</em></a> for this step.</p>
</div>
<div class="section" id="cross-compiling-between-sql-and-ddl-compilers">
<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>
<p>SQL and DDL constructs are each compiled using different base compilers -
<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
compilation rules of SQL expressions 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 constraint that embeds a SQL expression:</p>
<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>
<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>
<span class="k">return</span> <span class="s">"CONSTRAINT </span><span class="si">%s</span><span class="s"> CHECK (</span><span class="si">%s</span><span class="s">)"</span> <span class="o">%</span> <span class="p">(</span>
<span class="n">constraint</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
<span class="n">ddlcompiler</span><span class="o">.</span><span class="n">sql_compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">constraint</span><span class="o">.</span><span class="n">expression</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="enabling-autocommit-on-a-construct">
<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>
<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
asked to execute a construct in the absence of a user-defined transaction,
detects if the given construct represents DML or DDL, that is, a data
modification or data definition statement, which requires (or may require,
in the case of DDL) that the transaction generated by the DBAPI be committed
(recall that DBAPI always has a transaction going on regardless of what
SQLAlchemy does). Checking for this is actually accomplished by checking for
the “autocommit” execution option on the construct. When building a
construct like an INSERT derivation, a new DDL type, or perhaps a stored
procedure that alters data, the “autocommit” option needs to be set in order
for the statement to function with “connectionless” execution
(as described in <a class="reference internal" href="connections.html#dbengine-implicit"><em>Connectionless Execution, Implicit Execution</em></a>).</p>
<p>Currently a quick way to do this is to subclass <a class="reference internal" href="selectable.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 “autocommit” flag to the <tt class="docutils literal"><span class="pre">_execution_options</span></tt> dictionary (note this
is a “frozen” dictionary which supplies a generative <tt class="docutils literal"><span class="pre">union()</span></tt> method):</p>
<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>
<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>
<span class="n">_execution_options</span> <span class="o">=</span> \
<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">'autocommit'</span><span class="p">:</span> <span class="bp">True</span><span class="p">})</span></pre></div>
</div>
<p>More succinctly, if the construct is truly similar to an INSERT, UPDATE, or
DELETE, <a class="reference internal" href="dml.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
of <a class="reference internal" href="selectable.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="sqlelement.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
<tt class="docutils literal"><span class="pre">autocommit</span></tt> flag:</p>
<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>
<span class="k">class</span> <span class="nc">MyInsertThing</span><span class="p">(</span><span class="n">UpdateBase</span><span class="p">):</span>
<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>
<span class="o">...</span></pre></div>
</div>
<p>DDL elements that subclass <a class="reference internal" href="ddl.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
“autocommit” flag turned on.</p>
</div>
<div class="section" id="changing-the-default-compilation-of-existing-constructs">
<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>
<p>The compiler extension applies just as well to the existing constructs. When
overriding the compilation of a built in SQL construct, the @compiles
decorator is invoked upon 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>
<p>Within the new compilation function, to get at the “original” compilation
routine, use the appropriate visit_XXX method - this
because compiler.process() will call upon the overriding routine and cause
an endless loop. Such as, to add “prefix” to all insert statements:</p>
<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>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">Insert</span><span class="p">)</span>
<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>
<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">"some prefix"</span><span class="p">),</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span></pre></div>
</div>
<p>The above compiler will prefix all INSERT statements with “some prefix” when
compiled.</p>
</div>
<div class="section" id="changing-compilation-of-types">
<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>
<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 ‘max’ keyword for <tt class="docutils literal"><span class="pre">String</span></tt>/<tt class="docutils literal"><span class="pre">VARCHAR</span></tt>:</p>
<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">'mssql'</span><span class="p">)</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">VARCHAR</span><span class="p">,</span> <span class="s">'mssql'</span><span class="p">)</span>
<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>
<span class="k">if</span> <span class="n">element</span><span class="o">.</span><span class="n">length</span> <span class="o">==</span> <span class="s">'max'</span><span class="p">:</span>
<span class="k">return</span> <span class="s">"VARCHAR('max')"</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">return</span> <span class="n">compiler</span><span class="o">.</span><span class="n">visit_VARCHAR</span><span class="p">(</span><span class="n">element</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'foo'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="s">'max'</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="subclassing-guidelines">
<h2>Subclassing Guidelines<a class="headerlink" href="#subclassing-guidelines" title="Permalink to this headline">¶</a></h2>
<p>A big part of using the compiler extension is subclassing SQLAlchemy
expression constructs. To make this easier, the expression and
schema packages feature a set of “bases” intended for common tasks.
A synopsis is as follows:</p>
<ul>
<li><p class="first"><a class="reference internal" href="sqlelement.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> - This is the root
expression class. Any SQL expression can be derived from this base, and is
probably the best choice for longer constructs such as specialized INSERT
statements.</p>
</li>
<li><p class="first"><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">ColumnElement</span></tt></a> - The root of all
“column-like” elements. Anything that you’d place in the “columns” clause of
a SELECT statement (as well as order by and group by) can derive from this -
the object will automatically have Python “comparison” behavior.</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><tt class="xref py py-class docutils literal"><span class="pre">ColumnElement</span></tt></a> classes want to have a
<tt class="docutils literal"><span class="pre">type</span></tt> member which is expression’s return type. This can be established
at the instance level in the constructor, or at the class level if its
generally constant:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">timestamp</span><span class="p">(</span><span class="n">ColumnElement</span><span class="p">):</span>
<span class="nb">type</span> <span class="o">=</span> <span class="n">TIMESTAMP</span><span class="p">()</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="functions.html#sqlalchemy.sql.functions.FunctionElement" title="sqlalchemy.sql.functions.FunctionElement"><tt class="xref py py-class docutils literal"><span class="pre">FunctionElement</span></tt></a> - This is a hybrid of a
<tt class="docutils literal"><span class="pre">ColumnElement</span></tt> and a “from clause” like object, and represents a SQL
function or stored procedure type of call. Since most databases support
statements along the line of “SELECT FROM <some function>”
<tt class="docutils literal"><span class="pre">FunctionElement</span></tt> adds in the ability to be used in the FROM clause of a
<tt class="docutils literal"><span class="pre">select()</span></tt> construct:</p>
<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">FunctionElement</span>
<span class="k">class</span> <span class="nc">coalesce</span><span class="p">(</span><span class="n">FunctionElement</span><span class="p">):</span>
<span class="n">name</span> <span class="o">=</span> <span class="s">'coalesce'</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">coalesce</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">compile</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>
<span class="k">return</span> <span class="s">"coalesce(</span><span class="si">%s</span><span class="s">)"</span> <span class="o">%</span> <span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">clauses</span><span class="p">)</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">coalesce</span><span class="p">,</span> <span class="s">'oracle'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">compile</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>
<span class="k">if</span> <span class="nb">len</span><span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">clauses</span><span class="p">)</span> <span class="o">></span> <span class="mi">2</span><span class="p">:</span>
<span class="k">raise</span> <span class="ne">TypeError</span><span class="p">(</span><span class="s">"coalesce only supports two arguments on Oracle"</span><span class="p">)</span>
<span class="k">return</span> <span class="s">"nvl(</span><span class="si">%s</span><span class="s">)"</span> <span class="o">%</span> <span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">clauses</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first"><a class="reference internal" href="ddl.html#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><tt class="xref py py-class docutils literal"><span class="pre">DDLElement</span></tt></a> - The root of all DDL expressions,
like CREATE TABLE, ALTER TABLE, etc. Compilation of <tt class="docutils literal"><span class="pre">DDLElement</span></tt>
subclasses is issued by a <tt class="docutils literal"><span class="pre">DDLCompiler</span></tt> instead of a <tt class="docutils literal"><span class="pre">SQLCompiler</span></tt>.
<tt class="docutils literal"><span class="pre">DDLElement</span></tt> also features <tt class="docutils literal"><span class="pre">Table</span></tt> and <tt class="docutils literal"><span class="pre">MetaData</span></tt> event hooks via the
<tt class="docutils literal"><span class="pre">execute_at()</span></tt> method, allowing the construct to be invoked during CREATE
TABLE and DROP TABLE sequences.</p>
</li>
<li><p class="first"><a class="reference internal" href="selectable.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 used with any expression class that represents a “standalone”
SQL statement that can be passed directly to an <tt class="docutils literal"><span class="pre">execute()</span></tt> method. It
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>
</li>
</ul>
</div>
<div class="section" id="further-examples">
<h2>Further Examples<a class="headerlink" href="#further-examples" title="Permalink to this headline">¶</a></h2>
<div class="section" id="utc-timestamp-function">
<h3>“UTC timestamp” function<a class="headerlink" href="#utc-timestamp-function" title="Permalink to this headline">¶</a></h3>
<p>A function that works like “CURRENT_TIMESTAMP” except applies the
appropriate conversions so that the time is in UTC time. Timestamps are best
stored in relational databases as UTC, without time zones. UTC so that your
database doesn’t think time has gone backwards in the hour when daylight
savings ends, without timezones because timezones are like character
encodings - they’re best applied only at the endpoints of an application
(i.e. convert to UTC upon user input, re-apply desired timezone upon display).</p>
<p>For Postgresql and Microsoft SQL Server:</p>
<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>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="kn">import</span> <span class="n">DateTime</span>
<span class="k">class</span> <span class="nc">utcnow</span><span class="p">(</span><span class="n">expression</span><span class="o">.</span><span class="n">FunctionElement</span><span class="p">):</span>
<span class="nb">type</span> <span class="o">=</span> <span class="n">DateTime</span><span class="p">()</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">utcnow</span><span class="p">,</span> <span class="s">'postgresql'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">pg_utcnow</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>
<span class="k">return</span> <span class="s">"TIMEZONE('utc', CURRENT_TIMESTAMP)"</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">utcnow</span><span class="p">,</span> <span class="s">'mssql'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">ms_utcnow</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>
<span class="k">return</span> <span class="s">"GETUTCDATE()"</span></pre></div>
</div>
<p>Example usage:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="p">(</span>
<span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">DateTime</span><span class="p">,</span> <span class="n">MetaData</span>
<span class="p">)</span>
<span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">event</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"event"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"id"</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">Column</span><span class="p">(</span><span class="s">"description"</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="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"timestamp"</span><span class="p">,</span> <span class="n">DateTime</span><span class="p">,</span> <span class="n">server_default</span><span class="o">=</span><span class="n">utcnow</span><span class="p">())</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="greatest-function">
<h3>“GREATEST” function<a class="headerlink" href="#greatest-function" title="Permalink to this headline">¶</a></h3>
<p>The “GREATEST” function is given any number of arguments and returns the one
that is of the highest value - its equivalent to Python’s <tt class="docutils literal"><span class="pre">max</span></tt>
function. A SQL standard version versus a CASE based version which only
accommodates two arguments:</p>
<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>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="kn">import</span> <span class="n">Numeric</span>
<span class="k">class</span> <span class="nc">greatest</span><span class="p">(</span><span class="n">expression</span><span class="o">.</span><span class="n">FunctionElement</span><span class="p">):</span>
<span class="nb">type</span> <span class="o">=</span> <span class="n">Numeric</span><span class="p">()</span>
<span class="n">name</span> <span class="o">=</span> <span class="s">'greatest'</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">default_greatest</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>
<span class="k">return</span> <span class="n">compiler</span><span class="o">.</span><span class="n">visit_function</span><span class="p">(</span><span class="n">element</span><span class="p">)</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">,</span> <span class="s">'sqlite'</span><span class="p">)</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">,</span> <span class="s">'mssql'</span><span class="p">)</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">,</span> <span class="s">'oracle'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">case_greatest</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>
<span class="n">arg1</span><span class="p">,</span> <span class="n">arg2</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">clauses</span><span class="p">)</span>
<span class="k">return</span> <span class="s">"CASE WHEN </span><span class="si">%s</span><span class="s"> > </span><span class="si">%s</span><span class="s"> THEN </span><span class="si">%s</span><span class="s"> ELSE </span><span class="si">%s</span><span class="s"> END"</span> <span class="o">%</span> <span class="p">(</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">arg1</span><span class="p">),</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">arg2</span><span class="p">),</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">arg1</span><span class="p">),</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">arg2</span><span class="p">),</span>
<span class="p">)</span></pre></div>
</div>
<p>Example usage:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Account</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span>
<span class="n">greatest</span><span class="p">(</span>
<span class="n">Account</span><span class="o">.</span><span class="n">checking_balance</span><span class="p">,</span>
<span class="n">Account</span><span class="o">.</span><span class="n">savings_balance</span><span class="p">)</span> <span class="o">></span> <span class="mi">10000</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="false-expression">
<h3>“false” expression<a class="headerlink" href="#false-expression" title="Permalink to this headline">¶</a></h3>
<p>Render a “false” constant expression, rendering as “0” on platforms that
don’t have a “false” constant:</p>
<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>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
<span class="k">class</span> <span class="nc">sql_false</span><span class="p">(</span><span class="n">expression</span><span class="o">.</span><span class="n">ColumnElement</span><span class="p">):</span>
<span class="k">pass</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">default_false</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>
<span class="k">return</span> <span class="s">"false"</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">,</span> <span class="s">'mssql'</span><span class="p">)</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">,</span> <span class="s">'mysql'</span><span class="p">)</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">,</span> <span class="s">'oracle'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">int_false</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>
<span class="k">return</span> <span class="s">"0"</span></pre></div>
</div>
<p>Example usage:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">union_all</span>
<span class="n">exp</span> <span class="o">=</span> <span class="n">union_all</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">sql_false</span><span class="p">()</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">"enrolled"</span><span class="p">)]),</span>
<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>
<span class="p">)</span></pre></div>
</div>
<dl class="function">
<dt id="sqlalchemy.ext.compiler.compiles">
<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>
<dd><p>Register a function as a compiler for a
given <a class="reference internal" href="sqlelement.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>
</dd></dl>
<dl class="function">
<dt id="sqlalchemy.ext.compiler.deregister">
<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>
<dd><p>Remove all custom compilers associated with a given
<a class="reference internal" href="sqlelement.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>
</dd></dl>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="events.html" title="previous chapter">Core Events</a>
Next:
<a href="inspection.html" title="next chapter">Runtime Inspection API</a>
<div id="docs-copyright">
© <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>