<!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>
Customizing DDL
—
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="Schema Definition Language" href="schema.html" />
<link rel="next" title="Column and Data Types" href="types.html" />
<link rel="prev" title="Defining Constraints and Indexes" href="constraints.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><ul>
<li><span class="link-container"><a class="reference external" href="metadata.html">Describing Databases with MetaData</a></span></li>
<li><span class="link-container"><a class="reference external" href="reflection.html">Reflecting Database Objects</a></span></li>
<li><span class="link-container"><a class="reference external" href="defaults.html">Column Insert/Update Defaults</a></span></li>
<li><span class="link-container"><a class="reference external" href="constraints.html">Defining Constraints and Indexes</a></span></li>
<li class="selected"><span class="link-container"><strong>Customizing DDL</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#custom-ddl">Custom DDL</a></span></li>
<li><span class="link-container"><a class="reference external" href="#controlling-ddl-sequences">Controlling DDL Sequences</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-the-built-in-ddlelement-classes">Using the built-in DDLElement Classes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#ddl-expression-constructs-api">DDL Expression Constructs API</a></span></li>
</ul>
</li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="types.html">Column and Data Types</a></span></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.schema"><span id="metadata-ddl"></span><span id="metadata-ddl-toplevel"></span></span><div class="section" id="customizing-ddl">
<h1>Customizing DDL<a class="headerlink" href="#customizing-ddl" title="Permalink to this headline">¶</a></h1>
<p>In the preceding sections we’ve discussed a variety of schema constructs
including <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>,
<a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a>,
<a class="reference internal" href="constraints.html#sqlalchemy.schema.CheckConstraint" title="sqlalchemy.schema.CheckConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">CheckConstraint</span></code></a>, and
<a class="reference internal" href="defaults.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><code class="xref py py-class docutils literal notranslate"><span class="pre">Sequence</span></code></a>. Throughout, we’ve relied upon the
<code class="docutils literal notranslate"><span class="pre">create()</span></code> and <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_all()</span></code></a> methods of
<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> and <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a> in
order to issue data definition language (DDL) for all constructs. When issued,
a pre-determined order of operations is invoked, and DDL to create each table
is created unconditionally including all constraints and other objects
associated with it. For more complex scenarios where database-specific DDL is
required, SQLAlchemy offers two techniques which can be used to add any DDL
based on any condition, either accompanying the standard generation of tables
or by itself.</p>
<div class="section" id="custom-ddl">
<h2>Custom DDL<a class="headerlink" href="#custom-ddl" title="Permalink to this headline">¶</a></h2>
<p>Custom DDL phrases are most easily achieved using the
<a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a> construct. This construct works like all the
other DDL elements except it accepts a string which is the text to be emitted:</p>
<div class="highlight-python+sql notranslate"><div class="highlight"><pre><span></span><span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">metadata</span><span class="p">,</span>
<span class="s2">"after_create"</span><span class="p">,</span>
<span class="n">DDL</span><span class="p">(</span><span class="s2">"ALTER TABLE users ADD CONSTRAINT "</span>
<span class="s2">"cst_user_name_length "</span>
<span class="s2">" CHECK (length(user_name) >= 8)"</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>A more comprehensive method of creating libraries of DDL constructs is to use
custom compilation - see <a class="reference internal" href="compiler.html"><span class="std std-ref">Custom SQL Constructs and Compilation Extension</span></a> for
details.</p>
</div>
<div class="section" id="controlling-ddl-sequences">
<span id="schema-ddl-sequences"></span><h2>Controlling DDL Sequences<a class="headerlink" href="#controlling-ddl-sequences" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a> construct introduced previously also has the
ability to be invoked conditionally based on inspection of the
database. This feature is available using the <a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DDLElement.execute_if()</span></code></a>
method. For example, if we wanted to create a trigger but only on
the PostgreSQL backend, we could invoke this as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mytable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span>
<span class="s1">'mytable'</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">'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">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">trigger</span> <span class="o">=</span> <span class="n">DDL</span><span class="p">(</span>
<span class="s2">"CREATE TRIGGER dt_ins BEFORE INSERT ON mytable "</span>
<span class="s2">"FOR EACH ROW BEGIN SET NEW.data='ins'; END"</span>
<span class="p">)</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">mytable</span><span class="p">,</span>
<span class="s1">'after_create'</span><span class="p">,</span>
<span class="n">trigger</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="s1">'postgresql'</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if.params.dialect" title="sqlalchemy.schema.DDLElement.execute_if"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">DDLElement.execute_if.dialect</span></code></a> keyword also accepts a tuple
of string dialect names:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">mytable</span><span class="p">,</span>
<span class="s2">"after_create"</span><span class="p">,</span>
<span class="n">trigger</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="p">(</span><span class="s1">'postgresql'</span><span class="p">,</span> <span class="s1">'mysql'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">mytable</span><span class="p">,</span>
<span class="s2">"before_drop"</span><span class="p">,</span>
<span class="n">trigger</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="p">(</span><span class="s1">'postgresql'</span><span class="p">,</span> <span class="s1">'mysql'</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DDLElement.execute_if()</span></code></a> method can also work against a callable
function that will receive the database connection in use. In the
example below, we use this to conditionally create a CHECK constraint,
first looking within the PostgreSQL catalogs to see if it exists:</p>
<div class="highlight-python+sql notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">should_create</span><span class="p">(</span><span class="n">ddl</span><span class="p">,</span> <span class="n">target</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="n">row</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="s2">"select conname from pg_constraint where conname='</span><span class="si">%s</span><span class="s2">'"</span> <span class="o">%</span>
<span class="n">ddl</span><span class="o">.</span><span class="n">element</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span>
<span class="k">return</span> <span class="ow">not</span> <span class="nb">bool</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">should_drop</span><span class="p">(</span><span class="n">ddl</span><span class="p">,</span> <span class="n">target</span><span class="p">,</span> <span class="n">connection</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="ow">not</span> <span class="n">should_create</span><span class="p">(</span><span class="n">ddl</span><span class="p">,</span> <span class="n">target</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">users</span><span class="p">,</span>
<span class="s2">"after_create"</span><span class="p">,</span>
<span class="n">DDL</span><span class="p">(</span>
<span class="s2">"ALTER TABLE users ADD CONSTRAINT "</span>
<span class="s2">"cst_user_name_length CHECK (length(user_name) >= 8)"</span>
<span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">callable_</span><span class="o">=</span><span class="n">should_create</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">users</span><span class="p">,</span>
<span class="s2">"before_drop"</span><span class="p">,</span>
<span class="n">DDL</span><span class="p">(</span>
<span class="s2">"ALTER TABLE users DROP CONSTRAINT cst_user_name_length"</span>
<span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">callable_</span><span class="o">=</span><span class="n">should_drop</span><span class="p">)</span>
<span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="n">users</span><span class="o">.</span><span class="n">create</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>CREATE TABLE users (
user_id SERIAL NOT NULL,
user_name VARCHAR(40) NOT NULL,
PRIMARY KEY (user_id)
)
select conname from pg_constraint where conname='cst_user_name_length'
ALTER TABLE users ADD CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8)</div>
<a href='#' class='sql_link'>sql</a><span class="n">users</span><span class="o">.</span><span class="n">drop</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>select conname from pg_constraint where conname='cst_user_name_length'
ALTER TABLE users DROP CONSTRAINT cst_user_name_length
DROP TABLE users</div></pre></div>
</div>
</div>
<div class="section" id="using-the-built-in-ddlelement-classes">
<h2>Using the built-in DDLElement Classes<a class="headerlink" href="#using-the-built-in-ddlelement-classes" title="Permalink to this headline">¶</a></h2>
<p>The <code class="docutils literal notranslate"><span class="pre">sqlalchemy.schema</span></code> package contains SQL expression constructs that
provide DDL expressions. For example, to produce a <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> statement:</p>
<div class="highlight-python+sql notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.schema</span> <span class="kn">import</span> <span class="n">CreateTable</span>
<a href='#' class='sql_link'>sql</a><span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">CreateTable</span><span class="p">(</span><span class="n">mytable</span><span class="p">))</span>
<div class='popup_sql'>CREATE TABLE mytable (
col1 INTEGER,
col2 INTEGER,
col3 INTEGER,
col4 INTEGER,
col5 INTEGER,
col6 INTEGER
)</div></pre></div>
</div>
<p>Above, the <a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateTable</span></code></a> construct works like any
other expression construct (such as <code class="docutils literal notranslate"><span class="pre">select()</span></code>, <code class="docutils literal notranslate"><span class="pre">table.insert()</span></code>, etc.).
All of SQLAlchemy’s DDL oriented constructs are subclasses of
the <a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLElement</span></code></a> base class; this is the base of all the
objects corresponding to CREATE and DROP as well as ALTER,
not only in SQLAlchemy but in Alembic Migrations as well.
A full reference of available constructs is in <a class="reference internal" href="#schema-api-ddl"><span class="std std-ref">DDL Expression Constructs API</span></a>.</p>
<p>User-defined DDL constructs may also be created as subclasses of
<a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLElement</span></code></a> itself. The documentation in
<a class="reference internal" href="compiler.html"><span class="std std-ref">Custom SQL Constructs and Compilation Extension</span></a> has several examples of this.</p>
<p>The event-driven DDL system described in the previous section
<a class="reference internal" href="#schema-ddl-sequences"><span class="std std-ref">Controlling DDL Sequences</span></a> is available with other <a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLElement</span></code></a>
objects as well. However, when dealing with the built-in constructs
such as <a class="reference internal" href="#sqlalchemy.schema.CreateIndex" title="sqlalchemy.schema.CreateIndex"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateIndex</span></code></a>, <a class="reference internal" href="#sqlalchemy.schema.CreateSequence" title="sqlalchemy.schema.CreateSequence"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateSequence</span></code></a>, etc, the event
system is of <strong>limited</strong> use, as methods like <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.create" title="sqlalchemy.schema.Table.create"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.create()</span></code></a> and
<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.create_all()</span></code></a> will invoke these constructs unconditionally.
In a future SQLAlchemy release, the DDL event system including conditional
execution will taken into account for built-in constructs that currently
invoke in all cases.</p>
<p>We can illustrate an event-driven
example with the <a class="reference internal" href="#sqlalchemy.schema.AddConstraint" title="sqlalchemy.schema.AddConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">AddConstraint</span></code></a> and <a class="reference internal" href="#sqlalchemy.schema.DropConstraint" title="sqlalchemy.schema.DropConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">DropConstraint</span></code></a>
constructs, as the event-driven system will work for CHECK and UNIQUE
constraints, using these as we did in our previous example of
<a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DDLElement.execute_if()</span></code></a>:</p>
<div class="highlight-python+sql notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">should_create</span><span class="p">(</span><span class="n">ddl</span><span class="p">,</span> <span class="n">target</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="n">row</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="s2">"select conname from pg_constraint where conname='</span><span class="si">%s</span><span class="s2">'"</span> <span class="o">%</span>
<span class="n">ddl</span><span class="o">.</span><span class="n">element</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">scalar</span><span class="p">()</span>
<span class="k">return</span> <span class="ow">not</span> <span class="nb">bool</span><span class="p">(</span><span class="n">row</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">should_drop</span><span class="p">(</span><span class="n">ddl</span><span class="p">,</span> <span class="n">target</span><span class="p">,</span> <span class="n">connection</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="ow">not</span> <span class="n">should_create</span><span class="p">(</span><span class="n">ddl</span><span class="p">,</span> <span class="n">target</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">users</span><span class="p">,</span>
<span class="s2">"after_create"</span><span class="p">,</span>
<span class="n">AddConstraint</span><span class="p">(</span><span class="n">constraint</span><span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">callable_</span><span class="o">=</span><span class="n">should_create</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">users</span><span class="p">,</span>
<span class="s2">"before_drop"</span><span class="p">,</span>
<span class="n">DropConstraint</span><span class="p">(</span><span class="n">constraint</span><span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">callable_</span><span class="o">=</span><span class="n">should_drop</span><span class="p">)</span>
<span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="n">users</span><span class="o">.</span><span class="n">create</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>CREATE TABLE users (
user_id SERIAL NOT NULL,
user_name VARCHAR(40) NOT NULL,
PRIMARY KEY (user_id)
)
select conname from pg_constraint where conname='cst_user_name_length'
ALTER TABLE users ADD CONSTRAINT cst_user_name_length CHECK (length(user_name) >= 8)</div>
<a href='#' class='sql_link'>sql</a><span class="n">users</span><span class="o">.</span><span class="n">drop</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>select conname from pg_constraint where conname='cst_user_name_length'
ALTER TABLE users DROP CONSTRAINT cst_user_name_length
DROP TABLE users</div></pre></div>
</div>
<p>While the above example is against the built-in <a class="reference internal" href="#sqlalchemy.schema.AddConstraint" title="sqlalchemy.schema.AddConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">AddConstraint</span></code></a>
and <a class="reference internal" href="#sqlalchemy.schema.DropConstraint" title="sqlalchemy.schema.DropConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">DropConstraint</span></code></a> objects, the main usefulness of DDL events
for now remains focused on the use of the <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a> construct itself,
as well as with user-defined subclasses of <a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLElement</span></code></a> that aren’t
already part of the <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.create_all()</span></code></a>, <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.create" title="sqlalchemy.schema.Table.create"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.create()</span></code></a>,
and corresponding “drop” processes.</p>
</div>
<div class="section" id="ddl-expression-constructs-api">
<span id="schema-api-ddl"></span><h2>DDL Expression Constructs API<a class="headerlink" href="#ddl-expression-constructs-api" title="Permalink to this headline">¶</a></h2>
<dl class="function">
<dt id="sqlalchemy.schema.sort_tables">
<code class="descclassname">sqlalchemy.schema.</code><code class="descname">sort_tables</code><span class="sig-paren">(</span><em>tables</em>, <em>skip_fn=None</em>, <em>extra_dependencies=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.sort_tables" title="Permalink to this definition">¶</a></dt>
<dd><p>sort a collection of <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> objects based on dependency.</p>
<p>This is a dependency-ordered sort which will emit <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>
objects such that they will follow their dependent <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> objects.
Tables are dependent on another based on the presence of
<a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a> objects as well as explicit dependencies
added by <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.add_is_dependent_on" title="sqlalchemy.schema.Table.add_is_dependent_on"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.add_is_dependent_on()</span></code></a>.</p>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>The <a class="reference internal" href="#sqlalchemy.schema.sort_tables" title="sqlalchemy.schema.sort_tables"><code class="xref py py-func docutils literal notranslate"><span class="pre">sort_tables()</span></code></a> function cannot by itself accommodate
automatic resolution of dependency cycles between tables, which
are usually caused by mutually dependent foreign key constraints.
To resolve these cycles, either the
<a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint.params.use_alter" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">ForeignKeyConstraint.use_alter</span></code></a> parameter may be applied
to those constraints, or use the
<code class="xref py py-func docutils literal notranslate"><span class="pre">sql.sort_tables_and_constraints()</span></code> function which will break
out foreign key constraints involved in cycles separately.</p>
</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.schema.sort_tables.params.tables"></span><strong>tables</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.sort_tables.params.tables">¶</a> – a sequence of <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> objects.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.sort_tables.params.skip_fn"></span><strong>skip_fn</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.sort_tables.params.skip_fn">¶</a> – optional callable which will be passed a
<a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> object; if it returns True, this
constraint will not be considered as a dependency. Note this is
<strong>different</strong> from the same parameter in
<a class="reference internal" href="#sqlalchemy.schema.sort_tables_and_constraints" title="sqlalchemy.schema.sort_tables_and_constraints"><code class="xref py py-func docutils literal notranslate"><span class="pre">sort_tables_and_constraints()</span></code></a>, which is
instead passed the owning <a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a> object.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.sort_tables.params.extra_dependencies"></span><strong>extra_dependencies</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.sort_tables.params.extra_dependencies">¶</a> – a sequence of 2-tuples of tables which will
also be considered as dependent on each other.</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.schema.sort_tables_and_constraints" title="sqlalchemy.schema.sort_tables_and_constraints"><code class="xref py py-func docutils literal notranslate"><span class="pre">sort_tables_and_constraints()</span></code></a></p>
<p><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.sorted_tables()</span></code> - uses this function to sort</p>
</div>
</dd></dl>
<dl class="function">
<dt id="sqlalchemy.schema.sort_tables_and_constraints">
<code class="descclassname">sqlalchemy.schema.</code><code class="descname">sort_tables_and_constraints</code><span class="sig-paren">(</span><em>tables</em>, <em>filter_fn=None</em>, <em>extra_dependencies=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.sort_tables_and_constraints" title="Permalink to this definition">¶</a></dt>
<dd><p>sort a collection of <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> / <a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a>
objects.</p>
<p>This is a dependency-ordered sort which will emit tuples of
<code class="docutils literal notranslate"><span class="pre">(Table,</span> <span class="pre">[ForeignKeyConstraint,</span> <span class="pre">...])</span></code> such that each
<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> follows its dependent <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> objects.
Remaining <a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a> objects that are separate due to
dependency rules not satisfied by the sort are emitted afterwards
as <code class="docutils literal notranslate"><span class="pre">(None,</span> <span class="pre">[ForeignKeyConstraint</span> <span class="pre">...])</span></code>.</p>
<p>Tables are dependent on another based on the presence of
<a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a> objects, explicit dependencies
added by <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table.add_is_dependent_on" title="sqlalchemy.schema.Table.add_is_dependent_on"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Table.add_is_dependent_on()</span></code></a>, as well as dependencies
stated here using the <a class="reference internal" href="#sqlalchemy.schema.sort_tables_and_constraints.params.skip_fn" title="sqlalchemy.schema.sort_tables_and_constraints"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">skip_fn</span></code></a>
and/or <a class="reference internal" href="#sqlalchemy.schema.sort_tables_and_constraints.params.extra_dependencies" title="sqlalchemy.schema.sort_tables_and_constraints"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">extra_dependencies</span></code></a>
parameters.</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.schema.sort_tables_and_constraints.params.tables"></span><strong>tables</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.sort_tables_and_constraints.params.tables">¶</a> – a sequence of <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> objects.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.sort_tables_and_constraints.params.filter_fn"></span><strong>filter_fn</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.sort_tables_and_constraints.params.filter_fn">¶</a> – optional callable which will be passed a
<a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a> object, and returns a value based on
whether this constraint should definitely be included or excluded as
an inline constraint, or neither. If it returns False, the constraint
will definitely be included as a dependency that cannot be subject
to ALTER; if True, it will <strong>only</strong> be included as an ALTER result at
the end. Returning None means the constraint is included in the
table-based result unless it is detected as part of a dependency cycle.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.sort_tables_and_constraints.params.extra_dependencies"></span><strong>extra_dependencies</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.sort_tables_and_constraints.params.extra_dependencies">¶</a> – a sequence of 2-tuples of tables which will
also be considered as dependent on each other.</p></li>
</ul>
</dd>
</dl>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#sqlalchemy.schema.sort_tables" title="sqlalchemy.schema.sort_tables"><code class="xref py py-func docutils literal notranslate"><span class="pre">sort_tables()</span></code></a></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.DDLElement">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">DDLElement</code><a class="headerlink" href="#sqlalchemy.schema.DDLElement" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.sql.expression.Executable</span></code></a>, <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._DDLCompiles</span></code></p>
<p>Base class for DDL expression constructs.</p>
<p>This class is the base for the general purpose <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a> class,
as well as the various create/drop clause constructs such as
<a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateTable</span></code></a>, <a class="reference internal" href="#sqlalchemy.schema.DropTable" title="sqlalchemy.schema.DropTable"><code class="xref py py-class docutils literal notranslate"><span class="pre">DropTable</span></code></a>, <a class="reference internal" href="#sqlalchemy.schema.AddConstraint" title="sqlalchemy.schema.AddConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">AddConstraint</span></code></a>,
etc.</p>
<p><a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLElement</span></code></a> integrates closely with SQLAlchemy events,
introduced in <a class="reference internal" href="event.html"><span class="std std-ref">Events</span></a>. An instance of one is
itself an event receiving callable:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">users</span><span class="p">,</span>
<span class="s1">'after_create'</span><span class="p">,</span>
<span class="n">AddConstraint</span><span class="p">(</span><span class="n">constraint</span><span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="s1">'postgresql'</span><span class="p">)</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="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a></p>
<p><a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLEvents</span></code></a></p>
<p><a class="reference internal" href="event.html"><span class="std std-ref">Events</span></a></p>
<p><a class="reference internal" href="#schema-ddl-sequences"><span class="std std-ref">Controlling DDL Sequences</span></a></p>
</div>
<dl class="method">
<dt id="sqlalchemy.schema.DDLElement.__call__">
<code class="descname">__call__</code><span class="sig-paren">(</span><em>target</em>, <em>bind</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DDLElement.__call__" title="Permalink to this definition">¶</a></dt>
<dd><p>Execute the DDL as a ddl_listener.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.schema.DDLElement.against">
<code class="descname">against</code><span class="sig-paren">(</span><em>target</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DDLElement.against" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a copy of this DDL against a specific schema item.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.schema.DDLElement.bind">
<code class="descname">bind</code><a class="headerlink" href="#sqlalchemy.schema.DDLElement.bind" title="Permalink to this definition">¶</a></dt>
<dd><p>Returns the <a class="reference internal" href="connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> or <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> to
which this <a class="reference internal" href="selectable.html#sqlalchemy.sql.expression.Executable" title="sqlalchemy.sql.expression.Executable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Executable</span></code></a> is bound, or None if none found.</p>
<p>This is a traversal which checks locally, then
checks among the “from” clauses of associated objects
until a bound engine or connection is found.</p>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.schema.DDLElement.callable_">
<code class="descname">callable_</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.schema.DDLElement.callable_" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.schema.DDLElement.dialect">
<code class="descname">dialect</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.schema.DDLElement.dialect" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>
<dl class="method">
<dt id="sqlalchemy.schema.DDLElement.execute">
<code class="descname">execute</code><span class="sig-paren">(</span><em>bind=None</em>, <em>target=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DDLElement.execute" title="Permalink to this definition">¶</a></dt>
<dd><p>Execute this DDL immediately.</p>
<p>Executes the DDL statement in isolation using the supplied
<a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connectable</span></code></a> or
<a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connectable</span></code></a> assigned to the <code class="docutils literal notranslate"><span class="pre">.bind</span></code>
property, if not supplied. If the DDL has a conditional <code class="docutils literal notranslate"><span class="pre">on</span></code>
criteria, it will be invoked with None as the event.</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.schema.DDLElement.execute.params.bind"></span><strong>bind</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDLElement.execute.params.bind">¶</a> – Optional, an <code class="docutils literal notranslate"><span class="pre">Engine</span></code> or <code class="docutils literal notranslate"><span class="pre">Connection</span></code>. If not supplied, a valid
<a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connectable</span></code></a> must be present in the
<code class="docutils literal notranslate"><span class="pre">.bind</span></code> property.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.DDLElement.execute.params.target"></span><strong>target</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDLElement.execute.params.target">¶</a> – Optional, defaults to None. The target SchemaItem for the
execute call. Will be passed to the <code class="docutils literal notranslate"><span class="pre">on</span></code> callable if any,
and may also provide string expansion data for the
statement. See <code class="docutils literal notranslate"><span class="pre">execute_at</span></code> for more information.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.schema.DDLElement.execute_at">
<code class="descname">execute_at</code><span class="sig-paren">(</span><em>event_name</em>, <em>target</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DDLElement.execute_at" title="Permalink to this definition">¶</a></dt>
<dd><p>Link execution of this DDL to the DDL lifecycle of a SchemaItem.</p>
<div class="deprecated">
<p><span class="versionmodified deprecated">Deprecated since version 0.7: </span>The <a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_at" title="sqlalchemy.schema.DDLElement.execute_at"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DDLElement.execute_at()</span></code></a> method is deprecated and will be removed in a future release. Please use the <a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLEvents</span></code></a> listener interface in conjunction with the <a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DDLElement.execute_if()</span></code></a> method.</p>
</div>
<p>Links this <code class="docutils literal notranslate"><span class="pre">DDLElement</span></code> to a <code class="docutils literal notranslate"><span class="pre">Table</span></code> or <code class="docutils literal notranslate"><span class="pre">MetaData</span></code> instance,
executing it when that schema item is created or dropped. The DDL
statement will be executed using the same Connection and transactional
context as the Table create/drop itself. The <code class="docutils literal notranslate"><span class="pre">.bind</span></code> property of
this statement is ignored.</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.schema.DDLElement.execute_at.params.event"></span><strong>event</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDLElement.execute_at.params.event">¶</a> – One of the events defined in the schema item’s <code class="docutils literal notranslate"><span class="pre">.ddl_events</span></code>;
e.g. ‘before-create’, ‘after-create’, ‘before-drop’ or ‘after-drop’</p></li>
<li><p><span class="target" id="sqlalchemy.schema.DDLElement.execute_at.params.target"></span><strong>target</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDLElement.execute_at.params.target">¶</a> – The Table or MetaData instance for which this DDLElement will
be associated with.</p></li>
</ul>
</dd>
</dl>
<p>A DDLElement instance can be linked to any number of schema items.</p>
<p><code class="docutils literal notranslate"><span class="pre">execute_at</span></code> builds on the <code class="docutils literal notranslate"><span class="pre">append_ddl_listener</span></code> interface of
<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a> and <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> objects.</p>
<p>Caveat: Creating or dropping a Table in isolation will also trigger
any DDL set to <code class="docutils literal notranslate"><span class="pre">execute_at</span></code> that Table’s MetaData. This may change
in a future release.</p>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.schema.DDLElement.execute_if">
<code class="descname">execute_if</code><span class="sig-paren">(</span><em>dialect=None</em>, <em>callable_=None</em>, <em>state=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DDLElement.execute_if" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a callable that will execute this
DDLElement conditionally.</p>
<p>Used to provide a wrapper for event listening:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span>
<span class="n">metadata</span><span class="p">,</span>
<span class="s1">'before_create'</span><span class="p">,</span>
<span class="n">DDL</span><span class="p">(</span><span class="s2">"my_ddl"</span><span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="s1">'postgresql'</span><span class="p">)</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.schema.DDLElement.execute_if.params.dialect"></span><strong>dialect</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDLElement.execute_if.params.dialect">¶</a> – <p>May be a string, tuple or a callable
predicate. If a string, it will be compared to the name of the
executing database dialect:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DDL</span><span class="p">(</span><span class="s1">'something'</span><span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="s1">'postgresql'</span><span class="p">)</span></pre></div>
</div>
<p>If a tuple, specifies multiple dialect names:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DDL</span><span class="p">(</span><span class="s1">'something'</span><span class="p">)</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="p">(</span><span class="s1">'postgresql'</span><span class="p">,</span> <span class="s1">'mysql'</span><span class="p">))</span></pre></div>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.schema.DDLElement.execute_if.params.callable_"></span><strong>callable_</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDLElement.execute_if.params.callable_">¶</a> – <p>A callable, which will be invoked with
four positional arguments as well as optional keyword
arguments:</p>
<blockquote>
<div><dl class="field-list simple">
<dt class="field-odd">ddl</dt>
<dd class="field-odd"><p>This DDL element.</p>
</dd>
<dt class="field-even">target</dt>
<dd class="field-even"><p>The <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> or <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a> object which is the
target of this event. May be None if the DDL is executed
explicitly.</p>
</dd>
<dt class="field-odd">bind</dt>
<dd class="field-odd"><p>The <a class="reference internal" href="connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connection</span></code></a> being used for DDL execution</p>
</dd>
<dt class="field-even">tables</dt>
<dd class="field-even"><p>Optional keyword argument - a list of Table objects which are to
be created/ dropped within a MetaData.create_all() or drop_all()
method call.</p>
</dd>
<dt class="field-odd">state</dt>
<dd class="field-odd"><p>Optional keyword argument - will be the <code class="docutils literal notranslate"><span class="pre">state</span></code> argument
passed to this function.</p>
</dd>
<dt class="field-even">checkfirst</dt>
<dd class="field-even"><p>Keyword argument, will be True if the ‘checkfirst’ flag was
set during the call to <code class="docutils literal notranslate"><span class="pre">create()</span></code>, <code class="docutils literal notranslate"><span class="pre">create_all()</span></code>,
<code class="docutils literal notranslate"><span class="pre">drop()</span></code>, <code class="docutils literal notranslate"><span class="pre">drop_all()</span></code>.</p>
</dd>
</dl>
</div></blockquote>
<p>If the callable returns a true value, the DDL statement will be
executed.</p>
</p></li>
<li><p><span class="target" id="sqlalchemy.schema.DDLElement.execute_if.params.state"></span><strong>state</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDLElement.execute_if.params.state">¶</a> – any value which will be passed to the callable_
as the <code class="docutils literal notranslate"><span class="pre">state</span></code> keyword argument.</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLEvents</span></code></a></p>
<p><a class="reference internal" href="event.html"><span class="std std-ref">Events</span></a></p>
</div>
</dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.schema.DDLElement.on">
<code class="descname">on</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.schema.DDLElement.on" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.schema.DDLElement.target">
<code class="descname">target</code><em class="property"> = None</em><a class="headerlink" href="#sqlalchemy.schema.DDLElement.target" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.DDL">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">DDL</code><span class="sig-paren">(</span><em>statement</em>, <em>on=None</em>, <em>context=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DDL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema.DDLElement</span></code></a></p>
<p>A literal DDL statement.</p>
<p>Specifies literal SQL DDL to be executed by the database. DDL objects
function as DDL event listeners, and can be subscribed to those events
listed in <a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLEvents</span></code></a>, using either <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> or
<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a> objects as targets. Basic templating support allows
a single DDL instance to handle repetitive tasks for multiple tables.</p>
<p>Examples:</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">event</span><span class="p">,</span> <span class="n">DDL</span>
<span class="n">tbl</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'users'</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">'uid'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span><span class="n">tbl</span><span class="p">,</span> <span class="s1">'before_create'</span><span class="p">,</span> <span class="n">DDL</span><span class="p">(</span><span class="s1">'DROP TRIGGER users_trigger'</span><span class="p">))</span>
<span class="n">spow</span> <span class="o">=</span> <span class="n">DDL</span><span class="p">(</span><span class="s1">'ALTER TABLE </span><span class="si">%(table)s</span><span class="s1"> SET secretpowers TRUE'</span><span class="p">)</span>
<span class="n">event</span><span class="o">.</span><span class="n">listen</span><span class="p">(</span><span class="n">tbl</span><span class="p">,</span> <span class="s1">'after_create'</span><span class="p">,</span> <span class="n">spow</span><span class="o">.</span><span class="n">execute_if</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="s1">'somedb'</span><span class="p">))</span>
<span class="n">drop_spow</span> <span class="o">=</span> <span class="n">DDL</span><span class="p">(</span><span class="s1">'ALTER TABLE users SET secretpowers FALSE'</span><span class="p">)</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">drop_spow</span><span class="p">)</span></pre></div>
</div>
<p>When operating on Table events, the following <code class="docutils literal notranslate"><span class="pre">statement</span></code>
string substitutions are available:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">%</span><span class="p">(</span><span class="n">table</span><span class="p">)</span><span class="n">s</span> <span class="o">-</span> <span class="n">the</span> <span class="n">Table</span> <span class="n">name</span><span class="p">,</span> <span class="k">with</span> <span class="nb">any</span> <span class="n">required</span> <span class="n">quoting</span> <span class="n">applied</span>
<span class="o">%</span><span class="p">(</span><span class="n">schema</span><span class="p">)</span><span class="n">s</span> <span class="o">-</span> <span class="n">the</span> <span class="n">schema</span> <span class="n">name</span><span class="p">,</span> <span class="k">with</span> <span class="nb">any</span> <span class="n">required</span> <span class="n">quoting</span> <span class="n">applied</span>
<span class="o">%</span><span class="p">(</span><span class="n">fullname</span><span class="p">)</span><span class="n">s</span> <span class="o">-</span> <span class="n">the</span> <span class="n">Table</span> <span class="n">name</span> <span class="n">including</span> <span class="n">schema</span><span class="p">,</span> <span class="n">quoted</span> <span class="k">if</span> <span class="n">needed</span></pre></div>
</div>
<p>The DDL’s “context”, if any, will be combined with the standard
substitutions noted above. Keys present in the context will override
the standard substitutions.</p>
<dl class="method">
<dt id="sqlalchemy.schema.DDL.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>statement</em>, <em>on=None</em>, <em>context=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DDL.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a DDL statement.</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.schema.DDL.params.statement"></span><strong>statement</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDL.params.statement">¶</a> – <p>A string or unicode string to be executed. Statements will be
processed with Python’s string formatting operator. See the
<code class="docutils literal notranslate"><span class="pre">context</span></code> argument and the <code class="docutils literal notranslate"><span class="pre">execute_at</span></code> method.</p>
<p>A literal ‘%’ in a statement must be escaped as ‘%%’.</p>
<p>SQL bind parameters are not available in DDL statements.</p>
</p></li>
<li><p><span class="target" id="sqlalchemy.schema.DDL.params.on"></span><strong>on</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDL.params.on">¶</a> – <div class="deprecated">
<p><span class="versionmodified deprecated">Deprecated since version 0.7: </span>The <a class="reference internal" href="#sqlalchemy.schema.DDL.params.on" title="sqlalchemy.schema.DDL"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">DDL.on</span></code></a> parameter is deprecated and will be
removed in a future release. Please refer to
<a class="reference internal" href="#sqlalchemy.schema.DDLElement.execute_if" title="sqlalchemy.schema.DDLElement.execute_if"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DDLElement.execute_if()</span></code></a>.</p>
</div>
<p>Optional filtering criteria. May be a string, tuple or a callable
predicate. If a string, it will be compared to the name of the
executing database dialect:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DDL</span><span class="p">(</span><span class="s1">'something'</span><span class="p">,</span> <span class="n">on</span><span class="o">=</span><span class="s1">'postgresql'</span><span class="p">)</span></pre></div>
</div>
<p>If a tuple, specifies multiple dialect names:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DDL</span><span class="p">(</span><span class="s1">'something'</span><span class="p">,</span> <span class="n">on</span><span class="o">=</span><span class="p">(</span><span class="s1">'postgresql'</span><span class="p">,</span> <span class="s1">'mysql'</span><span class="p">))</span></pre></div>
</div>
<p>If a callable, it will be invoked with four positional arguments
as well as optional keyword arguments:</p>
<blockquote>
<div><dl class="field-list simple">
<dt class="field-odd">ddl</dt>
<dd class="field-odd"><p>This DDL element.</p>
</dd>
<dt class="field-even">event</dt>
<dd class="field-even"><p>The name of the event that has triggered this DDL, such as
‘after-create’ Will be None if the DDL is executed explicitly.</p>
</dd>
<dt class="field-odd">target</dt>
<dd class="field-odd"><p>The <code class="docutils literal notranslate"><span class="pre">Table</span></code> or <code class="docutils literal notranslate"><span class="pre">MetaData</span></code> object which is the target of
this event. May be None if the DDL is executed explicitly.</p>
</dd>
<dt class="field-even">connection</dt>
<dd class="field-even"><p>The <code class="docutils literal notranslate"><span class="pre">Connection</span></code> being used for DDL execution</p>
</dd>
<dt class="field-odd">tables</dt>
<dd class="field-odd"><p>Optional keyword argument - a list of Table objects which are to
be created/ dropped within a MetaData.create_all() or drop_all()
method call.</p>
</dd>
</dl>
</div></blockquote>
<p>If the callable returns a true value, the DDL statement will be
executed.</p>
</p></li>
<li><p><span class="target" id="sqlalchemy.schema.DDL.params.context"></span><strong>context</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDL.params.context">¶</a> – Optional dictionary, defaults to None. These values will be
available for use in string substitutions on the DDL statement.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.DDL.params.bind"></span><strong>bind</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.DDL.params.bind">¶</a> – Optional. A <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><code class="xref py py-class docutils literal notranslate"><span class="pre">Connectable</span></code></a>, used by
default when <code class="docutils literal notranslate"><span class="pre">execute()</span></code> is invoked without a bind argument.</p></li>
</ul>
</dd>
</dl>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="events.html#sqlalchemy.events.DDLEvents" title="sqlalchemy.events.DDLEvents"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDLEvents</span></code></a></p>
<p><a class="reference internal" href="event.html"><span class="std std-ref">Events</span></a></p>
</div>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema._CreateDropBase">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">_CreateDropBase</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema._CreateDropBase" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema.DDLElement" title="sqlalchemy.schema.DDLElement"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema.DDLElement</span></code></a></p>
<p>Base class for DDL constructs that represent CREATE and DROP or
equivalents.</p>
<p>The common theme of _CreateDropBase is a single
<code class="docutils literal notranslate"><span class="pre">element</span></code> attribute which refers to the element
to be created or dropped.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.CreateTable">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">CreateTable</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em>, <em>include_foreign_key_constraints=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.CreateTable" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a CREATE TABLE statement.</p>
<dl class="method">
<dt id="sqlalchemy.schema.CreateTable.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em>, <em>include_foreign_key_constraints=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.CreateTable.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a <a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateTable</span></code></a> construct.</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.schema.CreateTable.params.element"></span><strong>element</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.CreateTable.params.element">¶</a> – a <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> that’s the subject
of the CREATE</p></li>
<li><p><span class="target" id="sqlalchemy.schema.CreateTable.params.on"></span><strong>on</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.CreateTable.params.on">¶</a> – See the description for ‘on’ in <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a>.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.CreateTable.params.bind"></span><strong>bind</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.CreateTable.params.bind">¶</a> – See the description for ‘bind’ in <a class="reference internal" href="#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a>.</p></li>
<li><p><span class="target" id="sqlalchemy.schema.CreateTable.params.include_foreign_key_constraints"></span><strong>include_foreign_key_constraints</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.schema.CreateTable.params.include_foreign_key_constraints">¶</a> – <p>optional sequence of
<a class="reference internal" href="constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a> objects that will be included
inline within the CREATE construct; if omitted, all foreign key
constraints that do not specify use_alter=True are included.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.DropTable">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">DropTable</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DropTable" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a DROP TABLE statement.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.CreateColumn">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">CreateColumn</code><span class="sig-paren">(</span><em>element</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.CreateColumn" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._DDLCompiles</span></code></p>
<p>Represent 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> as rendered in a CREATE TABLE statement,
via the <a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateTable</span></code></a> construct.</p>
<p>This is provided to support custom column DDL within the generation
of CREATE TABLE statements, by using the
compiler extension documented in <a class="reference internal" href="compiler.html"><span class="std std-ref">Custom SQL Constructs and Compilation Extension</span></a>
to extend <a class="reference internal" href="#sqlalchemy.schema.CreateColumn" title="sqlalchemy.schema.CreateColumn"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateColumn</span></code></a>.</p>
<p>Typical integration is to examine the incoming <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>
object, and to redirect compilation if a particular flag or condition
is found:</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">schema</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="nd">@compiles</span><span class="p">(</span><span class="n">schema</span><span class="o">.</span><span class="n">CreateColumn</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">compile</span><span class="p">(</span><span class="n">element</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="n">column</span> <span class="o">=</span> <span class="n">element</span><span class="o">.</span><span class="n">element</span>
<span class="k">if</span> <span class="s2">"special"</span> <span class="ow">not</span> <span class="ow">in</span> <span class="n">column</span><span class="o">.</span><span class="n">info</span><span class="p">:</span>
<span class="k">return</span> <span class="n">compiler</span><span class="o">.</span><span class="n">visit_create_column</span><span class="p">(</span><span class="n">element</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span>
<span class="n">text</span> <span class="o">=</span> <span class="s2">"</span><span class="si">%s</span><span class="s2"> SPECIAL DIRECTIVE </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="p">(</span>
<span class="n">column</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">type_compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">column</span><span class="o">.</span><span class="n">type</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">default</span> <span class="o">=</span> <span class="n">compiler</span><span class="o">.</span><span class="n">get_column_default_string</span><span class="p">(</span><span class="n">column</span><span class="p">)</span>
<span class="k">if</span> <span class="n">default</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">text</span> <span class="o">+=</span> <span class="s2">" DEFAULT "</span> <span class="o">+</span> <span class="n">default</span>
<span class="k">if</span> <span class="ow">not</span> <span class="n">column</span><span class="o">.</span><span class="n">nullable</span><span class="p">:</span>
<span class="n">text</span> <span class="o">+=</span> <span class="s2">" NOT NULL"</span>
<span class="k">if</span> <span class="n">column</span><span class="o">.</span><span class="n">constraints</span><span class="p">:</span>
<span class="n">text</span> <span class="o">+=</span> <span class="s2">" "</span><span class="o">.</span><span class="n">join</span><span class="p">(</span>
<span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">const</span><span class="p">)</span>
<span class="k">for</span> <span class="n">const</span> <span class="ow">in</span> <span class="n">column</span><span class="o">.</span><span class="n">constraints</span><span class="p">)</span>
<span class="k">return</span> <span class="n">text</span></pre></div>
</div>
<p>The above construct can be applied to a <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> as follows:</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">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">Integer</span><span class="p">,</span> <span class="n">String</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">schema</span>
<span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'mytable'</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">'x'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">info</span><span class="o">=</span><span class="p">{</span><span class="s2">"special"</span><span class="p">:</span><span class="kc">True</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">'y'</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">'z'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">20</span><span class="p">),</span> <span class="n">info</span><span class="o">=</span><span class="p">{</span><span class="s2">"special"</span><span class="p">:</span><span class="kc">True</span><span class="p">})</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></pre></div>
</div>
<p>Above, the directives we’ve added to the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column.info" title="sqlalchemy.schema.Column.info"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Column.info</span></code></a> collection
will be detected by our custom compilation scheme:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">mytable</span> <span class="p">(</span>
<span class="n">x</span> <span class="n">SPECIAL</span> <span class="n">DIRECTIVE</span> <span class="n">INTEGER</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">,</span>
<span class="n">y</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span>
<span class="n">z</span> <span class="n">SPECIAL</span> <span class="n">DIRECTIVE</span> <span class="n">VARCHAR</span><span class="p">(</span><span class="mi">20</span><span class="p">),</span>
<span class="n">PRIMARY</span> <span class="n">KEY</span> <span class="p">(</span><span class="n">x</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.schema.CreateColumn" title="sqlalchemy.schema.CreateColumn"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateColumn</span></code></a> construct can also be used to skip certain
columns when producing a <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code>. This is accomplished by
creating a compilation rule that conditionally returns <code class="docutils literal notranslate"><span class="pre">None</span></code>.
This is essentially how to produce the same effect as using the
<code class="docutils literal notranslate"><span class="pre">system=True</span></code> argument on <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>, which marks a column
as an implicitly-present “system” column.</p>
<p>For example, suppose we wish to produce a <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> which skips
rendering of the PostgreSQL <code class="docutils literal notranslate"><span class="pre">xmin</span></code> column against the PostgreSQL
backend, but on other backends does render it, in anticipation of a
triggered rule. A conditional compilation rule could skip this name only
on PostgreSQL:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.schema</span> <span class="k">import</span> <span class="n">CreateColumn</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">CreateColumn</span><span class="p">,</span> <span class="s2">"postgresql"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">skip_xmin</span><span class="p">(</span><span class="n">element</span><span class="p">,</span> <span class="n">compiler</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="k">if</span> <span class="n">element</span><span class="o">.</span><span class="n">element</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s1">'xmin'</span><span class="p">:</span>
<span class="k">return</span> <span class="kc">None</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">return</span> <span class="n">compiler</span><span class="o">.</span><span class="n">visit_create_column</span><span class="p">(</span><span class="n">element</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span>
<span class="n">my_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'mytable'</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">'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">'xmin'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>Above, a <a class="reference internal" href="#sqlalchemy.schema.CreateTable" title="sqlalchemy.schema.CreateTable"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateTable</span></code></a> construct will generate a <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code>
which only includes the <code class="docutils literal notranslate"><span class="pre">id</span></code> column in the string; the <code class="docutils literal notranslate"><span class="pre">xmin</span></code> column
will be omitted, but only against the PostgreSQL backend.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.CreateSequence">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">CreateSequence</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.CreateSequence" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a CREATE SEQUENCE statement.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.DropSequence">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">DropSequence</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DropSequence" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a DROP SEQUENCE statement.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.CreateIndex">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">CreateIndex</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.CreateIndex" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a CREATE INDEX statement.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.DropIndex">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">DropIndex</code><span class="sig-paren">(</span><em>element</em>, <em>on=None</em>, <em>bind=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DropIndex" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a DROP INDEX statement.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.AddConstraint">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">AddConstraint</code><span class="sig-paren">(</span><em>element</em>, <em>*args</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.AddConstraint" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent an ALTER TABLE ADD CONSTRAINT statement.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.DropConstraint">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">DropConstraint</code><span class="sig-paren">(</span><em>element</em>, <em>cascade=False</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DropConstraint" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent an ALTER TABLE DROP CONSTRAINT statement.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.CreateSchema">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">CreateSchema</code><span class="sig-paren">(</span><em>name</em>, <em>quote=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.CreateSchema" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a CREATE SCHEMA statement.</p>
<p>The argument here is the string name of the schema.</p>
<dl class="method">
<dt id="sqlalchemy.schema.CreateSchema.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>name</em>, <em>quote=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.CreateSchema.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a new <a class="reference internal" href="#sqlalchemy.schema.CreateSchema" title="sqlalchemy.schema.CreateSchema"><code class="xref py py-class docutils literal notranslate"><span class="pre">CreateSchema</span></code></a> construct.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.schema.DropSchema">
<em class="property">class </em><code class="descclassname">sqlalchemy.schema.</code><code class="descname">DropSchema</code><span class="sig-paren">(</span><em>name</em>, <em>quote=None</em>, <em>cascade=False</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DropSchema" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="#sqlalchemy.schema._CreateDropBase" title="sqlalchemy.schema._CreateDropBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.schema._CreateDropBase</span></code></a></p>
<p>Represent a DROP SCHEMA statement.</p>
<p>The argument here is the string name of the schema.</p>
<dl class="method">
<dt id="sqlalchemy.schema.DropSchema.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>name</em>, <em>quote=None</em>, <em>cascade=False</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.schema.DropSchema.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a new <a class="reference internal" href="#sqlalchemy.schema.DropSchema" title="sqlalchemy.schema.DropSchema"><code class="xref py py-class docutils literal notranslate"><span class="pre">DropSchema</span></code></a> construct.</p>
</dd></dl>
</dd></dl>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="constraints.html" title="previous chapter">Defining Constraints and Indexes</a>
Next:
<a href="types.html" title="next chapter">Column and Data Types</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>
