Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/base.py: 29%
1080 statements
« prev ^ index » next coverage.py v7.2.7, created at 2023-06-07 06:35 +0000
1# mysql/base.py
2# Copyright (C) 2005-2023 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.4, 10.5
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.process(flags, **kw),
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.process(flags, **kw),
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 replacement = binary.modifiers["replacement"]
1687 if flags is None:
1688 return "REGEXP_REPLACE(%s, %s, %s)" % (
1689 self.process(binary.left, **kw),
1690 self.process(binary.right, **kw),
1691 self.process(replacement, **kw),
1692 )
1693 elif self.dialect.is_mariadb:
1694 return "REGEXP_REPLACE(%s, %s, %s)" % (
1695 self.process(binary.left, **kw),
1696 self._mariadb_regexp_flags(flags, binary.right),
1697 self.process(replacement, **kw),
1698 )
1699 else:
1700 return "REGEXP_REPLACE(%s, %s, %s, %s)" % (
1701 self.process(binary.left, **kw),
1702 self.process(binary.right, **kw),
1703 self.process(replacement, **kw),
1704 self.process(flags, **kw),
1705 )
1708class MySQLDDLCompiler(compiler.DDLCompiler):
1709 def get_column_specification(self, column, **kw):
1710 """Builds column DDL."""
1712 colspec = [
1713 self.preparer.format_column(column),
1714 self.dialect.type_compiler.process(
1715 column.type, type_expression=column
1716 ),
1717 ]
1719 if column.computed is not None:
1720 colspec.append(self.process(column.computed))
1722 is_timestamp = isinstance(
1723 column.type._unwrapped_dialect_impl(self.dialect),
1724 sqltypes.TIMESTAMP,
1725 )
1727 if not column.nullable:
1728 colspec.append("NOT NULL")
1730 # see: https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql_timestamp_null # noqa
1731 elif column.nullable and is_timestamp:
1732 colspec.append("NULL")
1734 comment = column.comment
1735 if comment is not None:
1736 literal = self.sql_compiler.render_literal_value(
1737 comment, sqltypes.String()
1738 )
1739 colspec.append("COMMENT " + literal)
1741 if (
1742 column.table is not None
1743 and column is column.table._autoincrement_column
1744 and (
1745 column.server_default is None
1746 or isinstance(column.server_default, sa_schema.Identity)
1747 )
1748 and not (
1749 self.dialect.supports_sequences
1750 and isinstance(column.default, sa_schema.Sequence)
1751 and not column.default.optional
1752 )
1753 ):
1754 colspec.append("AUTO_INCREMENT")
1755 else:
1756 default = self.get_column_default_string(column)
1757 if default is not None:
1758 colspec.append("DEFAULT " + default)
1759 return " ".join(colspec)
1761 def post_create_table(self, table):
1762 """Build table-level CREATE options like ENGINE and COLLATE."""
1764 table_opts = []
1766 opts = dict(
1767 (k[len(self.dialect.name) + 1 :].upper(), v)
1768 for k, v in table.kwargs.items()
1769 if k.startswith("%s_" % self.dialect.name)
1770 )
1772 if table.comment is not None:
1773 opts["COMMENT"] = table.comment
1775 partition_options = [
1776 "PARTITION_BY",
1777 "PARTITIONS",
1778 "SUBPARTITIONS",
1779 "SUBPARTITION_BY",
1780 ]
1782 nonpart_options = set(opts).difference(partition_options)
1783 part_options = set(opts).intersection(partition_options)
1785 for opt in topological.sort(
1786 [
1787 ("DEFAULT_CHARSET", "COLLATE"),
1788 ("DEFAULT_CHARACTER_SET", "COLLATE"),
1789 ("CHARSET", "COLLATE"),
1790 ("CHARACTER_SET", "COLLATE"),
1791 ],
1792 nonpart_options,
1793 ):
1794 arg = opts[opt]
1795 if opt in _reflection._options_of_type_string:
1797 arg = self.sql_compiler.render_literal_value(
1798 arg, sqltypes.String()
1799 )
1801 if opt in (
1802 "DATA_DIRECTORY",
1803 "INDEX_DIRECTORY",
1804 "DEFAULT_CHARACTER_SET",
1805 "CHARACTER_SET",
1806 "DEFAULT_CHARSET",
1807 "DEFAULT_COLLATE",
1808 ):
1809 opt = opt.replace("_", " ")
1811 joiner = "="
1812 if opt in (
1813 "TABLESPACE",
1814 "DEFAULT CHARACTER SET",
1815 "CHARACTER SET",
1816 "COLLATE",
1817 ):
1818 joiner = " "
1820 table_opts.append(joiner.join((opt, arg)))
1822 for opt in topological.sort(
1823 [
1824 ("PARTITION_BY", "PARTITIONS"),
1825 ("PARTITION_BY", "SUBPARTITION_BY"),
1826 ("PARTITION_BY", "SUBPARTITIONS"),
1827 ("PARTITIONS", "SUBPARTITIONS"),
1828 ("PARTITIONS", "SUBPARTITION_BY"),
1829 ("SUBPARTITION_BY", "SUBPARTITIONS"),
1830 ],
1831 part_options,
1832 ):
1833 arg = opts[opt]
1834 if opt in _reflection._options_of_type_string:
1835 arg = self.sql_compiler.render_literal_value(
1836 arg, sqltypes.String()
1837 )
1839 opt = opt.replace("_", " ")
1840 joiner = " "
1842 table_opts.append(joiner.join((opt, arg)))
1844 return " ".join(table_opts)
1846 def visit_create_index(self, create, **kw):
1847 index = create.element
1848 self._verify_index_table(index)
1849 preparer = self.preparer
1850 table = preparer.format_table(index.table)
1852 columns = [
1853 self.sql_compiler.process(
1854 elements.Grouping(expr)
1855 if (
1856 isinstance(expr, elements.BinaryExpression)
1857 or (
1858 isinstance(expr, elements.UnaryExpression)
1859 and expr.modifier
1860 not in (operators.desc_op, operators.asc_op)
1861 )
1862 or isinstance(expr, functions.FunctionElement)
1863 )
1864 else expr,
1865 include_table=False,
1866 literal_binds=True,
1867 )
1868 for expr in index.expressions
1869 ]
1871 name = self._prepared_index_name(index)
1873 text = "CREATE "
1874 if index.unique:
1875 text += "UNIQUE "
1877 index_prefix = index.kwargs.get("%s_prefix" % self.dialect.name, None)
1878 if index_prefix:
1879 text += index_prefix + " "
1881 text += "INDEX "
1882 if create.if_not_exists:
1883 text += "IF NOT EXISTS "
1884 text += "%s ON %s " % (name, table)
1886 length = index.dialect_options[self.dialect.name]["length"]
1887 if length is not None:
1889 if isinstance(length, dict):
1890 # length value can be a (column_name --> integer value)
1891 # mapping specifying the prefix length for each column of the
1892 # index
1893 columns = ", ".join(
1894 "%s(%d)" % (expr, length[col.name])
1895 if col.name in length
1896 else (
1897 "%s(%d)" % (expr, length[expr])
1898 if expr in length
1899 else "%s" % expr
1900 )
1901 for col, expr in zip(index.expressions, columns)
1902 )
1903 else:
1904 # or can be an integer value specifying the same
1905 # prefix length for all columns of the index
1906 columns = ", ".join(
1907 "%s(%d)" % (col, length) for col in columns
1908 )
1909 else:
1910 columns = ", ".join(columns)
1911 text += "(%s)" % columns
1913 parser = index.dialect_options["mysql"]["with_parser"]
1914 if parser is not None:
1915 text += " WITH PARSER %s" % (parser,)
1917 using = index.dialect_options["mysql"]["using"]
1918 if using is not None:
1919 text += " USING %s" % (preparer.quote(using))
1921 return text
1923 def visit_primary_key_constraint(self, constraint):
1924 text = super(MySQLDDLCompiler, self).visit_primary_key_constraint(
1925 constraint
1926 )
1927 using = constraint.dialect_options["mysql"]["using"]
1928 if using:
1929 text += " USING %s" % (self.preparer.quote(using))
1930 return text
1932 def visit_drop_index(self, drop):
1933 index = drop.element
1934 text = "\nDROP INDEX "
1935 if drop.if_exists:
1936 text += "IF EXISTS "
1938 return text + "%s ON %s" % (
1939 self._prepared_index_name(index, include_schema=False),
1940 self.preparer.format_table(index.table),
1941 )
1943 def visit_drop_constraint(self, drop):
1944 constraint = drop.element
1945 if isinstance(constraint, sa_schema.ForeignKeyConstraint):
1946 qual = "FOREIGN KEY "
1947 const = self.preparer.format_constraint(constraint)
1948 elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
1949 qual = "PRIMARY KEY "
1950 const = ""
1951 elif isinstance(constraint, sa_schema.UniqueConstraint):
1952 qual = "INDEX "
1953 const = self.preparer.format_constraint(constraint)
1954 elif isinstance(constraint, sa_schema.CheckConstraint):
1955 if self.dialect.is_mariadb:
1956 qual = "CONSTRAINT "
1957 else:
1958 qual = "CHECK "
1959 const = self.preparer.format_constraint(constraint)
1960 else:
1961 qual = ""
1962 const = self.preparer.format_constraint(constraint)
1963 return "ALTER TABLE %s DROP %s%s" % (
1964 self.preparer.format_table(constraint.table),
1965 qual,
1966 const,
1967 )
1969 def define_constraint_match(self, constraint):
1970 if constraint.match is not None:
1971 raise exc.CompileError(
1972 "MySQL ignores the 'MATCH' keyword while at the same time "
1973 "causes ON UPDATE/ON DELETE clauses to be ignored."
1974 )
1975 return ""
1977 def visit_set_table_comment(self, create):
1978 return "ALTER TABLE %s COMMENT %s" % (
1979 self.preparer.format_table(create.element),
1980 self.sql_compiler.render_literal_value(
1981 create.element.comment, sqltypes.String()
1982 ),
1983 )
1985 def visit_drop_table_comment(self, create):
1986 return "ALTER TABLE %s COMMENT ''" % (
1987 self.preparer.format_table(create.element)
1988 )
1990 def visit_set_column_comment(self, create):
1991 return "ALTER TABLE %s CHANGE %s %s" % (
1992 self.preparer.format_table(create.element.table),
1993 self.preparer.format_column(create.element),
1994 self.get_column_specification(create.element),
1995 )
1998class MySQLTypeCompiler(compiler.GenericTypeCompiler):
1999 def _extend_numeric(self, type_, spec):
2000 "Extend a numeric-type declaration with MySQL specific extensions."
2002 if not self._mysql_type(type_):
2003 return spec
2005 if type_.unsigned:
2006 spec += " UNSIGNED"
2007 if type_.zerofill:
2008 spec += " ZEROFILL"
2009 return spec
2011 def _extend_string(self, type_, defaults, spec):
2012 """Extend a string-type declaration with standard SQL CHARACTER SET /
2013 COLLATE annotations and MySQL specific extensions.
2015 """
2017 def attr(name):
2018 return getattr(type_, name, defaults.get(name))
2020 if attr("charset"):
2021 charset = "CHARACTER SET %s" % attr("charset")
2022 elif attr("ascii"):
2023 charset = "ASCII"
2024 elif attr("unicode"):
2025 charset = "UNICODE"
2026 else:
2027 charset = None
2029 if attr("collation"):
2030 collation = "COLLATE %s" % type_.collation
2031 elif attr("binary"):
2032 collation = "BINARY"
2033 else:
2034 collation = None
2036 if attr("national"):
2037 # NATIONAL (aka NCHAR/NVARCHAR) trumps charsets.
2038 return " ".join(
2039 [c for c in ("NATIONAL", spec, collation) if c is not None]
2040 )
2041 return " ".join(
2042 [c for c in (spec, charset, collation) if c is not None]
2043 )
2045 def _mysql_type(self, type_):
2046 return isinstance(type_, (_StringType, _NumericType))
2048 def visit_NUMERIC(self, type_, **kw):
2049 if type_.precision is None:
2050 return self._extend_numeric(type_, "NUMERIC")
2051 elif type_.scale is None:
2052 return self._extend_numeric(
2053 type_,
2054 "NUMERIC(%(precision)s)" % {"precision": type_.precision},
2055 )
2056 else:
2057 return self._extend_numeric(
2058 type_,
2059 "NUMERIC(%(precision)s, %(scale)s)"
2060 % {"precision": type_.precision, "scale": type_.scale},
2061 )
2063 def visit_DECIMAL(self, type_, **kw):
2064 if type_.precision is None:
2065 return self._extend_numeric(type_, "DECIMAL")
2066 elif type_.scale is None:
2067 return self._extend_numeric(
2068 type_,
2069 "DECIMAL(%(precision)s)" % {"precision": type_.precision},
2070 )
2071 else:
2072 return self._extend_numeric(
2073 type_,
2074 "DECIMAL(%(precision)s, %(scale)s)"
2075 % {"precision": type_.precision, "scale": type_.scale},
2076 )
2078 def visit_DOUBLE(self, type_, **kw):
2079 if type_.precision is not None and type_.scale is not None:
2080 return self._extend_numeric(
2081 type_,
2082 "DOUBLE(%(precision)s, %(scale)s)"
2083 % {"precision": type_.precision, "scale": type_.scale},
2084 )
2085 else:
2086 return self._extend_numeric(type_, "DOUBLE")
2088 def visit_REAL(self, type_, **kw):
2089 if type_.precision is not None and type_.scale is not None:
2090 return self._extend_numeric(
2091 type_,
2092 "REAL(%(precision)s, %(scale)s)"
2093 % {"precision": type_.precision, "scale": type_.scale},
2094 )
2095 else:
2096 return self._extend_numeric(type_, "REAL")
2098 def visit_FLOAT(self, type_, **kw):
2099 if (
2100 self._mysql_type(type_)
2101 and type_.scale is not None
2102 and type_.precision is not None
2103 ):
2104 return self._extend_numeric(
2105 type_, "FLOAT(%s, %s)" % (type_.precision, type_.scale)
2106 )
2107 elif type_.precision is not None:
2108 return self._extend_numeric(
2109 type_, "FLOAT(%s)" % (type_.precision,)
2110 )
2111 else:
2112 return self._extend_numeric(type_, "FLOAT")
2114 def visit_INTEGER(self, type_, **kw):
2115 if self._mysql_type(type_) and type_.display_width is not None:
2116 return self._extend_numeric(
2117 type_,
2118 "INTEGER(%(display_width)s)"
2119 % {"display_width": type_.display_width},
2120 )
2121 else:
2122 return self._extend_numeric(type_, "INTEGER")
2124 def visit_BIGINT(self, type_, **kw):
2125 if self._mysql_type(type_) and type_.display_width is not None:
2126 return self._extend_numeric(
2127 type_,
2128 "BIGINT(%(display_width)s)"
2129 % {"display_width": type_.display_width},
2130 )
2131 else:
2132 return self._extend_numeric(type_, "BIGINT")
2134 def visit_MEDIUMINT(self, type_, **kw):
2135 if self._mysql_type(type_) and type_.display_width is not None:
2136 return self._extend_numeric(
2137 type_,
2138 "MEDIUMINT(%(display_width)s)"
2139 % {"display_width": type_.display_width},
2140 )
2141 else:
2142 return self._extend_numeric(type_, "MEDIUMINT")
2144 def visit_TINYINT(self, type_, **kw):
2145 if self._mysql_type(type_) and type_.display_width is not None:
2146 return self._extend_numeric(
2147 type_, "TINYINT(%s)" % type_.display_width
2148 )
2149 else:
2150 return self._extend_numeric(type_, "TINYINT")
2152 def visit_SMALLINT(self, type_, **kw):
2153 if self._mysql_type(type_) and type_.display_width is not None:
2154 return self._extend_numeric(
2155 type_,
2156 "SMALLINT(%(display_width)s)"
2157 % {"display_width": type_.display_width},
2158 )
2159 else:
2160 return self._extend_numeric(type_, "SMALLINT")
2162 def visit_BIT(self, type_, **kw):
2163 if type_.length is not None:
2164 return "BIT(%s)" % type_.length
2165 else:
2166 return "BIT"
2168 def visit_DATETIME(self, type_, **kw):
2169 if getattr(type_, "fsp", None):
2170 return "DATETIME(%d)" % type_.fsp
2171 else:
2172 return "DATETIME"
2174 def visit_DATE(self, type_, **kw):
2175 return "DATE"
2177 def visit_TIME(self, type_, **kw):
2178 if getattr(type_, "fsp", None):
2179 return "TIME(%d)" % type_.fsp
2180 else:
2181 return "TIME"
2183 def visit_TIMESTAMP(self, type_, **kw):
2184 if getattr(type_, "fsp", None):
2185 return "TIMESTAMP(%d)" % type_.fsp
2186 else:
2187 return "TIMESTAMP"
2189 def visit_YEAR(self, type_, **kw):
2190 if type_.display_width is None:
2191 return "YEAR"
2192 else:
2193 return "YEAR(%s)" % type_.display_width
2195 def visit_TEXT(self, type_, **kw):
2196 if type_.length:
2197 return self._extend_string(type_, {}, "TEXT(%d)" % type_.length)
2198 else:
2199 return self._extend_string(type_, {}, "TEXT")
2201 def visit_TINYTEXT(self, type_, **kw):
2202 return self._extend_string(type_, {}, "TINYTEXT")
2204 def visit_MEDIUMTEXT(self, type_, **kw):
2205 return self._extend_string(type_, {}, "MEDIUMTEXT")
2207 def visit_LONGTEXT(self, type_, **kw):
2208 return self._extend_string(type_, {}, "LONGTEXT")
2210 def visit_VARCHAR(self, type_, **kw):
2211 if type_.length:
2212 return self._extend_string(type_, {}, "VARCHAR(%d)" % type_.length)
2213 else:
2214 raise exc.CompileError(
2215 "VARCHAR requires a length on dialect %s" % self.dialect.name
2216 )
2218 def visit_CHAR(self, type_, **kw):
2219 if type_.length:
2220 return self._extend_string(
2221 type_, {}, "CHAR(%(length)s)" % {"length": type_.length}
2222 )
2223 else:
2224 return self._extend_string(type_, {}, "CHAR")
2226 def visit_NVARCHAR(self, type_, **kw):
2227 # We'll actually generate the equiv. "NATIONAL VARCHAR" instead
2228 # of "NVARCHAR".
2229 if type_.length:
2230 return self._extend_string(
2231 type_,
2232 {"national": True},
2233 "VARCHAR(%(length)s)" % {"length": type_.length},
2234 )
2235 else:
2236 raise exc.CompileError(
2237 "NVARCHAR requires a length on dialect %s" % self.dialect.name
2238 )
2240 def visit_NCHAR(self, type_, **kw):
2241 # We'll actually generate the equiv.
2242 # "NATIONAL CHAR" instead of "NCHAR".
2243 if type_.length:
2244 return self._extend_string(
2245 type_,
2246 {"national": True},
2247 "CHAR(%(length)s)" % {"length": type_.length},
2248 )
2249 else:
2250 return self._extend_string(type_, {"national": True}, "CHAR")
2252 def visit_VARBINARY(self, type_, **kw):
2253 return "VARBINARY(%d)" % type_.length
2255 def visit_JSON(self, type_, **kw):
2256 return "JSON"
2258 def visit_large_binary(self, type_, **kw):
2259 return self.visit_BLOB(type_)
2261 def visit_enum(self, type_, **kw):
2262 if not type_.native_enum:
2263 return super(MySQLTypeCompiler, self).visit_enum(type_)
2264 else:
2265 return self._visit_enumerated_values("ENUM", type_, type_.enums)
2267 def visit_BLOB(self, type_, **kw):
2268 if type_.length:
2269 return "BLOB(%d)" % type_.length
2270 else:
2271 return "BLOB"
2273 def visit_TINYBLOB(self, type_, **kw):
2274 return "TINYBLOB"
2276 def visit_MEDIUMBLOB(self, type_, **kw):
2277 return "MEDIUMBLOB"
2279 def visit_LONGBLOB(self, type_, **kw):
2280 return "LONGBLOB"
2282 def _visit_enumerated_values(self, name, type_, enumerated_values):
2283 quoted_enums = []
2284 for e in enumerated_values:
2285 quoted_enums.append("'%s'" % e.replace("'", "''"))
2286 return self._extend_string(
2287 type_, {}, "%s(%s)" % (name, ",".join(quoted_enums))
2288 )
2290 def visit_ENUM(self, type_, **kw):
2291 return self._visit_enumerated_values("ENUM", type_, type_.enums)
2293 def visit_SET(self, type_, **kw):
2294 return self._visit_enumerated_values("SET", type_, type_.values)
2296 def visit_BOOLEAN(self, type_, **kw):
2297 return "BOOL"
2300class MySQLIdentifierPreparer(compiler.IdentifierPreparer):
2301 reserved_words = RESERVED_WORDS_MYSQL
2303 def __init__(self, dialect, server_ansiquotes=False, **kw):
2304 if not server_ansiquotes:
2305 quote = "`"
2306 else:
2307 quote = '"'
2309 super(MySQLIdentifierPreparer, self).__init__(
2310 dialect, initial_quote=quote, escape_quote=quote
2311 )
2313 def _quote_free_identifiers(self, *ids):
2314 """Unilaterally identifier-quote any number of strings."""
2316 return tuple([self.quote_identifier(i) for i in ids if i is not None])
2319class MariaDBIdentifierPreparer(MySQLIdentifierPreparer):
2320 reserved_words = RESERVED_WORDS_MARIADB
2323@log.class_logger
2324class MySQLDialect(default.DefaultDialect):
2325 """Details of the MySQL dialect.
2326 Not used directly in application code.
2327 """
2329 name = "mysql"
2330 supports_statement_cache = True
2332 supports_alter = True
2334 # MySQL has no true "boolean" type; we
2335 # allow for the "true" and "false" keywords, however
2336 supports_native_boolean = False
2338 # identifiers are 64, however aliases can be 255...
2339 max_identifier_length = 255
2340 max_index_name_length = 64
2341 max_constraint_name_length = 64
2343 supports_native_enum = True
2345 supports_sequences = False # default for MySQL ...
2346 # ... may be updated to True for MariaDB 10.3+ in initialize()
2348 sequences_optional = False
2350 supports_for_update_of = False # default for MySQL ...
2351 # ... may be updated to True for MySQL 8+ in initialize()
2353 # MySQL doesn't support "DEFAULT VALUES" but *does* support
2354 # "VALUES (DEFAULT)"
2355 supports_default_values = False
2356 supports_default_metavalue = True
2358 supports_sane_rowcount = True
2359 supports_sane_multi_rowcount = False
2360 supports_multivalues_insert = True
2362 supports_comments = True
2363 inline_comments = True
2364 default_paramstyle = "format"
2365 colspecs = colspecs
2367 cte_follows_insert = True
2369 statement_compiler = MySQLCompiler
2370 ddl_compiler = MySQLDDLCompiler
2371 type_compiler = MySQLTypeCompiler
2372 ischema_names = ischema_names
2373 preparer = MySQLIdentifierPreparer
2375 is_mariadb = False
2376 _mariadb_normalized_version_info = None
2378 # default SQL compilation settings -
2379 # these are modified upon initialize(),
2380 # i.e. first connect
2381 _backslash_escapes = True
2382 _server_ansiquotes = False
2384 construct_arguments = [
2385 (sa_schema.Table, {"*": None}),
2386 (sql.Update, {"limit": None}),
2387 (sa_schema.PrimaryKeyConstraint, {"using": None}),
2388 (
2389 sa_schema.Index,
2390 {
2391 "using": None,
2392 "length": None,
2393 "prefix": None,
2394 "with_parser": None,
2395 },
2396 ),
2397 ]
2399 def __init__(
2400 self,
2401 isolation_level=None,
2402 json_serializer=None,
2403 json_deserializer=None,
2404 is_mariadb=None,
2405 **kwargs
2406 ):
2407 kwargs.pop("use_ansiquotes", None) # legacy
2408 default.DefaultDialect.__init__(self, **kwargs)
2409 self.isolation_level = isolation_level
2410 self._json_serializer = json_serializer
2411 self._json_deserializer = json_deserializer
2412 self._set_mariadb(is_mariadb, None)
2414 def on_connect(self):
2415 if self.isolation_level is not None:
2417 def connect(conn):
2418 self.set_isolation_level(conn, self.isolation_level)
2420 return connect
2421 else:
2422 return None
2424 _isolation_lookup = set(
2425 [
2426 "SERIALIZABLE",
2427 "READ UNCOMMITTED",
2428 "READ COMMITTED",
2429 "REPEATABLE READ",
2430 ]
2431 )
2433 def set_isolation_level(self, connection, level):
2434 level = level.replace("_", " ")
2436 # adjust for ConnectionFairy being present
2437 # allows attribute set e.g. "connection.autocommit = True"
2438 # to work properly
2439 if hasattr(connection, "dbapi_connection"):
2440 connection = connection.dbapi_connection
2442 self._set_isolation_level(connection, level)
2444 def _set_isolation_level(self, connection, level):
2445 if level not in self._isolation_lookup:
2446 raise exc.ArgumentError(
2447 "Invalid value '%s' for isolation_level. "
2448 "Valid isolation levels for %s are %s"
2449 % (level, self.name, ", ".join(self._isolation_lookup))
2450 )
2451 cursor = connection.cursor()
2452 cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % level)
2453 cursor.execute("COMMIT")
2454 cursor.close()
2456 def get_isolation_level(self, connection):
2457 cursor = connection.cursor()
2458 if self._is_mysql and self.server_version_info >= (5, 7, 20):
2459 cursor.execute("SELECT @@transaction_isolation")
2460 else:
2461 cursor.execute("SELECT @@tx_isolation")
2462 row = cursor.fetchone()
2463 if row is None:
2464 util.warn(
2465 "Could not retrieve transaction isolation level for MySQL "
2466 "connection."
2467 )
2468 raise NotImplementedError()
2469 val = row[0]
2470 cursor.close()
2471 if util.py3k and isinstance(val, bytes):
2472 val = val.decode()
2473 return val.upper().replace("-", " ")
2475 @classmethod
2476 def _is_mariadb_from_url(cls, url):
2477 dbapi = cls.dbapi()
2478 dialect = cls(dbapi=dbapi)
2480 cargs, cparams = dialect.create_connect_args(url)
2481 conn = dialect.connect(*cargs, **cparams)
2482 try:
2483 cursor = conn.cursor()
2484 cursor.execute("SELECT VERSION() LIKE '%MariaDB%'")
2485 val = cursor.fetchone()[0]
2486 except:
2487 raise
2488 else:
2489 return bool(val)
2490 finally:
2491 conn.close()
2493 def _get_server_version_info(self, connection):
2494 # get database server version info explicitly over the wire
2495 # to avoid proxy servers like MaxScale getting in the
2496 # way with their own values, see #4205
2497 dbapi_con = connection.connection
2498 cursor = dbapi_con.cursor()
2499 cursor.execute("SELECT VERSION()")
2500 val = cursor.fetchone()[0]
2501 cursor.close()
2502 if util.py3k and isinstance(val, bytes):
2503 val = val.decode()
2505 return self._parse_server_version(val)
2507 def _parse_server_version(self, val):
2508 version = []
2509 is_mariadb = False
2511 r = re.compile(r"[.\-+]")
2512 tokens = r.split(val)
2513 for token in tokens:
2514 parsed_token = re.match(
2515 r"^(?:(\d+)(?:a|b|c)?|(MariaDB\w*))$", token
2516 )
2517 if not parsed_token:
2518 continue
2519 elif parsed_token.group(2):
2520 self._mariadb_normalized_version_info = tuple(version[-3:])
2521 is_mariadb = True
2522 else:
2523 digit = int(parsed_token.group(1))
2524 version.append(digit)
2526 server_version_info = tuple(version)
2528 self._set_mariadb(server_version_info and is_mariadb, val)
2530 if not is_mariadb:
2531 self._mariadb_normalized_version_info = server_version_info
2533 if server_version_info < (5, 0, 2):
2534 raise NotImplementedError(
2535 "the MySQL/MariaDB dialect supports server "
2536 "version info 5.0.2 and above."
2537 )
2539 # setting it here to help w the test suite
2540 self.server_version_info = server_version_info
2541 return server_version_info
2543 def _set_mariadb(self, is_mariadb, server_version_info):
2544 if is_mariadb is None:
2545 return
2547 if not is_mariadb and self.is_mariadb:
2548 raise exc.InvalidRequestError(
2549 "MySQL version %s is not a MariaDB variant."
2550 % (server_version_info,)
2551 )
2552 if is_mariadb:
2553 self.preparer = MariaDBIdentifierPreparer
2554 # this would have been set by the default dialect already,
2555 # so set it again
2556 self.identifier_preparer = self.preparer(self)
2557 self.is_mariadb = is_mariadb
2559 def do_begin_twophase(self, connection, xid):
2560 connection.execute(sql.text("XA BEGIN :xid"), dict(xid=xid))
2562 def do_prepare_twophase(self, connection, xid):
2563 connection.execute(sql.text("XA END :xid"), dict(xid=xid))
2564 connection.execute(sql.text("XA PREPARE :xid"), dict(xid=xid))
2566 def do_rollback_twophase(
2567 self, connection, xid, is_prepared=True, recover=False
2568 ):
2569 if not is_prepared:
2570 connection.execute(sql.text("XA END :xid"), dict(xid=xid))
2571 connection.execute(sql.text("XA ROLLBACK :xid"), dict(xid=xid))
2573 def do_commit_twophase(
2574 self, connection, xid, is_prepared=True, recover=False
2575 ):
2576 if not is_prepared:
2577 self.do_prepare_twophase(connection, xid)
2578 connection.execute(sql.text("XA COMMIT :xid"), dict(xid=xid))
2580 def do_recover_twophase(self, connection):
2581 resultset = connection.exec_driver_sql("XA RECOVER")
2582 return [row["data"][0 : row["gtrid_length"]] for row in resultset]
2584 def is_disconnect(self, e, connection, cursor):
2585 if isinstance(
2586 e,
2587 (
2588 self.dbapi.OperationalError,
2589 self.dbapi.ProgrammingError,
2590 self.dbapi.InterfaceError,
2591 ),
2592 ) and self._extract_error_code(e) in (
2593 1927,
2594 2006,
2595 2013,
2596 2014,
2597 2045,
2598 2055,
2599 4031,
2600 ):
2601 return True
2602 elif isinstance(
2603 e, (self.dbapi.InterfaceError, self.dbapi.InternalError)
2604 ):
2605 # if underlying connection is closed,
2606 # this is the error you get
2607 return "(0, '')" in str(e)
2608 else:
2609 return False
2611 def _compat_fetchall(self, rp, charset=None):
2612 """Proxy result rows to smooth over MySQL-Python driver
2613 inconsistencies."""
2615 return [_DecodingRow(row, charset) for row in rp.fetchall()]
2617 def _compat_fetchone(self, rp, charset=None):
2618 """Proxy a result row to smooth over MySQL-Python driver
2619 inconsistencies."""
2621 row = rp.fetchone()
2622 if row:
2623 return _DecodingRow(row, charset)
2624 else:
2625 return None
2627 def _compat_first(self, rp, charset=None):
2628 """Proxy a result row to smooth over MySQL-Python driver
2629 inconsistencies."""
2631 row = rp.first()
2632 if row:
2633 return _DecodingRow(row, charset)
2634 else:
2635 return None
2637 def _extract_error_code(self, exception):
2638 raise NotImplementedError()
2640 def _get_default_schema_name(self, connection):
2641 return connection.exec_driver_sql("SELECT DATABASE()").scalar()
2643 def has_table(self, connection, table_name, schema=None):
2644 self._ensure_has_table_connection(connection)
2646 if schema is None:
2647 schema = self.default_schema_name
2649 rs = connection.execute(
2650 text(
2651 "SELECT COUNT(*) FROM information_schema.tables WHERE "
2652 "table_schema = :table_schema AND "
2653 "table_name = :table_name"
2654 ).bindparams(
2655 sql.bindparam("table_schema", type_=Unicode),
2656 sql.bindparam("table_name", type_=Unicode),
2657 ),
2658 {
2659 "table_schema": util.text_type(schema),
2660 "table_name": util.text_type(table_name),
2661 },
2662 )
2663 return bool(rs.scalar())
2665 def has_sequence(self, connection, sequence_name, schema=None):
2666 if not self.supports_sequences:
2667 self._sequences_not_supported()
2668 if not schema:
2669 schema = self.default_schema_name
2670 # MariaDB implements sequences as a special type of table
2671 #
2672 cursor = connection.execute(
2673 sql.text(
2674 "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
2675 "WHERE TABLE_TYPE='SEQUENCE' and TABLE_NAME=:name AND "
2676 "TABLE_SCHEMA=:schema_name"
2677 ),
2678 dict(
2679 name=util.text_type(sequence_name),
2680 schema_name=util.text_type(schema),
2681 ),
2682 )
2683 return cursor.first() is not None
2685 def _sequences_not_supported(self):
2686 raise NotImplementedError(
2687 "Sequences are supported only by the "
2688 "MariaDB series 10.3 or greater"
2689 )
2691 @reflection.cache
2692 def get_sequence_names(self, connection, schema=None, **kw):
2693 if not self.supports_sequences:
2694 self._sequences_not_supported()
2695 if not schema:
2696 schema = self.default_schema_name
2697 # MariaDB implements sequences as a special type of table
2698 cursor = connection.execute(
2699 sql.text(
2700 "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
2701 "WHERE TABLE_TYPE='SEQUENCE' and TABLE_SCHEMA=:schema_name"
2702 ),
2703 dict(schema_name=schema),
2704 )
2705 return [
2706 row[0]
2707 for row in self._compat_fetchall(
2708 cursor, charset=self._connection_charset
2709 )
2710 ]
2712 def initialize(self, connection):
2713 # this is driver-based, does not need server version info
2714 # and is fairly critical for even basic SQL operations
2715 self._connection_charset = self._detect_charset(connection)
2717 # call super().initialize() because we need to have
2718 # server_version_info set up. in 1.4 under python 2 only this does the
2719 # "check unicode returns" thing, which is the one area that some
2720 # SQL gets compiled within initialize() currently
2721 default.DefaultDialect.initialize(self, connection)
2723 self._detect_sql_mode(connection)
2724 self._detect_ansiquotes(connection) # depends on sql mode
2725 self._detect_casing(connection)
2726 if self._server_ansiquotes:
2727 # if ansiquotes == True, build a new IdentifierPreparer
2728 # with the new setting
2729 self.identifier_preparer = self.preparer(
2730 self, server_ansiquotes=self._server_ansiquotes
2731 )
2733 self.supports_sequences = (
2734 self.is_mariadb and self.server_version_info >= (10, 3)
2735 )
2737 self.supports_for_update_of = (
2738 self._is_mysql and self.server_version_info >= (8,)
2739 )
2741 self._needs_correct_for_88718_96365 = (
2742 not self.is_mariadb and self.server_version_info >= (8,)
2743 )
2745 self._warn_for_known_db_issues()
2747 def _warn_for_known_db_issues(self):
2748 if self.is_mariadb:
2749 mdb_version = self._mariadb_normalized_version_info
2750 if mdb_version > (10, 2) and mdb_version < (10, 2, 9):
2751 util.warn(
2752 "MariaDB %r before 10.2.9 has known issues regarding "
2753 "CHECK constraints, which impact handling of NULL values "
2754 "with SQLAlchemy's boolean datatype (MDEV-13596). An "
2755 "additional issue prevents proper migrations of columns "
2756 "with CHECK constraints (MDEV-11114). Please upgrade to "
2757 "MariaDB 10.2.9 or greater, or use the MariaDB 10.1 "
2758 "series, to avoid these issues." % (mdb_version,)
2759 )
2761 @property
2762 def _support_float_cast(self):
2763 if not self.server_version_info:
2764 return False
2765 elif self.is_mariadb:
2766 # ref https://mariadb.com/kb/en/mariadb-1045-release-notes/
2767 return self.server_version_info >= (10, 4, 5)
2768 else:
2769 # ref https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html#mysqld-8-0-17-feature # noqa
2770 return self.server_version_info >= (8, 0, 17)
2772 @property
2773 def _is_mariadb(self):
2774 return self.is_mariadb
2776 @property
2777 def _is_mysql(self):
2778 return not self.is_mariadb
2780 @property
2781 def _is_mariadb_102(self):
2782 return self.is_mariadb and self._mariadb_normalized_version_info > (
2783 10,
2784 2,
2785 )
2787 @reflection.cache
2788 def get_schema_names(self, connection, **kw):
2789 rp = connection.exec_driver_sql("SHOW schemas")
2790 return [r[0] for r in rp]
2792 @reflection.cache
2793 def get_table_names(self, connection, schema=None, **kw):
2794 """Return a Unicode SHOW TABLES from a given schema."""
2795 if schema is not None:
2796 current_schema = schema
2797 else:
2798 current_schema = self.default_schema_name
2800 charset = self._connection_charset
2802 rp = connection.exec_driver_sql(
2803 "SHOW FULL TABLES FROM %s"
2804 % self.identifier_preparer.quote_identifier(current_schema)
2805 )
2807 return [
2808 row[0]
2809 for row in self._compat_fetchall(rp, charset=charset)
2810 if row[1] == "BASE TABLE"
2811 ]
2813 @reflection.cache
2814 def get_view_names(self, connection, schema=None, **kw):
2815 if schema is None:
2816 schema = self.default_schema_name
2817 charset = self._connection_charset
2818 rp = connection.exec_driver_sql(
2819 "SHOW FULL TABLES FROM %s"
2820 % self.identifier_preparer.quote_identifier(schema)
2821 )
2822 return [
2823 row[0]
2824 for row in self._compat_fetchall(rp, charset=charset)
2825 if row[1] in ("VIEW", "SYSTEM VIEW")
2826 ]
2828 @reflection.cache
2829 def get_table_options(self, connection, table_name, schema=None, **kw):
2831 parsed_state = self._parsed_state_or_create(
2832 connection, table_name, schema, **kw
2833 )
2834 return parsed_state.table_options
2836 @reflection.cache
2837 def get_columns(self, connection, table_name, schema=None, **kw):
2838 parsed_state = self._parsed_state_or_create(
2839 connection, table_name, schema, **kw
2840 )
2841 return parsed_state.columns
2843 @reflection.cache
2844 def get_pk_constraint(self, connection, table_name, schema=None, **kw):
2845 parsed_state = self._parsed_state_or_create(
2846 connection, table_name, schema, **kw
2847 )
2848 for key in parsed_state.keys:
2849 if key["type"] == "PRIMARY":
2850 # There can be only one.
2851 cols = [s[0] for s in key["columns"]]
2852 return {"constrained_columns": cols, "name": None}
2853 return {"constrained_columns": [], "name": None}
2855 @reflection.cache
2856 def get_foreign_keys(self, connection, table_name, schema=None, **kw):
2858 parsed_state = self._parsed_state_or_create(
2859 connection, table_name, schema, **kw
2860 )
2861 default_schema = None
2863 fkeys = []
2865 for spec in parsed_state.fk_constraints:
2866 ref_name = spec["table"][-1]
2867 ref_schema = len(spec["table"]) > 1 and spec["table"][-2] or schema
2869 if not ref_schema:
2870 if default_schema is None:
2871 default_schema = connection.dialect.default_schema_name
2872 if schema == default_schema:
2873 ref_schema = schema
2875 loc_names = spec["local"]
2876 ref_names = spec["foreign"]
2878 con_kw = {}
2879 for opt in ("onupdate", "ondelete"):
2880 if spec.get(opt, False) not in ("NO ACTION", None):
2881 con_kw[opt] = spec[opt]
2883 fkey_d = {
2884 "name": spec["name"],
2885 "constrained_columns": loc_names,
2886 "referred_schema": ref_schema,
2887 "referred_table": ref_name,
2888 "referred_columns": ref_names,
2889 "options": con_kw,
2890 }
2891 fkeys.append(fkey_d)
2893 if self._needs_correct_for_88718_96365:
2894 self._correct_for_mysql_bugs_88718_96365(fkeys, connection)
2896 return fkeys
2898 def _correct_for_mysql_bugs_88718_96365(self, fkeys, connection):
2899 # Foreign key is always in lower case (MySQL 8.0)
2900 # https://bugs.mysql.com/bug.php?id=88718
2901 # issue #4344 for SQLAlchemy
2903 # table name also for MySQL 8.0
2904 # https://bugs.mysql.com/bug.php?id=96365
2905 # issue #4751 for SQLAlchemy
2907 # for lower_case_table_names=2, information_schema.columns
2908 # preserves the original table/schema casing, but SHOW CREATE
2909 # TABLE does not. this problem is not in lower_case_table_names=1,
2910 # but use case-insensitive matching for these two modes in any case.
2912 if self._casing in (1, 2):
2914 def lower(s):
2915 return s.lower()
2917 else:
2918 # if on case sensitive, there can be two tables referenced
2919 # with the same name different casing, so we need to use
2920 # case-sensitive matching.
2921 def lower(s):
2922 return s
2924 default_schema_name = connection.dialect.default_schema_name
2925 col_tuples = [
2926 (
2927 lower(rec["referred_schema"] or default_schema_name),
2928 lower(rec["referred_table"]),
2929 col_name,
2930 )
2931 for rec in fkeys
2932 for col_name in rec["referred_columns"]
2933 ]
2935 if col_tuples:
2937 correct_for_wrong_fk_case = connection.execute(
2938 sql.text(
2939 """
2940 select table_schema, table_name, column_name
2941 from information_schema.columns
2942 where (table_schema, table_name, lower(column_name)) in
2943 :table_data;
2944 """
2945 ).bindparams(sql.bindparam("table_data", expanding=True)),
2946 dict(table_data=col_tuples),
2947 )
2949 # in casing=0, table name and schema name come back in their
2950 # exact case.
2951 # in casing=1, table name and schema name come back in lower
2952 # case.
2953 # in casing=2, table name and schema name come back from the
2954 # information_schema.columns view in the case
2955 # that was used in CREATE DATABASE and CREATE TABLE, but
2956 # SHOW CREATE TABLE converts them to *lower case*, therefore
2957 # not matching. So for this case, case-insensitive lookup
2958 # is necessary
2959 d = defaultdict(dict)
2960 for schema, tname, cname in correct_for_wrong_fk_case:
2961 d[(lower(schema), lower(tname))]["SCHEMANAME"] = schema
2962 d[(lower(schema), lower(tname))]["TABLENAME"] = tname
2963 d[(lower(schema), lower(tname))][cname.lower()] = cname
2965 for fkey in fkeys:
2966 rec = d[
2967 (
2968 lower(fkey["referred_schema"] or default_schema_name),
2969 lower(fkey["referred_table"]),
2970 )
2971 ]
2973 fkey["referred_table"] = rec["TABLENAME"]
2974 if fkey["referred_schema"] is not None:
2975 fkey["referred_schema"] = rec["SCHEMANAME"]
2977 fkey["referred_columns"] = [
2978 rec[col.lower()] for col in fkey["referred_columns"]
2979 ]
2981 @reflection.cache
2982 def get_check_constraints(self, connection, table_name, schema=None, **kw):
2983 parsed_state = self._parsed_state_or_create(
2984 connection, table_name, schema, **kw
2985 )
2987 return [
2988 {"name": spec["name"], "sqltext": spec["sqltext"]}
2989 for spec in parsed_state.ck_constraints
2990 ]
2992 @reflection.cache
2993 def get_table_comment(self, connection, table_name, schema=None, **kw):
2994 parsed_state = self._parsed_state_or_create(
2995 connection, table_name, schema, **kw
2996 )
2997 return {
2998 "text": parsed_state.table_options.get(
2999 "%s_comment" % self.name, None
3000 )
3001 }
3003 @reflection.cache
3004 def get_indexes(self, connection, table_name, schema=None, **kw):
3006 parsed_state = self._parsed_state_or_create(
3007 connection, table_name, schema, **kw
3008 )
3010 indexes = []
3012 for spec in parsed_state.keys:
3013 dialect_options = {}
3014 unique = False
3015 flavor = spec["type"]
3016 if flavor == "PRIMARY":
3017 continue
3018 if flavor == "UNIQUE":
3019 unique = True
3020 elif flavor in ("FULLTEXT", "SPATIAL"):
3021 dialect_options["%s_prefix" % self.name] = flavor
3022 elif flavor is None:
3023 pass
3024 else:
3025 self.logger.info(
3026 "Converting unknown KEY type %s to a plain KEY", flavor
3027 )
3028 pass
3030 if spec["parser"]:
3031 dialect_options["%s_with_parser" % (self.name)] = spec[
3032 "parser"
3033 ]
3035 index_d = {}
3037 index_d["name"] = spec["name"]
3038 index_d["column_names"] = [s[0] for s in spec["columns"]]
3039 mysql_length = {
3040 s[0]: s[1] for s in spec["columns"] if s[1] is not None
3041 }
3042 if mysql_length:
3043 dialect_options["%s_length" % self.name] = mysql_length
3045 index_d["unique"] = unique
3046 if flavor:
3047 index_d["type"] = flavor
3049 if dialect_options:
3050 index_d["dialect_options"] = dialect_options
3052 indexes.append(index_d)
3053 return indexes
3055 @reflection.cache
3056 def get_unique_constraints(
3057 self, connection, table_name, schema=None, **kw
3058 ):
3059 parsed_state = self._parsed_state_or_create(
3060 connection, table_name, schema, **kw
3061 )
3063 return [
3064 {
3065 "name": key["name"],
3066 "column_names": [col[0] for col in key["columns"]],
3067 "duplicates_index": key["name"],
3068 }
3069 for key in parsed_state.keys
3070 if key["type"] == "UNIQUE"
3071 ]
3073 @reflection.cache
3074 def get_view_definition(self, connection, view_name, schema=None, **kw):
3076 charset = self._connection_charset
3077 full_name = ".".join(
3078 self.identifier_preparer._quote_free_identifiers(schema, view_name)
3079 )
3080 sql = self._show_create_table(
3081 connection, None, charset, full_name=full_name
3082 )
3083 return sql
3085 def _parsed_state_or_create(
3086 self, connection, table_name, schema=None, **kw
3087 ):
3088 return self._setup_parser(
3089 connection,
3090 table_name,
3091 schema,
3092 info_cache=kw.get("info_cache", None),
3093 )
3095 @util.memoized_property
3096 def _tabledef_parser(self):
3097 """return the MySQLTableDefinitionParser, generate if needed.
3099 The deferred creation ensures that the dialect has
3100 retrieved server version information first.
3102 """
3103 preparer = self.identifier_preparer
3104 return _reflection.MySQLTableDefinitionParser(self, preparer)
3106 @reflection.cache
3107 def _setup_parser(self, connection, table_name, schema=None, **kw):
3108 charset = self._connection_charset
3109 parser = self._tabledef_parser
3110 full_name = ".".join(
3111 self.identifier_preparer._quote_free_identifiers(
3112 schema, table_name
3113 )
3114 )
3115 sql = self._show_create_table(
3116 connection, None, charset, full_name=full_name
3117 )
3118 if parser._check_view(sql):
3119 # Adapt views to something table-like.
3120 columns = self._describe_table(
3121 connection, None, charset, full_name=full_name
3122 )
3123 sql = parser._describe_to_create(table_name, columns)
3124 return parser.parse(sql, charset)
3126 def _fetch_setting(self, connection, setting_name):
3127 charset = self._connection_charset
3129 if self.server_version_info and self.server_version_info < (5, 6):
3130 sql = "SHOW VARIABLES LIKE '%s'" % setting_name
3131 fetch_col = 1
3132 else:
3133 sql = "SELECT @@%s" % setting_name
3134 fetch_col = 0
3136 show_var = connection.exec_driver_sql(sql)
3137 row = self._compat_first(show_var, charset=charset)
3138 if not row:
3139 return None
3140 else:
3141 return row[fetch_col]
3143 def _detect_charset(self, connection):
3144 raise NotImplementedError()
3146 def _detect_casing(self, connection):
3147 """Sniff out identifier case sensitivity.
3149 Cached per-connection. This value can not change without a server
3150 restart.
3152 """
3153 # https://dev.mysql.com/doc/refman/en/identifier-case-sensitivity.html
3155 setting = self._fetch_setting(connection, "lower_case_table_names")
3156 if setting is None:
3157 cs = 0
3158 else:
3159 # 4.0.15 returns OFF or ON according to [ticket:489]
3160 # 3.23 doesn't, 4.0.27 doesn't..
3161 if setting == "OFF":
3162 cs = 0
3163 elif setting == "ON":
3164 cs = 1
3165 else:
3166 cs = int(setting)
3167 self._casing = cs
3168 return cs
3170 def _detect_collations(self, connection):
3171 """Pull the active COLLATIONS list from the server.
3173 Cached per-connection.
3174 """
3176 collations = {}
3177 charset = self._connection_charset
3178 rs = connection.exec_driver_sql("SHOW COLLATION")
3179 for row in self._compat_fetchall(rs, charset):
3180 collations[row[0]] = row[1]
3181 return collations
3183 def _detect_sql_mode(self, connection):
3184 setting = self._fetch_setting(connection, "sql_mode")
3186 if setting is None:
3187 util.warn(
3188 "Could not retrieve SQL_MODE; please ensure the "
3189 "MySQL user has permissions to SHOW VARIABLES"
3190 )
3191 self._sql_mode = ""
3192 else:
3193 self._sql_mode = setting or ""
3195 def _detect_ansiquotes(self, connection):
3196 """Detect and adjust for the ANSI_QUOTES sql mode."""
3198 mode = self._sql_mode
3199 if not mode:
3200 mode = ""
3201 elif mode.isdigit():
3202 mode_no = int(mode)
3203 mode = (mode_no | 4 == mode_no) and "ANSI_QUOTES" or ""
3205 self._server_ansiquotes = "ANSI_QUOTES" in mode
3207 # as of MySQL 5.0.1
3208 self._backslash_escapes = "NO_BACKSLASH_ESCAPES" not in mode
3210 def _show_create_table(
3211 self, connection, table, charset=None, full_name=None
3212 ):
3213 """Run SHOW CREATE TABLE for a ``Table``."""
3215 if full_name is None:
3216 full_name = self.identifier_preparer.format_table(table)
3217 st = "SHOW CREATE TABLE %s" % full_name
3219 rp = None
3220 try:
3221 rp = connection.execution_options(
3222 skip_user_error_events=True
3223 ).exec_driver_sql(st)
3224 except exc.DBAPIError as e:
3225 if self._extract_error_code(e.orig) == 1146:
3226 util.raise_(exc.NoSuchTableError(full_name), replace_context=e)
3227 else:
3228 raise
3229 row = self._compat_first(rp, charset=charset)
3230 if not row:
3231 raise exc.NoSuchTableError(full_name)
3232 return row[1].strip()
3234 def _describe_table(self, connection, table, charset=None, full_name=None):
3235 """Run DESCRIBE for a ``Table`` and return processed rows."""
3237 if full_name is None:
3238 full_name = self.identifier_preparer.format_table(table)
3239 st = "DESCRIBE %s" % full_name
3241 rp, rows = None, None
3242 try:
3243 try:
3244 rp = connection.execution_options(
3245 skip_user_error_events=True
3246 ).exec_driver_sql(st)
3247 except exc.DBAPIError as e:
3248 code = self._extract_error_code(e.orig)
3249 if code == 1146:
3250 util.raise_(
3251 exc.NoSuchTableError(full_name), replace_context=e
3252 )
3253 elif code == 1356:
3254 util.raise_(
3255 exc.UnreflectableTableError(
3256 "Table or view named %s could not be "
3257 "reflected: %s" % (full_name, e)
3258 ),
3259 replace_context=e,
3260 )
3261 else:
3262 raise
3263 rows = self._compat_fetchall(rp, charset=charset)
3264 finally:
3265 if rp:
3266 rp.close()
3267 return rows
3270class _DecodingRow(object):
3271 """Return unicode-decoded values based on type inspection.
3273 Smooth over data type issues (esp. with alpha driver versions) and
3274 normalize strings as Unicode regardless of user-configured driver
3275 encoding settings.
3277 """
3279 # Some MySQL-python versions can return some columns as
3280 # sets.Set(['value']) (seriously) but thankfully that doesn't
3281 # seem to come up in DDL queries.
3283 _encoding_compat = {
3284 "koi8r": "koi8_r",
3285 "koi8u": "koi8_u",
3286 "utf16": "utf-16-be", # MySQL's uft16 is always bigendian
3287 "utf8mb4": "utf8", # real utf8
3288 "utf8mb3": "utf8", # real utf8; saw this happen on CI but I cannot
3289 # reproduce, possibly mariadb10.6 related
3290 "eucjpms": "ujis",
3291 }
3293 def __init__(self, rowproxy, charset):
3294 self.rowproxy = rowproxy
3295 self.charset = self._encoding_compat.get(charset, charset)
3297 def __getitem__(self, index):
3298 item = self.rowproxy[index]
3299 if isinstance(item, _array):
3300 item = item.tostring()
3302 if self.charset and isinstance(item, util.binary_type):
3303 return item.decode(self.charset)
3304 else:
3305 return item
3307 def __getattr__(self, attr):
3308 item = getattr(self.rowproxy, attr)
3309 if isinstance(item, _array):
3310 item = item.tostring()
3311 if self.charset and isinstance(item, util.binary_type):
3312 return item.decode(self.charset)
3313 else:
3314 return item