Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/base.py: 29%

Shortcuts on this page

r m x   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1079 statements  

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 

8r""" 

9 

10.. dialect:: mysql 

11 :name: MySQL / MariaDB 

12 :full_support: 5.6, 5.7, 8.0 / 10.8, 10.9 

13 :normal_support: 5.6+ / 10+ 

14 :best_effort: 5.0.2+ / 5.0.2+ 

15 

16Supported Versions and Features 

17------------------------------- 

18 

19SQLAlchemy supports MySQL starting with version 5.0.2 through modern releases, 

20as well as all modern versions of MariaDB. See the official MySQL 

21documentation for detailed information about features supported in any given 

22server release. 

23 

24.. versionchanged:: 1.4 minimum MySQL version supported is now 5.0.2. 

25 

26MariaDB Support 

27~~~~~~~~~~~~~~~ 

28 

29The MariaDB variant of MySQL retains fundamental compatibility with MySQL's 

30protocols however the development of these two products continues to diverge. 

31Within the realm of SQLAlchemy, the two databases have a small number of 

32syntactical and behavioral differences that SQLAlchemy accommodates automatically. 

33To connect to a MariaDB database, no changes to the database URL are required:: 

34 

35 

36 engine = create_engine("mysql+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4") 

37 

38Upon first connect, the SQLAlchemy dialect employs a 

39server version detection scheme that determines if the 

40backing database reports as MariaDB. Based on this flag, the dialect 

41can make different choices in those of areas where its behavior 

42must be different. 

43 

44.. _mysql_mariadb_only_mode: 

45 

46MariaDB-Only Mode 

47~~~~~~~~~~~~~~~~~ 

48 

49The dialect also supports an **optional** "MariaDB-only" mode of connection, which may be 

50useful for the case where an application makes use of MariaDB-specific features 

51and is not compatible with a MySQL database. To use this mode of operation, 

52replace the "mysql" token in the above URL with "mariadb":: 

53 

54 engine = create_engine("mariadb+pymysql://user:pass@some_mariadb/dbname?charset=utf8mb4") 

55 

56The above engine, upon first connect, will raise an error if the server version 

57detection detects that the backing database is not MariaDB. 

58 

59When using an engine with ``"mariadb"`` as the dialect name, **all mysql-specific options 

60that include the name "mysql" in them are now named with "mariadb"**. This means 

61options like ``mysql_engine`` should be named ``mariadb_engine``, etc. Both 

62"mysql" and "mariadb" options can be used simultaneously for applications that 

63use URLs with both "mysql" and "mariadb" dialects:: 

64 

65 my_table = Table( 

66 "mytable", 

67 metadata, 

68 Column("id", Integer, primary_key=True), 

69 Column("textdata", String(50)), 

70 mariadb_engine="InnoDB", 

71 mysql_engine="InnoDB", 

72 ) 

73 

74 Index( 

75 "textdata_ix", 

76 my_table.c.textdata, 

77 mysql_prefix="FULLTEXT", 

78 mariadb_prefix="FULLTEXT", 

79 ) 

80 

81Similar behavior will occur when the above structures are reflected, i.e. the 

82"mariadb" prefix will be present in the option names when the database URL 

83is based on the "mariadb" name. 

84 

85.. versionadded:: 1.4 Added "mariadb" dialect name supporting "MariaDB-only mode" 

86 for the MySQL dialect. 

87 

88.. _mysql_connection_timeouts: 

89 

90Connection Timeouts and Disconnects 

91----------------------------------- 

92 

93MySQL / MariaDB feature an automatic connection close behavior, for connections that 

94have been idle for a fixed period of time, defaulting to eight hours. 

95To circumvent having this issue, use 

96the :paramref:`_sa.create_engine.pool_recycle` option which ensures that 

97a connection will be discarded and replaced with a new one if it has been 

98present in the pool for a fixed number of seconds:: 

99 

100 engine = create_engine('mysql+mysqldb://...', pool_recycle=3600) 

101 

102For more comprehensive disconnect detection of pooled connections, including 

103accommodation of server restarts and network issues, a pre-ping approach may 

104be employed. See :ref:`pool_disconnects` for current approaches. 

105 

106.. seealso:: 

107 

108 :ref:`pool_disconnects` - Background on several techniques for dealing 

109 with timed out connections as well as database restarts. 

110 

111.. _mysql_storage_engines: 

112 

113CREATE TABLE arguments including Storage Engines 

114------------------------------------------------ 

115 

116Both MySQL's and MariaDB's CREATE TABLE syntax includes a wide array of special options, 

117including ``ENGINE``, ``CHARSET``, ``MAX_ROWS``, ``ROW_FORMAT``, 

118``INSERT_METHOD``, and many more. 

119To accommodate the rendering of these arguments, specify the form 

120``mysql_argument_name="value"``. For example, to specify a table with 

121``ENGINE`` of ``InnoDB``, ``CHARSET`` of ``utf8mb4``, and ``KEY_BLOCK_SIZE`` 

122of ``1024``:: 

123 

124 Table('mytable', metadata, 

125 Column('data', String(32)), 

126 mysql_engine='InnoDB', 

127 mysql_charset='utf8mb4', 

128 mysql_key_block_size="1024" 

129 ) 

130 

131When supporting :ref:`mysql_mariadb_only_mode` mode, similar keys against 

132the "mariadb" prefix must be included as well. The values can of course 

133vary independently so that different settings on MySQL vs. MariaDB may 

134be maintained:: 

135 

136 # support both "mysql" and "mariadb-only" engine URLs 

137 

138 Table('mytable', metadata, 

139 Column('data', String(32)), 

140 

141 mysql_engine='InnoDB', 

142 mariadb_engine='InnoDB', 

143 

144 mysql_charset='utf8mb4', 

145 mariadb_charset='utf8', 

146 

147 mysql_key_block_size="1024" 

148 mariadb_key_block_size="1024" 

149 

150 ) 

151 

152The MySQL / MariaDB dialects will normally transfer any keyword specified as 

153``mysql_keyword_name`` to be rendered as ``KEYWORD_NAME`` in the 

154``CREATE TABLE`` statement. A handful of these names will render with a space 

155instead of an underscore; to support this, the MySQL dialect has awareness of 

156these particular names, which include ``DATA DIRECTORY`` 

157(e.g. ``mysql_data_directory``), ``CHARACTER SET`` (e.g. 

158``mysql_character_set``) and ``INDEX DIRECTORY`` (e.g. 

159``mysql_index_directory``). 

160 

161The most common argument is ``mysql_engine``, which refers to the storage 

162engine for the table. Historically, MySQL server installations would default 

163to ``MyISAM`` for this value, although newer versions may be defaulting 

164to ``InnoDB``. The ``InnoDB`` engine is typically preferred for its support 

165of transactions and foreign keys. 

166 

167A :class:`_schema.Table` 

168that is created in a MySQL / MariaDB database with a storage engine 

169of ``MyISAM`` will be essentially non-transactional, meaning any 

170INSERT/UPDATE/DELETE statement referring to this table will be invoked as 

171autocommit. It also will have no support for foreign key constraints; while 

172the ``CREATE TABLE`` statement accepts foreign key options, when using the 

173``MyISAM`` storage engine these arguments are discarded. Reflecting such a 

174table will also produce no foreign key constraint information. 

175 

176For fully atomic transactions as well as support for foreign key 

177constraints, all participating ``CREATE TABLE`` statements must specify a 

178transactional engine, which in the vast majority of cases is ``InnoDB``. 

179 

180 

181Case Sensitivity and Table Reflection 

182------------------------------------- 

183 

184Both MySQL and MariaDB have inconsistent support for case-sensitive identifier 

185names, basing support on specific details of the underlying 

186operating system. However, it has been observed that no matter 

187what case sensitivity behavior is present, the names of tables in 

188foreign key declarations are *always* received from the database 

189as all-lower case, making it impossible to accurately reflect a 

190schema where inter-related tables use mixed-case identifier names. 

191 

192Therefore it is strongly advised that table names be declared as 

193all lower case both within SQLAlchemy as well as on the MySQL / MariaDB 

194database itself, especially if database reflection features are 

195to be used. 

196 

197.. _mysql_isolation_level: 

198 

199Transaction Isolation Level 

200--------------------------- 

201 

202All MySQL / MariaDB dialects support setting of transaction isolation level both via a 

203dialect-specific parameter :paramref:`_sa.create_engine.isolation_level` 

204accepted 

205by :func:`_sa.create_engine`, as well as the 

206:paramref:`.Connection.execution_options.isolation_level` argument as passed to 

207:meth:`_engine.Connection.execution_options`. 

208This feature works by issuing the 

209command ``SET SESSION TRANSACTION ISOLATION LEVEL <level>`` for each new 

210connection. For the special AUTOCOMMIT isolation level, DBAPI-specific 

211techniques are used. 

212 

213To set isolation level using :func:`_sa.create_engine`:: 

214 

215 engine = create_engine( 

216 "mysql://scott:tiger@localhost/test", 

217 isolation_level="READ UNCOMMITTED" 

218 ) 

219 

220To set using per-connection execution options:: 

221 

222 connection = engine.connect() 

223 connection = connection.execution_options( 

224 isolation_level="READ COMMITTED" 

225 ) 

226 

227Valid values for ``isolation_level`` include: 

228 

229* ``READ COMMITTED`` 

230* ``READ UNCOMMITTED`` 

231* ``REPEATABLE READ`` 

232* ``SERIALIZABLE`` 

233* ``AUTOCOMMIT`` 

234 

235The special ``AUTOCOMMIT`` value makes use of the various "autocommit" 

236attributes provided by specific DBAPIs, and is currently supported by 

237MySQLdb, MySQL-Client, MySQL-Connector Python, and PyMySQL. Using it, 

238the database connection will return true for the value of 

239``SELECT @@autocommit;``. 

240 

241There are also more options for isolation level configurations, such as 

242"sub-engine" objects linked to a main :class:`_engine.Engine` which each apply 

243different isolation level settings. See the discussion at 

244:ref:`dbapi_autocommit` for background. 

245 

246.. seealso:: 

247 

248 :ref:`dbapi_autocommit` 

249 

250AUTO_INCREMENT Behavior 

251----------------------- 

252 

253When creating tables, SQLAlchemy will automatically set ``AUTO_INCREMENT`` on 

254the first :class:`.Integer` primary key column which is not marked as a 

255foreign key:: 

256 

257 >>> t = Table('mytable', metadata, 

258 ... Column('mytable_id', Integer, primary_key=True) 

259 ... ) 

260 >>> t.create() 

261 CREATE TABLE mytable ( 

262 id INTEGER NOT NULL AUTO_INCREMENT, 

263 PRIMARY KEY (id) 

264 ) 

265 

266You can disable this behavior by passing ``False`` to the 

267:paramref:`_schema.Column.autoincrement` argument of :class:`_schema.Column`. 

268This flag 

269can also be used to enable auto-increment on a secondary column in a 

270multi-column key for some storage engines:: 

271 

272 Table('mytable', metadata, 

273 Column('gid', Integer, primary_key=True, autoincrement=False), 

274 Column('id', Integer, primary_key=True) 

275 ) 

276 

277.. _mysql_ss_cursors: 

278 

279Server Side Cursors 

280------------------- 

281 

282Server-side cursor support is available for the mysqlclient, PyMySQL, 

283mariadbconnector dialects and may also be available in others. This makes use 

284of either the "buffered=True/False" flag if available or by using a class such 

285as ``MySQLdb.cursors.SSCursor`` or ``pymysql.cursors.SSCursor`` internally. 

286 

287 

288Server side cursors are enabled on a per-statement basis by using the 

289:paramref:`.Connection.execution_options.stream_results` connection execution 

290option:: 

291 

292 with engine.connect() as conn: 

293 result = conn.execution_options(stream_results=True).execute(text("select * from table")) 

294 

295Note that some kinds of SQL statements may not be supported with 

296server side cursors; generally, only SQL statements that return rows should be 

297used with this option. 

298 

299.. deprecated:: 1.4 The dialect-level server_side_cursors flag is deprecated 

300 and will be removed in a future release. Please use the 

301 :paramref:`_engine.Connection.stream_results` execution option for 

302 unbuffered cursor support. 

303 

304.. seealso:: 

305 

306 :ref:`engine_stream_results` 

307 

308.. _mysql_unicode: 

309 

310Unicode 

311------- 

312 

313Charset Selection 

314~~~~~~~~~~~~~~~~~ 

315 

316Most MySQL / MariaDB DBAPIs offer the option to set the client character set for 

317a connection. This is typically delivered using the ``charset`` parameter 

318in the URL, such as:: 

319 

320 e = create_engine( 

321 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4") 

322 

323This charset is the **client character set** for the connection. Some 

324MySQL DBAPIs will default this to a value such as ``latin1``, and some 

325will make use of the ``default-character-set`` setting in the ``my.cnf`` 

326file as well. Documentation for the DBAPI in use should be consulted 

327for specific behavior. 

328 

329The encoding used for Unicode has traditionally been ``'utf8'``. However, for 

330MySQL versions 5.5.3 and MariaDB 5.5 on forward, a new MySQL-specific encoding 

331``'utf8mb4'`` has been introduced, and as of MySQL 8.0 a warning is emitted by 

332the server if plain ``utf8`` is specified within any server-side directives, 

333replaced with ``utf8mb3``. The rationale for this new encoding is due to the 

334fact that MySQL's legacy utf-8 encoding only supports codepoints up to three 

335bytes instead of four. Therefore, when communicating with a MySQL or MariaDB 

336database that includes codepoints more than three bytes in size, this new 

337charset is preferred, if supported by both the database as well as the client 

338DBAPI, as in:: 

339 

340 e = create_engine( 

341 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4") 

342 

343All modern DBAPIs should support the ``utf8mb4`` charset. 

344 

345In order to use ``utf8mb4`` encoding for a schema that was created with legacy 

346``utf8``, changes to the MySQL/MariaDB schema and/or server configuration may be 

347required. 

348 

349.. seealso:: 

350 

351 `The utf8mb4 Character Set \ 

352 <https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html>`_ - \ 

353 in the MySQL documentation 

354 

355.. _mysql_binary_introducer: 

356 

357Dealing with Binary Data Warnings and Unicode 

358~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

359 

360MySQL versions 5.6, 5.7 and later (not MariaDB at the time of this writing) now 

361emit a warning when attempting to pass binary data to the database, while a 

362character set encoding is also in place, when the binary data itself is not 

363valid for that encoding:: 

364 

365 default.py:509: Warning: (1300, "Invalid utf8mb4 character string: 

366 'F9876A'") 

367 cursor.execute(statement, parameters) 

368 

369This warning is due to the fact that the MySQL client library is attempting to 

370interpret the binary string as a unicode object even if a datatype such 

371as :class:`.LargeBinary` is in use. To resolve this, the SQL statement requires 

372a binary "character set introducer" be present before any non-NULL value 

373that renders like this:: 

374 

375 INSERT INTO table (data) VALUES (_binary %s) 

376 

377These character set introducers are provided by the DBAPI driver, assuming the 

378use of mysqlclient or PyMySQL (both of which are recommended). Add the query 

379string parameter ``binary_prefix=true`` to the URL to repair this warning:: 

380 

381 # mysqlclient 

382 engine = create_engine( 

383 "mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true") 

384 

385 # PyMySQL 

386 engine = create_engine( 

387 "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true") 

388 

389 

390The ``binary_prefix`` flag may or may not be supported by other MySQL drivers. 

391 

392SQLAlchemy itself cannot render this ``_binary`` prefix reliably, as it does 

393not work with the NULL value, which is valid to be sent as a bound parameter. 

394As the MySQL driver renders parameters directly into the SQL string, it's the 

395most efficient place for this additional keyword to be passed. 

396 

397.. seealso:: 

398 

399 `Character set introducers <https://dev.mysql.com/doc/refman/5.7/en/charset-introducer.html>`_ - on the MySQL website 

400 

401 

402ANSI Quoting Style 

403------------------ 

404 

405MySQL / MariaDB feature two varieties of identifier "quoting style", one using 

406backticks and the other using quotes, e.g. ```some_identifier``` vs. 

407``"some_identifier"``. All MySQL dialects detect which version 

408is in use by checking the value of :ref:`sql_mode<mysql_sql_mode>` when a connection is first 

409established with a particular :class:`_engine.Engine`. 

410This quoting style comes 

411into play when rendering table and column names as well as when reflecting 

412existing database structures. The detection is entirely automatic and 

413no special configuration is needed to use either quoting style. 

414 

415 

416.. _mysql_sql_mode: 

417 

418Changing the sql_mode 

419--------------------- 

420 

421MySQL supports operating in multiple 

422`Server SQL Modes <https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html>`_ for 

423both Servers and Clients. To change the ``sql_mode`` for a given application, a 

424developer can leverage SQLAlchemy's Events system. 

425 

426In the following example, the event system is used to set the ``sql_mode`` on 

427the ``first_connect`` and ``connect`` events:: 

428 

429 from sqlalchemy import create_engine, event 

430 

431 eng = create_engine("mysql://scott:tiger@localhost/test", echo='debug') 

432 

433 # `insert=True` will ensure this is the very first listener to run 

434 @event.listens_for(eng, "connect", insert=True) 

435 def connect(dbapi_connection, connection_record): 

436 cursor = dbapi_connection.cursor() 

437 cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'") 

438 

439 conn = eng.connect() 

440 

441In the example illustrated above, the "connect" event will invoke the "SET" 

442statement on the connection at the moment a particular DBAPI connection is 

443first created for a given Pool, before the connection is made available to the 

444connection pool. Additionally, because the function was registered with 

445``insert=True``, it will be prepended to the internal list of registered 

446functions. 

447 

448 

449MySQL / MariaDB SQL Extensions 

450------------------------------ 

451 

452Many of the MySQL / MariaDB SQL extensions are handled through SQLAlchemy's generic 

453function and operator support:: 

454 

455 table.select(table.c.password==func.md5('plaintext')) 

456 table.select(table.c.username.op('regexp')('^[a-d]')) 

457 

458And of course any valid SQL statement can be executed as a string as well. 

459 

460Some limited direct support for MySQL / MariaDB extensions to SQL is currently 

461available. 

462 

463* INSERT..ON DUPLICATE KEY UPDATE: See 

464 :ref:`mysql_insert_on_duplicate_key_update` 

465 

466* SELECT pragma, use :meth:`_expression.Select.prefix_with` and 

467 :meth:`_query.Query.prefix_with`:: 

468 

469 select(...).prefix_with(['HIGH_PRIORITY', 'SQL_SMALL_RESULT']) 

470 

471* UPDATE with LIMIT:: 

472 

473 update(..., mysql_limit=10, mariadb_limit=10) 

474 

475* optimizer hints, use :meth:`_expression.Select.prefix_with` and 

476 :meth:`_query.Query.prefix_with`:: 

477 

478 select(...).prefix_with("/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */") 

479 

480* index hints, use :meth:`_expression.Select.with_hint` and 

481 :meth:`_query.Query.with_hint`:: 

482 

483 select(...).with_hint(some_table, "USE INDEX xyz") 

484 

485* MATCH operator support:: 

486 

487 from sqlalchemy.dialects.mysql import match 

488 select(...).where(match(col1, col2, against="some expr").in_boolean_mode()) 

489 

490 .. seealso:: 

491 

492 :class:`_mysql.match` 

493 

494.. _mysql_insert_on_duplicate_key_update: 

495 

496INSERT...ON DUPLICATE KEY UPDATE (Upsert) 

497------------------------------------------ 

498 

499MySQL / MariaDB allow "upserts" (update or insert) 

500of rows into a table via the ``ON DUPLICATE KEY UPDATE`` clause of the 

501``INSERT`` statement. A candidate row will only be inserted if that row does 

502not match an existing primary or unique key in the table; otherwise, an UPDATE 

503will be performed. The statement allows for separate specification of the 

504values to INSERT versus the values for UPDATE. 

505 

506SQLAlchemy provides ``ON DUPLICATE KEY UPDATE`` support via the MySQL-specific 

507:func:`.mysql.insert()` function, which provides 

508the generative method :meth:`~.mysql.Insert.on_duplicate_key_update`: 

509 

510.. sourcecode:: pycon+sql 

511 

512 >>> from sqlalchemy.dialects.mysql import insert 

513 

514 >>> insert_stmt = insert(my_table).values( 

515 ... id='some_existing_id', 

516 ... data='inserted value') 

517 

518 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( 

519 ... data=insert_stmt.inserted.data, 

520 ... status='U' 

521 ... ) 

522 >>> print(on_duplicate_key_stmt) 

523 {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) 

524 ON DUPLICATE KEY UPDATE data = VALUES(data), status = %s 

525 

526 

527Unlike PostgreSQL's "ON CONFLICT" phrase, the "ON DUPLICATE KEY UPDATE" 

528phrase will always match on any primary key or unique key, and will always 

529perform an UPDATE if there's a match; there are no options for it to raise 

530an error or to skip performing an UPDATE. 

531 

532``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already 

533existing row, using any combination of new values as well as values 

534from the proposed insertion. These values are normally specified using 

535keyword arguments passed to the 

536:meth:`_mysql.Insert.on_duplicate_key_update` 

537given column key values (usually the name of the column, unless it 

538specifies :paramref:`_schema.Column.key` 

539) as keys and literal or SQL expressions 

540as values: 

541 

542.. sourcecode:: pycon+sql 

543 

544 >>> insert_stmt = insert(my_table).values( 

545 ... id='some_existing_id', 

546 ... data='inserted value') 

547 

548 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( 

549 ... data="some data", 

550 ... updated_at=func.current_timestamp(), 

551 ... ) 

552 

553 >>> print(on_duplicate_key_stmt) 

554 {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) 

555 ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP 

556 

557In a manner similar to that of :meth:`.UpdateBase.values`, other parameter 

558forms are accepted, including a single dictionary: 

559 

560.. sourcecode:: pycon+sql 

561 

562 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( 

563 ... {"data": "some data", "updated_at": func.current_timestamp()}, 

564 ... ) 

565 

566as well as a list of 2-tuples, which will automatically provide 

567a parameter-ordered UPDATE statement in a manner similar to that described 

568at :ref:`tutorial_parameter_ordered_updates`. Unlike the :class:`_expression.Update` 

569object, 

570no special flag is needed to specify the intent since the argument form is 

571this context is unambiguous: 

572 

573.. sourcecode:: pycon+sql 

574 

575 >>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( 

576 ... [ 

577 ... ("data", "some data"), 

578 ... ("updated_at", func.current_timestamp()), 

579 ... ] 

580 ... ) 

581 

582 >>> print(on_duplicate_key_stmt) 

583 {opensql}INSERT INTO my_table (id, data) VALUES (%s, %s) 

584 ON DUPLICATE KEY UPDATE data = %s, updated_at = CURRENT_TIMESTAMP 

585 

586.. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within 

587 MySQL ON DUPLICATE KEY UPDATE 

588 

589.. warning:: 

590 

591 The :meth:`_mysql.Insert.on_duplicate_key_update` 

592 method does **not** take into 

593 account Python-side default UPDATE values or generation functions, e.g. 

594 e.g. those specified using :paramref:`_schema.Column.onupdate`. 

595 These values will not be exercised for an ON DUPLICATE KEY style of UPDATE, 

596 unless they are manually specified explicitly in the parameters. 

597 

598 

599 

600In order to refer to the proposed insertion row, the special alias 

601:attr:`_mysql.Insert.inserted` is available as an attribute on 

602the :class:`_mysql.Insert` object; this object is a 

603:class:`_expression.ColumnCollection` which contains all columns of the target 

604table: 

605 

606.. sourcecode:: pycon+sql 

607 

608 >>> stmt = insert(my_table).values( 

609 ... id='some_id', 

610 ... data='inserted value', 

611 ... author='jlh') 

612 

613 >>> do_update_stmt = stmt.on_duplicate_key_update( 

614 ... data="updated value", 

615 ... author=stmt.inserted.author 

616 ... ) 

617 

618 >>> print(do_update_stmt) 

619 {opensql}INSERT INTO my_table (id, data, author) VALUES (%s, %s, %s) 

620 ON DUPLICATE KEY UPDATE data = %s, author = VALUES(author) 

621 

622When rendered, the "inserted" namespace will produce the expression 

623``VALUES(<columnname>)``. 

624 

625.. versionadded:: 1.2 Added support for MySQL ON DUPLICATE KEY UPDATE clause 

626 

627 

628 

629rowcount Support 

630---------------- 

631 

632SQLAlchemy standardizes the DBAPI ``cursor.rowcount`` attribute to be the 

633usual definition of "number of rows matched by an UPDATE or DELETE" statement. 

634This is in contradiction to the default setting on most MySQL DBAPI drivers, 

635which is "number of rows actually modified/deleted". For this reason, the 

636SQLAlchemy MySQL dialects always add the ``constants.CLIENT.FOUND_ROWS`` 

637flag, or whatever is equivalent for the target dialect, upon connection. 

638This setting is currently hardcoded. 

639 

640.. seealso:: 

641 

642 :attr:`_engine.CursorResult.rowcount` 

643 

644 

645.. _mysql_indexes: 

646 

647MySQL / MariaDB- Specific Index Options 

648----------------------------------------- 

649 

650MySQL and MariaDB-specific extensions to the :class:`.Index` construct are available. 

651 

652Index Length 

653~~~~~~~~~~~~~ 

654 

655MySQL and MariaDB both provide an option to create index entries with a certain length, where 

656"length" refers to the number of characters or bytes in each value which will 

657become part of the index. SQLAlchemy provides this feature via the 

658``mysql_length`` and/or ``mariadb_length`` parameters:: 

659 

660 Index('my_index', my_table.c.data, mysql_length=10, mariadb_length=10) 

661 

662 Index('a_b_idx', my_table.c.a, my_table.c.b, mysql_length={'a': 4, 

663 'b': 9}) 

664 

665 Index('a_b_idx', my_table.c.a, my_table.c.b, mariadb_length={'a': 4, 

666 'b': 9}) 

667 

668Prefix lengths are given in characters for nonbinary string types and in bytes 

669for binary string types. The value passed to the keyword argument *must* be 

670either an integer (and, thus, specify the same prefix length value for all 

671columns of the index) or a dict in which keys are column names and values are 

672prefix length values for corresponding columns. MySQL and MariaDB only allow a 

673length for a column of an index if it is for a CHAR, VARCHAR, TEXT, BINARY, 

674VARBINARY and BLOB. 

675 

676Index Prefixes 

677~~~~~~~~~~~~~~ 

678 

679MySQL storage engines permit you to specify an index prefix when creating 

680an index. SQLAlchemy provides this feature via the 

681``mysql_prefix`` parameter on :class:`.Index`:: 

682 

683 Index('my_index', my_table.c.data, mysql_prefix='FULLTEXT') 

684 

685The value passed to the keyword argument will be simply passed through to the 

686underlying CREATE INDEX, so it *must* be a valid index prefix for your MySQL 

687storage engine. 

688 

689.. versionadded:: 1.1.5 

690 

691.. seealso:: 

692 

693 `CREATE INDEX <https://dev.mysql.com/doc/refman/5.0/en/create-index.html>`_ - MySQL documentation 

694 

695Index Types 

696~~~~~~~~~~~~~ 

697 

698Some MySQL storage engines permit you to specify an index type when creating 

699an index or primary key constraint. SQLAlchemy provides this feature via the 

700``mysql_using`` parameter on :class:`.Index`:: 

701 

702 Index('my_index', my_table.c.data, mysql_using='hash', mariadb_using='hash') 

703 

704As well as the ``mysql_using`` parameter on :class:`.PrimaryKeyConstraint`:: 

705 

706 PrimaryKeyConstraint("data", mysql_using='hash', mariadb_using='hash') 

707 

708The value passed to the keyword argument will be simply passed through to the 

709underlying CREATE INDEX or PRIMARY KEY clause, so it *must* be a valid index 

710type for your MySQL storage engine. 

711 

712More information can be found at: 

713 

714https://dev.mysql.com/doc/refman/5.0/en/create-index.html 

715 

716https://dev.mysql.com/doc/refman/5.0/en/create-table.html 

717 

718Index Parsers 

719~~~~~~~~~~~~~ 

720 

721CREATE FULLTEXT INDEX in MySQL also supports a "WITH PARSER" option. This 

722is available using the keyword argument ``mysql_with_parser``:: 

723 

724 Index( 

725 'my_index', my_table.c.data, 

726 mysql_prefix='FULLTEXT', mysql_with_parser="ngram", 

727 mariadb_prefix='FULLTEXT', mariadb_with_parser="ngram", 

728 ) 

729 

730.. versionadded:: 1.3 

731 

732 

733.. _mysql_foreign_keys: 

734 

735MySQL / MariaDB Foreign Keys 

736----------------------------- 

737 

738MySQL and MariaDB's behavior regarding foreign keys has some important caveats. 

739 

740Foreign Key Arguments to Avoid 

741~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

742 

743Neither MySQL nor MariaDB support the foreign key arguments "DEFERRABLE", "INITIALLY", 

744or "MATCH". Using the ``deferrable`` or ``initially`` keyword argument with 

745:class:`_schema.ForeignKeyConstraint` or :class:`_schema.ForeignKey` 

746will have the effect of 

747these keywords being rendered in a DDL expression, which will then raise an 

748error on MySQL or MariaDB. In order to use these keywords on a foreign key while having 

749them ignored on a MySQL / MariaDB backend, use a custom compile rule:: 

750 

751 from sqlalchemy.ext.compiler import compiles 

752 from sqlalchemy.schema import ForeignKeyConstraint 

753 

754 @compiles(ForeignKeyConstraint, "mysql", "mariadb") 

755 def process(element, compiler, **kw): 

756 element.deferrable = element.initially = None 

757 return compiler.visit_foreign_key_constraint(element, **kw) 

758 

759The "MATCH" keyword is in fact more insidious, and is explicitly disallowed 

760by SQLAlchemy in conjunction with the MySQL or MariaDB backends. This argument is 

761silently ignored by MySQL / MariaDB, but in addition has the effect of ON UPDATE and ON 

762DELETE options also being ignored by the backend. Therefore MATCH should 

763never be used with the MySQL / MariaDB backends; as is the case with DEFERRABLE and 

764INITIALLY, custom compilation rules can be used to correct a 

765ForeignKeyConstraint at DDL definition time. 

766 

767Reflection of Foreign Key Constraints 

768~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

769 

770Not all MySQL / MariaDB storage engines support foreign keys. When using the 

771very common ``MyISAM`` MySQL storage engine, the information loaded by table 

772reflection will not include foreign keys. For these tables, you may supply a 

773:class:`~sqlalchemy.ForeignKeyConstraint` at reflection time:: 

774 

775 Table('mytable', metadata, 

776 ForeignKeyConstraint(['other_id'], ['othertable.other_id']), 

777 autoload_with=engine 

778 ) 

779 

780.. seealso:: 

781 

782 :ref:`mysql_storage_engines` 

783 

784.. _mysql_unique_constraints: 

785 

786MySQL / MariaDB Unique Constraints and Reflection 

787---------------------------------------------------- 

788 

789SQLAlchemy supports both the :class:`.Index` construct with the 

790flag ``unique=True``, indicating a UNIQUE index, as well as the 

791:class:`.UniqueConstraint` construct, representing a UNIQUE constraint. 

792Both objects/syntaxes are supported by MySQL / MariaDB when emitting DDL to create 

793these constraints. However, MySQL / MariaDB does not have a unique constraint 

794construct that is separate from a unique index; that is, the "UNIQUE" 

795constraint on MySQL / MariaDB is equivalent to creating a "UNIQUE INDEX". 

796 

797When reflecting these constructs, the 

798:meth:`_reflection.Inspector.get_indexes` 

799and the :meth:`_reflection.Inspector.get_unique_constraints` 

800methods will **both** 

801return an entry for a UNIQUE index in MySQL / MariaDB. However, when performing 

802full table reflection using ``Table(..., autoload_with=engine)``, 

803the :class:`.UniqueConstraint` construct is 

804**not** part of the fully reflected :class:`_schema.Table` construct under any 

805circumstances; this construct is always represented by a :class:`.Index` 

806with the ``unique=True`` setting present in the :attr:`_schema.Table.indexes` 

807collection. 

808 

809 

810TIMESTAMP / DATETIME issues 

811--------------------------- 

812 

813.. _mysql_timestamp_onupdate: 

814 

815Rendering ON UPDATE CURRENT TIMESTAMP for MySQL / MariaDB's explicit_defaults_for_timestamp 

816~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

817 

818MySQL / MariaDB have historically expanded the DDL for the :class:`_types.TIMESTAMP` 

819datatype into the phrase "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 

820CURRENT_TIMESTAMP", which includes non-standard SQL that automatically updates 

821the column with the current timestamp when an UPDATE occurs, eliminating the 

822usual need to use a trigger in such a case where server-side update changes are 

823desired. 

824 

825MySQL 5.6 introduced a new flag `explicit_defaults_for_timestamp 

826<https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html 

827#sysvar_explicit_defaults_for_timestamp>`_ which disables the above behavior, 

828and in MySQL 8 this flag defaults to true, meaning in order to get a MySQL 

829"on update timestamp" without changing this flag, the above DDL must be 

830rendered explicitly. Additionally, the same DDL is valid for use of the 

831``DATETIME`` datatype as well. 

832 

833SQLAlchemy's MySQL dialect does not yet have an option to generate 

834MySQL's "ON UPDATE CURRENT_TIMESTAMP" clause, noting that this is not a general 

835purpose "ON UPDATE" as there is no such syntax in standard SQL. SQLAlchemy's 

836:paramref:`_schema.Column.server_onupdate` parameter is currently not related 

837to this special MySQL behavior. 

838 

839To generate this DDL, make use of the :paramref:`_schema.Column.server_default` 

840parameter and pass a textual clause that also includes the ON UPDATE clause:: 

841 

842 from sqlalchemy import Table, MetaData, Column, Integer, String, TIMESTAMP 

843 from sqlalchemy import text 

844 

845 metadata = MetaData() 

846 

847 mytable = Table( 

848 "mytable", 

849 metadata, 

850 Column('id', Integer, primary_key=True), 

851 Column('data', String(50)), 

852 Column( 

853 'last_updated', 

854 TIMESTAMP, 

855 server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP") 

856 ) 

857 ) 

858 

859The same instructions apply to use of the :class:`_types.DateTime` and 

860:class:`_types.DATETIME` datatypes:: 

861 

862 from sqlalchemy import DateTime 

863 

864 mytable = Table( 

865 "mytable", 

866 metadata, 

867 Column('id', Integer, primary_key=True), 

868 Column('data', String(50)), 

869 Column( 

870 'last_updated', 

871 DateTime, 

872 server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP") 

873 ) 

874 ) 

875 

876 

877Even though the :paramref:`_schema.Column.server_onupdate` feature does not 

878generate this DDL, it still may be desirable to signal to the ORM that this 

879updated value should be fetched. This syntax looks like the following:: 

880 

881 from sqlalchemy.schema import FetchedValue 

882 

883 class MyClass(Base): 

884 __tablename__ = 'mytable' 

885 

886 id = Column(Integer, primary_key=True) 

887 data = Column(String(50)) 

888 last_updated = Column( 

889 TIMESTAMP, 

890 server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), 

891 server_onupdate=FetchedValue() 

892 ) 

893 

894 

895.. _mysql_timestamp_null: 

896 

897TIMESTAMP Columns and NULL 

898~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

899 

900MySQL historically enforces that a column which specifies the 

901TIMESTAMP datatype implicitly includes a default value of 

902CURRENT_TIMESTAMP, even though this is not stated, and additionally 

903sets the column as NOT NULL, the opposite behavior vs. that of all 

904other datatypes:: 

905 

906 mysql> CREATE TABLE ts_test ( 

907 -> a INTEGER, 

908 -> b INTEGER NOT NULL, 

909 -> c TIMESTAMP, 

910 -> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 

911 -> e TIMESTAMP NULL); 

912 Query OK, 0 rows affected (0.03 sec) 

913 

914 mysql> SHOW CREATE TABLE ts_test; 

915 +---------+----------------------------------------------------- 

916 | Table | Create Table 

917 +---------+----------------------------------------------------- 

918 | ts_test | CREATE TABLE `ts_test` ( 

919 `a` int(11) DEFAULT NULL, 

920 `b` int(11) NOT NULL, 

921 `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 

922 `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 

923 `e` timestamp NULL DEFAULT NULL 

924 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

925 

926Above, we see that an INTEGER column defaults to NULL, unless it is specified 

927with NOT NULL. But when the column is of type TIMESTAMP, an implicit 

928default of CURRENT_TIMESTAMP is generated which also coerces the column 

929to be a NOT NULL, even though we did not specify it as such. 

930 

931This behavior of MySQL can be changed on the MySQL side using the 

932`explicit_defaults_for_timestamp 

933<https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html 

934#sysvar_explicit_defaults_for_timestamp>`_ configuration flag introduced in 

935MySQL 5.6. With this server setting enabled, TIMESTAMP columns behave like 

936any other datatype on the MySQL side with regards to defaults and nullability. 

937 

938However, to accommodate the vast majority of MySQL databases that do not 

939specify this new flag, SQLAlchemy emits the "NULL" specifier explicitly with 

940any TIMESTAMP column that does not specify ``nullable=False``. In order to 

941accommodate newer databases that specify ``explicit_defaults_for_timestamp``, 

942SQLAlchemy also emits NOT NULL for TIMESTAMP columns that do specify 

943``nullable=False``. The following example illustrates:: 

944 

945 from sqlalchemy import MetaData, Integer, Table, Column, text 

946 from sqlalchemy.dialects.mysql import TIMESTAMP 

947 

948 m = MetaData() 

949 t = Table('ts_test', m, 

950 Column('a', Integer), 

951 Column('b', Integer, nullable=False), 

952 Column('c', TIMESTAMP), 

953 Column('d', TIMESTAMP, nullable=False) 

954 ) 

955 

956 

957 from sqlalchemy import create_engine 

958 e = create_engine("mysql://scott:tiger@localhost/test", echo=True) 

959 m.create_all(e) 

960 

961output:: 

962 

963 CREATE TABLE ts_test ( 

964 a INTEGER, 

965 b INTEGER NOT NULL, 

966 c TIMESTAMP NULL, 

967 d TIMESTAMP NOT NULL 

968 ) 

969 

970.. versionchanged:: 1.0.0 - SQLAlchemy now renders NULL or NOT NULL in all 

971 cases for TIMESTAMP columns, to accommodate 

972 ``explicit_defaults_for_timestamp``. Prior to this version, it will 

973 not render "NOT NULL" for a TIMESTAMP column that is ``nullable=False``. 

974 

975""" # noqa 

