SqlAlchemy 2.0 中文文档（四十九）（1）-阿里云开发者社区

原文：docs.sqlalchemy.org/en/20/contents.html

MySQL 和 MariaDB

原文：docs.sqlalchemy.org/en/20/dialects/mysql.html

支持 MySQL / MariaDB 数据库。

以下表总结了数据库发布版本的当前支持级别。

支持的 MySQL / MariaDB 版本

支持类型	版本
CI 中完全测试	5.6, 5.7, 8.0 / 10.8, 10.9
正常支持	5.6+ / 10+
尽力而为	5.0.2+ / 5.0.2+

DBAPI 支持

提供以下方言/DBAPI 选项。请参考各自的 DBAPI 部分以获取连接信息。

mysqlclient（MySQL-Python 的维护分支）
PyMySQL
MariaDB Connector/Python
MySQL Connector/Python
asyncmy
aiomysql
CyMySQL
PyODBC

支持的版本和功能

SQLAlchemy 支持从版本 5.0.2 开始的 MySQL，以及所有现代版本的 MariaDB。有关任何给定服务器版本支持的功能的详细信息，请参阅官方 MySQL 文档。

从版本 1.4 开始更改：最低支持的 MySQL 版本现在是 5.0.2。

MariaDB 支持

MySQL 的 MariaDB 变体保留了与 MySQL 协议的基本兼容性，但这两个产品的发展仍在分歧。在 SQLAlchemy 领域，这两个数据库有一小部分语法和行为上的差异，SQLAlchemy 会自动适应。要连接到 MariaDB 数据库，不需要对数据库 URL 进行任何更改：

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

在首次连接时，SQLAlchemy 方言采用服务器版本检测方案，确定后端数据库是否报告为 MariaDB。根据此标志，方言可以在其行为必须不同的领域做出不同选择。

仅 MariaDB 模式

该方言还支持一个可选的“仅 MariaDB”连接模式，这对于应用程序使用 MariaDB 特定功能且与 MySQL 数据库不兼容的情况可能很有用。要使用此操作模式，请将上述 URL 中的“mysql”标记替换为“mariadb”：

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

上述引擎在首次连接时，如果服务器版本检测检测到后端数据库不是 MariaDB，则会引发错误。

当使用以"mariadb"作为方言名称的引擎时，所有包含“mysql”名称的 mysql 特定选项现在都以"mariadb"命名。这意味着选项如mysql_engine应该命名为mariadb_engine，等等。对于同时使用“mysql”和"mariadb"方言的应用程序，可以同时使用“mysql”和"mariadb"选项：

my_table = Table(
    "mytable",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("textdata", String(50)),
    mariadb_engine="InnoDB",
    mysql_engine="InnoDB",
)
Index(
    "textdata_ix",
    my_table.c.textdata,
    mysql_prefix="FULLTEXT",
    mariadb_prefix="FULLTEXT",
)

当上述结构被反映时，将发生类似的行为，即当数据库 URL 基于“mariadb”名称时，“mariadb”前缀将存在于选项名称中。

版本 1.4 中的新功能：添加了支持“MariaDB-only mode”的“mariadb”方言名称，用于 MySQL 方言。 ## 连接超时和断开连接

MySQL / MariaDB 具有自动连接关闭行为，对于空闲时间超过固定时间的连接，默认为 8 小时。为了避免出现此问题，使用create_engine.pool_recycle选项，该选项确保如果连接在池中存在了固定秒数，则该连接将被丢弃并替换为新连接：

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

为了更全面地检测连接池中的断开连接，包括适应服务器重启和网络问题，可以采用预先 ping 的方法。请参阅处理断开连接了解当前的方法。

另请参阅

处理断开连接 - 关于处理超时连接以及数据库重新启动的几种技术的背景。 ## 包括存储引擎的 CREATE TABLE 参数

MySQL 和 MariaDB 的 CREATE TABLE 语法都包括各种特殊选项，包括ENGINE、CHARSET、MAX_ROWS、ROW_FORMAT、INSERT_METHOD等等。为了适应这些参数的渲染，指定形式mysql_argument_name="value"。例如，要指定一个具有ENGINE为InnoDB、CHARSET为utf8mb4和KEY_BLOCK_SIZE为1024的表：

