U ÂÏ aÚã"@sdZddlmZddlmZddlmZddlZddlm Z ddlm Z ddl m Z d d l mZd d lmZd d lmZd d lmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlmZd dlm Z d dlm!Z!d dlm"Z"d dlm#Z#d dlm$Z$d dlm%Z%d dlm&Z&d dlm'Z'd d lm(Z(d d!lm)Z)d d"lm*Z*d d#lm+Z+d d$lm,Z,d d%lm-Z-d d&lm.Z.d d'lm/Z/d d(lm0Z0d d)lm1Z1d d*lm2Z2d d+lm3Z3d d,lm4Z4d d-lm5Z5d d.lm6Z6d/d0l m7Z7d/d1l m8Z8d/d2l m9Z:d/d3l m;Z;d/d4l mZ<d/d5l m=Z=d/d6l>m?Z?d/d l>mZd/d7l;m@Z@d/d8l;mAZAd/d9l;mBZBd/d:l;mCZCd/d;l;mDZDd/dlmIZId/d?lmJZJd/d@lmKZKd/dAlmLZLd/dBlmMZMd/dCl=mNZNeOdDdEdFdGdHdIdJdKdLdMdNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpdqdrdsdtdudvdwdxdydzd{d|d}d~dd€dd‚dƒd„d…d†d‡dˆd‰dŠd‹dŒddŽddd‘d’d“d”d•d–d—d˜d™dšd›dœddždŸd d¡d¢d£d¤d¥d¦d§d¨d©dªd«d¬d­d®d¯d°d±d²d³d´dµd¶d·d¸d¹dºd»d¼d½d¾d¿dÀdÁdÂdÃdÄdÅdÆdÇdÈdÉdÊdÊdËdÌdÍdÎdÏdÐdÑdÒdÓdÔdÕdÖd×d×dØdÙdÚdÛdÜdÝdÞdßdàdádâdãdädådædçdèdédêdëdìdídîdïdðdñdòdódôdõdöd÷dødùdúdûdüdýdþdÿddddddddddddd d d d d ddddddddddddddddddd d!d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdGdHdIdJdKdLdMdNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_g!ƒZPe Qd`ejRejSB¡ZTe QdaejRejSB¡ZUe0ZVeZWeZXe%ZYe'ZZe2Z[eJZ\eIZ]eMZ^e*Z_e,Z`eZae5Zbe&Zce)Zde4Zee/Zfe6Zge1ZheZie.Zje3Zke(ZleZme+Zne!Zoe"Zpe-Zqe#Zre$Zseeeeeee`` for each new connection. For the special AUTOCOMMIT isolation level, DBAPI-specific techniques are used. To set isolation level using :func:`_sa.create_engine`:: engine = create_engine( "mysql://scott:tiger@localhost/test", isolation_level="READ UNCOMMITTED" ) To set using per-connection execution options:: connection = engine.connect() connection = connection.execution_options( isolation_level="READ COMMITTED" ) Valid values for ``isolation_level`` include: * ``READ COMMITTED`` * ``READ UNCOMMITTED`` * ``REPEATABLE READ`` * ``SERIALIZABLE`` * ``AUTOCOMMIT`` The special ``AUTOCOMMIT`` value makes use of the various "autocommit" attributes provided by specific DBAPIs, and is currently supported by MySQLdb, MySQL-Client, MySQL-Connector Python, and PyMySQL. Using it, the database connection will return true for the value of ``SELECT @@autocommit;``. .. seealso:: :ref:`dbapi_autocommit` AUTO_INCREMENT Behavior ----------------------- When creating tables, SQLAlchemy will automatically set ``AUTO_INCREMENT`` on the first :class:`.Integer` primary key column which is not marked as a foreign key:: >>> t = Table('mytable', metadata, ... Column('mytable_id', Integer, primary_key=True) ... ) >>> t.create() CREATE TABLE mytable ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) You can disable this behavior by passing ``False`` to the :paramref:`_schema.Column.autoincrement` argument of :class:`_schema.Column`. This flag can also be used to enable auto-increment on a secondary column in a multi-column key for some storage engines:: Table('mytable', metadata, Column('gid', Integer, primary_key=True, autoincrement=False), Column('id', Integer, primary_key=True) ) .. _mysql_ss_cursors: Server Side Cursors ------------------- Server-side cursor support is available for the mysqlclient, PyMySQL, mariadbconnector dialects and may also be available in others. This makes use of either the "buffered=True/False" flag if available or by using a class such as ``MySQLdb.cursors.SSCursor`` or ``pymysql.cursors.SSCursor`` internally. Server side cursors are enabled on a per-statement basis by using the :paramref:`.Connection.execution_options.stream_results` connection execution option:: with engine.connect() as conn: result = conn.execution_options(stream_results=True).execute(text("select * from table")) Note that some kinds of SQL statements may not be supported with server side cursors; generally, only SQL statements that return rows should be used with this option. .. deprecated:: 1.4 The dialect-level server_side_cursors flag is deprecated and will be removed in a future release. Please use the :paramref:`_engine.Connection.stream_results` execution option for unbuffered cursor support. .. seealso:: :ref:`engine_stream_results` .. _mysql_unicode: Unicode ------- Charset Selection ~~~~~~~~~~~~~~~~~ Most MySQL / MariaDB DBAPIs offer the option to set the client character set for a connection. This is typically delivered using the ``charset`` parameter in the URL, such as:: e = create_engine( "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4") This charset is the **client character set** for the connection. Some MySQL DBAPIs will default this to a value such as ``latin1``, and some will make use of the ``default-character-set`` setting in the ``my.cnf`` file as well. Documentation for the DBAPI in use should be consulted for specific behavior. The encoding used for Unicode has traditionally been ``'utf8'``. However, for MySQL versions 5.5.3 and MariaDB 5.5 on forward, a new MySQL-specific encoding ``'utf8mb4'`` has been introduced, and as of MySQL 8.0 a warning is emitted by the server if plain ``utf8`` is specified within any server-side directives, replaced with ``utf8mb3``. The rationale for this new encoding is due to the fact that MySQL's legacy utf-8 encoding only supports codepoints up to three bytes instead of four. Therefore, when communicating with a MySQL or MariaDB database that includes codepoints more than three bytes in size, this new charset is preferred, if supported by both the database as well as the client DBAPI, as in:: e = create_engine( "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4") All modern DBAPIs should support the ``utf8mb4`` charset. In order to use ``utf8mb4`` encoding for a schema that was created with legacy ``utf8``, changes to the MySQL/MariaDB schema and/or server configuration may be required. .. seealso:: `The utf8mb4 Character Set \ `_ - \ in the MySQL documentation .. _mysql_binary_introducer: Dealing with Binary Data Warnings and Unicode ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ MySQL versions 5.6, 5.7 and later (not MariaDB at the time of this writing) now emit a warning when attempting to pass binary data to the database, while a character set encoding is also in place, when the binary data itself is not valid for that encoding:: default.py:509: Warning: (1300, "Invalid utf8mb4 character string: 'F9876A'") cursor.execute(statement, parameters) This warning is due to the fact that the MySQL client library is attempting to interpret the binary string as a unicode object even if a datatype such as :class:`.LargeBinary` is in use. To resolve this, the SQL statement requires a binary "character set introducer" be present before any non-NULL value that renders like this:: INSERT INTO table (data) VALUES (_binary %s) These character set introducers are provided by the DBAPI driver, assuming the use of mysqlclient or PyMySQL (both of which are recommended). Add the query string parameter ``binary_prefix=true`` to the URL to repair this warning:: # mysqlclient engine = create_engine( "mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true") # PyMySQL engine = create_engine( "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true") The ``binary_prefix`` flag may or may not be supported by other MySQL drivers. SQLAlchemy itself cannot render this ``_binary`` prefix reliably, as it does not work with the NULL value, which is valid to be sent as a bound parameter. As the MySQL driver renders parameters directly into the SQL string, it's the most efficient place for this additional keyword to be passed. .. seealso:: `Character set introducers `_ - on the MySQL website ANSI Quoting Style ------------------ MySQL / MariaDB feature two varieties of identifier "quoting style", one using backticks and the other using quotes, e.g. ```some_identifier``` vs. ``"some_identifier"``. All MySQL dialects detect which version is in use by checking the value of ``sql_mode`` when a connection is first established with a particular :class:`_engine.Engine`. This quoting style comes into play when rendering table and column names as well as when reflecting existing database structures. The detection is entirely automatic and no special configuration is needed to use either quoting style. MySQL / MariaDB SQL Extensions ------------------------------ Many of the MySQL / MariaDB SQL extensions are handled through SQLAlchemy's generic function and operator support:: table.select(table.c.password==func.md5('plaintext')) table.select(table.c.username.op('regexp')('^[a-d]')) And of course any valid SQL statement can be executed as a string as well. Some limited direct support for MySQL / MariaDB extensions to SQL is currently available. * INSERT..ON DUPLICATE KEY UPDATE: See :ref:`mysql_insert_on_duplicate_key_update` * SELECT pragma, use :meth:`_expression.Select.prefix_with` and :meth:`_query.Query.prefix_with`:: select(...).prefix_with(['HIGH_PRIORITY', 'SQL_SMALL_RESULT']) * UPDATE with LIMIT:: update(..., mysql_limit=10, mariadb_limit=10) * optimizer hints, use :meth:`_expression.Select.prefix_with` and :meth:`_query.Query.prefix_with`:: select(...).prefix_with("/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */") * index hints, use :meth:`_expression.Select.with_hint` and :meth:`_query.Query.with_hint`:: select(...).with_hint(some_table, "USE INDEX xyz") * MATCH operator support:: from sqlalchemy.dialects.mysql import match select(...).where(match(col1, col2, against="some expr").in_boolean_mode()) .. seealso:: :class:`_mysql.match` .. _mysql_insert_on_duplicate_key_update: INSERT...ON DUPLICATE KEY UPDATE (Upsert) ------------------------------------------ MySQL / MariaDB allow "upserts" (update or insert) of rows into a table via the ``ON DUPLICATE KEY UPDATE`` clause of the ``INSERT`` statement. A candidate row will only be inserted if that row does not match an existing primary or unique key in the table; otherwise, an UPDATE will be performed. The statement allows for separate specification of the values to INSERT versus the values for UPDATE. SQLAlchemy provides ``ON DUPLICATE KEY UPDATE`` support via the MySQL-specific :func:`.mysql.insert()` function, which provides the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`: .. sourcecode:: pycon+sql >>> from sqlalchemy.dialects.mysql import insert >>> insert_stmt = insert(my_table).values( ... id='some_existing_id', ... data='inserted value') >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( ... data=insert_stmt.inserted.data, ... status='U' ... ) >>> print(on_duplicate_key_stmt) {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) ON DUPLICATE KEY UPDATE data = VALUES(data), status = %s Unlike PostgreSQL's "ON CONFLICT" phrase, the "ON DUPLICATE KEY UPDATE" phrase will always match on any primary key or unique key, and will always perform an UPDATE if there's a match; there are no options for it to raise an error or to skip performing an UPDATE. ``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already existing row, using any combination of new values as well as values from the proposed insertion. These values are normally specified using keyword arguments passed to the :meth:`_mysql.Insert.on_duplicate_key_update` given column key values (usually the name of the column, unless it specifies :paramref:`_schema.Column.key` ) as keys and literal or SQL expressions as values: .. sourcecode:: pycon+sql >>> insert_stmt = insert(my_table).values( ... id='some_existing_id', ... data='inserted value') >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( ... data="some data", ... updated_at=func.current_timestamp(), ... ) >>> print(on_duplicate_key_stmt) {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP In a manner similar to that of :meth:`.UpdateBase.values`, other parameter forms are accepted, including a single dictionary: .. sourcecode:: pycon+sql >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( ... {"data": "some data", "updated_at": func.current_timestamp()}, ... ) as well as a list of 2-tuples, which will automatically provide a parameter-ordered UPDATE statement in a manner similar to that described at :ref:`updates_order_parameters`. Unlike the :class:`_expression.Update` object, no special flag is needed to specify the intent since the argument form is this context is unambiguous: .. sourcecode:: pycon+sql >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( ... [ ... ("data", "some data"), ... ("updated_at", func.current_timestamp()), ... ] ... ) >>> print(on_duplicate_key_stmt) {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP .. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within MySQL ON DUPLICATE KEY UPDATE .. warning:: The :meth:`_mysql.Insert.on_duplicate_key_update` method does **not** take into account Python-side default UPDATE values or generation functions, e.g. e.g. those specified using :paramref:`_schema.Column.onupdate`. These values will not be exercised for an ON DUPLICATE KEY style of UPDATE, unless they are manually specified explicitly in the parameters. In order to refer to the proposed insertion row, the special alias :attr:`_mysql.Insert.inserted` is available as an attribute on the :class:`_mysql.Insert` object; this object is a :class:`_expression.ColumnCollection` which contains all columns of the target table: .. sourcecode:: pycon+sql >>> stmt = insert(my_table).values( ... id='some_id', ... data='inserted value', ... author='jlh') >>> do_update_stmt = stmt.on_duplicate_key_update( ... data="updated value", ... author=stmt.inserted.author ... ) >>> print(do_update_stmt) {opensql}INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE data = %s, author = VALUES(author) When rendered, the "inserted" namespace will produce the expression ``VALUES()``. .. versionadded:: 1.2 Added support for MySQL ON DUPLICATE KEY UPDATE clause rowcount Support ---------------- SQLAlchemy standardizes the DBAPI ``cursor.rowcount`` attribute to be the usual definition of "number of rows matched by an UPDATE or DELETE" statement. This is in contradiction to the default setting on most MySQL DBAPI drivers, which is "number of rows actually modified/deleted". For this reason, the SQLAlchemy MySQL dialects always add the ``constants.CLIENT.FOUND_ROWS`` flag, or whatever is equivalent for the target dialect, upon connection. This setting is currently hardcoded. .. seealso:: :attr:`_engine.CursorResult.rowcount` .. _mysql_indexes: MySQL / MariaDB- Specific Index Options ----------------------------------------- MySQL and MariaDB-specific extensions to the :class:`.Index` construct are available. Index Length ~~~~~~~~~~~~~ MySQL and MariaDB both provide an option to create index entries with a certain length, where "length" refers to the number of characters or bytes in each value which will become part of the index. SQLAlchemy provides this feature via the ``mysql_length`` and/or ``mariadb_length`` parameters:: Index('my_index', my_table.c.data, mysql_length=10, mariadb_length=10) Index('a_b_idx', my_table.c.a, my_table.c.b, mysql_length={'a': 4, 'b': 9}) Index('a_b_idx', my_table.c.a, my_table.c.b, mariadb_length={'a': 4, 'b': 9}) Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. The value passed to the keyword argument *must* be either an integer (and, thus, specify the same prefix length value for all columns of the index) or a dict in which keys are column names and values are prefix length values for corresponding columns. MySQL and MariaDB only allow a length for a column of an index if it is for a CHAR, VARCHAR, TEXT, BINARY, VARBINARY and BLOB. Index Prefixes ~~~~~~~~~~~~~~ MySQL storage engines permit you to specify an index prefix when creating an index. SQLAlchemy provides this feature via the ``mysql_prefix`` parameter on :class:`.Index`:: Index('my_index', my_table.c.data, mysql_prefix='FULLTEXT') The value passed to the keyword argument will be simply passed through to the underlying CREATE INDEX, so it *must* be a valid index prefix for your MySQL storage engine. .. versionadded:: 1.1.5 .. seealso:: `CREATE INDEX `_ - MySQL documentation Index Types ~~~~~~~~~~~~~ Some MySQL storage engines permit you to specify an index type when creating an index or primary key constraint. SQLAlchemy provides this feature via the ``mysql_using`` parameter on :class:`.Index`:: Index('my_index', my_table.c.data, mysql_using='hash', mariadb_using='hash') As well as the ``mysql_using`` parameter on :class:`.PrimaryKeyConstraint`:: PrimaryKeyConstraint("data", mysql_using='hash', mariadb_using='hash') The value passed to the keyword argument will be simply passed through to the underlying CREATE INDEX or PRIMARY KEY clause, so it *must* be a valid index type for your MySQL storage engine. More information can be found at: https://dev.mysql.com/doc/refman/5.0/en/create-index.html https://dev.mysql.com/doc/refman/5.0/en/create-table.html Index Parsers ~~~~~~~~~~~~~ CREATE FULLTEXT INDEX in MySQL also supports a "WITH PARSER" option. This is available using the keyword argument ``mysql_with_parser``:: Index( 'my_index', my_table.c.data, mysql_prefix='FULLTEXT', mysql_with_parser="ngram", mariadb_prefix='FULLTEXT', mariadb_with_parser="ngram", ) .. versionadded:: 1.3 .. _mysql_foreign_keys: MySQL / MariaDB Foreign Keys ----------------------------- MySQL and MariaDB's behavior regarding foreign keys has some important caveats. Foreign Key Arguments to Avoid ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Neither MySQL nor MariaDB support the foreign key arguments "DEFERRABLE", "INITIALLY", or "MATCH". Using the ``deferrable`` or ``initially`` keyword argument with :class:`_schema.ForeignKeyConstraint` or :class:`_schema.ForeignKey` will have the effect of these keywords being rendered in a DDL expression, which will then raise an error on MySQL or MariaDB. In order to use these keywords on a foreign key while having them ignored on a MySQL / MariaDB backend, use a custom compile rule:: from sqlalchemy.ext.compiler import compiles from sqlalchemy.schema import ForeignKeyConstraint @compiles(ForeignKeyConstraint, "mysql", "mariadb") def process(element, compiler, **kw): element.deferrable = element.initially = None return compiler.visit_foreign_key_constraint(element, **kw) The "MATCH" keyword is in fact more insidious, and is explicitly disallowed by SQLAlchemy in conjunction with the MySQL or MariaDB backends. This argument is silently ignored by MySQL / MariaDB, but in addition has the effect of ON UPDATE and ON DELETE options also being ignored by the backend. Therefore MATCH should never be used with the MySQL / MariaDB backends; as is the case with DEFERRABLE and INITIALLY, custom compilation rules can be used to correct a ForeignKeyConstraint at DDL definition time. Reflection of Foreign Key Constraints ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Not all MySQL / MariaDB storage engines support foreign keys. When using the very common ``MyISAM`` MySQL storage engine, the information loaded by table reflection will not include foreign keys. For these tables, you may supply a :class:`~sqlalchemy.ForeignKeyConstraint` at reflection time:: Table('mytable', metadata, ForeignKeyConstraint(['other_id'], ['othertable.other_id']), autoload_with=engine ) .. seealso:: :ref:`mysql_storage_engines` .. _mysql_unique_constraints: MySQL / MariaDB Unique Constraints and Reflection ---------------------------------------------------- SQLAlchemy supports both the :class:`.Index` construct with the flag ``unique=True``, indicating a UNIQUE index, as well as the :class:`.UniqueConstraint` construct, representing a UNIQUE constraint. Both objects/syntaxes are supported by MySQL / MariaDB when emitting DDL to create these constraints. However, MySQL / MariaDB does not have a unique constraint construct that is separate from a unique index; that is, the "UNIQUE" constraint on MySQL / MariaDB is equivalent to creating a "UNIQUE INDEX". When reflecting these constructs, the :meth:`_reflection.Inspector.get_indexes` and the :meth:`_reflection.Inspector.get_unique_constraints` methods will **both** return an entry for a UNIQUE index in MySQL / MariaDB. However, when performing full table reflection using ``Table(..., autoload_with=engine)``, the :class:`.UniqueConstraint` construct is **not** part of the fully reflected :class:`_schema.Table` construct under any circumstances; this construct is always represented by a :class:`.Index` with the ``unique=True`` setting present in the :attr:`_schema.Table.indexes` collection. TIMESTAMP / DATETIME issues --------------------------- .. _mysql_timestamp_onupdate: Rendering ON UPDATE CURRENT TIMESTAMP for MySQL / MariaDB's explicit_defaults_for_timestamp ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ MySQL / MariaDB have historically expanded the DDL for the :class:`_types.TIMESTAMP` datatype into the phrase "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", which includes non-standard SQL that automatically updates the column with the current timestamp when an UPDATE occurs, eliminating the usual need to use a trigger in such a case where server-side update changes are desired. MySQL 5.6 introduced a new flag `explicit_defaults_for_timestamp `_ which disables the above behavior, and in MySQL 8 this flag defaults to true, meaning in order to get a MySQL "on update timestamp" without changing this flag, the above DDL must be rendered explicitly. Additionally, the same DDL is valid for use of the ``DATETIME`` datatype as well. SQLAlchemy's MySQL dialect does not yet have an option to generate MySQL's "ON UPDATE CURRENT_TIMESTAMP" clause, noting that this is not a general purpose "ON UPDATE" as there is no such syntax in standard SQL. SQLAlchemy's :paramref:`_schema.Column.server_onupdate` parameter is currently not related to this special MySQL behavior. To generate this DDL, make use of the :paramref:`_schema.Column.server_default` parameter and pass a textual clause that also includes the ON UPDATE clause:: from sqlalchemy import Table, MetaData, Column, Integer, String, TIMESTAMP from sqlalchemy import text metadata = MetaData() mytable = Table( "mytable", metadata, Column('id', Integer, primary_key=True), Column('data', String(50)), Column( 'last_updated', TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP") ) ) The same instructions apply to use of the :class:`_types.DateTime` and :class:`_types.DATETIME` datatypes:: from sqlalchemy import DateTime mytable = Table( "mytable", metadata, Column('id', Integer, primary_key=True), Column('data', String(50)), Column( 'last_updated', DateTime, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP") ) ) Even though the :paramref:`_schema.Column.server_onupdate` feature does not generate this DDL, it still may be desirable to signal to the ORM that this updated value should be fetched. This syntax looks like the following:: from sqlalchemy.schema import FetchedValue class MyClass(Base): __tablename__ = 'mytable' id = Column(Integer, primary_key=True) data = Column(String(50)) last_updated = Column( TIMESTAMP, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), server_onupdate=FetchedValue() ) .. _mysql_timestamp_null: TIMESTAMP Columns and NULL ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ MySQL historically enforces that a column which specifies the TIMESTAMP datatype implicitly includes a default value of CURRENT_TIMESTAMP, even though this is not stated, and additionally sets the column as NOT NULL, the opposite behavior vs. that of all other datatypes:: mysql> CREATE TABLE ts_test ( -> a INTEGER, -> b INTEGER NOT NULL, -> c TIMESTAMP, -> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -> e TIMESTAMP NULL); Query OK, 0 rows affected (0.03 sec) mysql> SHOW CREATE TABLE ts_test; +---------+----------------------------------------------------- | Table | Create Table +---------+----------------------------------------------------- | ts_test | CREATE TABLE `ts_test` ( `a` int(11) DEFAULT NULL, `b` int(11) NOT NULL, `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `e` timestamp NULL DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Above, we see that an INTEGER column defaults to NULL, unless it is specified with NOT NULL. But when the column is of type TIMESTAMP, an implicit default of CURRENT_TIMESTAMP is generated which also coerces the column to be a NOT NULL, even though we did not specify it as such. This behavior of MySQL can be changed on the MySQL side using the `explicit_defaults_for_timestamp `_ configuration flag introduced in MySQL 5.6. With this server setting enabled, TIMESTAMP columns behave like any other datatype on the MySQL side with regards to defaults and nullability. However, to accommodate the vast majority of MySQL databases that do not specify this new flag, SQLAlchemy emits the "NULL" specifier explicitly with any TIMESTAMP column that does not specify ``nullable=False``. In order to accommodate newer databases that specify ``explicit_defaults_for_timestamp``, SQLAlchemy also emits NOT NULL for TIMESTAMP columns that do specify ``nullable=False``. The following example illustrates:: from sqlalchemy import MetaData, Integer, Table, Column, text from sqlalchemy.dialects.mysql import TIMESTAMP m = MetaData() t = Table('ts_test', m, Column('a', Integer), Column('b', Integer, nullable=False), Column('c', TIMESTAMP), Column('d', TIMESTAMP, nullable=False) ) from sqlalchemy import create_engine e = create_engine("mysql://scott:tiger@localhost/test", echo=True) m.create_all(e) output:: CREATE TABLE ts_test ( a INTEGER, b INTEGER NOT NULL, c TIMESTAMP NULL, d TIMESTAMP NOT NULL ) .. versionchanged:: 1.0.0 - SQLAlchemy now renders NULL or NOT NULL in all cases for TIMESTAMP columns, to accommodate ``explicit_defaults_for_timestamp``. Prior to this version, it will not render "NOT NULL" for a TIMESTAMP column that is ``nullable=False``. é)Úarray)Ú defaultdict)ÚcompressN)Úliteral_column)Útext)Úvisitorsé)Ú reflection)ÚENUM)ÚSET)ÚJSON)Ú JSONIndexType)Ú JSONPathType)Ú _FloatType)Ú _IntegerType)Ú _MatchType)Ú _NumericType)Ú _StringType)ÚBIGINT)ÚBIT)ÚCHAR)ÚDATETIME)ÚDECIMAL)ÚDOUBLE)ÚFLOAT)ÚINTEGER)ÚLONGBLOB)ÚLONGTEXT)Ú MEDIUMBLOB)Ú MEDIUMINT)Ú MEDIUMTEXT)ÚNCHAR)ÚNUMERIC)ÚNVARCHAR)ÚREAL)ÚSMALLINT)ÚTEXT)ÚTIME)Ú TIMESTAMP)ÚTINYBLOB)ÚTINYINT)ÚTINYTEXT)ÚVARCHAR)ÚYEARé)Úexc)Úlog)Úschema)Úsql)Útypes)Úutil)Údefault)Ú coercions)Úcompiler)Úelements)Ú functions)Ú operators)Úroles)ÚUnicode)ÚBINARY)ÚBLOB)ÚBOOLEAN)ÚDATE)Ú VARBINARY)Ú topologicalZ accessibleÚactionÚaddZadminÚallZalterZanalyzeÚandrÚasZascZ asensitiveZbeforeZbetweenÚbigintÚbinaryÚblobZbothZbyÚcallZcascadeZcaseZchangeÚcharÚ characterÚcheckZcollateÚcolumnÚcolumnsÚ conditionÚ constraintÚcontinueÚconvertÚcreateZcrossZcubeZ cume_distZ current_dateÚ current_timeZcurrent_timestampZ current_userÚcursorZdatabaseZ databasesZday_hourZday_microsecondZ day_minuteZ day_secondÚdecÚdecimalZdeclarer5ZdelayedÚdeleteÚdescZdescribeZ deterministicZdistinctZ distinctrowÚdivÚdoubleÚdropZdualZeachÚelseZelseifÚemptyZenclosedÚescapedÚexceptÚexistsÚexitÚexplainÚfalseÚfetchÚfieldsZ first_valueÚfloatZfloat4Zfloat8ÚforÚforceÚforeignÚfromZfulltextÚfunctionZgeneralÚ generatedÚgetZgrantÚgroupÚgroupingÚgroupsZhavingZ high_priorityZhour_microsecondZ hour_minuteZ hour_secondÚifÚignoreZignore_server_idsÚinÚindexÚinfileÚinnerZinoutZ insensitiveÚinsertÚintZint1Zint2Zint3Zint4Zint8ÚintegerÚintervalZintoZio_after_gtidsZio_before_gtidsÚisZiterateÚjoinZ json_tableÚkeyÚkeysÚkillÚ last_valueZlateralÚleadingZleaveÚleftÚlevelZlikeÚlimitZlinearÚlinesÚloadÚ localtimeZlocaltimestampÚlockÚlongÚlongblobÚlongtextZloopZ low_priorityZ master_bindZmaster_heartbeat_periodZmaster_ssl_verify_server_certÚmatchZmaxvalueÚ mediumblobÚ mediumintÚ mediumtextÚmemberZ middleintZminute_microsecondZ minute_secondÚmodÚmodeZmodifiesZnaturalZno_write_to_binlogÚnotZ nth_valueZntileÚnullÚnumericÚofÚonZone_shotÚoptimizeZoptimizer_costsÚoptionZ optionallyÚorÚorderÚoutÚouterÚoutfileZoverÚ partitionZ percent_rankZpersistZ persist_onlyÚ precisionZprimaryZ privilegesZ procedureÚpurgeÚrangeZrankÚreadZ read_onlyZ read_writeZreadsÚrealÚ recursiveZ referencesÚregexpÚreleaseÚrenameÚrepeatÚreplaceÚrequireZresignalZrestrictÚreturnZrevokeÚrightZrlikeÚroleÚrowZ row_numberÚrowsr1ZschemasZsecond_microsecondÚselectZ sensitiveÚ separatorÚsetÚshowÚsignalZslowÚsmallintZsonameZspatialZspecificr2Zsql_after_gtidsZsql_before_gtidsZsql_big_resultZsql_calc_found_rowsZsql_small_resultZ sqlexceptionZsqlstateZ sqlwarningÚsslZstartingÚstatusZstoredZ straight_joinÚsystemÚtableÚtablesZ terminatedrZthenÚtimeÚtinyblobÚtinyintÚtinytextÚtoZtrailingZtriggerÚtrueZundoÚunionÚuniqueZunlockÚunsignedÚupdateÚusageZuseÚusingZutc_dateZutc_timeZ utc_timestampÚvaluesÚ varbinaryÚvarcharZ varcharacterZvaryingZvirtualÚwhenÚwhereÚwhileZwindowÚwithÚwriteÚx509ÚxorZ year_monthÚzerofillz@\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|LOAD +DATA|REPLACE)z%\s*SET\s+(?:(?:GLOBAL|SESSION)\s+)?\w)#rHrIÚbitrJÚbooleanrLÚdateÚdatetimerYr]ÚenumZfixedrir{r|ÚjsonrrŽrr‘r’ZncharZnvarcharr˜r¶r¹rr¿Ú timestamprÀrÁrÂrÌrÍÚyearc@s$eZdZdd„Zdd„Zdd„ZdS)ÚMySQLExecutionContextcCs t |¡S©N)Ú AUTOCOMMIT_REr)ÚselfÚ statement©rãú_C:\Users\vtejo\AppData\Local\Temp\pip-unpacked-wheel-nyjtotrf\sqlalchemy\dialects\mysql\base.pyÚshould_autocommit_textusz,MySQLExecutionContext.should_autocommit_textcCs"|jjr|j |jj¡Stƒ‚dSrß)ÚdialectZsupports_server_side_cursorsZ_dbapi_connectionrWZ _sscursorÚNotImplementedError©rárãrãräÚcreate_server_side_cursorxsz/MySQLExecutionContext.create_server_side_cursorcCs| d|j |¡|¡S)Nzselect nextval(%s))Z_execute_scalarÚidentifier_preparerÚformat_sequence)ráÚseqÚtype_rãrãräÚ fire_sequence~s  ÿûz#MySQLExecutionContext.fire_sequenceN)Ú__name__Ú __module__Ú __qualname__rårérîrãrãrãrärÞtsrÞcsVeZdZdZejj ¡Ze ddi¡dd„Z dd„Z dd „Z d d „Z d d „Z dd„Zdd„Zdd„Zdd„ZedƒZdZdd„Zdd„Zdd„ZdLdd „Zd!d"„Z‡fd#d$„Zd%d&„Zd'd(„Z‡fd)d*„ZdMd,d-„Zd.d/„Zd0d1„Z d2d3„Z!d4d5„Z"d6d7„Z#d8d9„Z$d:d;„Z%dd?„Z'd@dA„Z(dBdC„Z)dDdE„Z*dFdG„Z+dHdI„Z,dJdK„Z-‡Z.S)NÚ MySQLCompilerTZ millisecondsZ millisecondcCs"|jr|jdd}|jrdSdS)zlCalled when a ``SELECT`` statement has no froms, and no ``FROM`` clause is to be appended. éÿÿÿÿZ selectablez FROM DUALÚ)ÚstackZ_where_criteria)ráZstmtrãrãräÚ default_froms zMySQLCompiler.default_fromcKsd| |¡S)Nzrand%s)Zfunction_argspec©ráÚfnÚkwrãrãräÚvisit_random_funcœszMySQLCompiler.visit_random_funccKsd|j |¡S)Nz nextval(%s))Úpreparerrë)rárìrùrãrãräÚvisit_sequenceŸszMySQLCompiler.visit_sequencecKsdS)Nz SYSDATE()rãr÷rãrãräÚvisit_sysdate_func¢sz MySQLCompiler.visit_sysdate_funccKsŠ|jjtjkr2d|j|jf|Ž|j|jf|ŽfSd|j|jf|Ž|j|jf|Žf}|jjtjkrŠd|j|jf|Ž|j|jf|Žf}nð|jjtjkr |jj dk rä|jj dk räd|j|jf|Ž|j|jf|Ž|jj |jj f}n$d|j|jf|Ž|j|jf|Žf}np|jjtj kr d}nZ|jjtj krVd|j|jf|Ž|j|jf|Žf}n$d|j|jf|Ž|j|jf|Žf}|d |d S) NzJSON_EXTRACT(%s, %s)z/CASE JSON_EXTRACT(%s, %s) WHEN 'null' THEN NULLz1ELSE CAST(JSON_EXTRACT(%s, %s) AS SIGNED INTEGER)z2ELSE CAST(JSON_EXTRACT(%s, %s) AS DECIMAL(%s, %s))z2ELSE JSON_EXTRACT(%s, %s)+0.0000000000000000000000zWHEN true THEN true ELSE falsez'ELSE JSON_UNQUOTE(JSON_EXTRACT(%s, %s))zELSE JSON_EXTRACT(%s, %s)ú z END) ÚtypeZ_type_affinityÚsqltypesr Úprocessr…r°ÚIntegerÚNumericÚscaler£ÚBooleanÚString)rárIÚoperatorrùZcase_expressionÚtype_expressionrãrãräÚ _render_json_extract_from_binary¥s^þþþÿÿ ÿ þüÿÿ þÿÿþþz.MySQLCompiler._render_json_extract_from_binarycKs|j||f|ŽSrß©r ©rárIrrùrãrãräÚvisit_json_getitem_op_binaryísz*MySQLCompiler.visit_json_getitem_op_binarycKs|j||f|ŽSrßr r rãrãräÚ!visit_json_path_getitem_op_binaryðsz/MySQLCompiler.visit_json_path_getitem_op_binaryc  sVˆj‰ˆjrNdd„ˆjDƒ}t|ƒ‰‡fdd„|Dƒ‡fdd„ˆjjDƒ}nˆjj}g}‡fdd„|DƒD]‰ˆjˆj}t |¡r¬t j d|ˆj d}ˆj |  ¡dd }n0‡‡‡fd d „}t |i|¡}ˆj |  ¡dd }ˆj ˆj¡} | d | |f¡qltˆjƒtd d„|Dƒƒ} | rHt dˆjjjd dd„| Dƒ¡f¡dd |¡S)NcSsg|]}t tj|¡‘qSrã)r6Úexpectr;Z DMLColumnRole©Ú.0r€rãrãräÚ ÷sÿz?MySQLCompiler.visit_on_duplicate_key_update..cs$g|]}|ˆjjkrˆjj|‘qSrã)r½Úcr)rârãrärüs þcsg|]}|jˆkr|‘qSrã©r€©rr)Ú ordered_keysrãrärs c3s|]}|jˆjkr|VqdSrß)r€rÈ©rÚcol)Ú on_duplicaterãräÚ s z>MySQLCompiler.visit_on_duplicate_key_update..©ríF)Ú use_schemacsft|tjƒr(|jjr(| ¡}ˆj|_|St|tjƒr^|jˆjkr^t dˆj   ˆj ¡dƒ}|SdSdS)NzVALUES(ú)) Ú isinstancer8Ú BindParameterrÿZ_isnullZ_cloneZ ColumnClauser½Zinserted_aliasrrûÚquoteÚname)Úobj)rOrrárãrär­s  ÿþ ÿ þÿz.replacez%s = %scss|] }|jVqdSrßrrrãrãrär)szFAdditional column names not matching any column keys in table '%s': %sú, css|]}d|VqdS)ú'%s'Nrãrrãrãrär0szON DUPLICATE KEY UPDATE )Zcurrent_executableZ_parameter_orderingr¶r½rrÈr€r6Z _is_literalr8rrÿrÚ self_grouprZreplacement_traverserûrr Úappendr4Úwarnrâr) rárrùZparameter_orderingÚcolsZclausesÚvalZ value_textr­Z name_textZ non_matchingrã)rOrrrárâräÚvisit_on_duplicate_key_updateósBþ þü  þþÿ z+MySQLCompiler.visit_on_duplicate_key_updatecKs$d|j|jf|Ž|j|jf|ŽfS)Nzconcat(%s, %s)©rr…r°r rãrãräÚvisit_concat_op_binary6sþz$MySQLCompiler.visit_concat_op_binary))FFF)TFF)FTF)FFT)FTT)zIN BOOLEAN MODEzIN NATURAL LANGUAGE MODEzWITH QUERY EXPANSIONcKs|j||jf|ŽSrß)Úvisit_match_op_binaryr©ráÚelementrùrãrãräÚvisit_mysql_matchMszMySQLCompiler.visit_mysql_matchc KsÊ|j}| dd¡}| dd¡}| dd¡}|||f}||jkrld|d|d|f} d  | ¡} t d | ¡‚|j} |j| f|Ž} |j|jf|Ž} t |ƒr¾t |j |ƒ} | g} |   | ¡d  | ¡} d | | fS) zp Note that `mysql_boolean_mode` is enabled by default because of backward compatibility Zmysql_boolean_modeTZmysql_natural_languageFZmysql_query_expansionzin_boolean_mode=%szin_natural_language_mode=%szwith_query_expansion=%sr"zInvalid MySQL match flags: %srþzMATCH (%s) AGAINST (%s)) Ú modifiersrpÚ_match_valid_flag_combinationsrr/Ú CompileErrorr…rr°ÚanyrÚ_match_flag_expressionsÚextend) rárIrrùr0Z boolean_modeZnatural_languageZquery_expansionZflag_combinationÚflagsZ match_clauseZagainst_clauseZflag_expressionsrãrãrär,Ps0     ý þ  z#MySQLCompiler.visit_match_op_binarycCs|Srßrã)rár½rrãrãräÚget_from_hint_textzsz MySQLCompiler.get_from_hint_textNcKs2|dkr|j |j¡}t|tjƒr4|j||jf|ŽSt|tjƒrVt |ddƒrPdSdSnØt|tj ƒrfdSt|tj tj tj tjfƒrŽ|jj |¡St|tjƒrÀt|ttfƒsÀt |¡}|jj |¡St|tjƒrÐdSt|tjƒràdSt|tjƒr|jj |¡ dd ¡St|tjƒr*|jjr*|jj |¡SdSdS) NrÇFzUNSIGNED INTEGERzSIGNED INTEGERrr=r r"r)rÿZ dialect_implrærrZ TypeDecoratorÚvisit_typeclauseÚimplrÚgetattrr(rÚDateTimeÚDateÚTimeÚ type_compilerrrr r rZ_adapt_string_for_castZ_Binaryr r"r­ÚFloatÚ_support_float_cast)ráÚ typeclauserírùZadaptedrãrãrär8}sP    üþ ÿ   ÿ ÿþzMySQLCompiler.visit_typeclausecKs\| |j¡}|dkrDt d|jj |jj¡¡|j|j ¡f|ŽSd|j|jf|Ž|fS)NzMDatatype %s does not support CAST on MySQL/MariaDb; the CAST will be skipped.zCAST(%s AS %s)) rrAr4r&rær>rÿZclauser$)ráÚcastrùrírãrãräÚ visit_cast¨s þÿzMySQLCompiler.visit_castcs*tt|ƒ ||¡}|jjr&| dd¡}|S)Nú\z\\)ÚsuperròÚrender_literal_valueræÚ_backslash_escapesr­)ráÚvaluerí©Ú __class__rãrärF´s z"MySQLCompiler.render_literal_valuecKsdS)NrÄrãr-rãrãräÚ visit_true¼szMySQLCompiler.visit_truecKsdS)Nrfrãr-rãrãräÚ visit_false¿szMySQLCompiler.visit_falsec s>t|jtjƒr*tjddd|j ¡dStt|ƒj|f|ŽS)zìAdd special MySQL keywords in place of DISTINCT. .. deprecated 1.4:: this usage is deprecated. :meth:`_expression.Select.prefix_with` should be used for special keywords at the start of a SELECT. zÐSending string values for 'distinct' is deprecated in the MySQL dialect and will be removed in a future release. Please use :meth:`.Select.prefix_with` for special keywords at the start of a SELECT statementz1.4)Úversionrþ) rZ _distinctr4Ú string_typesZwarn_deprecatedÚupperrEròÚget_select_precolumns)rár´rùrIrãrärPÂsûz#MySQLCompiler.get_select_precolumnsFc Ksˆ|r|j |j|jf¡|jr$d}n|jr0d}nd}d |j|jfd|dœ|—Ž||j|jfd|dœ|—Žd|j|jfd|i|—Žf¡S) Nz FULL OUTER JOIN z LEFT OUTER JOIN z INNER JOIN rôT)ÚasfromÚ from_linterz ON rR) ÚedgesrDr…r°ÚfullZisouterrrZonclause)rárrQrRÚkwargsZ join_typerãrãräÚ visit_joinÖs:ÿÿÿÿÿÿ÷ÿzMySQLCompiler.visit_joinc sŽ|jjrd}nd}|jjrjˆjjrjt ¡}|jjD]}| t  |¡¡q2|dd  ‡‡fdd„|Dƒ¡7}|jj rz|d7}|jj rŠ|d7}|S) Nz LOCK IN SHARE MODEz FOR UPDATEz OF r"c3s&|]}ˆj|fdddœˆ—ŽVqdS)TF)ÚashintrN©r)rr½©rùrárãrärûsÿz2MySQLCompiler.for_update_clause..z NOWAITz SKIP LOCKED) Z_for_update_argr¦r™ræÚsupports_for_update_ofr4Z OrderedSetrÈÚsql_utilZsurface_selectables_onlyrZnowaitZ skip_locked)rár´rùÚtmpr¾rrãrYräÚfor_update_clauseïs þ zMySQLCompiler.for_update_clausecKs‚|j|j}}|dkr"|dkr"dS|dk rj|dkrHd|j|f|ŽdfSd|j|f|Ž|j|f|ŽfSnd|j|f|ŽfSdS)Nrôz LIMIT %s, %sZ18446744073709551615z LIMIT %s)Z _limit_clauseZ_offset_clauser)rár´rùÚ limit_clauseZ offset_clauserãrãrär^s þ þ  þzMySQLCompiler.limit_clausecCs*|j d|jjd¡}|r"d|SdSdS)Nz%s_limitzLIMIT %s)rUrprær )ráÚ update_stmtr‡rãrãräÚupdate_limit_clause0sz!MySQLCompiler.update_limit_clausec s,dˆd<d ‡‡fdd„|gt|ƒDƒ¡S)NTrQr"c3s|]}|jˆfˆŽVqdSrß©Z_compiler_dispatch©rÚtrYrãrär9sÿz5MySQLCompiler.update_tables_clause..)rÚlist)rár_Ú from_tableÚ extra_fromsrùrãrYräÚupdate_tables_clause7s þz"MySQLCompiler.update_tables_clausecKsdSrßrã)rár_rerfÚ from_hintsrùrãrãräÚupdate_from_clause>sz MySQLCompiler.update_from_clausecCsd}|r d}|j|dd|dS)z=If we have extra froms make sure we render any alias as hint.FT)rQZiscrudrWra)ráÚ delete_stmtrerfrWrãrãräÚdelete_table_clauseCsÿz!MySQLCompiler.delete_table_clausec s.dˆd<dd ‡‡‡fdd„|g|Dƒ¡S)z4Render the DELETE .. USING clause specific to MySQL.TrQzUSING r"c3s$|]}|jˆfdˆiˆ—ŽVqdS)Z fromhintsNrarb©rhrùrárãrärQsÿz9MySQLCompiler.delete_extra_from_clause..)r)rárjrerfrhrùrãrlräÚdelete_extra_from_clauseLsþz&MySQLCompiler.delete_extra_from_clausecCs6dd dd„t|ƒDƒ¡d dd„t|ƒDƒ¡dœS)NzASELECT %(outer)s FROM (SELECT %(inner)s) as _empty_set WHERE 1!=1r"css|]\}}d|VqdS)z 1 AS _in_%sNrã©rÚidxrírãrãrär[sÿz5MySQLCompiler.visit_empty_set_expr..css|]\}}d|VqdS)z_in_%sNrãrnrãrãrär_s)ryr )rÚ enumerate)ráZ element_typesrãrãräÚvisit_empty_set_exprVs þ ÿûþÿz"MySQLCompiler.visit_empty_set_exprcKsd| |j¡| |j¡fS)NzNOT (%s <=> %s)r*r rãrãräÚvisit_is_distinct_from_binaryes  þz+MySQLCompiler.visit_is_distinct_from_binarycKsd| |j¡| |j¡fS)Nz %s <=> %sr*r rãrãräÚ!visit_is_not_distinct_from_binaryks  þz/MySQLCompiler.visit_is_not_distinct_from_binarycKs d|j|f|Ž|j|f|ŽfS)NzCONCAT('(?', %s, ')', %s)rX)rár6ÚpatternrùrãrãräÚ_mariadb_regexp_flagsqs  þz#MySQLCompiler._mariadb_regexp_flagscKs–|jd}|dkr"|j||f|ŽS|jjrNd|j|jf|Ž|| ||j¡fSd|j|jf|Ž|j|jf|Ž|j|f|Žf}|dkrŽd|S|SdS)Nr6z%s%s%szREGEXP_LIKE(%s, %s, %s)ú NOT REGEXP zNOT %s)r0Z_generate_generic_binaryræÚ is_mariadbrr…rur°)ráZ op_stringrIrrùr6rrãrãräÚ _regexp_matchws"  ý ýzMySQLCompiler._regexp_matchcKs|jd||f|ŽS)Nz REGEXP ©rxr rãrãräÚvisit_regexp_match_op_binaryŒsz*MySQLCompiler.visit_regexp_match_op_binarycKs|jd||f|ŽS)Nrvryr rãrãräÚ visit_not_regexp_match_op_binarysz.MySQLCompiler.visit_not_regexp_match_op_binarycKsÂ|jd}|jd}|dkrLd|j|jf|Ž|j|jf|Ž|j|f|ŽfS|jjr‚d|j|jf|Ž| ||j¡|j|f|ŽfSd|j|jf|Ž|j|jf|Ž|j|f|Ž|j|f|ŽfSdS)Nr6Ú replacementzREGEXP_REPLACE(%s, %s, %s)zREGEXP_REPLACE(%s, %s, %s, %s))r0rr…r°rærwru)rárIrrùr6r|rãrãräÚvisit_regexp_replace_op_binary’s(   ý  ý  üz,MySQLCompiler.visit_regexp_replace_op_binary)N)FN)/rïrðrñZ'render_table_with_column_in_update_fromr7Ú SQLCompilerZ extract_mapÚcopyrÈrörúrürýr r r r)r+Ú frozensetr1r4r/r,r7r8rCrFrKrLrPrVr]r^r`rgrirkrmrqrrrsrurxrzr{r}Ú __classcell__rãrãrIräròˆsR  HCÿ * +   (  ròcsdeZdZdd„Zdd„Zdd„Z‡fdd„Zd d „Zd d „Zd d„Z dd„Z dd„Z dd„Z ‡Z S)ÚMySQLDDLCompilercKs*|j |¡|jjj|j|dg}|jdk r<| | |j¡¡t|j  |j¡t j ƒ}|j sd| d¡n|j rx|rx| d¡|j }|dk r¦|j |t  ¡¡}| d|¡|jdk rþ||jjkrþ|jdksÔt|jtjƒrþ|jjròt|jtjƒrò|jjrþ| d¡n"| |¡}|dk r | d|¡d |¡S) zBuilds column DDL.)rNzNOT NULLÚNULLzCOMMENT ZAUTO_INCREMENTzDEFAULT rþ)rûÚ format_columnrær>rrÿZcomputedr%rZ_unwrapped_dialect_implrr(ZnullableÚcommentÚ sql_compilerrFrr½Z_autoincrement_columnZserver_defaultÚ sa_schemaZIdentityÚsupports_sequencesr5ÚSequenceÚoptionalZget_column_default_stringr)rárOrùZcolspecZ is_timestampr…Úliteralr5rãrãräÚget_column_specification«sT ÿþ  þ   ÿÿ þü ûø ÷ ö   z)MySQLDDLCompiler.get_column_specificationc sDg}t‡fdd„|j ¡Dƒƒ}|jdk r4|j|d<ddddg}t|ƒ |¡}t|ƒ |¡}t d d d d g|¡D]`}||}|t j kr˜ˆj   |t  ¡¡}|d kr¬| dd¡}d} |dkr¼d} | |  ||f¡¡qpt ddddddg|¡D]N}||}|t j krˆj   |t  ¡¡}| dd¡}d} | |  ||f¡¡qêd |¡S)z9Build table-level CREATE options like ENGINE and COLLATE.c3sD|]<\}}| dˆjj¡r|tˆjjƒdd… ¡|fVqdS)z%s_rN)Ú startswithrær ÚlenrO)rÚkÚvrèrãräräsþz5MySQLDDLCompiler.post_create_table..NÚCOMMENTÚ PARTITION_BYÚ PARTITIONSÚ SUBPARTITIONSÚSUBPARTITION_BY)ÚDEFAULT_CHARSETÚCOLLATE)ÚDEFAULT_CHARACTER_SETr—)ÚCHARSETr—)Ú CHARACTER_SETr—)ZDATA_DIRECTORYZINDEX_DIRECTORYr˜ršr–ZDEFAULT_COLLATEÚ_rþú=)Z TABLESPACEzDEFAULT CHARACTER SETz CHARACTER SETr—)r’r“)r’r•)r’r”)r“r”)r“r•)r•r”)ÚdictrUÚitemsr…r¶Ú differenceÚ intersectionrBÚsortÚ _reflectionZ_options_of_type_stringr†rFrrr­r%r) rár½Z table_optsÚoptsZpartition_optionsZnonpart_optionsZ part_optionsÚoptÚargÚjoinerrãrèräÚpost_create_tableßsj þ  üüù  ÿ ú÷  ÿ z"MySQLDDLCompiler.post_create_tablec  sh|j}ˆ |¡ˆj}| |j¡}‡fdd„|jDƒ}ˆ |¡}d}|jrR|d7}|j  dˆj j d¡} | rx|| d7}|d7}|j rŽ|d7}|d ||f7}|j ˆj j d ‰ˆdk rþtˆtƒräd  ‡fd d „t|j|ƒDƒ¡}nd  ‡fdd „|Dƒ¡}n d  |¡}|d|7}|j dd} | dk r:|d| f7}|j dd} | dk rd|d| | ¡7}|S)Ncs^g|]V}ˆjjt|tjƒsDt|tjƒr8|jtjtj fksDt|t j ƒrNt  |¡n|ddd‘qS)FT)Z include_tableZ literal_binds) r†rrr8ZBinaryExpressionZUnaryExpressionÚmodifierr:Zdesc_opZasc_opr9ZFunctionElementZGrouping)rÚexprrèrãrär:s ñ þ ü ÿû ø óz7MySQLDDLCompiler.visit_create_index..zCREATE zUNIQUE ú %s_prefixrþúINDEX zIF NOT EXISTS z %s ON %s Úlengthr"c3sN|]F\}}|jˆkr&d|ˆ|jfn|ˆkr>d|ˆ|fnd|VqdS)ú%s(%d)z%sN©r )rrr©©r¬rãrärcs úÿÿz6MySQLDDLCompiler.visit_create_index..c3s|]}d|ˆfVqdS)r­Nrãrr¯rãrärpsz(%s)ÚmysqlÚ with_parserz WITH PARSER %srÊú USING %s)r.Z_verify_index_tablerûÚ format_tabler½Z expressionsÚ_prepared_index_namerÆrUrprær Z if_not_existsÚdialect_optionsrrrÚzipr) rárUrùrwrûr½rPr rZ index_prefixÚparserrÊrã)r¬ráräÚvisit_create_index4sH   ð    ø ÿ     z#MySQLDDLCompiler.visit_create_indexcs:tt|ƒ |¡}|jdd}|r6|d|j |¡7}|S)Nr°rÊr²)rEr‚Úvisit_primary_key_constraintrµrûr)rárRrrÊrIrãrär¹s ÿz-MySQLDDLCompiler.visit_primary_key_constraintcCs<|j}d}|jr|d7}|d|j|dd|j |j¡fS)Nz DROP INDEX z IF EXISTS z%s ON %sF)Zinclude_schema)r.Z if_existsr´rûr³r½)rár^rwrrãrãräÚvisit_drop_indexŠs  þz!MySQLDDLCompiler.visit_drop_indexcCs¬|j}t|tjƒr$d}|j |¡}npt|tjƒr:d}d}nZt|tjƒrXd}|j |¡}n„Z!d?d@„Z"dAdB„Z#dCdD„Z$dEdF„Z%dGdH„Z&dIdJ„Z'dKdL„Z(dMdN„Z)‡Z*S)OÚMySQLTypeCompilercCs.| |¡s|S|jr|d7}|jr*|d7}|S)zAExtend a numeric-type declaration with MySQL specific extensions.z UNSIGNEDz ZEROFILL)Ú _mysql_typerÇrÕ)ráríÚspecrãrãräÚ_extend_numericÍs z!MySQLTypeCompiler._extend_numericcs¦‡‡fdd„}|dƒr$d|dƒ}n |dƒr2d}n|dƒr@d}nd }|d ƒrXd ˆj}n|d ƒrfd }nd }|dƒrŒd dd„d||fDƒ¡Sd dd„|||fDƒ¡S)z‡Extend a string-type declaration with standard SQL CHARACTER SET / COLLATE annotations and MySQL specific extensions. cstˆ|ˆ |¡ƒSrß)r:rpr®©ÚdefaultsrírãräÚattrßsz.MySQLTypeCompiler._extend_string..attrÚcharsetzCHARACTER SET %sÚasciiÚASCIIÚunicodeÚUNICODENÚ collationz COLLATE %srIr=ÚnationalrþcSsg|]}|dk r|‘qSrßrãrrãrãrärõsz4MySQLTypeCompiler._extend_string..ZNATIONALcSsg|]}|dk r|‘qSrßrãrrãrãrärøs)rÏr)rárírÈrÅrÉrÊrÏrãrÇräÚ_extend_stringÙs( ÿÿz MySQLTypeCompiler._extend_stringcCst|ttfƒSrß)rrr)rárírãrãrärÄûszMySQLTypeCompiler._mysql_typecKsT|jdkr| |d¡S|jdkr6| |dd|ji¡S| |d|j|jdœ¡SdS)Nr"zNUMERIC(%(precision)s)r£z!NUMERIC(%(precision)s, %(scale)s)©r£r©r£rÆr©rárírùrãrãräÚ visit_NUMERICþs    þ ÿþzMySQLTypeCompiler.visit_NUMERICcKsT|jdkr| |d¡S|jdkr6| |dd|ji¡S| |d|j|jdœ¡SdS)NrzDECIMAL(%(precision)s)r£z!DECIMAL(%(precision)s, %(scale)s)rÒrÓrÔrãrãräÚ visit_DECIMAL s    þ ÿþzMySQLTypeCompiler.visit_DECIMALcKs>|jdk r.|jdk r.| |d|j|jdœ¡S| |d¡SdS)Nz DOUBLE(%(precision)s, %(scale)s)rÒr©r£rrÆrÔrãrãräÚ visit_DOUBLE s ÿþzMySQLTypeCompiler.visit_DOUBLEcKs>|jdk r.|jdk r.| |d|j|jdœ¡S| |d¡SdS)NzREAL(%(precision)s, %(scale)s)rÒr$r×rÔrãrãräÚ visit_REAL& s ÿþzMySQLTypeCompiler.visit_REALcKsd| |¡r6|jdk r6|jdk r6| |d|j|jf¡S|jdk rT| |d|jf¡S| |d¡SdS)Nz FLOAT(%s, %s)z FLOAT(%s)r)rÄrr£rÆrÔrãrãräÚ visit_FLOAT0 s ÿþýÿ  ÿzMySQLTypeCompiler.visit_FLOATcKs:| |¡r*|jdk r*| |dd|ji¡S| |d¡SdS)NzINTEGER(%(display_width)s)Ú display_widthr©rÄrÛrÆrÔrãrãräÚ visit_INTEGER@ sÿþzMySQLTypeCompiler.visit_INTEGERcKs:| |¡r*|jdk r*| |dd|ji¡S| |d¡SdS)NzBIGINT(%(display_width)s)rÛrrÜrÔrãrãräÚ visit_BIGINTJ sÿþzMySQLTypeCompiler.visit_BIGINTcKs:| |¡r*|jdk r*| |dd|ji¡S| |d¡SdS)NzMEDIUMINT(%(display_width)s)rÛrrÜrÔrãrãräÚvisit_MEDIUMINTT sÿþz!MySQLTypeCompiler.visit_MEDIUMINTcKs6| |¡r&|jdk r&| |d|j¡S| |d¡SdS)Nz TINYINT(%s)r*rÜrÔrãrãräÚ visit_TINYINT^ s ÿzMySQLTypeCompiler.visit_TINYINTcKs:| |¡r*|jdk r*| |dd|ji¡S| |d¡SdS)NzSMALLINT(%(display_width)s)rÛr%rÜrÔrãrãräÚvisit_SMALLINTf sÿþz MySQLTypeCompiler.visit_SMALLINTcKs|jdk rd|jSdSdS)NzBIT(%s)rr¯rÔrãrãräÚ visit_BITp s  zMySQLTypeCompiler.visit_BITcKst|ddƒrd|jSdSdS)NÚfspz DATETIME(%d)r©r:rãrÔrãrãräÚvisit_DATETIMEv s  z MySQLTypeCompiler.visit_DATETIMEcKsdS)Nr@rãrÔrãrãräÚ visit_DATE| szMySQLTypeCompiler.visit_DATEcKst|ddƒrd|jSdSdS)NrãzTIME(%d)r'rärÔrãrãräÚ visit_TIME s  zMySQLTypeCompiler.visit_TIMEcKst|ddƒrd|jSdSdS)Nrãz TIMESTAMP(%d)r(rärÔrãrãräÚvisit_TIMESTAMP… s  z!MySQLTypeCompiler.visit_TIMESTAMPcKs|jdkrdSd|jSdS)Nr-zYEAR(%s))rÛrÔrãrãräÚ visit_YEAR‹ s zMySQLTypeCompiler.visit_YEARcKs,|jr| |id|j¡S| |id¡SdS)NzTEXT(%d)r&©r¬rÑrÔrãrãräÚ visit_TEXT‘ szMySQLTypeCompiler.visit_TEXTcKs| |id¡S)Nr+©rÑrÔrãrãräÚvisit_TINYTEXT— sz MySQLTypeCompiler.visit_TINYTEXTcKs| |id¡S)Nr rìrÔrãrãräÚvisit_MEDIUMTEXTš sz"MySQLTypeCompiler.visit_MEDIUMTEXTcKs| |id¡S)NrrìrÔrãrãräÚvisit_LONGTEXT sz MySQLTypeCompiler.visit_LONGTEXTcKs0|jr| |id|j¡St d|jj¡‚dS)Nz VARCHAR(%d)z'VARCHAR requires a length on dialect %s©r¬rÑr/r2rær rÔrãrãräÚ visit_VARCHAR  s  ÿzMySQLTypeCompiler.visit_VARCHARcKs0|jr| |idd|ji¡S| |id¡SdS)NúCHAR(%(length)s)r¬rrêrÔrãrãräÚ visit_CHAR¨ s ÿzMySQLTypeCompiler.visit_CHARcKs8|jr"| |ddidd|ji¡St d|jj¡‚dS)NrÐTzVARCHAR(%(length)s)r¬z(NVARCHAR requires a length on dialect %srðrÔrãrãräÚvisit_NVARCHAR° s ý ÿz MySQLTypeCompiler.visit_NVARCHARcKs8|jr"| |ddidd|ji¡S| |ddid¡SdS)NrÐTròr¬rrêrÔrãrãräÚ visit_NCHAR¾ s ýzMySQLTypeCompiler.visit_NCHARcKs d|jS)Nz VARBINARY(%d)r¯rÔrãrãräÚvisit_VARBINARYÊ sz!MySQLTypeCompiler.visit_VARBINARYcKsdS)Nr rãrÔrãrãräÚ visit_JSONÍ szMySQLTypeCompiler.visit_JSONcKs | |¡Srß)Ú visit_BLOBrÔrãrãräÚvisit_large_binaryÐ sz$MySQLTypeCompiler.visit_large_binaryc s*|jstt|ƒ |¡S| d||j¡SdS©Nr )Z native_enumrErÃÚ visit_enumÚ_visit_enumerated_valuesÚenumsrÔrIrãrärûÓ szMySQLTypeCompiler.visit_enumcKs|jrd|jSdSdS)NzBLOB(%d)r>r¯rÔrãrãrärøÙ s zMySQLTypeCompiler.visit_BLOBcKsdS)Nr)rãrÔrãrãräÚvisit_TINYBLOBß sz MySQLTypeCompiler.visit_TINYBLOBcKsdS)NrrãrÔrãrãräÚvisit_MEDIUMBLOBâ sz"MySQLTypeCompiler.visit_MEDIUMBLOBcKsdS)NrrãrÔrãrãräÚvisit_LONGBLOBå sz MySQLTypeCompiler.visit_LONGBLOBc Cs@g}|D]}| d| dd¡¡q| |id|d |¡f¡S)Nr#ú'z''z%s(%s)ú,)r%r­rÑr)rár ríZenumerated_valuesZ quoted_enumsÚerãrãrärüè sÿz*MySQLTypeCompiler._visit_enumerated_valuescKs| d||j¡Srú)rürýrÔrãrãräÚ visit_ENUMð szMySQLTypeCompiler.visit_ENUMcKs| d||j¡S)Nr )rürËrÔrãrãräÚ visit_SETó szMySQLTypeCompiler.visit_SETcKsdS)NÚBOOLrãrÔrãrãräÚ visit_BOOLEANö szMySQLTypeCompiler.visit_BOOLEAN)+rïrðrñrÆrÑrÄrÕrÖrØrÙrÚrÝrÞrßràrárârårærçrèrérërírîrïrñrórôrõrör÷rùrûrørþrÿrrürrrrrãrãrIrärÃÌsN "        rÃcs*eZdZeZd‡fdd„ Zdd„Z‡ZS)ÚMySQLIdentifierPreparerFc s(|s d}nd}tt|ƒj|||ddS)Nú`ú")Z initial_quoteZ escape_quote)rErÚ__init__)ráræÚserver_ansiquotesrùrrIrãrär þ s ÿz MySQLIdentifierPreparer.__init__cst‡fdd„|DƒƒS)z4Unilaterally identifier-quote any number of strings.csg|]}|dk rˆ |¡‘qSrß)Úquote_identifier)rÚirèrãrär szCMySQLIdentifierPreparer._quote_free_identifiers..)Útuple)ráÚidsrãrèräÚ_quote_free_identifiers sz/MySQLIdentifierPreparer._quote_free_identifiers)F)rïrðrñÚRESERVED_WORDSZreserved_wordsr rrrãrãrIrärú s rc @seZdZdZdZdZdZdZdZdZ dZ dZ dZ dZ dZdZdZdZdZdZdZdZdZeZdZeZeZeZeZe Z!dZ"dZ#dZ$dZ%e&j'd dife(j)d dife&j*d dife&j+ddddd œfgZ,d{d d„Z-dd„Z.e/ddddgƒZ0dd„Z1dd„Z2dd„Z3e4dd„ƒZ5dd„Z6dd „Z7d!d"„Z8d#d$„Z9d%d&„Z:d|d'd(„Z;d}d)d*„Zd~d/d0„Z?dd1d2„Z@d€d3d4„ZAd5d6„ZBd7d8„ZCdd9d:„ZDd‚d;d<„ZEd=d>„ZFeGjHdƒd?d@„ƒZIdAdB„ZJdCdD„ZKeLdEdF„ƒZMeLdGdH„ƒZNeLdIdJ„ƒZOeLdKdL„ƒZPeGjHdMdN„ƒZQeGjHd„dOdP„ƒZReGjHd…dQdR„ƒZSeGjHd†dSdT„ƒZTeGjHd‡dUdV„ƒZUeGjHdˆdWdX„ƒZVeGjHd‰dYdZ„ƒZWd[d\„ZXeGjHdŠd]d^„ƒZYeGjHd‹d_d`„ƒZZeGjHdŒdadb„ƒZ[eGjHddcdd„ƒZ\eGjHdŽdedf„ƒZ]ddgdh„Z^e_j`didj„ƒZaeGjHddkdl„ƒZbdmdn„Zcdodp„Zddqdr„Zedsdt„Zfdudv„Zgd‘dwdx„Zhd’dydz„ZidS)“Ú MySQLDialectzMDetails of the MySQL dialect. Not used directly in application code. r°TFéÿé@ÚformatNÚ*r‡rÊ)rÊr¬Úprefixr±cKs>| dd¡tjj|f|Ž||_||_||_| |d¡dS)NZuse_ansiquotes)Úpopr5ÚDefaultDialectr Úisolation_levelZ_json_serializerZ_json_deserializerÚ _set_mariadb)rárZjson_serializerZjson_deserializerrwrUrãrãrär Z s  zMySQLDialect.__init__cs"ˆjdk r‡fdd„}|SdSdS)Ncsˆ |ˆj¡dSrß)Úset_isolation_levelr)ÚconnrèrãräÚconnectl sz(MySQLDialect.on_connect..connect)r)rárrãrèräÚ on_connecti s  zMySQLDialect.on_connectZ SERIALIZABLEzREAD UNCOMMITTEDzREAD COMMITTEDzREPEATABLE READcCs,| dd¡}t|dƒr|j}| ||¡dS)Nr›rþÚ connection)r­Úhasattrr!Ú_set_isolation_level)rár!r†rãrãrär| s  z MySQLDialect.set_isolation_levelcCsT||jkr(t d||jd |j¡f¡‚| ¡}| d|¡| d¡| ¡dS)NzLInvalid value '%s' for isolation_level. Valid isolation levels for %s are %sr"z*SET SESSION TRANSACTION ISOLATION LEVEL %sZCOMMIT)Ú_isolation_lookupr/Ú ArgumentErrorr rrWÚexecuteÚclose)rár!r†rWrãrãrär#‡ s þÿ z!MySQLDialect._set_isolation_levelcCs†| ¡}|jr$|jdkr$| d¡n | d¡| ¡}|dkrNt d¡tƒ‚|d}| ¡tj rvt |t ƒrv|  ¡}|  ¡ dd¡S)N)ééézSELECT @@transaction_isolationzSELECT @@tx_isolationzDCould not retrieve transaction isolation level for MySQL connection.rú-rþ)rWÚ _is_mysqlÚserver_version_infor&Úfetchoner4r&rçr'Úpy3krÚbytesÚdecoderOr­)rár!rWr²r(rãrãräÚget_isolation_level“ s  ÿz MySQLDialect.get_isolation_levelc Cs~| ¡}||d}| |¡\}}|j||Ž}zBz"| ¡}| d¡| ¡d}Wn‚YnXt|ƒW¢SW5| ¡XdS)N)Údbapiz!SELECT VERSION() LIKE '%MariaDB%'r)r3Zcreate_connect_argsrr'rWr&r.Úbool) ÚclsÚurlr3ræZcargsZcparamsrrWr(rãrãräÚ_is_mariadb_from_url¦ s   z!MySQLDialect._is_mariadb_from_urlcCsN|j}| ¡}| d¡| ¡d}| ¡tjrDt|tƒrD|  ¡}|  |¡S)NzSELECT VERSION()r) r!rWr&r.r'r4r/rr0r1Ú_parse_server_version)rár!Z dbapi_conrWr(rãrãräÚ_get_server_version_info¸ s  z%MySQLDialect._get_server_version_infoc Cs°g}d}t d¡}| |¡}|D]R}t d|¡}|s8q q | d¡rZt|dd…ƒ|_d}q t| d¡ƒ}| |¡q t|ƒ} |  | o†||¡|s–| |_| dkr¦t d ƒ‚| |_ | S) NFz[.\-+]z"^(?:(\d+)(?:a|b|c)?|(MariaDB\w*))$ééýÿÿÿTr)r(rr:zGthe MySQL/MariaDB dialect supports server version info 5.0.2 and above.) ÚreÚcompileÚsplitrrqrÚ _mariadb_normalized_version_infor{r%rrçr-) rár(rMrwÚrÚtokensÚtokenZ parsed_tokenÚdigitr-rãrãrär8Æ s4  ÿ  ÿz"MySQLDialect._parse_server_versioncCs0|dkr dS|s&|jr&t d|f¡‚||_dS)Nz*MySQL version %s is not a MariaDB variant.)rwr/ZInvalidRequestError)rárwr-rãrãrärê s ÿÿzMySQLDialect._set_mariadbcCs| t d¡t|d¡dS)Nz XA BEGIN :xid©Úxid©r&r2rr©rár!rErãrãräÚdo_begin_twophaseõ szMySQLDialect.do_begin_twophasecCs4| t d¡t|d¡| t d¡t|d¡dS)Nú XA END :xidrDzXA PREPARE :xidrFrGrãrãräÚdo_prepare_twophaseø sz MySQLDialect.do_prepare_twophasecCs8|s| t d¡t|d¡| t d¡t|d¡dS)NrIrDzXA ROLLBACK :xidrF©rár!rEZ is_preparedZrecoverrãrãräÚdo_rollback_twophaseü sz!MySQLDialect.do_rollback_twophasecCs,|s| ||¡| t d¡t|d¡dS)NzXA COMMIT :xidrD)rJr&r2rrrKrãrãräÚdo_commit_twophase s zMySQLDialect.do_commit_twophasecCs| d¡}dd„|DƒS)Nz XA RECOVERcSs g|]}|dd|d…‘qS)ÚdatarZ gtrid_lengthrã©rr²rãrãrär sz4MySQLDialect.do_recover_twophase..©Úexec_driver_sql)rár!Z resultsetrãrãräÚdo_recover_twophase s z MySQLDialect.do_recover_twophasecCsNt||jj|jjfƒr$| |¡dkSt||jj|jjfƒrFdt|ƒkSdSdS)N)i‡iÖiÝiÞiýiz(0, '')F)rr3ZOperationalErrorZProgrammingErrorÚ_extract_error_codeZInterfaceErrorZ InternalErrorÚstr)rárr!rWrãrãräÚ is_disconnect sÿÿ zMySQLDialect.is_disconnectcs‡fdd„| ¡DƒS)zMProxy result rows to smooth over MySQL-Python driver inconsistencies.csg|]}t|ˆƒ‘qSrã)Ú _DecodingRowrO©rÊrãrär' sz1MySQLDialect._compat_fetchall..)Zfetchall)ráÚrprÊrãrWräÚ_compat_fetchall# szMySQLDialect._compat_fetchallcCs| ¡}|rt||ƒSdSdS©zNProxy a result row to smooth over MySQL-Python driver inconsistencies.N)r.rV©rárXrÊr²rãrãräÚ_compat_fetchone) s zMySQLDialect._compat_fetchonecCs| ¡}|rt||ƒSdSdSrZ)ÚfirstrVr[rãrãräÚ _compat_first3 s zMySQLDialect._compat_firstcCs tƒ‚dSrß©rç)ráÚ exceptionrãrãrärS= sz MySQLDialect._extract_error_codecCs| d¡ ¡S)NzSELECT DATABASE())rQÚscalar©rár!rãrãräÚ_get_default_schema_name@ sz%MySQLDialect._get_default_schema_namec Csb| |¡|dkr|j}| tdƒ tjdtdtjdtd¡t  |¡t  |¡dœ¡}t |  ¡ƒS)NznSELECT COUNT(*) FROM information_schema.tables WHERE table_schema = :table_schema AND table_name = :table_nameÚ table_schemarÚ table_name)rdre) Z_ensure_has_table_connectionÚdefault_schema_namer&rÚ bindparamsr2Ú bindparamr<r4Ú text_typer4ra)rár!rer1ÚrsrãrãräÚ has_tableC s ÿ  ú þ÷zMySQLDialect.has_tablecCs>|js| ¡|s|j}| t d¡t||d¡}| ¡dk S)NzSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='SEQUENCE' and TABLE_NAME=:name AND TABLE_SCHEMA=:schema_name)r Ú schema_name)rˆÚ_sequences_not_supportedrfr&r2rrr])rár!Z sequence_namer1rWrãrãräÚ has_sequenceY sÿ úzMySQLDialect.has_sequencecCs tdƒ‚dS)NzBSequences are supported only by the MariaDB series 10.3 or greaterr_rèrãrãrärmj sÿz%MySQLDialect._sequences_not_supportedcKsJ|js| ¡|s|j}| t d¡t|d¡}dd„|j||jdDƒS)NzjSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='SEQUENCE' and TABLE_SCHEMA=:schema_name)rlcSsg|] }|d‘qS©rrãrOrãrãrär~ sÿz3MySQLDialect.get_sequence_names..rW) rˆrmrfr&r2rrrYÚ_connection_charset)rár!r1rùrWrãrãräÚget_sequence_namesp s ÿûÿþzMySQLDialect.get_sequence_namescCs”| |¡|_| |¡| |¡| |¡|jrB|j||jd|_tj   ||¡|j o^|j dk|_ |jop|j dk|_|j o„|j dk|_| ¡dS)N)r )é r.)é)Ú_detect_charsetrpÚ_detect_sql_modeÚ_detect_ansiquotesÚ_detect_casingÚ_server_ansiquotesrûrêr5rÚ initializerwr-rˆr,rZÚ_needs_correct_for_88718_96365Ú_warn_for_known_db_issuesrbrãrãräry… s"    ÿÿÿÿzMySQLDialect.initializecCs0|jr,|j}|dkr,|dkr,t d|f¡dS)N©rrr:)rrr:é a`MariaDB %r before 10.2.9 has known issues regarding CHECK constraints, which impact handling of NULL values with SQLAlchemy's boolean datatype (MDEV-13596). An additional issue prevents proper migrations of columns with CHECK constraints (MDEV-11114). Please upgrade to MariaDB 10.2.9 or greater, or use the MariaDB 10.1 series, to avoid these issues.)rwr?r4r&)ráZ mdb_versionrãrãrär{¡ súÿz&MySQLDialect._warn_for_known_db_issuescCs(|js dS|jr|jdkS|jdkSdS)NF)rrér()rsré)r-rwrèrãrãrär@¯ s  z MySQLDialect._support_float_castcCs|jSrß©rwrèrãrãräÚ _is_mariadbº szMySQLDialect._is_mariadbcCs|j Srßr€rèrãrãrär,¾ szMySQLDialect._is_mysqlcCs|jo|jdkS)Nr|)rwr?rèrãrãräÚ_is_mariadb_102 szMySQLDialect._is_mariadb_102cKs| d¡}dd„|DƒS)Nz SHOW schemascSsg|] }|d‘qSrorã)rr@rãrãrärÌ sz1MySQLDialect.get_schema_names..rP)rár!rùrXrãrãräÚget_schema_namesÉ s zMySQLDialect.get_schema_namescKsH|dk r|}n|j}|j}| d|j |¡¡}dd„|j||dDƒS)z1Return a Unicode SHOW TABLES from a given schema.NúSHOW FULL TABLES FROM %scSs g|]}|ddkr|d‘qS)rz BASE TABLErrãrOrãrãrärÝ s þz0MySQLDialect.get_table_names..rW©rfrprQrêr rY)rár!r1rùZcurrent_schemarÊrXrãrãräÚget_table_namesÎ s ÿÿ þzMySQLDialect.get_table_namescKsB|dkr|j}|j}| d|j |¡¡}dd„|j||dDƒS)Nr„cSs g|]}|ddkr|d‘qS)r)ZVIEWz SYSTEM VIEWrrãrOrãrãrärì s þz/MySQLDialect.get_view_names..rWr…)rár!r1rùrÊrXrãrãräÚget_view_namesã s ÿÿ þzMySQLDialect.get_view_namescKs|j|||f|Ž}|jSrß)Ú_parsed_state_or_createÚ table_options©rár!rer1rùÚ parsed_staterãrãräÚget_table_optionsò sÿÿzMySQLDialect.get_table_optionscKs|j|||f|Ž}|jSrß)rˆrPrŠrãrãräÚ get_columnsú sÿÿzMySQLDialect.get_columnscKsT|j|||f|Ž}|jD]0}|ddkrdd„|dDƒ}|ddœSqgddœS)NrÿÚPRIMARYcSsg|] }|d‘qSrorã©rÚsrãrãrär sz2MySQLDialect.get_pk_constraint..rP)Úconstrained_columnsr ©rˆr)rár!rer1rùr‹r€r'rãrãräÚget_pk_constraint sÿÿ  zMySQLDialect.get_pk_constraintcKsâ|j|||f|Ž}d}g}|jD]ª}|dd} t|dƒdkrL|ddpN|} | sp|dkrd|jj}||krp|} |d} |d} i} dD] }| |d¡d krˆ||| |<qˆ|d | | | | | d œ}| |¡q |jrÞ| ||¡|S) Nr½róréþÿÿÿÚlocalrl)ZonupdateZondeleteF)z NO ACTIONNr )r r‘Úreferred_schemaÚreferred_tableÚreferred_columnsÚoptions) rˆZfk_constraintsrŽrærfrpr%rzÚ#_correct_for_mysql_bugs_88718_96365)rár!rer1rùr‹Zdefault_schemaÚfkeysrÅZref_nameZ ref_schemaZ loc_namesZ ref_namesZcon_kwr¤Zfkey_drãrãräÚget_foreign_keys sDÿÿ   ú  zMySQLDialect.get_foreign_keysc s8|jdkrdd„‰ndd„‰|jj‰‡‡fdd„|Dƒ}|r4| t d¡ tjdd d ¡t|d ¡}t tƒ}|D]V\}}}||ˆ|ƒˆ|ƒfd <||ˆ|ƒˆ|ƒfd <||ˆ|ƒˆ|ƒf|  ¡<qr|D]d} |ˆ| dpàˆƒˆ| dƒf‰ˆd | d<| ddk rˆd | d<‡fdd„| dDƒ| d<qÎdS)N)rr:cSs| ¡Srß©Úlower©rrãrãräržH sz?MySQLDialect._correct_for_mysql_bugs_88718_96365..lowercSs|SrßrãrŸrãrãräržO scs8g|]0}|dD]"}ˆ|dp ˆƒˆ|dƒ|f‘qqS)r˜r–r—rã)rÚrecZcol_name)rfržrãrärS s û ýzDMySQLDialect._correct_for_mysql_bugs_88718_96365..zó select table_schema, table_name, column_name from information_schema.columns where (table_schema, table_name, lower(column_name)) in :table_data; Ú table_dataT)Z expanding)r¡Z SCHEMANAMEZ TABLENAMEr–r—csg|]}ˆ| ¡‘qSrãrr)r rãrär‡ sr˜) Ú_casingrærfr&r2rrgrhrrrž) rár›r!Z col_tuplesZcorrect_for_wrong_fk_caseÚdr1ZtnameÚcnameZfkeyrã)rfržr rärš8 sB   ú ÿ ù÷ þÿ   ÿz0MySQLDialect._correct_for_mysql_bugs_88718_96365cKs"|j|||f|Ž}dd„|jDƒS)NcSsg|]}|d|ddœ‘qS)r Úsqltext)r r¥rã)rrÅrãrãrär‘ sÿz6MySQLDialect.get_check_constraints..)rˆZck_constraintsrŠrãrãräÚget_check_constraints‹ sÿÿþz"MySQLDialect.get_check_constraintscKs*|j|||f|Ž}d|j d|jd¡iS)Nrz %s_comment)rˆr‰rpr rŠrãrãräÚget_table_comment– sÿÿÿÿzMySQLDialect.get_table_commentc Ksè|j|||f|Ž}g}|jD]Æ}i}d} |d} | dkr:q| dkrHd} n0| dkr`| |d|j<n| dkrjn|j d| ¡|d r’|d |d |j<i} |r¢|| d <|d | d <d d„|dDƒ| d<| | d<| rØ| | d<| | ¡q|S)NFrÿrŽÚUNIQUET)ZFULLTEXTZSPATIALrªz-Converting unknown KEY type %s to a plain KEYr·z%s_with_parserrµr cSsg|] }|d‘qSrorãrrãrãrärÆ sz,MySQLDialect.get_indexes..rPÚ column_namesrÆ)rˆrr ÚloggerÚinfor%) rár!rer1rùr‹ZindexesrÅrµrÆZflavorZindex_drãrãräÚ get_indexes¡ sLÿÿ ÿÿ  zMySQLDialect.get_indexescKs"|j|||f|Ž}dd„|jDƒS)NcSs:g|]2}|ddkr|ddd„|dDƒ|ddœ‘qS)rÿr¨r cSsg|] }|d‘qSrorãrrãrãrärØ szBMySQLDialect.get_unique_constraints...rP)r r©Zduplicates_indexrãrrãrãrärÕ s  ûýz7MySQLDialect.get_unique_constraints..r’rŠrãrãräÚget_unique_constraintsÍ sÿÿúz#MySQLDialect.get_unique_constraintscKs0|j}d |j ||¡¡}|j|d||d}|S)NÚ.©Ú full_name)rprrêrÚ_show_create_table)rár!Z view_namer1rùrÊr°r2rãrãräÚget_view_definitionß s ÿÿz MySQLDialect.get_view_definitioncKs|j|||| dd¡dS)NÚ info_cache)r³)Ú _setup_parserrp)rár!rer1rùrãrãrärˆë s  üz$MySQLDialect._parsed_state_or_createcCs|j}t ||¡S)z´return the MySQLTableDefinitionParser, generate if needed. The deferred creation ensures that the dialect has retrieved server version information first. )rêr¢ZMySQLTableDefinitionParser)rárûrãrãräÚ_tabledef_parserõ szMySQLDialect._tabledef_parserc Ksh|j}|j}d |j ||¡¡}|j|d||d}t d|¡r\|j|d||d} |  || ¡}|  ||¡S)Nr®r¯z^CREATE (?:ALGORITHM)?.* VIEW) rprµrrêrr±r<rÚ_describe_tableZ_describe_to_createÚparse) rár!rer1rùrÊr·r°r2rPrãrãrär´ s.ÿÿÿ ÿ zMySQLDialect._setup_parsercCs tƒ‚dSrßr_rbrãrãrärt szMySQLDialect._detect_charsetcCsh|j}| t d¡¡}|j||d}|s.d}n0|ddkr@d}n|ddkrRd}n t|dƒ}||_|S)zŒSniff out identifier case sensitivity. Cached per-connection. This value can not change without a server restart. z,SHOW VARIABLES LIKE 'lower_case_table_names'rWrrZOFFÚON)rpr&r2rr^r{r¢)rár!rÊZshow_varr²Úcsrãrãrärw s" ÿþ   zMySQLDialect._detect_casingcCs:i}|j}| d¡}| ||¡D]}|d||d<q |S)zYPull the active COLLATIONS list from the server. Cached per-connection. zSHOW COLLATIONrr)rprQrY)rár!Z collationsrÊrjr²rãrãräÚ_detect_collations6 s  zMySQLDialect._detect_collationscCs>|j| d¡|jd}|s,t d¡d|_n|dp6d|_dS)NzSHOW VARIABLES LIKE 'sql_mode'rWz[Could not retrieve SQL_MODE; please ensure the MySQL user has permissions to SHOW VARIABLESrôr)r^rQrpr4r&Ú _sql_mode)rár!r²rãrãräruC sþÿzMySQLDialect._detect_sql_modecCsL|j}|sd}n$| ¡r4t|ƒ}|dB|kr0dp2d}d|k|_d|k|_dS)z/Detect and adjust for the ANSI_QUOTES sql mode.rôr~Z ANSI_QUOTESZNO_BACKSLASH_ESCAPESN)r»Úisdigitr{rxrG)rár!r•Zmode_norãrãrärvR s zMySQLDialect._detect_ansiquotesc Cs¬|dkr|j |¡}d|}d}z|jdd |¡}WnLtjk r‚}z,| |j¡dkrptj t  |¡|dn‚W5d}~XYnX|j ||d}|s t  |¡‚|d  ¡S) z&Run SHOW CREATE TABLE for a ``Table``.NzSHOW CREATE TABLE %sT©Zskip_user_error_eventséz©Zreplace_contextrWr) rêr³Úexecution_optionsrQr/Ú DBAPIErrorrSÚorigr4Úraise_ÚNoSuchTableErrorr^Ústrip) rár!r½rÊr°ÚstrXrr²rãrãrär±a s$ ÿþ zMySQLDialect._show_create_tablec CsØ|dkr|j |¡}d|}d\}}z z|jdd |¡}Wnvtjk r²}zV| |j¡} | dkrzt j t  |¡|dn(| dkr t j t  d ||f¡|dn‚W5d}~XYnX|j ||d }W5|rÒ| ¡X|S) z7Run DESCRIBE for a ``Table`` and return processed rows.Nz DESCRIBE %s)NNTr½r¾r¿iLz1Table or view named %s could not be reflected: %srW)rêr³r'rÀrQr/rÁrSrÂr4rÃrÄZUnreflectableTableErrorrY) rár!r½rÊr°rÆrXr³rÚcoderãrãrär¶y s@ ÿþ ÿÿÿû zMySQLDialect._describe_table)NNNN)TF)TF)N)N)N)N)N)N)N)N)N)N)N)N)N)N)N)N)N)N)N)NN)NN)jrïrðrñÚ__doc__r Zsupports_statement_cacheZsupports_alterZsupports_native_booleanZmax_identifier_lengthZmax_index_name_lengthZmax_constraint_name_lengthZsupports_native_enumrˆZsequences_optionalrZZsupports_default_valuesZsupports_default_metavalueZsupports_sane_rowcountZsupports_sane_multi_rowcountZsupports_multivalues_insertZsupports_commentsZinline_commentsZdefault_paramstyleÚcolspecsZcte_follows_insertròZstatement_compilerr‚Z ddl_compilerrÃr>Ú ischema_namesrrûrwr?rGrxr‡ZTabler2ZUpdater»ZIndexZconstruct_argumentsr r r¶r$rr#r2Ú classmethodr7r9r8rrHrJrLrMrRrUrYr\r^rSrcrkrnrmr Úcacherqryr{Úpropertyr@rr,r‚rƒr†r‡rŒrr“rœršr¦r§r¬r­r²rˆr4Zmemoized_propertyrµr´rtrwrºrurvr±r¶rãrãrãrär s    üþüû  üÿ    $ ÿ ÿ                 *S   +ÿ  ÿ    ÿ rc@s8eZdZdZddddddœZdd „Zd d „Zd d „ZdS)rVzâReturn unicode-decoded values based on type inspection. Smooth over data type issues (esp. with alpha driver versions) and normalize strings as Unicode regardless of user-configured driver encoding settings. Úkoi8_rÚkoi8_uz utf-16-beÚutf8Úujis)Zkoi8rZkoi8uÚutf16Zutf8mb4ZeucjpmscCs||_|j ||¡|_dSrß)ÚrowproxyÚ_encoding_compatrprÊ)rárÓrÊrãrãrär ² sz_DecodingRow.__init__cCsB|j|}t|tƒr| ¡}|jr:t|tjƒr:| |j¡S|SdSrß)rÓrÚ_arrayÚtostringrÊr4Ú binary_typer1)rárwÚitemrãrãräÚ __getitem__¶ s    z_DecodingRow.__getitem__cCsDt|j|ƒ}t|tƒr| ¡}|jrr?r@rArBr¶rr=ÚIrÎràZSET_REZMSTimeZMSSetZMSEnumZ MSLongBlobZ MSMediumBlobZ MSTinyBlobZMSBlobZMSBinaryZ MSVarBinaryZMSNCharZ MSNVarCharZMSCharZMSStringZ MSLongTextZ MSMediumTextZ MSTinyTextZMSTextZMSYearZ MSTimeStampZMSBitZMSSmallIntegerZ MSTinyIntegerZMSMediumIntegerZ MSBigIntegerZ MSNumericZ MSDecimalZMSDoubleZMSRealZMSFloatZ MSIntegerrr?r=ÚEnumZ MatchTyperÉrÊZDefaultExecutionContextrÞr~ròZ DDLCompilerr‚ZGenericTypeCompilerrÃZIdentifierPreparerrZ class_loggerrrÚobjectrVrãrãrãräÚsÒ)                                                                 €€ßÿ( þ ÿõÝ'&$0