976 

977from array import array as _array 

978from collections import defaultdict 

979from itertools import compress 

980import re 

981 

982from sqlalchemy import literal_column 

983from sqlalchemy import text 

984from sqlalchemy.sql import visitors 

985from . import reflection as _reflection 

986from .enumerated import ENUM 

987from .enumerated import SET 

988from .json import JSON 

989from .json import JSONIndexType 

990from .json import JSONPathType 

991from .reserved_words import RESERVED_WORDS_MARIADB 

992from .reserved_words import RESERVED_WORDS_MYSQL 

993from .types import _FloatType 

994from .types import _IntegerType 

995from .types import _MatchType 

996from .types import _NumericType 

997from .types import _StringType 

998from .types import BIGINT 

999from .types import BIT 

1000from .types import CHAR 

1001from .types import DATETIME 

1002from .types import DECIMAL 

1003from .types import DOUBLE 

1004from .types import FLOAT 

1005from .types import INTEGER 

1006from .types import LONGBLOB 

1007from .types import LONGTEXT 

1008from .types import MEDIUMBLOB 

1009from .types import MEDIUMINT 

1010from .types import MEDIUMTEXT 

1011from .types import NCHAR 

1012from .types import NUMERIC 

1013from .types import NVARCHAR 

1014from .types import REAL 

