<!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>
MetaData / Schema
—
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="Frequently Asked Questions" href="index.html" />
<link rel="next" title="SQL Expressions" href="sqlexpressions.html" />
<link rel="prev" title="Connections / Engines" href="connections.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="Frequently Asked Questions">Frequently Asked Questions</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="connections.html">Connections / Engines</a></span></li>
<li class="selected"><span class="link-container"><strong>MetaData / Schema</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all">My program is hanging when I say <code class="docutils literal notranslate"><span class="pre">table.drop()</span></code> / <code class="docutils literal notranslate"><span class="pre">metadata.drop_all()</span></code></a></span></li>
<li><span class="link-container"><a class="reference external" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality">Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#how-can-i-sort-table-objects-in-order-of-their-dependency">How can I sort Table objects in order of their dependency?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string">How can I get the CREATE TABLE/ DROP TABLE output as a string?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations">How can I subclass Table/Column to provide certain behaviors/configurations?</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="sqlexpressions.html">SQL Expressions</a></span></li>
<li><span class="link-container"><a class="reference external" href="ormconfiguration.html">ORM Configuration</a></span></li>
<li><span class="link-container"><a class="reference external" href="performance.html">Performance</a></span></li>
<li><span class="link-container"><a class="reference external" href="sessions.html">Sessions / Queries</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="metadata-schema">
<h1>MetaData / Schema<a class="headerlink" href="#metadata-schema" title="Permalink to this headline">¶</a></h1>
<div class="contents faq local topic" id="contents">
<ul class="simple">
<li><p><a class="reference internal" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all" id="id1">My program is hanging when I say <code class="docutils literal notranslate"><span class="pre">table.drop()</span></code> / <code class="docutils literal notranslate"><span class="pre">metadata.drop_all()</span></code></a></p></li>
<li><p><a class="reference internal" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality" id="id2">Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?</a></p></li>
<li><p><a class="reference internal" href="#how-can-i-sort-table-objects-in-order-of-their-dependency" id="id3">How can I sort Table objects in order of their dependency?</a></p></li>
<li><p><a class="reference internal" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string" id="id4">How can I get the CREATE TABLE/ DROP TABLE output as a string?</a></p></li>
<li><p><a class="reference internal" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations" id="id5">How can I subclass Table/Column to provide certain behaviors/configurations?</a></p></li>
</ul>
</div>
<div class="section" id="my-program-is-hanging-when-i-say-table-drop-metadata-drop-all">
<h2>My program is hanging when I say <code class="docutils literal notranslate"><span class="pre">table.drop()</span></code> / <code class="docutils literal notranslate"><span class="pre">metadata.drop_all()</span></code><a class="headerlink" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all" title="Permalink to this headline">¶</a></h2>
<p>This usually corresponds to two conditions: 1. using PostgreSQL, which is really
strict about table locks, and 2. you have a connection still open which
contains locks on the table and is distinct from the connection being used for
the DROP statement. Heres the most minimal version of the pattern:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">result</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="n">mytable</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">drop</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<p>Above, a connection pool connection is still checked out; furthermore, the
result object above also maintains a link to this connection. If
“implicit execution” is used, the result will hold this connection opened until
the result object is closed or all rows are exhausted.</p>
<p>The call to <code class="docutils literal notranslate"><span class="pre">mytable.drop(engine)</span></code> attempts to emit DROP TABLE on a second
connection procured from the <a class="reference internal" href="../core/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> which will lock.</p>
<p>The solution is to close out all connections before emitting DROP TABLE:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">result</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="n">mytable</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="c1"># fully read result sets</span>
<span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c1"># close connections</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
<span class="c1"># now locks are removed</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">drop</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality">
<h2>Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?<a class="headerlink" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality" title="Permalink to this headline">¶</a></h2>
<p>General ALTER support isn’t present in SQLAlchemy directly. For special DDL
on an ad-hoc basis, the <a class="reference internal" href="../core/ddl.html#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><code class="xref py py-class docutils literal notranslate"><span class="pre">DDL</span></code></a> and related constructs can be used.
See <span class="xref std std-doc">core/ddl</span> for a discussion on this subject.</p>
<p>A more comprehensive option is to use schema migration tools, such as Alembic
or SQLAlchemy-Migrate; see <a class="reference internal" href="../core/metadata.html#schema-migrations"><span class="std std-ref">Altering Schemas through Migrations</span></a> for discussion on this.</p>
</div>
<div class="section" id="how-can-i-sort-table-objects-in-order-of-their-dependency">
<h2>How can I sort Table objects in order of their dependency?<a class="headerlink" href="#how-can-i-sort-table-objects-in-order-of-their-dependency" title="Permalink to this headline">¶</a></h2>
<p>This is available via the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.sorted_tables" title="sqlalchemy.schema.MetaData.sorted_tables"><code class="xref py py-attr docutils literal notranslate"><span class="pre">MetaData.sorted_tables</span></code></a> function:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="c1"># ... add Table objects to metadata</span>
<span class="n">ti</span> <span class="o">=</span> <span class="n">metadata</span><span class="o">.</span><span class="n">sorted_tables</span><span class="p">:</span>
<span class="k">for</span> <span class="n">t</span> <span class="ow">in</span> <span class="n">ti</span><span class="p">:</span>
<span class="nb">print</span><span class="p">(</span><span class="n">t</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="how-can-i-get-the-create-table-drop-table-output-as-a-string">
<h2>How can I get the CREATE TABLE/ DROP TABLE output as a string?<a class="headerlink" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string" title="Permalink to this headline">¶</a></h2>
<p>Modern SQLAlchemy has clause constructs which represent DDL operations. These
can be rendered to strings like any other SQL expression:</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">CreateTable</span>
<span class="nb">print</span><span class="p">(</span><span class="n">CreateTable</span><span class="p">(</span><span class="n">mytable</span><span class="p">))</span></pre></div>
</div>
<p>To get the string specific to a certain engine:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nb">print</span><span class="p">(</span><span class="n">CreateTable</span><span class="p">(</span><span class="n">mytable</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">engine</span><span class="p">))</span></pre></div>
</div>
<p>There’s also a special form of <a class="reference internal" href="../core/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> that can let you dump an entire
metadata creation sequence, using this recipe:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">dump</span><span class="p">(</span><span class="n">sql</span><span class="p">,</span> <span class="o">*</span><span class="n">multiparams</span><span class="p">,</span> <span class="o">**</span><span class="n">params</span><span class="p">):</span>
<span class="nb">print</span><span class="p">(</span><span class="n">sql</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">engine</span><span class="o">.</span><span class="n">dialect</span><span class="p">))</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'postgresql://'</span><span class="p">,</span> <span class="n">strategy</span><span class="o">=</span><span class="s1">'mock'</span><span class="p">,</span> <span class="n">executor</span><span class="o">=</span><span class="n">dump</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">engine</span><span class="p">,</span> <span class="n">checkfirst</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span></pre></div>
</div>
<p>The <a class="reference external" href="https://alembic.sqlalchemy.org">Alembic</a> tool also supports
an “offline” SQL generation mode that renders database migrations as SQL scripts.</p>
</div>
<div class="section" id="how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations">
<h2>How can I subclass Table/Column to provide certain behaviors/configurations?<a class="headerlink" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations" title="Permalink to this headline">¶</a></h2>
<p><a class="reference internal" href="../core/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="../core/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> are not good targets for direct subclassing.
However, there are simple ways to get on-construction behaviors using creation
functions, and behaviors related to the linkages between schema objects such as
constraint conventions or naming conventions using attachment events.
An example of many of these
techniques can be seen at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions">Naming Conventions</a>.</p>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="connections.html" title="previous chapter">Connections / Engines</a>
Next:
<a href="sqlexpressions.html" title="next chapter">SQL Expressions</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1.
</div>
</div>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '1.2.19',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/language_data.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
b0b6ffab06cbeede296e36ce94734bf8
>
files
>
798
