<!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 Types
—
SQLAlchemy 1.2 Documentation
</title>
<!-- begin iterate through site-imported + 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/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<!-- end iterate through site-imported + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<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 1.2 Documentation" href="../index.html" />
<link rel="up" title="Column and Data Types" href="types.html" />
<link rel="next" title="Base Type API" href="type_api.html" />
<link rel="prev" title="Column and Data Types" href="type_basics.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">1.2.19</span>
| Release Date: April 15, 2019
</div>
<h1>SQLAlchemy 1.2 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 1.2 Documentation</a></h3>
<p id="sidebar-topnav">
<a href="../contents.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<label>
Search terms:
<input type="text" placeholder="search..." name="q" size="12" />
</label>
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<div id="sidebar-banner">
</div>
<div id="docs-sidebar-inner">
<h3>
<a href="index.html" title="SQLAlchemy Core">SQLAlchemy Core</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="tutorial.html">SQL Expression Language Tutorial</a></span></li>
<li><span class="link-container"><a class="reference external" href="expression_api.html">SQL Statements and Expressions API</a></span></li>
<li><span class="link-container"><a class="reference external" href="schema.html">Schema Definition Language</a></span></li>
<li><span class="link-container"><a class="reference external" href="types.html">Column and Data Types</a></span><ul>
<li><span class="link-container"><a class="reference external" href="type_basics.html">Column and Data Types</a></span></li>
<li class="selected"><span class="link-container"><strong>Custom Types</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#overriding-type-compilation">Overriding Type Compilation</a></span></li>
<li><span class="link-container"><a class="reference external" href="#augmenting-existing-types">Augmenting Existing Types</a></span></li>
<li><span class="link-container"><a class="reference external" href="#typedecorator-recipes">TypeDecorator Recipes</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#coercing-encoded-strings-to-unicode">Coercing Encoded Strings to Unicode</a></span></li>
<li><span class="link-container"><a class="reference external" href="#rounding-numerics">Rounding Numerics</a></span></li>
<li><span class="link-container"><a class="reference external" href="#backend-agnostic-guid-type">Backend-agnostic GUID Type</a></span></li>
<li><span class="link-container"><a class="reference external" href="#marshal-json-strings">Marshal JSON Strings</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#adding-mutability">Adding Mutability</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dealing-with-comparison-operations">Dealing with Comparison Operations</a></span></li>
</ul>
</li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#replacing-the-bind-result-processing-of-existing-types">Replacing the Bind/Result Processing of Existing Types</a></span></li>
<li><span class="link-container"><a class="reference external" href="#applying-sql-level-bind-result-processing">Applying SQL-level Bind/Result Processing</a></span></li>
<li><span class="link-container"><a class="reference external" href="#redefining-and-creating-new-operators">Redefining and Creating New Operators</a></span></li>
<li><span class="link-container"><a class="reference external" href="#creating-new-types">Creating New Types</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="type_api.html">Base Type API</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="engines_connections.html">Engine and Connection Use</a></span></li>
<li><span class="link-container"><a class="reference external" href="api_basics.html">Core API Basics</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<span class="target" id="module-sqlalchemy.types"></span><div class="section" id="custom-types">
<span id="types-custom"></span><h1>Custom Types<a class="headerlink" href="#custom-types" title="Permalink to this headline">¶</a></h1>
<p>A variety of methods exist to redefine the behavior of existing types
as well as to provide new ones.</p>
<div class="section" id="overriding-type-compilation">
<h2>Overriding Type Compilation<a class="headerlink" href="#overriding-type-compilation" title="Permalink to this headline">¶</a></h2>
<p>A frequent need is to force the “string” version of a type, that is
the one rendered in a CREATE TABLE statement or other SQL function
like CAST, to be changed. For example, an application may want
to force the rendering of <code class="docutils literal notranslate"><span class="pre">BINARY</span></code> for all platforms
except for one, in which is wants <code class="docutils literal notranslate"><span class="pre">BLOB</span></code> to be rendered. Usage
of an existing generic type, in this case <a class="reference internal" href="type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a>, is
preferred for most use cases. But to control
types more accurately, a compilation directive that is per-dialect
can be associated with any type:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="k">import</span> <span class="n">compiles</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="k">import</span> <span class="n">BINARY</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">BINARY</span><span class="p">,</span> <span class="s2">"sqlite"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">compile_binary_sqlite</span><span class="p">(</span><span class="n">type_</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="s2">"BLOB"</span></pre></div>
</div>
<p>The above code allows the usage of <a class="reference internal" href="type_basics.html#sqlalchemy.types.BINARY" title="sqlalchemy.types.BINARY"><code class="xref py py-class docutils literal notranslate"><span class="pre">types.BINARY</span></code></a>, which
will produce the string <code class="docutils literal notranslate"><span class="pre">BINARY</span></code> against all backends except SQLite,
in which case it will produce <code class="docutils literal notranslate"><span class="pre">BLOB</span></code>.</p>
<p>See the section <a class="reference internal" href="compiler.html#type-compilation-extension"><span class="std std-ref">Changing Compilation of Types</span></a>, a subsection of
<a class="reference internal" href="compiler.html"><span class="std std-ref">Custom SQL Constructs and Compilation Extension</span></a>, for additional examples.</p>
</div>
<div class="section" id="augmenting-existing-types">
<span id="types-typedecorator"></span><h2>Augmenting Existing Types<a class="headerlink" href="#augmenting-existing-types" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> allows the creation of custom types which
add bind-parameter and result-processing behavior to an existing
type object. It is used when additional in-Python marshaling of data
to and from the database is required.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The bind- and result-processing of <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>
is <em>in addition</em> to the processing already performed by the hosted
type, which is customized by SQLAlchemy on a per-DBAPI basis to perform
processing specific to that DBAPI. To change the DBAPI-level processing
for an existing type, see the section <a class="reference internal" href="#replacing-processors"><span class="std std-ref">Replacing the Bind/Result Processing of Existing Types</span></a>.</p>
</div>
<dl class="class">
<dt id="sqlalchemy.types.TypeDecorator">
<em class="property">class </em><code class="descclassname">sqlalchemy.types.</code><code class="descname">TypeDecorator</code><span class="sig-paren">(</span><em>*args</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.sql.expression.SchemaEventTarget</span></code>, <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<p>Allows the creation of types which add additional functionality
to an existing type.</p>
<p>This method is preferred to direct subclassing of SQLAlchemy’s
built-in types as it ensures that all required functionality of
the underlying type is kept in place.</p>
<p>Typical usage:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlalchemy.types</span> <span class="k">as</span> <span class="nn">types</span>
<span class="k">class</span> <span class="nc">MyType</span><span class="p">(</span><span class="n">types</span><span class="o">.</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="sd">'''Prefixes Unicode values with "PREFIX:" on the way in and</span>
<span class="sd"> strips it off on the way out.</span>
<span class="sd"> '''</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">types</span><span class="o">.</span><span class="n">Unicode</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">return</span> <span class="s2">"PREFIX:"</span> <span class="o">+</span> <span class="n">value</span>
<span class="k">def</span> <span class="nf">process_result_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">return</span> <span class="n">value</span><span class="p">[</span><span class="mi">7</span><span class="p">:]</span>
<span class="k">def</span> <span class="nf">copy</span><span class="p">(</span><span class="bp">self</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">MyType</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">impl</span><span class="o">.</span><span class="n">length</span><span class="p">)</span></pre></div>
</div>
<p>The class-level “impl” attribute is required, and can reference any
TypeEngine class. Alternatively, the load_dialect_impl() method
can be used to provide different type classes based on the dialect
given; in this case, the “impl” variable can reference
<code class="docutils literal notranslate"><span class="pre">TypeEngine</span></code> as a placeholder.</p>
<p>Types that receive a Python type that isn’t similar to the ultimate type
used may want to define the <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.coerce_compared_value" title="sqlalchemy.types.TypeDecorator.coerce_compared_value"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.coerce_compared_value()</span></code></a>
method. This is used to give the expression system a hint when coercing
Python objects into bind parameters within expressions. Consider this
expression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">somecol</span> <span class="o">+</span> <span class="n">datetime</span><span class="o">.</span><span class="n">date</span><span class="p">(</span><span class="mi">2009</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">15</span><span class="p">)</span></pre></div>
</div>
<p>Above, if “somecol” is an <code class="docutils literal notranslate"><span class="pre">Integer</span></code> variant, it makes sense that
we’re doing date arithmetic, where above is usually interpreted
by databases as adding a number of days to the given date.
The expression system does the right thing by not attempting to
coerce the “date()” value into an integer-oriented bind parameter.</p>
<p>However, in the case of <code class="docutils literal notranslate"><span class="pre">TypeDecorator</span></code>, we are usually changing an
incoming Python type to something new - <code class="docutils literal notranslate"><span class="pre">TypeDecorator</span></code> by default will
“coerce” the non-typed side to be the same type as itself. Such as below,
we define an “epoch” type that stores a date value as an integer:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyEpochType</span><span class="p">(</span><span class="n">types</span><span class="o">.</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">types</span><span class="o">.</span><span class="n">Integer</span>
<span class="n">epoch</span> <span class="o">=</span> <span class="n">datetime</span><span class="o">.</span><span class="n">date</span><span class="p">(</span><span class="mi">1970</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">return</span> <span class="p">(</span><span class="n">value</span> <span class="o">-</span> <span class="bp">self</span><span class="o">.</span><span class="n">epoch</span><span class="p">)</span><span class="o">.</span><span class="n">days</span>
<span class="k">def</span> <span class="nf">process_result_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">epoch</span> <span class="o">+</span> <span class="n">timedelta</span><span class="p">(</span><span class="n">days</span><span class="o">=</span><span class="n">value</span><span class="p">)</span></pre></div>
</div>
<p>Our expression of <code class="docutils literal notranslate"><span class="pre">somecol</span> <span class="pre">+</span> <span class="pre">date</span></code> with the above type will coerce the
“date” on the right side to also be treated as <code class="docutils literal notranslate"><span class="pre">MyEpochType</span></code>.</p>
<p>This behavior can be overridden via the
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.coerce_compared_value" title="sqlalchemy.types.TypeDecorator.coerce_compared_value"><code class="xref py py-meth docutils literal notranslate"><span class="pre">coerce_compared_value()</span></code></a> method, which returns a type
that should be used for the value of the expression. Below we set it such
that an integer value will be treated as an <code class="docutils literal notranslate"><span class="pre">Integer</span></code>, and any other
value is assumed to be a date and will be treated as a <code class="docutils literal notranslate"><span class="pre">MyEpochType</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">coerce_compared_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">op</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="nb">int</span><span class="p">):</span>
<span class="k">return</span> <span class="n">Integer</span><span class="p">()</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">return</span> <span class="bp">self</span></pre></div>
</div>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>Note that the <strong>behavior of coerce_compared_value is not inherited
by default from that of the base type</strong>.
If the <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> is augmenting a
type that requires special logic for certain types of operators,
this method <strong>must</strong> be overridden. A key example is when decorating
the <a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.JSON</span></code></a> and <a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB" title="sqlalchemy.dialects.postgresql.JSONB"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.JSONB</span></code></a> types;
the default rules of <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.coerce_compared_value" title="sqlalchemy.types.TypeEngine.coerce_compared_value"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.coerce_compared_value()</span></code></a> should
be used in order to deal with operators like index operations:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyJsonType</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">JSON</span>
<span class="k">def</span> <span class="nf">coerce_compared_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">op</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">impl</span><span class="o">.</span><span class="n">coerce_compared_value</span><span class="p">(</span><span class="n">op</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span></pre></div>
</div>
<p>Without the above step, index operations such as <code class="docutils literal notranslate"><span class="pre">mycol['foo']</span></code>
will cause the index value <code class="docutils literal notranslate"><span class="pre">'foo'</span></code> to be JSON encoded.</p>
</div>
<dl class="class">
<dt id="sqlalchemy.types.TypeDecorator.Comparator">
<em class="property">class </em><code class="descname">Comparator</code><span class="sig-paren">(</span><em>expr</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Comparator</span></code></p>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.__eq__">
<code class="descname">__eq__</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.__eq__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__eq__" title="sqlalchemy.sql.operators.ColumnOperators.__eq__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__eq__()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">==</span></code> operator.</p>
<p>In a column context, produces the clause <code class="docutils literal notranslate"><span class="pre">a</span> <span class="pre">=</span> <span class="pre">b</span></code>.
If the target is <code class="docutils literal notranslate"><span class="pre">None</span></code>, produces <code class="docutils literal notranslate"><span class="pre">a</span> <span class="pre">IS</span> <span class="pre">NULL</span></code>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.__le__">
<code class="descname">__le__</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.__le__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__le__" title="sqlalchemy.sql.operators.ColumnOperators.__le__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__le__()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre"><=</span></code> operator.</p>
<p>In a column context, produces the clause <code class="docutils literal notranslate"><span class="pre">a</span> <span class="pre"><=</span> <span class="pre">b</span></code>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.__lt__">
<code class="descname">__lt__</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.__lt__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__lt__" title="sqlalchemy.sql.operators.ColumnOperators.__lt__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__lt__()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre"><</span></code> operator.</p>
<p>In a column context, produces the clause <code class="docutils literal notranslate"><span class="pre">a</span> <span class="pre"><</span> <span class="pre">b</span></code>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.__ne__">
<code class="descname">__ne__</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.__ne__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__ne__" title="sqlalchemy.sql.operators.ColumnOperators.__ne__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__ne__()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">!=</span></code> operator.</p>
<p>In a column context, produces the clause <code class="docutils literal notranslate"><span class="pre">a</span> <span class="pre">!=</span> <span class="pre">b</span></code>.
If the target is <code class="docutils literal notranslate"><span class="pre">None</span></code>, produces <code class="docutils literal notranslate"><span class="pre">a</span> <span class="pre">IS</span> <span class="pre">NOT</span> <span class="pre">NULL</span></code>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.all_">
<code class="descname">all_</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.all_" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.all_" title="sqlalchemy.sql.operators.ColumnOperators.all_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">all_()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.all_" title="sqlalchemy.sql.expression.all_"><code class="xref py py-func docutils literal notranslate"><span class="pre">all_()</span></code></a> clause against the
parent object.</p>
<p>This operator is only appropriate against a scalar subquery
object, or for some backends an column expression that is
against the ARRAY type, e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># postgresql '5 = ALL (somearray)'</span>
<span class="n">expr</span> <span class="o">=</span> <span class="mi">5</span> <span class="o">==</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">somearray</span><span class="o">.</span><span class="n">all_</span><span class="p">()</span>
<span class="c1"># mysql '5 = ALL (SELECT value FROM table)'</span>
<span class="n">expr</span> <span class="o">=</span> <span class="mi">5</span> <span class="o">==</span> <span class="n">select</span><span class="p">([</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">value</span><span class="p">])</span><span class="o">.</span><span class="n">as_scalar</span><span class="p">()</span><span class="o">.</span><span class="n">all_</span><span class="p">()</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.all_" title="sqlalchemy.sql.expression.all_"><code class="xref py py-func docutils literal notranslate"><span class="pre">all_()</span></code></a> - standalone version</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.any_" title="sqlalchemy.sql.expression.any_"><code class="xref py py-func docutils literal notranslate"><span class="pre">any_()</span></code></a> - ANY operator</p>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.any_">
<code class="descname">any_</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.any_" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.any_" title="sqlalchemy.sql.operators.ColumnOperators.any_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">any_()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.any_" title="sqlalchemy.sql.expression.any_"><code class="xref py py-func docutils literal notranslate"><span class="pre">any_()</span></code></a> clause against the
parent object.</p>
<p>This operator is only appropriate against a scalar subquery
object, or for some backends an column expression that is
against the ARRAY type, e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># postgresql '5 = ANY (somearray)'</span>
<span class="n">expr</span> <span class="o">=</span> <span class="mi">5</span> <span class="o">==</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">somearray</span><span class="o">.</span><span class="n">any_</span><span class="p">()</span>
<span class="c1"># mysql '5 = ANY (SELECT value FROM table)'</span>
<span class="n">expr</span> <span class="o">=</span> <span class="mi">5</span> <span class="o">==</span> <span class="n">select</span><span class="p">([</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">value</span><span class="p">])</span><span class="o">.</span><span class="n">as_scalar</span><span class="p">()</span><span class="o">.</span><span class="n">any_</span><span class="p">()</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.any_" title="sqlalchemy.sql.expression.any_"><code class="xref py py-func docutils literal notranslate"><span class="pre">any_()</span></code></a> - standalone version</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.all_" title="sqlalchemy.sql.expression.all_"><code class="xref py py-func docutils literal notranslate"><span class="pre">all_()</span></code></a> - ALL operator</p>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.asc">
<code class="descname">asc</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.asc" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.asc" title="sqlalchemy.sql.operators.ColumnOperators.asc"><code class="xref py py-meth docutils literal notranslate"><span class="pre">asc()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.asc" title="sqlalchemy.sql.expression.asc"><code class="xref py py-func docutils literal notranslate"><span class="pre">asc()</span></code></a> clause against the
parent object.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.between">
<code class="descname">between</code><span class="sig-paren">(</span><em>cleft</em>, <em>cright</em>, <em>symmetric=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.between" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.between" title="sqlalchemy.sql.operators.ColumnOperators.between"><code class="xref py py-meth docutils literal notranslate"><span class="pre">between()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.between" title="sqlalchemy.sql.expression.between"><code class="xref py py-func docutils literal notranslate"><span class="pre">between()</span></code></a> clause against
the parent object, given the lower and upper range.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.bool_op">
<code class="descname">bool_op</code><span class="sig-paren">(</span><em>opstring</em>, <em>precedence=0</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.bool_op" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.bool_op" title="sqlalchemy.sql.operators.Operators.bool_op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">bool_op()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators" title="sqlalchemy.sql.operators.Operators"><code class="xref py py-class docutils literal notranslate"><span class="pre">Operators</span></code></a></p>
</div>
<p>Return a custom boolean operator.</p>
<p>This method is shorthand for calling
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a> and passing the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op.params.is_comparison" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Operators.op.is_comparison</span></code></a>
flag with True.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.0b3.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.collate">
<code class="descname">collate</code><span class="sig-paren">(</span><em>collation</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.collate" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.collate" title="sqlalchemy.sql.operators.ColumnOperators.collate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">collate()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.collate" title="sqlalchemy.sql.expression.collate"><code class="xref py py-func docutils literal notranslate"><span class="pre">collate()</span></code></a> clause against
the parent object, given the collation string.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.collate" title="sqlalchemy.sql.expression.collate"><code class="xref py py-func docutils literal notranslate"><span class="pre">collate()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.concat">
<code class="descname">concat</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.concat" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.concat" title="sqlalchemy.sql.operators.ColumnOperators.concat"><code class="xref py py-meth docutils literal notranslate"><span class="pre">concat()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the ‘concat’ operator.</p>
<p>In a column context, produces the clause <code class="docutils literal notranslate"><span class="pre">a</span> <span class="pre">||</span> <span class="pre">b</span></code>,
or uses the <code class="docutils literal notranslate"><span class="pre">concat()</span></code> operator on MySQL.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.contains">
<code class="descname">contains</code><span class="sig-paren">(</span><em>other</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.contains" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-meth docutils literal notranslate"><span class="pre">contains()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the ‘contains’ operator.</p>
<p>Produces a LIKE expression that tests against a match for the middle
of a string value:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">column</span> <span class="n">LIKE</span> <span class="s1">'%'</span> <span class="o">||</span> <span class="o"><</span><span class="n">other</span><span class="o">></span> <span class="o">||</span> <span class="s1">'%'</span></pre></div>
</div>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">sometable</span><span class="p">])</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">column</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="s2">"foobar"</span><span class="p">))</span></pre></div>
</div>
<p>Since the operator uses <code class="docutils literal notranslate"><span class="pre">LIKE</span></code>, wildcard characters
<code class="docutils literal notranslate"><span class="pre">"%"</span></code> and <code class="docutils literal notranslate"><span class="pre">"_"</span></code> that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.contains.autoescape</span></code></a> flag
may be set to <code class="docutils literal notranslate"><span class="pre">True</span></code> to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains.params.escape" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.contains.escape</span></code></a> parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.contains.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.contains.params.other">¶</a> – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters <code class="docutils literal notranslate"><span class="pre">%</span></code> and <code class="docutils literal notranslate"><span class="pre">_</span></code> are not escaped by default unless
the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.contains.autoescape</span></code></a> flag is
set to True.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.contains.params.autoescape"></span><strong>autoescape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.contains.params.autoescape">¶</a> – <p>boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
<code class="docutils literal notranslate"><span class="pre">"%"</span></code>, <code class="docutils literal notranslate"><span class="pre">"_"</span></code> and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.</p>
<p>An expression such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="s2">"foo%bar"</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Will render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span> <span class="n">LIKE</span> <span class="s1">'%'</span> <span class="o">||</span> <span class="p">:</span><span class="n">param</span> <span class="o">||</span> <span class="s1">'%'</span> <span class="n">ESCAPE</span> <span class="s1">'/'</span></pre></div>
</div>
<p>With the value of :param as <code class="docutils literal notranslate"><span class="pre">"foo/%bar"</span></code>.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2.0: </span>The
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.contains.autoescape</span></code></a> parameter is
now a simple boolean rather than a character; the escape
character itself is also escaped, and defaults to a forwards
slash, which itself can be customized using the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains.params.escape" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.contains.escape</span></code></a> parameter.</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.contains.params.escape"></span><strong>escape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.contains.params.escape">¶</a> – <p>a character which when given will render with the
<code class="docutils literal notranslate"><span class="pre">ESCAPE</span></code> keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of <code class="docutils literal notranslate"><span class="pre">%</span></code> and <code class="docutils literal notranslate"><span class="pre">_</span></code> to allow them to act as themselves and not
wildcard characters.</p>
<p>An expression such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="s2">"foo/%bar"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"^"</span><span class="p">)</span></pre></div>
</div>
<p>Will render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span> <span class="n">LIKE</span> <span class="s1">'%'</span> <span class="o">||</span> <span class="p">:</span><span class="n">param</span> <span class="o">||</span> <span class="s1">'%'</span> <span class="n">ESCAPE</span> <span class="s1">'^'</span></pre></div>
</div>
<p>The parameter may also be combined with
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.contains.autoescape</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="s2">"foo%bar^bat"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"^"</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Where above, the given literal parameter will be converted to
<code class="docutils literal notranslate"><span class="pre">"foo^%bar^^bat"</span></code> before being passed to the database.</p>
</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.startswith()</span></code></a></p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.endswith()</span></code></a></p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.like()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.desc">
<code class="descname">desc</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.desc" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.desc" title="sqlalchemy.sql.operators.ColumnOperators.desc"><code class="xref py py-meth docutils literal notranslate"><span class="pre">desc()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.desc" title="sqlalchemy.sql.expression.desc"><code class="xref py py-func docutils literal notranslate"><span class="pre">desc()</span></code></a> clause against the
parent object.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.distinct">
<code class="descname">distinct</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.distinct" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.distinct" title="sqlalchemy.sql.operators.ColumnOperators.distinct"><code class="xref py py-meth docutils literal notranslate"><span class="pre">distinct()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.distinct" title="sqlalchemy.sql.expression.distinct"><code class="xref py py-func docutils literal notranslate"><span class="pre">distinct()</span></code></a> clause against the
parent object.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.endswith">
<code class="descname">endswith</code><span class="sig-paren">(</span><em>other</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.endswith" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">endswith()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the ‘endswith’ operator.</p>
<p>Produces a LIKE expression that tests against a match for the end
of a string value:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">column</span> <span class="n">LIKE</span> <span class="s1">'%'</span> <span class="o">||</span> <span class="o"><</span><span class="n">other</span><span class="o">></span></pre></div>
</div>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">sometable</span><span class="p">])</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">column</span><span class="o">.</span><span class="n">endswith</span><span class="p">(</span><span class="s2">"foobar"</span><span class="p">))</span></pre></div>
</div>
<p>Since the operator uses <code class="docutils literal notranslate"><span class="pre">LIKE</span></code>, wildcard characters
<code class="docutils literal notranslate"><span class="pre">"%"</span></code> and <code class="docutils literal notranslate"><span class="pre">"_"</span></code> that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.endswith.autoescape</span></code></a> flag
may be set to <code class="docutils literal notranslate"><span class="pre">True</span></code> to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith.params.escape" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.endswith.escape</span></code></a> parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.endswith.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.endswith.params.other">¶</a> – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters <code class="docutils literal notranslate"><span class="pre">%</span></code> and <code class="docutils literal notranslate"><span class="pre">_</span></code> are not escaped by default unless
the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.endswith.autoescape</span></code></a> flag is
set to True.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.endswith.params.autoescape"></span><strong>autoescape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.endswith.params.autoescape">¶</a> – <p>boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
<code class="docutils literal notranslate"><span class="pre">"%"</span></code>, <code class="docutils literal notranslate"><span class="pre">"_"</span></code> and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.</p>
<p>An expression such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">endswith</span><span class="p">(</span><span class="s2">"foo%bar"</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Will render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span> <span class="n">LIKE</span> <span class="s1">'%'</span> <span class="o">||</span> <span class="p">:</span><span class="n">param</span> <span class="n">ESCAPE</span> <span class="s1">'/'</span></pre></div>
</div>
<p>With the value of :param as <code class="docutils literal notranslate"><span class="pre">"foo/%bar"</span></code>.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2.0: </span>The
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.endswith.autoescape</span></code></a> parameter is
now a simple boolean rather than a character; the escape
character itself is also escaped, and defaults to a forwards
slash, which itself can be customized using the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith.params.escape" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.endswith.escape</span></code></a> parameter.</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.endswith.params.escape"></span><strong>escape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.endswith.params.escape">¶</a> – <p>a character which when given will render with the
<code class="docutils literal notranslate"><span class="pre">ESCAPE</span></code> keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of <code class="docutils literal notranslate"><span class="pre">%</span></code> and <code class="docutils literal notranslate"><span class="pre">_</span></code> to allow them to act as themselves and not
wildcard characters.</p>
<p>An expression such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">endswith</span><span class="p">(</span><span class="s2">"foo/%bar"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"^"</span><span class="p">)</span></pre></div>
</div>
<p>Will render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span> <span class="n">LIKE</span> <span class="s1">'%'</span> <span class="o">||</span> <span class="p">:</span><span class="n">param</span> <span class="n">ESCAPE</span> <span class="s1">'^'</span></pre></div>
</div>
<p>The parameter may also be combined with
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.endswith.autoescape</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">endswith</span><span class="p">(</span><span class="s2">"foo%bar^bat"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"^"</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Where above, the given literal parameter will be converted to
<code class="docutils literal notranslate"><span class="pre">"foo^%bar^^bat"</span></code> before being passed to the database.</p>
</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.startswith()</span></code></a></p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.contains()</span></code></a></p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.like()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.ilike">
<code class="descname">ilike</code><span class="sig-paren">(</span><em>other</em>, <em>escape=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.ilike" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.ilike" title="sqlalchemy.sql.operators.ColumnOperators.ilike"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ilike()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">ilike</span></code> operator, e.g. case insensitive LIKE.</p>
<p>In a column context, produces an expression either of the form:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">lower</span><span class="p">(</span><span class="n">a</span><span class="p">)</span> <span class="n">LIKE</span> <span class="n">lower</span><span class="p">(</span><span class="n">other</span><span class="p">)</span></pre></div>
</div>
<p>Or on backends that support the ILIKE operator:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">a</span> <span class="n">ILIKE</span> <span class="n">other</span></pre></div>
</div>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">sometable</span><span class="p">])</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">column</span><span class="o">.</span><span class="n">ilike</span><span class="p">(</span><span class="s2">"</span><span class="si">%f</span><span class="s2">oobar%"</span><span class="p">))</span></pre></div>
</div>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.ilike.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.ilike.params.other">¶</a> – expression to be compared</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.ilike.params.escape"></span><strong>escape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.ilike.params.escape">¶</a> – <p>optional escape character, renders the <code class="docutils literal notranslate"><span class="pre">ESCAPE</span></code>
keyword, e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">ilike</span><span class="p">(</span><span class="s2">"foo/%bar"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"/"</span><span class="p">)</span></pre></div>
</div>
</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.like()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.in_">
<code class="descname">in_</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.in_" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">in_()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">in</span></code> operator.</p>
<p>In a column context, produces the clause <code class="docutils literal notranslate"><span class="pre">column</span> <span class="pre">IN</span> <span class="pre"><other></span></code>.</p>
<p>The given parameter <code class="docutils literal notranslate"><span class="pre">other</span></code> may be:</p>
<ul>
<li><p>A list of literal values, e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">column</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">]))</span></pre></div>
</div>
<p>In this calling form, the list of items is converted to a set of
bound parameters the same length as the list given:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>WHERE COL IN (?, ?, ?)</pre></div>
</div>
</li>
<li><p>An empty list, e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">column</span><span class="o">.</span><span class="n">in_</span><span class="p">([]))</span></pre></div>
</div>
<p>In this calling form, the expression renders a “false” expression,
e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">WHERE</span> <span class="mi">1</span> <span class="o">!=</span> <span class="mi">1</span></pre></div>
</div>
<p>This “false” expression has historically had different behaviors
in older SQLAlchemy versions, see
<a class="reference internal" href="engines.html#sqlalchemy.create_engine.params.empty_in_strategy" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.empty_in_strategy</span></code></a> for behavioral options.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2: </span>simplified the behavior of “empty in”
expressions</p>
</div>
</li>
<li><p>A bound parameter, e.g. <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a>, may be used if it
includes the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam.params.expanding" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">bindparam.expanding</span></code></a> flag:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">column</span><span class="o">.</span><span class="n">in_</span><span class="p">(</span><span class="n">bindparam</span><span class="p">(</span><span class="s1">'value'</span><span class="p">,</span> <span class="n">expanding</span><span class="o">=</span><span class="kc">True</span><span class="p">)))</span></pre></div>
</div>
<p>In this calling form, the expression renders a special non-SQL
placeholder expression that looks like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">WHERE</span> <span class="n">COL</span> <span class="n">IN</span> <span class="p">([</span><span class="n">EXPANDING_value</span><span class="p">])</span></pre></div>
</div>
<p>This placeholder expression is intercepted at statement execution
time to be converted into the variable number of bound parameter
form illustrated earlier. If the statement were executed as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">,</span> <span class="p">{</span><span class="s2">"value"</span><span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">]})</span></pre></div>
</div>
<p>The database would be passed a bound parameter for each value:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>WHERE COL IN (?, ?, ?)</pre></div>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2: </span>added “expanding” bound parameters</p>
</div>
<p>The “expanding” feature in version 1.2 of SQLAlchemy does not
support passing an empty list as a parameter value; however,
version 1.3 does support this.</p>
</li>
<li><p>a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> construct, which is usually a correlated
scalar select:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
<span class="n">column</span><span class="o">.</span><span class="n">in_</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">othertable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span><span class="p">])</span><span class="o">.</span>
<span class="n">where</span><span class="p">(</span><span class="n">table</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="n">othertable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>In this calling form, <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.in_()</span></code></a> renders as given:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">WHERE</span> <span class="n">COL</span> <span class="n">IN</span> <span class="p">(</span><span class="n">SELECT</span> <span class="n">othertable</span><span class="o">.</span><span class="n">y</span>
<span class="n">FROM</span> <span class="n">othertable</span> <span class="n">WHERE</span> <span class="n">othertable</span><span class="o">.</span><span class="n">x</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">x</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.in_.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.in_.params.other">¶</a> – a list of literals, a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> construct,
or a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> construct that includes the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.bindparam.params.expanding" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">bindparam.expanding</span></code></a> flag set to True.</p>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.is_">
<code class="descname">is_</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.is_" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.is_" title="sqlalchemy.sql.operators.ColumnOperators.is_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">is_()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">IS</span></code> operator.</p>
<p>Normally, <code class="docutils literal notranslate"><span class="pre">IS</span></code> is generated automatically when comparing to a
value of <code class="docutils literal notranslate"><span class="pre">None</span></code>, which resolves to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>. However, explicit
usage of <code class="docutils literal notranslate"><span class="pre">IS</span></code> may be desirable if comparing to boolean values
on certain platforms.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.isnot" title="sqlalchemy.sql.operators.ColumnOperators.isnot"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.isnot()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.is_distinct_from">
<code class="descname">is_distinct_from</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.is_distinct_from" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.is_distinct_from" title="sqlalchemy.sql.operators.ColumnOperators.is_distinct_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">is_distinct_from()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">IS</span> <span class="pre">DISTINCT</span> <span class="pre">FROM</span></code> operator.</p>
<p>Renders “a IS DISTINCT FROM b” on most platforms;
on some such as SQLite may render “a IS NOT b”.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.isnot">
<code class="descname">isnot</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.isnot" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.isnot" title="sqlalchemy.sql.operators.ColumnOperators.isnot"><code class="xref py py-meth docutils literal notranslate"><span class="pre">isnot()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">IS</span> <span class="pre">NOT</span></code> operator.</p>
<p>Normally, <code class="docutils literal notranslate"><span class="pre">IS</span> <span class="pre">NOT</span></code> is generated automatically when comparing to a
value of <code class="docutils literal notranslate"><span class="pre">None</span></code>, which resolves to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>. However, explicit
usage of <code class="docutils literal notranslate"><span class="pre">IS</span> <span class="pre">NOT</span></code> may be desirable if comparing to boolean values
on certain platforms.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.is_" title="sqlalchemy.sql.operators.ColumnOperators.is_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.is_()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.isnot_distinct_from">
<code class="descname">isnot_distinct_from</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.isnot_distinct_from" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.isnot_distinct_from" title="sqlalchemy.sql.operators.ColumnOperators.isnot_distinct_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">isnot_distinct_from()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">IS</span> <span class="pre">NOT</span> <span class="pre">DISTINCT</span> <span class="pre">FROM</span></code> operator.</p>
<p>Renders “a IS NOT DISTINCT FROM b” on most platforms;
on some such as SQLite may render “a IS b”.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.like">
<code class="descname">like</code><span class="sig-paren">(</span><em>other</em>, <em>escape=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.like" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">like()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">like</span></code> operator.</p>
<p>In a column context, produces the expression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">a</span> <span class="n">LIKE</span> <span class="n">other</span></pre></div>
</div>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">sometable</span><span class="p">])</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">column</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s2">"</span><span class="si">%f</span><span class="s2">oobar%"</span><span class="p">))</span></pre></div>
</div>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.like.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.like.params.other">¶</a> – expression to be compared</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.like.params.escape"></span><strong>escape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.like.params.escape">¶</a> – <p>optional escape character, renders the <code class="docutils literal notranslate"><span class="pre">ESCAPE</span></code>
keyword, e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s2">"foo/%bar"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"/"</span><span class="p">)</span></pre></div>
</div>
</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.ilike" title="sqlalchemy.sql.operators.ColumnOperators.ilike"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.ilike()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.match">
<code class="descname">match</code><span class="sig-paren">(</span><em>other</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.match" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.match" title="sqlalchemy.sql.operators.ColumnOperators.match"><code class="xref py py-meth docutils literal notranslate"><span class="pre">match()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implements a database-specific ‘match’ operator.</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.match" title="sqlalchemy.sql.operators.ColumnOperators.match"><code class="xref py py-meth docutils literal notranslate"><span class="pre">match()</span></code></a> attempts to resolve to
a MATCH-like function or operator provided by the backend.
Examples include:</p>
<ul class="simple">
<li><p>PostgreSQL - renders <code class="docutils literal notranslate"><span class="pre">x</span> <span class="pre">@@</span> <span class="pre">to_tsquery(y)</span></code></p></li>
<li><p>MySQL - renders <code class="docutils literal notranslate"><span class="pre">MATCH</span> <span class="pre">(x)</span> <span class="pre">AGAINST</span> <span class="pre">(y</span> <span class="pre">IN</span> <span class="pre">BOOLEAN</span> <span class="pre">MODE)</span></code></p></li>
<li><p>Oracle - renders <code class="docutils literal notranslate"><span class="pre">CONTAINS(x,</span> <span class="pre">y)</span></code></p></li>
<li><p>other backends may provide special implementations.</p></li>
<li><p>Backends without any special implementation will emit
the operator as “MATCH”. This is compatible with SQLite, for
example.</p></li>
</ul>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.notilike">
<code class="descname">notilike</code><span class="sig-paren">(</span><em>other</em>, <em>escape=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.notilike" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.notilike" title="sqlalchemy.sql.operators.ColumnOperators.notilike"><code class="xref py py-meth docutils literal notranslate"><span class="pre">notilike()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>implement the <code class="docutils literal notranslate"><span class="pre">NOT</span> <span class="pre">ILIKE</span></code> operator.</p>
<p>This is equivalent to using negation with
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.ilike" title="sqlalchemy.sql.operators.ColumnOperators.ilike"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.ilike()</span></code></a>, i.e. <code class="docutils literal notranslate"><span class="pre">~x.ilike(y)</span></code>.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.ilike" title="sqlalchemy.sql.operators.ColumnOperators.ilike"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.ilike()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.notin_">
<code class="descname">notin_</code><span class="sig-paren">(</span><em>other</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.notin_" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.notin_" title="sqlalchemy.sql.operators.ColumnOperators.notin_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">notin_()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>implement the <code class="docutils literal notranslate"><span class="pre">NOT</span> <span class="pre">IN</span></code> operator.</p>
<p>This is equivalent to using negation with
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.in_()</span></code></a>, i.e. <code class="docutils literal notranslate"><span class="pre">~x.in_(y)</span></code>.</p>
<p>In the case that <code class="docutils literal notranslate"><span class="pre">other</span></code> is an empty sequence, the compiler
produces an “empty not in” expression. This defaults to the
expression “1 = 1” to produce true in all cases. The
<a class="reference internal" href="engines.html#sqlalchemy.create_engine.params.empty_in_strategy" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.empty_in_strategy</span></code></a> may be used to
alter this behavior.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2: </span>The <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.in_()</span></code></a> and
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.notin_" title="sqlalchemy.sql.operators.ColumnOperators.notin_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.notin_()</span></code></a> operators
now produce a “static” expression for an empty IN sequence
by default.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.in_()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.notlike">
<code class="descname">notlike</code><span class="sig-paren">(</span><em>other</em>, <em>escape=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.notlike" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.notlike" title="sqlalchemy.sql.operators.ColumnOperators.notlike"><code class="xref py py-meth docutils literal notranslate"><span class="pre">notlike()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>implement the <code class="docutils literal notranslate"><span class="pre">NOT</span> <span class="pre">LIKE</span></code> operator.</p>
<p>This is equivalent to using negation with
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.like()</span></code></a>, i.e. <code class="docutils literal notranslate"><span class="pre">~x.like(y)</span></code>.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.like()</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.nullsfirst">
<code class="descname">nullsfirst</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.nullsfirst" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.nullsfirst" title="sqlalchemy.sql.operators.ColumnOperators.nullsfirst"><code class="xref py py-meth docutils literal notranslate"><span class="pre">nullsfirst()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.nullsfirst" title="sqlalchemy.sql.expression.nullsfirst"><code class="xref py py-func docutils literal notranslate"><span class="pre">nullsfirst()</span></code></a> clause against the
parent object.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.nullslast">
<code class="descname">nullslast</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.nullslast" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.nullslast" title="sqlalchemy.sql.operators.ColumnOperators.nullslast"><code class="xref py py-meth docutils literal notranslate"><span class="pre">nullslast()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Produce a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.nullslast" title="sqlalchemy.sql.expression.nullslast"><code class="xref py py-func docutils literal notranslate"><span class="pre">nullslast()</span></code></a> clause against the
parent object.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.op">
<code class="descname">op</code><span class="sig-paren">(</span><em>opstring</em>, <em>precedence=0</em>, <em>is_comparison=False</em>, <em>return_type=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.op" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">op()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators" title="sqlalchemy.sql.operators.Operators"><code class="xref py py-class docutils literal notranslate"><span class="pre">Operators</span></code></a></p>
</div>
<p>produce a generic operator function.</p>
<p>e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s2">"*"</span><span class="p">)(</span><span class="mi">5</span><span class="p">)</span></pre></div>
</div>
<p>produces:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span> <span class="o">*</span> <span class="mi">5</span></pre></div>
</div>
<p>This function can also be used to make bitwise operators explicit. For
example:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'&'</span><span class="p">)(</span><span class="mh">0xff</span><span class="p">)</span></pre></div>
</div>
<p>is a bitwise AND of the value in <code class="docutils literal notranslate"><span class="pre">somecolumn</span></code>.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.op.params.operator"></span><strong>operator</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.op.params.operator">¶</a> – a string which will be output as the infix operator
between this element and the expression passed to the
generated function.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.op.params.precedence"></span><strong>precedence</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.op.params.precedence">¶</a> – precedence to apply to the operator, when
parenthesizing expressions. A lower number will cause the expression
to be parenthesized when applied against another operator with
higher precedence. The default value of <code class="docutils literal notranslate"><span class="pre">0</span></code> is lower than all
operators except for the comma (<code class="docutils literal notranslate"><span class="pre">,</span></code>) and <code class="docutils literal notranslate"><span class="pre">AS</span></code> operators.
A value of 100 will be higher or equal to all operators, and -100
will be lower than or equal to all operators.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.op.params.is_comparison"></span><strong>is_comparison</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.op.params.is_comparison">¶</a> – <p>if True, the operator will be considered as a
“comparison” operator, that is which evaluates to a boolean
true/false value, like <code class="docutils literal notranslate"><span class="pre">==</span></code>, <code class="docutils literal notranslate"><span class="pre">></span></code>, etc. This flag should be set
so that ORM relationships can establish that the operator is a
comparison operator when used in a custom join condition.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.2: </span>- added the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op.params.is_comparison" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Operators.op.is_comparison</span></code></a> flag.</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.op.params.return_type"></span><strong>return_type</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.op.params.return_type">¶</a> – <p>a <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> class or object that will
force the return type of an expression produced by this operator
to be of that type. By default, operators that specify
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op.params.is_comparison" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Operators.op.is_comparison</span></code></a> will resolve to
<a class="reference internal" href="type_basics.html#sqlalchemy.types.Boolean" title="sqlalchemy.types.Boolean"><code class="xref py py-class docutils literal notranslate"><span class="pre">Boolean</span></code></a>, and those that do not will be of the same
type as the left-hand operand.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.0b3: </span>- added the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op.params.return_type" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Operators.op.return_type</span></code></a> argument.</p>
</div>
</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#types-operators"><span class="std std-ref">Redefining and Creating New Operators</span></a></p>
<p><a class="reference internal" href="../orm/join_conditions.html#relationship-custom-operator"><span class="std std-ref">Using custom operators in join conditions</span></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.operate">
<code class="descname">operate</code><span class="sig-paren">(</span><em>op</em>, <em>*other</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.operate" title="Permalink to this definition">¶</a></dt>
<dd><p>Operate on an argument.</p>
<p>This is the lowest level of operation, raises
<code class="xref py py-class docutils literal notranslate"><span class="pre">NotImplementedError</span></code> by default.</p>
<p>Overriding this on a subclass can allow common
behavior to be applied to all operations.
For example, overriding <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a>
to apply <code class="docutils literal notranslate"><span class="pre">func.lower()</span></code> to the left and right
side:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyComparator</span><span class="p">(</span><span class="n">ColumnOperators</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">operate</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">op</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="n">op</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">lower</span><span class="p">(</span><span class="bp">self</span><span class="p">),</span> <span class="n">func</span><span class="o">.</span><span class="n">lower</span><span class="p">(</span><span class="n">other</span><span class="p">))</span></pre></div>
</div>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.operate.params.op"></span><strong>op</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.operate.params.op">¶</a> – Operator callable.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.operate.params.*other"></span><strong>*other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.operate.params.*other">¶</a> – the ‘other’ side of the operation. Will
be a single scalar for most operations.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.operate.params.**kwargs"></span><strong>**kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.operate.params.**kwargs">¶</a> – modifiers. These may be passed by special
operators such as <code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.contains()</span></code>.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.reverse_operate">
<code class="descname">reverse_operate</code><span class="sig-paren">(</span><em>op</em>, <em>other</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.reverse_operate" title="Permalink to this definition">¶</a></dt>
<dd><p>Reverse operate on an argument.</p>
<p>Usage is the same as <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.operate" title="sqlalchemy.types.TypeDecorator.Comparator.operate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">operate()</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.Comparator.startswith">
<code class="descname">startswith</code><span class="sig-paren">(</span><em>other</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.Comparator.startswith" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">startswith()</span></code></a> <em>method of</em> <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators" title="sqlalchemy.sql.operators.ColumnOperators"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnOperators</span></code></a></p>
</div>
<p>Implement the <code class="docutils literal notranslate"><span class="pre">startswith</span></code> operator.</p>
<p>Produces a LIKE expression that tests against a match for the start
of a string value:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">column</span> <span class="n">LIKE</span> <span class="o"><</span><span class="n">other</span><span class="o">></span> <span class="o">||</span> <span class="s1">'%'</span></pre></div>
</div>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">sometable</span><span class="p">])</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">column</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s2">"foobar"</span><span class="p">))</span></pre></div>
</div>
<p>Since the operator uses <code class="docutils literal notranslate"><span class="pre">LIKE</span></code>, wildcard characters
<code class="docutils literal notranslate"><span class="pre">"%"</span></code> and <code class="docutils literal notranslate"><span class="pre">"_"</span></code> that are present inside the <other> expression
will behave like wildcards as well. For literal string
values, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.startswith.autoescape</span></code></a> flag
may be set to <code class="docutils literal notranslate"><span class="pre">True</span></code> to apply escaping to occurrences of these
characters within the string value so that they match as themselves
and not as wildcard characters. Alternatively, the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith.params.escape" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.startswith.escape</span></code></a> parameter will establish
a given character as an escape character which can be of use when
the target expression is not a literal string.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.startswith.params.other"></span><strong>other</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.startswith.params.other">¶</a> – expression to be compared. This is usually a plain
string value, but can also be an arbitrary SQL expression. LIKE
wildcard characters <code class="docutils literal notranslate"><span class="pre">%</span></code> and <code class="docutils literal notranslate"><span class="pre">_</span></code> are not escaped by default unless
the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.startswith.autoescape</span></code></a> flag is
set to True.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.startswith.params.autoescape"></span><strong>autoescape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.startswith.params.autoescape">¶</a> – <p>boolean; when True, establishes an escape character
within the LIKE expression, then applies it to all occurrences of
<code class="docutils literal notranslate"><span class="pre">"%"</span></code>, <code class="docutils literal notranslate"><span class="pre">"_"</span></code> and the escape character itself within the
comparison value, which is assumed to be a literal string and not a
SQL expression.</p>
<p>An expression such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s2">"foo%bar"</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Will render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span> <span class="n">LIKE</span> <span class="p">:</span><span class="n">param</span> <span class="o">||</span> <span class="s1">'%'</span> <span class="n">ESCAPE</span> <span class="s1">'/'</span></pre></div>
</div>
<p>With the value of :param as <code class="docutils literal notranslate"><span class="pre">"foo/%bar"</span></code>.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2.0: </span>The
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.startswith.autoescape</span></code></a> parameter is
now a simple boolean rather than a character; the escape
character itself is also escaped, and defaults to a forwards
slash, which itself can be customized using the
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith.params.escape" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.startswith.escape</span></code></a> parameter.</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.Comparator.startswith.params.escape"></span><strong>escape</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.Comparator.startswith.params.escape">¶</a> – <p>a character which when given will render with the
<code class="docutils literal notranslate"><span class="pre">ESCAPE</span></code> keyword to establish that character as the escape
character. This character can then be placed preceding occurrences
of <code class="docutils literal notranslate"><span class="pre">%</span></code> and <code class="docutils literal notranslate"><span class="pre">_</span></code> to allow them to act as themselves and not
wildcard characters.</p>
<p>An expression such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s2">"foo/%bar"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"^"</span><span class="p">)</span></pre></div>
</div>
<p>Will render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span> <span class="n">LIKE</span> <span class="p">:</span><span class="n">param</span> <span class="o">||</span> <span class="s1">'%'</span> <span class="n">ESCAPE</span> <span class="s1">'^'</span></pre></div>
</div>
<p>The parameter may also be combined with
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith.params.autoescape" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ColumnOperators.startswith.autoescape</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">somecolumn</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s2">"foo%bar^bat"</span><span class="p">,</span> <span class="n">escape</span><span class="o">=</span><span class="s2">"^"</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Where above, the given literal parameter will be converted to
<code class="docutils literal notranslate"><span class="pre">"foo^%bar^^bat"</span></code> before being passed to the database.</p>
</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.endswith()</span></code></a></p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.contains()</span></code></a></p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.like()</span></code></a></p>
</div>
</dd></dl>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.TypeDecorator.__eq__">
<code class="descname">__eq__</code><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.__eq__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__eq__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Return self==value.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>*args</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>.</p>
<p>Arguments sent here are passed to the constructor
of the class assigned to the <code class="docutils literal notranslate"><span class="pre">impl</span></code> class level attribute,
assuming the <code class="docutils literal notranslate"><span class="pre">impl</span></code> is a callable, and the resulting
object is assigned to the <code class="docutils literal notranslate"><span class="pre">self.impl</span></code> instance attribute
(thus overriding the class attribute of the same name).</p>
<p>If the class level <code class="docutils literal notranslate"><span class="pre">impl</span></code> is not a callable (the unusual case),
it will be assigned to the same instance attribute ‘as-is’,
ignoring those arguments passed to the constructor.</p>
<p>Subclasses can override this to customize the generation
of <code class="docutils literal notranslate"><span class="pre">self.impl</span></code> entirely.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.TypeDecorator.__le__">
<code class="descname">__le__</code><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.__le__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__le__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Return self<=value.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.TypeDecorator.__lt__">
<code class="descname">__lt__</code><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.__lt__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__lt__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Return self<value.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.TypeDecorator.__ne__">
<code class="descname">__ne__</code><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.__ne__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__ne__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Return self!=value.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.adapt">
<code class="descname">adapt</code><span class="sig-paren">(</span><em>cls</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.adapt" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.adapt" title="sqlalchemy.types.TypeEngine.adapt"><code class="xref py py-meth docutils literal notranslate"><span class="pre">adapt()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Produce an “adapted” form of this type, given an “impl” class
to work with.</p>
<p>This method is used internally to associate generic
types with “implementation” types that are specific to a particular
dialect.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.bind_expression">
<code class="descname">bind_expression</code><span class="sig-paren">(</span><em>bindvalue</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.bind_expression" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.bind_expression" title="sqlalchemy.types.TypeEngine.bind_expression"><code class="xref py py-meth docutils literal notranslate"><span class="pre">bind_expression()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>“Given a bind value (i.e. a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.BindParameter" title="sqlalchemy.sql.expression.BindParameter"><code class="xref py py-class docutils literal notranslate"><span class="pre">BindParameter</span></code></a> instance),
return a SQL expression in its place.</p>
<p>This is typically a SQL function that wraps the existing bound
parameter within the statement. It is used for special data types
that require literals being wrapped in some special database function
in order to coerce an application-level value into a database-specific
format. It is the SQL analogue of the
<a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.bind_processor" title="sqlalchemy.types.TypeEngine.bind_processor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.bind_processor()</span></code></a> method.</p>
<p>The method is evaluated at statement compile time, as opposed
to statement construction time.</p>
<p>Note that this method, when implemented, should always return
the exact same structure, without any conditional logic, as it
may be used in an executemany() call against an arbitrary number
of bound parameter sets.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#types-sql-value-processing"><span class="std std-ref">Applying SQL-level Bind/Result Processing</span></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.bind_processor">
<code class="descname">bind_processor</code><span class="sig-paren">(</span><em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.bind_processor" title="Permalink to this definition">¶</a></dt>
<dd><p>Provide a bound value processing function for the
given <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a>.</p>
<p>This is the method that fulfills the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a>
contract for bound value conversion. <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>
will wrap a user-defined implementation of
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_bind_param" title="sqlalchemy.types.TypeDecorator.process_bind_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">process_bind_param()</span></code></a> here.</p>
<p>User-defined code can override this method directly,
though its likely best to use <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_bind_param" title="sqlalchemy.types.TypeDecorator.process_bind_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">process_bind_param()</span></code></a> so that
the processing provided by <code class="docutils literal notranslate"><span class="pre">self.impl</span></code> is maintained.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.types.TypeDecorator.bind_processor.params.dialect"></span><strong>dialect</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.bind_processor.params.dialect">¶</a> – Dialect instance in use.</p>
</dd>
</dl>
<p>This method is the reverse counterpart to the
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.result_processor" title="sqlalchemy.types.TypeDecorator.result_processor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">result_processor()</span></code></a> method of this class.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.coerce_compared_value">
<code class="descname">coerce_compared_value</code><span class="sig-paren">(</span><em>op</em>, <em>value</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.coerce_compared_value" title="Permalink to this definition">¶</a></dt>
<dd><p>Suggest a type for a ‘coerced’ Python value in an expression.</p>
<p>By default, returns self. This method is called by
the expression system when an object using this type is
on the left or right side of an expression against a plain Python
object which does not yet have a SQLAlchemy type assigned:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">expr</span> <span class="o">=</span> <span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">somecolumn</span> <span class="o">+</span> <span class="mi">35</span></pre></div>
</div>
<p>Where above, if <code class="docutils literal notranslate"><span class="pre">somecolumn</span></code> uses this type, this method will
be called with the value <code class="docutils literal notranslate"><span class="pre">operator.add</span></code>
and <code class="docutils literal notranslate"><span class="pre">35</span></code>. The return value is whatever SQLAlchemy type should
be used for <code class="docutils literal notranslate"><span class="pre">35</span></code> for this particular operation.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.TypeDecorator.coerce_to_is_types">
<code class="descname">coerce_to_is_types</code><em class="property"> = (<class 'NoneType'>,)</em><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.coerce_to_is_types" title="Permalink to this definition">¶</a></dt>
<dd><p>Specify those Python types which should be coerced at the expression
level to “IS <constant>” when compared using <code class="docutils literal notranslate"><span class="pre">==</span></code> (and same for
<code class="docutils literal notranslate"><span class="pre">IS</span> <span class="pre">NOT</span></code> in conjunction with <code class="docutils literal notranslate"><span class="pre">!=</span></code>.</p>
<p>For most SQLAlchemy types, this includes <code class="docutils literal notranslate"><span class="pre">NoneType</span></code>, as well as
<code class="docutils literal notranslate"><span class="pre">bool</span></code>.</p>
<p><a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> modifies this list to only include <code class="docutils literal notranslate"><span class="pre">NoneType</span></code>,
as typedecorator implementations that deal with boolean types are common.</p>
<p>Custom <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> classes can override this attribute to
return an empty tuple, in which case no values will be coerced to
constants.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.column_expression">
<code class="descname">column_expression</code><span class="sig-paren">(</span><em>colexpr</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.column_expression" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.column_expression" title="sqlalchemy.types.TypeEngine.column_expression"><code class="xref py py-meth docutils literal notranslate"><span class="pre">column_expression()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Given a SELECT column expression, return a wrapping SQL expression.</p>
<p>This is typically a SQL function that wraps a column expression
as rendered in the columns clause of a SELECT statement.
It is used for special data types that require
columns to be wrapped in some special database function in order
to coerce the value before being sent back to the application.
It is the SQL analogue of the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.result_processor" title="sqlalchemy.types.TypeEngine.result_processor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.result_processor()</span></code></a>
method.</p>
<p>The method is evaluated at statement compile time, as opposed
to statement construction time.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#types-sql-value-processing"><span class="std std-ref">Applying SQL-level Bind/Result Processing</span></a></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.TypeDecorator.comparator_factory">
<code class="descname">comparator_factory</code><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>Base class for custom comparison operations defined at the
type level. See <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.comparator_factory" title="sqlalchemy.types.TypeEngine.comparator_factory"><code class="xref py py-attr docutils literal notranslate"><span class="pre">TypeEngine.comparator_factory</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.compare_against_backend">
<code class="descname">compare_against_backend</code><span class="sig-paren">(</span><em>dialect</em>, <em>conn_type</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.compare_against_backend" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.compare_against_backend" title="sqlalchemy.types.TypeEngine.compare_against_backend"><code class="xref py py-meth docutils literal notranslate"><span class="pre">compare_against_backend()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Compare this type against the given backend type.</p>
<p>This function is currently not implemented for SQLAlchemy
types, and for all built in types will return <code class="docutils literal notranslate"><span class="pre">None</span></code>. However,
it can be implemented by a user-defined type
where it can be consumed by schema comparison tools such as
Alembic autogenerate.</p>
<p>A future release of SQLAlchemy will potentially implement this method
for builtin types as well.</p>
<p>The function should return True if this type is equivalent to the
given type; the type is typically reflected from the database
so should be database specific. The dialect in use is also
passed. It can also return False to assert that the type is
not equivalent.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.compare_against_backend.params.dialect"></span><strong>dialect</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.compare_against_backend.params.dialect">¶</a> – a <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a> that is involved in the comparison.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.compare_against_backend.params.conn_type"></span><strong>conn_type</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.compare_against_backend.params.conn_type">¶</a> – the type object reflected from the backend.</p></li>
</ul>
</dd>
</dl>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.3.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.compare_values">
<code class="descname">compare_values</code><span class="sig-paren">(</span><em>x</em>, <em>y</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.compare_values" title="Permalink to this definition">¶</a></dt>
<dd><p>Given two values, compare them for equality.</p>
<p>By default this calls upon <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.compare_values" title="sqlalchemy.types.TypeEngine.compare_values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.compare_values()</span></code></a>
of the underlying “impl”, which in turn usually
uses the Python equals operator <code class="docutils literal notranslate"><span class="pre">==</span></code>.</p>
<p>This function is used by the ORM to compare
an original-loaded value with an intercepted
“changed” value, to determine if a net change
has occurred.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.compile">
<code class="descname">compile</code><span class="sig-paren">(</span><em>dialect=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.compile" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.compile" title="sqlalchemy.types.TypeEngine.compile"><code class="xref py py-meth docutils literal notranslate"><span class="pre">compile()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Produce a string-compiled form of this <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a>.</p>
<p>When called with no arguments, uses a “default” dialect
to produce a string result.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.types.TypeDecorator.compile.params.dialect"></span><strong>dialect</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.compile.params.dialect">¶</a> – a <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a> instance.</p>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.copy">
<code class="descname">copy</code><span class="sig-paren">(</span><em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.copy" title="Permalink to this definition">¶</a></dt>
<dd><p>Produce a copy of this <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> instance.</p>
<p>This is a shallow copy and is provided to fulfill part of
the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> contract. It usually does not
need to be overridden unless the user-defined <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>
has local state that should be deep-copied.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.dialect_impl">
<code class="descname">dialect_impl</code><span class="sig-paren">(</span><em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.dialect_impl" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.dialect_impl" title="sqlalchemy.types.TypeEngine.dialect_impl"><code class="xref py py-meth docutils literal notranslate"><span class="pre">dialect_impl()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Return a dialect-specific implementation for this
<a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.evaluates_none">
<code class="descname">evaluates_none</code><span class="sig-paren">(</span><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.evaluates_none" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.evaluates_none" title="sqlalchemy.types.TypeEngine.evaluates_none"><code class="xref py py-meth docutils literal notranslate"><span class="pre">evaluates_none()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Return a copy of this type which has the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.should_evaluate_none" title="sqlalchemy.types.TypeEngine.should_evaluate_none"><code class="xref py py-attr docutils literal notranslate"><span class="pre">should_evaluate_none</span></code></a>
flag set to True.</p>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s1">'some_table'</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="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span><span class="o">.</span><span class="n">evaluates_none</span><span class="p">(),</span>
<span class="n">nullable</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span>
<span class="n">server_default</span><span class="o">=</span><span class="s1">'no value'</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>The ORM uses this flag to indicate that a positive value of <code class="docutils literal notranslate"><span class="pre">None</span></code>
is passed to the column in an INSERT statement, rather than omitting
the column from the INSERT statement which has the effect of firing
off column-level defaults. It also allows for types which have
special behavior associated with the Python None value to indicate
that the value doesn’t necessarily translate into SQL NULL; a
prime example of this is a JSON type which may wish to persist the
JSON value <code class="docutils literal notranslate"><span class="pre">'null'</span></code>.</p>
<p>In all cases, the actual NULL SQL value can be always be
persisted in any column by using
the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.null" title="sqlalchemy.sql.expression.null"><code class="xref py py-obj docutils literal notranslate"><span class="pre">null</span></code></a> SQL construct in an INSERT statement
or associated with an ORM-mapped attribute.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The “evaluates none” flag does <strong>not</strong> apply to a value
of <code class="docutils literal notranslate"><span class="pre">None</span></code> passed to <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column.params.default" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.default</span></code></a> or
<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column.params.server_default" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.server_default</span></code></a>; in these cases, <code class="docutils literal notranslate"><span class="pre">None</span></code>
still means “no default”.</p>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/persistence_techniques.html#session-forcing-null"><span class="std std-ref">Forcing NULL on a column with a default</span></a> - in the ORM documentation</p>
<p><a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON.params.none_as_null" title="sqlalchemy.dialects.postgresql.JSON"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">postgresql.JSON.none_as_null</span></code></a> - PostgreSQL JSON
interaction with this flag.</p>
<p><a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.should_evaluate_none" title="sqlalchemy.types.TypeEngine.should_evaluate_none"><code class="xref py py-attr docutils literal notranslate"><span class="pre">TypeEngine.should_evaluate_none</span></code></a> - class-level flag</p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.get_dbapi_type">
<code class="descname">get_dbapi_type</code><span class="sig-paren">(</span><em>dbapi</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.get_dbapi_type" title="Permalink to this definition">¶</a></dt>
<dd><p>Return the DBAPI type object represented by this
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>.</p>
<p>By default this calls upon <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.get_dbapi_type" title="sqlalchemy.types.TypeEngine.get_dbapi_type"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.get_dbapi_type()</span></code></a> of the
underlying “impl”.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.literal_processor">
<code class="descname">literal_processor</code><span class="sig-paren">(</span><em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.literal_processor" title="Permalink to this definition">¶</a></dt>
<dd><p>Provide a literal processing function for the given
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a>.</p>
<p>Subclasses here will typically override
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_literal_param" title="sqlalchemy.types.TypeDecorator.process_literal_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_literal_param()</span></code></a> instead of this method
directly.</p>
<p>By default, this method makes use of
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_bind_param" title="sqlalchemy.types.TypeDecorator.process_bind_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_bind_param()</span></code></a> if that method is
implemented, where <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_literal_param" title="sqlalchemy.types.TypeDecorator.process_literal_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_literal_param()</span></code></a> is
not. The rationale here is that <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> typically
deals with Python conversions of data that are above the layer of
database presentation. With the value converted by
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_bind_param" title="sqlalchemy.types.TypeDecorator.process_bind_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_bind_param()</span></code></a>, the underlying type will
then handle whether it needs to be presented to the DBAPI as a bound
parameter or to the database as an inline SQL value.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.load_dialect_impl">
<code class="descname">load_dialect_impl</code><span class="sig-paren">(</span><em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.load_dialect_impl" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> object corresponding to a dialect.</p>
<p>This is an end-user override hook that can be used to provide
differing types depending on the given dialect. It is used
by the <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> implementation of <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.type_engine" title="sqlalchemy.types.TypeDecorator.type_engine"><code class="xref py py-meth docutils literal notranslate"><span class="pre">type_engine()</span></code></a>
to help determine what type should ultimately be returned
for a given <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>.</p>
<p>By default returns <code class="docutils literal notranslate"><span class="pre">self.impl</span></code>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.process_bind_param">
<code class="descname">process_bind_param</code><span class="sig-paren">(</span><em>value</em>, <em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.process_bind_param" title="Permalink to this definition">¶</a></dt>
<dd><p>Receive a bound parameter value to be converted.</p>
<p>Subclasses override this method to return the
value that should be passed along to the underlying
<a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> object, and from there to the
DBAPI <code class="docutils literal notranslate"><span class="pre">execute()</span></code> method.</p>
<p>The operation could be anything desired to perform custom
behavior, such as transforming or serializing data.
This could also be used as a hook for validating logic.</p>
<p>This operation should be designed with the reverse operation
in mind, which would be the process_result_value method of
this class.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.process_bind_param.params.value"></span><strong>value</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.process_bind_param.params.value">¶</a> – Data to operate upon, of any type expected by
this method in the subclass. Can be <code class="docutils literal notranslate"><span class="pre">None</span></code>.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.process_bind_param.params.dialect"></span><strong>dialect</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.process_bind_param.params.dialect">¶</a> – the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a> in use.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.process_literal_param">
<code class="descname">process_literal_param</code><span class="sig-paren">(</span><em>value</em>, <em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.process_literal_param" title="Permalink to this definition">¶</a></dt>
<dd><p>Receive a literal parameter value to be rendered inline within
a statement.</p>
<p>This method is used when the compiler renders a
literal value without using binds, typically within DDL
such as in the “server default” of a column or an expression
within a CHECK constraint.</p>
<p>The returned string will be rendered into the output string.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0.</span></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.process_result_value">
<code class="descname">process_result_value</code><span class="sig-paren">(</span><em>value</em>, <em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.process_result_value" title="Permalink to this definition">¶</a></dt>
<dd><p>Receive a result-row column value to be converted.</p>
<p>Subclasses should implement this method to operate on data
fetched from the database.</p>
<p>Subclasses override this method to return the
value that should be passed back to the application,
given a value that is already processed by
the underlying <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> object, originally
from the DBAPI cursor method <code class="docutils literal notranslate"><span class="pre">fetchone()</span></code> or similar.</p>
<p>The operation could be anything desired to perform custom
behavior, such as transforming or serializing data.
This could also be used as a hook for validating logic.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.process_result_value.params.value"></span><strong>value</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.process_result_value.params.value">¶</a> – Data to operate upon, of any type expected by
this method in the subclass. Can be <code class="docutils literal notranslate"><span class="pre">None</span></code>.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.process_result_value.params.dialect"></span><strong>dialect</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.process_result_value.params.dialect">¶</a> – the <a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a> in use.</p></li>
</ul>
</dd>
</dl>
<p>This operation should be designed to be reversible by
the “process_bind_param” method of this class.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.TypeDecorator.python_type">
<code class="descname">python_type</code><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.python_type" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.python_type" title="sqlalchemy.types.TypeEngine.python_type"><code class="xref py py-attr docutils literal notranslate"><span class="pre">python_type</span></code></a> <em>attribute of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Return the Python type object expected to be returned
by instances of this type, if known.</p>
<p>Basically, for those types which enforce a return type,
or are known across the board to do such for all common
DBAPIs (like <code class="docutils literal notranslate"><span class="pre">int</span></code> for example), will return that type.</p>
<p>If a return type is not defined, raises
<code class="docutils literal notranslate"><span class="pre">NotImplementedError</span></code>.</p>
<p>Note that any type also accommodates NULL in SQL which
means you can also get back <code class="docutils literal notranslate"><span class="pre">None</span></code> from any type
in practice.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.result_processor">
<code class="descname">result_processor</code><span class="sig-paren">(</span><em>dialect</em>, <em>coltype</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.result_processor" title="Permalink to this definition">¶</a></dt>
<dd><p>Provide a result value processing function for the given
<a class="reference internal" href="internals.html#sqlalchemy.engine.interfaces.Dialect" title="sqlalchemy.engine.interfaces.Dialect"><code class="xref py py-class docutils literal notranslate"><span class="pre">Dialect</span></code></a>.</p>
<p>This is the method that fulfills the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a>
contract for result value conversion. <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>
will wrap a user-defined implementation of
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_result_value" title="sqlalchemy.types.TypeDecorator.process_result_value"><code class="xref py py-meth docutils literal notranslate"><span class="pre">process_result_value()</span></code></a> here.</p>
<p>User-defined code can override this method directly,
though its likely best to use <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.process_result_value" title="sqlalchemy.types.TypeDecorator.process_result_value"><code class="xref py py-meth docutils literal notranslate"><span class="pre">process_result_value()</span></code></a> so that
the processing provided by <code class="docutils literal notranslate"><span class="pre">self.impl</span></code> is maintained.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.result_processor.params.dialect"></span><strong>dialect</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.result_processor.params.dialect">¶</a> – Dialect instance in use.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.result_processor.params.coltype"></span><strong>coltype</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.result_processor.params.coltype">¶</a> – A SQLAlchemy data type</p></li>
</ul>
</dd>
</dl>
<p>This method is the reverse counterpart to the
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.bind_processor" title="sqlalchemy.types.TypeDecorator.bind_processor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">bind_processor()</span></code></a> method of this class.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.type_engine">
<code class="descname">type_engine</code><span class="sig-paren">(</span><em>dialect</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.type_engine" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a dialect-specific <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> instance
for this <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>.</p>
<p>In most cases this returns a dialect-adapted form of
the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> type represented by <code class="docutils literal notranslate"><span class="pre">self.impl</span></code>.
Makes usage of <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.dialect_impl" title="sqlalchemy.types.TypeDecorator.dialect_impl"><code class="xref py py-meth docutils literal notranslate"><span class="pre">dialect_impl()</span></code></a> but also traverses
into wrapped <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> instances.
Behavior can be customized here by overriding
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.load_dialect_impl" title="sqlalchemy.types.TypeDecorator.load_dialect_impl"><code class="xref py py-meth docutils literal notranslate"><span class="pre">load_dialect_impl()</span></code></a>.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.TypeDecorator.with_variant">
<code class="descname">with_variant</code><span class="sig-paren">(</span><em>type_</em>, <em>dialect_name</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.TypeDecorator.with_variant" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.with_variant" title="sqlalchemy.types.TypeEngine.with_variant"><code class="xref py py-meth docutils literal notranslate"><span class="pre">with_variant()</span></code></a> <em>method of</em> <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a></p>
</div>
<p>Produce a new type object that will utilize the given
type when applied to the dialect of the given name.</p>
<p>e.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="k">import</span> <span class="n">String</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects</span> <span class="k">import</span> <span class="n">mysql</span>
<span class="n">s</span> <span class="o">=</span> <span class="n">String</span><span class="p">()</span>
<span class="n">s</span> <span class="o">=</span> <span class="n">s</span><span class="o">.</span><span class="n">with_variant</span><span class="p">(</span><span class="n">mysql</span><span class="o">.</span><span class="n">VARCHAR</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s1">'foo'</span><span class="p">),</span> <span class="s1">'mysql'</span><span class="p">)</span></pre></div>
</div>
<p>The construction of <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.with_variant" title="sqlalchemy.types.TypeEngine.with_variant"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.with_variant()</span></code></a> is always
from the “fallback” type to that which is dialect specific.
The returned type is an instance of <a class="reference internal" href="type_api.html#sqlalchemy.types.Variant" title="sqlalchemy.types.Variant"><code class="xref py py-class docutils literal notranslate"><span class="pre">Variant</span></code></a>, which
itself provides a <code class="xref py py-meth docutils literal notranslate"><span class="pre">Variant.with_variant()</span></code>
that can be called repeatedly.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.with_variant.params.type_"></span><strong>type_</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.with_variant.params.type_">¶</a> – a <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> that will be selected
as a variant from the originating type, when a dialect
of the given name is in use.</p></li>
<li><p><span class="target" id="sqlalchemy.types.TypeDecorator.with_variant.params.dialect_name"></span><strong>dialect_name</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.types.TypeDecorator.with_variant.params.dialect_name">¶</a> – base name of the dialect which uses
this type. (i.e. <code class="docutils literal notranslate"><span class="pre">'postgresql'</span></code>, <code class="docutils literal notranslate"><span class="pre">'mysql'</span></code>, etc.)</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
</div>
<div class="section" id="typedecorator-recipes">
<h2>TypeDecorator Recipes<a class="headerlink" href="#typedecorator-recipes" title="Permalink to this headline">¶</a></h2>
<p>A few key <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> recipes follow.</p>
<div class="section" id="coercing-encoded-strings-to-unicode">
<span id="coerce-to-unicode"></span><h3>Coercing Encoded Strings to Unicode<a class="headerlink" href="#coercing-encoded-strings-to-unicode" title="Permalink to this headline">¶</a></h3>
<p>A common source of confusion regarding the <a class="reference internal" href="type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> type
is that it is intended to deal <em>only</em> with Python <code class="docutils literal notranslate"><span class="pre">unicode</span></code> objects
on the Python side, meaning values passed to it as bind parameters
must be of the form <code class="docutils literal notranslate"><span class="pre">u'some</span> <span class="pre">string'</span></code> if using Python 2 and not 3.
The encoding/decoding functions it performs are only to suit what the
DBAPI in use requires, and are primarily a private implementation detail.</p>
<p>The use case of a type that can safely receive Python bytestrings,
that is strings that contain non-ASCII characters and are not <code class="docutils literal notranslate"><span class="pre">u''</span></code>
objects in Python 2, can be achieved using a <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>
which coerces as needed:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="k">import</span> <span class="n">TypeDecorator</span><span class="p">,</span> <span class="n">Unicode</span>
<span class="k">class</span> <span class="nc">CoerceUTF8</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="sd">"""Safely coerce Python bytestrings to Unicode</span>
<span class="sd"> before passing off to the database."""</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">Unicode</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="nb">str</span><span class="p">):</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">value</span><span class="o">.</span><span class="n">decode</span><span class="p">(</span><span class="s1">'utf-8'</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
</div>
<div class="section" id="rounding-numerics">
<h3>Rounding Numerics<a class="headerlink" href="#rounding-numerics" title="Permalink to this headline">¶</a></h3>
<p>Some database connectors like those of SQL Server choke if a Decimal is passed with too
many decimal places. Here’s a recipe that rounds them down:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="k">import</span> <span class="n">TypeDecorator</span><span class="p">,</span> <span class="n">Numeric</span>
<span class="kn">from</span> <span class="nn">decimal</span> <span class="k">import</span> <span class="n">Decimal</span>
<span class="k">class</span> <span class="nc">SafeNumeric</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="sd">"""Adds quantization to Numeric."""</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">Numeric</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="n">arg</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="n">TypeDecorator</span><span class="o">.</span><span class="fm">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="o">*</span><span class="n">arg</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span>
<span class="bp">self</span><span class="o">.</span><span class="n">quantize_int</span> <span class="o">=</span> <span class="o">-</span> <span class="bp">self</span><span class="o">.</span><span class="n">impl</span><span class="o">.</span><span class="n">scale</span>
<span class="bp">self</span><span class="o">.</span><span class="n">quantize</span> <span class="o">=</span> <span class="n">Decimal</span><span class="p">(</span><span class="mi">10</span><span class="p">)</span> <span class="o">**</span> <span class="bp">self</span><span class="o">.</span><span class="n">quantize_int</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="n">Decimal</span><span class="p">)</span> <span class="ow">and</span> \
<span class="n">value</span><span class="o">.</span><span class="n">as_tuple</span><span class="p">()[</span><span class="mi">2</span><span class="p">]</span> <span class="o"><</span> <span class="bp">self</span><span class="o">.</span><span class="n">quantize_int</span><span class="p">:</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">value</span><span class="o">.</span><span class="n">quantize</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">quantize</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
</div>
<div class="section" id="backend-agnostic-guid-type">
<span id="custom-guid-type"></span><h3>Backend-agnostic GUID Type<a class="headerlink" href="#backend-agnostic-guid-type" title="Permalink to this headline">¶</a></h3>
<p>Receives and returns Python uuid() objects. Uses the PG UUID type
when using PostgreSQL, CHAR(32) on other backends, storing them
in stringified hex format. Can be modified to store
binary in CHAR(16) if desired:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="k">import</span> <span class="n">TypeDecorator</span><span class="p">,</span> <span class="n">CHAR</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="k">import</span> <span class="n">UUID</span>
<span class="kn">import</span> <span class="nn">uuid</span>
<span class="k">class</span> <span class="nc">GUID</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="sd">"""Platform-independent GUID type.</span>
<span class="sd"> Uses PostgreSQL's UUID type, otherwise uses</span>
<span class="sd"> CHAR(32), storing as stringified hex values.</span>
<span class="sd"> """</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">CHAR</span>
<span class="k">def</span> <span class="nf">load_dialect_impl</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">dialect</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s1">'postgresql'</span><span class="p">:</span>
<span class="k">return</span> <span class="n">dialect</span><span class="o">.</span><span class="n">type_descriptor</span><span class="p">(</span><span class="n">UUID</span><span class="p">())</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">return</span> <span class="n">dialect</span><span class="o">.</span><span class="n">type_descriptor</span><span class="p">(</span><span class="n">CHAR</span><span class="p">(</span><span class="mi">32</span><span class="p">))</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">elif</span> <span class="n">dialect</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s1">'postgresql'</span><span class="p">:</span>
<span class="k">return</span> <span class="nb">str</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">if</span> <span class="ow">not</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">):</span>
<span class="k">return</span> <span class="s2">"</span><span class="si">%.32x</span><span class="s2">"</span> <span class="o">%</span> <span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">(</span><span class="n">value</span><span class="p">)</span><span class="o">.</span><span class="n">int</span>
<span class="k">else</span><span class="p">:</span>
<span class="c1"># hexstring</span>
<span class="k">return</span> <span class="s2">"</span><span class="si">%.32x</span><span class="s2">"</span> <span class="o">%</span> <span class="n">value</span><span class="o">.</span><span class="n">int</span>
<span class="k">def</span> <span class="nf">process_result_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">if</span> <span class="ow">not</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">value</span><span class="p">,</span> <span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">):</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
</div>
<div class="section" id="marshal-json-strings">
<h3>Marshal JSON Strings<a class="headerlink" href="#marshal-json-strings" title="Permalink to this headline">¶</a></h3>
<p>This type uses <code class="docutils literal notranslate"><span class="pre">simplejson</span></code> to marshal Python data structures
to/from JSON. Can be modified to use Python’s builtin json encoder:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="k">import</span> <span class="n">TypeDecorator</span><span class="p">,</span> <span class="n">VARCHAR</span>
<span class="kn">import</span> <span class="nn">json</span>
<span class="k">class</span> <span class="nc">JSONEncodedDict</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="sd">"""Represents an immutable structure as a json-encoded string.</span>
<span class="sd"> Usage::</span>
<span class="sd"> JSONEncodedDict(255)</span>
<span class="sd"> """</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">VARCHAR</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">json</span><span class="o">.</span><span class="n">dumps</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">def</span> <span class="nf">process_result_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">json</span><span class="o">.</span><span class="n">loads</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
<div class="section" id="adding-mutability">
<h4>Adding Mutability<a class="headerlink" href="#adding-mutability" title="Permalink to this headline">¶</a></h4>
<p>The ORM by default will not detect “mutability” on such a type as above -
meaning, in-place changes to values will not be detected and will not be
flushed. Without further steps, you instead would need to replace the existing
value with a new one on each parent object to detect changes:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">obj</span><span class="o">.</span><span class="n">json_value</span><span class="p">[</span><span class="s2">"key"</span><span class="p">]</span> <span class="o">=</span> <span class="s2">"value"</span> <span class="c1"># will *not* be detected by the ORM</span>
<span class="n">obj</span><span class="o">.</span><span class="n">json_value</span> <span class="o">=</span> <span class="p">{</span><span class="s2">"key"</span><span class="p">:</span> <span class="s2">"value"</span><span class="p">}</span> <span class="c1"># *will* be detected by the ORM</span></pre></div>
</div>
<p>The above limitation may be
fine, as many applications may not require that the values are ever mutated
once created. For those which do have this requirement, support for mutability
is best applied using the <code class="docutils literal notranslate"><span class="pre">sqlalchemy.ext.mutable</span></code> extension. For a
dictionary-oriented JSON structure, we can apply this as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">json_type</span> <span class="o">=</span> <span class="n">MutableDict</span><span class="o">.</span><span class="n">as_mutable</span><span class="p">(</span><span class="n">JSONEncodedDict</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c1"># ...</span>
<span class="n">json_data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">json_type</span><span class="p">)</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/extensions/mutable.html"><span class="std std-ref">Mutation Tracking</span></a></p>
</div>
</div>
<div class="section" id="dealing-with-comparison-operations">
<h4>Dealing with Comparison Operations<a class="headerlink" href="#dealing-with-comparison-operations" title="Permalink to this headline">¶</a></h4>
<p>The default behavior of <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> is to coerce the “right hand side”
of any expression into the same type. For a type like JSON, this means that
any operator used must make sense in terms of JSON. For some cases,
users may wish for the type to behave like JSON in some circumstances, and
as plain text in others. One example is if one wanted to handle the
LIKE operator for the JSON type. LIKE makes no sense against a JSON structure,
but it does make sense against the underlying textual representation. To
get at this with a type like <code class="docutils literal notranslate"><span class="pre">JSONEncodedDict</span></code>, we need to
<strong>coerce</strong> the column to a textual form using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><code class="xref py py-func docutils literal notranslate"><span class="pre">cast()</span></code></a> or
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.type_coerce" title="sqlalchemy.sql.expression.type_coerce"><code class="xref py py-func docutils literal notranslate"><span class="pre">type_coerce()</span></code></a> before attempting to use this operator:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">type_coerce</span><span class="p">,</span> <span class="n">String</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">my_table</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
<span class="n">type_coerce</span><span class="p">(</span><span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">json_data</span><span class="p">,</span> <span class="n">String</span><span class="p">)</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s1">'</span><span class="si">%f</span><span class="s1">oo%'</span><span class="p">))</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> provides a built-in system for working up type
translations like these based on operators. If we wanted to frequently use the
LIKE operator with our JSON object interpreted as a string, we can build it
into the type by overriding the <a class="reference internal" href="#sqlalchemy.types.TypeDecorator.coerce_compared_value" title="sqlalchemy.types.TypeDecorator.coerce_compared_value"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.coerce_compared_value()</span></code></a>
method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">operators</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">String</span>
<span class="k">class</span> <span class="nc">JSONEncodedDict</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">VARCHAR</span>
<span class="k">def</span> <span class="nf">coerce_compared_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">op</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="k">if</span> <span class="n">op</span> <span class="ow">in</span> <span class="p">(</span><span class="n">operators</span><span class="o">.</span><span class="n">like_op</span><span class="p">,</span> <span class="n">operators</span><span class="o">.</span><span class="n">notlike_op</span><span class="p">):</span>
<span class="k">return</span> <span class="n">String</span><span class="p">()</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">return</span> <span class="bp">self</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">json</span><span class="o">.</span><span class="n">dumps</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">def</span> <span class="nf">process_result_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">json</span><span class="o">.</span><span class="n">loads</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
<p>Above is just one approach to handling an operator like “LIKE”. Other
applications may wish to raise <code class="docutils literal notranslate"><span class="pre">NotImplementedError</span></code> for operators that
have no meaning with a JSON object such as “LIKE”, rather than automatically
coercing to text.</p>
</div>
</div>
</div>
<div class="section" id="replacing-the-bind-result-processing-of-existing-types">
<span id="replacing-processors"></span><h2>Replacing the Bind/Result Processing of Existing Types<a class="headerlink" href="#replacing-the-bind-result-processing-of-existing-types" title="Permalink to this headline">¶</a></h2>
<p>Most augmentation of type behavior at the bind/result level
is achieved using <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>. For the rare scenario
where the specific processing applied by SQLAlchemy at the DBAPI
level needs to be replaced, the SQLAlchemy type can be subclassed
directly, and the <code class="docutils literal notranslate"><span class="pre">bind_processor()</span></code> or <code class="docutils literal notranslate"><span class="pre">result_processor()</span></code>
methods can be overridden. Doing so requires that the
<code class="docutils literal notranslate"><span class="pre">adapt()</span></code> method also be overridden. This method is the mechanism
by which SQLAlchemy produces DBAPI-specific type behavior during
statement execution. Overriding it allows a copy of the custom
type to be used in lieu of a DBAPI-specific type. Below we subclass
the <a class="reference internal" href="type_basics.html#sqlalchemy.types.TIME" title="sqlalchemy.types.TIME"><code class="xref py py-class docutils literal notranslate"><span class="pre">types.TIME</span></code></a> type to have custom result processing behavior.
The <code class="docutils literal notranslate"><span class="pre">process()</span></code> function will receive <code class="docutils literal notranslate"><span class="pre">value</span></code> from the DBAPI
cursor directly:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MySpecialTime</span><span class="p">(</span><span class="n">TIME</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">special_argument</span><span class="p">):</span>
<span class="nb">super</span><span class="p">(</span><span class="n">MySpecialTime</span><span class="p">,</span> <span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="fm">__init__</span><span class="p">()</span>
<span class="bp">self</span><span class="o">.</span><span class="n">special_argument</span> <span class="o">=</span> <span class="n">special_argument</span>
<span class="k">def</span> <span class="nf">result_processor</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">dialect</span><span class="p">,</span> <span class="n">coltype</span><span class="p">):</span>
<span class="kn">import</span> <span class="nn">datetime</span>
<span class="n">time</span> <span class="o">=</span> <span class="n">datetime</span><span class="o">.</span><span class="n">time</span>
<span class="k">def</span> <span class="nf">process</span><span class="p">(</span><span class="n">value</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">microseconds</span> <span class="o">=</span> <span class="n">value</span><span class="o">.</span><span class="n">microseconds</span>
<span class="n">seconds</span> <span class="o">=</span> <span class="n">value</span><span class="o">.</span><span class="n">seconds</span>
<span class="n">minutes</span> <span class="o">=</span> <span class="n">seconds</span> <span class="o">/</span> <span class="mi">60</span>
<span class="k">return</span> <span class="n">time</span><span class="p">(</span>
<span class="n">minutes</span> <span class="o">/</span> <span class="mi">60</span><span class="p">,</span>
<span class="n">minutes</span> <span class="o">%</span> <span class="mi">60</span><span class="p">,</span>
<span class="n">seconds</span> <span class="o">-</span> <span class="n">minutes</span> <span class="o">*</span> <span class="mi">60</span><span class="p">,</span>
<span class="n">microseconds</span><span class="p">)</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">return</span> <span class="kc">None</span>
<span class="k">return</span> <span class="n">process</span>
<span class="k">def</span> <span class="nf">adapt</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">impltype</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">MySpecialTime</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">special_argument</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="applying-sql-level-bind-result-processing">
<span id="types-sql-value-processing"></span><h2>Applying SQL-level Bind/Result Processing<a class="headerlink" href="#applying-sql-level-bind-result-processing" title="Permalink to this headline">¶</a></h2>
<p>As seen in the sections <a class="reference internal" href="#types-typedecorator"><span class="std std-ref">Augmenting Existing Types</span></a> and <a class="reference internal" href="#replacing-processors"><span class="std std-ref">Replacing the Bind/Result Processing of Existing Types</span></a>,
SQLAlchemy allows Python functions to be invoked both when parameters are sent
to a statement, as well as when result rows are loaded from the database, to apply
transformations to the values as they are sent to or from the database. It is also
possible to define SQL-level transformations as well. The rationale here is when
only the relational database contains a particular series of functions that are necessary
to coerce incoming and outgoing data between an application and persistence format.
Examples include using database-defined encryption/decryption functions, as well
as stored procedures that handle geographic data. The PostGIS extension to PostgreSQL
includes an extensive array of SQL functions that are necessary for coercing
data into particular formats.</p>
<p>Any <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a>, <a class="reference internal" href="#sqlalchemy.types.UserDefinedType" title="sqlalchemy.types.UserDefinedType"><code class="xref py py-class docutils literal notranslate"><span class="pre">UserDefinedType</span></code></a> or <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> subclass
can include implementations of
<a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.bind_expression" title="sqlalchemy.types.TypeEngine.bind_expression"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.bind_expression()</span></code></a> and/or <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.column_expression" title="sqlalchemy.types.TypeEngine.column_expression"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.column_expression()</span></code></a>, which
when defined to return a non-<code class="docutils literal notranslate"><span class="pre">None</span></code> value should return a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnElement</span></code></a>
expression to be injected into the SQL statement, either surrounding
bound parameters or a column expression. For example, to build a <code class="docutils literal notranslate"><span class="pre">Geometry</span></code>
type which will apply the PostGIS function <code class="docutils literal notranslate"><span class="pre">ST_GeomFromText</span></code> to all outgoing
values and the function <code class="docutils literal notranslate"><span class="pre">ST_AsText</span></code> to all incoming data, we can create
our own subclass of <a class="reference internal" href="#sqlalchemy.types.UserDefinedType" title="sqlalchemy.types.UserDefinedType"><code class="xref py py-class docutils literal notranslate"><span class="pre">UserDefinedType</span></code></a> which provides these methods
in conjunction with <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.func" title="sqlalchemy.sql.expression.func"><code class="xref py py-data docutils literal notranslate"><span class="pre">func</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">func</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.types</span> <span class="k">import</span> <span class="n">UserDefinedType</span>
<span class="k">class</span> <span class="nc">Geometry</span><span class="p">(</span><span class="n">UserDefinedType</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">get_col_spec</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="s2">"GEOMETRY"</span>
<span class="k">def</span> <span class="nf">bind_expression</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">bindvalue</span><span class="p">):</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">ST_GeomFromText</span><span class="p">(</span><span class="n">bindvalue</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="bp">self</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">column_expression</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">col</span><span class="p">):</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">ST_AsText</span><span class="p">(</span><span class="n">col</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="bp">self</span><span class="p">)</span></pre></div>
</div>
<p>We can apply the <code class="docutils literal notranslate"><span class="pre">Geometry</span></code> type into <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> metadata
and use it in a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> construct:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">geometry</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'geometry'</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="s1">'geom_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="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'geom_data'</span><span class="p">,</span> <span class="n">Geometry</span><span class="p">)</span>
<span class="p">)</span>
<span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">geometry</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span>
<span class="n">geometry</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">geom_data</span> <span class="o">==</span> <span class="s1">'LINESTRING(189412 252431,189631 259122)'</span><span class="p">))</span></pre></div>
</div>
<p>The resulting SQL embeds both functions as appropriate. <code class="docutils literal notranslate"><span class="pre">ST_AsText</span></code>
is applied to the columns clause so that the return value is run through
the function before passing into a result set, and <code class="docutils literal notranslate"><span class="pre">ST_GeomFromText</span></code>
is run on the bound parameter so that the passed-in value is converted:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">geometry</span><span class="o">.</span><span class="n">geom_id</span><span class="p">,</span> <span class="n">ST_AsText</span><span class="p">(</span><span class="n">geometry</span><span class="o">.</span><span class="n">geom_data</span><span class="p">)</span> <span class="n">AS</span> <span class="n">geom_data_1</span>
<span class="n">FROM</span> <span class="n">geometry</span>
<span class="n">WHERE</span> <span class="n">geometry</span><span class="o">.</span><span class="n">geom_data</span> <span class="o">=</span> <span class="n">ST_GeomFromText</span><span class="p">(:</span><span class="n">geom_data_2</span><span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.column_expression" title="sqlalchemy.types.TypeEngine.column_expression"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.column_expression()</span></code></a> method interacts with the
mechanics of the compiler such that the SQL expression does not interfere
with the labeling of the wrapped expression. Such as, if we rendered
a <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> against a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.label" title="sqlalchemy.sql.expression.label"><code class="xref py py-func docutils literal notranslate"><span class="pre">label()</span></code></a> of our expression, the string
label is moved to the outside of the wrapped expression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">geometry</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">geom_data</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s1">'my_data'</span><span class="p">)]))</span></pre></div>
</div>
<p>Output:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">ST_AsText</span><span class="p">(</span><span class="n">geometry</span><span class="o">.</span><span class="n">geom_data</span><span class="p">)</span> <span class="n">AS</span> <span class="n">my_data</span>
<span class="n">FROM</span> <span class="n">geometry</span></pre></div>
</div>
<p>For an example of subclassing a built in type directly, we subclass
<a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.BYTEA" title="sqlalchemy.dialects.postgresql.BYTEA"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.BYTEA</span></code></a> to provide a <code class="docutils literal notranslate"><span class="pre">PGPString</span></code>, which will make use of the
PostgreSQL <code class="docutils literal notranslate"><span class="pre">pgcrypto</span></code> extension to encrypt/decrypt values
transparently:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">func</span><span class="p">,</span> \
<span class="n">MetaData</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">type_coerce</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="k">import</span> <span class="n">BYTEA</span>
<span class="k">class</span> <span class="nc">PGPString</span><span class="p">(</span><span class="n">BYTEA</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">passphrase</span><span class="p">,</span> <span class="n">length</span><span class="o">=</span><span class="kc">None</span><span class="p">):</span>
<span class="nb">super</span><span class="p">(</span><span class="n">PGPString</span><span class="p">,</span> <span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="fm">__init__</span><span class="p">(</span><span class="n">length</span><span class="p">)</span>
<span class="bp">self</span><span class="o">.</span><span class="n">passphrase</span> <span class="o">=</span> <span class="n">passphrase</span>
<span class="k">def</span> <span class="nf">bind_expression</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">bindvalue</span><span class="p">):</span>
<span class="c1"># convert the bind's type from PGPString to</span>
<span class="c1"># String, so that it's passed to psycopg2 as is without</span>
<span class="c1"># a dbapi.Binary wrapper</span>
<span class="n">bindvalue</span> <span class="o">=</span> <span class="n">type_coerce</span><span class="p">(</span><span class="n">bindvalue</span><span class="p">,</span> <span class="n">String</span><span class="p">)</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">pgp_sym_encrypt</span><span class="p">(</span><span class="n">bindvalue</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">passphrase</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">column_expression</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">col</span><span class="p">):</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">pgp_sym_decrypt</span><span class="p">(</span><span class="n">col</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">passphrase</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">message</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'message'</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="s1">'username'</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">Column</span><span class="p">(</span><span class="s1">'message'</span><span class="p">,</span>
<span class="n">PGPString</span><span class="p">(</span><span class="s2">"this is my passphrase"</span><span class="p">,</span> <span class="n">length</span><span class="o">=</span><span class="mi">1000</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"postgresql://scott:tiger@localhost/test"</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">conn</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">message</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">username</span><span class="o">=</span><span class="s2">"some user"</span><span class="p">,</span>
<span class="n">message</span><span class="o">=</span><span class="s2">"this is my message"</span><span class="p">)</span>
<span class="nb">print</span><span class="p">(</span><span class="n">conn</span><span class="o">.</span><span class="n">scalar</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">message</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">message</span><span class="p">])</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">message</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">username</span> <span class="o">==</span> <span class="s2">"some user"</span><span class="p">)</span>
<span class="p">))</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">pgp_sym_encrypt</span></code> and <code class="docutils literal notranslate"><span class="pre">pgp_sym_decrypt</span></code> functions are applied
to the INSERT and SELECT statements:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">INSERT</span> <span class="n">INTO</span> <span class="n">message</span> <span class="p">(</span><span class="n">username</span><span class="p">,</span> <span class="n">message</span><span class="p">)</span>
<span class="n">VALUES</span> <span class="p">(</span><span class="o">%</span><span class="p">(</span><span class="n">username</span><span class="p">)</span><span class="n">s</span><span class="p">,</span> <span class="n">pgp_sym_encrypt</span><span class="p">(</span><span class="o">%</span><span class="p">(</span><span class="n">message</span><span class="p">)</span><span class="n">s</span><span class="p">,</span> <span class="o">%</span><span class="p">(</span><span class="n">pgp_sym_encrypt_1</span><span class="p">)</span><span class="n">s</span><span class="p">))</span>
<span class="p">{</span><span class="s1">'username'</span><span class="p">:</span> <span class="s1">'some user'</span><span class="p">,</span> <span class="s1">'message'</span><span class="p">:</span> <span class="s1">'this is my message'</span><span class="p">,</span>
<span class="s1">'pgp_sym_encrypt_1'</span><span class="p">:</span> <span class="s1">'this is my passphrase'</span><span class="p">}</span>
<span class="n">SELECT</span> <span class="n">pgp_sym_decrypt</span><span class="p">(</span><span class="n">message</span><span class="o">.</span><span class="n">message</span><span class="p">,</span> <span class="o">%</span><span class="p">(</span><span class="n">pgp_sym_decrypt_1</span><span class="p">)</span><span class="n">s</span><span class="p">)</span> <span class="n">AS</span> <span class="n">message_1</span>
<span class="n">FROM</span> <span class="n">message</span>
<span class="n">WHERE</span> <span class="n">message</span><span class="o">.</span><span class="n">username</span> <span class="o">=</span> <span class="o">%</span><span class="p">(</span><span class="n">username_1</span><span class="p">)</span><span class="n">s</span>
<span class="p">{</span><span class="s1">'pgp_sym_decrypt_1'</span><span class="p">:</span> <span class="s1">'this is my passphrase'</span><span class="p">,</span> <span class="s1">'username_1'</span><span class="p">:</span> <span class="s1">'some user'</span><span class="p">}</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/examples.html#examples-postgis"><span class="std std-ref">PostGIS Integration</span></a></p>
</div>
</div>
<div class="section" id="redefining-and-creating-new-operators">
<span id="types-operators"></span><h2>Redefining and Creating New Operators<a class="headerlink" href="#redefining-and-creating-new-operators" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy Core defines a fixed set of expression operators available to all column expressions.
Some of these operations have the effect of overloading Python’s built in operators;
examples of such operators include
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__eq__" title="sqlalchemy.sql.operators.ColumnOperators.__eq__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.__eq__()</span></code></a> (<code class="docutils literal notranslate"><span class="pre">table.c.somecolumn</span> <span class="pre">==</span> <span class="pre">'foo'</span></code>),
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__invert__" title="sqlalchemy.sql.operators.ColumnOperators.__invert__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.__invert__()</span></code></a> (<code class="docutils literal notranslate"><span class="pre">~table.c.flag</span></code>),
and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__add__" title="sqlalchemy.sql.operators.ColumnOperators.__add__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.__add__()</span></code></a> (<code class="docutils literal notranslate"><span class="pre">table.c.x</span> <span class="pre">+</span> <span class="pre">table.c.y</span></code>). Other operators are exposed as
explicit methods on column expressions, such as
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.in_()</span></code></a> (<code class="docutils literal notranslate"><span class="pre">table.c.value.in_(['x',</span> <span class="pre">'y'])</span></code>) and <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.like" title="sqlalchemy.sql.operators.ColumnOperators.like"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.like()</span></code></a>
(<code class="docutils literal notranslate"><span class="pre">table.c.value.like('%ed%')</span></code>).</p>
<p>The Core expression constructs in all cases consult the type of the expression in order to determine
the behavior of existing operators, as well as to locate additional operators that aren’t part of
the built in set. The <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> base class defines a root “comparison” implementation
<a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.Comparator" title="sqlalchemy.types.TypeEngine.Comparator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine.Comparator</span></code></a>, and many specific types provide their own sub-implementations of this
class. User-defined <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.Comparator" title="sqlalchemy.types.TypeEngine.Comparator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine.Comparator</span></code></a> implementations can be built directly into a
simple subclass of a particular type in order to override or define new operations. Below,
we create a <a class="reference internal" href="type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">Integer</span></code></a> subclass which overrides the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__add__" title="sqlalchemy.sql.operators.ColumnOperators.__add__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.__add__()</span></code></a> operator:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Integer</span>
<span class="k">class</span> <span class="nc">MyInt</span><span class="p">(</span><span class="n">Integer</span><span class="p">):</span>
<span class="k">class</span> <span class="nc">comparator_factory</span><span class="p">(</span><span class="n">Integer</span><span class="o">.</span><span class="n">Comparator</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__add__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s2">"goofy"</span><span class="p">)(</span><span class="n">other</span><span class="p">)</span></pre></div>
</div>
<p>The above configuration creates a new class <code class="docutils literal notranslate"><span class="pre">MyInt</span></code>, which
establishes the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.comparator_factory" title="sqlalchemy.types.TypeEngine.comparator_factory"><code class="xref py py-attr docutils literal notranslate"><span class="pre">TypeEngine.comparator_factory</span></code></a> attribute as
referring to a new class, subclassing the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.Comparator" title="sqlalchemy.types.TypeEngine.Comparator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine.Comparator</span></code></a> class
associated with the <a class="reference internal" href="type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">Integer</span></code></a> type.</p>
<p>Usage:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">sometable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s2">"sometable"</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="s2">"data"</span><span class="p">,</span> <span class="n">MyInt</span><span class="p">))</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span> <span class="o">+</span> <span class="mi">5</span><span class="p">)</span>
<span class="go">sometable.data goofy :data_1</span></pre></div>
</div>
<p>The implementation for <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.__add__" title="sqlalchemy.sql.operators.ColumnOperators.__add__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.__add__()</span></code></a> is consulted
by an owning SQL expression, by instantiating the <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.Comparator" title="sqlalchemy.types.TypeEngine.Comparator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine.Comparator</span></code></a> with
itself as the <code class="docutils literal notranslate"><span class="pre">expr</span></code> attribute. The mechanics of the expression
system are such that operations continue recursively until an
expression object produces a new SQL expression construct. Above, we
could just as well have said <code class="docutils literal notranslate"><span class="pre">self.expr.op("goofy")(other)</span></code> instead
of <code class="docutils literal notranslate"><span class="pre">self.op("goofy")(other)</span></code>.</p>
<p>When using <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a> for comparison operations that return a
boolean result, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op.params.is_comparison" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Operators.op.is_comparison</span></code></a> flag should be
set to <code class="docutils literal notranslate"><span class="pre">True</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyInt</span><span class="p">(</span><span class="n">Integer</span><span class="p">):</span>
<span class="k">class</span> <span class="nc">comparator_factory</span><span class="p">(</span><span class="n">Integer</span><span class="o">.</span><span class="n">Comparator</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">is_frobnozzled</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s2">"--is_frobnozzled->"</span><span class="p">,</span> <span class="n">is_comparison</span><span class="o">=</span><span class="kc">True</span><span class="p">)(</span><span class="n">other</span><span class="p">)</span></pre></div>
</div>
<p>New methods added to a <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.Comparator" title="sqlalchemy.types.TypeEngine.Comparator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine.Comparator</span></code></a> are exposed on an
owning SQL expression
using a <code class="docutils literal notranslate"><span class="pre">__getattr__</span></code> scheme, which exposes methods added to
<a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.Comparator" title="sqlalchemy.types.TypeEngine.Comparator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine.Comparator</span></code></a> onto the owning <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement" title="sqlalchemy.sql.expression.ColumnElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnElement</span></code></a>.
For example, to add a <code class="docutils literal notranslate"><span class="pre">log()</span></code> function
to integers:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">func</span>
<span class="k">class</span> <span class="nc">MyInt</span><span class="p">(</span><span class="n">Integer</span><span class="p">):</span>
<span class="k">class</span> <span class="nc">comparator_factory</span><span class="p">(</span><span class="n">Integer</span><span class="o">.</span><span class="n">Comparator</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">log</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">log</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">expr</span><span class="p">,</span> <span class="n">other</span><span class="p">)</span></pre></div>
</div>
<p>Using the above type:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">sometable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">log</span><span class="p">(</span><span class="mi">5</span><span class="p">))</span>
<span class="go">log(:log_1, :log_2)</span></pre></div>
</div>
<p>Unary operations
are also possible. For example, to add an implementation of the
PostgreSQL factorial operator, we combine the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.UnaryExpression" title="sqlalchemy.sql.expression.UnaryExpression"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnaryExpression</span></code></a> construct
along with a <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.custom_op" title="sqlalchemy.sql.operators.custom_op"><code class="xref py py-class docutils literal notranslate"><span class="pre">custom_op</span></code></a> to produce the factorial expression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Integer</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="k">import</span> <span class="n">UnaryExpression</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">operators</span>
<span class="k">class</span> <span class="nc">MyInteger</span><span class="p">(</span><span class="n">Integer</span><span class="p">):</span>
<span class="k">class</span> <span class="nc">comparator_factory</span><span class="p">(</span><span class="n">Integer</span><span class="o">.</span><span class="n">Comparator</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">factorial</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="n">UnaryExpression</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">expr</span><span class="p">,</span>
<span class="n">modifier</span><span class="o">=</span><span class="n">operators</span><span class="o">.</span><span class="n">custom_op</span><span class="p">(</span><span class="s2">"!"</span><span class="p">),</span>
<span class="n">type_</span><span class="o">=</span><span class="n">MyInteger</span><span class="p">)</span></pre></div>
</div>
<p>Using the above type:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">column</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">MyInteger</span><span class="p">)</span><span class="o">.</span><span class="n">factorial</span><span class="p">())</span>
<span class="go">x !</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a></p>
<p><a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine.comparator_factory" title="sqlalchemy.types.TypeEngine.comparator_factory"><code class="xref py py-attr docutils literal notranslate"><span class="pre">TypeEngine.comparator_factory</span></code></a></p>
</div>
</div>
<div class="section" id="creating-new-types">
<h2>Creating New Types<a class="headerlink" href="#creating-new-types" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="#sqlalchemy.types.UserDefinedType" title="sqlalchemy.types.UserDefinedType"><code class="xref py py-class docutils literal notranslate"><span class="pre">UserDefinedType</span></code></a> class is provided as a simple base class
for defining entirely new database types. Use this to represent native
database types not known by SQLAlchemy. If only Python translation behavior
is needed, use <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> instead.</p>
<dl class="class">
<dt id="sqlalchemy.types.UserDefinedType">
<em class="property">class </em><code class="descclassname">sqlalchemy.types.</code><code class="descname">UserDefinedType</code><a class="headerlink" href="#sqlalchemy.types.UserDefinedType" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<p>Base for user defined types.</p>
<p>This should be the base of new types. Note that
for most cases, <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> is probably
more appropriate:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">sqlalchemy.types</span> <span class="k">as</span> <span class="nn">types</span>
<span class="k">class</span> <span class="nc">MyType</span><span class="p">(</span><span class="n">types</span><span class="o">.</span><span class="n">UserDefinedType</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">precision</span> <span class="o">=</span> <span class="mi">8</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">precision</span> <span class="o">=</span> <span class="n">precision</span>
<span class="k">def</span> <span class="nf">get_col_spec</span><span class="p">(</span><span class="bp">self</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="s2">"MYTYPE(</span><span class="si">%s</span><span class="s2">)"</span> <span class="o">%</span> <span class="bp">self</span><span class="o">.</span><span class="n">precision</span>
<span class="k">def</span> <span class="nf">bind_processor</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">process</span><span class="p">(</span><span class="n">value</span><span class="p">):</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">return</span> <span class="n">process</span>
<span class="k">def</span> <span class="nf">result_processor</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">dialect</span><span class="p">,</span> <span class="n">coltype</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">process</span><span class="p">(</span><span class="n">value</span><span class="p">):</span>
<span class="k">return</span> <span class="n">value</span>
<span class="k">return</span> <span class="n">process</span></pre></div>
</div>
<p>Once the type is made, it’s immediately usable:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'foo'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'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="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'data'</span><span class="p">,</span> <span class="n">MyType</span><span class="p">(</span><span class="mi">16</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">get_col_spec()</span></code> method will in most cases receive a keyword
argument <code class="docutils literal notranslate"><span class="pre">type_expression</span></code> which refers to the owning expression
of the type as being compiled, such as a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> or
<a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><code class="xref py py-func docutils literal notranslate"><span class="pre">cast()</span></code></a> construct. This keyword is only sent if the method
accepts keyword arguments (e.g. <code class="docutils literal notranslate"><span class="pre">**kw</span></code>) in its argument signature;
introspection is used to check for this in order to support legacy
forms of this function.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0: </span>the owning expression is passed to
the <code class="docutils literal notranslate"><span class="pre">get_col_spec()</span></code> method via the keyword argument
<code class="docutils literal notranslate"><span class="pre">type_expression</span></code>, if it receives <code class="docutils literal notranslate"><span class="pre">**kw</span></code> in its signature.</p>
</div>
<dl class="class">
<dt id="sqlalchemy.types.UserDefinedType.Comparator">
<em class="property">class </em><code class="descname">Comparator</code><span class="sig-paren">(</span><em>expr</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.UserDefinedType.Comparator" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Comparator</span></code></p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.types.UserDefinedType.coerce_compared_value">
<code class="descname">coerce_compared_value</code><span class="sig-paren">(</span><em>op</em>, <em>value</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.types.UserDefinedType.coerce_compared_value" title="Permalink to this definition">¶</a></dt>
<dd><p>Suggest a type for a ‘coerced’ Python value in an expression.</p>
<p>Default behavior for <a class="reference internal" href="#sqlalchemy.types.UserDefinedType" title="sqlalchemy.types.UserDefinedType"><code class="xref py py-class docutils literal notranslate"><span class="pre">UserDefinedType</span></code></a> is the
same as that of <a class="reference internal" href="#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>; by default it returns
<code class="docutils literal notranslate"><span class="pre">self</span></code>, assuming the compared value should be coerced into
the same type as this one. See
<a class="reference internal" href="#sqlalchemy.types.TypeDecorator.coerce_compared_value" title="sqlalchemy.types.TypeDecorator.coerce_compared_value"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.coerce_compared_value()</span></code></a> for more detail.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.types.UserDefinedType.comparator_factory">
<code class="descname">comparator_factory</code><a class="headerlink" href="#sqlalchemy.types.UserDefinedType.comparator_factory" title="Permalink to this definition">¶</a></dt>
<dd><p>alias of <a class="reference internal" href="#sqlalchemy.types.UserDefinedType.Comparator" title="sqlalchemy.types.UserDefinedType.Comparator"><code class="xref py py-class docutils literal notranslate"><span class="pre">UserDefinedType.Comparator</span></code></a></p>
</dd></dl>
</dd></dl>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="type_basics.html" title="previous chapter">Column and Data Types</a>
Next:
<a href="type_api.html" title="next chapter">Base Type API</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1.
</div>
</div>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '1.2.19',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></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>
<script type="text/javascript" src="../_static/language_data.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>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
b0b6ffab06cbeede296e36ce94734bf8
>
files
>
759