1015from .types import SMALLINT 

1016from .types import TEXT 

1017from .types import TIME 

1018from .types import TIMESTAMP 

1019from .types import TINYBLOB 

1020from .types import TINYINT 

1021from .types import TINYTEXT 

1022from .types import VARCHAR 

1023from .types import YEAR 

1024from ... import exc 

1025from ... import log 

1026from ... import schema as sa_schema 

1027from ... import sql 

1028from ... import types as sqltypes 

1029from ... import util 

1030from ...engine import default 

1031from ...engine import reflection 

1032from ...sql import coercions 

1033from ...sql import compiler 

1034from ...sql import elements 

1035from ...sql import functions 

1036from ...sql import operators 

1037from ...sql import roles 

1038from ...sql import util as sql_util 

1039from ...sql.sqltypes import Unicode 

1040from ...types import BINARY 

1041from ...types import BLOB 

1042from ...types import BOOLEAN 

1043from ...types import DATE 

1044from ...types import VARBINARY 

1045from ...util import topological 

1046 

1047AUTOCOMMIT_RE = re.compile( 

1048 r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|LOAD +DATA|REPLACE)", 

1049 re.I | re.UNICODE, 

1050) 

1051SET_RE = re.compile( 

1052 r"\s*SET\s+(?:(?:GLOBAL|SESSION)\s+)?\w", re.I | re.UNICODE 

1053) 

1054 

1055 

1056# old names 

1057MSTime = TIME 

1058MSSet = SET 

1059MSEnum = ENUM 

1060MSLongBlob = LONGBLOB 

1061MSMediumBlob = MEDIUMBLOB 

1062MSTinyBlob = TINYBLOB 

1063MSBlob = BLOB 

1064MSBinary = BINARY 

1065MSVarBinary = VARBINARY 

1066MSNChar = NCHAR 

1067MSNVarChar = NVARCHAR 

1068MSChar = CHAR 

1069MSString = VARCHAR 

1070MSLongText = LONGTEXT 

1071MSMediumText = MEDIUMTEXT 

1072MSTinyText = TINYTEXT 

1073MSText = TEXT 

1074MSYear = YEAR 

1075MSTimeStamp = TIMESTAMP 

1076MSBit = BIT 

1077MSSmallInteger = SMALLINT 

1078MSTinyInteger = TINYINT 

1079MSMediumInteger = MEDIUMINT 

1080MSBigInteger = BIGINT 

1081MSNumeric = NUMERIC 

1082MSDecimal = DECIMAL 

1083MSDouble = DOUBLE 

1084MSReal = REAL 

1085MSFloat = FLOAT 

1086MSInteger = INTEGER 

1087 

1088colspecs = { 

1089 _IntegerType: _IntegerType, 

1090 _NumericType: _NumericType, 

1091 _FloatType: _FloatType, 

1092 sqltypes.Numeric: NUMERIC, 

1093 sqltypes.Float: FLOAT, 

1094 sqltypes.Time: TIME, 

1095 sqltypes.Enum: ENUM, 

1096 sqltypes.MatchType: _MatchType, 

1097 sqltypes.JSON: JSON, 

1098 sqltypes.JSON.JSONIndexType: JSONIndexType, 

1099 sqltypes.JSON.JSONPathType: JSONPathType, 

1100} 

1101 

1102# Everything 3.23 through 5.1 excepting OpenGIS types. 

1103ischema_names = { 

1104 "bigint": BIGINT, 

1105 "binary": BINARY, 

1106 "bit": BIT, 

1107 "blob": BLOB, 

1108 "boolean": BOOLEAN, 

1109 "char": CHAR, 

1110 "date": DATE, 

1111 "datetime": DATETIME, 

1112 "decimal": DECIMAL, 

1113 "double": DOUBLE, 

1114 "enum": ENUM, 

1115 "fixed": DECIMAL, 

1116 "float": FLOAT, 

1117 "int": INTEGER, 

1118 "integer": INTEGER, 

1119 "json": JSON, 

1120 "longblob": LONGBLOB, 

1121 "longtext": LONGTEXT, 

1122 "mediumblob": MEDIUMBLOB, 

1123 "mediumint": MEDIUMINT, 

1124 "mediumtext": MEDIUMTEXT, 

1125 "nchar": NCHAR, 

1126 "nvarchar": NVARCHAR, 

1127 "numeric": NUMERIC, 

1128 "set": SET, 

1129 "smallint": SMALLINT, 

1130 "text": TEXT, 

1131 "time": TIME, 

1132 "timestamp": TIMESTAMP, 

1133 "tinyblob": TINYBLOB, 

1134 "tinyint": TINYINT, 

1135 "tinytext": TINYTEXT, 

1136 "varbinary": VARBINARY, 

1137 "varchar": VARCHAR, 

1138 "year": YEAR, 

1139} 

1140 

1141 

1142class MySQLExecutionContext(default.DefaultExecutionContext): 

1143 def should_autocommit_text(self, statement): 

1144 return AUTOCOMMIT_RE.match(statement) 

1145 

1146 def create_server_side_cursor(self): 

1147 if self.dialect.supports_server_side_cursors: 

1148 return self._dbapi_connection.cursor(self.dialect._sscursor) 

1149 else: 

1150 raise NotImplementedError() 

1151 

1152 def fire_sequence(self, seq, type_): 

1153 return self._execute_scalar( 

1154 ( 

1155 "select nextval(%s)" 

1156 % self.identifier_preparer.format_sequence(seq) 

1157 ), 

1158 type_, 

1159 ) 

1160 

1161 

1162class MySQLCompiler(compiler.SQLCompiler): 

1163 

1164 render_table_with_column_in_update_from = True 

1165 """Overridden from base SQLCompiler value""" 

1166 

1167 extract_map = compiler.SQLCompiler.extract_map.copy() 

1168 extract_map.update({"milliseconds": "millisecond"}) 

1169 

1170 def default_from(self): 

1171 """Called when a ``SELECT`` statement has no froms, 

1172 and no ``FROM`` clause is to be appended. 

1173 

1174 """ 

1175 if self.stack: 

1176 stmt = self.stack[-1]["selectable"] 

1177 if stmt._where_criteria: 

1178 return " FROM DUAL" 

1179 

1180 return "" 

1181 

1182 def visit_random_func(self, fn, **kw): 

1183 return "rand%s" % self.function_argspec(fn) 

1184 

1185 def visit_sequence(self, seq, **kw): 

1186 return "nextval(%s)" % self.preparer.format_sequence(seq) 

1187 

1188 def visit_sysdate_func(self, fn, **kw): 

1189 return "SYSDATE()" 

1190 

1191 def _render_json_extract_from_binary(self, binary, operator, **kw): 

1192 # note we are intentionally calling upon the process() calls in the 

1193 # order in which they appear in the SQL String as this is used 

1194 # by positional parameter rendering 

1195 

1196 if binary.type._type_affinity is sqltypes.JSON: 

1197 return "JSON_EXTRACT(%s, %s)" % ( 

1198 self.process(binary.left, **kw), 

1199 self.process(binary.right, **kw), 

1200 ) 

1201 

1202 # for non-JSON, MySQL doesn't handle JSON null at all so it has to 

1203 # be explicit 

1204 case_expression = "CASE JSON_EXTRACT(%s, %s) WHEN 'null' THEN NULL" % ( 

1205 self.process(binary.left, **kw), 

1206 self.process(binary.right, **kw), 

1207 ) 