Table('mytable', metadata,
      Column('data', String(32)),
      mysql_engine='InnoDB',
      mysql_charset='utf8mb4',
      mysql_key_block_size="1024"
     )

当支持仅 MariaDB 模式时，还必须包含相同的“mariadb”前缀下的键。这些值当然可以独立变化，以便在 MySQL 和 MariaDB 上保持不同的设置：

# support both "mysql" and "mariadb-only" engine URLs
Table('mytable', metadata,
      Column('data', String(32)),
      mysql_engine='InnoDB',
      mariadb_engine='InnoDB',
      mysql_charset='utf8mb4',
      mariadb_charset='utf8',
      mysql_key_block_size="1024"
      mariadb_key_block_size="1024"
     )

MySQL / MariaDB 方言通常将任何指定为mysql_keyword_name的关键字转换为CREATE TABLE语句中的KEYWORD_NAME。其中一些名称将以空格而不是下划线呈现；为了支持此，MySQL 方言具有对这些特定名称的意识，其中包括DATA DIRECTORY（例如mysql_data_directory）、CHARACTER SET（例如mysql_character_set）和INDEX DIRECTORY（例如mysql_index_directory）。

最常见的参数是 mysql_engine，它指的是表的存储引擎。从历史上看，MySQL 服务器安装会将此值默认为 MyISAM，尽管较新版本可能将默认值设置为 InnoDB。InnoDB 引擎通常更受欢迎，因为它支持事务和外键。

在使用 MyISAM 存储引擎创建的 MySQL / MariaDB 数据库中创建的 Table 实际上是非事务性的，这意味着对该表的任何 INSERT/UPDATE/DELETE 语句都将被调用为自动提交。它也不支持外键约束；虽然 CREATE TABLE 语句接受外键选项，但在使用 MyISAM 存储引擎时，这些参数将被丢弃。反映这样的表也不会产生外键约束信息。

对于完全原子事务以及对外键约束的支持，所有参与的 CREATE TABLE 语句必须指定事务引擎，在绝大多数情况下是 InnoDB。

大小写敏感性和表反射

MySQL 和 MariaDB 都对大小写敏感的标识符名称提供不一致的支持，其支持基于底层操作系统的具体细节。但是，已经观察到无论存在何种大小写敏感性行为，外键声明中的表名称总是以全小写形式从数据库接收，这使得准确反映使用混合大小写标识符名称的相互关联表的架构成为不可能。

因此，强烈建议在 SQLAlchemy 中以及在 MySQL / MariaDB 数据库本身中将表名声明为全小写，特别是如果要使用数据库反射功能的话。

事务隔离级别

所有 MySQL / MariaDB 方言都支持通过方言特定参数 create_engine.isolation_level（由 create_engine() 接受）以及作为传递给 Connection.execution_options() 的参数的 Connection.execution_options.isolation_level 参数来设置事务隔离级别。此功能通过为每个新连接发出命令 SET SESSION TRANSACTION ISOLATION LEVEL 来工作。对于特殊的 AUTOCOMMIT 隔离级别，使用了 DBAPI 特定的技术。

使用 create_engine() 设置隔离级别：

engine = create_engine(
                "mysql+mysqldb://scott:tiger@localhost/test",
                isolation_level="READ UNCOMMITTED"
            )

通过每个连接执行选项进行设置：

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="READ COMMITTED"
)

isolation_level的有效值包括：

READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
AUTOCOMMIT

特殊的AUTOCOMMIT值利用特定 DBAPI 提供的各种“autocommit”属性，并且目前受到 MySQLdb、MySQL-Client、MySQL-Connector Python 和 PyMySQL 的支持。使用它，数据库连接将返回SELECT @@autocommit;的值为 true。

还有更多隔离级别配置选项，例如与主Engine关联的“子引擎”对象，每个对象应用不同的隔离级别设置。有关详情，请参阅设置事务隔离级别，包括 DBAPI 自动提交。

