Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# dialects/mysql/base.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8r"""
10.. dialect:: mysql
11 :name: MySQL / MariaDB
12 :full_support: 5.6, 5.7, 8.0 / 10.8, 10.9
13 :normal_support: 5.6+ / 10+
14 :best_effort: 5.0.2+ / 5.0.2+
16Supported Versions and Features
17-------------------------------
19SQLAlchemy supports MySQL starting with version 5.0.2 through modern releases,
20as well as all modern versions of MariaDB. See the official MySQL
21documentation for detailed information about features supported in any given
22server release.
24.. versionchanged:: 1.4 minimum MySQL version supported is now 5.0.2.
26MariaDB Support
27~~~~~~~~~~~~~~~
29The MariaDB variant of MySQL retains fundamental compatibility with MySQL's
30protocols however the development of these two products continues to diverge.
31Within the realm of SQLAlchemy, the two databases have a small number of
32syntactical and behavioral differences that SQLAlchemy accommodates automatically.
33To connect to a MariaDB database, no changes to the database URL are required::
36 engine = create_engine("mysql+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4")
38Upon first connect, the SQLAlchemy dialect employs a
39server version detection scheme that determines if the
40backing database reports as MariaDB. Based on this flag, the dialect
41can make different choices in those of areas where its behavior
42must be different.
44.. _mysql_mariadb_only_mode:
46MariaDB-Only Mode
47~~~~~~~~~~~~~~~~~
49The dialect also supports an **optional** "MariaDB-only" mode of connection, which may be
50useful for the case where an application makes use of MariaDB-specific features
51and is not compatible with a MySQL database. To use this mode of operation,
52replace the "mysql" token in the above URL with "mariadb"::
54 engine = create_engine("mariadb+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4")
56The above engine, upon first connect, will raise an error if the server version
57detection detects that the backing database is not MariaDB.
59When using an engine with ``"mariadb"`` as the dialect name, **all mysql-specific options
60that include the name "mysql" in them are now named with "mariadb"**. This means
61options like ``mysql_engine`` should be named ``mariadb_engine``, etc. Both
62"mysql" and "mariadb" options can be used simultaneously for applications that
63use URLs with both "mysql" and "mariadb" dialects::
65 my_table = Table(
66 "mytable",
67 metadata,
68 Column("id", Integer, primary_key=True),
69 Column("textdata", String(50)),
70 mariadb_engine="InnoDB",
71 mysql_engine="InnoDB",
72 )
74 Index(
75 "textdata_ix",
76 my_table.c.textdata,
77 mysql_prefix="FULLTEXT",
78 mariadb_prefix="FULLTEXT",
79 )
81Similar behavior will occur when the above structures are reflected, i.e. the
82"mariadb" prefix will be present in the option names when the database URL
83is based on the "mariadb" name.
85.. versionadded:: 1.4 Added "mariadb" dialect name supporting "MariaDB-only mode"
86 for the MySQL dialect.
88.. _mysql_connection_timeouts:
90Connection Timeouts and Disconnects
91-----------------------------------
93MySQL / MariaDB feature an automatic connection close behavior, for connections that
94have been idle for a fixed period of time, defaulting to eight hours.
95To circumvent having this issue, use
96the :paramref:`_sa.create_engine.pool_recycle` option which ensures that
97a connection will be discarded and replaced with a new one if it has been
98present in the pool for a fixed number of seconds::
100 engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)
102For more comprehensive disconnect detection of pooled connections, including
103accommodation of server restarts and network issues, a pre-ping approach may
104be employed. See :ref:`pool_disconnects` for current approaches.
106.. seealso::
108 :ref:`pool_disconnects` - Background on several techniques for dealing
109 with timed out connections as well as database restarts.
111.. _mysql_storage_engines:
113CREATE TABLE arguments including Storage Engines
114------------------------------------------------
116Both MySQL's and MariaDB's CREATE TABLE syntax includes a wide array of special options,
117including ``ENGINE``, ``CHARSET``, ``MAX_ROWS``, ``ROW_FORMAT``,
118``INSERT_METHOD``, and many more.
119To accommodate the rendering of these arguments, specify the form
120``mysql_argument_name="value"``. For example, to specify a table with
121``ENGINE`` of ``InnoDB``, ``CHARSET`` of ``utf8mb4``, and ``KEY_BLOCK_SIZE``
122of ``1024``::
124 Table('mytable', metadata,
125 Column('data', String(32)),
126 mysql_engine='InnoDB',
127 mysql_charset='utf8mb4',
128 mysql_key_block_size="1024"
129 )
131When supporting :ref:`mysql_mariadb_only_mode` mode, similar keys against
132the "mariadb" prefix must be included as well. The values can of course
133vary independently so that different settings on MySQL vs. MariaDB may
134be maintained::
136 # support both "mysql" and "mariadb-only" engine URLs
138 Table('mytable', metadata,
139 Column('data', String(32)),
141 mysql_engine='InnoDB',
142 mariadb_engine='InnoDB',
144 mysql_charset='utf8mb4',
145 mariadb_charset='utf8',
147 mysql_key_block_size="1024"
148 mariadb_key_block_size="1024"
150 )
152The MySQL / MariaDB dialects will normally transfer any keyword specified as
153``mysql_keyword_name`` to be rendered as ``KEYWORD_NAME`` in the
154``CREATE TABLE`` statement. A handful of these names will render with a space
155instead of an underscore; to support this, the MySQL dialect has awareness of
156these particular names, which include ``DATA DIRECTORY``
157(e.g. ``mysql_data_directory``), ``CHARACTER SET`` (e.g.
158``mysql_character_set``) and ``INDEX DIRECTORY`` (e.g.
159``mysql_index_directory``).
161The most common argument is ``mysql_engine``, which refers to the storage
162engine for the table. Historically, MySQL server installations would default
163to ``MyISAM`` for this value, although newer versions may be defaulting
164to ``InnoDB``. The ``InnoDB`` engine is typically preferred for its support
165of transactions and foreign keys.
167A :class:`_schema.Table`
168that is created in a MySQL / MariaDB database with a storage engine
169of ``MyISAM`` will be essentially non-transactional, meaning any
170INSERT/UPDATE/DELETE statement referring to this table will be invoked as
171autocommit. It also will have no support for foreign key constraints; while
172the ``CREATE TABLE`` statement accepts foreign key options, when using the
173``MyISAM`` storage engine these arguments are discarded. Reflecting such a
174table will also produce no foreign key constraint information.
176For fully atomic transactions as well as support for foreign key
177constraints, all participating ``CREATE TABLE`` statements must specify a
178transactional engine, which in the vast majority of cases is ``InnoDB``.
181Case Sensitivity and Table Reflection
182-------------------------------------
184Both MySQL and MariaDB have inconsistent support for case-sensitive identifier
185names, basing support on specific details of the underlying
186operating system. However, it has been observed that no matter
187what case sensitivity behavior is present, the names of tables in
188foreign key declarations are *always* received from the database
189as all-lower case, making it impossible to accurately reflect a
190schema where inter-related tables use mixed-case identifier names.
192Therefore it is strongly advised that table names be declared as
193all lower case both within SQLAlchemy as well as on the MySQL / MariaDB
194database itself, especially if database reflection features are
195to be used.
197.. _mysql_isolation_level:
199Transaction Isolation Level
200---------------------------
202All MySQL / MariaDB dialects support setting of transaction isolation level both via a
203dialect-specific parameter :paramref:`_sa.create_engine.isolation_level`
204accepted
205by :func:`_sa.create_engine`, as well as the
206:paramref:`.Connection.execution_options.isolation_level` argument as passed to
207:meth:`_engine.Connection.execution_options`.
208This feature works by issuing the
209command ``SET SESSION TRANSACTION ISOLATION LEVEL <level>`` for each new
210connection. For the special AUTOCOMMIT isolation level, DBAPI-specific
211techniques are used.
213To set isolation level using :func:`_sa.create_engine`::
215 engine = create_engine(
216 "mysql://scott:tiger@localhost/test",
217 isolation_level="READ UNCOMMITTED"
218 )
220To set using per-connection execution options::
222 connection = engine.connect()
223 connection = connection.execution_options(
224 isolation_level="READ COMMITTED"
225 )
227Valid values for ``isolation_level`` include:
229* ``READ COMMITTED``
230* ``READ UNCOMMITTED``
231* ``REPEATABLE READ``
232* ``SERIALIZABLE``
233* ``AUTOCOMMIT``
235The special ``AUTOCOMMIT`` value makes use of the various "autocommit"
236attributes provided by specific DBAPIs, and is currently supported by
237MySQLdb, MySQL-Client, MySQL-Connector Python, and PyMySQL. Using it,
238the database connection will return true for the value of
239``SELECT @@autocommit;``.
241There are also more options for isolation level configurations, such as
242"sub-engine" objects linked to a main :class:`_engine.Engine` which each apply
243different isolation level settings. See the discussion at
244:ref:`dbapi_autocommit` for background.
246.. seealso::
248 :ref:`dbapi_autocommit`
250AUTO_INCREMENT Behavior
251-----------------------
253When creating tables, SQLAlchemy will automatically set ``AUTO_INCREMENT`` on
254the first :class:`.Integer` primary key column which is not marked as a
255foreign key::
257 >>> t = Table('mytable', metadata,
258 ... Column('mytable_id', Integer, primary_key=True)
259 ... )
260 >>> t.create()
261 CREATE TABLE mytable (
262 id INTEGER NOT NULL AUTO_INCREMENT,
263 PRIMARY KEY (id)
264 )
266You can disable this behavior by passing ``False`` to the
267:paramref:`_schema.Column.autoincrement` argument of :class:`_schema.Column`.
268This flag
269can also be used to enable auto-increment on a secondary column in a
270multi-column key for some storage engines::
272 Table('mytable', metadata,
273 Column('gid', Integer, primary_key=True, autoincrement=False),
274 Column('id', Integer, primary_key=True)
275 )
277.. _mysql_ss_cursors:
279Server Side Cursors
280-------------------
282Server-side cursor support is available for the mysqlclient, PyMySQL,
283mariadbconnector dialects and may also be available in others. This makes use
284of either the "buffered=True/False" flag if available or by using a class such
285as ``MySQLdb.cursors.SSCursor`` or ``pymysql.cursors.SSCursor`` internally.
288Server side cursors are enabled on a per-statement basis by using the
289:paramref:`.Connection.execution_options.stream_results` connection execution
290option::
292 with engine.connect() as conn:
293 result = conn.execution_options(stream_results=True).execute(text("select * from table"))
295Note that some kinds of SQL statements may not be supported with
296server side cursors; generally, only SQL statements that return rows should be
297used with this option.
299.. deprecated:: 1.4 The dialect-level server_side_cursors flag is deprecated
300 and will be removed in a future release. Please use the
301 :paramref:`_engine.Connection.stream_results` execution option for
302 unbuffered cursor support.
304.. seealso::
306 :ref:`engine_stream_results`
308.. _mysql_unicode:
310Unicode
311-------
313Charset Selection
314~~~~~~~~~~~~~~~~~
316Most MySQL / MariaDB DBAPIs offer the option to set the client character set for
317a connection. This is typically delivered using the ``charset`` parameter
318in the URL, such as::
320 e = create_engine(
321 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")
323This charset is the **client character set** for the connection. Some
324MySQL DBAPIs will default this to a value such as ``latin1``, and some
325will make use of the ``default-character-set`` setting in the ``my.cnf``
326file as well. Documentation for the DBAPI in use should be consulted
327for specific behavior.
329The encoding used for Unicode has traditionally been ``'utf8'``. However, for
330MySQL versions 5.5.3 and MariaDB 5.5 on forward, a new MySQL-specific encoding
331``'utf8mb4'`` has been introduced, and as of MySQL 8.0 a warning is emitted by
332the server if plain ``utf8`` is specified within any server-side directives,
333replaced with ``utf8mb3``. The rationale for this new encoding is due to the
334fact that MySQL's legacy utf-8 encoding only supports codepoints up to three
335bytes instead of four. Therefore, when communicating with a MySQL or MariaDB
336database that includes codepoints more than three bytes in size, this new
337charset is preferred, if supported by both the database as well as the client
338DBAPI, as in::
340 e = create_engine(
341 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")
343All modern DBAPIs should support the ``utf8mb4`` charset.
345In order to use ``utf8mb4`` encoding for a schema that was created with legacy
346``utf8``, changes to the MySQL/MariaDB schema and/or server configuration may be
347required.
349.. seealso::
351 `The utf8mb4 Character Set \
352 <https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html>`_ - \
353 in the MySQL documentation
355.. _mysql_binary_introducer:
357Dealing with Binary Data Warnings and Unicode
358~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
360MySQL versions 5.6, 5.7 and later (not MariaDB at the time of this writing) now
361emit a warning when attempting to pass binary data to the database, while a
362character set encoding is also in place, when the binary data itself is not
363valid for that encoding::
365 default.py:509: Warning: (1300, "Invalid utf8mb4 character string:
366 'F9876A'")
367 cursor.execute(statement, parameters)
369This warning is due to the fact that the MySQL client library is attempting to
370interpret the binary string as a unicode object even if a datatype such
371as :class:`.LargeBinary` is in use. To resolve this, the SQL statement requires
372a binary "character set introducer" be present before any non-NULL value
373that renders like this::
375 INSERT INTO table (data) VALUES (_binary %s)
377These character set introducers are provided by the DBAPI driver, assuming the
378use of mysqlclient or PyMySQL (both of which are recommended). Add the query
379string parameter ``binary_prefix=true`` to the URL to repair this warning::
381 # mysqlclient
382 engine = create_engine(
383 "mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")
385 # PyMySQL
386 engine = create_engine(
387 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")
390The ``binary_prefix`` flag may or may not be supported by other MySQL drivers.
392SQLAlchemy itself cannot render this ``_binary`` prefix reliably, as it does
393not work with the NULL value, which is valid to be sent as a bound parameter.
394As the MySQL driver renders parameters directly into the SQL string, it's the
395most efficient place for this additional keyword to be passed.
397.. seealso::
399 `Character set introducers <https://dev.mysql.com/doc/refman/5.7/en/charset-introducer.html>`_ - on the MySQL website
402ANSI Quoting Style
403------------------
405MySQL / MariaDB feature two varieties of identifier "quoting style", one using
406backticks and the other using quotes, e.g. ```some_identifier``` vs.
407``"some_identifier"``. All MySQL dialects detect which version
408is in use by checking the value of :ref:`sql_mode<mysql_sql_mode>` when a connection is first
409established with a particular :class:`_engine.Engine`.
410This quoting style comes
411into play when rendering table and column names as well as when reflecting
412existing database structures. The detection is entirely automatic and
413no special configuration is needed to use either quoting style.
416.. _mysql_sql_mode:
418Changing the sql_mode
419---------------------
421MySQL supports operating in multiple
422`Server SQL Modes <https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html>`_ for
423both Servers and Clients. To change the ``sql_mode`` for a given application, a
424developer can leverage SQLAlchemy's Events system.
426In the following example, the event system is used to set the ``sql_mode`` on
427the ``first_connect`` and ``connect`` events::
429 from sqlalchemy import create_engine, event
431 eng = create_engine("mysql://scott:tiger@localhost/test", echo='debug')
433 # `insert=True` will ensure this is the very first listener to run
434 @event.listens_for(eng, "connect", insert=True)
435 def connect(dbapi_connection, connection_record):
436 cursor = dbapi_connection.cursor()
437 cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'")
439 conn = eng.connect()
441In the example illustrated above, the "connect" event will invoke the "SET"
442statement on the connection at the moment a particular DBAPI connection is
443first created for a given Pool, before the connection is made available to the
444connection pool. Additionally, because the function was registered with
445``insert=True``, it will be prepended to the internal list of registered
446functions.
449MySQL / MariaDB SQL Extensions
450------------------------------
452Many of the MySQL / MariaDB SQL extensions are handled through SQLAlchemy's generic
453function and operator support::
455 table.select(table.c.password==func.md5('plaintext'))
456 table.select(table.c.username.op('regexp')('^[a-d]'))
458And of course any valid SQL statement can be executed as a string as well.
460Some limited direct support for MySQL / MariaDB extensions to SQL is currently
461available.
463* INSERT..ON DUPLICATE KEY UPDATE: See
464 :ref:`mysql_insert_on_duplicate_key_update`
466* SELECT pragma, use :meth:`_expression.Select.prefix_with` and
467 :meth:`_query.Query.prefix_with`::
469 select(...).prefix_with(['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])
471* UPDATE with LIMIT::
473 update(..., mysql_limit=10, mariadb_limit=10)
475* optimizer hints, use :meth:`_expression.Select.prefix_with` and
476 :meth:`_query.Query.prefix_with`::
478 select(...).prefix_with("/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */")
480* index hints, use :meth:`_expression.Select.with_hint` and
481 :meth:`_query.Query.with_hint`::
483 select(...).with_hint(some_table, "USE INDEX xyz")
485* MATCH operator support::
487 from sqlalchemy.dialects.mysql import match
488 select(...).where(match(col1, col2, against="some expr").in_boolean_mode())
490 .. seealso::
492 :class:`_mysql.match`
494.. _mysql_insert_on_duplicate_key_update:
496INSERT...ON DUPLICATE KEY UPDATE (Upsert)
497------------------------------------------
499MySQL / MariaDB allow "upserts" (update or insert)
500of rows into a table via the ``ON DUPLICATE KEY UPDATE`` clause of the
501``INSERT`` statement. A candidate row will only be inserted if that row does
502not match an existing primary or unique key in the table; otherwise, an UPDATE
503will be performed. The statement allows for separate specification of the
504values to INSERT versus the values for UPDATE.
506SQLAlchemy provides ``ON DUPLICATE KEY UPDATE`` support via the MySQL-specific
507:func:`.mysql.insert()` function, which provides
508the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`:
510.. sourcecode:: pycon+sql
512 >>> from sqlalchemy.dialects.mysql import insert
514 >>> insert_stmt = insert(my_table).values(
515 ... id='some_existing_id',
516 ... data='inserted value')
518 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
519 ... data=insert_stmt.inserted.data,
520 ... status='U'
521 ... )
522 >>> print(on_duplicate_key_stmt)
523 {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s)
524 ON DUPLICATE KEY UPDATE data = VALUES(data), status = %s
527Unlike PostgreSQL's "ON CONFLICT" phrase, the "ON DUPLICATE KEY UPDATE"
528phrase will always match on any primary key or unique key, and will always
529perform an UPDATE if there's a match; there are no options for it to raise
530an error or to skip performing an UPDATE.
532``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already
533existing row, using any combination of new values as well as values
534from the proposed insertion. These values are normally specified using
535keyword arguments passed to the
536:meth:`_mysql.Insert.on_duplicate_key_update`
537given column key values (usually the name of the column, unless it
538specifies :paramref:`_schema.Column.key`
539) as keys and literal or SQL expressions
540as values:
542.. sourcecode:: pycon+sql
544 >>> insert_stmt = insert(my_table).values(
545 ... id='some_existing_id',
546 ... data='inserted value')
548 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
549 ... data="some data",
550 ... updated_at=func.current_timestamp(),
551 ... )
553 >>> print(on_duplicate_key_stmt)
554 {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s)
555 ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP
557In a manner similar to that of :meth:`.UpdateBase.values`, other parameter
558forms are accepted, including a single dictionary:
560.. sourcecode:: pycon+sql
562 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
563 ... {"data": "some data", "updated_at": func.current_timestamp()},
564 ... )
566as well as a list of 2-tuples, which will automatically provide
567a parameter-ordered UPDATE statement in a manner similar to that described
568at :ref:`tutorial_parameter_ordered_updates`. Unlike the :class:`_expression.Update`
569object,
570no special flag is needed to specify the intent since the argument form is
571this context is unambiguous:
573.. sourcecode:: pycon+sql
575 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
576 ... [
577 ... ("data", "some data"),
578 ... ("updated_at", func.current_timestamp()),
579 ... ]
580 ... )
582 >>> print(on_duplicate_key_stmt)
583 {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s)
584 ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP
586.. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within
587 MySQL ON DUPLICATE KEY UPDATE
589.. warning::
591 The :meth:`_mysql.Insert.on_duplicate_key_update`
592 method does **not** take into
593 account Python-side default UPDATE values or generation functions, e.g.
594 e.g. those specified using :paramref:`_schema.Column.onupdate`.
595 These values will not be exercised for an ON DUPLICATE KEY style of UPDATE,
596 unless they are manually specified explicitly in the parameters.
600In order to refer to the proposed insertion row, the special alias
601:attr:`_mysql.Insert.inserted` is available as an attribute on
602the :class:`_mysql.Insert` object; this object is a
603:class:`_expression.ColumnCollection` which contains all columns of the target
604table:
606.. sourcecode:: pycon+sql
608 >>> stmt = insert(my_table).values(
609 ... id='some_id',
610 ... data='inserted value',
611 ... author='jlh')
613 >>> do_update_stmt = stmt.on_duplicate_key_update(
614 ... data="updated value",
615 ... author=stmt.inserted.author
616 ... )
618 >>> print(do_update_stmt)
619 {opensql}INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s)
620 ON DUPLICATE KEY UPDATE data = %s, author = VALUES(author)
622When rendered, the "inserted" namespace will produce the expression
623``VALUES(<columnname>)``.
625.. versionadded:: 1.2 Added support for MySQL ON DUPLICATE KEY UPDATE clause
629rowcount Support
630----------------
632SQLAlchemy standardizes the DBAPI ``cursor.rowcount`` attribute to be the
633usual definition of "number of rows matched by an UPDATE or DELETE" statement.
634This is in contradiction to the default setting on most MySQL DBAPI drivers,
635which is "number of rows actually modified/deleted". For this reason, the
636SQLAlchemy MySQL dialects always add the ``constants.CLIENT.FOUND_ROWS``
637flag, or whatever is equivalent for the target dialect, upon connection.
638This setting is currently hardcoded.
640.. seealso::
642 :attr:`_engine.CursorResult.rowcount`
645.. _mysql_indexes:
647MySQL / MariaDB- Specific Index Options
648-----------------------------------------
650MySQL and MariaDB-specific extensions to the :class:`.Index` construct are available.
652Index Length
653~~~~~~~~~~~~~
655MySQL and MariaDB both provide an option to create index entries with a certain length, where
656"length" refers to the number of characters or bytes in each value which will
657become part of the index. SQLAlchemy provides this feature via the
658``mysql_length`` and/or ``mariadb_length`` parameters::
660 Index('my_index', my_table.c.data, mysql_length=10, mariadb_length=10)
662 Index('a_b_idx', my_table.c.a, my_table.c.b, mysql_length={'a': 4,
663 'b': 9})
665 Index('a_b_idx', my_table.c.a, my_table.c.b, mariadb_length={'a': 4,
666 'b': 9})
668Prefix lengths are given in characters for nonbinary string types and in bytes
669for binary string types. The value passed to the keyword argument *must* be
670either an integer (and, thus, specify the same prefix length value for all
671columns of the index) or a dict in which keys are column names and values are
672prefix length values for corresponding columns. MySQL and MariaDB only allow a
673length for a column of an index if it is for a CHAR, VARCHAR, TEXT, BINARY,
674VARBINARY and BLOB.
676Index Prefixes
677~~~~~~~~~~~~~~
679MySQL storage engines permit you to specify an index prefix when creating
680an index. SQLAlchemy provides this feature via the
681``mysql_prefix`` parameter on :class:`.Index`::
683 Index('my_index', my_table.c.data, mysql_prefix='FULLTEXT')
685The value passed to the keyword argument will be simply passed through to the
686underlying CREATE INDEX, so it *must* be a valid index prefix for your MySQL
687storage engine.
689.. versionadded:: 1.1.5
691.. seealso::
693 `CREATE INDEX <https://dev.mysql.com/doc/refman/5.0/en/create-index.html>`_ - MySQL documentation
695Index Types
696~~~~~~~~~~~~~
698Some MySQL storage engines permit you to specify an index type when creating
699an index or primary key constraint. SQLAlchemy provides this feature via the
700``mysql_using`` parameter on :class:`.Index`::
702 Index('my_index', my_table.c.data, mysql_using='hash', mariadb_using='hash')
704As well as the ``mysql_using`` parameter on :class:`.PrimaryKeyConstraint`::
706 PrimaryKeyConstraint("data", mysql_using='hash', mariadb_using='hash')
708The value passed to the keyword argument will be simply passed through to the
709underlying CREATE INDEX or PRIMARY KEY clause, so it *must* be a valid index
710type for your MySQL storage engine.
712More information can be found at:
714https://dev.mysql.com/doc/refman/5.0/en/create-index.html
716https://dev.mysql.com/doc/refman/5.0/en/create-table.html
718Index Parsers
719~~~~~~~~~~~~~
721CREATE FULLTEXT INDEX in MySQL also supports a "WITH PARSER" option. This
722is available using the keyword argument ``mysql_with_parser``::
724 Index(
725 'my_index', my_table.c.data,
726 mysql_prefix='FULLTEXT', mysql_with_parser="ngram",
727 mariadb_prefix='FULLTEXT', mariadb_with_parser="ngram",
728 )
730.. versionadded:: 1.3
733.. _mysql_foreign_keys:
735MySQL / MariaDB Foreign Keys
736-----------------------------
738MySQL and MariaDB's behavior regarding foreign keys has some important caveats.
740Foreign Key Arguments to Avoid
741~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
743Neither MySQL nor MariaDB support the foreign key arguments "DEFERRABLE", "INITIALLY",
744or "MATCH". Using the ``deferrable`` or ``initially`` keyword argument with
745:class:`_schema.ForeignKeyConstraint` or :class:`_schema.ForeignKey`
746will have the effect of
747these keywords being rendered in a DDL expression, which will then raise an
748error on MySQL or MariaDB. In order to use these keywords on a foreign key while having
749them ignored on a MySQL / MariaDB backend, use a custom compile rule::
751 from sqlalchemy.ext.compiler import compiles
752 from sqlalchemy.schema import ForeignKeyConstraint
754 @compiles(ForeignKeyConstraint, "mysql", "mariadb")
755 def process(element, compiler, **kw):
756 element.deferrable = element.initially = None
757 return compiler.visit_foreign_key_constraint(element, **kw)
759The "MATCH" keyword is in fact more insidious, and is explicitly disallowed
760by SQLAlchemy in conjunction with the MySQL or MariaDB backends. This argument is
761silently ignored by MySQL / MariaDB, but in addition has the effect of ON UPDATE and ON
762DELETE options also being ignored by the backend. Therefore MATCH should
763never be used with the MySQL / MariaDB backends; as is the case with DEFERRABLE and
764INITIALLY, custom compilation rules can be used to correct a
765ForeignKeyConstraint at DDL definition time.
767Reflection of Foreign Key Constraints
768~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
770Not all MySQL / MariaDB storage engines support foreign keys. When using the
771very common ``MyISAM`` MySQL storage engine, the information loaded by table
772reflection will not include foreign keys. For these tables, you may supply a
773:class:`~sqlalchemy.ForeignKeyConstraint` at reflection time::
775 Table('mytable', metadata,
776 ForeignKeyConstraint(['other_id'], ['othertable.other_id']),
777 autoload_with=engine
778 )
780.. seealso::
782 :ref:`mysql_storage_engines`
784.. _mysql_unique_constraints:
786MySQL / MariaDB Unique Constraints and Reflection
787----------------------------------------------------
789SQLAlchemy supports both the :class:`.Index` construct with the
790flag ``unique=True``, indicating a UNIQUE index, as well as the
791:class:`.UniqueConstraint` construct, representing a UNIQUE constraint.
792Both objects/syntaxes are supported by MySQL / MariaDB when emitting DDL to create
793these constraints. However, MySQL / MariaDB does not have a unique constraint
794construct that is separate from a unique index; that is, the "UNIQUE"
795constraint on MySQL / MariaDB is equivalent to creating a "UNIQUE INDEX".
797When reflecting these constructs, the
798:meth:`_reflection.Inspector.get_indexes`
799and the :meth:`_reflection.Inspector.get_unique_constraints`
800methods will **both**
801return an entry for a UNIQUE index in MySQL / MariaDB. However, when performing
802full table reflection using ``Table(..., autoload_with=engine)``,
803the :class:`.UniqueConstraint` construct is
804**not** part of the fully reflected :class:`_schema.Table` construct under any
805circumstances; this construct is always represented by a :class:`.Index`
806with the ``unique=True`` setting present in the :attr:`_schema.Table.indexes`
807collection.
810TIMESTAMP / DATETIME issues
811---------------------------
813.. _mysql_timestamp_onupdate:
815Rendering ON UPDATE CURRENT TIMESTAMP for MySQL / MariaDB's explicit_defaults_for_timestamp
816~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
818MySQL / MariaDB have historically expanded the DDL for the :class:`_types.TIMESTAMP`
819datatype into the phrase "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
820CURRENT_TIMESTAMP", which includes non-standard SQL that automatically updates
821the column with the current timestamp when an UPDATE occurs, eliminating the
822usual need to use a trigger in such a case where server-side update changes are
823desired.
825MySQL 5.6 introduced a new flag `explicit_defaults_for_timestamp
826<https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
827#sysvar_explicit_defaults_for_timestamp>`_ which disables the above behavior,
828and in MySQL 8 this flag defaults to true, meaning in order to get a MySQL
829"on update timestamp" without changing this flag, the above DDL must be
830rendered explicitly. Additionally, the same DDL is valid for use of the
831``DATETIME`` datatype as well.
833SQLAlchemy's MySQL dialect does not yet have an option to generate
834MySQL's "ON UPDATE CURRENT_TIMESTAMP" clause, noting that this is not a general
835purpose "ON UPDATE" as there is no such syntax in standard SQL. SQLAlchemy's
836:paramref:`_schema.Column.server_onupdate` parameter is currently not related
837to this special MySQL behavior.
839To generate this DDL, make use of the :paramref:`_schema.Column.server_default`
840parameter and pass a textual clause that also includes the ON UPDATE clause::
842 from sqlalchemy import Table, MetaData, Column, Integer, String, TIMESTAMP
843 from sqlalchemy import text
845 metadata = MetaData()
847 mytable = Table(
848 "mytable",
849 metadata,
850 Column('id', Integer, primary_key=True),
851 Column('data', String(50)),
852 Column(
853 'last_updated',
854 TIMESTAMP,
855 server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
856 )
857 )
859The same instructions apply to use of the :class:`_types.DateTime` and
860:class:`_types.DATETIME` datatypes::
862 from sqlalchemy import DateTime
864 mytable = Table(
865 "mytable",
866 metadata,
867 Column('id', Integer, primary_key=True),
868 Column('data', String(50)),
869 Column(
870 'last_updated',
871 DateTime,
872 server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
873 )
874 )
877Even though the :paramref:`_schema.Column.server_onupdate` feature does not
878generate this DDL, it still may be desirable to signal to the ORM that this
879updated value should be fetched. This syntax looks like the following::
881 from sqlalchemy.schema import FetchedValue
883 class MyClass(Base):
884 __tablename__ = 'mytable'
886 id = Column(Integer, primary_key=True)
887 data = Column(String(50))
888 last_updated = Column(
889 TIMESTAMP,
890 server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
891 server_onupdate=FetchedValue()
892 )
895.. _mysql_timestamp_null:
897TIMESTAMP Columns and NULL
898~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
900MySQL historically enforces that a column which specifies the
901TIMESTAMP datatype implicitly includes a default value of
902CURRENT_TIMESTAMP, even though this is not stated, and additionally
903sets the column as NOT NULL, the opposite behavior vs. that of all
904other datatypes::
906 mysql> CREATE TABLE ts_test (
907 -> a INTEGER,
908 -> b INTEGER NOT NULL,
909 -> c TIMESTAMP,
910 -> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
911 -> e TIMESTAMP NULL);
912 Query OK, 0 rows affected (0.03 sec)
914 mysql> SHOW CREATE TABLE ts_test;
915 +---------+-----------------------------------------------------
916 | Table | Create Table
917 +---------+-----------------------------------------------------
918 | ts_test | CREATE TABLE `ts_test` (
919 `a` int(11) DEFAULT NULL,
920 `b` int(11) NOT NULL,
921 `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
922 `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
923 `e` timestamp NULL DEFAULT NULL
924 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
926Above, we see that an INTEGER column defaults to NULL, unless it is specified
927with NOT NULL. But when the column is of type TIMESTAMP, an implicit
928default of CURRENT_TIMESTAMP is generated which also coerces the column
929to be a NOT NULL, even though we did not specify it as such.
931This behavior of MySQL can be changed on the MySQL side using the
932`explicit_defaults_for_timestamp
933<https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
934#sysvar_explicit_defaults_for_timestamp>`_ configuration flag introduced in
935MySQL 5.6. With this server setting enabled, TIMESTAMP columns behave like
936any other datatype on the MySQL side with regards to defaults and nullability.
938However, to accommodate the vast majority of MySQL databases that do not
939specify this new flag, SQLAlchemy emits the "NULL" specifier explicitly with
940any TIMESTAMP column that does not specify ``nullable=False``. In order to
941accommodate newer databases that specify ``explicit_defaults_for_timestamp``,
942SQLAlchemy also emits NOT NULL for TIMESTAMP columns that do specify
943``nullable=False``. The following example illustrates::
945 from sqlalchemy import MetaData, Integer, Table, Column, text
946 from sqlalchemy.dialects.mysql import TIMESTAMP
948 m = MetaData()
949 t = Table('ts_test', m,
950 Column('a', Integer),
951 Column('b', Integer, nullable=False),
952 Column('c', TIMESTAMP),
953 Column('d', TIMESTAMP, nullable=False)
954 )
957 from sqlalchemy import create_engine
958 e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
959 m.create_all(e)
961output::
963 CREATE TABLE ts_test (
964 a INTEGER,
965 b INTEGER NOT NULL,
966 c TIMESTAMP NULL,
967 d TIMESTAMP NOT NULL
968 )
970.. versionchanged:: 1.0.0 - SQLAlchemy now renders NULL or NOT NULL in all
971 cases for TIMESTAMP columns, to accommodate
972 ``explicit_defaults_for_timestamp``. Prior to this version, it will
973 not render "NOT NULL" for a TIMESTAMP column that is ``nullable=False``.
975""" # noqa
977from array import array as _array
978from collections import defaultdict
979from itertools import compress
980import re
982from sqlalchemy import literal_column
983from sqlalchemy import text
984from sqlalchemy.sql import visitors
985from . import reflection as _reflection
986from .enumerated import ENUM
987from .enumerated import SET
988from .json import JSON
989from .json import JSONIndexType
990from .json import JSONPathType
991from .reserved_words import RESERVED_WORDS_MARIADB
992from .reserved_words import RESERVED_WORDS_MYSQL
993from .types import _FloatType
994from .types import _IntegerType
995from .types import _MatchType
996from .types import _NumericType
997from .types import _StringType
998from .types import BIGINT
999from .types import BIT
1000from .types import CHAR
1001from .types import DATETIME
1002from .types import DECIMAL
1003from .types import DOUBLE
1004from .types import FLOAT
1005from .types import INTEGER
1006from .types import LONGBLOB
1007from .types import LONGTEXT
1008from .types import MEDIUMBLOB
1009from .types import MEDIUMINT
1010from .types import MEDIUMTEXT
1011from .types import NCHAR
1012from .types import NUMERIC
1013from .types import NVARCHAR
1014from .types import REAL
1015from .types import SMALLINT
1016from .types import TEXT
1017from .types import TIME
1018from .types import TIMESTAMP
1019from .types import TINYBLOB
1020from .types import TINYINT
1021from .types import TINYTEXT
1022from .types import VARCHAR
1023from .types import YEAR
1024from ... import exc
1025from ... import log
1026from ... import schema as sa_schema
1027from ... import sql
1028from ... import types as sqltypes
1029from ... import util
1030from ...engine import default
1031from ...engine import reflection
1032from ...sql import coercions
1033from ...sql import compiler
1034from ...sql import elements
1035from ...sql import functions
1036from ...sql import operators
1037from ...sql import roles
1038from ...sql import util as sql_util
1039from ...sql.sqltypes import Unicode
1040from ...types import BINARY
1041from ...types import BLOB
1042from ...types import BOOLEAN
1043from ...types import DATE
1044from ...types import VARBINARY
1045from ...util import topological
1047AUTOCOMMIT_RE = re.compile(
1048 r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|LOAD +DATA|REPLACE)",
1049 re.I | re.UNICODE,
1050)
1051SET_RE = re.compile(
1052 r"\s*SET\s+(?:(?:GLOBAL|SESSION)\s+)?\w", re.I | re.UNICODE
1053)
1056# old names
1057MSTime = TIME
1058MSSet = SET
1059MSEnum = ENUM
1060MSLongBlob = LONGBLOB
1061MSMediumBlob = MEDIUMBLOB
1062MSTinyBlob = TINYBLOB
1063MSBlob = BLOB
1064MSBinary = BINARY
1065MSVarBinary = VARBINARY
1066MSNChar = NCHAR
1067MSNVarChar = NVARCHAR
1068MSChar = CHAR
1069MSString = VARCHAR
1070MSLongText = LONGTEXT
1071MSMediumText = MEDIUMTEXT
1072MSTinyText = TINYTEXT
1073MSText = TEXT
1074MSYear = YEAR
1075MSTimeStamp = TIMESTAMP
1076MSBit = BIT
1077MSSmallInteger = SMALLINT
1078MSTinyInteger = TINYINT
1079MSMediumInteger = MEDIUMINT
1080MSBigInteger = BIGINT
1081MSNumeric = NUMERIC
1082MSDecimal = DECIMAL
1083MSDouble = DOUBLE
1084MSReal = REAL
1085MSFloat = FLOAT
1086MSInteger = INTEGER
1088colspecs = {
1089 _IntegerType: _IntegerType,
1090 _NumericType: _NumericType,
1091 _FloatType: _FloatType,
1092 sqltypes.Numeric: NUMERIC,
1093 sqltypes.Float: FLOAT,
1094 sqltypes.Time: TIME,
1095 sqltypes.Enum: ENUM,
1096 sqltypes.MatchType: _MatchType,
1097 sqltypes.JSON: JSON,
1098 sqltypes.JSON.JSONIndexType: JSONIndexType,
1099 sqltypes.JSON.JSONPathType: JSONPathType,
1100}
1102# Everything 3.23 through 5.1 excepting OpenGIS types.
1103ischema_names = {
1104 "bigint": BIGINT,
1105 "binary": BINARY,
1106 "bit": BIT,
1107 "blob": BLOB,
1108 "boolean": BOOLEAN,
1109 "char": CHAR,
1110 "date": DATE,
1111 "datetime": DATETIME,
1112 "decimal": DECIMAL,
1113 "double": DOUBLE,
1114 "enum": ENUM,
1115 "fixed": DECIMAL,
1116 "float": FLOAT,
1117 "int": INTEGER,
1118 "integer": INTEGER,
1119 "json": JSON,
1120 "longblob": LONGBLOB,
1121 "longtext": LONGTEXT,
1122 "mediumblob": MEDIUMBLOB,
1123 "mediumint": MEDIUMINT,
1124 "mediumtext": MEDIUMTEXT,
1125 "nchar": NCHAR,
1126 "nvarchar": NVARCHAR,
1127 "numeric": NUMERIC,
1128 "set": SET,
1129 "smallint": SMALLINT,
1130 "text": TEXT,
1131 "time": TIME,
1132 "timestamp": TIMESTAMP,
1133 "tinyblob": TINYBLOB,
1134 "tinyint": TINYINT,
1135 "tinytext": TINYTEXT,
1136 "varbinary": VARBINARY,
1137 "varchar": VARCHAR,
1138 "year": YEAR,
1139}
1142class MySQLExecutionContext(default.DefaultExecutionContext):
1143 def should_autocommit_text(self, statement):
1144 return AUTOCOMMIT_RE.match(statement)
1146 def create_server_side_cursor(self):
1147 if self.dialect.supports_server_side_cursors:
1148 return self._dbapi_connection.cursor(self.dialect._sscursor)
1149 else:
1150 raise NotImplementedError()
1152 def fire_sequence(self, seq, type_):
1153 return self._execute_scalar(
1154 (
1155 "select nextval(%s)"
1156 % self.identifier_preparer.format_sequence(seq)
1157 ),
1158 type_,
1159 )
1162class MySQLCompiler(compiler.SQLCompiler):
1164 render_table_with_column_in_update_from = True
1165 """Overridden from base SQLCompiler value"""
1167 extract_map = compiler.SQLCompiler.extract_map.copy()
1168 extract_map.update({"milliseconds": "millisecond"})
1170 def default_from(self):
1171 """Called when a ``SELECT`` statement has no froms,
1172 and no ``FROM`` clause is to be appended.
1174 """
1175 if self.stack:
1176 stmt = self.stack[-1]["selectable"]
1177 if stmt._where_criteria:
1178 return " FROM DUAL"
1180 return ""
1182 def visit_random_func(self, fn, **kw):
1183 return "rand%s" % self.function_argspec(fn)
1185 def visit_sequence(self, seq, **kw):
1186 return "nextval(%s)" % self.preparer.format_sequence(seq)
1188 def visit_sysdate_func(self, fn, **kw):
1189 return "SYSDATE()"
1191 def _render_json_extract_from_binary(self, binary, operator, **kw):
1192 # note we are intentionally calling upon the process() calls in the
1193 # order in which they appear in the SQL String as this is used
1194 # by positional parameter rendering
1196 if binary.type._type_affinity is sqltypes.JSON:
1197 return "JSON_EXTRACT(%s, %s)" % (
1198 self.process(binary.left, **kw),
1199 self.process(binary.right, **kw),
1200 )
1202 # for non-JSON, MySQL doesn't handle JSON null at all so it has to
1203 # be explicit
1204 case_expression = "CASE JSON_EXTRACT(%s, %s) WHEN 'null' THEN NULL" % (
1205 self.process(binary.left, **kw),
1206 self.process(binary.right, **kw),
1207 )
1209 if binary.type._type_affinity is sqltypes.Integer:
1210 type_expression = (
1211 "ELSE CAST(JSON_EXTRACT(%s, %s) AS SIGNED INTEGER)"
1212 % (
1213 self.process(binary.left, **kw),
1214 self.process(binary.right, **kw),
1215 )
1216 )
1217 elif binary.type._type_affinity is sqltypes.Numeric:
1218 if (
1219 binary.type.scale is not None
1220 and binary.type.precision is not None
1221 ):
1222 # using DECIMAL here because MySQL does not recognize NUMERIC
1223 type_expression = (
1224 "ELSE CAST(JSON_EXTRACT(%s, %s) AS DECIMAL(%s, %s))"
1225 % (
1226 self.process(binary.left, **kw),
1227 self.process(binary.right, **kw),
1228 binary.type.precision,
1229 binary.type.scale,
1230 )
1231 )
1232 else:
1233 # FLOAT / REAL not added in MySQL til 8.0.17
1234 type_expression = (
1235 "ELSE JSON_EXTRACT(%s, %s)+0.0000000000000000000000"
1236 % (
1237 self.process(binary.left, **kw),
1238 self.process(binary.right, **kw),
1239 )
1240 )
1241 elif binary.type._type_affinity is sqltypes.Boolean:
1242 # the NULL handling is particularly weird with boolean, so
1243 # explicitly return true/false constants
1244 type_expression = "WHEN true THEN true ELSE false"
1245 elif binary.type._type_affinity is sqltypes.String:
1246 # (gord): this fails with a JSON value that's a four byte unicode
1247 # string. SQLite has the same problem at the moment
1248 # (zzzeek): I'm not really sure. let's take a look at a test case
1249 # that hits each backend and maybe make a requires rule for it?
1250 type_expression = "ELSE JSON_UNQUOTE(JSON_EXTRACT(%s, %s))" % (
1251 self.process(binary.left, **kw),
1252 self.process(binary.right, **kw),
1253 )
1254 else:
1255 # other affinity....this is not expected right now
1256 type_expression = "ELSE JSON_EXTRACT(%s, %s)" % (
1257 self.process(binary.left, **kw),
1258 self.process(binary.right, **kw),
1259 )
1261 return case_expression + " " + type_expression + " END"
1263 def visit_json_getitem_op_binary(self, binary, operator, **kw):
1264 return self._render_json_extract_from_binary(binary, operator, **kw)
1266 def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
1267 return self._render_json_extract_from_binary(binary, operator, **kw)
1269 def visit_on_duplicate_key_update(self, on_duplicate, **kw):
1270 statement = self.current_executable
1272 if on_duplicate._parameter_ordering:
1273 parameter_ordering = [
1274 coercions.expect(roles.DMLColumnRole, key)
1275 for key in on_duplicate._parameter_ordering
1276 ]
1277 ordered_keys = set(parameter_ordering)
1278 cols = [
1279 statement.table.c[key]
1280 for key in parameter_ordering
1281 if key in statement.table.c
1282 ] + [c for c in statement.table.c if c.key not in ordered_keys]
1283 else:
1284 cols = statement.table.c
1286 clauses = []
1287 # traverses through all table columns to preserve table column order
1288 for column in (col for col in cols if col.key in on_duplicate.update):
1290 val = on_duplicate.update[column.key]
1292 if coercions._is_literal(val):
1293 val = elements.BindParameter(None, val, type_=column.type)
1294 value_text = self.process(val.self_group(), use_schema=False)
1295 else:
1297 def replace(obj):
1298 if (
1299 isinstance(obj, elements.BindParameter)
1300 and obj.type._isnull
1301 ):
1302 obj = obj._clone()
1303 obj.type = column.type
1304 return obj
1305 elif (
1306 isinstance(obj, elements.ColumnClause)
1307 and obj.table is on_duplicate.inserted_alias
1308 ):
1309 obj = literal_column(
1310 "VALUES(" + self.preparer.quote(obj.name) + ")"
1311 )
1312 return obj
1313 else:
1314 # element is not replaced
1315 return None
1317 val = visitors.replacement_traverse(val, {}, replace)
1318 value_text = self.process(val.self_group(), use_schema=False)
1320 name_text = self.preparer.quote(column.name)
1321 clauses.append("%s = %s" % (name_text, value_text))
1323 non_matching = set(on_duplicate.update) - set(c.key for c in cols)
1324 if non_matching:
1325 util.warn(
1326 "Additional column names not matching "
1327 "any column keys in table '%s': %s"
1328 % (
1329 self.statement.table.name,
1330 (", ".join("'%s'" % c for c in non_matching)),
1331 )
1332 )
1334 return "ON DUPLICATE KEY UPDATE " + ", ".join(clauses)
1336 def visit_concat_op_binary(self, binary, operator, **kw):
1337 return "concat(%s, %s)" % (
1338 self.process(binary.left, **kw),
1339 self.process(binary.right, **kw),
1340 )
1342 _match_valid_flag_combinations = frozenset(
1343 (
1344 # (boolean_mode, natural_language, query_expansion)
1345 (False, False, False),
1346 (True, False, False),
1347 (False, True, False),
1348 (False, False, True),
1349 (False, True, True),
1350 )
1351 )
1353 _match_flag_expressions = (
1354 "IN BOOLEAN MODE",
1355 "IN NATURAL LANGUAGE MODE",
1356 "WITH QUERY EXPANSION",
1357 )
1359 def visit_mysql_match(self, element, **kw):
1360 return self.visit_match_op_binary(element, element.operator, **kw)
1362 def visit_match_op_binary(self, binary, operator, **kw):
1363 """
1364 Note that `mysql_boolean_mode` is enabled by default because of
1365 backward compatibility
1366 """
1368 modifiers = binary.modifiers
1370 boolean_mode = modifiers.get("mysql_boolean_mode", True)
1371 natural_language = modifiers.get("mysql_natural_language", False)
1372 query_expansion = modifiers.get("mysql_query_expansion", False)
1374 flag_combination = (boolean_mode, natural_language, query_expansion)
1376 if flag_combination not in self._match_valid_flag_combinations:
1377 flags = (
1378 "in_boolean_mode=%s" % boolean_mode,
1379 "in_natural_language_mode=%s" % natural_language,
1380 "with_query_expansion=%s" % query_expansion,
1381 )
1383 flags = ", ".join(flags)
1385 raise exc.CompileError("Invalid MySQL match flags: %s" % flags)
1387 match_clause = binary.left
1388 match_clause = self.process(match_clause, **kw)
1389 against_clause = self.process(binary.right, **kw)
1391 if any(flag_combination):
1392 flag_expressions = compress(
1393 self._match_flag_expressions,
1394 flag_combination,
1395 )
1397 against_clause = [against_clause]
1398 against_clause.extend(flag_expressions)
1400 against_clause = " ".join(against_clause)
1402 return "MATCH (%s) AGAINST (%s)" % (match_clause, against_clause)
1404 def get_from_hint_text(self, table, text):
1405 return text
1407 def visit_typeclause(self, typeclause, type_=None, **kw):
1408 if type_ is None:
1409 type_ = typeclause.type.dialect_impl(self.dialect)
1410 if isinstance(type_, sqltypes.TypeDecorator):
1411 return self.visit_typeclause(typeclause, type_.impl, **kw)
1412 elif isinstance(type_, sqltypes.Integer):
1413 if getattr(type_, "unsigned", False):
1414 return "UNSIGNED INTEGER"
1415 else:
1416 return "SIGNED INTEGER"
1417 elif isinstance(type_, sqltypes.TIMESTAMP):
1418 return "DATETIME"
1419 elif isinstance(
1420 type_,
1421 (
1422 sqltypes.DECIMAL,
1423 sqltypes.DateTime,
1424 sqltypes.Date,
1425 sqltypes.Time,
1426 ),
1427 ):
1428 return self.dialect.type_compiler.process(type_)
1429 elif isinstance(type_, sqltypes.String) and not isinstance(
1430 type_, (ENUM, SET)
1431 ):
1432 adapted = CHAR._adapt_string_for_cast(type_)
1433 return self.dialect.type_compiler.process(adapted)
1434 elif isinstance(type_, sqltypes._Binary):
1435 return "BINARY"
1436 elif isinstance(type_, sqltypes.JSON):
1437 return "JSON"
1438 elif isinstance(type_, sqltypes.NUMERIC):
1439 return self.dialect.type_compiler.process(type_).replace(
1440 "NUMERIC", "DECIMAL"
1441 )
1442 elif (
1443 isinstance(type_, sqltypes.Float)
1444 and self.dialect._support_float_cast
1445 ):
1446 return self.dialect.type_compiler.process(type_)
1447 else:
1448 return None
1450 def visit_cast(self, cast, **kw):
1451 type_ = self.process(cast.typeclause)
1452 if type_ is None:
1453 util.warn(
1454 "Datatype %s does not support CAST on MySQL/MariaDb; "
1455 "the CAST will be skipped."
1456 % self.dialect.type_compiler.process(cast.typeclause.type)
1457 )
1458 return self.process(cast.clause.self_group(), **kw)
1460 return "CAST(%s AS %s)" % (self.process(cast.clause, **kw), type_)
1462 def render_literal_value(self, value, type_):
1463 value = super(MySQLCompiler, self).render_literal_value(value, type_)
1464 if self.dialect._backslash_escapes:
1465 value = value.replace("\\", "\\\\")
1466 return value
1468 # override native_boolean=False behavior here, as
1469 # MySQL still supports native boolean
1470 def visit_true(self, element, **kw):
1471 return "true"
1473 def visit_false(self, element, **kw):
1474 return "false"
1476 def get_select_precolumns(self, select, **kw):
1477 """Add special MySQL keywords in place of DISTINCT.
1479 .. deprecated 1.4:: this usage is deprecated.
1480 :meth:`_expression.Select.prefix_with` should be used for special
1481 keywords at the start of a SELECT.
1483 """
1484 if isinstance(select._distinct, util.string_types):
1485 util.warn_deprecated(
1486 "Sending string values for 'distinct' is deprecated in the "
1487 "MySQL dialect and will be removed in a future release. "
1488 "Please use :meth:`.Select.prefix_with` for special keywords "
1489 "at the start of a SELECT statement",
1490 version="1.4",
1491 )
1492 return select._distinct.upper() + " "
1494 return super(MySQLCompiler, self).get_select_precolumns(select, **kw)
1496 def visit_join(self, join, asfrom=False, from_linter=None, **kwargs):
1497 if from_linter:
1498 from_linter.edges.add((join.left, join.right))
1500 if join.full:
1501 join_type = " FULL OUTER JOIN "
1502 elif join.isouter:
1503 join_type = " LEFT OUTER JOIN "
1504 else:
1505 join_type = " INNER JOIN "
1507 return "".join(
1508 (
1509 self.process(
1510 join.left, asfrom=True, from_linter=from_linter, **kwargs
1511 ),
1512 join_type,
1513 self.process(
1514 join.right, asfrom=True, from_linter=from_linter, **kwargs
1515 ),
1516 " ON ",
1517 self.process(join.onclause, from_linter=from_linter, **kwargs),
1518 )
1519 )
1521 def for_update_clause(self, select, **kw):
1522 if select._for_update_arg.read:
1523 tmp = " LOCK IN SHARE MODE"
1524 else:
1525 tmp = " FOR UPDATE"
1527 if select._for_update_arg.of and self.dialect.supports_for_update_of:
1529 tables = util.OrderedSet()
1530 for c in select._for_update_arg.of:
1531 tables.update(sql_util.surface_selectables_only(c))
1533 tmp += " OF " + ", ".join(
1534 self.process(table, ashint=True, use_schema=False, **kw)
1535 for table in tables
1536 )
1538 if select._for_update_arg.nowait:
1539 tmp += " NOWAIT"
1541 if select._for_update_arg.skip_locked:
1542 tmp += " SKIP LOCKED"
1544 return tmp
1546 def limit_clause(self, select, **kw):
1547 # MySQL supports:
1548 # LIMIT <limit>
1549 # LIMIT <offset>, <limit>
1550 # and in server versions > 3.3:
1551 # LIMIT <limit> OFFSET <offset>
1552 # The latter is more readable for offsets but we're stuck with the
1553 # former until we can refine dialects by server revision.
1555 limit_clause, offset_clause = (
1556 select._limit_clause,
1557 select._offset_clause,
1558 )
1560 if limit_clause is None and offset_clause is None:
1561 return ""
1562 elif offset_clause is not None:
1563 # As suggested by the MySQL docs, need to apply an
1564 # artificial limit if one wasn't provided
1565 # https://dev.mysql.com/doc/refman/5.0/en/select.html
1566 if limit_clause is None:
1567 # hardwire the upper limit. Currently
1568 # needed by OurSQL with Python 3
1569 # (https://bugs.launchpad.net/oursql/+bug/686232),
1570 # but also is consistent with the usage of the upper
1571 # bound as part of MySQL's "syntax" for OFFSET with
1572 # no LIMIT
1573 return " \n LIMIT %s, %s" % (
1574 self.process(offset_clause, **kw),
1575 "18446744073709551615",
1576 )
1577 else:
1578 return " \n LIMIT %s, %s" % (
1579 self.process(offset_clause, **kw),
1580 self.process(limit_clause, **kw),
1581 )
1582 else:
1583 # No offset provided, so just use the limit
1584 return " \n LIMIT %s" % (self.process(limit_clause, **kw),)
1586 def update_limit_clause(self, update_stmt):
1587 limit = update_stmt.kwargs.get("%s_limit" % self.dialect.name, None)
1588 if limit:
1589 return "LIMIT %s" % limit
1590 else:
1591 return None
1593 def update_tables_clause(self, update_stmt, from_table, extra_froms, **kw):
1594 kw["asfrom"] = True
1595 return ", ".join(
1596 t._compiler_dispatch(self, **kw)
1597 for t in [from_table] + list(extra_froms)
1598 )
1600 def update_from_clause(
1601 self, update_stmt, from_table, extra_froms, from_hints, **kw
1602 ):
1603 return None
1605 def delete_table_clause(self, delete_stmt, from_table, extra_froms):
1606 """If we have extra froms make sure we render any alias as hint."""
1607 ashint = False
1608 if extra_froms:
1609 ashint = True
1610 return from_table._compiler_dispatch(
1611 self, asfrom=True, iscrud=True, ashint=ashint
1612 )
1614 def delete_extra_from_clause(
1615 self, delete_stmt, from_table, extra_froms, from_hints, **kw
1616 ):
1617 """Render the DELETE .. USING clause specific to MySQL."""
1618 kw["asfrom"] = True
1619 return "USING " + ", ".join(
1620 t._compiler_dispatch(self, fromhints=from_hints, **kw)
1621 for t in [from_table] + extra_froms
1622 )
1624 def visit_empty_set_expr(self, element_types):
1625 return (
1626 "SELECT %(outer)s FROM (SELECT %(inner)s) "
1627 "as _empty_set WHERE 1!=1"
1628 % {
1629 "inner": ", ".join(
1630 "1 AS _in_%s" % idx
1631 for idx, type_ in enumerate(element_types)
1632 ),
1633 "outer": ", ".join(
1634 "_in_%s" % idx for idx, type_ in enumerate(element_types)
1635 ),
1636 }
1637 )
1639 def visit_is_distinct_from_binary(self, binary, operator, **kw):
1640 return "NOT (%s <=> %s)" % (
1641 self.process(binary.left),
1642 self.process(binary.right),
1643 )
1645 def visit_is_not_distinct_from_binary(self, binary, operator, **kw):
1646 return "%s <=> %s" % (
1647 self.process(binary.left),
1648 self.process(binary.right),
1649 )
1651 def _mariadb_regexp_flags(self, flags, pattern, **kw):
1652 return "CONCAT('(?', %s, ')', %s)" % (
1653 self.render_literal_value(flags, sqltypes.STRINGTYPE),
1654 self.process(pattern, **kw),
1655 )
1657 def _regexp_match(self, op_string, binary, operator, **kw):
1658 flags = binary.modifiers["flags"]
1659 if flags is None:
1660 return self._generate_generic_binary(binary, op_string, **kw)
1661 elif self.dialect.is_mariadb:
1662 return "%s%s%s" % (
1663 self.process(binary.left, **kw),
1664 op_string,
1665 self._mariadb_regexp_flags(flags, binary.right),
1666 )
1667 else:
1668 text = "REGEXP_LIKE(%s, %s, %s)" % (
1669 self.process(binary.left, **kw),
1670 self.process(binary.right, **kw),
1671 self.render_literal_value(flags, sqltypes.STRINGTYPE),
1672 )
1673 if op_string == " NOT REGEXP ":
1674 return "NOT %s" % text
1675 else:
1676 return text
1678 def visit_regexp_match_op_binary(self, binary, operator, **kw):
1679 return self._regexp_match(" REGEXP ", binary, operator, **kw)
1681 def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
1682 return self._regexp_match(" NOT REGEXP ", binary, operator, **kw)
1684 def visit_regexp_replace_op_binary(self, binary, operator, **kw):
1685 flags = binary.modifiers["flags"]
1686 if flags is None:
1687 return "REGEXP_REPLACE(%s, %s)" % (
1688 self.process(binary.left, **kw),
1689 self.process(binary.right, **kw),
1690 )
1691 elif self.dialect.is_mariadb:
1692 return "REGEXP_REPLACE(%s, %s, %s)" % (
1693 self.process(binary.left, **kw),
1694 self._mariadb_regexp_flags(flags, binary.right.clauses[0]),
1695 self.process(binary.right.clauses[1], **kw),
1696 )
1697 else:
1698 return "REGEXP_REPLACE(%s, %s, %s)" % (
1699 self.process(binary.left, **kw),
1700 self.process(binary.right, **kw),
1701 self.render_literal_value(flags, sqltypes.STRINGTYPE),
1702 )
1705class MySQLDDLCompiler(compiler.DDLCompiler):
1706 def get_column_specification(self, column, **kw):
1707 """Builds column DDL."""
1709 colspec = [
1710 self.preparer.format_column(column),
1711 self.dialect.type_compiler.process(
1712 column.type, type_expression=column
1713 ),
1714 ]
1716 if column.computed is not None:
1717 colspec.append(self.process(column.computed))
1719 is_timestamp = isinstance(
1720 column.type._unwrapped_dialect_impl(self.dialect),
1721 sqltypes.TIMESTAMP,
1722 )
1724 if not column.nullable:
1725 colspec.append("NOT NULL")
1727 # see: https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql_timestamp_null # noqa
1728 elif column.nullable and is_timestamp:
1729 colspec.append("NULL")
1731 comment = column.comment
1732 if comment is not None:
1733 literal = self.sql_compiler.render_literal_value(
1734 comment, sqltypes.String()
1735 )
1736 colspec.append("COMMENT " + literal)
1738 if (
1739 column.table is not None
1740 and column is column.table._autoincrement_column
1741 and (
1742 column.server_default is None
1743 or isinstance(column.server_default, sa_schema.Identity)
1744 )
1745 and not (
1746 self.dialect.supports_sequences
1747 and isinstance(column.default, sa_schema.Sequence)
1748 and not column.default.optional
1749 )
1750 ):
1751 colspec.append("AUTO_INCREMENT")
1752 else:
1753 default = self.get_column_default_string(column)
1754 if default is not None:
1755 colspec.append("DEFAULT " + default)
1756 return " ".join(colspec)
1758 def post_create_table(self, table):
1759 """Build table-level CREATE options like ENGINE and COLLATE."""
1761 table_opts = []
1763 opts = dict(
1764 (k[len(self.dialect.name) + 1 :].upper(), v)
1765 for k, v in table.kwargs.items()
1766 if k.startswith("%s_" % self.dialect.name)
1767 )
1769 if table.comment is not None:
1770 opts["COMMENT"] = table.comment
1772 partition_options = [
1773 "PARTITION_BY",
1774 "PARTITIONS",
1775 "SUBPARTITIONS",
1776 "SUBPARTITION_BY",
1777 ]
1779 nonpart_options = set(opts).difference(partition_options)
1780 part_options = set(opts).intersection(partition_options)
1782 for opt in topological.sort(
1783 [
1784 ("DEFAULT_CHARSET", "COLLATE"),
1785 ("DEFAULT_CHARACTER_SET", "COLLATE"),
1786 ("CHARSET", "COLLATE"),
1787 ("CHARACTER_SET", "COLLATE"),
1788 ],
1789 nonpart_options,
1790 ):
1791 arg = opts[opt]
1792 if opt in _reflection._options_of_type_string:
1794 arg = self.sql_compiler.render_literal_value(
1795 arg, sqltypes.String()
1796 )
1798 if opt in (
1799 "DATA_DIRECTORY",
1800 "INDEX_DIRECTORY",
1801 "DEFAULT_CHARACTER_SET",
1802 "CHARACTER_SET",
1803 "DEFAULT_CHARSET",
1804 "DEFAULT_COLLATE",
1805 ):
1806 opt = opt.replace("_", " ")
1808 joiner = "="
1809 if opt in (
1810 "TABLESPACE",
1811 "DEFAULT CHARACTER SET",
1812 "CHARACTER SET",
1813 "COLLATE",
1814 ):
1815 joiner = " "
1817 table_opts.append(joiner.join((opt, arg)))
1819 for opt in topological.sort(
1820 [
1821 ("PARTITION_BY", "PARTITIONS"),
1822 ("PARTITION_BY", "SUBPARTITION_BY"),
1823 ("PARTITION_BY", "SUBPARTITIONS"),
1824 ("PARTITIONS", "SUBPARTITIONS"),
1825 ("PARTITIONS", "SUBPARTITION_BY"),
1826 ("SUBPARTITION_BY", "SUBPARTITIONS"),
1827 ],
1828 part_options,
1829 ):
1830 arg = opts[opt]
1831 if opt in _reflection._options_of_type_string:
1832 arg = self.sql_compiler.render_literal_value(
1833 arg, sqltypes.String()
1834 )
1836 opt = opt.replace("_", " ")
1837 joiner = " "
1839 table_opts.append(joiner.join((opt, arg)))
1841 return " ".join(table_opts)
1843 def visit_create_index(self, create, **kw):
1844 index = create.element
1845 self._verify_index_table(index)
1846 preparer = self.preparer
1847 table = preparer.format_table(index.table)
1849 columns = [
1850 self.sql_compiler.process(
1851 elements.Grouping(expr)
1852 if (
1853 isinstance(expr, elements.BinaryExpression)
1854 or (
1855 isinstance(expr, elements.UnaryExpression)
1856 and expr.modifier
1857 not in (operators.desc_op, operators.asc_op)
1858 )
1859 or isinstance(expr, functions.FunctionElement)
1860 )
1861 else expr,
1862 include_table=False,
1863 literal_binds=True,
1864 )
1865 for expr in index.expressions
1866 ]
1868 name = self._prepared_index_name(index)
1870 text = "CREATE "
1871 if index.unique:
1872 text += "UNIQUE "
1874 index_prefix = index.kwargs.get("%s_prefix" % self.dialect.name, None)
1875 if index_prefix:
1876 text += index_prefix + " "
1878 text += "INDEX "
1879 if create.if_not_exists:
1880 text += "IF NOT EXISTS "
1881 text += "%s ON %s " % (name, table)
1883 length = index.dialect_options[self.dialect.name]["length"]
1884 if length is not None:
1886 if isinstance(length, dict):
1887 # length value can be a (column_name --> integer value)
1888 # mapping specifying the prefix length for each column of the
1889 # index
1890 columns = ", ".join(
1891 "%s(%d)" % (expr, length[col.name])
1892 if col.name in length
1893 else (
1894 "%s(%d)" % (expr, length[expr])
1895 if expr in length
1896 else "%s" % expr
1897 )
1898 for col, expr in zip(index.expressions, columns)
1899 )
1900 else:
1901 # or can be an integer value specifying the same
1902 # prefix length for all columns of the index
1903 columns = ", ".join(
1904 "%s(%d)" % (col, length) for col in columns
1905 )
1906 else:
1907 columns = ", ".join(columns)
1908 text += "(%s)" % columns
1910 parser = index.dialect_options["mysql"]["with_parser"]
1911 if parser is not None:
1912 text += " WITH PARSER %s" % (parser,)
1914 using = index.dialect_options["mysql"]["using"]
1915 if using is not None:
1916 text += " USING %s" % (preparer.quote(using))
1918 return text
1920 def visit_primary_key_constraint(self, constraint):
1921 text = super(MySQLDDLCompiler, self).visit_primary_key_constraint(
1922 constraint
1923 )
1924 using = constraint.dialect_options["mysql"]["using"]
1925 if using:
1926 text += " USING %s" % (self.preparer.quote(using))
1927 return text
1929 def visit_drop_index(self, drop):
1930 index = drop.element
1931 text = "\nDROP INDEX "
1932 if drop.if_exists:
1933 text += "IF EXISTS "
1935 return text + "%s ON %s" % (
1936 self._prepared_index_name(index, include_schema=False),
1937 self.preparer.format_table(index.table),
1938 )
1940 def visit_drop_constraint(self, drop):
1941 constraint = drop.element
1942 if isinstance(constraint, sa_schema.ForeignKeyConstraint):
1943 qual = "FOREIGN KEY "
1944 const = self.preparer.format_constraint(constraint)
1945 elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
1946 qual = "PRIMARY KEY "
1947 const = ""
1948 elif isinstance(constraint, sa_schema.UniqueConstraint):
1949 qual = "INDEX "
1950 const = self.preparer.format_constraint(constraint)
1951 elif isinstance(constraint, sa_schema.CheckConstraint):
1952 if self.dialect.is_mariadb:
1953 qual = "CONSTRAINT "
1954 else:
1955 qual = "CHECK "
1956 const = self.preparer.format_constraint(constraint)
1957 else:
1958 qual = ""
1959 const = self.preparer.format_constraint(constraint)
1960 return "ALTER TABLE %s DROP %s%s" % (
1961 self.preparer.format_table(constraint.table),
1962 qual,
1963 const,
1964 )
1966 def define_constraint_match(self, constraint):
1967 if constraint.match is not None:
1968 raise exc.CompileError(
1969 "MySQL ignores the 'MATCH' keyword while at the same time "
1970 "causes ON UPDATE/ON DELETE clauses to be ignored."
1971 )
1972 return ""
1974 def visit_set_table_comment(self, create):
1975 return "ALTER TABLE %s COMMENT %s" % (
1976 self.preparer.format_table(create.element),
1977 self.sql_compiler.render_literal_value(
1978 create.element.comment, sqltypes.String()
1979 ),
1980 )
1982 def visit_drop_table_comment(self, create):
1983 return "ALTER TABLE %s COMMENT ''" % (
1984 self.preparer.format_table(create.element)
1985 )
1987 def visit_set_column_comment(self, create):
1988 return "ALTER TABLE %s CHANGE %s %s" % (
1989 self.preparer.format_table(create.element.table),
1990 self.preparer.format_column(create.element),
1991 self.get_column_specification(create.element),
1992 )
1995class MySQLTypeCompiler(compiler.GenericTypeCompiler):
1996 def _extend_numeric(self, type_, spec):
1997 "Extend a numeric-type declaration with MySQL specific extensions."
1999 if not self._mysql_type(type_):
2000 return spec
2002 if type_.unsigned:
2003 spec += " UNSIGNED"
2004 if type_.zerofill:
2005 spec += " ZEROFILL"
2006 return spec
2008 def _extend_string(self, type_, defaults, spec):
2009 """Extend a string-type declaration with standard SQL CHARACTER SET /
2010 COLLATE annotations and MySQL specific extensions.
2012 """
2014 def attr(name):
2015 return getattr(type_, name, defaults.get(name))
2017 if attr("charset"):
2018 charset = "CHARACTER SET %s" % attr("charset")
2019 elif attr("ascii"):
2020 charset = "ASCII"
2021 elif attr("unicode"):
2022 charset = "UNICODE"
2023 else:
2024 charset = None
2026 if attr("collation"):
2027 collation = "COLLATE %s" % type_.collation
2028 elif attr("binary"):
2029 collation = "BINARY"
2030 else:
2031 collation = None
2033 if attr("national"):
2034 # NATIONAL (aka NCHAR/NVARCHAR) trumps charsets.
2035 return " ".join(
2036 [c for c in ("NATIONAL", spec, collation) if c is not None]
2037 )
2038 return " ".join(
2039 [c for c in (spec, charset, collation) if c is not None]
2040 )
2042 def _mysql_type(self, type_):
2043 return isinstance(type_, (_StringType, _NumericType))
2045 def visit_NUMERIC(self, type_, **kw):
2046 if type_.precision is None:
2047 return self._extend_numeric(type_, "NUMERIC")
2048 elif type_.scale is None:
2049 return self._extend_numeric(
2050 type_,
2051 "NUMERIC(%(precision)s)" % {"precision": type_.precision},
2052 )
2053 else:
2054 return self._extend_numeric(
2055 type_,
2056 "NUMERIC(%(precision)s, %(scale)s)"
2057 % {"precision": type_.precision, "scale": type_.scale},
2058 )
2060 def visit_DECIMAL(self, type_, **kw):
2061 if type_.precision is None:
2062 return self._extend_numeric(type_, "DECIMAL")
2063 elif type_.scale is None:
2064 return self._extend_numeric(
2065 type_,
2066 "DECIMAL(%(precision)s)" % {"precision": type_.precision},
2067 )
2068 else:
2069 return self._extend_numeric(
2070 type_,
2071 "DECIMAL(%(precision)s, %(scale)s)"
2072 % {"precision": type_.precision, "scale": type_.scale},
2073 )
2075 def visit_DOUBLE(self, type_, **kw):
2076 if type_.precision is not None and type_.scale is not None:
2077 return self._extend_numeric(
2078 type_,
2079 "DOUBLE(%(precision)s, %(scale)s)"
2080 % {"precision": type_.precision, "scale": type_.scale},
2081 )
2082 else:
2083 return self._extend_numeric(type_, "DOUBLE")
2085 def visit_REAL(self, type_, **kw):
2086 if type_.precision is not None and type_.scale is not None:
2087 return self._extend_numeric(
2088 type_,
2089 "REAL(%(precision)s, %(scale)s)"
2090 % {"precision": type_.precision, "scale": type_.scale},
2091 )
2092 else:
2093 return self._extend_numeric(type_, "REAL")
2095 def visit_FLOAT(self, type_, **kw):
2096 if (
2097 self._mysql_type(type_)
2098 and type_.scale is not None
2099 and type_.precision is not None
2100 ):
2101 return self._extend_numeric(
2102 type_, "FLOAT(%s, %s)" % (type_.precision, type_.scale)
2103 )
2104 elif type_.precision is not None:
2105 return self._extend_numeric(
2106 type_, "FLOAT(%s)" % (type_.precision,)
2107 )
2108 else:
2109 return self._extend_numeric(type_, "FLOAT")
2111 def visit_INTEGER(self, type_, **kw):
2112 if self._mysql_type(type_) and type_.display_width is not None:
2113 return self._extend_numeric(
2114 type_,
2115 "INTEGER(%(display_width)s)"
2116 % {"display_width": type_.display_width},
2117 )
2118 else:
2119 return self._extend_numeric(type_, "INTEGER")
2121 def visit_BIGINT(self, type_, **kw):
2122 if self._mysql_type(type_) and type_.display_width is not None:
2123 return self._extend_numeric(
2124 type_,
2125 "BIGINT(%(display_width)s)"
2126 % {"display_width": type_.display_width},
2127 )
2128 else:
2129 return self._extend_numeric(type_, "BIGINT")
2131 def visit_MEDIUMINT(self, type_, **kw):
2132 if self._mysql_type(type_) and type_.display_width is not None:
2133 return self._extend_numeric(
2134 type_,
2135 "MEDIUMINT(%(display_width)s)"
2136 % {"display_width": type_.display_width},
2137 )
2138 else:
2139 return self._extend_numeric(type_, "MEDIUMINT")
2141 def visit_TINYINT(self, type_, **kw):
2142 if self._mysql_type(type_) and type_.display_width is not None:
2143 return self._extend_numeric(
2144 type_, "TINYINT(%s)" % type_.display_width
2145 )
2146 else:
2147 return self._extend_numeric(type_, "TINYINT")
2149 def visit_SMALLINT(self, type_, **kw):
2150 if self._mysql_type(type_) and type_.display_width is not None:
2151 return self._extend_numeric(
2152 type_,
2153 "SMALLINT(%(display_width)s)"
2154 % {"display_width": type_.display_width},
2155 )
2156 else:
2157 return self._extend_numeric(type_, "SMALLINT")
2159 def visit_BIT(self, type_, **kw):
2160 if type_.length is not None:
2161 return "BIT(%s)" % type_.length
2162 else:
2163 return "BIT"
2165 def visit_DATETIME(self, type_, **kw):
2166 if getattr(type_, "fsp", None):
2167 return "DATETIME(%d)" % type_.fsp
2168 else:
2169 return "DATETIME"
2171 def visit_DATE(self, type_, **kw):
2172 return "DATE"
2174 def visit_TIME(self, type_, **kw):
2175 if getattr(type_, "fsp", None):
2176 return "TIME(%d)" % type_.fsp
2177 else:
2178 return "TIME"
2180 def visit_TIMESTAMP(self, type_, **kw):
2181 if getattr(type_, "fsp", None):
2182 return "TIMESTAMP(%d)" % type_.fsp
2183 else:
2184 return "TIMESTAMP"
2186 def visit_YEAR(self, type_, **kw):
2187 if type_.display_width is None:
2188 return "YEAR"
2189 else:
2190 return "YEAR(%s)" % type_.display_width
2192 def visit_TEXT(self, type_, **kw):
2193 if type_.length:
2194 return self._extend_string(type_, {}, "TEXT(%d)" % type_.length)
2195 else:
2196 return self._extend_string(type_, {}, "TEXT")
2198 def visit_TINYTEXT(self, type_, **kw):
2199 return self._extend_string(type_, {}, "TINYTEXT")
2201 def visit_MEDIUMTEXT(self, type_, **kw):
2202 return self._extend_string(type_, {}, "MEDIUMTEXT")
2204 def visit_LONGTEXT(self, type_, **kw):
2205 return self._extend_string(type_, {}, "LONGTEXT")
2207 def visit_VARCHAR(self, type_, **kw):
2208 if type_.length:
2209 return self._extend_string(type_, {}, "VARCHAR(%d)" % type_.length)
2210 else:
2211 raise exc.CompileError(
2212 "VARCHAR requires a length on dialect %s" % self.dialect.name
2213 )
2215 def visit_CHAR(self, type_, **kw):
2216 if type_.length:
2217 return self._extend_string(
2218 type_, {}, "CHAR(%(length)s)" % {"length": type_.length}
2219 )
2220 else:
2221 return self._extend_string(type_, {}, "CHAR")
2223 def visit_NVARCHAR(self, type_, **kw):
2224 # We'll actually generate the equiv. "NATIONAL VARCHAR" instead
2225 # of "NVARCHAR".
2226 if type_.length:
2227 return self._extend_string(
2228 type_,
2229 {"national": True},
2230 "VARCHAR(%(length)s)" % {"length": type_.length},
2231 )
2232 else:
2233 raise exc.CompileError(
2234 "NVARCHAR requires a length on dialect %s" % self.dialect.name
2235 )
2237 def visit_NCHAR(self, type_, **kw):
2238 # We'll actually generate the equiv.
2239 # "NATIONAL CHAR" instead of "NCHAR".
2240 if type_.length:
2241 return self._extend_string(
2242 type_,
2243 {"national": True},
2244 "CHAR(%(length)s)" % {"length": type_.length},
2245 )
2246 else:
2247 return self._extend_string(type_, {"national": True}, "CHAR")
2249 def visit_VARBINARY(self, type_, **kw):
2250 return "VARBINARY(%d)" % type_.length
2252 def visit_JSON(self, type_, **kw):
2253 return "JSON"
2255 def visit_large_binary(self, type_, **kw):
2256 return self.visit_BLOB(type_)
2258 def visit_enum(self, type_, **kw):
2259 if not type_.native_enum:
2260 return super(MySQLTypeCompiler, self).visit_enum(type_)
2261 else:
2262 return self._visit_enumerated_values("ENUM", type_, type_.enums)
2264 def visit_BLOB(self, type_, **kw):
2265 if type_.length:
2266 return "BLOB(%d)" % type_.length
2267 else:
2268 return "BLOB"
2270 def visit_TINYBLOB(self, type_, **kw):
2271 return "TINYBLOB"
2273 def visit_MEDIUMBLOB(self, type_, **kw):
2274 return "MEDIUMBLOB"
2276 def visit_LONGBLOB(self, type_, **kw):
2277 return "LONGBLOB"
2279 def _visit_enumerated_values(self, name, type_, enumerated_values):
2280 quoted_enums = []
2281 for e in enumerated_values:
2282 quoted_enums.append("'%s'" % e.replace("'", "''"))
2283 return self._extend_string(
2284 type_, {}, "%s(%s)" % (name, ",".join(quoted_enums))
2285 )
2287 def visit_ENUM(self, type_, **kw):
2288 return self._visit_enumerated_values("ENUM", type_, type_.enums)
2290 def visit_SET(self, type_, **kw):
2291 return self._visit_enumerated_values("SET", type_, type_.values)
2293 def visit_BOOLEAN(self, type_, **kw):
2294 return "BOOL"
2297class MySQLIdentifierPreparer(compiler.IdentifierPreparer):
2298 reserved_words = RESERVED_WORDS_MYSQL
2300 def __init__(self, dialect, server_ansiquotes=False, **kw):
2301 if not server_ansiquotes:
2302 quote = "`"
2303 else:
2304 quote = '"'
2306 super(MySQLIdentifierPreparer, self).__init__(
2307 dialect, initial_quote=quote, escape_quote=quote
2308 )
2310 def _quote_free_identifiers(self, *ids):
2311 """Unilaterally identifier-quote any number of strings."""
2313 return tuple([self.quote_identifier(i) for i in ids if i is not None])
2316class MariaDBIdentifierPreparer(MySQLIdentifierPreparer):
2317 reserved_words = RESERVED_WORDS_MARIADB
2320@log.class_logger
2321class MySQLDialect(default.DefaultDialect):
2322 """Details of the MySQL dialect.
2323 Not used directly in application code.
2324 """
2326 name = "mysql"
2327 supports_statement_cache = True
2329 supports_alter = True
2331 # MySQL has no true "boolean" type; we
2332 # allow for the "true" and "false" keywords, however
2333 supports_native_boolean = False
2335 # identifiers are 64, however aliases can be 255...
2336 max_identifier_length = 255
2337 max_index_name_length = 64
2338 max_constraint_name_length = 64
2340 supports_native_enum = True
2342 supports_sequences = False # default for MySQL ...
2343 # ... may be updated to True for MariaDB 10.3+ in initialize()
2345 sequences_optional = False
2347 supports_for_update_of = False # default for MySQL ...
2348 # ... may be updated to True for MySQL 8+ in initialize()
2350 # MySQL doesn't support "DEFAULT VALUES" but *does* support
2351 # "VALUES (DEFAULT)"
2352 supports_default_values = False
2353 supports_default_metavalue = True
2355 supports_sane_rowcount = True
2356 supports_sane_multi_rowcount = False
2357 supports_multivalues_insert = True
2359 supports_comments = True
2360 inline_comments = True
2361 default_paramstyle = "format"
2362 colspecs = colspecs
2364 cte_follows_insert = True
2366 statement_compiler = MySQLCompiler
2367 ddl_compiler = MySQLDDLCompiler
2368 type_compiler = MySQLTypeCompiler
2369 ischema_names = ischema_names
2370 preparer = MySQLIdentifierPreparer
2372 is_mariadb = False
2373 _mariadb_normalized_version_info = None
2375 # default SQL compilation settings -
2376 # these are modified upon initialize(),
2377 # i.e. first connect
2378 _backslash_escapes = True
2379 _server_ansiquotes = False
2381 construct_arguments = [
2382 (sa_schema.Table, {"*": None}),
2383 (sql.Update, {"limit": None}),
2384 (sa_schema.PrimaryKeyConstraint, {"using": None}),
2385 (
2386 sa_schema.Index,
2387 {
2388 "using": None,
2389 "length": None,
2390 "prefix": None,
2391 "with_parser": None,
2392 },
2393 ),
2394 ]
2396 def __init__(
2397 self,
2398 isolation_level=None,
2399 json_serializer=None,
2400 json_deserializer=None,
2401 is_mariadb=None,
2402 **kwargs
2403 ):
2404 kwargs.pop("use_ansiquotes", None) # legacy
2405 default.DefaultDialect.__init__(self, **kwargs)
2406 self.isolation_level = isolation_level
2407 self._json_serializer = json_serializer
2408 self._json_deserializer = json_deserializer
2409 self._set_mariadb(is_mariadb, None)
2411 def on_connect(self):
2412 if self.isolation_level is not None:
2414 def connect(conn):
2415 self.set_isolation_level(conn, self.isolation_level)
2417 return connect
2418 else:
2419 return None
2421 _isolation_lookup = set(
2422 [
2423 "SERIALIZABLE",
2424 "READ UNCOMMITTED",
2425 "READ COMMITTED",
2426 "REPEATABLE READ",
2427 ]
2428 )
2430 def set_isolation_level(self, connection, level):
2431 level = level.replace("_", " ")
2433 # adjust for ConnectionFairy being present
2434 # allows attribute set e.g. "connection.autocommit = True"
2435 # to work properly
2436 if hasattr(connection, "dbapi_connection"):
2437 connection = connection.dbapi_connection
2439 self._set_isolation_level(connection, level)
2441 def _set_isolation_level(self, connection, level):
2442 if level not in self._isolation_lookup:
2443 raise exc.ArgumentError(
2444 "Invalid value '%s' for isolation_level. "
2445 "Valid isolation levels for %s are %s"
2446 % (level, self.name, ", ".join(self._isolation_lookup))
2447 )
2448 cursor = connection.cursor()
2449 cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % level)
2450 cursor.execute("COMMIT")
2451 cursor.close()
2453 def get_isolation_level(self, connection):
2454 cursor = connection.cursor()
2455 if self._is_mysql and self.server_version_info >= (5, 7, 20):
2456 cursor.execute("SELECT @@transaction_isolation")
2457 else:
2458 cursor.execute("SELECT @@tx_isolation")
2459 row = cursor.fetchone()
2460 if row is None:
2461 util.warn(
2462 "Could not retrieve transaction isolation level for MySQL "
2463 "connection."
2464 )
2465 raise NotImplementedError()
2466 val = row[0]
2467 cursor.close()
2468 if util.py3k and isinstance(val, bytes):
2469 val = val.decode()
2470 return val.upper().replace("-", " ")
2472 @classmethod
2473 def _is_mariadb_from_url(cls, url):
2474 dbapi = cls.dbapi()
2475 dialect = cls(dbapi=dbapi)
2477 cargs, cparams = dialect.create_connect_args(url)
2478 conn = dialect.connect(*cargs, **cparams)
2479 try:
2480 cursor = conn.cursor()
2481 cursor.execute("SELECT VERSION() LIKE '%MariaDB%'")
2482 val = cursor.fetchone()[0]
2483 except:
2484 raise
2485 else:
2486 return bool(val)
2487 finally:
2488 conn.close()
2490 def _get_server_version_info(self, connection):
2491 # get database server version info explicitly over the wire
2492 # to avoid proxy servers like MaxScale getting in the
2493 # way with their own values, see #4205
2494 dbapi_con = connection.connection
2495 cursor = dbapi_con.cursor()
2496 cursor.execute("SELECT VERSION()")
2497 val = cursor.fetchone()[0]
2498 cursor.close()
2499 if util.py3k and isinstance(val, bytes):
2500 val = val.decode()
2502 return self._parse_server_version(val)
2504 def _parse_server_version(self, val):
2505 version = []
2506 is_mariadb = False
2508 r = re.compile(r"[.\-+]")
2509 tokens = r.split(val)
2510 for token in tokens:
2511 parsed_token = re.match(
2512 r"^(?:(\d+)(?:a|b|c)?|(MariaDB\w*))$", token
2513 )
2514 if not parsed_token:
2515 continue
2516 elif parsed_token.group(2):
2517 self._mariadb_normalized_version_info = tuple(version[-3:])
2518 is_mariadb = True
2519 else:
2520 digit = int(parsed_token.group(1))
2521 version.append(digit)
2523 server_version_info = tuple(version)
2525 self._set_mariadb(server_version_info and is_mariadb, val)
2527 if not is_mariadb:
2528 self._mariadb_normalized_version_info = server_version_info
2530 if server_version_info < (5, 0, 2):
2531 raise NotImplementedError(
2532 "the MySQL/MariaDB dialect supports server "
2533 "version info 5.0.2 and above."
2534 )
2536 # setting it here to help w the test suite
2537 self.server_version_info = server_version_info
2538 return server_version_info
2540 def _set_mariadb(self, is_mariadb, server_version_info):
2541 if is_mariadb is None:
2542 return
2544 if not is_mariadb and self.is_mariadb:
2545 raise exc.InvalidRequestError(
2546 "MySQL version %s is not a MariaDB variant."
2547 % (server_version_info,)
2548 )
2549 if is_mariadb:
2550 self.preparer = MariaDBIdentifierPreparer
2551 # this would have been set by the default dialect already,
2552 # so set it again
2553 self.identifier_preparer = self.preparer(self)
2554 self.is_mariadb = is_mariadb
2556 def do_begin_twophase(self, connection, xid):
2557 connection.execute(sql.text("XA BEGIN :xid"), dict(xid=xid))
2559 def do_prepare_twophase(self, connection, xid):
2560 connection.execute(sql.text("XA END :xid"), dict(xid=xid))
2561 connection.execute(sql.text("XA PREPARE :xid"), dict(xid=xid))
2563 def do_rollback_twophase(
2564 self, connection, xid, is_prepared=True, recover=False
2565 ):
2566 if not is_prepared:
2567 connection.execute(sql.text("XA END :xid"), dict(xid=xid))
2568 connection.execute(sql.text("XA ROLLBACK :xid"), dict(xid=xid))
2570 def do_commit_twophase(
2571 self, connection, xid, is_prepared=True, recover=False
2572 ):
2573 if not is_prepared:
2574 self.do_prepare_twophase(connection, xid)
2575 connection.execute(sql.text("XA COMMIT :xid"), dict(xid=xid))
2577 def do_recover_twophase(self, connection):
2578 resultset = connection.exec_driver_sql("XA RECOVER")
2579 return [row["data"][0 : row["gtrid_length"]] for row in resultset]
2581 def is_disconnect(self, e, connection, cursor):
2582 if isinstance(
2583 e,
2584 (
2585 self.dbapi.OperationalError,
2586 self.dbapi.ProgrammingError,
2587 self.dbapi.InterfaceError,
2588 ),
2589 ) and self._extract_error_code(e) in (
2590 1927,
2591 2006,
2592 2013,
2593 2014,
2594 2045,
2595 2055,
2596 4031,
2597 ):
2598 return True
2599 elif isinstance(
2600 e, (self.dbapi.InterfaceError, self.dbapi.InternalError)
2601 ):
2602 # if underlying connection is closed,
2603 # this is the error you get
2604 return "(0, '')" in str(e)
2605 else:
2606 return False
2608 def _compat_fetchall(self, rp, charset=None):
2609 """Proxy result rows to smooth over MySQL-Python driver
2610 inconsistencies."""
2612 return [_DecodingRow(row, charset) for row in rp.fetchall()]
2614 def _compat_fetchone(self, rp, charset=None):
2615 """Proxy a result row to smooth over MySQL-Python driver
2616 inconsistencies."""
2618 row = rp.fetchone()
2619 if row:
2620 return _DecodingRow(row, charset)
2621 else:
2622 return None
2624 def _compat_first(self, rp, charset=None):
2625 """Proxy a result row to smooth over MySQL-Python driver
2626 inconsistencies."""
2628 row = rp.first()
2629 if row:
2630 return _DecodingRow(row, charset)
2631 else:
2632 return None
2634 def _extract_error_code(self, exception):
2635 raise NotImplementedError()
2637 def _get_default_schema_name(self, connection):
2638 return connection.exec_driver_sql("SELECT DATABASE()").scalar()
2640 def has_table(self, connection, table_name, schema=None):
2641 self._ensure_has_table_connection(connection)
2643 if schema is None:
2644 schema = self.default_schema_name
2646 rs = connection.execute(
2647 text(
2648 "SELECT COUNT(*) FROM information_schema.tables WHERE "
2649 "table_schema = :table_schema AND "
2650 "table_name = :table_name"
2651 ).bindparams(
2652 sql.bindparam("table_schema", type_=Unicode),
2653 sql.bindparam("table_name", type_=Unicode),
2654 ),
2655 {
2656 "table_schema": util.text_type(schema),
2657 "table_name": util.text_type(table_name),
2658 },
2659 )
2660 return bool(rs.scalar())
2662 def has_sequence(self, connection, sequence_name, schema=None):
2663 if not self.supports_sequences:
2664 self._sequences_not_supported()
2665 if not schema:
2666 schema = self.default_schema_name
2667 # MariaDB implements sequences as a special type of table
2668 #
2669 cursor = connection.execute(
2670 sql.text(
2671 "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
2672 "WHERE TABLE_TYPE='SEQUENCE' and TABLE_NAME=:name AND "
2673 "TABLE_SCHEMA=:schema_name"
2674 ),
2675 dict(
2676 name=util.text_type(sequence_name),
2677 schema_name=util.text_type(schema),
2678 ),
2679 )
2680 return cursor.first() is not None
2682 def _sequences_not_supported(self):
2683 raise NotImplementedError(
2684 "Sequences are supported only by the "
2685 "MariaDB series 10.3 or greater"
2686 )
2688 @reflection.cache
2689 def get_sequence_names(self, connection, schema=None, **kw):
2690 if not self.supports_sequences:
2691 self._sequences_not_supported()
2692 if not schema:
2693 schema = self.default_schema_name
2694 # MariaDB implements sequences as a special type of table
2695 cursor = connection.execute(
2696 sql.text(
2697 "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
2698 "WHERE TABLE_TYPE='SEQUENCE' and TABLE_SCHEMA=:schema_name"
2699 ),
2700 dict(schema_name=schema),
2701 )
2702 return [
2703 row[0]
2704 for row in self._compat_fetchall(
2705 cursor, charset=self._connection_charset
2706 )
2707 ]
2709 def initialize(self, connection):
2710 # this is driver-based, does not need server version info
2711 # and is fairly critical for even basic SQL operations
2712 self._connection_charset = self._detect_charset(connection)
2714 # call super().initialize() because we need to have
2715 # server_version_info set up. in 1.4 under python 2 only this does the
2716 # "check unicode returns" thing, which is the one area that some
2717 # SQL gets compiled within initialize() currently
2718 default.DefaultDialect.initialize(self, connection)
2720 self._detect_sql_mode(connection)
2721 self._detect_ansiquotes(connection) # depends on sql mode
2722 self._detect_casing(connection)
2723 if self._server_ansiquotes:
2724 # if ansiquotes == True, build a new IdentifierPreparer
2725 # with the new setting
2726 self.identifier_preparer = self.preparer(
2727 self, server_ansiquotes=self._server_ansiquotes
2728 )
2730 self.supports_sequences = (
2731 self.is_mariadb and self.server_version_info >= (10, 3)
2732 )
2734 self.supports_for_update_of = (
2735 self._is_mysql and self.server_version_info >= (8,)
2736 )
2738 self._needs_correct_for_88718_96365 = (
2739 not self.is_mariadb and self.server_version_info >= (8,)
2740 )
2742 self._warn_for_known_db_issues()
2744 def _warn_for_known_db_issues(self):
2745 if self.is_mariadb:
2746 mdb_version = self._mariadb_normalized_version_info
2747 if mdb_version > (10, 2) and mdb_version < (10, 2, 9):
2748 util.warn(
2749 "MariaDB %r before 10.2.9 has known issues regarding "
2750 "CHECK constraints, which impact handling of NULL values "
2751 "with SQLAlchemy's boolean datatype (MDEV-13596). An "
2752 "additional issue prevents proper migrations of columns "
2753 "with CHECK constraints (MDEV-11114). Please upgrade to "
2754 "MariaDB 10.2.9 or greater, or use the MariaDB 10.1 "
2755 "series, to avoid these issues." % (mdb_version,)
2756 )
2758 @property
2759 def _support_float_cast(self):
2760 if not self.server_version_info:
2761 return False
2762 elif self.is_mariadb:
2763 # ref https://mariadb.com/kb/en/mariadb-1045-release-notes/
2764 return self.server_version_info >= (10, 4, 5)
2765 else:
2766 # ref https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html#mysqld-8-0-17-feature # noqa
2767 return self.server_version_info >= (8, 0, 17)
2769 @property
2770 def _is_mariadb(self):
2771 return self.is_mariadb
2773 @property
2774 def _is_mysql(self):
2775 return not self.is_mariadb
2777 @property
2778 def _is_mariadb_102(self):
2779 return self.is_mariadb and self._mariadb_normalized_version_info > (
2780 10,
2781 2,
2782 )
2784 @reflection.cache
2785 def get_schema_names(self, connection, **kw):
2786 rp = connection.exec_driver_sql("SHOW schemas")
2787 return [r[0] for r in rp]
2789 @reflection.cache
2790 def get_table_names(self, connection, schema=None, **kw):
2791 """Return a Unicode SHOW TABLES from a given schema."""
2792 if schema is not None:
2793 current_schema = schema
2794 else:
2795 current_schema = self.default_schema_name
2797 charset = self._connection_charset
2799 rp = connection.exec_driver_sql(
2800 "SHOW FULL TABLES FROM %s"
2801 % self.identifier_preparer.quote_identifier(current_schema)
2802 )
2804 return [
2805 row[0]
2806 for row in self._compat_fetchall(rp, charset=charset)
2807 if row[1] == "BASE TABLE"
2808 ]
2810 @reflection.cache
2811 def get_view_names(self, connection, schema=None, **kw):
2812 if schema is None:
2813 schema = self.default_schema_name
2814 charset = self._connection_charset
2815 rp = connection.exec_driver_sql(
2816 "SHOW FULL TABLES FROM %s"
2817 % self.identifier_preparer.quote_identifier(schema)
2818 )
2819 return [
2820 row[0]
2821 for row in self._compat_fetchall(rp, charset=charset)
2822 if row[1] in ("VIEW", "SYSTEM VIEW")
2823 ]
2825 @reflection.cache
2826 def get_table_options(self, connection, table_name, schema=None, **kw):
2828 parsed_state = self._parsed_state_or_create(
2829 connection, table_name, schema, **kw
2830 )
2831 return parsed_state.table_options
2833 @reflection.cache
2834 def get_columns(self, connection, table_name, schema=None, **kw):
2835 parsed_state = self._parsed_state_or_create(
2836 connection, table_name, schema, **kw
2837 )
2838 return parsed_state.columns
2840 @reflection.cache
2841 def get_pk_constraint(self, connection, table_name, schema=None, **kw):
2842 parsed_state = self._parsed_state_or_create(
2843 connection, table_name, schema, **kw
2844 )
2845 for key in parsed_state.keys:
2846 if key["type"] == "PRIMARY":
2847 # There can be only one.
2848 cols = [s[0] for s in key["columns"]]
2849 return {"constrained_columns": cols, "name": None}
2850 return {"constrained_columns": [], "name": None}
2852 @reflection.cache
2853 def get_foreign_keys(self, connection, table_name, schema=None, **kw):
2855 parsed_state = self._parsed_state_or_create(
2856 connection, table_name, schema, **kw
2857 )
2858 default_schema = None
2860 fkeys = []
2862 for spec in parsed_state.fk_constraints:
2863 ref_name = spec["table"][-1]
2864 ref_schema = len(spec["table"]) > 1 and spec["table"][-2] or schema
2866 if not ref_schema:
2867 if default_schema is None:
2868 default_schema = connection.dialect.default_schema_name
2869 if schema == default_schema:
2870 ref_schema = schema
2872 loc_names = spec["local"]
2873 ref_names = spec["foreign"]
2875 con_kw = {}
2876 for opt in ("onupdate", "ondelete"):
2877 if spec.get(opt, False) not in ("NO ACTION", None):
2878 con_kw[opt] = spec[opt]
2880 fkey_d = {
2881 "name": spec["name"],
2882 "constrained_columns": loc_names,
2883 "referred_schema": ref_schema,
2884 "referred_table": ref_name,
2885 "referred_columns": ref_names,
2886 "options": con_kw,
2887 }
2888 fkeys.append(fkey_d)
2890 if self._needs_correct_for_88718_96365:
2891 self._correct_for_mysql_bugs_88718_96365(fkeys, connection)
2893 return fkeys
2895 def _correct_for_mysql_bugs_88718_96365(self, fkeys, connection):
2896 # Foreign key is always in lower case (MySQL 8.0)
2897 # https://bugs.mysql.com/bug.php?id=88718
2898 # issue #4344 for SQLAlchemy
2900 # table name also for MySQL 8.0
2901 # https://bugs.mysql.com/bug.php?id=96365
2902 # issue #4751 for SQLAlchemy
2904 # for lower_case_table_names=2, information_schema.columns
2905 # preserves the original table/schema casing, but SHOW CREATE
2906 # TABLE does not. this problem is not in lower_case_table_names=1,
2907 # but use case-insensitive matching for these two modes in any case.
2909 if self._casing in (1, 2):
2911 def lower(s):
2912 return s.lower()
2914 else:
2915 # if on case sensitive, there can be two tables referenced
2916 # with the same name different casing, so we need to use
2917 # case-sensitive matching.
2918 def lower(s):
2919 return s
2921 default_schema_name = connection.dialect.default_schema_name
2922 col_tuples = [
2923 (
2924 lower(rec["referred_schema"] or default_schema_name),
2925 lower(rec["referred_table"]),
2926 col_name,
2927 )
2928 for rec in fkeys
2929 for col_name in rec["referred_columns"]
2930 ]
2932 if col_tuples:
2934 correct_for_wrong_fk_case = connection.execute(
2935 sql.text(
2936 """
2937 select table_schema, table_name, column_name
2938 from information_schema.columns
2939 where (table_schema, table_name, lower(column_name)) in
2940 :table_data;
2941 """
2942 ).bindparams(sql.bindparam("table_data", expanding=True)),
2943 dict(table_data=col_tuples),
2944 )
2946 # in casing=0, table name and schema name come back in their
2947 # exact case.
2948 # in casing=1, table name and schema name come back in lower
2949 # case.
2950 # in casing=2, table name and schema name come back from the
2951 # information_schema.columns view in the case
2952 # that was used in CREATE DATABASE and CREATE TABLE, but
2953 # SHOW CREATE TABLE converts them to *lower case*, therefore
2954 # not matching. So for this case, case-insensitive lookup
2955 # is necessary
2956 d = defaultdict(dict)
2957 for schema, tname, cname in correct_for_wrong_fk_case:
2958 d[(lower(schema), lower(tname))]["SCHEMANAME"] = schema
2959 d[(lower(schema), lower(tname))]["TABLENAME"] = tname
2960 d[(lower(schema), lower(tname))][cname.lower()] = cname
2962 for fkey in fkeys:
2963 rec = d[
2964 (
2965 lower(fkey["referred_schema"] or default_schema_name),
2966 lower(fkey["referred_table"]),
2967 )
2968 ]
2970 fkey["referred_table"] = rec["TABLENAME"]
2971 if fkey["referred_schema"] is not None:
2972 fkey["referred_schema"] = rec["SCHEMANAME"]
2974 fkey["referred_columns"] = [
2975 rec[col.lower()] for col in fkey["referred_columns"]
2976 ]
2978 @reflection.cache
2979 def get_check_constraints(self, connection, table_name, schema=None, **kw):
2980 parsed_state = self._parsed_state_or_create(
2981 connection, table_name, schema, **kw
2982 )
2984 return [
2985 {"name": spec["name"], "sqltext": spec["sqltext"]}
2986 for spec in parsed_state.ck_constraints
2987 ]
2989 @reflection.cache
2990 def get_table_comment(self, connection, table_name, schema=None, **kw):
2991 parsed_state = self._parsed_state_or_create(
2992 connection, table_name, schema, **kw
2993 )
2994 return {
2995 "text": parsed_state.table_options.get(
2996 "%s_comment" % self.name, None
2997 )
2998 }
3000 @reflection.cache
3001 def get_indexes(self, connection, table_name, schema=None, **kw):
3003 parsed_state = self._parsed_state_or_create(
3004 connection, table_name, schema, **kw
3005 )
3007 indexes = []
3009 for spec in parsed_state.keys:
3010 dialect_options = {}
3011 unique = False
3012 flavor = spec["type"]
3013 if flavor == "PRIMARY":
3014 continue
3015 if flavor == "UNIQUE":
3016 unique = True
3017 elif flavor in ("FULLTEXT", "SPATIAL"):
3018 dialect_options["%s_prefix" % self.name] = flavor
3019 elif flavor is None:
3020 pass
3021 else:
3022 self.logger.info(
3023 "Converting unknown KEY type %s to a plain KEY", flavor
3024 )
3025 pass
3027 if spec["parser"]:
3028 dialect_options["%s_with_parser" % (self.name)] = spec[
3029 "parser"
3030 ]
3032 index_d = {}
3034 index_d["name"] = spec["name"]
3035 index_d["column_names"] = [s[0] for s in spec["columns"]]
3036 mysql_length = {
3037 s[0]: s[1] for s in spec["columns"] if s[1] is not None
3038 }
3039 if mysql_length:
3040 dialect_options["%s_length" % self.name] = mysql_length
3042 index_d["unique"] = unique
3043 if flavor:
3044 index_d["type"] = flavor
3046 if dialect_options:
3047 index_d["dialect_options"] = dialect_options
3049 indexes.append(index_d)
3050 return indexes
3052 @reflection.cache
3053 def get_unique_constraints(
3054 self, connection, table_name, schema=None, **kw
3055 ):
3056 parsed_state = self._parsed_state_or_create(
3057 connection, table_name, schema, **kw
3058 )
3060 return [
3061 {
3062 "name": key["name"],
3063 "column_names": [col[0] for col in key["columns"]],
3064 "duplicates_index": key["name"],
3065 }
3066 for key in parsed_state.keys
3067 if key["type"] == "UNIQUE"
3068 ]
3070 @reflection.cache
3071 def get_view_definition(self, connection, view_name, schema=None, **kw):
3073 charset = self._connection_charset
3074 full_name = ".".join(
3075 self.identifier_preparer._quote_free_identifiers(schema, view_name)
3076 )
3077 sql = self._show_create_table(
3078 connection, None, charset, full_name=full_name
3079 )
3080 return sql
3082 def _parsed_state_or_create(
3083 self, connection, table_name, schema=None, **kw
3084 ):
3085 return self._setup_parser(
3086 connection,
3087 table_name,
3088 schema,
3089 info_cache=kw.get("info_cache", None),
3090 )
3092 @util.memoized_property
3093 def _tabledef_parser(self):
3094 """return the MySQLTableDefinitionParser, generate if needed.
3096 The deferred creation ensures that the dialect has
3097 retrieved server version information first.
3099 """
3100 preparer = self.identifier_preparer
3101 return _reflection.MySQLTableDefinitionParser(self, preparer)
3103 @reflection.cache
3104 def _setup_parser(self, connection, table_name, schema=None, **kw):
3105 charset = self._connection_charset
3106 parser = self._tabledef_parser
3107 full_name = ".".join(
3108 self.identifier_preparer._quote_free_identifiers(
3109 schema, table_name
3110 )
3111 )
3112 sql = self._show_create_table(
3113 connection, None, charset, full_name=full_name
3114 )
3115 if parser._check_view(sql):
3116 # Adapt views to something table-like.
3117 columns = self._describe_table(
3118 connection, None, charset, full_name=full_name
3119 )
3120 sql = parser._describe_to_create(table_name, columns)
3121 return parser.parse(sql, charset)
3123 def _fetch_setting(self, connection, setting_name):
3124 charset = self._connection_charset
3126 if self.server_version_info and self.server_version_info < (5, 6):
3127 sql = "SHOW VARIABLES LIKE '%s'" % setting_name
3128 fetch_col = 1
3129 else:
3130 sql = "SELECT @@%s" % setting_name
3131 fetch_col = 0
3133 show_var = connection.exec_driver_sql(sql)
3134 row = self._compat_first(show_var, charset=charset)
3135 if not row:
3136 return None
3137 else:
3138 return row[fetch_col]
3140 def _detect_charset(self, connection):
3141 raise NotImplementedError()
3143 def _detect_casing(self, connection):
3144 """Sniff out identifier case sensitivity.
3146 Cached per-connection. This value can not change without a server
3147 restart.
3149 """
3150 # https://dev.mysql.com/doc/refman/en/identifier-case-sensitivity.html
3152 setting = self._fetch_setting(connection, "lower_case_table_names")
3153 if setting is None:
3154 cs = 0
3155 else:
3156 # 4.0.15 returns OFF or ON according to [ticket:489]
3157 # 3.23 doesn't, 4.0.27 doesn't..
3158 if setting == "OFF":
3159 cs = 0
3160 elif setting == "ON":
3161 cs = 1
3162 else:
3163 cs = int(setting)
3164 self._casing = cs
3165 return cs
3167 def _detect_collations(self, connection):
3168 """Pull the active COLLATIONS list from the server.
3170 Cached per-connection.
3171 """
3173 collations = {}
3174 charset = self._connection_charset
3175 rs = connection.exec_driver_sql("SHOW COLLATION")
3176 for row in self._compat_fetchall(rs, charset):
3177 collations[row[0]] = row[1]
3178 return collations
3180 def _detect_sql_mode(self, connection):
3181 setting = self._fetch_setting(connection, "sql_mode")
3183 if setting is None:
3184 util.warn(
3185 "Could not retrieve SQL_MODE; please ensure the "
3186 "MySQL user has permissions to SHOW VARIABLES"
3187 )
3188 self._sql_mode = ""
3189 else:
3190 self._sql_mode = setting or ""
3192 def _detect_ansiquotes(self, connection):
3193 """Detect and adjust for the ANSI_QUOTES sql mode."""
3195 mode = self._sql_mode
3196 if not mode:
3197 mode = ""
3198 elif mode.isdigit():
3199 mode_no = int(mode)
3200 mode = (mode_no | 4 == mode_no) and "ANSI_QUOTES" or ""
3202 self._server_ansiquotes = "ANSI_QUOTES" in mode
3204 # as of MySQL 5.0.1
3205 self._backslash_escapes = "NO_BACKSLASH_ESCAPES" not in mode
3207 def _show_create_table(
3208 self, connection, table, charset=None, full_name=None
3209 ):
3210 """Run SHOW CREATE TABLE for a ``Table``."""
3212 if full_name is None:
3213 full_name = self.identifier_preparer.format_table(table)
3214 st = "SHOW CREATE TABLE %s" % full_name
3216 rp = None
3217 try:
3218 rp = connection.execution_options(
3219 skip_user_error_events=True
3220 ).exec_driver_sql(st)
3221 except exc.DBAPIError as e:
3222 if self._extract_error_code(e.orig) == 1146:
3223 util.raise_(exc.NoSuchTableError(full_name), replace_context=e)
3224 else:
3225 raise
3226 row = self._compat_first(rp, charset=charset)
3227 if not row:
3228 raise exc.NoSuchTableError(full_name)
3229 return row[1].strip()
3231 def _describe_table(self, connection, table, charset=None, full_name=None):
3232 """Run DESCRIBE for a ``Table`` and return processed rows."""
3234 if full_name is None:
3235 full_name = self.identifier_preparer.format_table(table)
3236 st = "DESCRIBE %s" % full_name
3238 rp, rows = None, None
3239 try:
3240 try:
3241 rp = connection.execution_options(
3242 skip_user_error_events=True
3243 ).exec_driver_sql(st)
3244 except exc.DBAPIError as e:
3245 code = self._extract_error_code(e.orig)
3246 if code == 1146:
3247 util.raise_(
3248 exc.NoSuchTableError(full_name), replace_context=e
3249 )
3250 elif code == 1356:
3251 util.raise_(
3252 exc.UnreflectableTableError(
3253 "Table or view named %s could not be "
3254 "reflected: %s" % (full_name, e)
3255 ),
3256 replace_context=e,
3257 )
3258 else:
3259 raise
3260 rows = self._compat_fetchall(rp, charset=charset)
3261 finally:
3262 if rp:
3263 rp.close()
3264 return rows
3267class _DecodingRow(object):
3268 """Return unicode-decoded values based on type inspection.
3270 Smooth over data type issues (esp. with alpha driver versions) and
3271 normalize strings as Unicode regardless of user-configured driver
3272 encoding settings.
3274 """
3276 # Some MySQL-python versions can return some columns as
3277 # sets.Set(['value']) (seriously) but thankfully that doesn't
3278 # seem to come up in DDL queries.
3280 _encoding_compat = {
3281 "koi8r": "koi8_r",
3282 "koi8u": "koi8_u",
3283 "utf16": "utf-16-be", # MySQL's uft16 is always bigendian
3284 "utf8mb4": "utf8", # real utf8
3285 "utf8mb3": "utf8", # real utf8; saw this happen on CI but I cannot
3286 # reproduce, possibly mariadb10.6 related
3287 "eucjpms": "ujis",
3288 }
3290 def __init__(self, rowproxy, charset):
3291 self.rowproxy = rowproxy
3292 self.charset = self._encoding_compat.get(charset, charset)
3294 def __getitem__(self, index):
3295 item = self.rowproxy[index]
3296 if isinstance(item, _array):
3297 item = item.tostring()
3299 if self.charset and isinstance(item, util.binary_type):
3300 return item.decode(self.charset)
3301 else:
3302 return item
3304 def __getattr__(self, attr):
3305 item = getattr(self.rowproxy, attr)
3306 if isinstance(item, _array):
3307 item = item.tostring()
3308 if self.charset and isinstance(item, util.binary_type):
3309 return item.decode(self.charset)
3310 else:
3311 return item