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