另请参见

设置事务隔离级别，包括 DBAPI 自动提交

AUTO_INCREMENT 行为

在创建表时，SQLAlchemy 将自动在第一个未标记为外键的Integer主键列上设置AUTO_INCREMENT：

>>> t = Table('mytable', metadata,
...   Column('mytable_id', Integer, primary_key=True)
... )
>>> t.create()
CREATE TABLE mytable (
 id INTEGER NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (id)
)

您可以通过将False传递给Column.autoincrement参数的Column来禁用此行为。此标志也可用于在某些存储引擎中为多列键的次要列启用自动增量：

Table('mytable', metadata,
      Column('gid', Integer, primary_key=True, autoincrement=False),
      Column('id', Integer, primary_key=True)
     )

服务器端游标

mysqlclient、PyMySQL、mariadbconnector 方言支持服务器端游标，并且可能也适用于其他方言。这可以通过使用“buffered=True/False”标志（如果可用）或通过在内部使用类似于MySQLdb.cursors.SSCursor或pymysql.cursors.SSCursor的类来实现。

服务器端游标可以通过使用Connection.execution_options.stream_results连接执行选项来启用每个语句的。

with engine.connect() as conn:
    result = conn.execution_options(stream_results=True).execute(text("select * from table"))

请注意，某些类型的 SQL 语句可能不支持使用服务器端游标；通常，只应该使用返回行的 SQL 语句与此选项一起使用。

自版本 1.4 起弃用：dialect-level server_side_cursors 标志已弃用，并将在将来的版本中删除。请使用Connection.stream_results执行选项来支持无缓冲游标。

另请参见

使用服务器端游标（也称为流式结果） ## Unicode

字符集选择

大多数 MySQL / MariaDB DBAPI 都提供了为连接设置客户端字符集的选项。通常可以使用 URL 中的charset参数来实现，例如：

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

这个字符集是连接的客户端字符集。一些 MySQL DBAPI 会将其默认为诸如latin1之类的值，而一些则会使用my.cnf文件中的default-character-set设置。应该查阅所使用的 DBAPI 的文档以获取具体行为。

用于 Unicode 的编码传统上一直是'utf8'。然而，从 MySQL 版本 5.5.3 和 MariaDB 5.5 开始，引入了一个新的 MySQL 特定编码'utf8mb4'，而且自 MySQL 8.0 起，如果在任何服务器端指令中指定了纯utf8，服务器会发出警告，并用utf8mb3替换。之所以使用这种新编码的原因是因为 MySQL 的传统 utf-8 编码只支持三字节的代码点而不是四字节。因此，在与包含超过三字节大小的代码点的 MySQL 或 MariaDB 数据库通信时，如果数据库和客户端 DBAPI 都支持，首选使用这种新的字符集，如下所示：

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

所有现代的 DBAPI 都应支持utf8mb4字符集。

为了对使用了传统utf8的模式使用utf8mb4编码，可能需要对 MySQL/MariaDB 模式和/或服务器配置进行更改。

另请参阅

utf8mb4 字符集 - 在 MySQL 文档中

处理二进制数据警告和 Unicode

在写作本文时，MySQL 版本 5.6、5.7 和以后版本（不包括 MariaDB）现在在尝试将二进制数据传递到数据库时会发出警告，同时也设置了字符集编码，但是二进制数据本身对于该编码来说不合法：

