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

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

DBAPI

文档和 MySQL Connector/Python 的下载信息（如果适用）可在此处获取：pypi.org/project/mysql-connector-python/

连接

连接字符串：

mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

注意

自 MySQL Connector/Python 发布以来，DBAPI 存在许多问题，其中一些可能仍未解决，并且 mysqlconnector 方言 未经过 SQLAlchemy 的持续集成测试。推荐的 MySQL 方言是 mysqlclient 和 PyMySQL。 ## asyncmy

通过 asyncmy 驱动程序支持 MySQL / MariaDB 数据库。

DBAPI

文档和 asyncmy 的下载信息（如果适用）可在此处获取：github.com/long2ice/asyncmy

连接

连接字符串：

mysql+asyncmy://user:password@host:port/dbname[?key=value&key=value...]

使用特殊的 asyncio 中介层，asyncmy 方言可用作 SQLAlchemy asyncio 扩展包的后端。

此方言通常仅应与create_async_engine()引擎创建函数一起使用：

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("mysql+asyncmy://user:pass@hostname/dbname?charset=utf8mb4")
```## aiomysql
通过 aiomysql 驱动程序支持 MySQL / MariaDB 数据库。
### DBAPI
文档和 aiomysql 的下载信息（如果适用）可在此处获取：[`github.com/aio-libs/aiomysql`](https://github.com/aio-libs/aiomysql)
### 连接
连接字符串：
```py
mysql+aiomysql://user:password@host:port/dbname[?key=value&key=value...]

aiomysql 方言是 SQLAlchemy 的第二个 Python asyncio 方言。

使用特殊的 asyncio 中介层，aiomysql 方言可用作 SQLAlchemy asyncio 扩展包的后端。

此方言通常仅应与create_async_engine()引擎创建函数一起使用：

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("mysql+aiomysql://user:pass@hostname/dbname?charset=utf8mb4")
```## cymysql
通过 CyMySQL 驱动程序支持 MySQL / MariaDB 数据库。
### DBAPI
文档和 CyMySQL 的下载信息（如果适用）可在此处获取：[`github.com/nakagami/CyMySQL`](https://github.com/nakagami/CyMySQL)
### 连接
连接字符串：
```py
mysql+cymysql://<username>:<password>@<host>/<dbname>[?<options>]

注意

CyMySQL 方言 未经过 SQLAlchemy 的持续集成测试，可能存在未解决的问题。推荐的 MySQL 方言是 mysqlclient 和 PyMySQL。 ## pyodbc

通过 PyODBC 驱动程序支持 MySQL / MariaDB 数据库。

DBAPI

文档和 PyODBC 的下载信息（如果适用）可在此处获取：pypi.org/project/pyodbc/

连接

连接字符串：

mysql+pyodbc://<username>:<password>@<dsnname>

注意

MySQL 的 PyODBC 方言未经过 SQLAlchemy 的持续集成测试。推荐使用的 MySQL 方言是 mysqlclient 和 PyMySQL。但是，如果您想使用 mysql+pyodbc 方言并需要完全支持utf8mb4字符（包括表情符号等辅助字符），请确保使用当前版本的 MySQL Connector/ODBC 并在 DSN 或连接字符串中指定“ANSI”（不是“Unicode”）驱动程序版本。

通过精确的 pyodbc 连接字符串传递：

import urllib
connection_string = (
    'DRIVER=MySQL ODBC 8.0 ANSI Driver;'
    'SERVER=localhost;'
    'PORT=3307;'
    'DATABASE=mydb;'
    'UID=root;'
    'PWD=(whatever);'
    'charset=utf8mb4;'
)
params = urllib.parse.quote_plus(connection_string)
connection_uri = "mysql+pyodbc:///?odbc_connect=%s" % params

支持 MySQL / MariaDB 数据库。

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

支持的 MySQL / MariaDB 版本

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 支持

MariaDB 变种的 MySQL 保留了与 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"方言 URL 的应用程序，可以同时使用“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”前缀。

新版本中新增了“mariadb”方言名称，支持 MySQL 方言的“仅 MariaDB 模式”。

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"方言 URL 的应用程序，可以同时使用“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”前缀。

新版本中新增了“mariadb”方言名称，支持 MySQL 方言的“仅 MariaDB 模式”。

连接超时和断开连接

MySQL / MariaDB 具有自动关闭连接行为，对于空闲一段固定时间的连接，默认为八小时。要避免出现此问题，可以使用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 引擎通常更受欢迎，因为它支持事务和外键。

在 MySQL / MariaDB 数据库中创建的具有MyISAM存储引擎的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 提供的各种“自动提交”属性，目前由 MySQLdb、MySQL-Client、MySQL-Connector Python 和 PyMySQL 支持。使用它，数据库连接将返回 SELECT @@autocommit; 的值为真。

还有更多隔离级别配置选项，例如与主 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参数，您可以禁用此行为。此标志还可用于在某些存储引擎中启用多列键中的辅助列的自动增量：

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 驱动程序支持。

由于 MySQL 驱动程序直接将参数呈现到 SQL 字符串中，因此无法可靠地呈现此_binary前缀，因为它不会与 NULL 值一起使用，而 NULL 值可以作为绑定参数发送。由于 MySQL 驱动程序直接将参数呈现到 SQL 字符串中，因此这个附加关键字被传递的地方效率最高。

另请参阅

字符集引导 - MySQL 网站上

字符集选择

大多数 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（两者都推荐）。在 URL 中添加查询字符串参数binary_prefix=true以修复此警告：

# 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 的值来检测正在使用的版本。 当与特定池的给定 DBAPI 连接首次创建连接时，此引号样式用于渲染表和列名称以及反映现有数据库结构。 检测完全自动，不需要特殊配置来使用任何引号样式。

更改 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()

在上面说明的示例中，“connect”事件将在特定 DBAPI 连接首次为给定的池创建连接时在连接池将连接提供给连接池之前在连接上调用“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 扩展到 SQL 的方法。

• INSERT…ON DUPLICATE KEY UPDATE：参见 INSERT…ON DUPLICATE KEY UPDATE（Upsert）
  
• SELECT pragma，请使用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 的支持

INSERT…ON DUPLICATE KEY UPDATE（更新插入）

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

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”短语将始终匹配任何主键或唯一键，并且始终在匹配时执行 UPDATE；它没有选项可以引发错误或跳过执行 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 元组的列表，它将自动提供类似于参数顺序更新中描述的方法的参数排序 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 子句的支持

行数支持

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

另请参见

CursorResult.rowcount

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