codekingpro/portable-devtools
115k
1# dialects/mysql/base.py
2# Copyright (C) 2005-2026 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
7
8
9r"""
10
11.. dialect:: mysql
12 :name: MySQL / MariaDB
13 :normal_support: 5.6+ / 10+
14 :best_effort: 5.0.2+ / 5.0.2+
15
16Supported Versions and Features
17-------------------------------
18
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.
23
24.. versionchanged:: 1.4 minimum MySQL version supported is now 5.0.2.
25
26MariaDB Support
27~~~~~~~~~~~~~~~
28
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::
34
35
36 engine = create_engine(
37 "mysql+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4"
38 )
39
40Upon first connect, the SQLAlchemy dialect employs a
41server version detection scheme that determines if the
42backing database reports as MariaDB. Based on this flag, the dialect
43can make different choices in those of areas where its behavior
44must be different.
45
46.. _mysql_mariadb_only_mode:
47
48MariaDB-Only Mode
49~~~~~~~~~~~~~~~~~
50
51The dialect also supports an **optional** "MariaDB-only" mode of connection, which may be
52useful for the case where an application makes use of MariaDB-specific features
53and is not compatible with a MySQL database. To use this mode of operation,
54replace the "mysql" token in the above URL with "mariadb"::
55
56 engine = create_engine(
57 "mariadb+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4"
58 )
59
60The above engine, upon first connect, will raise an error if the server version
61detection detects that the backing database is not MariaDB.
62
63When using an engine with ``"mariadb"`` as the dialect name, **all mysql-specific options
64that include the name "mysql" in them are now named with "mariadb"**. This means
65options like ``mysql_engine`` should be named ``mariadb_engine``, etc. Both
66"mysql" and "mariadb" options can be used simultaneously for applications that
67use URLs with both "mysql" and "mariadb" dialects::
68
69 my_table = Table(
70 "mytable",
71 metadata,
72 Column("id", Integer, primary_key=True),
73 Column("textdata", String(50)),
74 mariadb_engine="InnoDB",
75 mysql_engine="InnoDB",
76 )
77
78 Index(
79 "textdata_ix",
80 my_table.c.textdata,
81 mysql_prefix="FULLTEXT",
82 mariadb_prefix="FULLTEXT",
83 )
84
85Similar behavior will occur when the above structures are reflected, i.e. the
86"mariadb" prefix will be present in the option names when the database URL
87is based on the "mariadb" name.
88
89.. versionadded:: 1.4 Added "mariadb" dialect name supporting "MariaDB-only mode"
90 for the MySQL dialect.
91
92.. _mysql_connection_timeouts:
93
94Connection Timeouts and Disconnects
95-----------------------------------
96
97MySQL / MariaDB feature an automatic connection close behavior, for connections that
98have been idle for a fixed period of time, defaulting to eight hours.
99To circumvent having this issue, use
100the :paramref:`_sa.create_engine.pool_recycle` option which ensures that
101a connection will be discarded and replaced with a new one if it has been
102present in the pool for a fixed number of seconds::
103
104 engine = create_engine("mysql+mysqldb://...", pool_recycle=3600)
105
106For more comprehensive disconnect detection of pooled connections, including
107accommodation of server restarts and network issues, a pre-ping approach may
108be employed. See :ref:`pool_disconnects` for current approaches.
109
110.. seealso::
111
112 :ref:`pool_disconnects` - Background on several techniques for dealing
113 with timed out connections as well as database restarts.
114
115.. _mysql_storage_engines:
116
117CREATE TABLE arguments including Storage Engines
118------------------------------------------------
119
120Both MySQL's and MariaDB's CREATE TABLE syntax includes a wide array of special options,
121including ``ENGINE``, ``CHARSET``, ``MAX_ROWS``, ``ROW_FORMAT``,
122``INSERT_METHOD``, and many more.
123To accommodate the rendering of these arguments, specify the form
124``mysql_argument_name="value"``. For example, to specify a table with
125``ENGINE`` of ``InnoDB``, ``CHARSET`` of ``utf8mb4``, and ``KEY_BLOCK_SIZE``
126of ``1024``::
127
128 Table(
129 "mytable",
130 metadata,
131 Column("data", String(32)),
132 mysql_engine="InnoDB",
133 mysql_charset="utf8mb4",
134 mysql_key_block_size="1024",
135 )
136
137When supporting :ref:`mysql_mariadb_only_mode` mode, similar keys against
138the "mariadb" prefix must be included as well. The values can of course
139vary independently so that different settings on MySQL vs. MariaDB may
140be maintained::
141
142 # support both "mysql" and "mariadb-only" engine URLs
143
144 Table(
145 "mytable",
146 metadata,
147 Column("data", String(32)),
148 mysql_engine="InnoDB",
149 mariadb_engine="InnoDB",
150 mysql_charset="utf8mb4",
151 mariadb_charset="utf8",
152 mysql_key_block_size="1024",
153 mariadb_key_block_size="1024",
154 )
155
156The MySQL / MariaDB dialects will normally transfer any keyword specified as
157``mysql_keyword_name`` to be rendered as ``KEYWORD_NAME`` in the
158``CREATE TABLE`` statement. A handful of these names will render with a space
159instead of an underscore; to support this, the MySQL dialect has awareness of
160these particular names, which include ``DATA DIRECTORY``
161(e.g. ``mysql_data_directory``), ``CHARACTER SET`` (e.g.
162``mysql_character_set``) and ``INDEX DIRECTORY`` (e.g.
163``mysql_index_directory``).
164
165The most common argument is ``mysql_engine``, which refers to the storage
166engine for the table. Historically, MySQL server installations would default
167to ``MyISAM`` for this value, although newer versions may be defaulting
168to ``InnoDB``. The ``InnoDB`` engine is typically preferred for its support
169of transactions and foreign keys.
170
171A :class:`_schema.Table`
172that is created in a MySQL / MariaDB database with a storage engine
173of ``MyISAM`` will be essentially non-transactional, meaning any
174INSERT/UPDATE/DELETE statement referring to this table will be invoked as
175autocommit. It also will have no support for foreign key constraints; while
176the ``CREATE TABLE`` statement accepts foreign key options, when using the
177``MyISAM`` storage engine these arguments are discarded. Reflecting such a
178table will also produce no foreign key constraint information.
179
180For fully atomic transactions as well as support for foreign key
181constraints, all participating ``CREATE TABLE`` statements must specify a
182transactional engine, which in the vast majority of cases is ``InnoDB``.
183
184Partitioning can similarly be specified using similar options.
185In the example below the create table will specify ``PARTITION_BY``,
186``PARTITIONS``, ``SUBPARTITIONS`` and ``SUBPARTITION_BY``::
187
188 # can also use mariadb_* prefix
189 Table(
190 "testtable",
191 MetaData(),
192 Column("id", Integer(), primary_key=True, autoincrement=True),
193 Column("other_id", Integer(), primary_key=True, autoincrement=False),
194 mysql_partitions="2",
195 mysql_partition_by="KEY(other_id)",
196 mysql_subpartition_by="HASH(some_expr)",
197 mysql_subpartitions="2",
198 )
199
200This will render:
201
202.. sourcecode:: sql
203
204 CREATE TABLE testtable (
205 id INTEGER NOT NULL AUTO_INCREMENT,
206 other_id INTEGER NOT NULL,
207 PRIMARY KEY (id, other_id)
208 )PARTITION BY KEY(other_id) PARTITIONS 2 SUBPARTITION BY HASH(some_expr) SUBPARTITIONS 2
209
210Case Sensitivity and Table Reflection
211-------------------------------------
212
213Both MySQL and MariaDB have inconsistent support for case-sensitive identifier
214names, basing support on specific details of the underlying
215operating system. However, it has been observed that no matter
216what case sensitivity behavior is present, the names of tables in
217foreign key declarations are *always* received from the database
218as all-lower case, making it impossible to accurately reflect a
219schema where inter-related tables use mixed-case identifier names.
220
221Therefore it is strongly advised that table names be declared as
222all lower case both within SQLAlchemy as well as on the MySQL / MariaDB
223database itself, especially if database reflection features are
224to be used.
225
226.. _mysql_isolation_level:
227
228Transaction Isolation Level
229---------------------------
230
231All MySQL / MariaDB dialects support setting of transaction isolation level both via a
232dialect-specific parameter :paramref:`_sa.create_engine.isolation_level`
233accepted
234by :func:`_sa.create_engine`, as well as the
235:paramref:`.Connection.execution_options.isolation_level` argument as passed to
236:meth:`_engine.Connection.execution_options`.
237This feature works by issuing the
238command ``SET SESSION TRANSACTION ISOLATION LEVEL <level>`` for each new
239connection. For the special AUTOCOMMIT isolation level, DBAPI-specific
240techniques are used.
241
242To set isolation level using :func:`_sa.create_engine`::
243
244 engine = create_engine(
245 "mysql+mysqldb://scott:tiger@localhost/test",
246 isolation_level="READ UNCOMMITTED",
247 )
248
249To set using per-connection execution options::
250
251 connection = engine.connect()
252 connection = connection.execution_options(isolation_level="READ COMMITTED")
253
254Valid values for ``isolation_level`` include:
255
256* ``READ COMMITTED``
257* ``READ UNCOMMITTED``
258* ``REPEATABLE READ``
259* ``SERIALIZABLE``
260* ``AUTOCOMMIT``
261
262The special ``AUTOCOMMIT`` value makes use of the various "autocommit"
263attributes provided by specific DBAPIs, and is currently supported by
264MySQLdb, MySQL-Client, MySQL-Connector Python, and PyMySQL. Using it,
265the database connection will return true for the value of
266``SELECT @@autocommit;``.
267
268There are also more options for isolation level configurations, such as
269"sub-engine" objects linked to a main :class:`_engine.Engine` which each apply
270different isolation level settings. See the discussion at
271:ref:`dbapi_autocommit` for background.
272
273.. seealso::
274
275 :ref:`dbapi_autocommit`
276
277AUTO_INCREMENT Behavior
278-----------------------
279
280When creating tables, SQLAlchemy will automatically set ``AUTO_INCREMENT`` on
281the first :class:`.Integer` primary key column which is not marked as a
282foreign key::
283
284 >>> t = Table(
285 ... "mytable", metadata, Column("mytable_id", Integer, primary_key=True)
286 ... )
287 >>> t.create()
288 CREATE TABLE mytable (
289 id INTEGER NOT NULL AUTO_INCREMENT,
290 PRIMARY KEY (id)
291 )
292
293You can disable this behavior by passing ``False`` to the
294:paramref:`_schema.Column.autoincrement` argument of :class:`_schema.Column`.
295This flag
296can also be used to enable auto-increment on a secondary column in a
297multi-column key for some storage engines::
298
299 Table(
300 "mytable",
301 metadata,
302 Column("gid", Integer, primary_key=True, autoincrement=False),
303 Column("id", Integer, primary_key=True),
304 )
305
306.. _mysql_ss_cursors:
307
308Server Side Cursors
309-------------------
310
311Server-side cursor support is available for the mysqlclient, PyMySQL,
312mariadbconnector dialects and may also be available in others. This makes use
313of either the "buffered=True/False" flag if available or by using a class such
314as ``MySQLdb.cursors.SSCursor`` or ``pymysql.cursors.SSCursor`` internally.
315
316
317Server side cursors are enabled on a per-statement basis by using the
318:paramref:`.Connection.execution_options.stream_results` connection execution
319option::
320
321 with engine.connect() as conn:
322 result = conn.execution_options(stream_results=True).execute(
323 text("select * from table")
324 )
325
326Note that some kinds of SQL statements may not be supported with
327server side cursors; generally, only SQL statements that return rows should be
328used with this option.
329
330.. deprecated:: 1.4 The dialect-level server_side_cursors flag is deprecated
331 and will be removed in a future release. Please use the
332 :paramref:`_engine.Connection.stream_results` execution option for
333 unbuffered cursor support.
334
335.. seealso::
336
337 :ref:`engine_stream_results`
338
339.. _mysql_unicode:
340
341Unicode
342-------
343
344Charset Selection
345~~~~~~~~~~~~~~~~~
346
347Most MySQL / MariaDB DBAPIs offer the option to set the client character set for
348a connection. This is typically delivered using the ``charset`` parameter
349in the URL, such as::
350
351 e = create_engine(
352 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4"
353 )
354
355This charset is the **client character set** for the connection. Some
356MySQL DBAPIs will default this to a value such as ``latin1``, and some
357will make use of the ``default-character-set`` setting in the ``my.cnf``
358file as well. Documentation for the DBAPI in use should be consulted
359for specific behavior.
360
361The encoding used for Unicode has traditionally been ``'utf8'``. However, for
362MySQL versions 5.5.3 and MariaDB 5.5 on forward, a new MySQL-specific encoding
363``'utf8mb4'`` has been introduced, and as of MySQL 8.0 a warning is emitted by
364the server if plain ``utf8`` is specified within any server-side directives,
365replaced with ``utf8mb3``. The rationale for this new encoding is due to the
366fact that MySQL's legacy utf-8 encoding only supports codepoints up to three
367bytes instead of four. Therefore, when communicating with a MySQL or MariaDB
368database that includes codepoints more than three bytes in size, this new
369charset is preferred, if supported by both the database as well as the client
370DBAPI, as in::
371
372 e = create_engine(
373 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4"
374 )
375
376All modern DBAPIs should support the ``utf8mb4`` charset.
377
378In order to use ``utf8mb4`` encoding for a schema that was created with legacy
379``utf8``, changes to the MySQL/MariaDB schema and/or server configuration may be
380required.
381
382.. seealso::
383
384 `The utf8mb4 Character Set \
385 <https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html>`_ - \
386 in the MySQL documentation
387
388.. _mysql_binary_introducer:
389
390Dealing with Binary Data Warnings and Unicode
391~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
392
393MySQL versions 5.6, 5.7 and later (not MariaDB at the time of this writing) now
394emit a warning when attempting to pass binary data to the database, while a
395character set encoding is also in place, when the binary data itself is not
396valid for that encoding:
397
398.. sourcecode:: text
399
400 default.py:509: Warning: (1300, "Invalid utf8mb4 character string:
401 'F9876A'")
402 cursor.execute(statement, parameters)
403
404This warning is due to the fact that the MySQL client library is attempting to
405interpret the binary string as a unicode object even if a datatype such
406as :class:`.LargeBinary` is in use. To resolve this, the SQL statement requires
407a binary "character set introducer" be present before any non-NULL value
408that renders like this:
409
410.. sourcecode:: sql
411
412 INSERT INTO table (data) VALUES (_binary %s)
413
414These character set introducers are provided by the DBAPI driver, assuming the
415use of mysqlclient or PyMySQL (both of which are recommended). Add the query
416string parameter ``binary_prefix=true`` to the URL to repair this warning::
417
418 # mysqlclient
419 engine = create_engine(
420 "mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true"
421 )
422
423 # PyMySQL
424 engine = create_engine(
425 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true"
426 )
427
428The ``binary_prefix`` flag may or may not be supported by other MySQL drivers.
429
430SQLAlchemy itself cannot render this ``_binary`` prefix reliably, as it does
431not work with the NULL value, which is valid to be sent as a bound parameter.
432As the MySQL driver renders parameters directly into the SQL string, it's the
433most efficient place for this additional keyword to be passed.
434
435.. seealso::
436
437 `Character set introducers <https://dev.mysql.com/doc/refman/5.7/en/charset-introducer.html>`_ - on the MySQL website
438
439
440ANSI Quoting Style
441------------------
442
443MySQL / MariaDB feature two varieties of identifier "quoting style", one using
444backticks and the other using quotes, e.g. ```some_identifier``` vs.
445``"some_identifier"``. All MySQL dialects detect which version
446is in use by checking the value of :ref:`sql_mode<mysql_sql_mode>` when a connection is first
447established with a particular :class:`_engine.Engine`.
448This quoting style comes
449into play when rendering table and column names as well as when reflecting
450existing database structures. The detection is entirely automatic and
451no special configuration is needed to use either quoting style.
452
453
454.. _mysql_sql_mode:
455
456Changing the sql_mode
457---------------------
458
459MySQL supports operating in multiple
460`Server SQL Modes <https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html>`_ for
461both Servers and Clients. To change the ``sql_mode`` for a given application, a
462developer can leverage SQLAlchemy's Events system.
463
464In the following example, the event system is used to set the ``sql_mode`` on
465the ``first_connect`` and ``connect`` events::
466
467 from sqlalchemy import create_engine, event
468
469 eng = create_engine(
470 "mysql+mysqldb://scott:tiger@localhost/test", echo="debug"
471 )
472
473
474 # `insert=True` will ensure this is the very first listener to run
475 @event.listens_for(eng, "connect", insert=True)
476 def connect(dbapi_connection, connection_record):
477 cursor = dbapi_connection.cursor()
478 cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'")
479
480
481 conn = eng.connect()
482
483In the example illustrated above, the "connect" event will invoke the "SET"
484statement on the connection at the moment a particular DBAPI connection is
485first created for a given Pool, before the connection is made available to the
486connection pool. Additionally, because the function was registered with
487``insert=True``, it will be prepended to the internal list of registered
488functions.
489
490
491MySQL / MariaDB SQL Extensions
492------------------------------
493
494Many of the MySQL / MariaDB SQL extensions are handled through SQLAlchemy's generic
495function and operator support::
496
497 table.select(table.c.password == func.md5("plaintext"))
498 table.select(table.c.username.op("regexp")("^[a-d]"))
499
500And of course any valid SQL statement can be executed as a string as well.
501
502Some limited direct support for MySQL / MariaDB extensions to SQL is currently
503available.
504
505* INSERT..ON DUPLICATE KEY UPDATE: See
506 :ref:`mysql_insert_on_duplicate_key_update`
507
508* SELECT pragma, use :meth:`_expression.Select.prefix_with` and
509 :meth:`_query.Query.prefix_with`::
510
511 select(...).prefix_with(["HIGH_PRIORITY", "SQL_SMALL_RESULT"])
512
513* UPDATE with LIMIT::
514
515 update(...).with_dialect_options(mysql_limit=10, mariadb_limit=10)
516
517* DELETE
518 with LIMIT::
519
520 delete(...).with_dialect_options(mysql_limit=10, mariadb_limit=10)
521
522 .. versionadded:: 2.0.37 Added delete with limit
523
524* optimizer hints, use :meth:`_expression.Select.prefix_with` and
525 :meth:`_query.Query.prefix_with`::
526
527 select(...).prefix_with("/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */")
528
529* index hints, use :meth:`_expression.Select.with_hint` and
530 :meth:`_query.Query.with_hint`::
531
532 select(...).with_hint(some_table, "USE INDEX xyz")
533
534* MATCH
535 operator support::
536
537 from sqlalchemy.dialects.mysql import match
538
539 select(...).where(match(col1, col2, against="some expr").in_boolean_mode())
540
541 .. seealso::
542
543 :class:`_mysql.match`
544
545INSERT/DELETE...RETURNING
546-------------------------
547
548The MariaDB dialect supports 10.5+'s ``INSERT..RETURNING`` and
549``DELETE..RETURNING`` (10.0+) syntaxes. ``INSERT..RETURNING`` may be used
550automatically in some cases in order to fetch newly generated identifiers in
551place of the traditional approach of using ``cursor.lastrowid``, however
552``cursor.lastrowid`` is currently still preferred for simple single-statement
553cases for its better performance.
554
555To specify an explicit ``RETURNING`` clause, use the
556:meth:`._UpdateBase.returning` method on a per-statement basis::
557
558 # INSERT..RETURNING
559 result = connection.execute(
560 table.insert().values(name="foo").returning(table.c.col1, table.c.col2)
561 )
562 print(result.all())
563
564 # DELETE..RETURNING
565 result = connection.execute(
566 table.delete()
567 .where(table.c.name == "foo")
568 .returning(table.c.col1, table.c.col2)
569 )
570 print(result.all())
571
572.. versionadded:: 2.0 Added support for MariaDB RETURNING
573
574.. _mysql_insert_on_duplicate_key_update:
575
576INSERT...ON DUPLICATE KEY UPDATE (Upsert)
577------------------------------------------
578
579MySQL / MariaDB allow "upserts" (update or insert)
580of rows into a table via the ``ON DUPLICATE KEY UPDATE`` clause of the
581``INSERT`` statement. A candidate row will only be inserted if that row does
582not match an existing primary or unique key in the table; otherwise, an UPDATE
583will be performed. The statement allows for separate specification of the
584values to INSERT versus the values for UPDATE.
585
586SQLAlchemy provides ``ON DUPLICATE KEY UPDATE`` support via the MySQL-specific
587:func:`.mysql.insert()` function, which provides
588the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`:
589
590.. sourcecode:: pycon+sql
591
592 >>> from sqlalchemy.dialects.mysql import insert
593
594 >>> insert_stmt = insert(my_table).values(
595 ... id="some_existing_id", data="inserted value"
596 ... )
597
598 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
599 ... data=insert_stmt.inserted.data, status="U"
600 ... )
601 >>> print(on_duplicate_key_stmt)
602 {printsql}INSERT INTO my_table (id, data) VALUES (%s, %s)
603 ON DUPLICATE KEY UPDATE data = VALUES(data), status = %s
604
605
606Unlike PostgreSQL's "ON CONFLICT" phrase, the "ON DUPLICATE KEY UPDATE"
607phrase will always match on any primary key or unique key, and will always
608perform an UPDATE if there's a match; there are no options for it to raise
609an error or to skip performing an UPDATE.
610
611``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already
612existing row, using any combination of new values as well as values
613from the proposed insertion. These values are normally specified using
614keyword arguments passed to the
615:meth:`_mysql.Insert.on_duplicate_key_update`
616given column key values (usually the name of the column, unless it
617specifies :paramref:`_schema.Column.key`
618) as keys and literal or SQL expressions
619as values:
620
621.. sourcecode:: pycon+sql
622
623 >>> insert_stmt = insert(my_table).values(
624 ... id="some_existing_id", data="inserted value"
625 ... )
626
627 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
628 ... data="some data",
629 ... updated_at=func.current_timestamp(),
630 ... )
631
632 >>> print(on_duplicate_key_stmt)
633 {printsql}INSERT INTO my_table (id, data) VALUES (%s, %s)
634 ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP
635
636In a manner similar to that of :meth:`.UpdateBase.values`, other parameter
637forms are accepted, including a single dictionary:
638
639.. sourcecode:: pycon+sql
640
641 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
642 ... {"data": "some data", "updated_at": func.current_timestamp()},
643 ... )
644
645as well as a list of 2-tuples, which will automatically provide
646a parameter-ordered UPDATE statement in a manner similar to that described
647at :ref:`tutorial_parameter_ordered_updates`. Unlike the :class:`_expression.Update`
648object,
649no special flag is needed to specify the intent since the argument form is
650this context is unambiguous:
651
652.. sourcecode:: pycon+sql
653
654 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
655 ... [
656 ... ("data", "some data"),
657 ... ("updated_at", func.current_timestamp()),
658 ... ]
659 ... )
660
661 >>> print(on_duplicate_key_stmt)
662 {printsql}INSERT INTO my_table (id, data) VALUES (%s, %s)
663 ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP
664
665.. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within
666 MySQL ON DUPLICATE KEY UPDATE
667
668.. warning::
669
670 The :meth:`_mysql.Insert.on_duplicate_key_update`
671 method does **not** take into
672 account Python-side default UPDATE values or generation functions, e.g.
673 e.g. those specified using :paramref:`_schema.Column.onupdate`.
674 These values will not be exercised for an ON DUPLICATE KEY style of UPDATE,
675 unless they are manually specified explicitly in the parameters.
676
677
678
679In order to refer to the proposed insertion row, the special alias
680:attr:`_mysql.Insert.inserted` is available as an attribute on
681the :class:`_mysql.Insert` object; this object is a
682:class:`_expression.ColumnCollection` which contains all columns of the target
683table:
684
685.. sourcecode:: pycon+sql
686
687 >>> stmt = insert(my_table).values(
688 ... id="some_id", data="inserted value", author="jlh"
689 ... )
690
691 >>> do_update_stmt = stmt.on_duplicate_key_update(
692 ... data="updated value", author=stmt.inserted.author
693 ... )
694
695 >>> print(do_update_stmt)
696 {printsql}INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s)
697 ON DUPLICATE KEY UPDATE data = %s, author = VALUES(author)
698
699When rendered, the "inserted" namespace will produce the expression
700``VALUES(<columnname>)``.
701
702.. versionadded:: 1.2 Added support for MySQL ON DUPLICATE KEY UPDATE clause
703
704
705
706rowcount Support
707----------------
708
709SQLAlchemy standardizes the DBAPI ``cursor.rowcount`` attribute to be the
710usual definition of "number of rows matched by an UPDATE or DELETE" statement.
711This is in contradiction to the default setting on most MySQL DBAPI drivers,
712which is "number of rows actually modified/deleted". For this reason, the
713SQLAlchemy MySQL dialects always add the ``constants.CLIENT.FOUND_ROWS``
714flag, or whatever is equivalent for the target dialect, upon connection.
715This setting is currently hardcoded.
716
717.. seealso::
718
719 :attr:`_engine.CursorResult.rowcount`
720
721
722.. _mysql_indexes:
723
724MySQL / MariaDB- Specific Index Options
725-----------------------------------------
726
727MySQL and MariaDB-specific extensions to the :class:`.Index` construct are available.
728
729Index Length
730~~~~~~~~~~~~~
731
732MySQL and MariaDB both provide an option to create index entries with a certain length, where
733"length" refers to the number of characters or bytes in each value which will
734become part of the index. SQLAlchemy provides this feature via the
735``mysql_length`` and/or ``mariadb_length`` parameters::
736
737 Index("my_index", my_table.c.data, mysql_length=10, mariadb_length=10)
738
739 Index("a_b_idx", my_table.c.a, my_table.c.b, mysql_length={"a": 4, "b": 9})
740
741 Index(
742 "a_b_idx", my_table.c.a, my_table.c.b, mariadb_length={"a": 4, "b": 9}
743 )
744
745Prefix lengths are given in characters for nonbinary string types and in bytes
746for binary string types. The value passed to the keyword argument *must* be
747either an integer (and, thus, specify the same prefix length value for all
748columns of the index) or a dict in which keys are column names and values are
749prefix length values for corresponding columns. MySQL and MariaDB only allow a
750length for a column of an index if it is for a CHAR, VARCHAR, TEXT, BINARY,
751VARBINARY and BLOB.
752
753Index Prefixes
754~~~~~~~~~~~~~~
755
756MySQL storage engines permit you to specify an index prefix when creating
757an index. SQLAlchemy provides this feature via the
758``mysql_prefix`` parameter on :class:`.Index`::
759
760 Index("my_index", my_table.c.data, mysql_prefix="FULLTEXT")
761
762The value passed to the keyword argument will be simply passed through to the
763underlying CREATE INDEX, so it *must* be a valid index prefix for your MySQL
764storage engine.
765
766.. seealso::
767
768 `CREATE INDEX <https://dev.mysql.com/doc/refman/5.0/en/create-index.html>`_ - MySQL documentation
769
770Index Types
771~~~~~~~~~~~~~
772
773Some MySQL storage engines permit you to specify an index type when creating
774an index or primary key constraint. SQLAlchemy provides this feature via the
775``mysql_using`` parameter on :class:`.Index`::
776
777 Index(
778 "my_index", my_table.c.data, mysql_using="hash", mariadb_using="hash"
779 )
780
781As well as the ``mysql_using`` parameter on :class:`.PrimaryKeyConstraint`::
782
783 PrimaryKeyConstraint("data", mysql_using="hash", mariadb_using="hash")
784
785The value passed to the keyword argument will be simply passed through to the
786underlying CREATE INDEX or PRIMARY KEY clause, so it *must* be a valid index
787type for your MySQL storage engine.
788
789More information can be found at:
790
791https://dev.mysql.com/doc/refman/5.0/en/create-index.html
792
793https://dev.mysql.com/doc/refman/5.0/en/create-table.html
794
795Index Parsers
796~~~~~~~~~~~~~
797
798CREATE FULLTEXT INDEX in MySQL also supports a "WITH PARSER" option. This
799is available using the keyword argument ``mysql_with_parser``::
800
801 Index(
802 "my_index",
803 my_table.c.data,
804 mysql_prefix="FULLTEXT",
805 mysql_with_parser="ngram",
806 mariadb_prefix="FULLTEXT",
807 mariadb_with_parser="ngram",
808 )
809
810.. versionadded:: 1.3
811
812
813.. _mysql_foreign_keys:
814
815MySQL / MariaDB Foreign Keys
816-----------------------------
817
818MySQL and MariaDB's behavior regarding foreign keys has some important caveats.
819
820Foreign Key Arguments to Avoid
821~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
822
823Neither MySQL nor MariaDB support the foreign key arguments "DEFERRABLE", "INITIALLY",
824or "MATCH". Using the ``deferrable`` or ``initially`` keyword argument with
825:class:`_schema.ForeignKeyConstraint` or :class:`_schema.ForeignKey`
826will have the effect of
827these keywords being rendered in a DDL expression, which will then raise an
828error on MySQL or MariaDB. In order to use these keywords on a foreign key while having
829them ignored on a MySQL / MariaDB backend, use a custom compile rule::
830
831 from sqlalchemy.ext.compiler import compiles
832 from sqlalchemy.schema import ForeignKeyConstraint
833
834
835 @compiles(ForeignKeyConstraint, "mysql", "mariadb")
836 def process(element, compiler, **kw):
837 element.deferrable = element.initially = None
838 return compiler.visit_foreign_key_constraint(element, **kw)
839
840The "MATCH" keyword is in fact more insidious, and is explicitly disallowed
841by SQLAlchemy in conjunction with the MySQL or MariaDB backends. This argument is
842silently ignored by MySQL / MariaDB, but in addition has the effect of ON UPDATE and ON
843DELETE options also being ignored by the backend. Therefore MATCH should
844never be used with the MySQL / MariaDB backends; as is the case with DEFERRABLE and
845INITIALLY, custom compilation rules can be used to correct a
846ForeignKeyConstraint at DDL definition time.
847
848Reflection of Foreign Key Constraints
849~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
850
851Not all MySQL / MariaDB storage engines support foreign keys. When using the
852very common ``MyISAM`` MySQL storage engine, the information loaded by table
853reflection will not include foreign keys. For these tables, you may supply a
854:class:`~sqlalchemy.ForeignKeyConstraint` at reflection time::
855
856 Table(
857 "mytable",
858 metadata,
859 ForeignKeyConstraint(["other_id"], ["othertable.other_id"]),
860 autoload_with=engine,
861 )
862
863.. seealso::
864
865 :ref:`mysql_storage_engines`
866
867.. _mysql_unique_constraints:
868
869MySQL / MariaDB Unique Constraints and Reflection
870----------------------------------------------------
871
872SQLAlchemy supports both the :class:`.Index` construct with the
873flag ``unique=True``, indicating a UNIQUE index, as well as the
874:class:`.UniqueConstraint` construct, representing a UNIQUE constraint.
875Both objects/syntaxes are supported by MySQL / MariaDB when emitting DDL to create
876these constraints. However, MySQL / MariaDB does not have a unique constraint
877construct that is separate from a unique index; that is, the "UNIQUE"
878constraint on MySQL / MariaDB is equivalent to creating a "UNIQUE INDEX".
879
880When reflecting these constructs, the
881:meth:`_reflection.Inspector.get_indexes`
882and the :meth:`_reflection.Inspector.get_unique_constraints`
883methods will **both**
884return an entry for a UNIQUE index in MySQL / MariaDB. However, when performing
885full table reflection using ``Table(..., autoload_with=engine)``,
886the :class:`.UniqueConstraint` construct is
887**not** part of the fully reflected :class:`_schema.Table` construct under any
888circumstances; this construct is always represented by a :class:`.Index`
889with the ``unique=True`` setting present in the :attr:`_schema.Table.indexes`
890collection.
891
892
893TIMESTAMP / DATETIME issues
894---------------------------
895
896.. _mysql_timestamp_onupdate:
897
898Rendering ON UPDATE CURRENT TIMESTAMP for MySQL / MariaDB's explicit_defaults_for_timestamp
899~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
900
901MySQL / MariaDB have historically expanded the DDL for the :class:`_types.TIMESTAMP`
902datatype into the phrase "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
903CURRENT_TIMESTAMP", which includes non-standard SQL that automatically updates
904the column with the current timestamp when an UPDATE occurs, eliminating the
905usual need to use a trigger in such a case where server-side update changes are
906desired.
907
908MySQL 5.6 introduced a new flag `explicit_defaults_for_timestamp
909<https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
910#sysvar_explicit_defaults_for_timestamp>`_ which disables the above behavior,
911and in MySQL 8 this flag defaults to true, meaning in order to get a MySQL
912"on update timestamp" without changing this flag, the above DDL must be
913rendered explicitly. Additionally, the same DDL is valid for use of the
914``DATETIME`` datatype as well.
915
916SQLAlchemy's MySQL dialect does not yet have an option to generate
917MySQL's "ON UPDATE CURRENT_TIMESTAMP" clause, noting that this is not a general
918purpose "ON UPDATE" as there is no such syntax in standard SQL. SQLAlchemy's
919:paramref:`_schema.Column.server_onupdate` parameter is currently not related
920to this special MySQL behavior.
921
922To generate this DDL, make use of the :paramref:`_schema.Column.server_default`
923parameter and pass a textual clause that also includes the ON UPDATE clause::
924
925 from sqlalchemy import Table, MetaData, Column, Integer, String, TIMESTAMP
926 from sqlalchemy import text
927
928 metadata = MetaData()
929
930 mytable = Table(
931 "mytable",
932 metadata,
933 Column("id", Integer, primary_key=True),
934 Column("data", String(50)),
935 Column(
936 "last_updated",
937 TIMESTAMP,
938 server_default=text(
939 "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
940 ),
941 ),
942 )
943
944The same instructions apply to use of the :class:`_types.DateTime` and
945:class:`_types.DATETIME` datatypes::
946
947 from sqlalchemy import DateTime
948
949 mytable = Table(
950 "mytable",
951 metadata,
952 Column("id", Integer, primary_key=True),
953 Column("data", String(50)),
954 Column(
955 "last_updated",
956 DateTime,
957 server_default=text(
958 "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
959 ),
960 ),
961 )
962
963Even though the :paramref:`_schema.Column.server_onupdate` feature does not
964generate this DDL, it still may be desirable to signal to the ORM that this
965updated value should be fetched. This syntax looks like the following::
966
967 from sqlalchemy.schema import FetchedValue
968
969
970 class MyClass(Base):
971 __tablename__ = "mytable"
972
973 id = Column(Integer, primary_key=True)
974 data = Column(String(50))
975 last_updated = Column(
976 TIMESTAMP,
977 server_default=text(
978 "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
979 ),
980 server_onupdate=FetchedValue(),
981 )
982
983.. _mysql_timestamp_null:
984
985TIMESTAMP Columns and NULL
986~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
987
988MySQL historically enforces that a column which specifies the
989TIMESTAMP datatype implicitly includes a default value of
990CURRENT_TIMESTAMP, even though this is not stated, and additionally
991sets the column as NOT NULL, the opposite behavior vs. that of all
992other datatypes:
993
994.. sourcecode:: text
995
996 mysql> CREATE TABLE ts_test (
997 -> a INTEGER,
998 -> b INTEGER NOT NULL,
999 -> c TIMESTAMP,
1000 -> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
1001 -> e TIMESTAMP NULL);
1002 Query OK, 0 rows affected (0.03 sec)
1003
1004 mysql> SHOW CREATE TABLE ts_test;
1005 +---------+-----------------------------------------------------
1006 | Table | Create Table
1007 +---------+-----------------------------------------------------
1008 | ts_test | CREATE TABLE `ts_test` (
1009 `a` int(11) DEFAULT NULL,
1010 `b` int(11) NOT NULL,
1011 `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1012 `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1013 `e` timestamp NULL DEFAULT NULL
1014 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1015
1016Above, we see that an INTEGER column defaults to NULL, unless it is specified
1017with NOT NULL. But when the column is of type TIMESTAMP, an implicit
1018default of CURRENT_TIMESTAMP is generated which also coerces the column
1019to be a NOT NULL, even though we did not specify it as such.
1020
1021This behavior of MySQL can be changed on the MySQL side using the
1022`explicit_defaults_for_timestamp
1023<https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
1024#sysvar_explicit_defaults_for_timestamp>`_ configuration flag introduced in
1025MySQL 5.6. With this server setting enabled, TIMESTAMP columns behave like
1026any other datatype on the MySQL side with regards to defaults and nullability.
1027
1028However, to accommodate the vast majority of MySQL databases that do not
1029specify this new flag, SQLAlchemy emits the "NULL" specifier explicitly with
1030any TIMESTAMP column that does not specify ``nullable=False``. In order to
1031accommodate newer databases that specify ``explicit_defaults_for_timestamp``,
1032SQLAlchemy also emits NOT NULL for TIMESTAMP columns that do specify
1033``nullable=False``. The following example illustrates::
1034
1035 from sqlalchemy import MetaData, Integer, Table, Column, text
1036 from sqlalchemy.dialects.mysql import TIMESTAMP
1037
1038 m = MetaData()
1039 t = Table(
1040 "ts_test",
1041 m,
1042 Column("a", Integer),
1043 Column("b", Integer, nullable=False),
1044 Column("c", TIMESTAMP),
1045 Column("d", TIMESTAMP, nullable=False),
1046 )
1047
1048
1049 from sqlalchemy import create_engine
1050
1051 e = create_engine("mysql+mysqldb://scott:tiger@localhost/test", echo=True)
1052 m.create_all(e)
1053
1054output:
1055
1056.. sourcecode:: sql
1057
1058 CREATE TABLE ts_test (
1059 a INTEGER,
1060 b INTEGER NOT NULL,
1061 c TIMESTAMP NULL,
1062 d TIMESTAMP NOT NULL
1063 )
1064
1065""" # noqa
1066from __future__ import annotations
1067
1068from collections import defaultdict
1069from itertools import compress
1070import re
1071from typing import Any
1072from typing import Callable
1073from typing import cast
1074from typing import DefaultDict
1075from typing import Dict
1076from typing import List
1077from typing import NoReturn
1078from typing import Optional
1079from typing import overload
1080from typing import Sequence
1081from typing import Tuple
1082from typing import TYPE_CHECKING
1083from typing import Union
1084
1085from . import reflection as _reflection
1086from .enumerated import ENUM
1087from .enumerated import SET
1088from .json import JSON
1089from .json import JSONIndexType
1090from .json import JSONPathType
1091from .reserved_words import RESERVED_WORDS_MARIADB
1092from .reserved_words import RESERVED_WORDS_MYSQL
1093from .types import _FloatType
1094from .types import _IntegerType
1095from .types import _MatchType
1096from .types import _NumericType
1097from .types import _StringType
1098from .types import BIGINT
1099from .types import BIT
1100from .types import CHAR
1101from .types import DATETIME
1102from .types import DECIMAL
1103from .types import DOUBLE
1104from .types import FLOAT
1105from .types import INTEGER
1106from .types import LONGBLOB
1107from .types import LONGTEXT
1108from .types import MEDIUMBLOB
1109from .types import MEDIUMINT
1110from .types import MEDIUMTEXT
1111from .types import NCHAR
1112from .types import NUMERIC
1113from .types import NVARCHAR
1114from .types import REAL
1115from .types import SMALLINT
1116from .types import TEXT
1117from .types import TIME
1118from .types import TIMESTAMP
1119from .types import TINYBLOB
1120from .types import TINYINT
1121from .types import TINYTEXT
1122from .types import VARCHAR
1123from .types import YEAR
1124from ... import exc
1125from ... import literal_column
1126from ... import schema as sa_schema
1127from ... import sql
1128from ... import util
1129from ...engine import cursor as _cursor
1130from ...engine import default
1131from ...engine import reflection
1132from ...engine.reflection import ReflectionDefaults
1133from ...sql import coercions
1134from ...sql import compiler
1135from ...sql import elements
1136from ...sql import functions
1137from ...sql import operators
1138from ...sql import roles
1139from ...sql import sqltypes
1140from ...sql import util as sql_util
1141from ...sql import visitors
1142from ...sql.compiler import InsertmanyvaluesSentinelOpts
1143from ...sql.compiler import SQLCompiler
1144from ...sql.schema import SchemaConst
1145from ...types import BINARY
1146from ...types import BLOB
1147from ...types import BOOLEAN
1148from ...types import DATE
1149from ...types import LargeBinary
1150from ...types import UUID
1151from ...types import VARBINARY
1152from ...util import topological
1153
1154if TYPE_CHECKING:
1155
1156 from ...dialects.mysql import expression
1157 from ...dialects.mysql.dml import OnDuplicateClause
1158 from ...engine.base import Connection
1159 from ...engine.cursor import CursorResult
1160 from ...engine.interfaces import DBAPIConnection
1161 from ...engine.interfaces import DBAPICursor
1162 from ...engine.interfaces import DBAPIModule
1163 from ...engine.interfaces import IsolationLevel
1164 from ...engine.interfaces import PoolProxiedConnection
1165 from ...engine.interfaces import ReflectedCheckConstraint
1166 from ...engine.interfaces import ReflectedColumn
1167 from ...engine.interfaces import ReflectedForeignKeyConstraint
1168 from ...engine.interfaces import ReflectedIndex
1169 from ...engine.interfaces import ReflectedPrimaryKeyConstraint
1170 from ...engine.interfaces import ReflectedTableComment
1171 from ...engine.interfaces import ReflectedUniqueConstraint
1172 from ...engine.row import Row
1173 from ...engine.url import URL
1174 from ...schema import Table
1175 from ...sql import ddl
1176 from ...sql import selectable
1177 from ...sql.dml import _DMLTableElement
1178 from ...sql.dml import Delete
1179 from ...sql.dml import Update
1180 from ...sql.dml import ValuesBase
1181 from ...sql.functions import aggregate_strings
1182 from ...sql.functions import random
1183 from ...sql.functions import rollup
1184 from ...sql.functions import sysdate
1185 from ...sql.schema import IdentityOptions
1186 from ...sql.schema import Sequence as Sequence_SchemaItem
1187 from ...sql.type_api import TypeEngine
1188 from ...sql.visitors import ExternallyTraversible
1189
1190
1191SET_RE = re.compile(
1192 r"\s*SET\s+(?:(?:GLOBAL|SESSION)\s+)?\w", re.I | re.UNICODE
1193)
1194
1195# old names
1196MSTime = TIME
1197MSSet = SET
1198MSEnum = ENUM
1199MSLongBlob = LONGBLOB
1200MSMediumBlob = MEDIUMBLOB