default.py:509: Warning: (1300, "Invalid utf8mb4 character string:
'F9876A'")
  cursor.execute(statement, parameters)

此警告是由于 MySQL 客户端库尝试将二进制字符串解释为 Unicode 对象，即使使用了诸如LargeBinary这样的数据类型也是如此。要解决此问题，SQL 语句在任何呈现如下的非 NULL 值之前都需要存在一个二进制的“字符集介绍”：

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

这些字符集介绍由 DBAPI 驱动程序提供，假设使用了 mysqlclient 或 PyMySQL（两者都是推荐的）。将查询字符串参数binary_prefix=true添加到 URL 中以修复此警告：

# mysqlclient
engine = create_engine(
    "mysql+mysqldb://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")
# PyMySQL
engine = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4&binary_prefix=true")

binary_prefix标志可能受到其他 MySQL 驱动程序的支持与否的影响。

SQLAlchemy 本身无法可靠地渲染这个_binary前缀，因为它不适用于 NULL 值，而绑定参数时 NULL 值是有效的。由于 MySQL 驱动程序将参数直接渲染到 SQL 字符串中，这是传递此附加关键字的最有效位置。

另请参阅

字符集介绍 - 在 MySQL 网站上

ANSI 引用风格

MySQL / MariaDB 具有两种标识符“引用风格”，一种使用反引号，另一种使用引号，例如 some_identifier vs. "some_identifier"。所有 MySQL 方言在首次使用特定 Engine 建立连接时，通过检查 sql_mode 的值来检测使用的版本。此引用风格在呈现表和列名称以及反映现有数据库结构时起作用。检测完全是自动的，不需要任何特殊配置来使用任一引用风格。

更改 sql_mode

MySQL 支持在多个服务器 SQL 模式下运行，对于服务器和客户端都是如此。要为给定应用程序更改 sql_mode，开发人员可以利用 SQLAlchemy 的事件系统。

在以下示例中，事件系统用于在 first_connect 和 connect 事件上设置 sql_mode：

from sqlalchemy import create_engine, event
eng = create_engine("mysql+mysqldb://scott:tiger@localhost/test", echo='debug')
# `insert=True` will ensure this is the very first listener to run
@event.listens_for(eng, "connect", insert=True)
def connect(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("SET sql_mode = 'STRICT_ALL_TABLES'")
conn = eng.connect()

在上面示例中，当特定的 DBAPI 连接首次为给定的连接池创建时，“connect”事件将在连接可供连接池使用之前在连接上调用“SET”语句。此外，因为函数被注册为 insert=True，它将被添加到已注册函数的内部列表之前。

MySQL / MariaDB SQL 扩展

许多 MySQL / MariaDB 的 SQL 扩展都通过 SQLAlchemy 的通用函数和操作符支持：

table.select(table.c.password==func.md5('plaintext'))
table.select(table.c.username.op('regexp')('^[a-d]'))

当然，任何有效的 SQL 语句也可以作为字符串执行。

目前可以直接支持一些有限的 MySQL / MariaDB 对 SQL 的扩展。

INSERT…ON DUPLICATE KEY UPDATE：参见 INSERT…ON DUPLICATE KEY UPDATE (Upsert)
SELECT 命令，使用Select.prefix_with()和Query.prefix_with()：

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

带有 LIMIT 的 UPDATE：

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

优化器提示，使用Select.prefix_with()和Query.prefix_with()：

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

索引提示，使用Select.with_hint()和Query.with_hint()：

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

MATCH 运算符支持：

from sqlalchemy.dialects.mysql import match
select(...).where(match(col1, col2, against="some expr").in_boolean_mode())
.. seealso::
    :class:`_mysql.match`

INSERT/DELETE…RETURNING

MariaDB 方言支持 10.5+的INSERT..RETURNING和DELETE..RETURNING（10.0+）语法。在某些情况下，INSERT..RETURNING可以自动使用，以获取新生成的标识符，而不是使用cursor.lastrowid的传统方法，但是对于简单的单语句情况，目前仍更喜欢使用cursor.lastrowid，因为它的性能更好。

要在每个语句的基础上使用显式的RETURNING子句，请使用 _UpdateBase.returning()方法：

# INSERT..RETURNING
result = connection.execute(
    table.insert().
    values(name='foo').
    returning(table.c.col1, table.c.col2)
)
print(result.all())
# DELETE..RETURNING
result = connection.execute(
    table.delete().
    where(table.c.name=='foo').
    returning(table.c.col1, table.c.col2)
)
print(result.all())

版本 2.0 中的新功能：增加了 MariaDB RETURNING 支持

插入…在重复键更新时（Upsert）

MySQL / MariaDB 允许通过 INSERT 语句的 ON DUPLICATE KEY UPDATE 子句将行“upserts”（更新或插入）到表中。只有在该行不匹配表中现有的主键或唯一键时，候选行才会被插入；否则，将执行更新。该语句允许分开指定要插入的值与要更新的值。

SQLAlchemy 通过 MySQL 特定的insert()函数提供ON DUPLICATE KEY UPDATE支持，该函数提供了生成方法Insert.on_duplicate_key_update()：

>>> from sqlalchemy.dialects.mysql import insert
>>> insert_stmt = insert(my_table).values(
...     id='some_existing_id',
...     data='inserted value')
>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     data=insert_stmt.inserted.data,
...     status='U'
... )
>>> print(on_duplicate_key_stmt)
INSERT  INTO  my_table  (id,  data)  VALUES  (%s,  %s)
ON  DUPLICATE  KEY  UPDATE  data  =  VALUES(data),  status  =  %s

不同于 PostgreSQL 的“ON CONFLICT”短语，"ON DUPLICATE KEY UPDATE"短语将始终匹配任何主键或唯一键，并且如果有匹配，将始终执行更新；它没有选项可以引发错误或跳过执行更新。

ON DUPLICATE KEY UPDATE用于对已存在的行执行更新，使用新值的任何组合以及提议插入的值。这些值通常使用传递给Insert.on_duplicate_key_update()的关键字参数指定为列键值（通常是列的名称，除非它指定了Column.key）作为键，字面值或 SQL 表达式作为值：

>>> insert_stmt = insert(my_table).values(
...          id='some_existing_id',
...          data='inserted value')
>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     data="some data",
...     updated_at=func.current_timestamp(),
... )
>>> print(on_duplicate_key_stmt)
INSERT  INTO  my_table  (id,  data)  VALUES  (%s,  %s)
ON  DUPLICATE  KEY  UPDATE  data  =  %s,  updated_at  =  CURRENT_TIMESTAMP