1208 

1209 if binary.type._type_affinity is sqltypes.Integer: 

1210 type_expression = ( 

1211 "ELSE CAST(JSON_EXTRACT(%s, %s) AS SIGNED INTEGER)" 

1212 % ( 

1213 self.process(binary.left, **kw), 

1214 self.process(binary.right, **kw), 

1215 ) 

1216 ) 

1217 elif binary.type._type_affinity is sqltypes.Numeric: 

1218 if ( 

1219 binary.type.scale is not None 

1220 and binary.type.precision is not None 

1221 ): 

1222 # using DECIMAL here because MySQL does not recognize NUMERIC 

1223 type_expression = ( 

1224 "ELSE CAST(JSON_EXTRACT(%s, %s) AS DECIMAL(%s, %s))" 

1225 % ( 

1226 self.process(binary.left, **kw), 

1227 self.process(binary.right, **kw), 

1228 binary.type.precision, 

1229 binary.type.scale, 

1230 ) 

1231 ) 

1232 else: 

1233 # FLOAT / REAL not added in MySQL til 8.0.17 

1234 type_expression = ( 

1235 "ELSE JSON_EXTRACT(%s, %s)+0.0000000000000000000000" 

1236 % ( 

1237 self.process(binary.left, **kw), 

1238 self.process(binary.right, **kw), 

1239 ) 

1240 ) 

1241 elif binary.type._type_affinity is sqltypes.Boolean: 

1242 # the NULL handling is particularly weird with boolean, so 

1243 # explicitly return true/false constants 

1244 type_expression = "WHEN true THEN true ELSE false" 

1245 elif binary.type._type_affinity is sqltypes.String: 

1246 # (gord): this fails with a JSON value that's a four byte unicode 

1247 # string. SQLite has the same problem at the moment 

1248 # (zzzeek): I'm not really sure. let's take a look at a test case 

1249 # that hits each backend and maybe make a requires rule for it? 

1250 type_expression = "ELSE JSON_UNQUOTE(JSON_EXTRACT(%s, %s))" % ( 

1251 self.process(binary.left, **kw), 

1252 self.process(binary.right, **kw), 

1253 ) 

1254 else: 

1255 # other affinity....this is not expected right now 

1256 type_expression = "ELSE JSON_EXTRACT(%s, %s)" % ( 

1257 self.process(binary.left, **kw), 

1258 self.process(binary.right, **kw), 

1259 ) 

1260 

1261 return case_expression + " " + type_expression + " END" 

1262 

1263 def visit_json_getitem_op_binary(self, binary, operator, **kw): 

1264 return self._render_json_extract_from_binary(binary, operator, **kw) 

1265 

1266 def visit_json_path_getitem_op_binary(self, binary, operator, **kw): 

1267 return self._render_json_extract_from_binary(binary, operator, **kw) 

1268 

1269 def visit_on_duplicate_key_update(self, on_duplicate, **kw): 

1270 statement = self.current_executable 

1271 

1272 if on_duplicate._parameter_ordering: 

1273 parameter_ordering = [ 

1274 coercions.expect(roles.DMLColumnRole, key) 

1275 for key in on_duplicate._parameter_ordering 

1276 ] 

1277 ordered_keys = set(parameter_ordering) 

1278 cols = [ 

1279 statement.table.c[key] 

1280 for key in parameter_ordering 

1281 if key in statement.table.c 

1282 ] + [c for c in statement.table.c if c.key not in ordered_keys] 

1283 else: 

1284 cols = statement.table.c 

1285 

1286 clauses = [] 

1287 # traverses through all table columns to preserve table column order 

1288 for column in (col for col in cols if col.key in on_duplicate.update): 

1289 

1290 val = on_duplicate.update[column.key] 

1291 

1292 if coercions._is_literal(val): 

1293 val = elements.BindParameter(None, val, type_=column.type) 

1294 value_text = self.process(val.self_group(), use_schema=False) 

1295 else: 

1296 

1297 def replace(obj): 

1298 if ( 

1299 isinstance(obj, elements.BindParameter) 

1300 and obj.type._isnull 

1301 ): 

1302 obj = obj._clone() 

1303 obj.type = column.type 

1304 return obj 

1305 elif ( 

1306 isinstance(obj, elements.ColumnClause) 

1307 and obj.table is on_duplicate.inserted_alias 

1308 ): 

1309 obj = literal_column( 

1310 "VALUES(" + self.preparer.quote(obj.name) + ")" 

1311 ) 

1312 return obj 

1313 else: 

1314 # element is not replaced 

1315 return None 

1316 

1317 val = visitors.replacement_traverse(val, {}, replace) 

1318 value_text = self.process(val.self_group(), use_schema=False) 

1319 

1320 name_text = self.preparer.quote(column.name) 

1321 clauses.append("%s = %s" % (name_text, value_text)) 

1322 

1323 non_matching = set(on_duplicate.update) - set(c.key for c in cols) 

1324 if non_matching: 

1325 util.warn( 

1326 "Additional column names not matching " 

1327 "any column keys in table '%s': %s" 

1328 % ( 

1329 self.statement.table.name, 

1330 (", ".join("'%s'" % c for c in non_matching)), 

1331 ) 

1332 ) 

1333 

1334 return "ON DUPLICATE KEY UPDATE " + ", ".join(clauses) 

1335 

1336 def visit_concat_op_binary(self, binary, operator, **kw): 

1337 return "concat(%s, %s)" % ( 

1338 self.process(binary.left, **kw), 

1339 self.process(binary.right, **kw), 

1340 ) 

1341 

1342 _match_valid_flag_combinations = frozenset( 

1343 ( 

1344 # (boolean_mode, natural_language, query_expansion) 

1345 (False, False, False), 

1346 (True, False, False), 

1347 (False, True, False), 

1348 (False, False, True), 

1349 (False, True, True), 

1350 ) 

1351 ) 

1352 

1353 _match_flag_expressions = ( 

1354 "IN BOOLEAN MODE", 

1355 "IN NATURAL LANGUAGE MODE", 

1356 "WITH QUERY EXPANSION", 

1357 ) 

1358 

1359 def visit_mysql_match(self, element, **kw): 

1360 return self.visit_match_op_binary(element, element.operator, **kw) 

1361 

1362 def visit_match_op_binary(self, binary, operator, **kw): 

1363 """ 

1364 Note that `mysql_boolean_mode` is enabled by default because of 

1365 backward compatibility 

1366 """ 

1367 

1368 modifiers = binary.modifiers 

1369 

1370 boolean_mode = modifiers.get("mysql_boolean_mode", True) 

1371 natural_language = modifiers.get("mysql_natural_language", False) 

1372 query_expansion = modifiers.get("mysql_query_expansion", False) 

1373 

1374 flag_combination = (boolean_mode, natural_language, query_expansion) 

1375 

1376 if flag_combination not in self._match_valid_flag_combinations: 

1377 flags = ( 

1378 "in_boolean_mode=%s" % boolean_mode, 

1379 "in_natural_language_mode=%s" % natural_language, 

1380 "with_query_expansion=%s" % query_expansion, 

1381 ) 

1382 

1383 flags = ", ".join(flags) 

1384 

1385 raise exc.CompileError("Invalid MySQL match flags: %s" % flags) 

1386 

1387 match_clause = binary.left 

1388 match_clause = self.process(match_clause, **kw) 

1389 against_clause = self.process(binary.right, **kw) 

1390 

1391 if any(flag_combination): 

1392 flag_expressions = compress( 

1393 self._match_flag_expressions, 

1394 flag_combination, 

1395 ) 

1396 

1397 against_clause = [against_clause] 

1398 against_clause.extend(flag_expressions) 

1399 

1400 against_clause = " ".join(against_clause) 

1401 

1402 return "MATCH (%s) AGAINST (%s)" % (match_clause, against_clause) 

1403 

1404 def get_from_hint_text(self, table, text): 

1405 return text 

1406 

1407 def visit_typeclause(self, typeclause, type_=None, **kw): 

1408 if type_ is None: 

1409 type_ = typeclause.type.dialect_impl(self.dialect) 

1410 if isinstance(type_, sqltypes.TypeDecorator): 

1411 return self.visit_typeclause(typeclause, type_.impl, **kw) 

1412 elif isinstance(type_, sqltypes.Integer): 

1413 if getattr(type_, "unsigned", False): 

1414 return "UNSIGNED INTEGER" 

1415 else: 

1416 return "SIGNED INTEGER" 

1417 elif isinstance(type_, sqltypes.TIMESTAMP): 

1418 return "DATETIME" 

1419 elif isinstance( 

1420 type_, 

1421 ( 

1422 sqltypes.DECIMAL, 

1423 sqltypes.DateTime, 

1424 sqltypes.Date, 

1425 sqltypes.Time, 

1426 ), 

1427 ): 

1428 return self.dialect.type_compiler.process(type_) 

1429 elif isinstance(type_, sqltypes.String) and not isinstance( 

1430 type_, (ENUM, SET) 

1431 ): 

1432 adapted = CHAR._adapt_string_for_cast(type_) 

1433 return self.dialect.type_compiler.process(adapted) 

1434 elif isinstance(type_, sqltypes._Binary): 

1435 return "BINARY" 

1436 elif isinstance(type_, sqltypes.JSON): 

1437 return "JSON" 

1438 elif isinstance(type_, sqltypes.NUMERIC): 

1439 return self.dialect.type_compiler.process(type_).replace( 

1440 "NUMERIC", "DECIMAL" 

1441 ) 

1442 elif ( 

1443 isinstance(type_, sqltypes.Float) 

1444 and self.dialect._support_float_cast 

1445 ): 

1446 return self.dialect.type_compiler.process(type_) 

1447 else: 

1448 return None 

1449 

1450 def visit_cast(self, cast, **kw): 

1451 type_ = self.process(cast.typeclause) 

1452 if type_ is None: 

1453 util.warn( 

1454 "Datatype %s does not support CAST on MySQL/MariaDb; " 

1455 "the CAST will be skipped." 

1456 % self.dialect.type_compiler.process(cast.typeclause.type) 

1457 ) 

1458 return self.process(cast.clause.self_group(), **kw) 

1459 

1460 return "CAST(%s AS %s)" % (self.process(cast.clause, **kw), type_) 

1461 

1462 def render_literal_value(self, value, type_): 

1463 value = super(MySQLCompiler, self).render_literal_value(value, type_) 

1464 if self.dialect._backslash_escapes: 

1465 value = value.replace("\\", "\\\\") 

1466 return value 

1467 

1468 # override native_boolean=False behavior here, as 

1469 # MySQL still supports native boolean 

1470 def visit_true(self, element, **kw): 

1471 return "true" 

1472 

1473 def visit_false(self, element, **kw): 

1474 return "false" 

1475 

1476 def get_select_precolumns(self, select, **kw): 

1477 """Add special MySQL keywords in place of DISTINCT. 

1478 

1479 .. deprecated 1.4:: this usage is deprecated. 

1480 :meth:`_expression.Select.prefix_with` should be used for special 

1481 keywords at the start of a SELECT. 

1482 

1483 """ 

1484 if isinstance(select._distinct, util.string_types): 

1485 util.warn_deprecated( 

1486 "Sending string values for 'distinct' is deprecated in the " 

1487 "MySQL dialect and will be removed in a future release. " 

1488 "Please use :meth:`.Select.prefix_with` for special keywords " 

1489 "at the start of a SELECT statement", 

1490 version="1.4", 

1491 ) 

1492 return select._distinct.upper() + " " 

1493 

1494 return super(MySQLCompiler, self).get_select_precolumns(select, **kw) 

1495 

1496 def visit_join(self, join, asfrom=False, from_linter=None, **kwargs): 

1497 if from_linter: 

1498 from_linter.edges.add((join.left, join.right)) 

1499 

1500 if join.full: 

1501 join_type = " FULL OUTER JOIN " 

1502 elif join.isouter: 

1503 join_type = " LEFT OUTER JOIN " 

1504 else: 

1505 join_type = " INNER JOIN " 

1506 

1507 return "".join( 

1508 ( 

1509 self.process( 

1510 join.left, asfrom=True, from_linter=from_linter, **kwargs 

1511 ), 

1512 join_type, 

1513 self.process( 

1514 join.right, asfrom=True, from_linter=from_linter, **kwargs 

1515 ), 

1516 " ON ", 

1517 self.process(join.onclause, from_linter=from_linter, **kwargs), 

1518 ) 

1519 ) 

1520 

1521 def for_update_clause(self, select, **kw): 

1522 if select._for_update_arg.read: 

1523 tmp = " LOCK IN SHARE MODE" 

1524 else: 

1525 tmp = " FOR UPDATE" 

1526 

1527 if select._for_update_arg.of and self.dialect.supports_for_update_of: 

1528 

1529 tables = util.OrderedSet() 

1530 for c in select._for_update_arg.of: 

1531 tables.update(sql_util.surface_selectables_only(c)) 

1532 

1533 tmp += " OF " + ", ".join( 

1534 self.process(table, ashint=True, use_schema=False, **kw) 

1535 for table in tables 

1536 ) 

1537 

1538 if select._for_update_arg.nowait: 

1539 tmp += " NOWAIT" 

1540 

1541 if select._for_update_arg.skip_locked: 

1542 tmp += " SKIP LOCKED" 

1543 

1544 return tmp 

1545 

1546 def limit_clause(self, select, **kw): 

1547 # MySQL supports: 

1548 # LIMIT <limit> 

1549 # LIMIT <offset>, <limit> 

1550 # and in server versions > 3.3: 

1551 # LIMIT <limit> OFFSET <offset> 

1552 # The latter is more readable for offsets but we're stuck with the 

1553 # former until we can refine dialects by server revision. 

1554 

1555 limit_clause, offset_clause = ( 

1556 select._limit_clause, 

1557 select._offset_clause, 

1558 ) 

1559 

1560 if limit_clause is None and offset_clause is None: 

1561 return "" 

1562 elif offset_clause is not None: 

1563 # As suggested by the MySQL docs, need to apply an 

1564 # artificial limit if one wasn't provided 

1565 # https://dev.mysql.com/doc/refman/5.0/en/select.html 

1566 if limit_clause is None: 

1567 # hardwire the upper limit. Currently 

1568 # needed by OurSQL with Python 3 

1569 # (https://bugs.launchpad.net/oursql/+bug/686232), 

1570 # but also is consistent with the usage of the upper 

1571 # bound as part of MySQL's "syntax" for OFFSET with 

1572 # no LIMIT 

1573 return " \n LIMIT %s, %s" % ( 

1574 self.process(offset_clause, **kw), 

1575 "18446744073709551615", 

1576 ) 

1577 else: 

1578 return " \n LIMIT %s, %s" % ( 

1579 self.process(offset_clause, **kw), 

1580 self.process(limit_clause, **kw), 

1581 ) 

1582 else: 

1583 # No offset provided, so just use the limit 

1584 return " \n LIMIT %s" % (self.process(limit_clause, **kw),) 

1585 

1586 def update_limit_clause(self, update_stmt): 

1587 limit = update_stmt.kwargs.get("%s_limit" % self.dialect.name, None) 

1588 if limit: 

1589 return "LIMIT %s" % limit 

1590 else: 

1591 return None 

1592 

1593 def update_tables_clause(self, update_stmt, from_table, extra_froms, **kw): 

1594 kw["asfrom"] = True 

1595 return ", ".join( 

1596 t._compiler_dispatch(self, **kw) 

1597 for t in [from_table] + list(extra_froms) 

1598 ) 

1599 

1600 def update_from_clause( 

1601 self, update_stmt, from_table, extra_froms, from_hints, **kw 

1602 ): 

1603 return None 

1604 

1605 def delete_table_clause(self, delete_stmt, from_table, extra_froms): 

1606 """If we have extra froms make sure we render any alias as hint.""" 

