<!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>
MySQL
—
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="Dialects" href="index.html" />
<link rel="next" title="SQLite" href="sqlite.html" />
<link rel="prev" title="PostgreSQL" href="postgresql.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="Dialects">Dialects</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="postgresql.html">PostgreSQL</a></span></li>
<li class="selected"><span class="link-container"><strong>MySQL</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql">Support for the MySQL database.</a></span></li>
<li><span class="link-container"><a class="reference external" href="#supported-versions-and-features">Supported Versions and Features</a></span></li>
<li><span class="link-container"><a class="reference external" href="#connection-timeouts-and-disconnects">Connection Timeouts and Disconnects</a></span></li>
<li><span class="link-container"><a class="reference external" href="#create-table-arguments-including-storage-engines">CREATE TABLE arguments including Storage Engines</a></span></li>
<li><span class="link-container"><a class="reference external" href="#case-sensitivity-and-table-reflection">Case Sensitivity and Table Reflection</a></span></li>
<li><span class="link-container"><a class="reference external" href="#transaction-isolation-level">Transaction Isolation Level</a></span></li>
<li><span class="link-container"><a class="reference external" href="#auto-increment-behavior">AUTO_INCREMENT Behavior</a></span></li>
<li><span class="link-container"><a class="reference external" href="#server-side-cursors">Server Side Cursors</a></span></li>
<li><span class="link-container"><a class="reference external" href="#unicode">Unicode</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#charset-selection">Charset Selection</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dealing-with-binary-data-warnings-and-unicode">Dealing with Binary Data Warnings and Unicode</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#ansi-quoting-style">ANSI Quoting Style</a></span></li>
<li><span class="link-container"><a class="reference external" href="#mysql-sql-extensions">MySQL SQL Extensions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#insert-on-duplicate-key-update-upsert">INSERT…ON DUPLICATE KEY UPDATE (Upsert)</a></span></li>
<li><span class="link-container"><a class="reference external" href="#rowcount-support">rowcount Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#cast-support">CAST Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#mysql-specific-index-options">MySQL Specific Index Options</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#index-length">Index Length</a></span></li>
<li><span class="link-container"><a class="reference external" href="#index-prefixes">Index Prefixes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#index-types">Index Types</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#mysql-foreign-keys">MySQL Foreign Keys</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#foreign-key-arguments-to-avoid">Foreign Key Arguments to Avoid</a></span></li>
<li><span class="link-container"><a class="reference external" href="#reflection-of-foreign-key-constraints">Reflection of Foreign Key Constraints</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#mysql-unique-constraints-and-reflection">MySQL Unique Constraints and Reflection</a></span></li>
<li><span class="link-container"><a class="reference external" href="#timestamp-columns-and-null">TIMESTAMP Columns and NULL</a></span></li>
<li><span class="link-container"><a class="reference external" href="#mysql-data-types">MySQL Data Types</a></span></li>
<li><span class="link-container"><a class="reference external" href="#mysql-dml-constructs">MySQL DML Constructs</a></span></li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.mysqldb">mysqlclient (fork of MySQL-Python)</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-mysqldb-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-mysqldb-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#driver-status">Driver Status</a></span></li>
<li><span class="link-container"><a class="reference external" href="#mysqldb-unicode">Unicode</a></span></li>
<li><span class="link-container"><a class="reference external" href="#using-mysqldb-with-google-cloud-sql">Using MySQLdb with Google Cloud SQL</a></span></li>
<li><span class="link-container"><a class="reference external" href="#id3">Server Side Cursors</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.pymysql">PyMySQL</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-pymysql-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-pymysql-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#id4">Unicode</a></span></li>
<li><span class="link-container"><a class="reference external" href="#mysql-python-compatibility">MySQL-Python Compatibility</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.mysqlconnector">MySQL-Connector</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-mysqlconnector-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-mysqlconnector-connect">Connecting</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.cymysql">cymysql</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-cymysql-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-cymysql-connect">Connecting</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.oursql">OurSQL</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-oursql-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-oursql-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#id5">Unicode</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.gaerdbms">Google App Engine</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-gaerdbms-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-gaerdbms-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#pooling">Pooling</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.pyodbc">pyodbc</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-pyodbc-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-pyodbc-connect">Connecting</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.mysql.zxjdbc">zxjdbc</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-zxjdbc-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-mysql-zxjdbc-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#character-sets">Character Sets</a></span></li>
</ul>
</li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="sqlite.html">SQLite</a></span></li>
<li><span class="link-container"><a class="reference external" href="oracle.html">Oracle</a></span></li>
<li><span class="link-container"><a class="reference external" href="mssql.html">Microsoft SQL Server</a></span></li>
<li><span class="link-container"><a class="reference external" href="firebird.html">Firebird</a></span></li>
<li><span class="link-container"><a class="reference external" href="sybase.html">Sybase</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.mysql.base">
<span id="mysql"></span><span id="mysql-toplevel"></span><h1>MySQL<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-mysql">
<p>Support for the MySQL database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-mysql" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.mysqldb">mysqlclient (maintained fork of MySQL-Python)</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.pymysql">PyMySQL</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.mysqlconnector">MySQL Connector/Python</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.cymysql">CyMySQL</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.oursql">OurSQL</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.gaerdbms">Google Cloud SQL</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.pyodbc">PyODBC</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.mysql.zxjdbc">zxjdbc for Jython</a></p></li>
</ul>
</p>
</div>
<div class="section" id="supported-versions-and-features">
<h2>Supported Versions and Features<a class="headerlink" href="#supported-versions-and-features" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy supports MySQL starting with version 4.1 through modern releases.
However, no heroic measures are taken to work around major missing
SQL features - if your server version does not support sub-selects, for
example, they won’t work in SQLAlchemy either.</p>
<p>See the official MySQL documentation for detailed information about features
supported in any given server release.</p>
</div>
<div class="section" id="connection-timeouts-and-disconnects">
<span id="mysql-connection-timeouts"></span><h2>Connection Timeouts and Disconnects<a class="headerlink" href="#connection-timeouts-and-disconnects" title="Permalink to this headline">¶</a></h2>
<p>MySQL features an automatic connection close behavior, for connections that
have been idle for a fixed period of time, defaulting to eight hours.
To circumvent having this issue, use
the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.pool_recycle" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.pool_recycle</span></code></a> option which ensures that
a connection will be discarded and replaced with a new one if it has been
present in the pool for a fixed number of seconds:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'mysql+mysqldb://...'</span><span class="p">,</span> <span class="n">pool_recycle</span><span class="o">=</span><span class="mi">3600</span><span class="p">)</span></pre></div>
</div>
<p>For more comprehensive disconnect detection of pooled connections, including
accommodation of server restarts and network issues, a pre-ping approach may
be employed. See <a class="reference internal" href="../core/pooling.html#pool-disconnects"><span class="std std-ref">Dealing with Disconnects</span></a> for current approaches.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/pooling.html#pool-disconnects"><span class="std std-ref">Dealing with Disconnects</span></a> - Background on several techniques for dealing
with timed out connections as well as database restarts.</p>
</div>
</div>
<div class="section" id="create-table-arguments-including-storage-engines">
<span id="mysql-storage-engines"></span><h2>CREATE TABLE arguments including Storage Engines<a class="headerlink" href="#create-table-arguments-including-storage-engines" title="Permalink to this headline">¶</a></h2>
<p>MySQL’s CREATE TABLE syntax includes a wide array of special options,
including <code class="docutils literal notranslate"><span class="pre">ENGINE</span></code>, <code class="docutils literal notranslate"><span class="pre">CHARSET</span></code>, <code class="docutils literal notranslate"><span class="pre">MAX_ROWS</span></code>, <code class="docutils literal notranslate"><span class="pre">ROW_FORMAT</span></code>,
<code class="docutils literal notranslate"><span class="pre">INSERT_METHOD</span></code>, and many more.
To accommodate the rendering of these arguments, specify the form
<code class="docutils literal notranslate"><span class="pre">mysql_argument_name="value"</span></code>. For example, to specify a table with
<code class="docutils literal notranslate"><span class="pre">ENGINE</span></code> of <code class="docutils literal notranslate"><span class="pre">InnoDB</span></code>, <code class="docutils literal notranslate"><span class="pre">CHARSET</span></code> of <code class="docutils literal notranslate"><span class="pre">utf8mb4</span></code>, and <code class="docutils literal notranslate"><span class="pre">KEY_BLOCK_SIZE</span></code>
of <code class="docutils literal notranslate"><span class="pre">1024</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">'data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">32</span><span class="p">)),</span>
<span class="n">mysql_engine</span><span class="o">=</span><span class="s1">'InnoDB'</span><span class="p">,</span>
<span class="n">mysql_charset</span><span class="o">=</span><span class="s1">'utf8mb4'</span><span class="p">,</span>
<span class="n">mysql_key_block_size</span><span class="o">=</span><span class="s2">"1024"</span>
<span class="p">)</span></pre></div>
</div>
<p>The MySQL dialect will normally transfer any keyword specified as
<code class="docutils literal notranslate"><span class="pre">mysql_keyword_name</span></code> to be rendered as <code class="docutils literal notranslate"><span class="pre">KEYWORD_NAME</span></code> in the
<code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> statement. A handful of these names will render with a space
instead of an underscore; to support this, the MySQL dialect has awareness of
these particular names, which include <code class="docutils literal notranslate"><span class="pre">DATA</span> <span class="pre">DIRECTORY</span></code>
(e.g. <code class="docutils literal notranslate"><span class="pre">mysql_data_directory</span></code>), <code class="docutils literal notranslate"><span class="pre">CHARACTER</span> <span class="pre">SET</span></code> (e.g.
<code class="docutils literal notranslate"><span class="pre">mysql_character_set</span></code>) and <code class="docutils literal notranslate"><span class="pre">INDEX</span> <span class="pre">DIRECTORY</span></code> (e.g.
<code class="docutils literal notranslate"><span class="pre">mysql_index_directory</span></code>).</p>
<p>The most common argument is <code class="docutils literal notranslate"><span class="pre">mysql_engine</span></code>, which refers to the storage
engine for the table. Historically, MySQL server installations would default
to <code class="docutils literal notranslate"><span class="pre">MyISAM</span></code> for this value, although newer versions may be defaulting
to <code class="docutils literal notranslate"><span class="pre">InnoDB</span></code>. The <code class="docutils literal notranslate"><span class="pre">InnoDB</span></code> engine is typically preferred for its support
of transactions and foreign keys.</p>
<p>A <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> that is created in a MySQL database with a storage engine
of <code class="docutils literal notranslate"><span class="pre">MyISAM</span></code> will be essentially non-transactional, meaning any
INSERT/UPDATE/DELETE statement referring to this table will be invoked as
autocommit. It also will have no support for foreign key constraints; while
the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> statement accepts foreign key options, when using the
<code class="docutils literal notranslate"><span class="pre">MyISAM</span></code> storage engine these arguments are discarded. Reflecting such a
table will also produce no foreign key constraint information.</p>
<p>For fully atomic transactions as well as support for foreign key
constraints, all participating <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> statements must specify a
transactional engine, which in the vast majority of cases is <code class="docutils literal notranslate"><span class="pre">InnoDB</span></code>.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference external" href="http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html">The InnoDB Storage Engine</a> -
on the MySQL website.</p>
</div>
</div>
<div class="section" id="case-sensitivity-and-table-reflection">
<h2>Case Sensitivity and Table Reflection<a class="headerlink" href="#case-sensitivity-and-table-reflection" title="Permalink to this headline">¶</a></h2>
<p>MySQL has inconsistent support for case-sensitive identifier
names, basing support on specific details of the underlying
operating system. However, it has been observed that no matter
what case sensitivity behavior is present, the names of tables in
foreign key declarations are <em>always</em> received from the database
as all-lower case, making it impossible to accurately reflect a
schema where inter-related tables use mixed-case identifier names.</p>
<p>Therefore it is strongly advised that table names be declared as
all lower case both within SQLAlchemy as well as on the MySQL
database itself, especially if database reflection features are
to be used.</p>
</div>
<div class="section" id="transaction-isolation-level">
<span id="mysql-isolation-level"></span><h2>Transaction Isolation Level<a class="headerlink" href="#transaction-isolation-level" title="Permalink to this headline">¶</a></h2>
<p>All MySQL dialects support setting of transaction isolation level both via a
dialect-specific parameter <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.isolation_level" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.isolation_level</span></code></a> accepted
by <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>, as well as the
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options.params.isolation_level" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.isolation_level</span></code></a> argument as passed to
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execution_options()</span></code></a>. This feature works by issuing the
command <code class="docutils literal notranslate"><span class="pre">SET</span> <span class="pre">SESSION</span> <span class="pre">TRANSACTION</span> <span class="pre">ISOLATION</span> <span class="pre">LEVEL</span> <span class="pre"><level></span></code> for each new
connection. For the special AUTOCOMMIT isolation level, DBAPI-specific
techniques are used.</p>
<p>To set isolation level using <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mysql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s2">"READ UNCOMMITTED"</span>
<span class="p">)</span></pre></div>
</div>
<p>To set using per-connection execution options:</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">connection</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execution_options</span><span class="p">(</span>
<span class="n">isolation_level</span><span class="o">=</span><span class="s2">"READ COMMITTED"</span>
<span class="p">)</span></pre></div>
</div>
<p>Valid values for <code class="docutils literal notranslate"><span class="pre">isolation_level</span></code> include:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">READ</span> <span class="pre">COMMITTED</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">REPEATABLE</span> <span class="pre">READ</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">SERIALIZABLE</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">AUTOCOMMIT</span></code></p></li>
</ul>
<p>The special <code class="docutils literal notranslate"><span class="pre">AUTOCOMMIT</span></code> value makes use of the various “autocommit”
attributes provided by specific DBAPIs, and is currently supported by
MySQLdb, MySQL-Client, MySQL-Connector Python, and PyMySQL. Using it,
the MySQL connection will return true for the value of
<code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">@@autocommit;</span></code>.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1: </span>- added support for the AUTOCOMMIT isolation level.</p>
</div>
</div>
<div class="section" id="auto-increment-behavior">
<h2>AUTO_INCREMENT Behavior<a class="headerlink" href="#auto-increment-behavior" title="Permalink to this headline">¶</a></h2>
<p>When creating tables, SQLAlchemy will automatically set <code class="docutils literal notranslate"><span class="pre">AUTO_INCREMENT</span></code> on
the first <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">Integer</span></code></a> primary key column which is not marked as a
foreign key:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">t</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="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s1">'mytable_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="gp">... </span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">t</span><span class="o">.</span><span class="n">create</span><span class="p">()</span>
<span class="go">CREATE TABLE mytable (</span>
<span class="go"> id INTEGER NOT NULL AUTO_INCREMENT,</span>
<span class="go"> PRIMARY KEY (id)</span>
<span class="go">)</span></pre></div>
</div>
<p>You can disable this behavior by passing <code class="docutils literal notranslate"><span class="pre">False</span></code> to the
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.autoincrement" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">autoincrement</span></code></a> argument of <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>. This flag
can also be used to enable auto-increment on a secondary column in a
multi-column key for some storage engines:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">'gid'</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">autoincrement</span><span class="o">=</span><span class="kc">False</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="p">)</span></pre></div>
</div>
</div>
<div class="section" id="server-side-cursors">
<span id="mysql-ss-cursors"></span><h2>Server Side Cursors<a class="headerlink" href="#server-side-cursors" title="Permalink to this headline">¶</a></h2>
<p>Server-side cursor support is available for the MySQLdb and PyMySQL dialects.
From a MySQL point of view this means that the <code class="docutils literal notranslate"><span class="pre">MySQLdb.cursors.SSCursor</span></code> or
<code class="docutils literal notranslate"><span class="pre">pymysql.cursors.SSCursor</span></code> class is used when building up the cursor which
will receive results. The most typical way of invoking this feature is via the
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options.params.stream_results" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Connection.execution_options.stream_results</span></code></a> connection execution
option. Server side cursors can also be enabled for all SELECT statements
unconditionally by passing <code class="docutils literal notranslate"><span class="pre">server_side_cursors=True</span></code> to
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.4: </span>- added server-side cursor support.</p>
</div>
</div>
<div class="section" id="unicode">
<span id="mysql-unicode"></span><h2>Unicode<a class="headerlink" href="#unicode" title="Permalink to this headline">¶</a></h2>
<div class="section" id="charset-selection">
<h3>Charset Selection<a class="headerlink" href="#charset-selection" title="Permalink to this headline">¶</a></h3>
<p>Most MySQL DBAPIs offer the option to set the client character set for
a connection. This is typically delivered using the <code class="docutils literal notranslate"><span class="pre">charset</span></code> parameter
in the URL, such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4"</span><span class="p">)</span></pre></div>
</div>
<p>This charset is the <strong>client character set</strong> for the connection. Some
MySQL DBAPIs will default this to a value such as <code class="docutils literal notranslate"><span class="pre">latin1</span></code>, and some
will make use of the <code class="docutils literal notranslate"><span class="pre">default-character-set</span></code> setting in the <code class="docutils literal notranslate"><span class="pre">my.cnf</span></code>
file as well. Documentation for the DBAPI in use should be consulted
for specific behavior.</p>
<p>The encoding used for Unicode has traditionally been <code class="docutils literal notranslate"><span class="pre">'utf8'</span></code>. However,
for MySQL versions 5.5.3 on forward, a new MySQL-specific encoding
<code class="docutils literal notranslate"><span class="pre">'utf8mb4'</span></code> has been introduced, and as of MySQL 8.0 a warning is emitted
by the server if plain <code class="docutils literal notranslate"><span class="pre">utf8</span></code> is specified within any server-side
directives, replaced with <code class="docutils literal notranslate"><span class="pre">utf8mb3</span></code>. The rationale for this new encoding
is due to the fact that MySQL’s legacy utf-8 encoding only supports
codepoints up to three bytes instead of four. Therefore,
when communicating with a MySQL database
that includes codepoints more than three bytes in size,
this new charset is preferred, if supported by both the database as well
as the client DBAPI, as in:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4"</span><span class="p">)</span></pre></div>
</div>
<p>All modern DBAPIs should support the <code class="docutils literal notranslate"><span class="pre">utf8mb4</span></code> charset.</p>
<p>In order to use <code class="docutils literal notranslate"><span class="pre">utf8mb4</span></code> encoding for a schema that was created with legacy
<code class="docutils literal notranslate"><span class="pre">utf8</span></code>, changes to the MySQL schema and/or server configuration may be
required.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference external" href="http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html">The utf8mb4 Character Set </a> - in the MySQL documentation</p>
</div>
</div>
<div class="section" id="dealing-with-binary-data-warnings-and-unicode">
<span id="mysql-binary-introducer"></span><h3>Dealing with Binary Data Warnings and Unicode<a class="headerlink" href="#dealing-with-binary-data-warnings-and-unicode" title="Permalink to this headline">¶</a></h3>
<p>MySQL versions 5.6, 5.7 and later (not MariaDB at the time of this writing) now
emit a warning when attempting to pass binary data to the database, while a
character set encoding is also in place, when the binary data itself is not
valid for that encoding:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">default</span><span class="o">.</span><span class="n">py</span><span class="p">:</span><span class="mi">509</span><span class="p">:</span> <span class="ne">Warning</span><span class="p">:</span> <span class="p">(</span><span class="mi">1300</span><span class="p">,</span> <span class="s2">"Invalid utf8mb4 character string:</span>
<span class="s1">'F9876A'</span><span class="s2">")</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">statement</span><span class="p">,</span> <span class="n">parameters</span><span class="p">)</span></pre></div>
</div>
<p>This warning is due to the fact that the MySQL client library is attempting to
interpret the binary string as a unicode object even if a datatype such
as <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a> is in use. To resolve this, the SQL statement requires
a binary “character set introducer” be present before any non-NULL value
that renders like this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">INSERT</span> <span class="n">INTO</span> <span class="n">table</span> <span class="p">(</span><span class="n">data</span><span class="p">)</span> <span class="n">VALUES</span> <span class="p">(</span><span class="n">_binary</span> <span class="o">%</span><span class="n">s</span><span class="p">)</span></pre></div>
</div>
<p>These character set introducers are provided by the DBAPI driver, assuming the
use of mysqlclient or PyMySQL (both of which are recommended). Add the query
string parameter <code class="docutils literal notranslate"><span class="pre">binary_prefix=true</span></code> to the URL to repair this warning:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># mysqlclient</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true"</span><span class="p">)</span>
<span class="c1"># PyMySQL</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true"</span><span class="p">)</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">binary_prefix</span></code> flag may or may not be supported by other MySQL drivers.</p>
<p>SQLAlchemy itself cannot render this <code class="docutils literal notranslate"><span class="pre">_binary</span></code> prefix reliably, as it does
not work with the NULL value, which is valid to be sent as a bound parameter.
As the MySQL driver renders parameters directly into the SQL string, it’s the
most efficient place for this additional keyword to be passed.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference external" href="https://dev.mysql.com/doc/refman/5.7/en/charset-introducer.html">Character set introducers</a> - on the MySQL website</p>
</div>
</div>
</div>
<div class="section" id="ansi-quoting-style">
<h2>ANSI Quoting Style<a class="headerlink" href="#ansi-quoting-style" title="Permalink to this headline">¶</a></h2>
<p>MySQL features two varieties of identifier “quoting style”, one using
backticks and the other using quotes, e.g. <code class="docutils literal notranslate"><span class="pre">`some_identifier`</span></code> vs.
<code class="docutils literal notranslate"><span class="pre">"some_identifier"</span></code>. All MySQL dialects detect which version
is in use by checking the value of <code class="docutils literal notranslate"><span class="pre">sql_mode</span></code> when a connection is first
established with a particular <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>. This quoting style comes
into play when rendering table and column names as well as when reflecting
existing database structures. The detection is entirely automatic and
no special configuration is needed to use either quoting style.</p>
</div>
<div class="section" id="mysql-sql-extensions">
<h2>MySQL SQL Extensions<a class="headerlink" href="#mysql-sql-extensions" title="Permalink to this headline">¶</a></h2>
<p>Many of the MySQL SQL extensions are handled through SQLAlchemy’s generic
function and operator support:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">table</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">password</span><span class="o">==</span><span class="n">func</span><span class="o">.</span><span class="n">md5</span><span class="p">(</span><span class="s1">'plaintext'</span><span class="p">))</span>
<span class="n">table</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">username</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'regexp'</span><span class="p">)(</span><span class="s1">'^[a-d]'</span><span class="p">))</span></pre></div>
</div>
<p>And of course any valid MySQL statement can be executed as a string as well.</p>
<p>Some limited direct support for MySQL extensions to SQL is currently
available.</p>
<ul>
<li><p>INSERT..ON DUPLICATE KEY UPDATE: See
<a class="reference internal" href="#mysql-insert-on-duplicate-key-update"><span class="std std-ref">INSERT…ON DUPLICATE KEY UPDATE (Upsert)</span></a></p></li>
<li><p>SELECT pragma:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">select</span><span class="p">(</span><span class="o">...</span><span class="p">,</span> <span class="n">prefixes</span><span class="o">=</span><span class="p">[</span><span class="s1">'HIGH_PRIORITY'</span><span class="p">,</span> <span class="s1">'SQL_SMALL_RESULT'</span><span class="p">])</span></pre></div>
</div>
</li>
<li><p>UPDATE with LIMIT:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">update</span><span class="p">(</span><span class="o">...</span><span class="p">,</span> <span class="n">mysql_limit</span><span class="o">=</span><span class="mi">10</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
</div>
<div class="section" id="insert-on-duplicate-key-update-upsert">
<span id="mysql-insert-on-duplicate-key-update"></span><h2>INSERT…ON DUPLICATE KEY UPDATE (Upsert)<a class="headerlink" href="#insert-on-duplicate-key-update-upsert" title="Permalink to this headline">¶</a></h2>
<p>MySQL allows “upserts” (update or insert)
of rows into a table via the <code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">DUPLICATE</span> <span class="pre">KEY</span> <span class="pre">UPDATE</span></code> clause of the
<code class="docutils literal notranslate"><span class="pre">INSERT</span></code> statement. A candidate row will only be inserted if that row does
not match an existing primary or unique key in the table; otherwise, an UPDATE
will be performed. The statement allows for separate specification of the
values to INSERT versus the values for UPDATE.</p>
<p>SQLAlchemy provides <code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">DUPLICATE</span> <span class="pre">KEY</span> <span class="pre">UPDATE</span></code> support via the MySQL-specific
<a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.insert" title="sqlalchemy.dialects.mysql.dml.insert"><code class="xref py py-func docutils literal notranslate"><span class="pre">mysql.dml.insert()</span></code></a> function, which provides
the generative method <a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update" title="sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">on_duplicate_key_update()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mysql</span> <span class="k">import</span> <span class="n">insert</span>
<span class="n">insert_stmt</span> <span class="o">=</span> <span class="n">insert</span><span class="p">(</span><span class="n">my_table</span><span class="p">)</span><span class="o">.</span><span class="n">values</span><span class="p">(</span>
<span class="nb">id</span><span class="o">=</span><span class="s1">'some_existing_id'</span><span class="p">,</span>
<span class="n">data</span><span class="o">=</span><span class="s1">'inserted value'</span><span class="p">)</span>
<span class="n">on_duplicate_key_stmt</span> <span class="o">=</span> <span class="n">insert_stmt</span><span class="o">.</span><span class="n">on_duplicate_key_update</span><span class="p">(</span>
<span class="n">data</span><span class="o">=</span><span class="n">insert_stmt</span><span class="o">.</span><span class="n">inserted</span><span class="o">.</span><span class="n">data</span><span class="p">,</span>
<span class="n">status</span><span class="o">=</span><span class="s1">'U'</span>
<span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">on_duplicate_key_stmt</span><span class="p">)</span></pre></div>
</div>
<p>Unlike PostgreSQL’s “ON CONFLICT” phrase, the “ON DUPLICATE KEY UPDATE”
phrase will always match on any primary key or unique key, and will always
perform an UPDATE if there’s a match; there are no options for it to raise
an error or to skip performing an UPDATE.</p>
<p><code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">DUPLICATE</span> <span class="pre">KEY</span> <span class="pre">UPDATE</span></code> is used to perform an update of the already
existing row, using any combination of new values as well as values
from the proposed insertion. These values are specified using
keyword arguments passed to the
<a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update" title="sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">on_duplicate_key_update()</span></code></a>
given column key values (usually the name of the column, unless it
specifies <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.key" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.key</span></code></a>) as keys and literal or SQL expressions
as values:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">on_duplicate_key_stmt</span> <span class="o">=</span> <span class="n">insert_stmt</span><span class="o">.</span><span class="n">on_duplicate_key_update</span><span class="p">(</span>
<span class="n">data</span><span class="o">=</span><span class="s2">"some data"</span>
<span class="n">updated_at</span><span class="o">=</span><span class="n">func</span><span class="o">.</span><span class="n">current_timestamp</span><span class="p">()</span>
<span class="p">)</span></pre></div>
</div>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update" title="sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Insert.on_duplicate_key_update()</span></code></a> method does <strong>not</strong> take into
account Python-side default UPDATE values or generation functions, e.g.
e.g. those specified using <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.onupdate" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.onupdate</span></code></a>.
These values will not be exercised for an ON DUPLICATE KEY style of UPDATE,
unless they are manually specified explicitly in the parameters.</p>
</div>
<p>In order to refer to the proposed insertion row, the special alias
<a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert.inserted" title="sqlalchemy.dialects.mysql.dml.Insert.inserted"><code class="xref py py-attr docutils literal notranslate"><span class="pre">inserted</span></code></a> is available as an attribute on
the <a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert" title="sqlalchemy.dialects.mysql.dml.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">mysql.dml.Insert</span></code></a> object; this object is a
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.ColumnCollection" title="sqlalchemy.sql.expression.ColumnCollection"><code class="xref py py-class docutils literal notranslate"><span class="pre">ColumnCollection</span></code></a> which contains all columns of the target
table:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mysql</span> <span class="k">import</span> <span class="n">insert</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">insert</span><span class="p">(</span><span class="n">my_table</span><span class="p">)</span><span class="o">.</span><span class="n">values</span><span class="p">(</span>
<span class="nb">id</span><span class="o">=</span><span class="s1">'some_id'</span><span class="p">,</span>
<span class="n">data</span><span class="o">=</span><span class="s1">'inserted value'</span><span class="p">,</span>
<span class="n">author</span><span class="o">=</span><span class="s1">'jlh'</span><span class="p">)</span>
<span class="n">do_update_stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">on_duplicate_key_update</span><span class="p">(</span>
<span class="n">data</span><span class="o">=</span><span class="s2">"updated value"</span><span class="p">,</span>
<span class="n">author</span><span class="o">=</span><span class="n">stmt</span><span class="o">.</span><span class="n">inserted</span><span class="o">.</span><span class="n">author</span>
<span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">do_update_stmt</span><span class="p">)</span></pre></div>
</div>
<p>When rendered, the “inserted” namespace will produce the expression
<code class="docutils literal notranslate"><span class="pre">VALUES(<columnname>)</span></code>.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2: </span>Added support for MySQL ON DUPLICATE KEY UPDATE clause</p>
</div>
</div>
<div class="section" id="rowcount-support">
<h2>rowcount Support<a class="headerlink" href="#rowcount-support" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy standardizes the DBAPI <code class="docutils literal notranslate"><span class="pre">cursor.rowcount</span></code> attribute to be the
usual definition of “number of rows matched by an UPDATE or DELETE” statement.
This is in contradiction to the default setting on most MySQL DBAPI drivers,
which is “number of rows actually modified/deleted”. For this reason, the
SQLAlchemy MySQL dialects always add the <code class="docutils literal notranslate"><span class="pre">constants.CLIENT.FOUND_ROWS</span></code>
flag, or whatever is equivalent for the target dialect, upon connection.
This setting is currently hardcoded.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/connections.html#sqlalchemy.engine.ResultProxy.rowcount" title="sqlalchemy.engine.ResultProxy.rowcount"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ResultProxy.rowcount</span></code></a></p>
</div>
</div>
<div class="section" id="cast-support">
<h2>CAST Support<a class="headerlink" href="#cast-support" title="Permalink to this headline">¶</a></h2>
<p>MySQL documents the CAST operator as available in version 4.0.2. When using
the SQLAlchemy <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><code class="xref py py-func docutils literal notranslate"><span class="pre">cast()</span></code></a> function, SQLAlchemy
will not render the CAST token on MySQL before this version, based on server
version detection, instead rendering the internal expression directly.</p>
<p>CAST may still not be desirable on an early MySQL version post-4.0.2, as it
didn’t add all datatype support until 4.1.1. If your application falls into
this narrow area, the behavior of CAST can be controlled using the
<a class="reference internal" href="../core/compiler.html"><span class="std std-ref">Custom SQL Constructs and Compilation Extension</span></a> system, as per the recipe below:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.sql.expression</span> <span class="k">import</span> <span class="n">Cast</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">Cast</span><span class="p">,</span> <span class="s1">'mysql'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">_check_mysql_version</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">compiler</span><span class="o">.</span><span class="n">dialect</span><span class="o">.</span><span class="n">server_version_info</span> <span class="o"><</span> <span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">0</span><span class="p">):</span>
<span class="k">return</span> <span class="n">compiler</span><span class="o">.</span><span class="n">process</span><span class="p">(</span><span class="n">element</span><span class="o">.</span><span class="n">clause</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</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_cast</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></pre></div>
</div>
<p>The above function, which only needs to be declared once
within an application, overrides the compilation of the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><code class="xref py py-func docutils literal notranslate"><span class="pre">cast()</span></code></a> construct to check for version 4.1.0 before
fully rendering CAST; else the internal element of the
construct is rendered directly.</p>
</div>
<div class="section" id="mysql-specific-index-options">
<span id="mysql-indexes"></span><h2>MySQL Specific Index Options<a class="headerlink" href="#mysql-specific-index-options" title="Permalink to this headline">¶</a></h2>
<p>MySQL-specific extensions to the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a> construct are available.</p>
<div class="section" id="index-length">
<h3>Index Length<a class="headerlink" href="#index-length" title="Permalink to this headline">¶</a></h3>
<p>MySQL provides an option to create index entries with a certain length, where
“length” refers to the number of characters or bytes in each value which will
become part of the index. SQLAlchemy provides this feature via the
<code class="docutils literal notranslate"><span class="pre">mysql_length</span></code> parameter:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s1">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="n">mysql_length</span><span class="o">=</span><span class="mi">10</span><span class="p">)</span>
<span class="n">Index</span><span class="p">(</span><span class="s1">'a_b_idx'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">a</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">b</span><span class="p">,</span> <span class="n">mysql_length</span><span class="o">=</span><span class="p">{</span><span class="s1">'a'</span><span class="p">:</span> <span class="mi">4</span><span class="p">,</span>
<span class="s1">'b'</span><span class="p">:</span> <span class="mi">9</span><span class="p">})</span></pre></div>
</div>
<p>Prefix lengths are given in characters for nonbinary string types and in bytes
for binary string types. The value passed to the keyword argument <em>must</em> be
either an integer (and, thus, specify the same prefix length value for all
columns of the index) or a dict in which keys are column names and values are
prefix length values for corresponding columns. MySQL only allows a length for
a column of an index if it is for a CHAR, VARCHAR, TEXT, BINARY, VARBINARY and
BLOB.</p>
</div>
<div class="section" id="index-prefixes">
<h3>Index Prefixes<a class="headerlink" href="#index-prefixes" title="Permalink to this headline">¶</a></h3>
<p>MySQL storage engines permit you to specify an index prefix when creating
an index. SQLAlchemy provides this feature via the
<code class="docutils literal notranslate"><span class="pre">mysql_prefix</span></code> parameter on <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s1">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="n">mysql_prefix</span><span class="o">=</span><span class="s1">'FULLTEXT'</span><span class="p">)</span></pre></div>
</div>
<p>The value passed to the keyword argument will be simply passed through to the
underlying CREATE INDEX, so it <em>must</em> be a valid index prefix for your MySQL
storage engine.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.5.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference external" href="http://dev.mysql.com/doc/refman/5.0/en/create-index.html">CREATE INDEX</a> - MySQL documentation</p>
</div>
</div>
<div class="section" id="index-types">
<h3>Index Types<a class="headerlink" href="#index-types" title="Permalink to this headline">¶</a></h3>
<p>Some MySQL storage engines permit you to specify an index type when creating
an index or primary key constraint. SQLAlchemy provides this feature via the
<code class="docutils literal notranslate"><span class="pre">mysql_using</span></code> parameter on <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s1">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="n">mysql_using</span><span class="o">=</span><span class="s1">'hash'</span><span class="p">)</span></pre></div>
</div>
<p>As well as the <code class="docutils literal notranslate"><span class="pre">mysql_using</span></code> parameter on <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.PrimaryKeyConstraint" title="sqlalchemy.schema.PrimaryKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">PrimaryKeyConstraint</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">PrimaryKeyConstraint</span><span class="p">(</span><span class="s2">"data"</span><span class="p">,</span> <span class="n">mysql_using</span><span class="o">=</span><span class="s1">'hash'</span><span class="p">)</span></pre></div>
</div>
<p>The value passed to the keyword argument will be simply passed through to the
underlying CREATE INDEX or PRIMARY KEY clause, so it <em>must</em> be a valid index
type for your MySQL storage engine.</p>
<p>More information can be found at:</p>
<p><a class="reference external" href="http://dev.mysql.com/doc/refman/5.0/en/create-index.html">http://dev.mysql.com/doc/refman/5.0/en/create-index.html</a></p>
<p><a class="reference external" href="http://dev.mysql.com/doc/refman/5.0/en/create-table.html">http://dev.mysql.com/doc/refman/5.0/en/create-table.html</a></p>
</div>
</div>
<div class="section" id="mysql-foreign-keys">
<span id="id1"></span><h2>MySQL Foreign Keys<a class="headerlink" href="#mysql-foreign-keys" title="Permalink to this headline">¶</a></h2>
<p>MySQL’s behavior regarding foreign keys has some important caveats.</p>
<div class="section" id="foreign-key-arguments-to-avoid">
<h3>Foreign Key Arguments to Avoid<a class="headerlink" href="#foreign-key-arguments-to-avoid" title="Permalink to this headline">¶</a></h3>
<p>MySQL does not support the foreign key arguments “DEFERRABLE”, “INITIALLY”,
or “MATCH”. Using the <code class="docutils literal notranslate"><span class="pre">deferrable</span></code> or <code class="docutils literal notranslate"><span class="pre">initially</span></code> keyword argument with
<a class="reference internal" href="../core/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> or <a class="reference internal" href="../core/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> will have the effect of
these keywords being rendered in a DDL expression, which will then raise an
error on MySQL. In order to use these keywords on a foreign key while having
them ignored on a MySQL backend, use a custom compile rule:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.ext.compiler</span> <span class="k">import</span> <span class="n">compiles</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.schema</span> <span class="k">import</span> <span class="n">ForeignKeyConstraint</span>
<span class="nd">@compiles</span><span class="p">(</span><span class="n">ForeignKeyConstraint</span><span class="p">,</span> <span class="s2">"mysql"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">process</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">element</span><span class="o">.</span><span class="n">deferrable</span> <span class="o">=</span> <span class="n">element</span><span class="o">.</span><span class="n">initially</span> <span class="o">=</span> <span class="kc">None</span>
<span class="k">return</span> <span class="n">compiler</span><span class="o">.</span><span class="n">visit_foreign_key_constraint</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></pre></div>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 0.9.0: </span>- the MySQL backend no longer silently ignores
the <code class="docutils literal notranslate"><span class="pre">deferrable</span></code> or <code class="docutils literal notranslate"><span class="pre">initially</span></code> keyword arguments of
<a class="reference internal" href="../core/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> and <a class="reference internal" href="../core/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>.</p>
</div>
<p>The “MATCH” keyword is in fact more insidious, and is explicitly disallowed
by SQLAlchemy in conjunction with the MySQL backend. This argument is
silently ignored by MySQL, but in addition has the effect of ON UPDATE and ON
DELETE options also being ignored by the backend. Therefore MATCH should
never be used with the MySQL backend; as is the case with DEFERRABLE and
INITIALLY, custom compilation rules can be used to correct a MySQL
ForeignKeyConstraint at DDL definition time.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0: </span>- the MySQL backend will raise a
<a class="reference internal" href="../core/exceptions.html#sqlalchemy.exc.CompileError" title="sqlalchemy.exc.CompileError"><code class="xref py py-class docutils literal notranslate"><span class="pre">CompileError</span></code></a> when the <code class="docutils literal notranslate"><span class="pre">match</span></code> keyword is used with
<a class="reference internal" href="../core/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> or <a class="reference internal" href="../core/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>.</p>
</div>
</div>
<div class="section" id="reflection-of-foreign-key-constraints">
<h3>Reflection of Foreign Key Constraints<a class="headerlink" href="#reflection-of-foreign-key-constraints" title="Permalink to this headline">¶</a></h3>
<p>Not all MySQL storage engines support foreign keys. When using the
very common <code class="docutils literal notranslate"><span class="pre">MyISAM</span></code> MySQL storage engine, the information loaded by table
reflection will not include foreign keys. For these tables, you may supply a
<code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code> at reflection time:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">ForeignKeyConstraint</span><span class="p">([</span><span class="s1">'other_id'</span><span class="p">],</span> <span class="p">[</span><span class="s1">'othertable.other_id'</span><span class="p">]),</span>
<span class="n">autoload</span><span class="o">=</span><span class="kc">True</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="#mysql-storage-engines"><span class="std std-ref">CREATE TABLE arguments including Storage Engines</span></a></p>
</div>
</div>
</div>
<div class="section" id="mysql-unique-constraints-and-reflection">
<span id="mysql-unique-constraints"></span><h2>MySQL Unique Constraints and Reflection<a class="headerlink" href="#mysql-unique-constraints-and-reflection" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy supports both the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a> construct with the
flag <code class="docutils literal notranslate"><span class="pre">unique=True</span></code>, indicating a UNIQUE index, as well as the
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">UniqueConstraint</span></code></a> construct, representing a UNIQUE constraint.
Both objects/syntaxes are supported by MySQL when emitting DDL to create
these constraints. However, MySQL does not have a unique constraint
construct that is separate from a unique index; that is, the “UNIQUE”
constraint on MySQL is equivalent to creating a “UNIQUE INDEX”.</p>
<p>When reflecting these constructs, the <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_indexes" title="sqlalchemy.engine.reflection.Inspector.get_indexes"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_indexes()</span></code></a>
and the <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_unique_constraints" title="sqlalchemy.engine.reflection.Inspector.get_unique_constraints"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_unique_constraints()</span></code></a> methods will <strong>both</strong>
return an entry for a UNIQUE index in MySQL. However, when performing
full table reflection using <code class="docutils literal notranslate"><span class="pre">Table(...,</span> <span class="pre">autoload=True)</span></code>,
the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">UniqueConstraint</span></code></a> construct is
<strong>not</strong> part of the fully reflected <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> construct under any
circumstances; this construct is always represented by a <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a>
with the <code class="docutils literal notranslate"><span class="pre">unique=True</span></code> setting present in the <code class="xref py py-attr docutils literal notranslate"><span class="pre">Table.indexes</span></code>
collection.</p>
</div>
<div class="section" id="timestamp-columns-and-null">
<span id="mysql-timestamp-null"></span><h2>TIMESTAMP Columns and NULL<a class="headerlink" href="#timestamp-columns-and-null" title="Permalink to this headline">¶</a></h2>
<p>MySQL historically enforces that a column which specifies the
TIMESTAMP datatype implicitly includes a default value of
CURRENT_TIMESTAMP, even though this is not stated, and additionally
sets the column as NOT NULL, the opposite behavior vs. that of all
other datatypes:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mysql> CREATE TABLE ts_test (
-> a INTEGER,
-> b INTEGER NOT NULL,
-> c TIMESTAMP,
-> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> e TIMESTAMP NULL);
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW CREATE TABLE ts_test;
+---------+-----------------------------------------------------
| Table | Create Table
+---------+-----------------------------------------------------
| ts_test | CREATE TABLE `ts_test` (
`a` int(11) DEFAULT NULL,
`b` int(11) NOT NULL,
`c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`e` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1</pre></div>
</div>
<p>Above, we see that an INTEGER column defaults to NULL, unless it is specified
with NOT NULL. But when the column is of type TIMESTAMP, an implicit
default of CURRENT_TIMESTAMP is generated which also coerces the column
to be a NOT NULL, even though we did not specify it as such.</p>
<p>This behavior of MySQL can be changed on the MySQL side using the
<a class="reference external" href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp">explicit_defaults_for_timestamp</a> configuration flag introduced in
MySQL 5.6. With this server setting enabled, TIMESTAMP columns behave like
any other datatype on the MySQL side with regards to defaults and nullability.</p>
<p>However, to accommodate the vast majority of MySQL databases that do not
specify this new flag, SQLAlchemy emits the “NULL” specifier explicitly with
any TIMESTAMP column that does not specify <code class="docutils literal notranslate"><span class="pre">nullable=False</span></code>. In order to
accommodate newer databases that specify <code class="docutils literal notranslate"><span class="pre">explicit_defaults_for_timestamp</span></code>,
SQLAlchemy also emits NOT NULL for TIMESTAMP columns that do specify
<code class="docutils literal notranslate"><span class="pre">nullable=False</span></code>. The following example illustrates:</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">MetaData</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">text</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mysql</span> <span class="k">import</span> <span class="n">TIMESTAMP</span>
<span class="n">m</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'ts_test'</span><span class="p">,</span> <span class="n">m</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'a'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'b'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'c'</span><span class="p">,</span> <span class="n">TIMESTAMP</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'d'</span><span class="p">,</span> <span class="n">TIMESTAMP</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="p">)</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mysql://scott:tiger@localhost/test"</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">m</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">e</span><span class="p">)</span></pre></div>
</div>
<p>output:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">ts_test</span> <span class="p">(</span>
<span class="n">a</span> <span class="n">INTEGER</span><span class="p">,</span>
<span class="n">b</span> <span class="n">INTEGER</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">,</span>
<span class="n">c</span> <span class="n">TIMESTAMP</span> <span class="n">NULL</span><span class="p">,</span>
<span class="n">d</span> <span class="n">TIMESTAMP</span> <span class="n">NOT</span> <span class="n">NULL</span>
<span class="p">)</span></pre></div>
</div>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.0.0: </span>- SQLAlchemy now renders NULL or NOT NULL in all
cases for TIMESTAMP columns, to accommodate
<code class="docutils literal notranslate"><span class="pre">explicit_defaults_for_timestamp</span></code>. Prior to this version, it will
not render “NOT NULL” for a TIMESTAMP column that is <code class="docutils literal notranslate"><span class="pre">nullable=False</span></code>.</p>
</div>
</div>
<div class="section" id="mysql-data-types">
<h2>MySQL Data Types<a class="headerlink" href="#mysql-data-types" title="Permalink to this headline">¶</a></h2>
<p>As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
valid with MySQL are importable from the top level dialect:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mysql</span> <span class="k">import</span> \
<span class="n">BIGINT</span><span class="p">,</span> <span class="n">BINARY</span><span class="p">,</span> <span class="n">BIT</span><span class="p">,</span> <span class="n">BLOB</span><span class="p">,</span> <span class="n">BOOLEAN</span><span class="p">,</span> <span class="n">CHAR</span><span class="p">,</span> <span class="n">DATE</span><span class="p">,</span> \
<span class="n">DATETIME</span><span class="p">,</span> <span class="n">DECIMAL</span><span class="p">,</span> <span class="n">DECIMAL</span><span class="p">,</span> <span class="n">DOUBLE</span><span class="p">,</span> <span class="n">ENUM</span><span class="p">,</span> <span class="n">FLOAT</span><span class="p">,</span> <span class="n">INTEGER</span><span class="p">,</span> \
<span class="n">LONGBLOB</span><span class="p">,</span> <span class="n">LONGTEXT</span><span class="p">,</span> <span class="n">MEDIUMBLOB</span><span class="p">,</span> <span class="n">MEDIUMINT</span><span class="p">,</span> <span class="n">MEDIUMTEXT</span><span class="p">,</span> <span class="n">NCHAR</span><span class="p">,</span> \
<span class="n">NUMERIC</span><span class="p">,</span> <span class="n">NVARCHAR</span><span class="p">,</span> <span class="n">REAL</span><span class="p">,</span> <span class="n">SET</span><span class="p">,</span> <span class="n">SMALLINT</span><span class="p">,</span> <span class="n">TEXT</span><span class="p">,</span> <span class="n">TIME</span><span class="p">,</span> <span class="n">TIMESTAMP</span><span class="p">,</span> \
<span class="n">TINYBLOB</span><span class="p">,</span> <span class="n">TINYINT</span><span class="p">,</span> <span class="n">TINYTEXT</span><span class="p">,</span> <span class="n">VARBINARY</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">,</span> <span class="n">YEAR</span></pre></div>
</div>
<p>Types which are specific to MySQL, or have MySQL-specific
construction arguments, are as follows:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.BIGINT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">BIGINT</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BIGINT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._IntegerType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.BIGINT" title="sqlalchemy.types.BIGINT"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.BIGINT</span></code></a></p>
<p>MySQL BIGINTEGER type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.BIGINT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BIGINT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a BIGINTEGER.</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.dialects.mysql.BIGINT.params.display_width"></span><strong>display_width</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.BIGINT.params.display_width">¶</a> – Optional, maximum display width for this number.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.BIGINT.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.BIGINT.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.BIGINT.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.BIGINT.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.BINARY">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">BINARY</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BINARY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types._Binary</span></code></p>
<p>The SQL BINARY type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.BINARY.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BINARY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code> <em>method of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">_Binary</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.BIT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">BIT</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BIT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<p>MySQL BIT type.</p>
<p>This type is for MySQL 5.0.3 or greater for MyISAM, and 5.0.5 or greater
for MyISAM, MEMORY, InnoDB and BDB. For older versions, use a
MSTinyInteger() type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.BIT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BIT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a BIT.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.mysql.BIT.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.BIT.params.length">¶</a> – Optional, number of bits.</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.BLOB">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">BLOB</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BLOB" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.LargeBinary</span></code></a></p>
<p>The SQL BLOB type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.BLOB.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BLOB.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary.__init__" title="sqlalchemy.types.LargeBinary.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a></p>
</div>
<p>Construct a LargeBinary type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.mysql.BLOB.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.BLOB.params.length">¶</a> – optional, a length for the column for use in
DDL statements, for those binary types that accept a length,
such as the MySQL BLOB type.</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.BOOLEAN">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">BOOLEAN</code><span class="sig-paren">(</span><em>create_constraint=True</em>, <em>name=None</em>, <em>_create_events=True</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BOOLEAN" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Boolean" title="sqlalchemy.types.Boolean"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Boolean</span></code></a></p>
<p>The SQL BOOLEAN type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.BOOLEAN.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>create_constraint=True</em>, <em>name=None</em>, <em>_create_events=True</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.BOOLEAN.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Boolean.__init__" title="sqlalchemy.types.Boolean.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Boolean" title="sqlalchemy.types.Boolean"><code class="xref py py-class docutils literal notranslate"><span class="pre">Boolean</span></code></a></p>
</div>
<p>Construct a Boolean.</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.dialects.mysql.BOOLEAN.params.create_constraint"></span><strong>create_constraint</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.BOOLEAN.params.create_constraint">¶</a> – defaults to True. If the boolean
is generated as an int/smallint, also create a CHECK constraint
on the table that ensures 1 or 0 as a value.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.BOOLEAN.params.name"></span><strong>name</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.BOOLEAN.params.name">¶</a> – if a CHECK constraint is generated, specify
the name of the constraint.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.CHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">CHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.CHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.CHAR" title="sqlalchemy.types.CHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.CHAR</span></code></a></p>
<p>MySQL CHAR type, for fixed-length character data.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.CHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.CHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a CHAR.</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.dialects.mysql.CHAR.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.CHAR.params.length">¶</a> – Maximum data length, in characters.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.CHAR.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.CHAR.params.binary">¶</a> – Optional, use the default binary collation for the
national character set. This does not affect the type of data
stored, use a BINARY type for binary data.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.CHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.CHAR.params.collation">¶</a> – Optional, request a particular collation. Must be
compatible with the national character set.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.DATE">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">DATE</code><a class="headerlink" href="#sqlalchemy.dialects.mysql.DATE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Date" title="sqlalchemy.types.Date"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Date</span></code></a></p>
<p>The SQL DATE type.</p>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mysql.DATE.__init__">
<code class="descname">__init__</code><a class="headerlink" href="#sqlalchemy.dialects.mysql.DATE.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-attr docutils literal notranslate"><span class="pre">__init__</span></code> <em>attribute of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">object</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.DATETIME">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">DATETIME</code><span class="sig-paren">(</span><em>timezone=False</em>, <em>fsp=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.DATETIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DATETIME" title="sqlalchemy.types.DATETIME"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.DATETIME</span></code></a></p>
<p>MySQL DATETIME type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.DATETIME.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>timezone=False</em>, <em>fsp=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.DATETIME.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a MySQL DATETIME type.</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.dialects.mysql.DATETIME.params.timezone"></span><strong>timezone</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DATETIME.params.timezone">¶</a> – not used by the MySQL dialect.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.DATETIME.params.fsp"></span><strong>fsp</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DATETIME.params.fsp">¶</a> – <p>fractional seconds precision value.
MySQL 5.6.4 supports storage of fractional seconds;
this parameter will be used when emitting DDL
for the DATETIME type.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>DBAPI driver support for fractional seconds may
be limited; current support includes
MySQL Connector/Python.</p>
</div>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.DECIMAL">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">DECIMAL</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.DECIMAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._NumericType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DECIMAL" title="sqlalchemy.types.DECIMAL"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.DECIMAL</span></code></a></p>
<p>MySQL DECIMAL type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.DECIMAL.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.DECIMAL.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a DECIMAL.</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.dialects.mysql.DECIMAL.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DECIMAL.params.precision">¶</a> – Total digits in this number. If scale and precision
are both None, values are stored to limits allowed by the server.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.DECIMAL.params.scale"></span><strong>scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DECIMAL.params.scale">¶</a> – The number of digits after the decimal point.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.DECIMAL.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DECIMAL.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.DECIMAL.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DECIMAL.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.DOUBLE">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">DOUBLE</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.DOUBLE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._FloatType</span></code></p>
<p>MySQL DOUBLE type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.DOUBLE.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.DOUBLE.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a DOUBLE.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.mysql.DOUBLE" title="sqlalchemy.dialects.mysql.DOUBLE"><code class="xref py py-class docutils literal notranslate"><span class="pre">DOUBLE</span></code></a> type by default converts from float
to Decimal, using a truncation that defaults to 10 digits.
Specify either <code class="docutils literal notranslate"><span class="pre">scale=n</span></code> or <code class="docutils literal notranslate"><span class="pre">decimal_return_scale=n</span></code> in order
to change this scale, or <code class="docutils literal notranslate"><span class="pre">asdecimal=False</span></code> to return values
directly as Python floating points.</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.dialects.mysql.DOUBLE.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DOUBLE.params.precision">¶</a> – Total digits in this number. If scale and precision
are both None, values are stored to limits allowed by the server.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.DOUBLE.params.scale"></span><strong>scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DOUBLE.params.scale">¶</a> – The number of digits after the decimal point.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.DOUBLE.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DOUBLE.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.DOUBLE.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.DOUBLE.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.ENUM">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">ENUM</code><span class="sig-paren">(</span><em>*enums</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.ENUM" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.NativeForEmulated</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Enum" title="sqlalchemy.types.Enum"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Enum</span></code></a>, <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.enumerated._EnumeratedValues</span></code></p>
<p>MySQL ENUM type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.ENUM.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>*enums</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.ENUM.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an ENUM.</p>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Column</span><span class="p">(</span><span class="s1">'myenum'</span><span class="p">,</span> <span class="n">ENUM</span><span class="p">(</span><span class="s2">"foo"</span><span class="p">,</span> <span class="s2">"bar"</span><span class="p">,</span> <span class="s2">"baz"</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.dialects.mysql.ENUM.params.enums"></span><strong>enums</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.enums">¶</a> – <p>The range of valid values for this ENUM. Values will be
quoted when generating the schema according to the quoting flag (see
below). This object may also be a PEP-435-compliant enumerated
type.</p>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.ENUM.params.strict"></span><strong>strict</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.strict">¶</a> – <p>This flag has no effect.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version The: </span>MySQL ENUM type as well as the base Enum
type now validates all Python data values.</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.ENUM.params.charset"></span><strong>charset</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.charset">¶</a> – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.ENUM.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.collation">¶</a> – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.ENUM.params.ascii"></span><strong>ascii</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.ascii">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">latin1</span></code>
character set, generates ASCII in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.ENUM.params.unicode"></span><strong>unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.unicode">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">ucs2</span></code>
character set, generates UNICODE in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.ENUM.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.binary">¶</a> – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.ENUM.params.quoting"></span><strong>quoting</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.ENUM.params.quoting">¶</a> – <p>Defaults to ‘auto’: automatically determine enum value
quoting. If all enum values are surrounded by the same quoting
character, then use ‘quoted’ mode. Otherwise, use ‘unquoted’ mode.</p>
<p>’quoted’: values in enums are already quoted, they will be used
directly when generating the schema - this usage is deprecated.</p>
<p>’unquoted’: values in enums are not quoted, they will be escaped and
surrounded by single quotes when generating the schema.</p>
<p>Previous versions of this type always required manually quoted
values to be supplied; future versions will always quote the string
literals for you. This is a transitional option.</p>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.FLOAT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">FLOAT</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=False</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.FLOAT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._FloatType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.FLOAT" title="sqlalchemy.types.FLOAT"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.FLOAT</span></code></a></p>
<p>MySQL FLOAT type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.FLOAT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=False</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.FLOAT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a FLOAT.</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.dialects.mysql.FLOAT.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.FLOAT.params.precision">¶</a> – Total digits in this number. If scale and precision
are both None, values are stored to limits allowed by the server.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.FLOAT.params.scale"></span><strong>scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.FLOAT.params.scale">¶</a> – The number of digits after the decimal point.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.FLOAT.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.FLOAT.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.FLOAT.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.FLOAT.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.INTEGER">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">INTEGER</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.INTEGER" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._IntegerType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.INTEGER" title="sqlalchemy.types.INTEGER"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.INTEGER</span></code></a></p>
<p>MySQL INTEGER type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.INTEGER.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.INTEGER.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an INTEGER.</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.dialects.mysql.INTEGER.params.display_width"></span><strong>display_width</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.INTEGER.params.display_width">¶</a> – Optional, maximum display width for this number.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.INTEGER.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.INTEGER.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.INTEGER.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.INTEGER.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.JSON">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">JSON</code><span class="sig-paren">(</span><em>none_as_null=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.JSON" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.JSON" title="sqlalchemy.types.JSON"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.JSON</span></code></a></p>
<p>MySQL JSON type.</p>
<p>MySQL supports JSON as of version 5.7. Note that MariaDB does <strong>not</strong>
support JSON at the time of this writing.</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.mysql.JSON" title="sqlalchemy.dialects.mysql.JSON"><code class="xref py py-class docutils literal notranslate"><span class="pre">mysql.JSON</span></code></a> type supports persistence of JSON values
as well as the core index operations provided by <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.JSON" title="sqlalchemy.types.JSON"><code class="xref py py-class docutils literal notranslate"><span class="pre">types.JSON</span></code></a>
datatype, by adapting the operations to render the <code class="docutils literal notranslate"><span class="pre">JSON_EXTRACT</span></code>
function at the database level.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.LONGBLOB">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">LONGBLOB</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.LONGBLOB" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types._Binary</span></code></p>
<p>MySQL LONGBLOB type, for binary data up to 2^32 bytes.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.LONGBLOB.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.LONGBLOB.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code> <em>method of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">_Binary</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.LONGTEXT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">LONGTEXT</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.LONGTEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code></p>
<p>MySQL LONGTEXT type, for text up to 2^32 characters.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.LONGTEXT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.LONGTEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a LONGTEXT.</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.dialects.mysql.LONGTEXT.params.charset"></span><strong>charset</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.LONGTEXT.params.charset">¶</a> – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.LONGTEXT.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.LONGTEXT.params.collation">¶</a> – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.LONGTEXT.params.ascii"></span><strong>ascii</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.LONGTEXT.params.ascii">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">latin1</span></code>
character set, generates ASCII in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.LONGTEXT.params.unicode"></span><strong>unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.LONGTEXT.params.unicode">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">ucs2</span></code>
character set, generates UNICODE in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.LONGTEXT.params.national"></span><strong>national</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.LONGTEXT.params.national">¶</a> – Optional. If true, use the server’s configured
national character set.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.LONGTEXT.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.LONGTEXT.params.binary">¶</a> – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.MEDIUMBLOB">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">MEDIUMBLOB</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.MEDIUMBLOB" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types._Binary</span></code></p>
<p>MySQL MEDIUMBLOB type, for binary data up to 2^24 bytes.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.MEDIUMBLOB.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.MEDIUMBLOB.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code> <em>method of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">_Binary</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.MEDIUMINT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">MEDIUMINT</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.MEDIUMINT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._IntegerType</span></code></p>
<p>MySQL MEDIUMINTEGER type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.MEDIUMINT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.MEDIUMINT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a MEDIUMINTEGER</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.dialects.mysql.MEDIUMINT.params.display_width"></span><strong>display_width</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMINT.params.display_width">¶</a> – Optional, maximum display width for this number.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.MEDIUMINT.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMINT.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.MEDIUMINT.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMINT.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.MEDIUMTEXT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">MEDIUMTEXT</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code></p>
<p>MySQL MEDIUMTEXT type, for text up to 2^24 characters.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.MEDIUMTEXT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a MEDIUMTEXT.</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.dialects.mysql.MEDIUMTEXT.params.charset"></span><strong>charset</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT.params.charset">¶</a> – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.MEDIUMTEXT.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT.params.collation">¶</a> – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.MEDIUMTEXT.params.ascii"></span><strong>ascii</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT.params.ascii">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">latin1</span></code>
character set, generates ASCII in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.MEDIUMTEXT.params.unicode"></span><strong>unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT.params.unicode">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">ucs2</span></code>
character set, generates UNICODE in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.MEDIUMTEXT.params.national"></span><strong>national</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT.params.national">¶</a> – Optional. If true, use the server’s configured
national character set.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.MEDIUMTEXT.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.MEDIUMTEXT.params.binary">¶</a> – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.NCHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">NCHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.NCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.NCHAR" title="sqlalchemy.types.NCHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.NCHAR</span></code></a></p>
<p>MySQL NCHAR type.</p>
<p>For fixed-length character data in the server’s configured national
character set.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.NCHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.NCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an NCHAR.</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.dialects.mysql.NCHAR.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NCHAR.params.length">¶</a> – Maximum data length, in characters.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.NCHAR.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NCHAR.params.binary">¶</a> – Optional, use the default binary collation for the
national character set. This does not affect the type of data
stored, use a BINARY type for binary data.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.NCHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NCHAR.params.collation">¶</a> – Optional, request a particular collation. Must be
compatible with the national character set.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.NUMERIC">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">NUMERIC</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.NUMERIC" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._NumericType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.NUMERIC" title="sqlalchemy.types.NUMERIC"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.NUMERIC</span></code></a></p>
<p>MySQL NUMERIC type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.NUMERIC.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.NUMERIC.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a NUMERIC.</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.dialects.mysql.NUMERIC.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NUMERIC.params.precision">¶</a> – Total digits in this number. If scale and precision
are both None, values are stored to limits allowed by the server.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.NUMERIC.params.scale"></span><strong>scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NUMERIC.params.scale">¶</a> – The number of digits after the decimal point.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.NUMERIC.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NUMERIC.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.NUMERIC.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NUMERIC.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.NVARCHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">NVARCHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.NVARCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.NVARCHAR" title="sqlalchemy.types.NVARCHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.NVARCHAR</span></code></a></p>
<p>MySQL NVARCHAR type.</p>
<p>For variable-length character data in the server’s configured national
character set.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.NVARCHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.NVARCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an NVARCHAR.</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.dialects.mysql.NVARCHAR.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NVARCHAR.params.length">¶</a> – Maximum data length, in characters.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.NVARCHAR.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NVARCHAR.params.binary">¶</a> – Optional, use the default binary collation for the
national character set. This does not affect the type of data
stored, use a BINARY type for binary data.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.NVARCHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.NVARCHAR.params.collation">¶</a> – Optional, request a particular collation. Must be
compatible with the national character set.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.REAL">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">REAL</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.REAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._FloatType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.REAL" title="sqlalchemy.types.REAL"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.REAL</span></code></a></p>
<p>MySQL REAL type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.REAL.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=True</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.REAL.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a REAL.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The <a class="reference internal" href="#sqlalchemy.dialects.mysql.REAL" title="sqlalchemy.dialects.mysql.REAL"><code class="xref py py-class docutils literal notranslate"><span class="pre">REAL</span></code></a> type by default converts from float
to Decimal, using a truncation that defaults to 10 digits.
Specify either <code class="docutils literal notranslate"><span class="pre">scale=n</span></code> or <code class="docutils literal notranslate"><span class="pre">decimal_return_scale=n</span></code> in order
to change this scale, or <code class="docutils literal notranslate"><span class="pre">asdecimal=False</span></code> to return values
directly as Python floating points.</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.dialects.mysql.REAL.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.REAL.params.precision">¶</a> – Total digits in this number. If scale and precision
are both None, values are stored to limits allowed by the server.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.REAL.params.scale"></span><strong>scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.REAL.params.scale">¶</a> – The number of digits after the decimal point.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.REAL.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.REAL.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.REAL.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.REAL.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.SET">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">SET</code><span class="sig-paren">(</span><em>*values</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.SET" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.enumerated._EnumeratedValues</span></code></p>
<p>MySQL SET type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.SET.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>*values</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.SET.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a SET.</p>
<p>E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Column</span><span class="p">(</span><span class="s1">'myset'</span><span class="p">,</span> <span class="n">SET</span><span class="p">(</span><span class="s2">"foo"</span><span class="p">,</span> <span class="s2">"bar"</span><span class="p">,</span> <span class="s2">"baz"</span><span class="p">))</span></pre></div>
</div>
<p>The list of potential values is required in the case that this
set will be used to generate DDL for a table, or if the
<a class="reference internal" href="#sqlalchemy.dialects.mysql.SET.params.retrieve_as_bitwise" title="sqlalchemy.dialects.mysql.SET"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">SET.retrieve_as_bitwise</span></code></a> flag is set to True.</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.dialects.mysql.SET.params.values"></span><strong>values</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.values">¶</a> – The range of valid values for this SET.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.convert_unicode">¶</a> – Same flag as that of
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.collation">¶</a> – same as that of <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.collation" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.collation</span></code></a></p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.charset"></span><strong>charset</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.charset">¶</a> – same as that of <a class="reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.charset" title="sqlalchemy.dialects.mysql.VARCHAR"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">VARCHAR.charset</span></code></a>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.ascii"></span><strong>ascii</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.ascii">¶</a> – same as that of <a class="reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.ascii" title="sqlalchemy.dialects.mysql.VARCHAR"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">VARCHAR.ascii</span></code></a>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.unicode"></span><strong>unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.unicode">¶</a> – same as that of <a class="reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.unicode" title="sqlalchemy.dialects.mysql.VARCHAR"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">VARCHAR.unicode</span></code></a>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.binary">¶</a> – same as that of <a class="reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.binary" title="sqlalchemy.dialects.mysql.VARCHAR"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">VARCHAR.binary</span></code></a>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.quoting"></span><strong>quoting</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.quoting">¶</a> – <p>Defaults to ‘auto’: automatically determine set value
quoting. If all values are surrounded by the same quoting
character, then use ‘quoted’ mode. Otherwise, use ‘unquoted’ mode.</p>
<p>’quoted’: values in enums are already quoted, they will be used
directly when generating the schema - this usage is deprecated.</p>
<p>’unquoted’: values in enums are not quoted, they will be escaped and
surrounded by single quotes when generating the schema.</p>
<p>Previous versions of this type always required manually quoted
values to be supplied; future versions will always quote the string
literals for you. This is a transitional option.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0.</span></p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SET.params.retrieve_as_bitwise"></span><strong>retrieve_as_bitwise</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SET.params.retrieve_as_bitwise">¶</a> – <p>if True, the data for the set type will be
persisted and selected using an integer value, where a set is coerced
into a bitwise mask for persistence. MySQL allows this mode which
has the advantage of being able to store values unambiguously,
such as the blank string <code class="docutils literal notranslate"><span class="pre">''</span></code>. The datatype will appear
as the expression <code class="docutils literal notranslate"><span class="pre">col</span> <span class="pre">+</span> <span class="pre">0</span></code> in a SELECT statement, so that the
value is coerced into an integer value in result sets.
This flag is required if one wishes
to persist a set that can store the blank string <code class="docutils literal notranslate"><span class="pre">''</span></code> as a value.</p>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>When using <a class="reference internal" href="#sqlalchemy.dialects.mysql.SET.params.retrieve_as_bitwise" title="sqlalchemy.dialects.mysql.SET"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">mysql.SET.retrieve_as_bitwise</span></code></a>, it is
essential that the list of set values is expressed in the
<strong>exact same order</strong> as exists on the MySQL database.</p>
</div>
<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.dialects.mysql.SMALLINT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">SMALLINT</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.SMALLINT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._IntegerType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.SMALLINT" title="sqlalchemy.types.SMALLINT"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.SMALLINT</span></code></a></p>
<p>MySQL SMALLINTEGER type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.SMALLINT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.SMALLINT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a SMALLINTEGER.</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.dialects.mysql.SMALLINT.params.display_width"></span><strong>display_width</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SMALLINT.params.display_width">¶</a> – Optional, maximum display width for this number.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SMALLINT.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SMALLINT.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.SMALLINT.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.SMALLINT.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.TEXT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">TEXT</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.TEXT" title="sqlalchemy.types.TEXT"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TEXT</span></code></a></p>
<p>MySQL TEXT type, for text up to 2^16 characters.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.TEXT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a TEXT.</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.dialects.mysql.TEXT.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TEXT.params.length">¶</a> – Optional, if provided the server may optimize storage
by substituting the smallest TEXT type sufficient to store
<code class="docutils literal notranslate"><span class="pre">length</span></code> characters.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TEXT.params.charset"></span><strong>charset</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TEXT.params.charset">¶</a> – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TEXT.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TEXT.params.collation">¶</a> – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TEXT.params.ascii"></span><strong>ascii</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TEXT.params.ascii">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">latin1</span></code>
character set, generates ASCII in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TEXT.params.unicode"></span><strong>unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TEXT.params.unicode">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">ucs2</span></code>
character set, generates UNICODE in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TEXT.params.national"></span><strong>national</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TEXT.params.national">¶</a> – Optional. If true, use the server’s configured
national character set.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TEXT.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TEXT.params.binary">¶</a> – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.TIME">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">TIME</code><span class="sig-paren">(</span><em>timezone=False</em>, <em>fsp=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.TIME" title="sqlalchemy.types.TIME"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TIME</span></code></a></p>
<p>MySQL TIME type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.TIME.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>timezone=False</em>, <em>fsp=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TIME.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a MySQL TIME type.</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.dialects.mysql.TIME.params.timezone"></span><strong>timezone</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TIME.params.timezone">¶</a> – not used by the MySQL dialect.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TIME.params.fsp"></span><strong>fsp</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TIME.params.fsp">¶</a> – <p>fractional seconds precision value.
MySQL 5.6 supports storage of fractional seconds;
this parameter will be used when emitting DDL
for the TIME type.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>DBAPI driver support for fractional seconds may
be limited; current support includes
MySQL Connector/Python.</p>
</div>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.TIMESTAMP">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">TIMESTAMP</code><span class="sig-paren">(</span><em>timezone=False</em>, <em>fsp=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TIMESTAMP" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.TIMESTAMP" title="sqlalchemy.types.TIMESTAMP"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TIMESTAMP</span></code></a></p>
<p>MySQL TIMESTAMP type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.TIMESTAMP.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>timezone=False</em>, <em>fsp=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TIMESTAMP.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a MySQL TIMESTAMP type.</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.dialects.mysql.TIMESTAMP.params.timezone"></span><strong>timezone</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TIMESTAMP.params.timezone">¶</a> – not used by the MySQL dialect.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TIMESTAMP.params.fsp"></span><strong>fsp</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TIMESTAMP.params.fsp">¶</a> – <p>fractional seconds precision value.
MySQL 5.6.4 supports storage of fractional seconds;
this parameter will be used when emitting DDL
for the TIMESTAMP type.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>DBAPI driver support for fractional seconds may
be limited; current support includes
MySQL Connector/Python.</p>
</div>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.TINYBLOB">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">TINYBLOB</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TINYBLOB" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types._Binary</span></code></p>
<p>MySQL TINYBLOB type, for binary data up to 2^8 bytes.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.TINYBLOB.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TINYBLOB.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code> <em>method of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">_Binary</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.TINYINT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">TINYINT</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TINYINT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._IntegerType</span></code></p>
<p>MySQL TINYINT type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.TINYINT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>display_width=None</em>, <em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TINYINT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a TINYINT.</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.dialects.mysql.TINYINT.params.display_width"></span><strong>display_width</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYINT.params.display_width">¶</a> – Optional, maximum display width for this number.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TINYINT.params.unsigned"></span><strong>unsigned</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYINT.params.unsigned">¶</a> – a boolean, optional.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TINYINT.params.zerofill"></span><strong>zerofill</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYINT.params.zerofill">¶</a> – Optional. If true, values will be stored as strings
left-padded with zeros. Note that this does not effect the values
returned by the underlying database API, which continue to be
numeric.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.TINYTEXT">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">TINYTEXT</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TINYTEXT" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code></p>
<p>MySQL TINYTEXT type, for text up to 2^8 characters.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.TINYTEXT.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.TINYTEXT.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a TINYTEXT.</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.dialects.mysql.TINYTEXT.params.charset"></span><strong>charset</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYTEXT.params.charset">¶</a> – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TINYTEXT.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYTEXT.params.collation">¶</a> – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TINYTEXT.params.ascii"></span><strong>ascii</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYTEXT.params.ascii">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">latin1</span></code>
character set, generates ASCII in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TINYTEXT.params.unicode"></span><strong>unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYTEXT.params.unicode">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">ucs2</span></code>
character set, generates UNICODE in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TINYTEXT.params.national"></span><strong>national</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYTEXT.params.national">¶</a> – Optional. If true, use the server’s configured
national character set.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.TINYTEXT.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.TINYTEXT.params.binary">¶</a> – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.VARBINARY">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">VARBINARY</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.VARBINARY" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types._Binary</span></code></p>
<p>The SQL VARBINARY type.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.VARBINARY.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.VARBINARY.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code> <em>method of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">_Binary</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.VARCHAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">VARCHAR</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.VARCHAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.types._StringType</span></code>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.VARCHAR" title="sqlalchemy.types.VARCHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.VARCHAR</span></code></a></p>
<p>MySQL VARCHAR type, for variable-length character data.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.VARCHAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.VARCHAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a VARCHAR.</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.dialects.mysql.VARCHAR.params.charset"></span><strong>charset</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.charset">¶</a> – Optional, a column-level character set for this string
value. Takes precedence to ‘ascii’ or ‘unicode’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.VARCHAR.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.collation">¶</a> – Optional, a column-level collation for this string
value. Takes precedence to ‘binary’ short-hand.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.VARCHAR.params.ascii"></span><strong>ascii</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.ascii">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">latin1</span></code>
character set, generates ASCII in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.VARCHAR.params.unicode"></span><strong>unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.unicode">¶</a> – Defaults to False: short-hand for the <code class="docutils literal notranslate"><span class="pre">ucs2</span></code>
character set, generates UNICODE in schema.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.VARCHAR.params.national"></span><strong>national</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.national">¶</a> – Optional. If true, use the server’s configured
national character set.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.mysql.VARCHAR.params.binary"></span><strong>binary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.VARCHAR.params.binary">¶</a> – Defaults to False: short-hand, pick the binary
collation type that matches the column’s character set. Generates
BINARY in schema. This does not affect the type of data stored,
only the collation of character data.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.YEAR">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.</code><code class="descname">YEAR</code><span class="sig-paren">(</span><em>display_width=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.YEAR" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<p>MySQL YEAR type, for single byte storage of years 1901-2155.</p>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.YEAR.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>display_width=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.YEAR.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
</div>
<div class="section" id="mysql-dml-constructs">
<h2>MySQL DML Constructs<a class="headerlink" href="#mysql-dml-constructs" title="Permalink to this headline">¶</a></h2>
<dl class="function">
<dt id="sqlalchemy.dialects.mysql.dml.insert">
<code class="descclassname">sqlalchemy.dialects.mysql.dml.</code><code class="descname">insert</code><span class="sig-paren">(</span><em>table</em>, <em>values=None</em>, <em>inline=False</em>, <em>bind=None</em>, <em>prefixes=None</em>, <em>returning=None</em>, <em>return_defaults=False</em>, <em>**dialect_kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.dml.insert" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a new <a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert" title="sqlalchemy.dialects.mysql.dml.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> object.</p>
<p>This constructor is mirrored as a public API function; see <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.insert" title="sqlalchemy.sql.expression.insert"><code class="xref py py-func docutils literal notranslate"><span class="pre">insert()</span></code></a> for a full usage and argument description.</p>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.mysql.dml.Insert">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.mysql.dml.</code><code class="descname">Insert</code><span class="sig-paren">(</span><em>table</em>, <em>values=None</em>, <em>inline=False</em>, <em>bind=None</em>, <em>prefixes=None</em>, <em>returning=None</em>, <em>return_defaults=False</em>, <em>**dialect_kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.dml.Insert" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.sql.expression.Insert</span></code></a></p>
<p>MySQL-specific implementation of INSERT.</p>
<p>Adds methods for MySQL-specific syntaxes such as ON DUPLICATE KEY UPDATE.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<dl class="attribute">
<dt id="sqlalchemy.dialects.mysql.dml.Insert.inserted">
<code class="descname">inserted</code><a class="headerlink" href="#sqlalchemy.dialects.mysql.dml.Insert.inserted" title="Permalink to this definition">¶</a></dt>
<dd><p>Provide the “inserted” namespace for an ON DUPLICATE KEY UPDATE statement</p>
<p>MySQL’s ON DUPLICATE KEY UPDATE clause allows reference to the row
that would be inserted, via a special function called <code class="docutils literal notranslate"><span class="pre">VALUES()</span></code>.
This attribute provides all columns in this row to be referenceable
such that they will render within a <code class="docutils literal notranslate"><span class="pre">VALUES()</span></code> function inside the
ON DUPLICATE KEY UPDATE clause. The attribute is named <code class="docutils literal notranslate"><span class="pre">.inserted</span></code>
so as not to conflict with the existing <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Insert.values" title="sqlalchemy.sql.expression.Insert.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Insert.values()</span></code></a> method.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#mysql-insert-on-duplicate-key-update"><span class="std std-ref">INSERT…ON DUPLICATE KEY UPDATE (Upsert)</span></a> - example of how
to use <a class="reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert.inserted" title="sqlalchemy.dialects.mysql.dml.Insert.inserted"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Insert.inserted</span></code></a></p>
</div>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update">
<code class="descname">on_duplicate_key_update</code><span class="sig-paren">(</span><em>**kw</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update" title="Permalink to this definition">¶</a></dt>
<dd><p>Specifies the ON DUPLICATE KEY UPDATE clause.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update.params.**kw"></span><strong>**kw</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update.params.**kw">¶</a> – Column keys linked to UPDATE values. The
values may be any SQL expression or supported literal Python
values.</p>
</dd>
</dl>
<div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>This dictionary does <strong>not</strong> take into account
Python-specified default UPDATE values or generation functions,
e.g. those specified using <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.onupdate" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.onupdate</span></code></a>.
These values will not be exercised for an ON DUPLICATE KEY UPDATE
style of UPDATE, unless values are manually specified here.</p>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.</span></p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#mysql-insert-on-duplicate-key-update"><span class="std std-ref">INSERT…ON DUPLICATE KEY UPDATE (Upsert)</span></a></p>
</div>
</dd></dl>
</dd></dl>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.mysqldb">
<span id="mysqlclient-fork-of-mysql-python"></span><h2>mysqlclient (fork of MySQL-Python)<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.mysqldb" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the mysqlclient (maintained fork of MySQL-Python) driver.</p>
<div class="section" id="dialect-mysql-mysqldb-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-mysqldb-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for mysqlclient (maintained fork of MySQL-Python) is available at:
<a class="reference external" href="https://pypi.org/project/mysqlclient/">https://pypi.org/project/mysqlclient/</a></p>
</div>
<div class="section" id="dialect-mysql-mysqldb-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-mysqldb-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mysql</span><span class="o">+</span><span class="n">mysqldb</span><span class="p">:</span><span class="o">//<</span><span class="n">user</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">host</span><span class="o">></span><span class="p">[:</span><span class="o"><</span><span class="n">port</span><span class="o">></span><span class="p">]</span><span class="o">/<</span><span class="n">dbname</span><span class="o">></span></pre></div>
</div>
</p>
</div>
<div class="section" id="driver-status">
<h3>Driver Status<a class="headerlink" href="#driver-status" title="Permalink to this headline">¶</a></h3>
<p>The mysqlclient DBAPI is a maintained fork of the
<a class="reference external" href="http://sourceforge.net/projects/mysql-python">MySQL-Python</a> DBAPI
that is no longer maintained. <a class="reference external" href="https://github.com/PyMySQL/mysqlclient-python">mysqlclient</a> supports Python 2 and Python 3
and is very stable.</p>
</div>
<div class="section" id="mysqldb-unicode">
<span id="id2"></span><h3>Unicode<a class="headerlink" href="#mysqldb-unicode" title="Permalink to this headline">¶</a></h3>
<p>Please see <a class="reference internal" href="#mysql-unicode"><span class="std std-ref">Unicode</span></a> for current recommendations on unicode
handling.</p>
</div>
<div class="section" id="using-mysqldb-with-google-cloud-sql">
<h3>Using MySQLdb with Google Cloud SQL<a class="headerlink" href="#using-mysqldb-with-google-cloud-sql" title="Permalink to this headline">¶</a></h3>
<p>Google Cloud SQL now recommends use of the MySQLdb dialect. Connect
using a URL like the following:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mysql+mysqldb://root@/<dbname>?unix_socket=/cloudsql/<projectid>:<instancename></pre></div>
</div>
</div>
<div class="section" id="id3">
<h3>Server Side Cursors<a class="headerlink" href="#id3" title="Permalink to this headline">¶</a></h3>
<p>The mysqldb dialect supports server-side cursors. See <a class="reference internal" href="#mysql-ss-cursors"><span class="std std-ref">Server Side Cursors</span></a>.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.pymysql">
<span id="pymysql"></span><h2>PyMySQL<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.pymysql" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the PyMySQL driver.</p>
<div class="section" id="dialect-mysql-pymysql-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-pymysql-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for PyMySQL is available at:
<a class="reference external" href="https://pymysql.readthedocs.io/">https://pymysql.readthedocs.io/</a></p>
</div>
<div class="section" id="dialect-mysql-pymysql-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-pymysql-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]</pre></div>
</div>
</p>
</div>
<div class="section" id="id4">
<h3>Unicode<a class="headerlink" href="#id4" title="Permalink to this headline">¶</a></h3>
<p>Please see <a class="reference internal" href="#mysql-unicode"><span class="std std-ref">Unicode</span></a> for current recommendations on unicode
handling.</p>
</div>
<div class="section" id="mysql-python-compatibility">
<h3>MySQL-Python Compatibility<a class="headerlink" href="#mysql-python-compatibility" title="Permalink to this headline">¶</a></h3>
<p>The pymysql DBAPI is a pure Python port of the MySQL-python (MySQLdb) driver,
and targets 100% compatibility. Most behavioral notes for MySQL-python apply
to the pymysql driver as well.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.mysqlconnector">
<span id="mysql-connector"></span><h2>MySQL-Connector<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.mysqlconnector" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the MySQL Connector/Python driver.</p>
<div class="section" id="dialect-mysql-mysqlconnector-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-mysqlconnector-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for MySQL Connector/Python is available at:
<a class="reference external" href="https://pypi.org/project/mysql-connector-python/">https://pypi.org/project/mysql-connector-python/</a></p>
</div>
<div class="section" id="dialect-mysql-mysqlconnector-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-mysqlconnector-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mysql</span><span class="o">+</span><span class="n">mysqlconnector</span><span class="p">:</span><span class="o">//<</span><span class="n">user</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">host</span><span class="o">></span><span class="p">[:</span><span class="o"><</span><span class="n">port</span><span class="o">></span><span class="p">]</span><span class="o">/<</span><span class="n">dbname</span><span class="o">></span></pre></div>
</div>
</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.cymysql">
<span id="cymysql"></span><h2>cymysql<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.cymysql" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the CyMySQL driver.</p>
<div class="section" id="dialect-mysql-cymysql-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-cymysql-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for CyMySQL is available at:
<a class="reference external" href="https://github.com/nakagami/CyMySQL">https://github.com/nakagami/CyMySQL</a></p>
</div>
<div class="section" id="dialect-mysql-cymysql-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-cymysql-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mysql+cymysql://<username>:<password>@<host>/<dbname>[?<options>]</pre></div>
</div>
</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.oursql">
<span id="oursql"></span><h2>OurSQL<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.oursql" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the OurSQL driver.</p>
<div class="section" id="dialect-mysql-oursql-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-oursql-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for OurSQL is available at:
<a class="reference external" href="http://packages.python.org/oursql/">http://packages.python.org/oursql/</a></p>
</div>
<div class="section" id="dialect-mysql-oursql-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-oursql-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mysql</span><span class="o">+</span><span class="n">oursql</span><span class="p">:</span><span class="o">//<</span><span class="n">user</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">host</span><span class="o">></span><span class="p">[:</span><span class="o"><</span><span class="n">port</span><span class="o">></span><span class="p">]</span><span class="o">/<</span><span class="n">dbname</span><span class="o">></span></pre></div>
</div>
</p>
</div>
<div class="section" id="id5">
<h3>Unicode<a class="headerlink" href="#id5" title="Permalink to this headline">¶</a></h3>
<p>Please see <a class="reference internal" href="#mysql-unicode"><span class="std std-ref">Unicode</span></a> for current recommendations on unicode
handling.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.gaerdbms">
<span id="google-app-engine"></span><h2>Google App Engine<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.gaerdbms" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the Google Cloud SQL driver.<p>This dialect is based primarily on the <a class="reference internal" href="#module-sqlalchemy.dialects.mysql.mysqldb" title="sqlalchemy.dialects.mysql.mysqldb"><code class="xref py py-mod docutils literal notranslate"><span class="pre">mysql.mysqldb</span></code></a> dialect with
minimal changes.</p>
<div class="deprecated">
<p><span class="versionmodified deprecated">Deprecated since version 1.0: </span>This dialect is <strong>no longer necessary</strong> for
Google Cloud SQL; the MySQLdb dialect can be used directly.
Cloud SQL now recommends creating connections via the
mysql dialect using the URL format</p>
<p><code class="docutils literal notranslate"><span class="pre">mysql+mysqldb://root@/<dbname>?unix_socket=/cloudsql/<projectid>:<instancename></span></code></p>
</div>
</p>
<div class="section" id="dialect-mysql-gaerdbms-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-gaerdbms-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for Google Cloud SQL is available at:
<a class="reference external" href="https://developers.google.com/appengine/docs/python/cloud-sql/developers-guide">https://developers.google.com/appengine/docs/python/cloud-sql/developers-guide</a></p>
</div>
<div class="section" id="dialect-mysql-gaerdbms-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-gaerdbms-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>mysql+gaerdbms:///<dbname>?instance=<instancename></pre></div>
</div>
</p>
</div>
<div class="section" id="pooling">
<h3>Pooling<a class="headerlink" href="#pooling" title="Permalink to this headline">¶</a></h3>
<p>Google App Engine connections appear to be randomly recycled,
so the dialect does not pool connections. The <a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.NullPool" title="sqlalchemy.pool.NullPool"><code class="xref py py-class docutils literal notranslate"><span class="pre">NullPool</span></code></a>
implementation is installed within 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> by
default.</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.pyodbc">
<span id="pyodbc"></span><h2>pyodbc<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.pyodbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the PyODBC driver.<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The PyODBC for MySQL dialect is not well supported, and
is subject to unresolved character encoding issues
which exist within the current ODBC drivers available.
(see <a class="reference external" href="http://code.google.com/p/pyodbc/issues/detail?id=25">http://code.google.com/p/pyodbc/issues/detail?id=25</a>).
Other dialects for MySQL are recommended.</p>
</div>
</p>
<div class="section" id="dialect-mysql-pyodbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-pyodbc-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for PyODBC is available at:
<a class="reference external" href="http://pypi.python.org/pypi/pyodbc/">http://pypi.python.org/pypi/pyodbc/</a></p>
</div>
<div class="section" id="dialect-mysql-pyodbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-pyodbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mysql</span><span class="o">+</span><span class="n">pyodbc</span><span class="p">:</span><span class="o">//<</span><span class="n">username</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">dsnname</span><span class="o">></span></pre></div>
</div>
</p>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.mysql.zxjdbc">
<span id="zxjdbc"></span><h2>zxjdbc<a class="headerlink" href="#module-sqlalchemy.dialects.mysql.zxjdbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the MySQL database via the zxjdbc for Jython driver.<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Jython is not supported by current versions of SQLAlchemy. The
zxjdbc dialect should be considered as experimental.</p>
</div>
</p>
<div class="section" id="dialect-mysql-zxjdbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-mysql-zxjdbc-url" title="Permalink to this headline">¶</a></h3>
<p>Drivers for this database are available at:
<a class="reference external" href="http://dev.mysql.com/downloads/connector/j/">http://dev.mysql.com/downloads/connector/j/</a></p>
</div>
<div class="section" id="dialect-mysql-zxjdbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-mysql-zxjdbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mysql</span><span class="o">+</span><span class="n">zxjdbc</span><span class="p">:</span><span class="o">//<</span><span class="n">user</span><span class="o">></span><span class="p">:</span><span class="o"><</span><span class="n">password</span><span class="o">>@<</span><span class="n">hostname</span><span class="o">></span><span class="p">[:</span><span class="o"><</span><span class="n">port</span><span class="o">></span><span class="p">]</span><span class="o">/<</span><span class="n">database</span><span class="o">></span></pre></div>
</div>
</p>
</div>
<div class="section" id="character-sets">
<h3>Character Sets<a class="headerlink" href="#character-sets" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy zxjdbc dialects pass unicode straight through to the
zxjdbc/JDBC layer. To allow multiple character sets to be sent from the
MySQL Connector/J JDBC driver, by default SQLAlchemy sets its
<code class="docutils literal notranslate"><span class="pre">characterEncoding</span></code> connection property to <code class="docutils literal notranslate"><span class="pre">UTF-8</span></code>. It may be
overridden via a <code class="docutils literal notranslate"><span class="pre">create_engine</span></code> URL parameter.</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="postgresql.html" title="previous chapter">PostgreSQL</a>
Next:
<a href="sqlite.html" title="next chapter">SQLite</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
>
789