与UpdateBase.values()类似的方式，也接受其他参数形式，包括一个单一的字典：

>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     {"data": "some data", "updated_at": func.current_timestamp()},
... )

以及一个 2-tuple 列表，它将自动提供类似于参数有序更新描述的参数有序 UPDATE 语句的方式。与Update对象不同，不需要特殊标志来指定意图，因为在此上下文中的参数形式是清楚的：

>>> on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
...     [
...         ("data", "some data"),
...         ("updated_at", func.current_timestamp()),
...     ]
... )
>>> print(on_duplicate_key_stmt)
INSERT  INTO  my_table  (id,  data)  VALUES  (%s,  %s)
ON  DUPLICATE  KEY  UPDATE  data  =  %s,  updated_at  =  CURRENT_TIMESTAMP

版本 1.3 中的更改：支持 MySQL ON DUPLICATE KEY UPDATE 内的参数有序 UPDATE 子句

警告

Insert.on_duplicate_key_update() 方法不考虑 Python 端的默认 UPDATE 值或生成函数，例如使用 Column.onupdate 指定的值。这些值不会用于 ON DUPLICATE KEY 样式的 UPDATE，除非在参数中手动明确指定。

为了引用提议的插入行，Insert.inserted 特殊别名可作为 Insert 对象上的属性；此对象是一个 ColumnCollection，包含目标表的所有列：

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh')
>>> do_update_stmt = stmt.on_duplicate_key_update(
...     data="updated value",
...     author=stmt.inserted.author
... )
>>> print(do_update_stmt)
INSERT  INTO  my_table  (id,  data,  author)  VALUES  (%s,  %s,  %s)
ON  DUPLICATE  KEY  UPDATE  data  =  %s,  author  =  VALUES(author)

渲染时，“inserted” 命名空间将生成表达式 VALUES()。

版本 1.2 中新增了对 MySQL ON DUPLICATE KEY UPDATE 子句的支持。

rowcount 支持