1607 ashint = False 

1608 if extra_froms: 

1609 ashint = True 

1610 return from_table._compiler_dispatch( 

1611 self, asfrom=True, iscrud=True, ashint=ashint 

1612 ) 

1613 

1614 def delete_extra_from_clause( 

1615 self, delete_stmt, from_table, extra_froms, from_hints, **kw 

1616 ): 

1617 """Render the DELETE .. USING clause specific to MySQL.""" 

1618 kw["asfrom"] = True 

1619 return "USING " + ", ".join( 

1620 t._compiler_dispatch(self, fromhints=from_hints, **kw) 

1621 for t in [from_table] + extra_froms 

1622 ) 

1623 

1624 def visit_empty_set_expr(self, element_types): 

1625 return ( 

1626 "SELECT %(outer)s FROM (SELECT %(inner)s) " 

1627 "as _empty_set WHERE 1!=1" 

1628 % { 

1629 "inner": ", ".join( 

1630 "1 AS _in_%s" % idx 

1631 for idx, type_ in enumerate(element_types) 

1632 ), 

1633 "outer": ", ".join( 

1634 "_in_%s" % idx for idx, type_ in enumerate(element_types) 

1635 ), 

1636 } 

1637 ) 

1638 

1639 def visit_is_distinct_from_binary(self, binary, operator, **kw): 

1640 return "NOT (%s <=> %s)" % ( 

1641 self.process(binary.left), 

1642 self.process(binary.right), 

1643 ) 

1644 

1645 def visit_is_not_distinct_from_binary(self, binary, operator, **kw): 

1646 return "%s <=> %s" % ( 

1647 self.process(binary.left), 

1648 self.process(binary.right), 

1649 ) 

1650 

1651 def _mariadb_regexp_flags(self, flags, pattern, **kw): 

1652 return "CONCAT('(?', %s, ')', %s)" % ( 

1653 self.render_literal_value(flags, sqltypes.STRINGTYPE), 

1654 self.process(pattern, **kw), 

1655 ) 

1656 

1657 def _regexp_match(self, op_string, binary, operator, **kw): 

1658 flags = binary.modifiers["flags"] 

1659 if flags is None: 

1660 return self._generate_generic_binary(binary, op_string, **kw) 

1661 elif self.dialect.is_mariadb: 

1662 return "%s%s%s" % ( 

1663 self.process(binary.left, **kw), 

1664 op_string, 

1665 self._mariadb_regexp_flags(flags, binary.right), 

1666 ) 

1667 else: 

1668 text = "REGEXP_LIKE(%s, %s, %s)" % ( 

1669 self.process(binary.left, **kw), 

1670 self.process(binary.right, **kw), 

1671 self.render_literal_value(flags, sqltypes.STRINGTYPE), 

1672 ) 

1673 if op_string == " NOT REGEXP ": 

1674 return "NOT %s" % text 

1675 else: 

1676 return text 

1677 

1678 def visit_regexp_match_op_binary(self, binary, operator, **kw): 

1679 return self._regexp_match(" REGEXP ", binary, operator, **kw) 

1680 

1681 def visit_not_regexp_match_op_binary(self, binary, operator, **kw): 

1682 return self._regexp_match(" NOT REGEXP ", binary, operator, **kw) 

1683 

1684 def visit_regexp_replace_op_binary(self, binary, operator, **kw): 

1685 flags = binary.modifiers["flags"] 

1686 if flags is None: 

1687 return "REGEXP_REPLACE(%s, %s)" % ( 

1688 self.process(binary.left, **kw), 

1689 self.process(binary.right, **kw), 

1690 ) 

1691 elif self.dialect.is_mariadb: 

1692 return "REGEXP_REPLACE(%s, %s, %s)" % ( 

1693 self.process(binary.left, **kw), 

1694 self._mariadb_regexp_flags(flags, binary.right.clauses[0]), 

1695 self.process(binary.right.clauses[1], **kw), 

1696 ) 

1697 else: 

1698 return "REGEXP_REPLACE(%s, %s, %s)" % ( 

1699 self.process(binary.left, **kw), 

1700 self.process(binary.right, **kw), 

1701 self.render_literal_value(flags, sqltypes.STRINGTYPE), 

1702 ) 

1703 

1704 

1705class MySQLDDLCompiler(compiler.DDLCompiler): 

1706 def get_column_specification(self, column, **kw): 

1707 """Builds column DDL.""" 

1708 

1709 colspec = [ 

1710 self.preparer.format_column(column), 

1711 self.dialect.type_compiler.process( 

1712 column.type, type_expression=column 

1713 ), 

1714 ] 

1715 

1716 if column.computed is not None: 

1717 colspec.append(self.process(column.computed)) 

1718 

1719 is_timestamp = isinstance( 

1720 column.type._unwrapped_dialect_impl(self.dialect), 

1721 sqltypes.TIMESTAMP, 

1722 ) 

1723 

1724 if not column.nullable: 

1725 colspec.append("NOT NULL") 

1726 

1727 # see: https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql_timestamp_null # noqa 

1728 elif column.nullable and is_timestamp: 

1729 colspec.append("NULL") 

1730 

1731 comment = column.comment 

1732 if comment is not None: 

1733 literal = self.sql_compiler.render_literal_value( 

1734 comment, sqltypes.String() 

1735 ) 

1736 colspec.append("COMMENT " + literal) 

1737 

1738 if ( 

1739 column.table is not None 

1740 and column is column.table._autoincrement_column 

1741 and ( 

1742 column.server_default is None 

1743 or isinstance(column.server_default, sa_schema.Identity) 

1744 ) 

1745 and not ( 

1746 self.dialect.supports_sequences 

1747 and isinstance(column.default, sa_schema.Sequence) 

1748 and not column.default.optional 

1749 ) 

1750 ): 

1751 colspec.append("AUTO_INCREMENT") 

1752 else: 

1753 default = self.get_column_default_string(column) 

1754 if default is not None: 

1755 colspec.append("DEFAULT " + default) 

1756 return " ".join(colspec) 

1757 

1758 def post_create_table(self, table): 

1759 """Build table-level CREATE options like ENGINE and COLLATE.""" 

1760 

1761 table_opts = [] 

1762 

1763 opts = dict( 

1764 (k[len(self.dialect.name) + 1 :].upper(), v) 

1765 for k, v in table.kwargs.items() 

1766 if k.startswith("%s_" % self.dialect.name) 

1767 ) 

1768 

1769 if table.comment is not None: 

1770 opts["COMMENT"] = table.comment 

1771 

1772 partition_options = [ 

1773 "PARTITION_BY", 

1774 "PARTITIONS", 

1775 "SUBPARTITIONS", 

1776 "SUBPARTITION_BY", 

1777 ] 

1778 

1779 nonpart_options = set(opts).difference(partition_options) 

1780 part_options = set(opts).intersection(partition_options) 

1781 

1782 for opt in topological.sort( 

1783 [ 

1784 ("DEFAULT_CHARSET", "COLLATE"), 

1785 ("DEFAULT_CHARACTER_SET", "COLLATE"), 

1786 ("CHARSET", "COLLATE"), 

1787 ("CHARACTER_SET", "COLLATE"), 

1788 ], 

1789 nonpart_options, 

1790 ): 

1791 arg = opts[opt] 

1792 if opt in _reflection._options_of_type_string: 

1793 

1794 arg = self.sql_compiler.render_literal_value( 

1795 arg, sqltypes.String() 

1796 ) 

1797 

1798 if opt in ( 

1799 "DATA_DIRECTORY", 

1800 "INDEX_DIRECTORY", 

1801 "DEFAULT_CHARACTER_SET", 

1802 "CHARACTER_SET", 

1803 "DEFAULT_CHARSET", 

1804 "DEFAULT_COLLATE", 

1805 ): 

1806 opt = opt.replace("_", " ") 

1807 

1808 joiner = "=" 

1809 if opt in ( 

1810 "TABLESPACE", 

1811 "DEFAULT CHARACTER SET", 

1812 "CHARACTER SET", 

1813 "COLLATE", 

1814 ): 

1815 joiner = " " 

1816 

1817 table_opts.append(joiner.join((opt, arg))) 

1818 

1819 for opt in topological.sort( 

1820 [ 

1821 ("PARTITION_BY", "PARTITIONS"), 

1822 ("PARTITION_BY", "SUBPARTITION_BY"), 

1823 ("PARTITION_BY", "SUBPARTITIONS"), 

1824 ("PARTITIONS", "SUBPARTITIONS"), 

1825 ("PARTITIONS", "SUBPARTITION_BY"), 

1826 ("SUBPARTITION_BY", "SUBPARTITIONS"), 

1827 ], 

1828 part_options, 

1829 ): 

1830 arg = opts[opt] 

1831 if opt in _reflection._options_of_type_string: 

1832 arg = self.sql_compiler.render_literal_value( 

1833 arg, sqltypes.String() 

1834 ) 

1835 

1836 opt = opt.replace("_", " ") 

1837 joiner = " " 

1838 

1839 table_opts.append(joiner.join((opt, arg))) 

1840 

1841 return " ".join(table_opts) 

1842 

1843 def visit_create_index(self, create, **kw): 

1844 index = create.element 

1845 self._verify_index_table(index) 

1846 preparer = self.preparer 

1847 table = preparer.format_table(index.table) 

1848 

1849 columns = [ 

1850 self.sql_compiler.process( 

1851 elements.Grouping(expr) 

1852 if ( 

1853 isinstance(expr, elements.BinaryExpression) 

1854 or ( 

1855 isinstance(expr, elements.UnaryExpression) 

1856 and expr.modifier 

1857 not in (operators.desc_op, operators.asc_op) 

1858 ) 

1859 or isinstance(expr, functions.FunctionElement) 

1860 ) 

1861 else expr, 

1862 include_table=False, 

1863 literal_binds=True, 

1864 ) 

1865 for expr in index.expressions 

1866 ] 

1867 

1868 name = self._prepared_index_name(index) 

1869 

1870 text = "CREATE " 

1871 if index.unique: 

1872 text += "UNIQUE " 

1873 

1874 index_prefix = index.kwargs.get("%s_prefix" % self.dialect.name, None) 

1875 if index_prefix: 

1876 text += index_prefix + " " 

1877 

1878 text += "INDEX " 

1879 if create.if_not_exists: 

1880 text += "IF NOT EXISTS " 

1881 text += "%s ON %s " % (name, table) 

1882 

1883 length = index.dialect_options[self.dialect.name]["length"] 

1884 if length is not None: 

1885 

1886 if isinstance(length, dict): 

1887 # length value can be a (column_name --> integer value) 

1888 # mapping specifying the prefix length for each column of the 

1889 # index 

1890 columns = ", ".join( 

1891 "%s(%d)" % (expr, length[col.name]) 

1892 if col.name in length 

1893 else ( 

1894 "%s(%d)" % (expr, length[expr]) 

1895 if expr in length 

1896 else "%s" % expr 

1897 ) 

1898 for col, expr in zip(index.expressions, columns) 

1899 ) 

1900 else: 

1901 # or can be an integer value specifying the same 

1902 # prefix length for all columns of the index 

1903 columns = ", ".join( 

1904 "%s(%d)" % (col, length) for col in columns 

1905 ) 

1906 else: 

1907 columns = ", ".join(columns) 

1908 text += "(%s)" % columns 

1909 

1910 parser = index.dialect_options["mysql"]["with_parser"] 

1911 if parser is not None: 

1912 text += " WITH PARSER %s" % (parser,) 

1913 

1914 using = index.dialect_options["mysql"]["using"] 

1915 if using is not None: 

1916 text += " USING %s" % (preparer.quote(using)) 

1917 

1918 return text 

1919 

1920 def visit_primary_key_constraint(self, constraint): 

1921 text = super(MySQLDDLCompiler, self).visit_primary_key_constraint( 

1922 constraint 

1923 ) 

1924 using = constraint.dialect_options["mysql"]["using"] 

1925 if using: 

1926 text += " USING %s" % (self.preparer.quote(using)) 

1927 return text 

1928 

1929 def visit_drop_index(self, drop): 

1930 index = drop.element 

1931 text = "\nDROP INDEX " 

1932 if drop.if_exists: 

1933 text += "IF EXISTS " 

1934 

1935 return text + "%s ON %s" % ( 

1936 self._prepared_index_name(index, include_schema=False), 

1937 self.preparer.format_table(index.table), 

1938 ) 

1939 

1940 def visit_drop_constraint(self, drop): 

1941 constraint = drop.element 

1942 if isinstance(constraint, sa_schema.ForeignKeyConstraint): 

1943 qual = "FOREIGN KEY " 

1944 const = self.preparer.format_constraint(constraint) 

1945 elif isinstance(constraint, sa_schema.PrimaryKeyConstraint): 

1946 qual = "PRIMARY KEY " 

1947 const = "" 

1948 elif isinstance(constraint, sa_schema.UniqueConstraint): 

1949 qual = "INDEX " 

1950 const = self.preparer.format_constraint(constraint) 

1951 elif isinstance(constraint, sa_schema.CheckConstraint): 

1952 if self.dialect.is_mariadb: 

1953 qual = "CONSTRAINT " 

1954 else: 

1955 qual = "CHECK " 

1956 const = self.preparer.format_constraint(constraint) 

1957 else: 

1958 qual = "" 

1959 const = self.preparer.format_constraint(constraint) 

1960 return "ALTER TABLE %s DROP %s%s" % ( 

1961 self.preparer.format_table(constraint.table), 

1962 qual, 

1963 const, 

1964 ) 

1965 

1966 def define_constraint_match(self, constraint): 

1967 if constraint.match is not None: 

1968 raise exc.CompileError( 

1969 "MySQL ignores the 'MATCH' keyword while at the same time " 

1970 "causes ON UPDATE/ON DELETE clauses to be ignored." 

1971 ) 

1972 return "" 

1973 

1974 def visit_set_table_comment(self, create): 

1975 return "ALTER TABLE %s COMMENT %s" % ( 

1976 self.preparer.format_table(create.element), 

1977 self.sql_compiler.render_literal_value( 

1978 create.element.comment, sqltypes.String() 

1979 ), 

1980 ) 

1981 

1982 def visit_drop_table_comment(self, create): 

1983 return "ALTER TABLE %s COMMENT ''" % ( 

1984 self.preparer.format_table(create.element) 

1985 ) 

1986 

1987 def visit_set_column_comment(self, create): 

1988 return "ALTER TABLE %s CHANGE %s %s" % ( 

1989 self.preparer.format_table(create.element.table), 

1990 self.preparer.format_column(create.element), 

1991 self.get_column_specification(create.element), 

1992 ) 

1993 

1994 

1995class MySQLTypeCompiler(compiler.GenericTypeCompiler): 

1996 def _extend_numeric(self, type_, spec): 

1997 "Extend a numeric-type declaration with MySQL specific extensions." 

1998 

1999 if not self._mysql_type(type_): 

2000 return spec 

2001 

2002 if type_.unsigned: 

2003 spec += " UNSIGNED" 

2004 if type_.zerofill: 

2005 spec += " ZEROFILL" 

2006 return spec 

2007 

2008 def _extend_string(self, type_, defaults, spec): 

2009 """Extend a string-type declaration with standard SQL CHARACTER SET / 

2010 COLLATE annotations and MySQL specific extensions. 

2011 

2012 """ 

2013 

2014 def attr(name): 

2015 return getattr(type_, name, defaults.get(name)) 

2016 

2017 if attr("charset"): 

2018 charset = "CHARACTER SET %s" % attr("charset") 

2019 elif attr("ascii"): 

2020 charset = "ASCII" 

2021 elif attr("unicode"): 

2022 charset = "UNICODE" 

2023 else: 

2024 charset = None 

2025 

2026 if attr("collation"): 

2027 collation = "COLLATE %s" % type_.collation 

2028 elif attr("binary"): 

2029 collation = "BINARY" 

