l5 ^̽d d Z ddlmZ ddlmZ ddlZddlZddlmZ ddl m
Z
ddl m Z
dd lmZ dd
lmZ dd lmZ dd lmZ dd
lmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlmZ ddlm Z ddlm Z ddlm Z ddlm Z ddlm Z ddlm!Z! ddlm"Z" ddlm#Z# dd lm$Z$ dd lm%Z% dd lm&Z& dd lm'Z' dd lm(Z( dd!lm)Z) dd"lm*Z* dd#lm+Z+ dd$lm,Z, dd%lm-Z- dd&lm.Z. dd'lm/Z/ dd(lm0Z0 dd)lm1Z1 dd*lm2Z2 dd+lm3Z3 dd,lm4Z4 d-d.l m5Z5 d-d/l m6Z6 d-d0l m7Z8 d-d1l m9Z9 d-d2l mZ: d-d3l m;Z; d-d4lZ> d-d6l9m?Z? d-d7l9m@Z@ d-d8l9mAZA d-d3l9m;ZB d-d9lmCZC d-d:lmDZD d-d;lmEZE d-dl;mHZH d-d?l;mIZI eHjJ rdd@lKmLZL eMg dA ZN ejO dBejP ejQ z ZR ejO dCejP ejQ z ZSe.ZTeZUeZVe#ZWe%ZXe0ZYeDZZeCZ[eGZ\e(Z]e*Z^e Z_e3Z`e$Zae'Zbe2Zce-Zde4Zee/Zfe Zge,Zhe1Zie&ZjeZke)Zle Zme Zne+Zoe!Zpe"Zqeeeeeee:jr e)e:js e!e:jt e.e:ju ee:jv ee:j ee:j j ee:j j ei Zwi dDedEeCdFe dGeDdHeEdIe dJeFdKe dLe dMe dNedOe dPe!dQe"dRe"dSedTe#i dUe$dVe%dWe&dXe'dYe(dZe*d[e)d\ed]e,d^e-d_e.d`e/dae0dbe1dce2ddeGdee3dfe4iZx G dg dhe=jy Zz G di dje>j{ Z| G dk dle>j} Z~ G dm dne>j Z G do dpe>j Ze6j G dq dre=j Z G ds dte ZdS )uaz
.. dialect:: mysql
:name: MySQL
Supported Versions and Features
-------------------------------
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.
See the official MySQL documentation for detailed information about features
supported in any given server release.
.. _mysql_connection_timeouts:
Connection Timeouts and Disconnects
-----------------------------------
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 :paramref:`_sa.create_engine.pool_recycle` 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::
engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)
For more comprehensive disconnect detection of pooled connections, including
accommodation of server restarts and network issues, a pre-ping approach may
be employed. See :ref:`pool_disconnects` for current approaches.
.. seealso::
:ref:`pool_disconnects` - Background on several techniques for dealing
with timed out connections as well as database restarts.
.. _mysql_storage_engines:
CREATE TABLE arguments including Storage Engines
------------------------------------------------
MySQL's CREATE TABLE syntax includes a wide array of special options,
including ``ENGINE``, ``CHARSET``, ``MAX_ROWS``, ``ROW_FORMAT``,
``INSERT_METHOD``, and many more.
To accommodate the rendering of these arguments, specify the form
``mysql_argument_name="value"``. For example, to specify a table with
``ENGINE`` of ``InnoDB``, ``CHARSET`` of ``utf8mb4``, and ``KEY_BLOCK_SIZE``
of ``1024``::
Table('mytable', metadata,
Column('data', String(32)),
mysql_engine='InnoDB',
mysql_charset='utf8mb4',
mysql_key_block_size="1024"
)
The MySQL dialect will normally transfer any keyword specified as
``mysql_keyword_name`` to be rendered as ``KEYWORD_NAME`` in the
``CREATE TABLE`` 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 ``DATA DIRECTORY``
(e.g. ``mysql_data_directory``), ``CHARACTER SET`` (e.g.
``mysql_character_set``) and ``INDEX DIRECTORY`` (e.g.
``mysql_index_directory``).
The most common argument is ``mysql_engine``, which refers to the storage
engine for the table. Historically, MySQL server installations would default
to ``MyISAM`` for this value, although newer versions may be defaulting
to ``InnoDB``. The ``InnoDB`` engine is typically preferred for its support
of transactions and foreign keys.
A :class:`_schema.Table`
that is created in a MySQL database with a storage engine
of ``MyISAM`` 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 ``CREATE TABLE`` statement accepts foreign key options, when using the
``MyISAM`` storage engine these arguments are discarded. Reflecting such a
table will also produce no foreign key constraint information.
For fully atomic transactions as well as support for foreign key
constraints, all participating ``CREATE TABLE`` statements must specify a
transactional engine, which in the vast majority of cases is ``InnoDB``.
.. seealso::
`The InnoDB Storage Engine
`_ -
on the MySQL website.
Case Sensitivity and Table Reflection
-------------------------------------
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 *always* 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.
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.
.. _mysql_isolation_level:
Transaction Isolation Level
---------------------------
All MySQL dialects support setting of transaction isolation level both via a
dialect-specific parameter :paramref:`_sa.create_engine.isolation_level`
accepted
by :func:`_sa.create_engine`, as well as the
:paramref:`.Connection.execution_options.isolation_level` argument as passed to
:meth:`_engine.Connection.execution_options`.
This feature works by issuing the
command ``SET SESSION TRANSACTION ISOLATION LEVEL `` for each new
connection. For the special AUTOCOMMIT isolation level, DBAPI-specific
techniques are used.
To set isolation level using :func:`_sa.create_engine`::
engine = create_engine(
"mysql://scott:tiger@localhost/test",
isolation_level="READ UNCOMMITTED"
)
To set using per-connection execution options::
connection = engine.connect()
connection = connection.execution_options(
isolation_level="READ COMMITTED"
)
Valid values for ``isolation_level`` include:
* ``READ COMMITTED``
* ``READ UNCOMMITTED``
* ``REPEATABLE READ``
* ``SERIALIZABLE``
* ``AUTOCOMMIT``
The special ``AUTOCOMMIT`` 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
``SELECT @@autocommit;``.
.. seealso::
:ref:`dbapi_autocommit`
AUTO_INCREMENT Behavior
-----------------------
When creating tables, SQLAlchemy will automatically set ``AUTO_INCREMENT`` on
the first :class:`.Integer` primary key column which is not marked as a
foreign key::
>>> t = Table('mytable', metadata,
... Column('mytable_id', Integer, primary_key=True)
... )
>>> t.create()
CREATE TABLE mytable (
id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
)
You can disable this behavior by passing ``False`` to the
:paramref:`_schema.Column.autoincrement` argument of :class:`_schema.Column`.
This flag
can also be used to enable auto-increment on a secondary column in a
multi-column key for some storage engines::
Table('mytable', metadata,
Column('gid', Integer, primary_key=True, autoincrement=False),
Column('id', Integer, primary_key=True)
)
.. _mysql_ss_cursors:
Server Side Cursors
-------------------
Server-side cursor support is available for the MySQLdb and PyMySQL dialects.
From a MySQL point of view this means that the ``MySQLdb.cursors.SSCursor`` or
``pymysql.cursors.SSCursor`` class is used when building up the cursor which
will receive results. The most typical way of invoking this feature is via the
:paramref:`.Connection.execution_options.stream_results` connection execution
option. Server side cursors can also be enabled for all SELECT statements
unconditionally by passing ``server_side_cursors=True`` to
:func:`_sa.create_engine`.
.. versionadded:: 1.1.4 - added server-side cursor support.
.. _mysql_unicode:
Unicode
-------
Charset Selection
~~~~~~~~~~~~~~~~~
Most MySQL DBAPIs offer the option to set the client character set for
a connection. This is typically delivered using the ``charset`` parameter
in the URL, such as::
e = create_engine(
"mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")
This charset is the **client character set** for the connection. Some
MySQL DBAPIs will default this to a value such as ``latin1``, and some
will make use of the ``default-character-set`` setting in the ``my.cnf``
file as well. Documentation for the DBAPI in use should be consulted
for specific behavior.
The encoding used for Unicode has traditionally been ``'utf8'``. However,
for MySQL versions 5.5.3 on forward, a new MySQL-specific encoding
``'utf8mb4'`` has been introduced, and as of MySQL 8.0 a warning is emitted
by the server if plain ``utf8`` is specified within any server-side
directives, replaced with ``utf8mb3``. 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::
e = create_engine(
"mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")
All modern DBAPIs should support the ``utf8mb4`` charset.
In order to use ``utf8mb4`` encoding for a schema that was created with legacy
``utf8``, changes to the MySQL schema and/or server configuration may be
required.
.. seealso::
`The utf8mb4 Character Set \
`_ - \
in the MySQL documentation
.. _mysql_binary_introducer:
Dealing with Binary Data Warnings and Unicode
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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::
default.py:509: Warning: (1300, "Invalid utf8mb4 character string:
'F9876A'")
cursor.execute(statement, parameters)
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 :class:`.LargeBinary` 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::
INSERT INTO table (data) VALUES (_binary %s)
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 ``binary_prefix=true`` to the URL to repair this warning::
# mysqlclient
engine = create_engine(
"mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")
# PyMySQL
engine = create_engine(
"mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")
The ``binary_prefix`` flag may or may not be supported by other MySQL drivers.
SQLAlchemy itself cannot render this ``_binary`` 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.
.. seealso::
`Character set introducers `_ - on the MySQL website
ANSI Quoting Style
------------------
MySQL features two varieties of identifier "quoting style", one using
backticks and the other using quotes, e.g. ```some_identifier``` vs.
``"some_identifier"``. All MySQL dialects detect which version
is in use by checking the value of ``sql_mode`` when a connection is first
established with a particular :class:`_engine.Engine`.
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.
MySQL SQL Extensions
--------------------
Many of the MySQL SQL extensions are handled through SQLAlchemy's generic
function and operator support::
table.select(table.c.password==func.md5('plaintext'))
table.select(table.c.username.op('regexp')('^[a-d]'))
And of course any valid MySQL statement can be executed as a string as well.
Some limited direct support for MySQL extensions to SQL is currently
available.
* INSERT..ON DUPLICATE KEY UPDATE: See
:ref:`mysql_insert_on_duplicate_key_update`
* SELECT pragma, use :meth:`_expression.Select.prefix_with` and
:meth:`_query.Query.prefix_with`::
select(...).prefix_with(['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])
* UPDATE with LIMIT::
update(..., mysql_limit=10)
* optimizer hints, use :meth:`_expression.Select.prefix_with` and
:meth:`_query.Query.prefix_with`::
select(...).prefix_with("/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */")
* index hints, use :meth:`_expression.Select.with_hint` and
:meth:`_query.Query.with_hint`::
select(...).with_hint(some_table, "USE INDEX xyz")
.. _mysql_insert_on_duplicate_key_update:
INSERT...ON DUPLICATE KEY UPDATE (Upsert)
------------------------------------------
MySQL allows "upserts" (update or insert)
of rows into a table via the ``ON DUPLICATE KEY UPDATE`` clause of the
``INSERT`` 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.
SQLAlchemy provides ``ON DUPLICATE KEY UPDATE`` support via the MySQL-specific
:func:`.mysql.insert()` function, which provides
the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`::
from sqlalchemy.dialects.mysql import insert
insert_stmt = insert(my_table).values(
id='some_existing_id',
data='inserted value')
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
data=insert_stmt.inserted.data,
status='U'
)
conn.execute(on_duplicate_key_stmt)
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.
``ON DUPLICATE KEY UPDATE`` 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 normally specified using
keyword arguments passed to the
:meth:`~.mysql.Insert.on_duplicate_key_update`
given column key values (usually the name of the column, unless it
specifies :paramref:`_schema.Column.key`
) as keys and literal or SQL expressions
as values::
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
data="some data",
updated_at=func.current_timestamp(),
)
In a manner similar to that of :meth:`.UpdateBase.values`, other parameter
forms are accepted, including a single dictionary::
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
{"data": "some data", "updated_at": func.current_timestamp()},
)
as well as a list of 2-tuples, which will automatically provide
a parameter-ordered UPDATE statement in a manner similar to that described
at :ref:`updates_order_parameters`. Unlike the :class:`_expression.Update`
object,
no special flag is needed to specify the intent since the argument form is
this context is unambiguous::
on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
[
("data", "some data"),
("updated_at", func.current_timestamp()),
],
)
.. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within
MySQL ON DUPLICATE KEY UPDATE
.. warning::
The :meth:`_expression.Insert.on_duplicate_key_update`
method does **not** take into
account Python-side default UPDATE values or generation functions, e.g.
e.g. those specified using :paramref:`_schema.Column.onupdate`.
These values will not be exercised for an ON DUPLICATE KEY style of UPDATE,
unless they are manually specified explicitly in the parameters.
In order to refer to the proposed insertion row, the special alias
:attr:`~.mysql.Insert.inserted` is available as an attribute on
the :class:`.mysql.Insert` object; this object is a
:class:`_expression.ColumnCollection` which contains all columns of the target
table::
from sqlalchemy.dialects.mysql import insert
stmt = insert(my_table).values(
id='some_id',
data='inserted value',
author='jlh')
do_update_stmt = stmt.on_duplicate_key_update(
data="updated value",
author=stmt.inserted.author
)
conn.execute(do_update_stmt)
When rendered, the "inserted" namespace will produce the expression
``VALUES()``.
.. versionadded:: 1.2 Added support for MySQL ON DUPLICATE KEY UPDATE clause
rowcount Support
----------------
SQLAlchemy standardizes the DBAPI ``cursor.rowcount`` 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 ``constants.CLIENT.FOUND_ROWS``
flag, or whatever is equivalent for the target dialect, upon connection.
This setting is currently hardcoded.
.. seealso::
:attr:`_engine.ResultProxy.rowcount`
CAST Support
------------
MySQL documents the CAST operator as available in version 4.0.2. When using
the SQLAlchemy :func:`.cast` function, SQLAlchemy
will not render the CAST token on MySQL before this version, based on server
version detection, instead rendering the internal expression directly.
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
:ref:`sqlalchemy.ext.compiler_toplevel` system, as per the recipe below::
from sqlalchemy.sql.expression import Cast
from sqlalchemy.ext.compiler import compiles
@compiles(Cast, 'mysql')
def _check_mysql_version(element, compiler, **kw):
if compiler.dialect.server_version_info < (4, 1, 0):
return compiler.process(element.clause, **kw)
else:
return compiler.visit_cast(element, **kw)
The above function, which only needs to be declared once
within an application, overrides the compilation of the
:func:`.cast` construct to check for version 4.1.0 before
fully rendering CAST; else the internal element of the
construct is rendered directly.
.. _mysql_indexes:
MySQL Specific Index Options
----------------------------
MySQL-specific extensions to the :class:`.Index` construct are available.
Index Length
~~~~~~~~~~~~~
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
``mysql_length`` parameter::
Index('my_index', my_table.c.data, mysql_length=10)
Index('a_b_idx', my_table.c.a, my_table.c.b, mysql_length={'a': 4,
'b': 9})
Prefix lengths are given in characters for nonbinary string types and in bytes
for binary string types. The value passed to the keyword argument *must* 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.
Index Prefixes
~~~~~~~~~~~~~~
MySQL storage engines permit you to specify an index prefix when creating
an index. SQLAlchemy provides this feature via the
``mysql_prefix`` parameter on :class:`.Index`::
Index('my_index', my_table.c.data, mysql_prefix='FULLTEXT')
The value passed to the keyword argument will be simply passed through to the
underlying CREATE INDEX, so it *must* be a valid index prefix for your MySQL
storage engine.
.. versionadded:: 1.1.5
.. seealso::
`CREATE INDEX `_ - MySQL documentation
Index Types
~~~~~~~~~~~~~
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
``mysql_using`` parameter on :class:`.Index`::
Index('my_index', my_table.c.data, mysql_using='hash')
As well as the ``mysql_using`` parameter on :class:`.PrimaryKeyConstraint`::
PrimaryKeyConstraint("data", mysql_using='hash')
The value passed to the keyword argument will be simply passed through to the
underlying CREATE INDEX or PRIMARY KEY clause, so it *must* be a valid index
type for your MySQL storage engine.
More information can be found at:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Index Parsers
~~~~~~~~~~~~~
CREATE FULLTEXT INDEX in MySQL also supports a "WITH PARSER" option. This
is available using the keyword argument ``mysql_with_parser``::
Index(
'my_index', my_table.c.data,
mysql_prefix='FULLTEXT', mysql_with_parser="ngram")
.. versionadded:: 1.3
.. _mysql_foreign_keys:
MySQL Foreign Keys
------------------
MySQL's behavior regarding foreign keys has some important caveats.
Foreign Key Arguments to Avoid
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MySQL does not support the foreign key arguments "DEFERRABLE", "INITIALLY",
or "MATCH". Using the ``deferrable`` or ``initially`` keyword argument with
:class:`_schema.ForeignKeyConstraint` or :class:`_schema.ForeignKey`
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::
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import ForeignKeyConstraint
@compiles(ForeignKeyConstraint, "mysql")
def process(element, compiler, **kw):
element.deferrable = element.initially = None
return compiler.visit_foreign_key_constraint(element, **kw)
.. versionchanged:: 0.9.0 - the MySQL backend no longer silently ignores
the ``deferrable`` or ``initially`` keyword arguments of
:class:`_schema.ForeignKeyConstraint` and :class:`_schema.ForeignKey`.
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.
.. versionadded:: 0.9.0 - the MySQL backend will raise a
:class:`.CompileError` when the ``match`` keyword is used with
:class:`_schema.ForeignKeyConstraint` or :class:`_schema.ForeignKey`.
Reflection of Foreign Key Constraints
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Not all MySQL storage engines support foreign keys. When using the
very common ``MyISAM`` MySQL storage engine, the information loaded by table
reflection will not include foreign keys. For these tables, you may supply a
:class:`~sqlalchemy.ForeignKeyConstraint` at reflection time::
Table('mytable', metadata,
ForeignKeyConstraint(['other_id'], ['othertable.other_id']),
autoload=True
)
.. seealso::
:ref:`mysql_storage_engines`
.. _mysql_unique_constraints:
MySQL Unique Constraints and Reflection
---------------------------------------
SQLAlchemy supports both the :class:`.Index` construct with the
flag ``unique=True``, indicating a UNIQUE index, as well as the
:class:`.UniqueConstraint` 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".
When reflecting these constructs, the
:meth:`_reflection.Inspector.get_indexes`
and the :meth:`_reflection.Inspector.get_unique_constraints`
methods will **both**
return an entry for a UNIQUE index in MySQL. However, when performing
full table reflection using ``Table(..., autoload=True)``,
the :class:`.UniqueConstraint` construct is
**not** part of the fully reflected :class:`_schema.Table` construct under any
circumstances; this construct is always represented by a :class:`.Index`
with the ``unique=True`` setting present in the :attr:`_schema.Table.indexes`
collection.
TIMESTAMP / DATETIME issues
---------------------------
.. _mysql_timestamp_onupdate:
Rendering ON UPDATE CURRENT TIMESTAMP for MySQL's explicit_defaults_for_timestamp
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MySQL has historically expanded the DDL for the :class:`_types.TIMESTAMP`
datatype into the phrase "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP", which includes non-standard SQL that automatically updates
the column with the current timestamp when an UPDATE occurs, eliminating the
usual need to use a trigger in such a case where server-side update changes are
desired.
MySQL 5.6 introduced a new flag `explicit_defaults_for_timestamp
`_ which disables the above behavior,
and in MySQL 8 this flag defaults to true, meaning in order to get a MySQL
"on update timestamp" without changing this flag, the above DDL must be
rendered explicitly. Additionally, the same DDL is valid for use of the
``DATETIME`` datatype as well.
SQLAlchemy's MySQL dialect does not yet have an option to generate
MySQL's "ON UPDATE CURRENT_TIMESTAMP" clause, noting that this is not a general
purpose "ON UPDATE" as there is no such syntax in standard SQL. SQLAlchemy's
:paramref:`_schema.Column.server_onupdate` parameter is currently not related
to this special MySQL behavior.
To generate this DDL, make use of the :paramref:`_schema.Column.server_default`
parameter and pass a textual clause that also includes the ON UPDATE clause::
from sqlalchemy import Table, MetaData, Column, Integer, String, TIMESTAMP
from sqlalchemy import text
metadata = MetaData()
mytable = Table(
"mytable",
metadata,
Column('id', Integer, primary_key=True),
Column('data', String(50)),
Column(
'last_updated',
TIMESTAMP,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
)
)
The same instructions apply to use of the :class:`_types.DateTime` and
:class:`_types.DATETIME` datatypes::
from sqlalchemy import DateTime
mytable = Table(
"mytable",
metadata,
Column('id', Integer, primary_key=True),
Column('data', String(50)),
Column(
'last_updated',
DateTime,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
)
)
Even though the :paramref:`_schema.Column.server_onupdate` feature does not
generate this DDL, it still may be desirable to signal to the ORM that this
updated value should be fetched. This syntax looks like the following::
from sqlalchemy.schema import FetchedValue
class MyClass(Base):
__tablename__ = 'mytable'
id = Column(Integer, primary_key=True)
data = Column(String(50))
last_updated = Column(
TIMESTAMP,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
server_onupdate=FetchedValue()
)
.. _mysql_timestamp_null:
TIMESTAMP Columns and NULL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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::
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
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.
This behavior of MySQL can be changed on the MySQL side using the
`explicit_defaults_for_timestamp
`_ 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.
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 ``nullable=False``. In order to
accommodate newer databases that specify ``explicit_defaults_for_timestamp``,
SQLAlchemy also emits NOT NULL for TIMESTAMP columns that do specify
``nullable=False``. The following example illustrates::
from sqlalchemy import MetaData, Integer, Table, Column, text
from sqlalchemy.dialects.mysql import TIMESTAMP
m = MetaData()
t = Table('ts_test', m,
Column('a', Integer),
Column('b', Integer, nullable=False),
Column('c', TIMESTAMP),
Column('d', TIMESTAMP, nullable=False)
)
from sqlalchemy import create_engine
e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
m.create_all(e)
output::
CREATE TABLE ts_test (
a INTEGER,
b INTEGER NOT NULL,
c TIMESTAMP NULL,
d TIMESTAMP NOT NULL
)
.. versionchanged:: 1.0.0 - SQLAlchemy now renders NULL or NOT NULL in all
cases for TIMESTAMP columns, to accommodate
``explicit_defaults_for_timestamp``. Prior to this version, it will
not render "NOT NULL" for a TIMESTAMP column that is ``nullable=False``.
)array) defaultdictN)literal_column)visitors )
reflection)ENUM)SET)JSON)
JSONIndexType) JSONPathType)
_FloatType) _IntegerType)
_MatchType) _NumericType) _StringType)BIGINT)BIT)CHAR)DATETIME)DECIMAL)DOUBLE)FLOAT)INTEGER)LONGBLOB)LONGTEXT)
MEDIUMBLOB) MEDIUMINT)
MEDIUMTEXT)NCHAR)NUMERIC)NVARCHAR)REAL)SMALLINT)TEXT)TIME) TIMESTAMP)TINYBLOB)TINYINT)TINYTEXT)VARCHAR)YEAR )exc)log)schema)sql)types)util)default)compiler)elements) functions) operators)BINARY)BLOB)BOOLEAN)DATE) VARBINARY)compat) topological)Any(!
accessibleactionaddadminallalteranalyzeandr asasc
asensitivebeforebetweenbigintbinaryblobbothbycallcascadecasechangechar charactercheckcollatecolumncolumns condition
constraintcontinueconvertcreatecrosscube cume_dist current_date current_timecurrent_timestamp current_usercursordatabase databasesday_hourday_microsecond
day_minute
day_seconddecdecimaldeclarer4 delayeddeletedescdescribe
deterministicdistinct distinctrowdivdoubledropdualeachelseelseifemptyenclosedescapedexceptexistsexitexplainfalsefetchfields first_valuefloatfloat4float8forforceforeignfromfulltextfunctiongeneral generatedgetgrantgroupgroupinggroupshaving
high_priorityhour_microsecond hour_minute hour_secondifignoreignore_server_idsinindexinfileinnerinout insensitiveinsertintint1int2int3int4int8integerintervalintoio_after_gtidsio_before_gtidsisiteratejoin
json_tablekeykeyskill
last_valuelateralleadingleaveleftlevellikelimitlinearr linesload localtimelocaltimestamplocklonglongbloblongtextloop low_priority master_bindmaster_heartbeat_period master_ssl_verify_server_certr matchmaxvalue
mediumblob mediumint
mediumtextmember middleintminute_microsecond
minute_secondmodmodemodifiesnaturalno_write_to_binlognot nth_valuentilenullnumericofonone_shotoptimizeoptimizer_costsoption
optionallyororderoutouteroutfileover partition percent_rankpersist persist_only precisionprimary
privileges procedurepurgeranger rankread read_onlyr
read_writer readsreal recursive
referencesregexpreleaserenamerepeatreplacerequireresignalrestrictreturnrevokerightrlikerolerow
row_numberrowsr0 schemassecond_microsecondselect sensitive separatorsetshowsignalslowsmallintsonamespatialspecificr1 sql_after_gtidssql_before_gtidssql_big_resultsql_calc_found_rowssql_small_result sqlexceptionsqlstate
sqlwarningsslstartingstatusstored
straight_joinsystemtabletables
terminatedtextthentimetinyblobtinyinttinytexttotrailingtriggertrueundounionuniqueunlockunsignedupdateusageuseusingutc_dateutc_time
utc_timestampvalues varbinaryvarchar varcharactervaryingvirtualwhenwherewhilewindowwithwritex509xor
year_monthzerofillz@\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|LOAD +DATA|REPLACE)z%\s*SET\s+(?:(?:GLOBAL|SESSION)\s+)?\wrN rO bitrP booleanrW datedatetimerq r{ enumfixedr r r jsonr r r r r ncharnvarcharr r r r3 r5 timestampr6 r7 r8 rJ rK yearc e Zd Zd Zd ZdS )MySQLExecutionContextc 6 t | S N)
AUTOCOMMIT_REr )self statements r/builddir/build/BUILD/cloudlinux-venv-1.0.10/venv/lib64/python3.11/site-packages/sqlalchemy/dialects/mysql/base.pyshould_autocommit_textz,MySQLExecutionContext.should_autocommit_text s ""9--- c ~ | j j r$| j | j j S t rg )dialect supports_server_side_cursors_dbapi_connectionri _sscursorNotImplementedErrorri s rk create_server_side_cursorz/MySQLExecutionContext.create_server_side_cursor s7 < 4 ()00 1GHH H%'' 'rm N)__name__
__module__ __qualname__rl ru rm rk re re s2 . . .( ( ( ( (rm re c . e Zd ZdZ ej j Ze ddi d Z d Z
d Z d Z d Z
d Zd
Zd Zd Zd
Zd!dZd Z fdZd Zd Zd Zd"dZd Zd Zd Z d Z d Z d Z d Z d Z!d Z"d Z# xZ$S )#
MySQLCompilerT milliseconds millisecondc L | j r | j d d }|j dS dS )zlCalled when a ``SELECT`` statement has no froms,
and no ``FROM`` clause is to be appended.
selectableNz
FROM DUAL )stack _whereclause)ri stmts rk default_fromzMySQLCompiler.default_from' s1
: $:b>,/D ,#|rrm c 2 d| | z S )Nzrand%s)function_argspecri fnkws rk visit_random_funcz MySQLCompiler.visit_random_func3 s $//3333rm c dS )Nz SYSDATE()ry r s rk visit_sysdate_funcz MySQLCompiler.visit_sysdate_func6 s {rm c |j j t j u r+d | j |j fi |d | j |j fi |dS d | j |j fi |d | j |j fi |d}|j j t j u r,d | j |j fi |d | j |j fi |d}n|j j t j u r,d | j |j fi |d | j |j fi |d}na|j j t j u rd }nF|j j t j
u r,d
| j |j fi |d | j |j fi |d }nd }|d
z |z dz S )Nz
JSON_EXTRACT(, )zCASE JSON_EXTRACT(z) WHEN 'null' THEN NULLzELSE CAST(JSON_EXTRACT(z) AS SIGNED INTEGER)z) AS DECIMAL(10, 6))z WHEN true THEN true ELSE falsez ELSE JSON_UNQUOTE(JSON_EXTRACT(z))zELSE JSON_EXTRACT(%s, %s) z END) type_type_affinitysqltypesr processr r IntegerNumericBooleanString)ri rO operatorr case_expressiontype_expressions rk _render_json_extract_from_binaryz.MySQLCompiler._render_json_extract_from_binary9 s
; % 6 6 6 V[//B//// V\00R0000
7
DL + + + + + + DL , , , , , ,
; %)9 9 9 9 !DL333333 DL 444444
O [
'8+;
;
;
;
!DL333333 DL 444444
O [
'8+;
;
; ?OO
[
'8?
:
:
: V[//B//// V\00R0000 OO :O $6??rm c | j ||fi |S rg r ri rO r r s rk visit_json_getitem_op_binaryz*MySQLCompiler.visit_json_getitem_op_binarym 4t4VXLLLLLrm c | j ||fi |S rg r r s rk !visit_json_path_getitem_op_binaryz/MySQLCompiler.visit_json_path_getitem_op_binaryp r rm c
j
j rHd j D }t |
fd|D fd
j j D z }n
j j }g }fd|D D ] j j }t j | rFt j d | j
} | d }nF fd}t j |i | } | d } j j } | | d | t j t d
|D z
}
|
rFt' j d j j j d d
d |
D dd
| z S )Nc 6 g | ]}t j | S ry )r6 _column_as_key.0r s rk
z?MySQLCompiler.visit_on_duplicate_key_update..w s3 " " " ',," " "rm c P g | ]"}|j j v j j | #S ry )r0 c)r r rj s rk r z?MySQLCompiler.visit_on_duplicate_key_update..| s= )/+++ !#&+++rm c & g | ]
}|j v |S ry r )r r ordered_keyss rk r z?MySQLCompiler.visit_on_duplicate_key_update.. s% KKKql1J1J1J1J1Jrm c 3 8 K | ]}|j j v |V d S rg )r rB )r col on_duplicates rk z>MySQLCompiler.visit_on_duplicate_key_update.. s2 MMscg 9L.L.Ls.L.L.L.LMMrm )type_F)
use_schemac P t | t j r.| j j r"| } j | _ | S t | t j rB| j j u r4t dj
j z dz } | S d S )NzVALUES(r )
isinstancer6
BindParameterr _isnull_clone ColumnClauser0 inserted_aliasr preparerquotename)objr[ r ri s rk r z.replace s "3(>??$ H ,$ "jjll#); "
"3(=>>
$ I )D D D , %
(;(;FK(H(H H3 N #
$trm z = c 3 $ K | ] }|j V d S rg r r r s rk r z>MySQLCompiler.visit_on_duplicate_key_update.. s$ 5J5Jae5J5J5J5J5J5Jrm z?Additional column names not matching any column keys in table 'z': r c 3 K | ] }d |z V
dS )'%s'Nry r s rk r z>MySQLCompiler.visit_on_duplicate_key_update.. s& @ @avz @ @ @ @ @ @rm zON DUPLICATE KEY UPDATE )current_executable_parameter_orderingr r0 r rB r r6 _is_literalr r r
self_groupr replacement_traverser r r appendr3 warnrj r )ri r r parameter_orderingcolsclausesval
value_textr name_text non_matchingr[ r rj s `` @@@rk visit_on_duplicate_key_updatez+MySQLCompiler.visit_on_duplicate_key_updates sY + + %" "';" " " 122L - LKKKIO -KKK LDD ?$DMMMMdMMM ! @ ! @F %fj1C#C((
N ,T3fkJJJ !\\#..*:*:u\ M M
$ $ $ $ $ $ $( 3CWEE !\\#..*:*:u\ M M
++FK88I NN ::> ? ? ? ?<.//#5J5JT5J5J5J2J2JJ II N(---YY @ @< @ @ @@@@
*DIIg,>,>>>rm c X d | j |j fi |d | j |j fi |dS )Nzconcat(r r r r r r s rk visit_concat_op_binaryz$MySQLCompiler.visit_concat_op_binary L DL + + + + + + DL , , , , , ,
rm c X d | j |j fi |d | j |j fi |dS )NzMATCH (z ) AGAINST (z IN BOOLEAN MODE)r r s rk visit_match_op_binaryz#MySQLCompiler.visit_match_op_binary r rm c |S rg ry )ri r0 r3 s rk get_from_hint_textz MySQLCompiler.get_from_hint_text s rm Nc V | |j | j }t |t j r | j ||j fi |S t |t j rt |dd rdS dS t |t j
rdS t |t j t j t j
t j f r | j j | S t |t j rOt |t$ t& f s3t) j | }| j j | S t |t j rdS t |t j rdS t |t j r3| j j | dd S t |t j r+| j j r | j j | S d S )
NrA FzUNSIGNED INTEGERzSIGNED INTEGERr r9 r r! r ) r dialect_implro r r
TypeDecoratorvisit_typeclauseimplr getattrr' r DateTimeDateTime
type_compilerr r r r
r _adapt_string_for_cast_Binaryr r! r Float_support_float_cast)ri
typeclauser r adapteds rk r z MySQLCompiler.visit_typeclause s = O00 >>E eX 3 4 4 & (4(UZFF2FF F
x /
0
0 $ uj%00
())''
x 1
2
2 :
!
<-55e<< <
x
/
/
D#;9
9
1%88G<-55g>> >
x /
0
0 8
x}
-
- 6
x /
0
0
<-55e<<DD9
uhn - - 0 <-55e<< <4rm c | j j s9t j d | j |j fi |S | |j }|ct j d| j j |j j z | j |j fi |S d | j |j fi |d|dS )NzFCurrent MySQL version does not support CAST; the CAST will be skipped.zMDatatype %s does not support CAST on MySQL/MariaDb; the CAST will be skipped.zCAST(z AS r )
ro _supports_castr3 r r clauser r r r )ri castr r s rk
visit_castzMySQLCompiler.visit_cast s |* @ I2
4< 6 6 8 8??B?? ? T_-- = I,,,44T_5IJJK
4< 6 6 8 8??B?? ?
$04< #B#Br#B#B#B#BEEEJJrm c t t | || }| j j r| dd }|S )N\z\\)superr{ render_literal_valuero _backslash_escapesr )ri valuer __class__s rk r z"MySQLCompiler.render_literal_value sG mT**??uMM < * 0MM$//E rm c dS )Nr< ry ri elementr s rk
visit_truezMySQLCompiler.visit_true vrm c dS )Nr ry r s rk visit_falsezMySQLCompiler.visit_false s wrm c t |j t j r |j dz S |j rdS dS )zAdd special MySQL keywords in place of DISTINCT.
.. note::
this usage is deprecated. :meth:`_expression.Select.prefix_with`
should be used for special keywords at the start
of a SELECT.
r z DISTINCT r )r _distinctr3 string_typesupper)ri r r s rk get_select_precolumnsz#MySQLCompiler.get_select_precolumns sK f&(9 : : #))++c1 1
;2rm Fc |j rd}n |j rd}nd}d | j |j fddi|| | j |j fddi|d | j |j fi |f S )Nz FULL OUTER JOIN z LEFT OUTER JOIN z INNER JOIN r asfromT ON )fullisouterr r r r onclause)ri r r kwargs join_types rk
visit_joinzMySQLCompiler.visit_join# s 9 '+II
\ '+II&Iww TY>>t>v>> TZ????? T]55f55
rm c |j j rd}nd}|j j r} j j rqt j }|j j D ])}| t j | *|dd
fd|D z z
}|j j r|dz
}|j j r& j j
r|dz
}nt j d |S ) Nz LOCK IN SHARE MODEz FOR UPDATEz OF r c 3 : K | ]} j |fd ddV dS )TF)ashintr N)r )r r0 r ri s rk r z2MySQLCompiler.for_update_clause..A sR & & UH4EHHRHH& & & & & &rm z NOWAITz SKIP LOCKEDzbSKIP LOCKED ignored on non-supporting MariaDB backend. This will raise an error in SQLAlchemy 1.4.)_for_update_argr r ro supports_for_update_ofr3
OrderedSetrB sql_utilsurface_selectables_onlyr nowait skip_locked _is_mysqlr )ri r r tmpr1 r s ` ` rk for_update_clausez MySQLCompiler.for_update_clause5 s* ! & 'CC C ! $ )L _&&F+.
D
D
h ? B BCCCC 6DII & & & & & #& & &
C
! ( 9 C ! - |%
~% B
rm c |j |j }}||dS |6|d | j |fi |ddS d | j |fi |d | j |fi |S d | j |fi |S )Nr z
LIMIT r 18446744073709551615)
_limit_clause_offset_clauser )ri r r limit_clause
offset_clauses rk r zMySQLCompiler.limit_clauseT s
! $
M$92
& ## !DL 55"5555** $ !DL 55"5555 DL 44444 ! ', &2T\,%E%E"%E%E%EG Grm c d |j d| j j z d }|rd|z S d S )Nz%s_limitzLIMIT %s)r
r ro r )ri update_stmtr s rk update_limit_clausez!MySQLCompiler.update_limit_clause| s< "&&zDL4E'EtLL % %4rm c l d fd|gt | z D S )Nr c 3 8 K | ]} |j fd diV dS )r TN_compiler_dispatch)r tr ri s rk r z5MySQLCompiler.update_tables_clause.. sQ
!A 9 9d 9b 9 9
rm )r list)ri r!
from_table extra_fromsr s ` `rk update_tables_clausez"MySQLCompiler.update_tables_clause sT yy
\D$5$55
rm c d S rg ry )ri r! r) r*
from_hintsr s rk update_from_clausez MySQLCompiler.update_from_clause s trm c @ d}|rd}| | dd| S )z=If we have extra froms make sure we render any alias as hint.FT)r iscrudr r% )ri delete_stmtr) r* r s rk delete_table_clausez!MySQLCompiler.delete_table_clause s: F,, d6 -
rm c \ dd fd|g|z D z S )z4Render the DELETE .. USING clause specific to MySQL.zUSING r c 3 : K | ]} |j fd dV dS )T)r fromhintsNr% )r r' r- r ri s rk r z9MySQLCompiler.delete_extra_from_clause.. sS $
$
!A Odj O OB O O$
$
$
$
$
$
rm )r )ri r1 r) r* r- r s ` ``rk delete_extra_from_clausez&MySQLCompiler.delete_extra_from_clause s[ $)) $
$
$
$
$
$
\K/$
$
$
rm c dd d t | D d d t | D dz S )NzASELECT %(outer)s FROM (SELECT %(inner)s) as _empty_set WHERE 1!=1r c 3 & K | ] \ }}d |z V
dS )z 1 AS _in_%sNry r idxr s rk r z5MySQLCompiler.visit_empty_set_expr.. s? # #"U "C'# # # # # #rm c 3 & K | ] \ }}d |z V
dS )z_in_%sNry r9 s rk r z5MySQLCompiler.visit_empty_set_expr.. s; # #'1sEHsN# # # # # #rm )r r )r enumerate)ri
element_typess rk visit_empty_set_exprz"MySQLCompiler.visit_empty_set_expr s
' # #&/
&>&># # # # #5>}5M5M# # #
rm c t d| |j d| |j dS )NzNOT ( <=> r r r s rk visit_is_distinct_from_binaryz+MySQLCompiler.visit_is_distinct_from_binary s< LL % % % % LL & & & &
rm c p | |j d| |j S )Nr@ r r s rk visit_isnot_distinct_from_binaryz.MySQLCompiler.visit_isnot_distinct_from_binary s7 LL % % % % LL & & &
rm rg F)%rv rw rx 'render_table_with_column_in_update_fromr5 SQLCompiler extract_mapcopyrB r r r r r r r r r r r r r r r r r r r r" r+ r. r2 r6 r> rA rC
__classcell__r s @rk r{ r{ s .2+0&27799K
6777
4 4 4 2@ 2@ 2@hM M MM M MA? A? A?F
) ) ) )VK K K( "
$ >&H &H &HP
rm r{ c T e Zd Zd Zd Zd Z fdZd Zd Zd Z d Z
d Z d
Z xZ
S ) MySQLDDLCompilerc R | j | | j j |j | g}|j -| | |j t |j | j t j }|j s| d n |j r|r| d |
| }|| d|z |j }|D| j |t j }| d|z |j *||j j u r |j | d d | S ) zBuilds column DDL.)r NzNOT NULLNULLzDEFAULT zCOMMENT AUTO_INCREMENTr )r
format_columnro r r r computedr r _unwrapped_dialect_implr r' nullableget_column_default_stringcomment sql_compilerr r r0 _autoincrement_columnserver_defaultr )ri r[ r colspec is_timestampr4 rU literals rk get_column_specificationz)MySQLDDLCompiler.get_column_specification s
M ' ' / / L & . . V
/
? & NN4<<88 9 9 9! K / / = =
# NN: & & & & _ # # NN6 " " "0088 NN:/ 0 0 0. '<< ** G
NN:/ 0 0 0
L $&,<<<%- NN+ , , ,xx rm c g }t fd|j D }|j
|j |d< g d}t | | }t | | }t j g d| D ]}|| }|t j
v r, j |t j }|dv r| dd }d } |d
v rd} | | ||f t j g d | D ]}|| }|t j
v r, j |t j }| dd }d} | | ||f d | S ) z9Build table-level CREATE options like ENGINE and COLLATE.c 3 K | ]a\ }}| d j j z '|t j j dz d |fV bdS )z%s_r N)
startswithro r lenr )r kvri s rk r z5MySQLDDLCompiler.post_create_table.. s
1||EDL$5 566
s4<$%%)++
,
2
2
4
4a 8
rm NCOMMENT) PARTITION_BY
PARTITIONS
SUBPARTITIONSSUBPARTITION_BY))DEFAULT_CHARSETCOLLATE)DEFAULT_CHARACTER_SETri )CHARSETri )
CHARACTER_SETri )DATA_DIRECTORYINDEX_DIRECTORYrj rl rh DEFAULT_COLLATE_r =)
TABLESPACEzDEFAULT CHARACTER SETz
CHARACTER SETri ))rd re )rd rg )rd rf )re rf )re rg )rg rf )dictr
itemsrU r
difference intersectionr? sort _reflection_options_of_type_stringrV r r r r r r )
ri r0
table_optsoptspartition_optionsnonpart_options part_optionsoptargjoiners
` rk post_create_tablez"MySQLDDLCompiler.post_create_table sG
**,,
= $ #mDO
d))../@AA4yy--.?@@ #
# 7 # 7C s)Ck999'<< ** kk#s++F fkk3* 5 5 6 6 6 6 #
7 7C s)Ck999'<< ** ++c3''CF fkk3* 5 5 6 6 6 6xx
###rm c ( |j } | j }| |j } fd|j D } | }d}|j r|dz
}|j dd } | r|| dz z
}|d|d|dz
}|j
d d lt t r5d
fd t |j | D }n7d
fd |D }nd
| }|d
|z z
}|j
d d }
|
|d|
z
}|j
d d } | |d| | z z
}|S )Nc L g | ]}j t |t j sSt |t j r |j t j t j fvst |t j rt j | n|d d S )FT)
include_table
literal_binds)
rV r r r6 BinaryExpressionUnaryExpressionmodifierr8 desc_opasc_opr7 FunctionElementGrouping)r exprri s rk r z7MySQLDDLCompiler.visit_create_index..F s
% % tX%>??
#4)ABB
!M ) 193C D E E "$ (ABB E !$''' # "
&
rm zCREATE zUNIQUE mysql_prefixr INDEX r mysqllengthr c 3 K | ]8\ }}|j v rd ||j fz n|v r
d || fz nd|z V 9dS )%s(%d)z%sN)r )r r r r s rk r z6MySQLDDLCompiler.visit_create_index..l s $ $ "T
x6)) fSX&6 777 6>> !D&,#777 !D[
$ $ $ $ $ $rm c 3 & K | ] }d |fz V dS )r Nry )r r r s rk r z6MySQLDDLCompiler.visit_create_index..y s= $ $14HV},$ $ $ $ $ $rm z(%s) with_parserz
WITH PARSER rE USING %s)r _verify_index_tabler format_tabler0 expressions_prepared_index_namer? r
r dialect_optionsr rs r zipr )
ri ra r r r r0 r\ r r3 index_prefixparserrE r s
` @rk visit_create_indexz#MySQLDDLCompiler.visit_create_index@ s '''=%%ek22
)!
& ((// < I D |''== ' L3& &D TTT55511&w/9 &$''
)) $ $ $ $ &)):G%D%D $ $ $ )) $ $ $ $8?$ $ $ ii((G &w/
> D1 1D%g.w7 K8>>%#8#89 9D rm c t t | | }|j d d }|r |d| j | z z
}|S )Nr rE r )r rL visit_primary_key_constraintr r r )ri r^ r3 rE r s rk r z-MySQLDDLCompiler.visit_primary_key_constraint sf %t,,II
*73G< ? K4=#6#6u#=#=> >D rm c |j }d| |d d| j |j S )Nz
DROP INDEX F)include_schemar )r r r r r0 )ri r| r s rk visit_drop_indexz!MySQLDDLCompiler.visit_drop_index sK
% %eE % B B B B M & &u{ 3 3 3
rm c < |j }t |t j r d}| j | }nt |t j rd}d}nt |t j r d}| j | }nbt |t j r,| j j
rd}nd}| j | }n d}| j | }d| j |j d||S ) Nz FOREIGN KEY z PRIMARY KEY r r z CONSTRAINT zCHECK ALTER TABLE z DROP )
r r sa_schemaForeignKeyConstraintr format_constraintPrimaryKeyConstraintUniqueConstraintCheckConstraintro _is_mariadbr r0 )ri r| r^ qualconsts rk visit_drop_constraintz&MySQLDDLCompiler.visit_drop_constraint s \
j)"@ A A @!DM33J??EE
I$B
C
C @!DEE
I$>
?
? @DM33J??EE
I$=
>
> @|'
$ M33J??EEDM33J??E M & &z'7 8 8 8 8 D E
rm c < |j t j d dS )NzjMySQL ignores the 'MATCH' keyword while at the same time causes ON UPDATE/ON DELETE clauses to be ignored.r )r r. CompileError)ri r^ s rk define_constraint_matchz(MySQLDDLCompiler.define_constraint_match s- '"D
rrm c d| j |j d| j |j j t
j S )Nr z COMMENT )r r r rV r rU r r ri ra s rk visit_set_table_commentz(MySQLDDLCompiler.visit_set_table_comment sV M & &v~ 6 6 6 6 2 2&(9(9
rm c F d| j |j z S )NzALTER TABLE %s COMMENT '')r r r r s rk visit_drop_table_commentz)MySQLDDLCompiler.visit_drop_table_comment s$ * M & &v~ 6 6
rm c d| j |j j d| j |j d| |j S )Nr z CHANGE r )r r r r0 rP r\ r s rk visit_set_column_commentz)MySQLDDLCompiler.visit_set_column_comment s] M & &v~'; < < < < M ' ' 7 7 7 7 ) )&. 9 9 9
rm )rv rw rx r\ r r r r r r r r r rI rJ s @rk rL rL s ,! ,! ,!\S$ S$ S$jH H HT
4
rm rL c e Zd Zd Zd Zd Zd Zd Zd Zd Z d Z
d Z d
Z d Z
d Zd
Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Zd Z d Z d Z d Z d Z fd Z!d Z"d! Z#d" Z$d# Z%d$ Z&d% Z'd& Z(d' Z) xZ*S )(MySQLTypeCompilerc d | | s|S |j r|dz
}|j r|dz
}|S )zAExtend a numeric-type declaration with MySQL specific extensions.z UNSIGNEDz ZEROFILL) _mysql_typerA rX )ri r specs rk _extend_numericz!MySQLTypeCompiler._extend_numeric sL && K > K D > K D rm c l fd} |d rd |d z }n |d rd}n |d rd}nd} |d r d
j z }n |d rd }nd} |d
r"d d d||fD S d d |||fD S )zExtend a string-type declaration with standard SQL CHARACTER SET /
COLLATE annotations and MySQL specific extensions.
c L t | | S rg )r r )r defaultsr s rk attrz.MySQLTypeCompiler._extend_string..attr s! 5$ T(:(:;; ;rm charsetzCHARACTER SET %sasciiASCIIunicodeUNICODEN collationz
COLLATE %srO r9 nationalr c g | ]}||S rg ry r s rk r z4MySQLTypeCompiler._extend_string.. s KKKqQ]]]]rm NATIONALc g | ]}||S rg ry r s rk r z4MySQLTypeCompiler._extend_string.. s D D D1amQmmmrm )r r )ri r r r r r r s `` rk _extend_stringz MySQLTypeCompiler._extend_string s' < < < < < < 4 ?? (44 ??:GG
T']] GG
T)__ GGG 4 $u6II
T(^^ II I 4
88KKZy 9KKK
xx D Dw 2 D D D
rm c : t |t t f S rg )r r r )ri r s rk r z MySQLTypeCompiler._mysql_type s %+|!<===rm c |j | |d S |j | |dd|j iz S | |d|j |j dz S )Nr! zNUMERIC(%(precision)s)r z!NUMERIC(%(precision)s, %(scale)s)r scaler r r ri r r s rk
visit_NUMERICz MySQLTypeCompiler.visit_NUMERIC ? "''y99 9
[
''(K +II
''3 %%+FFG
rm c |j | |d S |j | |dd|j iz S | |d|j |j dz S )Nr zDECIMAL(%(precision)s)r z!DECIMAL(%(precision)s, %(scale)s)r r r s rk
visit_DECIMALz MySQLTypeCompiler.visit_DECIMAL r rm c |j -|j &| |d|j |j dz S | |d S )Nz DOUBLE(%(precision)s, %(scale)s)r r r r r r s rk visit_DOUBLEz MySQLTypeCompiler.visit_DOUBLE" s\ ? &5;+B''2 %%+FFG
''x88 8rm c |j -|j &| |d|j |j dz S | |d S )Nz REAL(%(precision)s, %(scale)s)r r# r r s rk
visit_REALz MySQLTypeCompiler.visit_REAL, s\ ? &5;+B''0 %%+FFG
''v66 6rm c | | r5|j .|j '| |d|j d|j d S |j | |d|j d S | |d S )NzFLOAT(r r r )r r r r r s rk visit_FLOATz MySQLTypeCompiler.visit_FLOAT6 s U # #
8 '+''%///5;;;G
_
(''eooo7
''w77 7rm c | | r'|j | |dd|j iz S | |d S )NzINTEGER(%(display_width)s)
display_widthr r r r r s rk
visit_INTEGERz MySQLTypeCompiler.visit_INTEGERF sf E " " :u':'F'',"E$789
''y99 9rm c | | r'|j | |dd|j iz S | |d S )NzBIGINT(%(display_width)s)r r r r s rk visit_BIGINTz MySQLTypeCompiler.visit_BIGINTP sf E " " 9u':'F''+"E$789
''x88 8rm c | | r'|j | |dd|j iz S | |d S )Nz MEDIUMINT(%(display_width)s)r r r r s rk visit_MEDIUMINTz!MySQLTypeCompiler.visit_MEDIUMINTZ sf E " " > > >"" 2444,)?)?)?)?@
rm c : | d||j S r r _enumerated_valuesr s rk
visit_ENUMz MySQLTypeCompiler.visit_ENUM s$ ,, E53
rm c : | d||j S )Nr
r/ r s rk visit_SETzMySQLTypeCompiler.visit_SET s$ ,, 5%2
rm c dS )NBOOLry r s rk
visit_BOOLEANz MySQLTypeCompiler.visit_BOOLEAN r rm )+rv rw rx r r r r r r r r r r r r r r r r r r r r r r r r
r
r r r r r r r r! r$ r& r r1 r3 r6 rI rJ s @rk r r s{
D> > >
9 9 97 7 78 8 8 : : :9 9 9< < <: : :; ; ; 4 4 4 : : : : : :< < <: : : : : :
J
J
J. . . & & &M M M M M
rm r c * e Zd ZeZd fd Zd Z xZS )MySQLIdentifierPreparerFc l |sd}nd}t t | ||| d S )N`")
initial_quote escape_quote)r r8 __init__)ri ro server_ansiquotesr r r s rk r> z MySQLIdentifierPreparer.__init__ sP EEE
%t,,55 5u 6
rm c : t fd|D S )z4Unilaterally identifier-quote any number of strings.c > g | ]}| | S rg )quote_identifier)r iri s rk r zCMySQLIdentifierPreparer._quote_free_identifiers.. s( MMM1q}d++A..}}}rm )tuple)ri idss ` rk _quote_free_identifiersz/MySQLIdentifierPreparer._quote_free_identifiers s( MMMMMMMNNNrm rD )rv rw rx RESERVED_WORDSreserved_wordsr> rF rI rJ s @rk r8 r8 sX #N
O O O O O O Orm r8 c " e Zd ZdZdZdZdZdZdZdZ dZ
dZ dZ dZ
dZdZdZdZeZdZeZeZeZeZeZ dZ dZ e j dd ife!j" d
d ife j# d d ife j$ d d d d d fgZ% dBd
Z&d Z' e(g d Z)d Z*d Z+d Z,d Z-d Z.d Z/d Z0d Z1d Z2 dCdZ3 dCdZ4d Z5d Z6dDd Z7dDd Z8dDd Z9d Z:d! Z;dDd"Ze?d% Z@e?d& ZAe?d' ZBe?d( ZCe?d) ZDe?d* ZEeFjG d+ ZHeFjG dDd, ZIeFjG dDd- ZJeFjG dDd. ZKeFjG dDd/ ZLeFjG dDd0 ZMeFjG dDd1 ZNd2 ZOeFjG dDd3 ZPeFjG dDd4 ZQeFjG dDd5 ZReFjG dDd6 ZSeFjG dDd7 ZT dDd8ZUeVjW d9 ZXeFjG dDd: ZYd; ZZd< Z[d= Z\d> Z]d? Z^ dEd@Z_dEdAZ`d S )F MySQLDialectzMDetails of the MySQL dialect.
Not used directly in application code.
r TF @ format*Nr rE )rE r prefixr c | dd t j j | fi | || _ || _ || _ d S )Nuse_ansiquotes)popr4 DefaultDialectr> isolation_level_json_serializer_json_deserializer)ri rT json_serializerjson_deserializerr
s rk r> zMySQLDialect.__init__U sS
#T*** '77777 . / "3 rm c $ j fd}|S d S )Nc > | j d S rg )set_isolation_levelrT )connri s rk connectz(MySQLDialect.on_connect..connecte s" ((t/CDDDDDrm )rT )ri r] s ` rk
on_connectzMySQLDialect.on_connectb s6 +
E
E
E
E
E N4rm ) SERIALIZABLEzREAD UNCOMMITTEDzREAD COMMITTEDzREPEATABLE READc | dd }t |d r|j }| || d S )Nrp r
connection)r hasattrra _set_isolation_level)ri ra r s rk r[ z MySQLDialect.set_isolation_levelu sM
c3''
:| , , /#.J !!*e44444rm c
6 || j vr:t j d|d| j dd | j | }| d|z | d | d S )NzInvalid value 'z2' for isolation_level. Valid isolation levels for z are r z*SET SESSION TRANSACTION ISOLATION LEVEL %sCOMMIT)_isolation_lookupr.
ArgumentErrorr r ri executeclose)ri ra r ri s rk rc z!MySQLDialect._set_isolation_level s . . .## 55$)))TYYt/E%F%F%FH
""$$CeKLLLx rm c | }| j r!| j dk r| d n| d | }|"t j d t |d }| t
j r)t |t r| }|
dd S )N) z SELECT @@transaction_isolationzSELECT @@tx_isolationzDCould not retrieve transaction isolation level for MySQL connection.r -r )ri r server_version_inforh fetchoner3 r rs ri py3kr bytesdecoder r )ri ra ri r r s rk get_isolation_levelz MySQLDialect.get_isolation_level s ""$$ > 4d 6* D D NN; < < < < NN2 3 3 3oo ; I
&'' '!f 9 C// **,,Cyy{{""3,,,rm c R |j }| }| d | d }| t
j r)t |t r| }|
| S )NzSELECT VERSION()r ) ra ri rh rp ri r3 rq r rr rs _parse_server_version)ri ra dbapi_conri r s rk _get_server_version_infoz%MySQLDialect._get_server_version_info s ) !!##)***oo " 9 C// **,,C))#...rm c g }t j d }| | D ]} | t | &# t
$ ra t j d| }|r2| d | D n| | Y w xY wt | S )Nz[.\-]z(.*)(MariaDB)(.*)c 3 K | ]}||V d S rg ry )r gs rk r z5MySQLDialect._parse_server_version.. s' "D"D!"D1"D"D"D"D"D"Drm )
recompilesplitr r
ValueErrorr extendr rD )ri r versionrnmariadbs rk rv z"MySQLDialect._parse_server_version s Jx & &A
&s1vv&&&&
&
&
& (#7;; &NN"D"Dgnn.>.>"D"D"DDDDDNN1%%%
& W~~ s "AA(B=<B=c | dS # t $ r< | j dk r/t j d j }|r|d dk rY dS w xY w)zExecute a COMMIT.r- r r ( N)commit Exceptionro sysexc_infoargsri dbapi_connectionr s rk do_commitzMySQLDialect.do_commit sy # # % % % % % '+55|~~a(- DGtOOFF AA A c | dS # t $ r< | j dk r/t j d j }|r|d dk rY dS w xY w)zExecute a ROLLBACK.r r r r N)rollbackr ro r r r r s rk do_rollbackzMySQLDialect.do_rollback sy % % ' ' ' ' ' '+55|~~a(- DGtOOFF r c X | t j d | d S )Nz
XA BEGIN :xidxidrh r1 r3 ri ra r s rk do_begin_twophasez MySQLDialect.do_begin_twophase s+ 38O44#>>>>>rm c | t j d | | t j d | d S )N XA END :xidr zXA PREPARE :xidr r s rk do_prepare_twophasez MySQLDialect.do_prepare_twophase sO 38M22<<<38$566C@@@@@rm c |s)| t j d | | t j d | d S )Nr r zXA ROLLBACK :xidr ri ra r is_preparedrecovers rk do_rollback_twophasez!MySQLDialect.do_rollback_twophase sZ A sx
6 6C @ @ @38$677SAAAAArm c |s| || | t j d | d S )NzXA COMMIT :xidr )r rh r1 r3 r s rk do_commit_twophasez MySQLDialect.do_commit_twophase sL 6 $ $Z 5 5 538$4553?????rm c D | d }d |D S )Nz
XA RECOVERc : g | ]}|d d|d S )datar gtrid_lengthry r r s rk r z4MySQLDialect.do_recover_twophase.. s+ JJJF AN 3 34JJJrm rh )ri ra resultsets rk do_recover_twophasez MySQLDialect.do_recover_twophase s) &&|44 JJ JJJJrm c t || j j | j j f r| | dv S t || j j | j j f rdt | v S dS )N)i i i i i i z(0, '')F)r dbapiOperationalErrorProgrammingError_extract_error_codeInterfaceError
InternalErrorstr)ri r- ra ri s rk
is_disconnectzMySQLDialect.is_disconnect s
+TZ-HI
++A.. 3
)4:+CD
A& &5rm c D fd| D S )zMProxy result rows to smooth over MySQL-Python driver
inconsistencies.c 0 g | ]}t | S ry )_DecodingRowProxy)r r r s rk r z1MySQLDialect._compat_fetchall..
s$ IIIC!#w//IIIrm )fetchall)ri rpr s `rk _compat_fetchallz MySQLDialect._compat_fetchall
s( JIII2;;==IIIIrm c R | }|rt || S dS zNProxy a result row to smooth over MySQL-Python driver
inconsistencies.N)rp r ri r r r s rk _compat_fetchonez MySQLDialect._compat_fetchone
s. kkmm $S'22 24rm c R | }|rt || S dS r )firstr r s rk
_compat_firstzMySQLDialect._compat_first
s. hhjj $S'22 24rm c t rg rs )ri exceptions rk r z MySQLDialect._extract_error_code!
s !###rm c P | d S )NzSELECT DATABASE())rh scalarri ra s rk _get_default_schema_namez%MySQLDialect._get_default_schema_name$
s# !!"566==???rm c ( d | j || }d|z }d } | d | }| d u}| ||r| S S # t j $ rB}| |j
dk r Y d }~|r| dS dS d }~ww xY w# |r| w w xY w)N. DESCRIBE %sTskip_user_error_eventsz F) r identifier_preparerrF execution_optionsrh rp ri r.
DBAPIErrorr orig) ri ra
table_namer0 full_namestrshaver- s rk has_tablezMySQLDialect.has_table'
sM HH $ < <
Y
&
11+/ 2 '"++ {{}}D0
>
++AF33t;; 555
s0 AB$ $C53 C0C8 /C00C55C8 8Dc | | | _ | | | | | | | j r!| | | j | _ t j
| | | j o
| j dk | _
| j o
| j dk | _ | d S )Nr? ) )_detect_charset_connection_charset_detect_sql_mode_detect_ansiquotes_detect_casing_server_ansiquotesr r r4 rS
initializer ro r r _needs_correct_for_88718_96365_warn_for_known_db_issuesr s rk r zMySQLDialect.initializeJ
s #'#7#7
#C#C j)))
+++ J''' " (,}}(? (5 ( (D $ ))$
;;;
N ?t 74 ?
#
ET%=%E
+
&&(((((rm c v | j r-| j }|dk r"|dk r t j d|d d S d S d S d S )N
)r r zMariaDB aV before 10.2.9 has known issues regarding CHECK constraints, which impact handling of NULL values with SQLAlchemy's boolean datatype (MDEV-13596). An additional issue prevents proper migrations of columns with CHECK constraints (MDEV-11114). Please upgrade to MariaDB 10.2.9 or greater, or use the MariaDB 10.1 series, to avoid these issues.)r _mariadb_normalized_version_infor3 r )ri mdb_versions rk r z&MySQLDialect._warn_for_known_db_issuesb
sp ?KW$$z)A)A 9D
F $$)A)Arm c N | j sdS | j r | j dk S | j dk S )NF)r rk )r r )ro r rt s rk r z MySQLDialect._support_float_castp
s; ' :5
:+z9 9 +z9 9rm c " | j od| j v S )NMariaDBro rt s rk r zMySQLDialect._is_mariadb{
s
$ Nd6N)N
rm c | j S rg )r rt s rk r zMySQLDialect._is_mysql
s ###rm c & | j o
| j dk S )Nr )r r rt s rk _is_mariadb_102z MySQLDialect._is_mariadb_102
s"
D$I M
%
rm c v | j r,| j d }| j |dz
| S | j S )Nr r- )r ro r )ri r: s rk r z-MySQLDialect._mariadb_normalized_version_info
sD
,*00;;C+C!GcM: :+ +rm c * | j d u p
| j dk S )N)r r r r rt s rk r zMySQLDialect._supports_cast
s$
$ ,
5'94
rm c D | d }d |D S )Nz SHOW schemasc g | ]
}|d S r ry )r r s rk r z1MySQLDialect.get_schema_names..
s !!!!!!!rm r )ri ra r r s rk get_schema_namesz MySQLDialect.get_schema_names
s)
/
/!!b!!!!rm c ||}n| j }| j }| j dk rQ| d| j | z }d | || D S | d| j | z }d | || D S )z1Return a Unicode SHOW TABLES from a given schema.Nrk r r zSHOW TABLES FROM %sc g | ]
}|d S r ry r s rk r z0MySQLDialect.get_table_names..
s) A rm r SHOW FULL TABLES FROM %sc 6 g | ]}|d dk |d S )r z
BASE TABLEr ry r s rk r z0MySQLDialect.get_table_names..
s5 q6\)) A)))rm )default_schema_namer ro rh r rB r )ri ra r0 r current_schemar r s rk get_table_namesz MySQLDialect.get_table_names
s #NN ! 5N* #i / /##%*;;NKKL B "&"7"7G"7"L"L
##**;;NKKL B
00W0EE
rm c * | j dk rt || j }| j dk r| || S | j }| d| j | z }d | || D S )Nr r c 2 g | ]}|d dv |d S )r )VIEWz SYSTEM VIEWr ry r s rk r z/MySQLDialect.get_view_names..
s6
1v000
F000rm r ) ro rs r r r rh r rB r )ri ra r0 r r r s rk get_view_nameszMySQLDialect.get_view_names
s #i / /% % >-F #i / /''
F;; ;*
&&77??
@
,,R,AA
rm c 0 | j |||fi |}|j S rg )_parsed_state_or_create
table_optionsri ra r r0 r parsed_states rk get_table_optionsz MySQLDialect.get_table_options
s7 4t3
F
.0
))rm c 0 | j |||fi |}|j S rg )r r\ r
s rk get_columnszMySQLDialect.get_columns
s5 3t3
F
.0
##rm c | j |||fi |}|j D ]'}|d dk rd |d D }|d dc S (g d dS )Nr PRIMARYc g | ]
}|d S r ry r ss rk r z2MySQLDialect.get_pk_constraint..
s 555!555rm r\ )constrained_columnsr r r )ri ra r r0 r r r r s rk get_pk_constraintz MySQLDialect.get_pk_constraint
s 3t3
F
.0
$ C CC6{i''55c)n555/3TBBBBB ( (*4888rm c | j |||fi |}d }g }|j D ]}|d d } t |d dk r|d d p|}
|
s| |j j }||k r|}
|d } |d } i }
dD ]#}| |d r || |
|< $|d | |
| | |
d
}| | | j r| || |S ) Nr0 r r localr )onupdateondeleteFr )r r referred_schemareferred_tablereferred_columnsoptions) r fk_constraintsr` ro r r r r #_correct_for_mysql_bugs_88718_96365)ri ra r r0 r r default_schemafkeysr ref_name
ref_schema loc_names ref_namescon_kwr fkey_ds rk get_foreign_keysz MySQLDialect.get_foreign_keys
sV 4t3
F
.0
/ ! !DG}R(H T']++a/EDM"4EOJ
(!)%/%7%KN^++!'J W
I YIF/
,
,88C'' ,"&s)F3K V '0#-"*$-!
F
LL . H 4 4UJ G G G rm c
| j dv rd nd |j j
fd|D }|r@| t j d t j dd | }t t }|D ]u\ }}}|| | | f d
< || | | f d < || | | f |
< v|D ]c} | | d p
| d
f d | d
< | d d
| d < fd| d D | d< bd S d S )N)r r c * | S rg lowerr s rk r1 z?MySQLDialect._correct_for_mysql_bugs_88718_96365..lower) s wwyy rm c | S rg ry r2 s rk r1 z?MySQLDialect._correct_for_mysql_bugs_88718_96365..lower0 s rm c p g | ]2}|d D ]'} |d p |d |f(3S )r! r r ry )r reccol_namer r1 s rk r zDMySQLDialect._correct_for_mysql_bugs_88718_96365..4 sy
2 3
c+,C0CDDc*+,,
rm z
select table_schema, table_name, column_name
from information_schema.columns
where (table_schema, table_name, lower(column_name)) in
:table_data;
table_dataT) expanding)r7
SCHEMANAME TABLENAMEr r c D g | ] }| S ry r0 )r r r5 s rk r zDMySQLDialect._correct_for_mysql_bugs_88718_96365..h s3 , , ,),C $, , ,rm r! ) _casingro r rh r1 r3
bindparams bindparamr rs r1 )
ri r& ra
col_tuplescorrect_for_wrong_fk_casedr0 tnamecnamefkeyr r1 r5 s
@@@rk r$ z0MySQLDialect._correct_for_mysql_bugs_88718_96365 s0 <6 ! !
!
!
!
!
) 0 D
, (2(:(: *S]<4 H H HII% );
)
) %, D!!A(A
H
H$uAG55==%%,,/0 >@E55==%%,,/0=BG55==%%,,/0 ??
d#4 5 L9LMM d#3 455 *-[)9%&)*6.1,.?D*+, , , ,045G0H, , ,'((U, , <
rm c D | j |||fi |}d |j D S )Nc 0 g | ]}|d |d dS )r sqltext)r rG ry )r r s rk r z6MySQLDialect.get_check_constraints..r s8
&\d9o > >
rm )r ck_constraintsr
s rk get_check_constraintsz"MySQLDialect.get_check_constraintsl sN 3t3
F
.0
$3
rm c \ | j |||fi |}d|j dd iS )Nr3
mysql_comment)r r r r
s rk get_table_commentz MySQLDialect.get_table_commentw sJ 3t3
F
.0
266MMNNrm c | j |||fi |}g }|j D ]}i }d} |d }
|
dk r|
dk rd} n)|
dv r|
|d< n |
n | j d|
|d r |d |d
< i } |r|| d < |d | d < d
|d D | d< | | d< |
r|
| d< | | |S )NFr r UNIQUET)FULLTEXTSPATIALr z-Converting unknown KEY type %s to a plain KEYr mysql_with_parserr r c g | ]
}|d S r ry r s rk r z,MySQLDialect.get_indexes.. s &E&E&Eqt&E&E&Erm r\ column_namesr? )r r loggerinfor ) ri ra r r0 r r indexesr r r? flavorindex_ds rk get_indexeszMySQLDialect.get_indexes~ sO 4t3
F
.0
% $ $D OF&\F""!! 22228// CV H~
F7;H~ 34G
=-<)* "6lGFO&E&ET)_&E&E&EGN # &GH
)"( NN7 # # # #rm c D | j |||fi |}d |j D S )Nc j g | ]0}|d dk |d d |d D |d d1S )r rN r c g | ]
}|d S r ry )r r s rk r zBMySQLDialect.get_unique_constraints... s B B BCQ B B Brm r\ )r rS duplicates_indexry r s rk r z7MySQLDialect.get_unique_constraints.. s_
6{h&& F B B3y> B B B$'K
'&&rm r r
s rk get_unique_constraintsz#MySQLDialect.get_unique_constraints sR 4t3
F
.0
$(
rm c | j }d | j || }| |d || }|S )Nr r )r r r rF _show_create_table)ri ra view_namer0 r r r r1 s rk get_view_definitionz MySQLDialect.get_view_definition sa *HH $ <
//774<<K,Y77 T\\K33/8yy!! = )*
((W(==D
s0 )A D C+"A?C&!D &C++D D5)NNN)TFrg r )arv rw rx __doc__r supports_altersupports_native_booleanmax_identifier_lengthmax_index_name_lengthmax_constraint_name_lengthsupports_native_enumr supports_sane_rowcount supports_sane_multi_rowcountsupports_multivalues_insertsupports_commentsinline_commentsdefault_paramstylecolspecscte_follows_insertr{ statement_compilerrL ddl_compilerr r
ischema_namesr8 r r r r Tabler1 Updater Indexconstruct_argumentsr> r^ r rf r[ rc rt rx rv r r r r r r r r r r r r r r r r propertyr r r r r r r cacher r r r r r r- r$ rI rL rY r^ rc r r3 memoized_propertyri rf r r ru r r ra rk ry rm rk rJ rJ s DN $ !# " "#( "& O!H &#L%M!M&H
3+& gt_% ''4 9 O #
" 4 4 4 4
5 5 5
- - -& / / / $
? ? ?A A A
:?B B B B :?@ @ @ @K K K *J J J J $ $ $@ @ @! ! ! !F) ) )0 : : X:
X
$ $ X$
X
, , X,
X
" " " 8
$ * * * * $ $ $ $ 9 9 9 9 ( ( ( (TQ Q Qf
O O O O ' ' ' 'R -1
" .2
F F F * * * *&$ $ $ 8
*
*
*
E
E
E :> 4! ! ! ! ! !rm rJ c 4 e Zd ZdZddddddZd Zd Zd
Zd S ) r zReturn unicode-decoded values based on type inspection.
Smooth over data type issues (esp. with alpha driver versions) and
normalize strings as Unicode regardless of user-configured driver
encoding settings.
koi8_rkoi8_uz utf-16-beutf8ujis)koi8rkoi8uutf16utf8mb4eucjpmsc T || _ | j || | _ d S rg )rowproxy_encoding_compatr r )ri r r s rk r> z_DecodingRowProxy.__init__ s'
,00'BB rm c | j | }t |t r| }| j r4t |t
j r| | j S |S rg )r r _arraytostringr r3 binary_typers )ri r items rk __getitem__z _DecodingRowProxy.__getitem__ sd }U# dF # # #==??D < JtT-= > > ;;t|,, ,Krm c t | j | }t |t r| }| j r4t |t j r| | j S |S rg ) r r r r r r r3 r rs )ri r r s rk __getattr__z _DecodingRowProxy.__getattr__ si t}d++ dF # # #==??D < JtT-= > > ;;t|,, ,Krm N)rv rw rx r r r> r r ry rm rk r r sl C C C rm r )r r r collectionsr r| r
sqlalchemyr sqlalchemy.sqlr r r rx
enumeratedr r
r_ r r r
r2 r r r r r r r r r r r r r r r r r r r r! r" r# r$ r% r& r' r( r) r* r+ r, r. r/ r0 r r1 r r3 enginer4 r5 r6 r7 r8 r r9 r: r; r< r= r> r?
TYPE_CHECKINGtypingr@ r rG r} Ir rh SET_REMSTimeMSSetMSEnum
MSLongBlob MSMediumBlob
MSTinyBlobMSBlobMSBinary MSVarBinaryMSNChar
MSNVarCharMSCharMSString
MSLongText MSMediumText
MSTinyTextMSTextMSYear MSTimeStampMSBitMSSmallInteger
MSTinyIntegerMSMediumInteger MSBigInteger MSNumeric MSDecimalMSDoubleMSRealMSFloat MSIntegerr r r Enum MatchTyper r DefaultExecutionContextre rF r{ DDLCompilerrL GenericTypeCompilerr IdentifierPreparerr8 class_loggerrS rJ objectr ry rm rk r s) @
@
D " ! ! ! ! ! # # # # # #
% % % % % % # # # # # # ' ' ' ' ' ' # # # # # # ! ! ! ! ! ! # # # # # #
b b bd dL
GD2:
,bdRZ.?
,,
g NE M4 M4
M4 M M $ f$ f$
3$ D $
w $ D
$ D$ $ w$
f$ D$ W$ U$
7 $ w $ D!$" #$ $$ %$& *'$( )$* *+$, U-$. /$0 w1$2
33$4 5$6 D7$8 D9$: ;$< =$> w?$@ A$B C$D wE$ $F DG$ $
N( ( ( ( (G ; ( ( (Z
Z
Z
Z
Z
H( Z
Z
Z
z S
S
S
S
S
x+ S
S
S
lo o o o o4 o o od O O O O Oh 9 O O O( c
c
c
c
c
7) c
c
c
L* * * * * * * * * *rm