SQLAlchemy 将 DBAPI cursor.rowcount 属性标准化为“UPDATE 或 DELETE 语句匹配的行数”的通常定义。这与大多数 MySQL DBAPI 驱动程序的默认设置相矛盾，后者是“实际修改/删除的行数”。因此，SQLAlchemy MySQL 方言在连接时始终添加 constants.CLIENT.FOUND_ROWS 标志，或者在目标方言上等效的标志。这个设置目前是硬编码的。

另见

CursorResult.rowcount

MySQL / MariaDB 特定索引选项

可用于 MySQL 和 MariaDB 的 Index 构造的特定扩展。

索引长度

MySQL 和 MariaDB 都提供了一个选项，可以创建一定长度的索引条目，其中“长度”是指每个值中的字符数或字节数，这些值将成为索引的一部分。SQLAlchemy 通过 mysql_length 和/或 mariadb_length 参数提供了这个功能：

Index('my_index', my_table.c.data, mysql_length=10, mariadb_length=10)
Index('a_b_idx', my_table.c.a, my_table.c.b, mysql_length={'a': 4,
                                                           'b': 9})
Index('a_b_idx', my_table.c.a, my_table.c.b, mariadb_length={'a': 4,
                                                           'b': 9})

对于非二进制字符串类型，前缀长度以字符表示，对于二进制字符串类型，以字节表示。传递给关键字参数的值必须是整数（因此对索引的所有列都指定相同的前缀长度值）或字典，在字典中，键是列名，值是相应列的前缀长度值。MySQL 和 MariaDB 仅允许对索引的列指定长度，如果它是 CHAR、VARCHAR、TEXT、BINARY、VARBINARY 和 BLOB 类型的列。

索引前缀

MySQL 存储引擎允许在创建索引时指定索引前缀。SQLAlchemy 通过Index的mysql_prefix参数提供了这个功能：

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

传递给关键字参数的值将简单地传递给底层的 CREATE INDEX，因此它必须是您的 MySQL 存储引擎的有效索引前缀。

另请参阅

CREATE INDEX - MySQL 文档

索引类型

一些 MySQL 存储引擎允许在创建索引或主键约束时指定索引类型。SQLAlchemy 通过Index的mysql_using参数提供了这个功能：

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

以及PrimaryKeyConstraint上的mysql_using参数：

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

传递给关键字参数的值将简单地传递给底层的 CREATE INDEX 或 PRIMARY KEY 子句，因此它必须是您的 MySQL 存储引擎的有效索引类型。

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

索引解析器

MySQL 中的 CREATE FULLTEXT INDEX 也支持“WITH PARSER”选项。可以使用关键字参数mysql_with_parser来实现：

Index(
    'my_index', my_table.c.data,
    mysql_prefix='FULLTEXT', mysql_with_parser="ngram",
    mariadb_prefix='FULLTEXT', mariadb_with_parser="ngram",
)

1.3 版本中的新功能 ## MySQL / MariaDB 外键

MySQL 和 MariaDB 关于外键的行为有一些重要的注意事项。

避免使用的外键参数

MySQL 和 MariaDB 都不支持外键参数“DEFERRABLE”、“INITIALLY”或“MATCH”。在ForeignKeyConstraint或ForeignKey中使用deferrable或initially关键字参数将导致这些关键字在 DDL 表达式中被渲染，然后在 MySQL 或 MariaDB 上引发错误。为了在外键上使用这些关键字，同时在 MySQL / MariaDB 后端上忽略它们，可以使用自定义编译规则：

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.schema import ForeignKeyConstraint
@compiles(ForeignKeyConstraint, "mysql", "mariadb")
def process(element, compiler, **kw):
    element.deferrable = element.initially = None
    return compiler.visit_foreign_key_constraint(element, **kw)

“MATCH”关键字实际上更加隐蔽，而且在与 MySQL 或 MariaDB 后端一起使用时，SQLAlchemy 明确禁止使用。这个参数在 MySQL / MariaDB 中被静默忽略，但另外的效果是 ON UPDATE 和 ON DELETE 选项也被后端忽略。因此，不应该在 MySQL / MariaDB 后端使用 MATCH；与 DEFERRABLE 和 INITIALLY 一样，可以使用自定义编译规则来在 DDL 定义时纠正 ForeignKeyConstraint。