2030 else: 

2031 collation = None 

2032 

2033 if attr("national"): 

2034 # NATIONAL (aka NCHAR/NVARCHAR) trumps charsets. 

2035 return " ".join( 

2036 [c for c in ("NATIONAL", spec, collation) if c is not None] 

2037 ) 

2038 return " ".join( 

2039 [c for c in (spec, charset, collation) if c is not None] 

2040 ) 

2041 

2042 def _mysql_type(self, type_): 

2043 return isinstance(type_, (_StringType, _NumericType)) 

2044 

2045 def visit_NUMERIC(self, type_, **kw): 

2046 if type_.precision is None: 

2047 return self._extend_numeric(type_, "NUMERIC") 

2048 elif type_.scale is None: 

2049 return self._extend_numeric( 

2050 type_, 

2051 "NUMERIC(%(precision)s)" % {"precision": type_.precision}, 

2052 ) 

2053 else: 

2054 return self._extend_numeric( 

2055 type_, 

2056 "NUMERIC(%(precision)s, %(scale)s)" 

2057 % {"precision": type_.precision, "scale": type_.scale}, 

2058 ) 

2059 

2060 def visit_DECIMAL(self, type_, **kw): 

2061 if type_.precision is None: 

2062 return self._extend_numeric(type_, "DECIMAL") 

2063 elif type_.scale is None: 

2064 return self._extend_numeric( 

2065 type_, 

2066 "DECIMAL(%(precision)s)" % {"precision": type_.precision}, 

2067 ) 

2068 else: 

2069 return self._extend_numeric( 

2070 type_, 

2071 "DECIMAL(%(precision)s, %(scale)s)" 

2072 % {"precision": type_.precision, "scale": type_.scale}, 

2073 ) 

2074 

2075 def visit_DOUBLE(self, type_, **kw): 

2076 if type_.precision is not None and type_.scale is not None: 

2077 return self._extend_numeric( 

2078 type_, 

2079 "DOUBLE(%(precision)s, %(scale)s)" 

2080 % {"precision": type_.precision, "scale": type_.scale}, 

2081 ) 

2082 else: 

2083 return self._extend_numeric(type_, "DOUBLE") 

2084 

2085 def visit_REAL(self, type_, **kw): 

2086 if type_.precision is not None and type_.scale is not None: 

2087 return self._extend_numeric( 

2088 type_, 

2089 "REAL(%(precision)s, %(scale)s)" 

2090 % {"precision": type_.precision, "scale": type_.scale}, 

2091 ) 

2092 else: 

2093 return self._extend_numeric(type_, "REAL") 

2094 

2095 def visit_FLOAT(self, type_, **kw): 

2096 if ( 

2097 self._mysql_type(type_) 

2098 and type_.scale is not None 

2099 and type_.precision is not None 

2100 ): 

2101 return self._extend_numeric( 

2102 type_, "FLOAT(%s, %s)" % (type_.precision, type_.scale) 

2103 ) 

2104 elif type_.precision is not None: 

2105 return self._extend_numeric( 

2106 type_, "FLOAT(%s)" % (type_.precision,) 

2107 ) 

2108 else: 

2109 return self._extend_numeric(type_, "FLOAT") 

2110 

2111 def visit_INTEGER(self, type_, **kw): 

2112 if self._mysql_type(type_) and type_.display_width is not None: 

2113 return self._extend_numeric( 

2114 type_, 

2115 "INTEGER(%(display_width)s)" 

2116 % {"display_width": type_.display_width}, 

2117 ) 

2118 else: 

2119 return self._extend_numeric(type_, "INTEGER") 

2120 

2121 def visit_BIGINT(self, type_, **kw): 

2122 if self._mysql_type(type_) and type_.display_width is not None: 

2123 return self._extend_numeric( 

2124 type_, 

2125 "BIGINT(%(display_width)s)" 

2126 % {"display_width": type_.display_width}, 

2127 ) 

2128 else: 

2129 return self._extend_numeric(type_, "BIGINT") 

2130 

2131 def visit_MEDIUMINT(self, type_, **kw): 

2132 if self._mysql_type(type_) and type_.display_width is not None: 

2133 return self._extend_numeric( 

2134 type_, 

2135 "MEDIUMINT(%(display_width)s)" 

2136 % {"display_width": type_.display_width}, 

2137 ) 

2138 else: 

2139 return self._extend_numeric(type_, "MEDIUMINT") 

2140 

2141 def visit_TINYINT(self, type_, **kw): 

2142 if self._mysql_type(type_) and type_.display_width is not None: 

2143 return self._extend_numeric( 

2144 type_, "TINYINT(%s)" % type_.display_width 

2145 ) 

2146 else: 

2147 return self._extend_numeric(type_, "TINYINT") 

2148 

2149 def visit_SMALLINT(self, type_, **kw): 

2150 if self._mysql_type(type_) and type_.display_width is not None: 

2151 return self._extend_numeric( 

2152 type_, 

2153 "SMALLINT(%(display_width)s)" 

2154 % {"display_width": type_.display_width}, 

2155 ) 

2156 else: 

2157 return self._extend_numeric(type_, "SMALLINT") 

2158 

2159 def visit_BIT(self, type_, **kw): 

2160 if type_.length is not None: 

2161 return "BIT(%s)" % type_.length 

2162 else: 

2163 return "BIT" 

2164 

2165 def visit_DATETIME(self, type_, **kw): 

2166 if getattr(type_, "fsp", None): 

2167 return "DATETIME(%d)" % type_.fsp 

2168 else: 

2169 return "DATETIME" 

2170 

2171 def visit_DATE(self, type_, **kw): 

2172 return "DATE" 

2173 

2174 def visit_TIME(self, type_, **kw): 

2175 if getattr(type_, "fsp", None): 

2176 return "TIME(%d)" % type_.fsp 

2177 else: 

2178 return "TIME" 

2179 

2180 def visit_TIMESTAMP(self, type_, **kw): 

2181 if getattr(type_, "fsp", None): 

2182 return "TIMESTAMP(%d)" % type_.fsp 

2183 else: 

2184 return "TIMESTAMP" 

2185 

2186 def visit_YEAR(self, type_, **kw): 

2187 if type_.display_width is None: 

2188 return "YEAR" 

2189 else: 

2190 return "YEAR(%s)" % type_.display_width 

2191 

2192 def visit_TEXT(self, type_, **kw): 

2193 if type_.length: 

2194 return self._extend_string(type_, {}, "TEXT(%d)" % type_.length) 

2195 else: 

2196 return self._extend_string(type_, {}, "TEXT") 

2197 

2198 def visit_TINYTEXT(self, type_, **kw): 

2199 return self._extend_string(type_, {}, "TINYTEXT") 

2200 

2201 def visit_MEDIUMTEXT(self, type_, **kw): 

2202 return self._extend_string(type_, {}, "MEDIUMTEXT") 

2203 

2204 def visit_LONGTEXT(self, type_, **kw): 

2205 return self._extend_string(type_, {}, "LONGTEXT") 

2206 

2207 def visit_VARCHAR(self, type_, **kw): 

2208 if type_.length: 

2209 return self._extend_string(type_, {}, "VARCHAR(%d)" % type_.length) 

2210 else: 

2211 raise exc.CompileError( 

2212 "VARCHAR requires a length on dialect %s" % self.dialect.name 

2213 ) 

2214 

2215 def visit_CHAR(self, type_, **kw): 

2216 if type_.length: 

2217 return self._extend_string( 

2218 type_, {}, "CHAR(%(length)s)" % {"length": type_.length} 

2219 ) 

2220 else: 

2221 return self._extend_string(type_, {}, "CHAR") 

2222 

2223 def visit_NVARCHAR(self, type_, **kw): 

2224 # We'll actually generate the equiv. "NATIONAL VARCHAR" instead 

2225 # of "NVARCHAR". 

2226 if type_.length: 

2227 return self._extend_string( 

2228 type_, 

2229 {"national": True}, 

2230 "VARCHAR(%(length)s)" % {"length": type_.length}, 

2231 ) 

2232 else: 

2233 raise exc.CompileError( 

2234 "NVARCHAR requires a length on dialect %s" % self.dialect.name 

2235 ) 

2236 

2237 def visit_NCHAR(self, type_, **kw): 

2238 # We'll actually generate the equiv. 

2239 # "NATIONAL CHAR" instead of "NCHAR". 

2240 if type_.length: 

2241 return self._extend_string( 

2242 type_, 

2243 {"national": True}, 

2244 "CHAR(%(length)s)" % {"length": type_.length}, 

2245 ) 

2246 else: 

2247 return self._extend_string(type_, {"national": True}, "CHAR") 

2248 

2249 def visit_VARBINARY(self, type_, **kw): 

2250 return "VARBINARY(%d)" % type_.length 

2251 

2252 def visit_JSON(self, type_, **kw): 

2253 return "JSON" 

2254 

2255 def visit_large_binary(self, type_, **kw): 

2256 return self.visit_BLOB(type_) 

2257 

2258 def visit_enum(self, type_, **kw): 

2259 if not type_.native_enum: 

2260 return super(MySQLTypeCompiler, self).visit_enum(type_) 

2261 else: 

2262 return self._visit_enumerated_values("ENUM", type_, type_.enums) 

2263 

2264 def visit_BLOB(self, type_, **kw): 

2265 if type_.length: 

2266 return "BLOB(%d)" % type_.length 

2267 else: 

2268 return "BLOB" 

2269 

2270 def visit_TINYBLOB(self, type_, **kw): 

2271 return "TINYBLOB" 

2272 

2273 def visit_MEDIUMBLOB(self, type_, **kw): 

2274 return "MEDIUMBLOB" 

2275 

2276 def visit_LONGBLOB(self, type_, **kw): 

2277 return "LONGBLOB" 

2278 

2279 def _visit_enumerated_values(self, name, type_, enumerated_values): 

2280 quoted_enums = [] 

2281 for e in enumerated_values: 

2282 quoted_enums.append("'%s'" % e.replace("'", "''")) 

2283 return self._extend_string( 

2284 type_, {}, "%s(%s)" % (name, ",".join(quoted_enums)) 

2285 ) 

2286 

2287 def visit_ENUM(self, type_, **kw): 

2288 return self._visit_enumerated_values("ENUM", type_, type_.enums) 

2289 

2290 def visit_SET(self, type_, **kw): 

2291 return self._visit_enumerated_values("SET", type_, type_.values) 

2292 

2293 def visit_BOOLEAN(self, type_, **kw): 

2294 return "BOOL" 

2295 

2296 

2297class MySQLIdentifierPreparer(compiler.IdentifierPreparer): 

2298 reserved_words = RESERVED_WORDS_MYSQL 

2299 

2300 def __init__(self, dialect, server_ansiquotes=False, **kw): 

2301 if not server_ansiquotes: 

2302 quote = "`" 

2303 else: 

2304 quote = '"' 

2305 

2306 super(MySQLIdentifierPreparer, self).__init__( 

2307 dialect, initial_quote=quote, escape_quote=quote 

2308 ) 

2309 

2310 def _quote_free_identifiers(self, *ids): 

2311 """Unilaterally identifier-quote any number of strings.""" 

2312 

2313 return tuple([self.quote_identifier(i) for i in ids if i is not None]) 

2314 

2315 

2316class MariaDBIdentifierPreparer(MySQLIdentifierPreparer): 

2317 reserved_words = RESERVED_WORDS_MARIADB 

2318 

2319 

2320@log.class_logger 

2321class MySQLDialect(default.DefaultDialect): 

2322 """Details of the MySQL dialect. 

2323 Not used directly in application code. 

2324 """ 

2325 

2326 name = "mysql" 

2327 supports_statement_cache = True 

2328 

2329 supports_alter = True 

2330 

2331 # MySQL has no true "boolean" type; we 

2332 # allow for the "true" and "false" keywords, however 

2333 supports_native_boolean = False 

2334 

2335 # identifiers are 64, however aliases can be 255... 

2336 max_identifier_length = 255 

2337 max_index_name_length = 64 

2338 max_constraint_name_length = 64 

2339 

2340 supports_native_enum = True 

2341 

2342 supports_sequences = False # default for MySQL ... 

2343 # ... may be updated to True for MariaDB 10.3+ in initialize() 

2344 

2345 sequences_optional = False 

2346 

2347 supports_for_update_of = False # default for MySQL ... 

2348 # ... may be updated to True for MySQL 8+ in initialize() 

2349 

2350 # MySQL doesn't support "DEFAULT VALUES" but *does* support 

2351 # "VALUES (DEFAULT)" 

2352 supports_default_values = False 

2353 supports_default_metavalue = True 

2354 

2355 supports_sane_rowcount = True 

2356 supports_sane_multi_rowcount = False 

2357 supports_multivalues_insert = True 

2358 

2359 supports_comments = True 

2360 inline_comments = True 

2361 default_paramstyle = "format" 

2362 colspecs = colspecs 

2363 

2364 cte_follows_insert = True 

2365 

2366 statement_compiler = MySQLCompiler 

2367 ddl_compiler = MySQLDDLCompiler 

2368 type_compiler = MySQLTypeCompiler 

2369 ischema_names = ischema_names 

2370 preparer = MySQLIdentifierPreparer 

2371 

2372 is_mariadb = False 

2373 _mariadb_normalized_version_info = None 

2374 

2375 # default SQL compilation settings - 

2376 # these are modified upon initialize(), 

2377 # i.e. first connect 

2378 _backslash_escapes = True 

2379 _server_ansiquotes = False 

2380 

2381 construct_arguments = [ 

2382 (sa_schema.Table, {"*": None}), 

2383 (sql.Update, {"limit": None}), 

2384 (sa_schema.PrimaryKeyConstraint, {"using": None}), 

2385 ( 

2386 sa_schema.Index, 

2387 { 

2388 "using": None, 

2389 "length": None, 

2390 "prefix": None, 

2391 "with_parser": None, 

2392 }, 

2393 ), 

2394 ] 

2395 

2396 def __init__( 

2397 self, 

2398 isolation_level=None, 

2399 json_serializer=None, 

2400 json_deserializer=None, 

2401 is_mariadb=None, 

2402 **kwargs 

2403 ): 

2404 kwargs.pop("use_ansiquotes", None) # legacy 

2405 default.DefaultDialect.__init__(self, **kwargs) 

2406 self.isolation_level = isolation_level 

2407 self._json_serializer = json_serializer 

2408 self._json_deserializer = json_deserializer 

2409 self._set_mariadb(is_mariadb, None) 

2410 

2411 def on_connect(self): 

2412 if self.isolation_level is not None: 

2413 

2414 def connect(conn): 

2415 self.set_isolation_level(conn, self.isolation_level) 

2416 

2417 return connect 

2418 else: 

2419 return None 

2420 

2421 _isolation_lookup = set( 

2422 [ 

2423 "SERIALIZABLE", 

2424 "READ UNCOMMITTED", 

2425 "READ COMMITTED", 

2426 "REPEATABLE READ", 

2427 ] 

2428 ) 

2429 

2430 def set_isolation_level(self, connection, level): 

2431 level = level.replace("_", " ") 

2432 

2433 # adjust for ConnectionFairy being present 

2434 # allows attribute set e.g. "connection.autocommit = True" 

2435 # to work properly 

2436 if hasattr(connection, "dbapi_connection"): 

2437 connection = connection.dbapi_connection 

2438 

2439 self._set_isolation_level(connection, level) 

2440 

2441 def _set_isolation_level(self, connection, level): 

2442 if level not in self._isolation_lookup: 

2443 raise exc.ArgumentError( 

2444 "Invalid value '%s' for isolation_level. " 

2445 "Valid isolation levels for %s are %s" 

2446 % (level, self.name, ", ".join(self._isolation_lookup)) 

2447 ) 

2448 cursor = connection.cursor() 

2449 cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % level) 

2450 cursor.execute("COMMIT") 

2451 cursor.close() 

