PageRenderTime 62ms CodeModel.GetById 17ms RepoModel.GetById 1ms app.codeStats 0ms

/SQLAlchemy-0.7.8/doc/core/compiler.html

#
HTML | 515 lines | 435 code | 80 blank | 0 comment | 0 complexity | c162c6338f13fe361eb44ba27f2e3401 MD5 | raw file
    

  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    2. 

  2.   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    3. 

  3. 

  4. <html>
    5. 

  5.     <head>
    6. 

  6.         <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    7. 

  7.         
    8. 

  8.         <title>
    9. 

  9.             
    10. 

  10.     
    11. 

  11.                 Custom SQL Constructs and Compilation Extension
    12. 

  12.              &mdash; 
    13. 

  13.     SQLAlchemy 0.7 Documentation
    14. 

  14. 

  15.         </title>
    16. 

  16.         
    17. 

  17.     <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    18. 

  18.     <link rel="stylesheet" href="../_static/docs.css" type="text/css" />
    19. 

  19. 

  20.     <script type="text/javascript">
    21. 

  21.       var DOCUMENTATION_OPTIONS = {
    22. 

  22.           URL_ROOT:    '../',
    23. 

  23.           VERSION:     '0.7.8',
    24. 

  24.           COLLAPSE_MODINDEX: false,
    25. 

  25.           FILE_SUFFIX: '.html'
    26. 

  26.       };
    27. 

  27.     </script>
    28. 

  28.         <script type="text/javascript" src="../_static/jquery.js"></script>
    29. 

  29.         <script type="text/javascript" src="../_static/underscore.js"></script>
    30. 

  30.         <script type="text/javascript" src="../_static/doctools.js"></script>
    31. 

  31.     <script type="text/javascript" src="../_static/init.js"></script>
    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. 

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

  36.         <link rel="up" title="SQLAlchemy Core" href="index.html" />
    37. 

  37.         <link rel="next" title="Expression Serializer Extension" href="serializer.html" />
    38. 

  38.         <link rel="prev" title="Core Events" href="events.html" />
    39. 

  39. 

  40.     </head>
    41. 

  41.     <body>
    42. 

  42.         
    43. 

  43. 

  44. 

  45. 

  46. 

  47. 

  48. 

  49. 

  50. 

  51. 

  52. 

  53. <div id="docs-container">
    54. 

  54. 

  55. 

  56. 

  57. <div id="docs-header">
    58. 

  58.     <h1>SQLAlchemy 0.7 Documentation</h1>
    59. 

  59. 

  60.     <div id="docs-search">
    61. 

  61.     Search:
    62. 

  62.     <form class="search" action="../search.html" method="get">
    63. 

  63.       <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
    64. 

  64.       <input type="hidden" name="check_keywords" value="yes" />
    65. 

  65.       <input type="hidden" name="area" value="default" />
    66. 

  66.     </form>
    67. 

  67.     </div>
    68. 

  68. 

  69.     <div id="docs-version-header">
    70. 

  70.         Release: <span class="version-num">0.7.8</span> | Release Date: June 16, 2012
    71. 

  71. 

  72. 

  73.     </div>
    74. 

  74. 

  75. </div>
    76. 

  76. 

  77. <div id="docs-top-navigation">
    78. 

  78.     <div id="docs-top-page-control" class="docs-navigation-links">
    79. 

  79.         <ul>
    80. 

  80.             <li>Prev:
    81. 

  81.             <a href="events.html" title="previous chapter">Core Events</a>
    82. 

  82.             </li>
    83. 

  83.             <li>Next:
    84. 

  84.             <a href="serializer.html" title="next chapter">Expression Serializer Extension</a>
    85. 

  85.             </li>
    86. 

  86. 

  87.         <li>
    88. 

  88.             <a href="../contents.html">Table of Contents</a> |
    89. 

  89.             <a href="../genindex.html">Index</a>
    90. 

  90.             | <a href="../_sources/core/compiler.txt">view source
    91. 

  91.         </li>
    92. 

  92.         </ul>
    93. 

  93.     </div>
    94. 

  94. 

  95.     <div id="docs-navigation-banner">
    96. 

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

  97.                 ť <a href="index.html" title="SQLAlchemy Core">SQLAlchemy Core</a>
    98. 

  98.         ť 
    99. 

  99.                 Custom SQL Constructs and Compilation Extension
    100. 

  100.              
    101. 

  101. 

  102.         <h2>
    103. 

  103.             
    104. 

  104.                 Custom SQL Constructs and Compilation Extension
    105. 

  105.             
    106. 

  106.         </h2>
    107. 

  107.     </div>
    108. 

  108. 

  109. </div>
    110. 

  110. 

  111. <div id="docs-body-container">
    112. 

  112. 

  113.     <div id="docs-sidebar">
    114. 

  114.     <h3><a href="../index.html">Table of Contents</a></h3>
    115. 

  115.     <ul>
    116. 

  116. <li><a class="reference internal" href="#">Custom SQL Constructs and Compilation Extension</a><ul>
    117. 

  117. <li><a class="reference internal" href="#synopsis">Synopsis</a></li>
    118. 

  118. <li><a class="reference internal" href="#dialect-specific-compilation-rules">Dialect-specific compilation rules</a></li>
    119. 

  119. <li><a class="reference internal" href="#compiling-sub-elements-of-a-custom-expression-construct">Compiling sub-elements of a custom expression construct</a><ul>
    120. 

  120. <li><a class="reference internal" href="#cross-compiling-between-sql-and-ddl-compilers">Cross Compiling between SQL and DDL compilers</a></li>
    121. 

  121. </ul>
    122. 

  122. </li>
    123. 

  123. <li><a class="reference internal" href="#enabling-autocommit-on-a-construct">Enabling Autocommit on a Construct</a></li>
    124. 

  124. <li><a class="reference internal" href="#changing-the-default-compilation-of-existing-constructs">Changing the default compilation of existing constructs</a></li>
    125. 

  125. <li><a class="reference internal" href="#changing-compilation-of-types">Changing Compilation of Types</a></li>
    126. 

  126. <li><a class="reference internal" href="#subclassing-guidelines">Subclassing Guidelines</a></li>
    127. 

  127. <li><a class="reference internal" href="#further-examples">Further Examples</a><ul>
    128. 

  128. <li><a class="reference internal" href="#utc-timestamp-function">&#8220;UTC timestamp&#8221; function</a></li>
    129. 

  129. <li><a class="reference internal" href="#greatest-function">&#8220;GREATEST&#8221; function</a></li>
    130. 

  130. <li><a class="reference internal" href="#false-expression">&#8220;false&#8221; expression</a></li>
    131. 

  131. </ul>
    132. 

  132. </li>
    133. 

  133. </ul>
    134. 

  134. </li>
    135. 

  135. </ul>
    136. 

  136. 

  137. 

  138.     <h4>Previous Topic</h4>
    139. 

  139.     <p>
    140. 

  140.     <a href="events.html" title="previous chapter">Core Events</a>
    141. 

  141.     </p>
    142. 

  142.     <h4>Next Topic</h4>
    143. 

  143.     <p>
    144. 

  144.     <a href="serializer.html" title="next chapter">Expression Serializer Extension</a>
    145. 

  145.     </p>
    146. 

  146. 

  147. 

  148.     <h4>Quick Search</h4>
    149. 

  149.     <p>
    150. 

  150.     <form class="search" action="../search.html" method="get">
    151. 

  151.       <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
    152. 

  152.       <input type="hidden" name="check_keywords" value="yes" />
    153. 

  153.       <input type="hidden" name="area" value="default" />
    154. 

  154.     </form>
    155. 

  155.     </p>
    156. 

  156. 

  157.     </div>
    158. 

  158. 

  159.     <div id="docs-body" class="withsidebar" >
    160. 

  160.         
    161. 

  161. <div class="section" id="module-sqlalchemy.ext.compiler">
    162. 

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

  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. 

  167. subclasses and one or more callables defining its compilation:</p>
    168. 

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

  169. <span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="kn">import</span> <span class="n">ColumnClause</span>
    170. 

  170. 

  171. <span class="k">class</span> <span class="nc">MyColumn</span><span class="p">(</span><span class="n">ColumnClause</span><span class="p">):</span>
    172. 

  172.     <span class="k">pass</span>
    173. 

  173. 

  174. <span class="nd">@compiles</span><span class="p">(</span><span class="n">MyColumn</span><span class="p">)</span>
    175. 

  175. <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>
    176. 

  176.     <span class="k">return</span> <span class="s">&quot;[</span><span class="si">%s</span><span class="s">]&quot;</span> <span class="o">%</span> <span class="n">element</span><span class="o">.</span><span class="n">name</span></pre></div>
    177. 

  177. </div>
    178. 

  178. <p>Above, <tt class="docutils literal"><span class="pre">MyColumn</span></tt> extends <a class="reference internal" href="expression_api.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>,
    179. 

  179. the base expression element for named column objects. The <tt class="docutils literal"><span class="pre">compiles</span></tt>
    180. 

  180. decorator registers itself with the <tt class="docutils literal"><span class="pre">MyColumn</span></tt> class so that it is invoked
    181. 

  181. when the object is compiled to a string:</p>
    182. 

  182. <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>
    183. 

  183. 

  184. <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">&#39;x&#39;</span><span class="p">),</span> <span class="n">MyColumn</span><span class="p">(</span><span class="s">&#39;y&#39;</span><span class="p">)])</span>
    185. 

  185. <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>
    186. 

  186. </div>
    187. 

  187. <p>Produces:</p>
    188. 

  188. <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>
    189. 

  189. </div>
    190. 

  190. </div>
    191. 

  191. <div class="section" id="dialect-specific-compilation-rules">
    192. 

  192. <h2>Dialect-specific compilation rules<a class="headerlink" href="#dialect-specific-compilation-rules" title="Permalink to this headline">ś</a></h2>
    193. 

  193. <p>Compilers can also be made dialect-specific. The appropriate compiler will be
    194. 

  194. invoked for the dialect in use:</p>
    195. 

  195. <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>
    196. 

  196. 

  197. <span class="k">class</span> <span class="nc">AlterColumn</span><span class="p">(</span><span class="n">DDLElement</span><span class="p">):</span>
    198. 

  198. 

  199.     <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>
    200. 

  200.         <span class="bp">self</span><span class="o">.</span><span class="n">column</span> <span class="o">=</span> <span class="n">column</span>
    201. 

  201.         <span class="bp">self</span><span class="o">.</span><span class="n">cmd</span> <span class="o">=</span> <span class="n">cmd</span>
    202. 

  202. 

  203. <span class="nd">@compiles</span><span class="p">(</span><span class="n">AlterColumn</span><span class="p">)</span>
    204. 

  204. <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>
    205. 

  205.     <span class="k">return</span> <span class="s">&quot;ALTER COLUMN </span><span class="si">%s</span><span class="s"> ...&quot;</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>
    206. 

  206. 

  207. <span class="nd">@compiles</span><span class="p">(</span><span class="n">AlterColumn</span><span class="p">,</span> <span class="s">&#39;postgresql&#39;</span><span class="p">)</span>
    208. 

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

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

  210. </div>
    211. 

  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. 

  212. </div>
    213. 

  213. <div class="section" id="compiling-sub-elements-of-a-custom-expression-construct">
    214. 

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

  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. 

  216. object in use. This object can be inspected for any information about the
    217. 

  217. in-progress compilation, including <tt class="docutils literal"><span class="pre">compiler.dialect</span></tt>,
    218. 

  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. 

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

  220. method which can be used for compilation of embedded attributes:</p>
    221. 

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

  222. 

  223. <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>
    224. 

  224.     <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>
    225. 

  225.         <span class="bp">self</span><span class="o">.</span><span class="n">table</span> <span class="o">=</span> <span class="n">table</span>
    226. 

  226.         <span class="bp">self</span><span class="o">.</span><span class="n">select</span> <span class="o">=</span> <span class="n">select</span>
    227. 

  227. 

  228. <span class="nd">@compiles</span><span class="p">(</span><span class="n">InsertFromSelect</span><span class="p">)</span>
    229. 

  229. <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>
    230. 

  230.     <span class="k">return</span> <span class="s">&quot;INSERT INTO </span><span class="si">%s</span><span class="s"> (</span><span class="si">%s</span><span class="s">)&quot;</span> <span class="o">%</span> <span class="p">(</span>
    231. 

  231.         <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>
    232. 

  232.         <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>
    233. 

  233.     <span class="p">)</span>
    234. 

  234. 

  235. <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">&gt;</span><span class="mi">5</span><span class="p">))</span>
    236. 

  236. <span class="k">print</span> <span class="n">insert</span></pre></div>
    237. 

  237. </div>
    238. 

  238. <p>Produces:</p>
    239. 

  239. <div class="highlight-python"><div class="highlight"><pre><span class="s">&quot;INSERT INTO mytable (SELECT mytable.x, mytable.y, mytable.z FROM mytable WHERE mytable.x &gt; :x_1)&quot;</span></pre></div>
    240. 

  240. </div>
    241. 

  241. <div class="admonition note">
    242. 

  242. <p class="first admonition-title">Note</p>
    243. 

  243. <p class="last">The above <tt class="docutils literal"><span class="pre">InsertFromSelect</span></tt> construct probably wants to have &#8220;autocommit&#8221; 
    244. 

  244. enabled.  See <a class="reference internal" href="#enabling-compiled-autocommit"><em>Enabling Autocommit on a Construct</em></a> for this step.</p>
    245. 

  245. </div>
    246. 

  246. <div class="section" id="cross-compiling-between-sql-and-ddl-compilers">
    247. 

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

  248. <p>SQL and DDL constructs are each compiled using different base compilers - <tt class="docutils literal"><span class="pre">SQLCompiler</span></tt>
    249. 

  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. 

  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. 

  251. constraint that embeds a SQL expression:</p>
    252. 

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

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

  254.     <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>
    255. 

  255.         <span class="n">constraint</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
    256. 

  256.         <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>
    257. 

  257.     <span class="p">)</span></pre></div>
    258. 

  258. </div>
    259. 

  259. </div>
    260. 

  260. </div>
    261. 

  261. <div class="section" id="enabling-autocommit-on-a-construct">
    262. 

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

  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. 

  264. a construct in the absence of a user-defined transaction, detects if the given
    265. 

  265. construct represents DML or DDL, that is, a data modification or data definition statement, which 
    266. 

  266. requires (or may require, in the case of DDL) that the transaction generated by the DBAPI be committed
    267. 

  267. (recall that DBAPI always has a transaction going on regardless of what SQLAlchemy does).   Checking 
    268. 

  268. for this is actually accomplished
    269. 

  269. by checking for the &#8220;autocommit&#8221; execution option on the construct.    When building a construct like
    270. 

  270. an INSERT derivation, a new DDL type, or perhaps a stored procedure that alters data, the &#8220;autocommit&#8221; 
    271. 

  271. option needs to be set in order for the statement to function with &#8220;connectionless&#8221; execution
    272. 

  272. (as described in <a class="reference internal" href="connections.html#dbengine-implicit"><em>Connectionless Execution, Implicit Execution</em></a>).</p>
    273. 

  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. 

  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. 

  275. <tt class="docutils literal"><span class="pre">union()</span></tt> method):</p>
    276. 

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

  277. 

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

  279.     <span class="n">_execution_options</span> <span class="o">=</span> \
    280. 

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

  281. </div>
    282. 

  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. 

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

  284. <tt class="docutils literal"><span class="pre">autocommit</span></tt> flag:</p>
    285. 

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

  286. 

  287. <span class="k">class</span> <span class="nc">MyInsertThing</span><span class="p">(</span><span class="n">UpdateBase</span><span class="p">):</span>
    288. 

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

  289.         <span class="o">...</span></pre></div>
    290. 

  290. </div>
    291. 

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

  292. </div>
    293. 

  293. <div class="section" id="changing-the-default-compilation-of-existing-constructs">
    294. 

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

  295. <p>The compiler extension applies just as well to the existing constructs.  When overriding
    296. 

  296. the compilation of a built in SQL construct, the &#64;compiles decorator is invoked upon
    297. 

  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. 

  298. <p>Within the new compilation function, to get at the &#8220;original&#8221; compilation routine,
    299. 

  299. use the appropriate visit_XXX method - this because compiler.process() will call upon the 
    300. 

  300. overriding routine and cause an endless loop.   Such as, to add &#8220;prefix&#8221; to all insert statements:</p>
    301. 

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

  302. 

  303. <span class="nd">@compiles</span><span class="p">(</span><span class="n">Insert</span><span class="p">)</span>
    304. 

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

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

  306. </div>
    307. 

  307. <p>The above compiler will prefix all INSERT statements with &#8220;some prefix&#8221; when compiled.</p>
    308. 

  308. </div>
    309. 

  309. <div class="section" id="changing-compilation-of-types">
    310. 

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

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

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

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

  314. <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>
    315. 

  315.     <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">&#39;max&#39;</span><span class="p">:</span>
    316. 

  316.         <span class="k">return</span> <span class="s">&quot;VARCHAR(&#39;max&#39;)&quot;</span>
    317. 

  317.     <span class="k">else</span><span class="p">:</span>
    318. 

  318.         <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>
    319. 

  319. 

  320. <span class="n">foo</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&#39;foo&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
    321. 

  321.     <span class="n">Column</span><span class="p">(</span><span class="s">&#39;data&#39;</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="s">&#39;max&#39;</span><span class="p">))</span>
    322. 

  322. <span class="p">)</span></pre></div>
    323. 

  323. </div>
    324. 

  324. </div>
    325. 

  325. <div class="section" id="subclassing-guidelines">
    326. 

  326. <h2>Subclassing Guidelines<a class="headerlink" href="#subclassing-guidelines" title="Permalink to this headline">ś</a></h2>
    327. 

  327. <p>A big part of using the compiler extension is subclassing SQLAlchemy
    328. 

  328. expression constructs. To make this easier, the expression and
    329. 

  329. schema packages feature a set of &#8220;bases&#8221; intended for common tasks.
    330. 

  330. A synopsis is as follows:</p>
    331. 

  331. <ul>
    332. 

  332. <li><p class="first"><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> - This is the root
    333. 

  333. expression class. Any SQL expression can be derived from this base, and is
    334. 

  334. probably the best choice for longer constructs such as specialized INSERT
    335. 

  335. statements.</p>
    336. 

  336. </li>
    337. 

  337. <li><p class="first"><a class="reference internal" href="expression_api.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
    338. 

  338. &#8220;column-like&#8221; elements. Anything that you&#8217;d place in the &#8220;columns&#8221; clause of
    339. 

  339. a SELECT statement (as well as order by and group by) can derive from this -
    340. 

  340. the object will automatically have Python &#8220;comparison&#8221; behavior.</p>
    341. 

  341. <p><a class="reference internal" href="expression_api.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
    342. 

  342. <tt class="docutils literal"><span class="pre">type</span></tt> member which is expression&#8217;s return type.  This can be established
    343. 

  343. at the instance level in the constructor, or at the class level if its
    344. 

  344. generally constant:</p>
    345. 

  345. <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>
    346. 

  346.     <span class="nb">type</span> <span class="o">=</span> <span class="n">TIMESTAMP</span><span class="p">()</span></pre></div>
    347. 

  347. </div>
    348. 

  348. </li>
    349. 

  349. <li><p class="first"><a class="reference internal" href="expression_api.html#sqlalchemy.sql.expression.FunctionElement" title="sqlalchemy.sql.expression.FunctionElement"><tt class="xref py py-class docutils literal"><span class="pre">FunctionElement</span></tt></a> - This is a hybrid of a
    350. 

  350. <tt class="docutils literal"><span class="pre">ColumnElement</span></tt> and a &#8220;from clause&#8221; like object, and represents a SQL
    351. 

  351. function or stored procedure type of call. Since most databases support
    352. 

  352. statements along the line of &#8220;SELECT FROM &lt;some function&gt;&#8221;
    353. 

  353. <tt class="docutils literal"><span class="pre">FunctionElement</span></tt> adds in the ability to be used in the FROM clause of a
    354. 

  354. <tt class="docutils literal"><span class="pre">select()</span></tt> construct:</p>
    355. 

  355. <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>
    356. 

  356. 

  357. <span class="k">class</span> <span class="nc">coalesce</span><span class="p">(</span><span class="n">FunctionElement</span><span class="p">):</span>
    358. 

  358.     <span class="n">name</span> <span class="o">=</span> <span class="s">&#39;coalesce&#39;</span>
    359. 

  359. 

  360. <span class="nd">@compiles</span><span class="p">(</span><span class="n">coalesce</span><span class="p">)</span>
    361. 

  361. <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>
    362. 

  362.     <span class="k">return</span> <span class="s">&quot;coalesce(</span><span class="si">%s</span><span class="s">)&quot;</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>
    363. 

  363. 

  364. <span class="nd">@compiles</span><span class="p">(</span><span class="n">coalesce</span><span class="p">,</span> <span class="s">&#39;oracle&#39;</span><span class="p">)</span>
    365. 

  365. <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>
    366. 

  366.     <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">&gt;</span> <span class="mi">2</span><span class="p">:</span>
    367. 

  367.         <span class="k">raise</span> <span class="ne">TypeError</span><span class="p">(</span><span class="s">&quot;coalesce only supports two arguments on Oracle&quot;</span><span class="p">)</span>
    368. 

  368.     <span class="k">return</span> <span class="s">&quot;nvl(</span><span class="si">%s</span><span class="s">)&quot;</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>
    369. 

  369. </div>
    370. 

  370. </li>
    371. 

  371. <li><p class="first"><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> - The root of all DDL expressions,
    372. 

  372. like CREATE TABLE, ALTER TABLE, etc. Compilation of <tt class="docutils literal"><span class="pre">DDLElement</span></tt>
    373. 

  373. 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>.
    374. 

  374. <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
    375. 

  375. <tt class="docutils literal"><span class="pre">execute_at()</span></tt> method, allowing the construct to be invoked during CREATE
    376. 

  376. TABLE and DROP TABLE sequences.</p>
    377. 

  377. </li>
    378. 

  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. 

  379. used with any expression class that represents a &#8220;standalone&#8221; SQL statement that
    380. 

  380. can be passed directly to an <tt class="docutils literal"><span class="pre">execute()</span></tt> method.  It is already implicit 
    381. 

  381. within <tt class="docutils literal"><span class="pre">DDLElement</span></tt> and <tt class="docutils literal"><span class="pre">FunctionElement</span></tt>.</p>
    382. 

  382. </li>
    383. 

  383. </ul>
    384. 

  384. </div>
    385. 

  385. <div class="section" id="further-examples">
    386. 

  386. <h2>Further Examples<a class="headerlink" href="#further-examples" title="Permalink to this headline">ś</a></h2>
    387. 

  387. <div class="section" id="utc-timestamp-function">
    388. 

  388. <h3>&#8220;UTC timestamp&#8221; function<a class="headerlink" href="#utc-timestamp-function" title="Permalink to this headline">ś</a></h3>
    389. 

  389. <p>A function that works like &#8220;CURRENT_TIMESTAMP&#8221; except applies the appropriate conversions
    390. 

  390. so that the time is in UTC time.   Timestamps are best stored in relational databases
    391. 

  391. as UTC, without time zones.   UTC so that your database doesn&#8217;t think time has gone
    392. 

  392. backwards in the hour when daylight savings ends, without timezones because timezones
    393. 

  393. are like character encodings - they&#8217;re best applied only at the endpoints of an 
    394. 

  394. application (i.e. convert to UTC upon user input, re-apply desired timezone upon display).</p>
    395. 

  395. <p>For Postgresql and Microsoft SQL Server:</p>
    396. 

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

  397. <span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
    398. 

  398. <span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="kn">import</span> <span class="n">DateTime</span>
    399. 

  399. 

  400. <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>
    401. 

  401.     <span class="nb">type</span> <span class="o">=</span> <span class="n">DateTime</span><span class="p">()</span>
    402. 

  402. 

  403. <span class="nd">@compiles</span><span class="p">(</span><span class="n">utcnow</span><span class="p">,</span> <span class="s">&#39;postgresql&#39;</span><span class="p">)</span>
    404. 

  404. <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>
    405. 

  405.     <span class="k">return</span> <span class="s">&quot;TIMEZONE(&#39;utc&#39;, CURRENT_TIMESTAMP)&quot;</span>
    406. 

  406. 

  407. <span class="nd">@compiles</span><span class="p">(</span><span class="n">utcnow</span><span class="p">,</span> <span class="s">&#39;mssql&#39;</span><span class="p">)</span>
    408. 

  408. <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>
    409. 

  409.     <span class="k">return</span> <span class="s">&quot;GETUTCDATE()&quot;</span></pre></div>
    410. 

  410. </div>
    411. 

  411. <p>Example usage:</p>
    412. 

  412. <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>
    413. 

  413.             <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>
    414. 

  414.         <span class="p">)</span>
    415. 

  415. <span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
    416. 

  416. <span class="n">event</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&quot;event&quot;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
    417. 

  417.     <span class="n">Column</span><span class="p">(</span><span class="s">&quot;id&quot;</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>
    418. 

  418.     <span class="n">Column</span><span class="p">(</span><span class="s">&quot;description&quot;</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>
    419. 

  419.     <span class="n">Column</span><span class="p">(</span><span class="s">&quot;timestamp&quot;</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>
    420. 

  420. <span class="p">)</span></pre></div>
    421. 

  421. </div>
    422. 

  422. </div>
    423. 

  423. <div class="section" id="greatest-function">
    424. 

  424. <h3>&#8220;GREATEST&#8221; function<a class="headerlink" href="#greatest-function" title="Permalink to this headline">ś</a></h3>
    425. 

  425. <p>The &#8220;GREATEST&#8221; function is given any number of arguments and returns the one that is
    426. 

  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. 

  427. standard version versus a CASE based version which only accommodates two 
    428. 

  428. arguments:</p>
    429. 

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

  430. <span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
    431. 

  431. <span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="kn">import</span> <span class="n">Numeric</span>
    432. 

  432. 

  433. <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>
    434. 

  434.     <span class="nb">type</span> <span class="o">=</span> <span class="n">Numeric</span><span class="p">()</span>
    435. 

  435.     <span class="n">name</span> <span class="o">=</span> <span class="s">&#39;greatest&#39;</span>
    436. 

  436. 

  437. <span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">)</span>
    438. 

  438. <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>
    439. 

  439.     <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>
    440. 

  440. 

  441. <span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">,</span> <span class="s">&#39;sqlite&#39;</span><span class="p">)</span>
    442. 

  442. <span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">,</span> <span class="s">&#39;mssql&#39;</span><span class="p">)</span>
    443. 

  443. <span class="nd">@compiles</span><span class="p">(</span><span class="n">greatest</span><span class="p">,</span> <span class="s">&#39;oracle&#39;</span><span class="p">)</span>
    444. 

  444. <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>
    445. 

  445.     <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>
    446. 

  446.     <span class="k">return</span> <span class="s">&quot;CASE WHEN </span><span class="si">%s</span><span class="s"> &gt; </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&quot;</span> <span class="o">%</span> <span class="p">(</span>
    447. 

  447.         <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>
    448. 

  448.         <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>
    449. 

  449.         <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>
    450. 

  450.         <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>
    451. 

  451.     <span class="p">)</span></pre></div>
    452. 

  452. </div>
    453. 

  453. <p>Example usage:</p>
    454. 

  454. <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>\
    455. 

  455.         <span class="nb">filter</span><span class="p">(</span>
    456. 

  456.             <span class="n">greatest</span><span class="p">(</span>
    457. 

  457.                 <span class="n">Account</span><span class="o">.</span><span class="n">checking_balance</span><span class="p">,</span> 
    458. 

  458.                 <span class="n">Account</span><span class="o">.</span><span class="n">savings_balance</span><span class="p">)</span> <span class="o">&gt;</span> <span class="mi">10000</span>
    459. 

  459.         <span class="p">)</span></pre></div>
    460. 

  460. </div>
    461. 

  461. </div>
    462. 

  462. <div class="section" id="false-expression">
    463. 

  463. <h3>&#8220;false&#8221; expression<a class="headerlink" href="#false-expression" title="Permalink to this headline">ś</a></h3>
    464. 

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

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

  466. <span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="kn">import</span> <span class="n">compiles</span>
    467. 

  467. 

  468. <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>
    469. 

  469.     <span class="k">pass</span>
    470. 

  470. 

  471. <span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">)</span>
    472. 

  472. <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>
    473. 

  473.     <span class="k">return</span> <span class="s">&quot;false&quot;</span>
    474. 

  474. 

  475. <span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">,</span> <span class="s">&#39;mssql&#39;</span><span class="p">)</span>
    476. 

  476. <span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">,</span> <span class="s">&#39;mysql&#39;</span><span class="p">)</span>
    477. 

  477. <span class="nd">@compiles</span><span class="p">(</span><span class="n">sql_false</span><span class="p">,</span> <span class="s">&#39;oracle&#39;</span><span class="p">)</span>
    478. 

  478. <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>
    479. 

  479.     <span class="k">return</span> <span class="s">&quot;0&quot;</span></pre></div>
    480. 

  480. </div>
    481. 

  481. <p>Example usage:</p>
    482. 

  482. <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>
    483. 

  483. 

  484. <span class="n">exp</span> <span class="o">=</span> <span class="n">union_all</span><span class="p">(</span>
    485. 

  485.     <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">&quot;enrolled&quot;</span><span class="p">)]),</span>
    486. 

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

  487. <span class="p">)</span></pre></div>
    488. 

  488. </div>
    489. 

  489. </div>
    490. 

  490. </div>
    491. 

  491. </div>
    492. 

  492. 

  493.     </div>
    494. 

  494. 

  495. </div>
    496. 

  496. 

  497. <div id="docs-bottom-navigation" class="docs-navigation-links">
    498. 

  498.         Previous:
    499. 

  499.         <a href="events.html" title="previous chapter">Core Events</a>
    500. 

  500.         Next:
    501. 

  501.         <a href="serializer.html" title="next chapter">Expression Serializer Extension</a>
    502. 

  502. 

  503.     <div id="docs-copyright">
    504. 

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

  505.         Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
    506. 

  506.     </div>
    507. 

  507. </div>
    508. 

  508. 

  509. </div>
    510. 

  510. 

  511.         
    512. 

  512.     </body>
    513. 

  513. </html>
    514. 

  514. 

  515. 

  516.