外键约束的反射

并非所有 MySQL / MariaDB 存储引擎都支持外键。在使用非常常见的 MyISAM MySQL 存储引擎时，通过表反射加载的信息将不包括外键。对于这些表，您可以在反射时提供一个 ForeignKeyConstraint：

Table('mytable', metadata,
      ForeignKeyConstraint(['other_id'], ['othertable.other_id']),
      autoload_with=engine
     )

另请参阅

包括存储引擎的 CREATE TABLE 参数 ## MySQL / MariaDB 唯一约束和反射

SQLAlchemy 支持带有标志 unique=True 的 Index 构造，表示唯一索引，以及表示唯一约束的 UniqueConstraint 构造。在创建这些约束时，MySQL / MariaDB 支持这两种对象/语法。但是，MySQL / MariaDB 没有一个独立于唯一索引的唯一约束构造；也就是说，在 MySQL / MariaDB 上，“UNIQUE” 约束等同于创建一个“UNIQUE INDEX”。

在反射这些构造时，Inspector.get_indexes() 和 Inspector.get_unique_constraints() 方法都会在 MySQL / MariaDB 中为唯一索引返回一个条目。然而，在使用 Table(..., autoload_with=engine) 执行完整表反射时，UniqueConstraint 构造在任何情况下都不是完全反映的 Table 构造的一部分；这个构造总是由 Table.indexes 集合中存在 unique=True 设置的 Index 表示。

TIMESTAMP / DATETIME 问题

为 MySQL / MariaDB 的 explicit_defaults_for_timestamp 启用 ON UPDATE CURRENT TIMESTAMP 渲染

MySQL / MariaDB 在历史上将 TIMESTAMP 数据类型的 DDL 扩展为短语“TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”，其中包含非标准 SQL，当发生 UPDATE 时自动使用当前时间戳更新列，消除了在需要服务器端更新更改的情况下使用触发器的常规需求。

MySQL 5.6 引入了一个新的标志 explicit_defaults_for_timestamp，它禁用了上述行为，在 MySQL 8 中，此标志默认为 true，这意味着为了获得 MySQL 的“更新时间戳”，而不改变此标志，上述 DDL 必须显式地呈现。此外，相同的 DDL 对于 DATETIME 数据类型也是有效的。

SQLAlchemy 的 MySQL 方言尚未提供生成 MySQL 的“ON UPDATE CURRENT_TIMESTAMP”子句的选项，注意这不是通用的“ON UPDATE”，因为标准 SQL 中没有这样的语法。SQLAlchemy 的 Column.server_onupdate 参数目前与此特殊的 MySQL 行为无关。

要生成此 DDL，请使用 Column.server_default 参数，并传递一个包含 ON UPDATE 子句的文本子句：

from sqlalchemy import Table, MetaData, Column, Integer, String, TIMESTAMP
from sqlalchemy import text
metadata = MetaData()
mytable = Table(
    "mytable",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('data', String(50)),
    Column(
        'last_updated',
        TIMESTAMP,
        server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
    )
)

相同的说明适用于使用 DateTime 和 DATETIME 数据类型：

from sqlalchemy import DateTime
mytable = Table(
    "mytable",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('data', String(50)),
    Column(
        'last_updated',
        DateTime,
        server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
    )
)

即使 Column.server_onupdate 功能不生成此 DDL，但仍然有可能希望向 ORM 发出信号，表示应该获取此更新的值。此语法如下所示：

from sqlalchemy.schema import FetchedValue
class MyClass(Base):
    __tablename__ = 'mytable'
    id = Column(Integer, primary_key=True)
    data = Column(String(50))
    last_updated = Column(
        TIMESTAMP,
        server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
        server_onupdate=FetchedValue()
    )