2452 

2453 def get_isolation_level(self, connection): 

2454 cursor = connection.cursor() 

2455 if self._is_mysql and self.server_version_info >= (5, 7, 20): 

2456 cursor.execute("SELECT @@transaction_isolation") 

2457 else: 

2458 cursor.execute("SELECT @@tx_isolation") 

2459 row = cursor.fetchone() 

2460 if row is None: 

2461 util.warn( 

2462 "Could not retrieve transaction isolation level for MySQL " 

2463 "connection." 

2464 ) 

2465 raise NotImplementedError() 

2466 val = row[0] 

2467 cursor.close() 

2468 if util.py3k and isinstance(val, bytes): 

2469 val = val.decode() 

2470 return val.upper().replace("-", " ") 

2471 

2472 @classmethod 

2473 def _is_mariadb_from_url(cls, url): 

2474 dbapi = cls.dbapi() 

2475 dialect = cls(dbapi=dbapi) 

2476 

2477 cargs, cparams = dialect.create_connect_args(url) 

2478 conn = dialect.connect(*cargs, **cparams) 

2479 try: 

2480 cursor = conn.cursor() 

2481 cursor.execute("SELECT VERSION() LIKE '%MariaDB%'") 

2482 val = cursor.fetchone()[0] 

2483 except: 

2484 raise 

2485 else: 

2486 return bool(val) 

2487 finally: 

2488 conn.close() 

2489 

2490 def _get_server_version_info(self, connection): 

2491 # get database server version info explicitly over the wire 

2492 # to avoid proxy servers like MaxScale getting in the 

2493 # way with their own values, see #4205 

2494 dbapi_con = connection.connection 

2495 cursor = dbapi_con.cursor() 

2496 cursor.execute("SELECT VERSION()") 

2497 val = cursor.fetchone()[0] 

2498 cursor.close() 

2499 if util.py3k and isinstance(val, bytes): 

2500 val = val.decode() 

2501 

2502 return self._parse_server_version(val) 

2503 

2504 def _parse_server_version(self, val): 

2505 version = [] 

2506 is_mariadb = False 

2507 

2508 r = re.compile(r"[.\-+]") 

2509 tokens = r.split(val) 

2510 for token in tokens: 

2511 parsed_token = re.match( 

2512 r"^(?:(\d+)(?:a|b|c)?|(MariaDB\w*))$", token 

2513 ) 

2514 if not parsed_token: 

2515 continue 

2516 elif parsed_token.group(2): 

2517 self._mariadb_normalized_version_info = tuple(version[-3:]) 

2518 is_mariadb = True 

2519 else: 

2520 digit = int(parsed_token.group(1)) 

2521 version.append(digit) 

2522 

2523 server_version_info = tuple(version) 

2524 

2525 self._set_mariadb(server_version_info and is_mariadb, val) 

2526 

2527 if not is_mariadb: 

2528 self._mariadb_normalized_version_info = server_version_info 

2529 

2530 if server_version_info < (5, 0, 2): 

2531 raise NotImplementedError( 

2532 "the MySQL/MariaDB dialect supports server " 

2533 "version info 5.0.2 and above." 

2534 ) 

2535 

2536 # setting it here to help w the test suite 

2537 self.server_version_info = server_version_info 

2538 return server_version_info 

2539 

2540 def _set_mariadb(self, is_mariadb, server_version_info): 

2541 if is_mariadb is None: 

2542 return 

2543 

2544 if not is_mariadb and self.is_mariadb: 

2545 raise exc.InvalidRequestError( 

2546 "MySQL version %s is not a MariaDB variant." 

2547 % (server_version_info,) 

2548 ) 

2549 if is_mariadb: 

2550 self.preparer = MariaDBIdentifierPreparer 

2551 # this would have been set by the default dialect already, 

2552 # so set it again 

2553 self.identifier_preparer = self.preparer(self) 

2554 self.is_mariadb = is_mariadb 

2555 

2556 def do_begin_twophase(self, connection, xid): 

2557 connection.execute(sql.text("XA BEGIN :xid"), dict(xid=xid)) 

2558 

2559 def do_prepare_twophase(self, connection, xid): 

2560 connection.execute(sql.text("XA END :xid"), dict(xid=xid)) 

2561 connection.execute(sql.text("XA PREPARE :xid"), dict(xid=xid)) 

2562 

2563 def do_rollback_twophase( 

2564 self, connection, xid, is_prepared=True, recover=False 

2565 ): 

2566 if not is_prepared: 

2567 connection.execute(sql.text("XA END :xid"), dict(xid=xid)) 

2568 connection.execute(sql.text("XA ROLLBACK :xid"), dict(xid=xid)) 

2569 

2570 def do_commit_twophase( 

2571 self, connection, xid, is_prepared=True, recover=False 

2572 ): 

2573 if not is_prepared: 

2574 self.do_prepare_twophase(connection, xid) 

2575 connection.execute(sql.text("XA COMMIT :xid"), dict(xid=xid)) 

2576 

2577 def do_recover_twophase(self, connection): 

2578 resultset = connection.exec_driver_sql("XA RECOVER") 

2579 return [row["data"][0 : row["gtrid_length"]] for row in resultset] 

2580 

2581 def is_disconnect(self, e, connection, cursor): 

2582 if isinstance( 

2583 e, 

2584 ( 

2585 self.dbapi.OperationalError, 

2586 self.dbapi.ProgrammingError, 

2587 self.dbapi.InterfaceError, 

2588 ), 

2589 ) and self._extract_error_code(e) in ( 

2590 1927, 

2591 2006, 

2592 2013, 

2593 2014, 

2594 2045, 

2595 2055, 

2596 4031, 

2597 ): 

2598 return True 

2599 elif isinstance( 

2600 e, (self.dbapi.InterfaceError, self.dbapi.InternalError) 

2601 ): 

2602 # if underlying connection is closed, 

2603 # this is the error you get 

2604 return "(0, '')" in str(e) 

2605 else: 

2606 return False 

2607 

2608 def _compat_fetchall(self, rp, charset=None): 

2609 """Proxy result rows to smooth over MySQL-Python driver 

2610 inconsistencies.""" 

2611 

2612 return [_DecodingRow(row, charset) for row in rp.fetchall()] 

2613 

2614 def _compat_fetchone(self, rp, charset=None): 

2615 """Proxy a result row to smooth over MySQL-Python driver 

2616 inconsistencies.""" 

2617 

2618 row = rp.fetchone() 

2619 if row: 

2620 return _DecodingRow(row, charset) 

2621 else: 

2622 return None 

2623 

2624 def _compat_first(self, rp, charset=None): 

2625 """Proxy a result row to smooth over MySQL-Python driver 

2626 inconsistencies.""" 

2627 

2628 row = rp.first() 

2629 if row: 

2630 return _DecodingRow(row, charset) 

2631 else: 

2632 return None 

2633 

2634 def _extract_error_code(self, exception): 

2635 raise NotImplementedError() 

2636 

2637 def _get_default_schema_name(self, connection): 

2638 return connection.exec_driver_sql("SELECT DATABASE()").scalar() 

2639 

2640 def has_table(self, connection, table_name, schema=None): 

2641 self._ensure_has_table_connection(connection) 

2642 

2643 if schema is None: 

2644 schema = self.default_schema_name 

2645 

2646 rs = connection.execute( 

2647 text( 

2648 "SELECT COUNT(*) FROM information_schema.tables WHERE " 

2649 "table_schema = :table_schema AND " 

2650 "table_name = :table_name" 

2651 ).bindparams( 

2652 sql.bindparam("table_schema", type_=Unicode), 

2653 sql.bindparam("table_name", type_=Unicode), 

2654 ), 

2655 { 

2656 "table_schema": util.text_type(schema), 

2657 "table_name": util.text_type(table_name), 

2658 }, 

2659 ) 

2660 return bool(rs.scalar()) 

2661 

2662 def has_sequence(self, connection, sequence_name, schema=None): 

2663 if not self.supports_sequences: 

2664 self._sequences_not_supported() 

2665 if not schema: 

2666 schema = self.default_schema_name 

2667 # MariaDB implements sequences as a special type of table 

2668 # 

2669 cursor = connection.execute( 

2670 sql.text( 

2671 "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " 

2672 "WHERE TABLE_TYPE='SEQUENCE' and TABLE_NAME=:name AND " 

2673 "TABLE_SCHEMA=:schema_name" 

2674 ), 

2675 dict( 

2676 name=util.text_type(sequence_name), 

2677 schema_name=util.text_type(schema), 

2678 ), 

2679 ) 

2680 return cursor.first() is not None 

2681 

2682 def _sequences_not_supported(self): 

2683 raise NotImplementedError( 

2684 "Sequences are supported only by the " 

2685 "MariaDB series 10.3 or greater" 

2686 ) 

2687 

2688 @reflection.cache 

2689 def get_sequence_names(self, connection, schema=None, **kw): 

2690 if not self.supports_sequences: 

2691 self._sequences_not_supported() 

2692 if not schema: 

2693 schema = self.default_schema_name 

2694 # MariaDB implements sequences as a special type of table 

2695 cursor = connection.execute( 

2696 sql.text( 

2697 "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " 

2698 "WHERE TABLE_TYPE='SEQUENCE' and TABLE_SCHEMA=:schema_name" 

2699 ), 

2700 dict(schema_name=schema), 

2701 ) 

2702 return [ 

2703 row[0] 

2704 for row in self._compat_fetchall( 

2705 cursor, charset=self._connection_charset 

2706 ) 

2707 ] 

2708 

2709 def initialize(self, connection): 

2710 # this is driver-based, does not need server version info 

2711 # and is fairly critical for even basic SQL operations 

2712 self._connection_charset = self._detect_charset(connection) 

2713 

2714 # call super().initialize() because we need to have 

2715 # server_version_info set up. in 1.4 under python 2 only this does the 

2716 # "check unicode returns" thing, which is the one area that some 

2717 # SQL gets compiled within initialize() currently 

2718 default.DefaultDialect.initialize(self, connection) 

2719 

2720 self._detect_sql_mode(connection) 

2721 self._detect_ansiquotes(connection) # depends on sql mode 

2722 self._detect_casing(connection) 

2723 if self._server_ansiquotes: 

2724 # if ansiquotes == True, build a new IdentifierPreparer 

2725 # with the new setting 

2726 self.identifier_preparer = self.preparer( 

2727 self, server_ansiquotes=self._server_ansiquotes 

2728 ) 

2729 

2730 self.supports_sequences = ( 

2731 self.is_mariadb and self.server_version_info >= (10, 3) 

2732 ) 

2733 

2734 self.supports_for_update_of = ( 

2735 self._is_mysql and self.server_version_info >= (8,) 

2736 ) 

2737 

2738 self._needs_correct_for_88718_96365 = ( 

2739 not self.is_mariadb and self.server_version_info >= (8,) 

2740 ) 

2741 

2742 self._warn_for_known_db_issues() 

2743 

2744 def _warn_for_known_db_issues(self): 

2745 if self.is_mariadb: 

2746 mdb_version = self._mariadb_normalized_version_info 

2747 if mdb_version > (10, 2) and mdb_version < (10, 2, 9): 

2748 util.warn( 

2749 "MariaDB %r before 10.2.9 has known issues regarding " 

2750 "CHECK constraints, which impact handling of NULL values " 

2751 "with SQLAlchemy's boolean datatype (MDEV-13596). An " 

2752 "additional issue prevents proper migrations of columns " 

2753 "with CHECK constraints (MDEV-11114). Please upgrade to " 

2754 "MariaDB 10.2.9 or greater, or use the MariaDB 10.1 " 

2755 "series, to avoid these issues." % (mdb_version,) 

2756 ) 

2757 

2758 @property 

2759 def _support_float_cast(self): 

2760 if not self.server_version_info: 

2761 return False 

2762 elif self.is_mariadb: 

2763 # ref https://mariadb.com/kb/en/mariadb-1045-release-notes/ 

2764 return self.server_version_info >= (10, 4, 5) 

2765 else: 

2766 # ref https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html#mysqld-8-0-17-feature # noqa 

2767 return self.server_version_info >= (8, 0, 17) 

2768 

2769 @property 

2770 def _is_mariadb(self): 

2771 return self.is_mariadb 

2772 

2773 @property 

2774 def _is_mysql(self): 

2775 return not self.is_mariadb 

2776 

2777 @property 

2778 def _is_mariadb_102(self): 

2779 return self.is_mariadb and self._mariadb_normalized_version_info > ( 

2780 10, 

2781 2, 

2782 ) 

2783 

2784 @reflection.cache 

2785 def get_schema_names(self, connection, **kw): 

2786 rp = connection.exec_driver_sql("SHOW schemas") 

2787 return [r[0] for r in rp] 

2788 

2789 @reflection.cache 

2790 def get_table_names(self, connection, schema=None, **kw): 

2791 """Return a Unicode SHOW TABLES from a given schema.""" 

2792 if schema is not None: 

2793 current_schema = schema 

2794 else: 

2795 current_schema = self.default_schema_name 

2796 

2797 charset = self._connection_charset 

2798 

2799 rp = connection.exec_driver_sql( 

2800 "SHOW FULL TABLES FROM %s" 

2801 % self.identifier_preparer.quote_identifier(current_schema) 

2802 ) 

2803 

2804 return [ 

2805 row[0] 

2806 for row in self._compat_fetchall(rp, charset=charset) 

2807 if row[1] == "BASE TABLE" 

2808 ] 

2809 

2810 @reflection.cache 

2811 def get_view_names(self, connection, schema=None, **kw): 

2812 if schema is None: 

2813 schema = self.default_schema_name 

2814 charset = self._connection_charset 

2815 rp = connection.exec_driver_sql( 

2816 "SHOW FULL TABLES FROM %s" 

2817 % self.identifier_preparer.quote_identifier(schema) 

2818 ) 

2819 return [ 

2820 row[0] 

2821 for row in self._compat_fetchall(rp, charset=charset) 

2822 if row[1] in ("VIEW", "SYSTEM VIEW") 

2823 ] 

2824 

2825 @reflection.cache 

2826 def get_table_options(self, connection, table_name, schema=None, **kw): 

2827 

2828 parsed_state = self._parsed_state_or_create( 

2829 connection, table_name, schema, **kw 

2830 ) 

2831 return parsed_state.table_options 

2832 

2833 @reflection.cache 

2834 def get_columns(self, connection, table_name, schema=None, **kw): 

2835 parsed_state = self._parsed_state_or_create( 

2836 connection, table_name, schema, **kw 

2837 ) 

2838 return parsed_state.columns 

2839 

2840 @reflection.cache 

2841 def get_pk_constraint(self, connection, table_name, schema=None, **kw): 

2842 parsed_state = self._parsed_state_or_create( 

2843 connection, table_name, schema, **kw 

2844 ) 

2845 for key in parsed_state.keys: 

2846 if key["type"] == "PRIMARY": 

2847 # There can be only one. 

2848 cols = [s[0] for s in key["columns"]] 

2849 return {"constrained_columns": cols, "name": None} 

2850 return {"constrained_columns": [], "name": None} 

2851 

2852 @reflection.cache 

2853 def get_foreign_keys(self, connection, table_name, schema=None, **kw): 

2854 

2855 parsed_state = self._parsed_state_or_create( 

2856 connection, table_name, schema, **kw 

2857 ) 

2858 default_schema = None 

2859 

2860 fkeys = [] 

2861 

2862 for spec in parsed_state.fk_constraints: 

2863 ref_name = spec["table"][-1] 

2864 ref_schema = len(spec["table"]) > 1 and spec["table"][-2] or schema 

2865 

2866 if not ref_schema: 

2867 if default_schema is None: 

2868 default_schema = connection.dialect.default_schema_name 

2869 if schema == default_schema: 

2870 ref_schema = schema 

2871 

2872 loc_names = spec["local"] 

2873 ref_names = spec["foreign"] 