```### TIMESTAMP 列和 NULL
MySQL 历史上要求指定 TIMESTAMP 数据类型的列隐式包括默认值 CURRENT_TIMESTAMP，即使没有明确说明，并且另外将列设置为 NOT NULL，这与所有其他数据类型相反的行为：
```py
mysql> CREATE TABLE ts_test (
    -> a INTEGER,
    -> b INTEGER NOT NULL,
    -> c TIMESTAMP,
    -> d TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -> e TIMESTAMP NULL);
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW CREATE TABLE ts_test;
+---------+-----------------------------------------------------
| Table   | Create Table
+---------+-----------------------------------------------------
| ts_test | CREATE TABLE `ts_test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) NOT NULL,
  `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `e` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

以上，我们看到一个 INTEGER 列默认为 NULL，除非指定为 NOT NULL。但是当列的类型为 TIMESTAMP 时，会生成一个隐式的默认值 CURRENT_TIMESTAMP，这也会强制使列成为 NOT NULL，即使我们没有这样指定。

MySQL 的这种行为可以通过 MySQL 方面的 explicit_defaults_for_timestamp 配置标志在 MySQL 5.6 中引入。启用此服务器设置后，TIMESTAMP 列在 MySQL 方面的默认值和可空性方面的行为类似于任何其他数据类型。

然而，为了适应大多数未指定此新标志的 MySQL 数据库，SQLAlchemy 在任何未指定 nullable=False 的 TIMESTAMP 列中都显式地发出“NULL”指示符。为了适应指定了 explicit_defaults_for_timestamp 的新数据库，SQLAlchemy 还为指定了 nullable=False 的 TIMESTAMP 列发出 NOT NULL。以下示例说明了：

from sqlalchemy import MetaData, Integer, Table, Column, text
from sqlalchemy.dialects.mysql import TIMESTAMP
m = MetaData()
t = Table('ts_test', m,
        Column('a', Integer),
        Column('b', Integer, nullable=False),
        Column('c', TIMESTAMP),
        Column('d', TIMESTAMP, nullable=False)
    )
from sqlalchemy import create_engine
e = create_engine("mysql+mysqldb://scott:tiger@localhost/test", echo=True)
m.create_all(e)

输出：

CREATE TABLE ts_test (
    a INTEGER,
    b INTEGER NOT NULL,
    c TIMESTAMP NULL,
    d TIMESTAMP NOT NULL
)

SqlAlchemy 2.0 中文文档（四十九）（2）https://developer.aliyun.com/article/1563028

SqlAlchemy 2.0 中文文档（四十九）（1）

MySQL 和 MariaDB

DBAPI 支持

支持的版本和功能

MariaDB 支持

仅 MariaDB 模式

大小写敏感性和表反射

事务隔离级别

AUTO_INCREMENT 行为

服务器端游标

字符集选择

处理二进制数据警告和 Unicode

ANSI 引用风格

更改 sql_mode

MySQL / MariaDB SQL 扩展

INSERT/DELETE…RETURNING

插入…在重复键更新时（Upsert）

rowcount 支持

MySQL / MariaDB 特定索引选项

索引长度

索引前缀

索引类型

索引解析器

避免使用的外键参数

外键约束的反射

TIMESTAMP / DATETIME 问题

为 MySQL / MariaDB 的 explicit_defaults_for_timestamp 启用 ON UPDATE CURRENT TIMESTAMP 渲染

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

SqlAlchemy 2.0 中文文档（四十九）（1）

MySQL 和 MariaDB

DBAPI 支持

支持的版本和功能

MariaDB 支持

仅 MariaDB 模式

大小写敏感性和表反射

事务隔离级别

AUTO_INCREMENT 行为

服务器端游标

字符集选择

处理二进制数据警告和 Unicode

ANSI 引用风格

更改 sql_mode

MySQL / MariaDB SQL 扩展

INSERT/DELETE…RETURNING

插入…在重复键更新时（Upsert）

rowcount 支持

MySQL / MariaDB 特定索引选项

索引长度

索引前缀

索引类型

索引解析器

避免使用的外键参数

外键约束的反射

TIMESTAMP / DATETIME 问题

为 MySQL / MariaDB 的 explicit_defaults_for_timestamp 启用 ON UPDATE CURRENT TIMESTAMP 渲染

热门文章

最新文章

相关电子书