2874 

2875 con_kw = {} 

2876 for opt in ("onupdate", "ondelete"): 

2877 if spec.get(opt, False) not in ("NO ACTION", None): 

2878 con_kw[opt] = spec[opt] 

2879 

2880 fkey_d = { 

2881 "name": spec["name"], 

2882 "constrained_columns": loc_names, 

2883 "referred_schema": ref_schema, 

2884 "referred_table": ref_name, 

2885 "referred_columns": ref_names, 

2886 "options": con_kw, 

2887 } 

2888 fkeys.append(fkey_d) 

2889 

2890 if self._needs_correct_for_88718_96365: 

2891 self._correct_for_mysql_bugs_88718_96365(fkeys, connection) 

2892 

2893 return fkeys 

2894 

2895 def _correct_for_mysql_bugs_88718_96365(self, fkeys, connection): 

2896 # Foreign key is always in lower case (MySQL 8.0) 

2897 # https://bugs.mysql.com/bug.php?id=88718 

2898 # issue #4344 for SQLAlchemy 

2899 

2900 # table name also for MySQL 8.0 

2901 # https://bugs.mysql.com/bug.php?id=96365 

2902 # issue #4751 for SQLAlchemy 

2903 

2904 # for lower_case_table_names=2, information_schema.columns 

2905 # preserves the original table/schema casing, but SHOW CREATE 

2906 # TABLE does not. this problem is not in lower_case_table_names=1, 

2907 # but use case-insensitive matching for these two modes in any case. 

2908 

2909 if self._casing in (1, 2): 

2910 

2911 def lower(s): 

2912 return s.lower() 

2913 

2914 else: 

2915 # if on case sensitive, there can be two tables referenced 

2916 # with the same name different casing, so we need to use 

2917 # case-sensitive matching. 

2918 def lower(s): 

2919 return s 

2920 

2921 default_schema_name = connection.dialect.default_schema_name 

2922 col_tuples = [ 

2923 ( 

2924 lower(rec["referred_schema"] or default_schema_name), 

2925 lower(rec["referred_table"]), 

2926 col_name, 

2927 ) 

2928 for rec in fkeys 

2929 for col_name in rec["referred_columns"] 

2930 ] 

2931 

2932 if col_tuples: 

2933 

2934 correct_for_wrong_fk_case = connection.execute( 

2935 sql.text( 

2936 """ 

2937 select table_schema, table_name, column_name 

2938 from information_schema.columns 

2939 where (table_schema, table_name, lower(column_name)) in 

2940 :table_data; 

2941 """ 

2942 ).bindparams(sql.bindparam("table_data", expanding=True)), 

2943 dict(table_data=col_tuples), 

2944 ) 

2945 

2946 # in casing=0, table name and schema name come back in their 

2947 # exact case. 

2948 # in casing=1, table name and schema name come back in lower 

2949 # case. 

2950 # in casing=2, table name and schema name come back from the 

2951 # information_schema.columns view in the case 

2952 # that was used in CREATE DATABASE and CREATE TABLE, but 

2953 # SHOW CREATE TABLE converts them to *lower case*, therefore 

2954 # not matching. So for this case, case-insensitive lookup 

2955 # is necessary 

2956 d = defaultdict(dict) 

2957 for schema, tname, cname in correct_for_wrong_fk_case: 

2958 d[(lower(schema), lower(tname))]["SCHEMANAME"] = schema 

2959 d[(lower(schema), lower(tname))]["TABLENAME"] = tname 

2960 d[(lower(schema), lower(tname))][cname.lower()] = cname 

2961 

2962 for fkey in fkeys: 

2963 rec = d[ 

2964 ( 

2965 lower(fkey["referred_schema"] or default_schema_name), 

2966 lower(fkey["referred_table"]), 

2967 ) 

2968 ] 

2969 

2970 fkey["referred_table"] = rec["TABLENAME"] 

2971 if fkey["referred_schema"] is not None: 

2972 fkey["referred_schema"] = rec["SCHEMANAME"] 

2973 

2974 fkey["referred_columns"] = [ 

2975 rec[col.lower()] for col in fkey["referred_columns"] 

2976 ] 

2977 

2978 @reflection.cache 

2979 def get_check_constraints(self, connection, table_name, schema=None, **kw): 

2980 parsed_state = self._parsed_state_or_create( 

2981 connection, table_name, schema, **kw 

2982 ) 

2983 

2984 return [ 

2985 {"name": spec["name"], "sqltext": spec["sqltext"]} 

2986 for spec in parsed_state.ck_constraints 

2987 ] 

2988 

2989 @reflection.cache 

2990 def get_table_comment(self, connection, table_name, schema=None, **kw): 

2991 parsed_state = self._parsed_state_or_create( 

2992 connection, table_name, schema, **kw 

2993 ) 

2994 return { 

2995 "text": parsed_state.table_options.get( 

2996 "%s_comment" % self.name, None 

2997 ) 

2998 } 

2999 

3000 @reflection.cache 

3001 def get_indexes(self, connection, table_name, schema=None, **kw): 

3002 

3003 parsed_state = self._parsed_state_or_create( 

3004 connection, table_name, schema, **kw 

3005 ) 

3006 

3007 indexes = [] 

3008 

3009 for spec in parsed_state.keys: 

3010 dialect_options = {} 

3011 unique = False 

3012 flavor = spec["type"] 

3013 if flavor == "PRIMARY": 

3014 continue 

3015 if flavor == "UNIQUE": 

3016 unique = True 

3017 elif flavor in ("FULLTEXT", "SPATIAL"): 

3018 dialect_options["%s_prefix" % self.name] = flavor 

3019 elif flavor is None: 

3020 pass 

3021 else: 

3022 self.logger.info( 

3023 "Converting unknown KEY type %s to a plain KEY", flavor 

3024 ) 

3025 pass 

3026 

3027 if spec["parser"]: 

3028 dialect_options["%s_with_parser" % (self.name)] = spec[ 

3029 "parser" 

3030 ] 

3031 

3032 index_d = {} 

3033 

3034 index_d["name"] = spec["name"] 

3035 index_d["column_names"] = [s[0] for s in spec["columns"]] 

3036 mysql_length = { 

3037 s[0]: s[1] for s in spec["columns"] if s[1] is not None 

3038 } 

3039 if mysql_length: 

3040 dialect_options["%s_length" % self.name] = mysql_length 

3041 

3042 index_d["unique"] = unique 

3043 if flavor: 

3044 index_d["type"] = flavor 

3045 

3046 if dialect_options: 

3047 index_d["dialect_options"] = dialect_options 

3048 

3049 indexes.append(index_d) 

3050 return indexes 

3051 

3052 @reflection.cache 

3053 def get_unique_constraints( 

3054 self, connection, table_name, schema=None, **kw 

3055 ): 

3056 parsed_state = self._parsed_state_or_create( 

3057 connection, table_name, schema, **kw 

3058 ) 

3059 

3060 return [ 

3061 { 

3062 "name": key["name"], 

3063 "column_names": [col[0] for col in key["columns"]], 

3064 "duplicates_index": key["name"], 

3065 } 

3066 for key in parsed_state.keys 

3067 if key["type"] == "UNIQUE" 

3068 ] 

3069 

3070 @reflection.cache 

3071 def get_view_definition(self, connection, view_name, schema=None, **kw): 

3072 

3073 charset = self._connection_charset 

3074 full_name = ".".join( 

3075 self.identifier_preparer._quote_free_identifiers(schema, view_name) 

3076 ) 

3077 sql = self._show_create_table( 

3078 connection, None, charset, full_name=full_name 

3079 ) 

3080 return sql 

3081 

3082 def _parsed_state_or_create( 

3083 self, connection, table_name, schema=None, **kw 

3084 ): 

3085 return self._setup_parser( 

3086 connection, 

3087 table_name, 

3088 schema, 

3089 info_cache=kw.get("info_cache", None), 

3090 ) 

3091 

3092 @util.memoized_property 

3093 def _tabledef_parser(self): 

3094 """return the MySQLTableDefinitionParser, generate if needed. 

3095 

3096 The deferred creation ensures that the dialect has 

3097 retrieved server version information first. 

3098 

3099 """ 

3100 preparer = self.identifier_preparer 

3101 return _reflection.MySQLTableDefinitionParser(self, preparer) 

3102 

3103 @reflection.cache 

3104 def _setup_parser(self, connection, table_name, schema=None, **kw): 

3105 charset = self._connection_charset 

3106 parser = self._tabledef_parser 

3107 full_name = ".".join( 

3108 self.identifier_preparer._quote_free_identifiers( 

3109 schema, table_name 

3110 ) 

3111 ) 

3112 sql = self._show_create_table( 

3113 connection, None, charset, full_name=full_name 

3114 ) 

3115 if parser._check_view(sql): 

3116 # Adapt views to something table-like. 

3117 columns = self._describe_table( 

3118 connection, None, charset, full_name=full_name 

3119 ) 

3120 sql = parser._describe_to_create(table_name, columns) 

3121 return parser.parse(sql, charset) 

3122 

3123 def _fetch_setting(self, connection, setting_name): 

3124 charset = self._connection_charset 

3125 

3126 if self.server_version_info and self.server_version_info < (5, 6): 

3127 sql = "SHOW VARIABLES LIKE '%s'" % setting_name 

3128 fetch_col = 1 

3129 else: 

3130 sql = "SELECT @@%s" % setting_name 

3131 fetch_col = 0 

3132 

3133 show_var = connection.exec_driver_sql(sql) 

3134 row = self._compat_first(show_var, charset=charset) 

3135 if not row: 

3136 return None 

3137 else: 

3138 return row[fetch_col] 

3139 

3140 def _detect_charset(self, connection): 

3141 raise NotImplementedError() 

3142 

3143 def _detect_casing(self, connection): 

3144 """Sniff out identifier case sensitivity. 

3145 

3146 Cached per-connection. This value can not change without a server 

3147 restart. 

3148 

3149 """ 

3150 # https://dev.mysql.com/doc/refman/en/identifier-case-sensitivity.html 

3151 

3152 setting = self._fetch_setting(connection, "lower_case_table_names") 

3153 if setting is None: 

3154 cs = 0 

3155 else: 

3156 # 4.0.15 returns OFF or ON according to [ticket:489] 

3157 # 3.23 doesn't, 4.0.27 doesn't.. 

3158 if setting == "OFF": 

3159 cs = 0 

3160 elif setting == "ON": 

3161 cs = 1 

3162 else: 

3163 cs = int(setting) 

3164 self._casing = cs 

3165 return cs 

3166 

3167 def _detect_collations(self, connection): 

3168 """Pull the active COLLATIONS list from the server. 

3169 

3170 Cached per-connection. 

3171 """ 

3172 

3173 collations = {} 

3174 charset = self._connection_charset 

3175 rs = connection.exec_driver_sql("SHOW COLLATION") 

3176 for row in self._compat_fetchall(rs, charset): 

3177 collations[row[0]] = row[1] 

3178 return collations 

3179 

3180 def _detect_sql_mode(self, connection): 

3181 setting = self._fetch_setting(connection, "sql_mode") 

3182 

3183 if setting is None: 

3184 util.warn( 

3185 "Could not retrieve SQL_MODE; please ensure the " 

3186 "MySQL user has permissions to SHOW VARIABLES" 

3187 ) 

3188 self._sql_mode = "" 

3189 else: 

3190 self._sql_mode = setting or "" 

3191 

3192 def _detect_ansiquotes(self, connection): 

3193 """Detect and adjust for the ANSI_QUOTES sql mode.""" 

3194 

3195 mode = self._sql_mode 

3196 if not mode: 

3197 mode = "" 

3198 elif mode.isdigit(): 

3199 mode_no = int(mode) 

3200 mode = (mode_no | 4 == mode_no) and "ANSI_QUOTES" or "" 

3201 

3202 self._server_ansiquotes = "ANSI_QUOTES" in mode 

3203 

3204 # as of MySQL 5.0.1 

3205 self._backslash_escapes = "NO_BACKSLASH_ESCAPES" not in mode 

3206 

3207 def _show_create_table( 

3208 self, connection, table, charset=None, full_name=None 

3209 ): 

3210 """Run SHOW CREATE TABLE for a ``Table``.""" 

3211 

3212 if full_name is None: 

3213 full_name = self.identifier_preparer.format_table(table) 

3214 st = "SHOW CREATE TABLE %s" % full_name 

3215 

3216 rp = None 

3217 try: 

3218 rp = connection.execution_options( 

3219 skip_user_error_events=True 

3220 ).exec_driver_sql(st) 

3221 except exc.DBAPIError as e: 

3222 if self._extract_error_code(e.orig) == 1146: 

3223 util.raise_(exc.NoSuchTableError(full_name), replace_context=e) 

3224 else: 

3225 raise 

3226 row = self._compat_first(rp, charset=charset) 

3227 if not row: 

3228 raise exc.NoSuchTableError(full_name) 

3229 return row[1].strip() 

3230 

3231 def _describe_table(self, connection, table, charset=None, full_name=None): 

3232 """Run DESCRIBE for a ``Table`` and return processed rows.""" 

3233 

3234 if full_name is None: 

3235 full_name = self.identifier_preparer.format_table(table) 

3236 st = "DESCRIBE %s" % full_name 

3237 

3238 rp, rows = None, None 

3239 try: 

3240 try: 

3241 rp = connection.execution_options( 

3242 skip_user_error_events=True 

3243 ).exec_driver_sql(st) 

3244 except exc.DBAPIError as e: 

3245 code = self._extract_error_code(e.orig) 

3246 if code == 1146: 

3247 util.raise_( 

3248 exc.NoSuchTableError(full_name), replace_context=e 

3249 ) 

3250 elif code == 1356: 

3251 util.raise_( 

3252 exc.UnreflectableTableError( 

3253 "Table or view named %s could not be " 

3254 "reflected: %s" % (full_name, e) 

3255 ), 

3256 replace_context=e, 

3257 ) 

3258 else: 

3259 raise 

3260 rows = self._compat_fetchall(rp, charset=charset) 

3261 finally: 

3262 if rp: 

3263 rp.close() 

3264 return rows 

3265 

3266 

3267class _DecodingRow(object): 

3268 """Return unicode-decoded values based on type inspection. 

3269 

3270 Smooth over data type issues (esp. with alpha driver versions) and 

3271 normalize strings as Unicode regardless of user-configured driver 

3272 encoding settings. 

3273 

3274 """ 

3275 

3276 # Some MySQL-python versions can return some columns as 

3277 # sets.Set(['value']) (seriously) but thankfully that doesn't 

3278 # seem to come up in DDL queries. 

3279 

3280 _encoding_compat = { 

3281 "koi8r": "koi8_r", 

3282 "koi8u": "koi8_u", 

3283 "utf16": "utf-16-be", # MySQL's uft16 is always bigendian 

3284 "utf8mb4": "utf8", # real utf8 

3285 "utf8mb3": "utf8", # real utf8; saw this happen on CI but I cannot 

3286 # reproduce, possibly mariadb10.6 related 

3287 "eucjpms": "ujis", 

3288 } 

3289 

3290 def __init__(self, rowproxy, charset): 

3291 self.rowproxy = rowproxy 

3292 self.charset = self._encoding_compat.get(charset, charset) 

3293 

3294 def __getitem__(self, index): 

3295 item = self.rowproxy[index] 

3296 if isinstance(item, _array): 

3297 item = item.tostring() 

3298 

3299 if self.charset and isinstance(item, util.binary_type): 

3300 return item.decode(self.charset) 

3301 else: 

3302 return item 

3303 

3304 def __getattr__(self, attr): 

3305 item = getattr(self.rowproxy, attr) 

3306 if isinstance(item, _array): 

3307 item = item.tostring() 

3308 if self.charset and isinstance(item, util.binary_type): 

3309 return item.decode(self.charset) 

3310 else: 

3311 return item