SqlAlchemy 2.0 中文文档（五十二）（2）

SqlAlchemy 2.0 中文文档（五十二）（1）https://developer.aliyun.com/article/1563140

DDL 注释支持

注释支持包括对 Table.comment 和 Column.comment 等属性的 DDL 渲染，以及反映这些注释的能力，假设使用的 SQL Server 版本支持。如果在首次连接时检测到不支持的版本，如 Azure Synapse（基于 fn_listextendedproperty SQL 函数的存在），则禁用注释支持，包括渲染和表注释反射，因为这两个功能都依赖于并非所有后端类型都可用的 SQL Server 存储过程和函数。

要强制启用或禁用注释支持，绕过自动检测，在 create_engine() 中设置参数 supports_comments：

e = create_engine("mssql+pyodbc://u:p@dsn", supports_comments=False)

版本 2.0 新增了对 SQL Server 方言的表和列注释的支持，包括 DDL 生成和反射。 ## 事务隔离级别

所有 SQL Server 方言都支持通过方言特定参数create_engine.isolation_level（由create_engine()接受）以及作为传递给Connection.execution_options()的参数的Connection.execution_options.isolation_level来设置事务隔离级别。此功能通过为每个新连接发出SET TRANSACTION ISOLATION LEVEL 命令来工作。

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

engine = create_engine(
    "mssql+pyodbc://scott:tiger@ms_2008",
    isolation_level="REPEATABLE READ"
)

使用每个连接的执行选项来设置：

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

isolation_level 的有效值包括：

• AUTOCOMMIT - 仅适用于 pyodbc / pymssql
  
• READ COMMITTED
  
• READ UNCOMMITTED
  
• REPEATABLE READ
  
• SERIALIZABLE
  
• SNAPSHOT - 适用于 SQL Server 特定的隔离级别
  

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

另请参阅

设置事务隔离级别，包括 DBAPI 自动提交 ## 临时表 / 资源重置以用于连接池

SQLAlchemy Engine 对象使用的 QueuePool 连接池实现包括在连接返回池时调用 DBAPI 的.rollback()方法的 重置行为，虽然此回滚会清除上一个事务使用的即时状态，但它不包括更广泛的会话级状态，包括临时表以及其他服务器状态，如预编译的语句句柄和语句缓存。一个名为 sp_reset_connection 的未记录的 SQL Server 过程已知可解决此问题，它将重置在连接上建立的大部分会话状态，包括临时表。

要将 sp_reset_connection 安装为执行返回时的重置手段，可以使用 PoolEvents.reset() 事件挂钩，如下面的示例所示。create_engine.pool_reset_on_return 参数设置为 None，以便自定义方案可以完全替换默认行为。自定义挂钩实现在任何情况下都调用 .rollback()，因为通常重要的是 DBAPI 自身对提交/回滚的跟踪将与事务的状态保持一致：

from sqlalchemy import create_engine
from sqlalchemy import event
mssql_engine = create_engine(
    "mssql+pyodbc://scott:tiger⁵HHH@mssql2017:1433/test?driver=ODBC+Driver+17+for+SQL+Server",
    # disable default reset-on-return scheme
    pool_reset_on_return=None,
)
@event.listens_for(mssql_engine, "reset")
def _reset_mssql(dbapi_connection, connection_record, reset_state):
    if not reset_state.terminate_only:
        dbapi_connection.execute("{call sys.sp_reset_connection}")
    # so that the DBAPI itself knows that the connection has been
    # reset
    dbapi_connection.rollback()

从版本 2.0.0b3 中更改：为 PoolEvents.reset() 事件添加了额外的状态参数，并确保该事件对所有“重置”事件都会被调用，以便作为自定义“重置”处理程序的适当位置。以前使用 PoolEvents.checkin() 处理程序的方案仍然可用。

另请参阅

返回时重置 - 在 连接池 文档中

可空性

MSSQL 支持三种列可空性级别。默认的可空性允许空值，并在 CREATE TABLE 构造中明确指定：

name VARCHAR(20) NULL

如果指定了 nullable=None，则不进行任何规定。换句话说，将使用数据库配置的默认值。这将导致：

name VARCHAR(20)

如果 nullable 是 True 或 False，则列将分别为 NULL 或 NOT NULL。

日期/时间处理

DATE 和 TIME 是受支持的。必要时，绑定参数将转换为 datetime.datetime() 对象，大多数 MSSQL  驱动程序都需要这样做，并且如果需要的话，结果将从字符串中进行处理。 DATE 和 TIME 类型对于 MSSQL 2005 及以前的版本不可用 -  如果检测到低于 2008 的服务器版本，则将为这些类型发出 DATETIME 的 DDL。

大型文本/二进制类型弃用

根据 SQL Server 2012/2014 文档，NTEXT、TEXT 和 IMAGE 数据类型将在将来的版本中从 SQL Server 中删除。SQLAlchemy 通常将这些类型关联到 UnicodeText、TextClause 和 LargeBinary 数据类型。

为了适应这种变化，为该方言添加了一个新标志 deprecate_large_types，如果用户没有另外设置，则将基于使用的服务器版本自动设置。此标志的行为如下：

• 当此标志为True时，当用于渲染 DDL 时，UnicodeText、TextClause和LargeBinary数据类型将分别呈现类型NVARCHAR(max)、VARCHAR(max)和VARBINARY(max)。这是从添加此标志开始的新行为。
  
• 当此标志为False时，当用于渲染 DDL 时，UnicodeText、TextClause和LargeBinary数据类型将分别呈现类型NTEXT、TEXT和IMAGE。这是这些类型的长期行为。
  
• 在建立数据库连接之前，标志始于值None。如果使用方言渲染 DDL 而没有设置标志，则其被解释为False。
  
• 在首次连接时，方言会检测是否使用了 SQL Server 版本 2012 或更高版本；如果标志仍然为None，则基于是否检测到 2012 或更高版本，将其设置为True或False。
  
• 当方言创建时，可以将标志设置为True或False，通常通过create_engine()来实现：

eng = create_engine("mssql+pymssql://user:pass@host/db",
                deprecate_large_types=True)

• 在所有 SQLAlchemy 版本中，始终可以使用大写类型对象完全控制“旧”或“新”类型的渲染：NVARCHAR、VARCHAR、VARBINARY、TEXT、NTEXT、IMAGE将始终保持不变，并且始终输出确切的类型。 ## 多部分模式名称
  

SQL Server 模式有时需要多部分来表示其“模式”限定符，即将数据库名称和所有者名称作为单独的标记，例如mydatabase.dbo.some_table。可以使用Table.schema参数一次设置这些多部分名称。

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="mydatabase.dbo"
)

在执行诸如表或组件反射之类的操作时，包含点的模式参数将被拆分为单独的“数据库”和“所有者”组件，以便正确查询 SQL Server  信息模式表，因为这两个值是分开存储的。此外，在为 DDL 或 SQL  呈现模式名称时，这两个组件将被分别引用以用于区分大小写的名称和其他特殊字符。给定如下参数：

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="MyDataBase.dbo"
)

上述模式将呈现为[MyDataBase].dbo，并且在反射中，将使用“dbo”作为所有者和“MyDataBase”作为数据库名称进行反射。

要控制模式名称如何被拆分为数据库/所有者，请在名称中指定括号（在 SQL Server 中是引用字符）。下面，“所有者”将被视为MyDataBase.dbo，而“数据库”将为 None：

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="[MyDataBase.dbo]"
)

要单独指定带有特殊字符或嵌入点的数据库和所有者名称，请使用两组括号：

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="[MyDataBase.Period].[MyOwner.Dot]"
)

自版本 1.2 更改：SQL Server 方言现在将括号视为标识符分隔符，将模式拆分为单独的数据库和所有者标记，以允许名称本身中的点。 ## 传统模式模式

非常旧版本的 MSSQL 方言引入了这样的行为，即在 SELECT 语句中使用模式限定的表时，将自动为其设置别名；给定一个表：

account_table = Table(
    'account', metadata,
    Column('id', Integer, primary_key=True),
    Column('info', String(100)),
    schema="customer_schema"
)

此传统呈现模式将假定“customer_schema.account”不会被 SQL 语句的所有部分接受，如下所示：

>>> eng = create_engine("mssql+pymssql://mydsn", legacy_schema_aliasing=True)
>>> print(account_table.select().compile(eng))
SELECT  account_1.id,  account_1.info
FROM  customer_schema.account  AS  account_1 

此行为模式现在默认关闭，因为似乎没有任何作用；但是，如果传统应用程序依赖于它，则可以使用legacy_schema_aliasing参数来create_engine()，如上所示。

自版本 1.4 弃用：legacy_schema_aliasing标志现已弃用，并将在将来的版本中删除。 ## 聚集索引支持

MSSQL 方言支持通过mssql_clustered选项生成聚集索引（和主键）。此选项适用于Index、UniqueConstraint和PrimaryKeyConstraint。对于索引，此选项可以与mssql_columnstore结合使用以创建聚集列存储索引。

要生成聚集索引：

Index("my_index", table.c.x, mssql_clustered=True)

将索引呈现为CREATE CLUSTERED INDEX my_index ON table (x)。

要生成聚集主键，请使用：

Table('my_table', metadata,
      Column('x', ...),
      Column('y', ...),
      PrimaryKeyConstraint("x", "y", mssql_clustered=True))

这将例如呈现表为：

CREATE TABLE my_table (x INTEGER NOT NULL, y INTEGER NOT NULL,
                       PRIMARY KEY CLUSTERED (x, y))

类似地，我们可以使用以下方式生成��集唯一约束：

Table('my_table', metadata,
      Column('x', ...),
      Column('y', ...),
      PrimaryKeyConstraint("x"),
      UniqueConstraint("y", mssql_clustered=True),
      )

要显式请求非聚集主键（例如，当需要单独的聚集索引时），请使用：

Table('my_table', metadata,
      Column('x', ...),
      Column('y', ...),
      PrimaryKeyConstraint("x", "y", mssql_clustered=False))

这将例如呈现表为：

CREATE TABLE my_table (x INTEGER NOT NULL, y INTEGER NOT NULL,
                       PRIMARY KEY NONCLUSTERED (x, y))

列存储索引支持

MSSQL 方言通过 mssql_columnstore 选项支持列存储索引。此选项适用于 Index。它可以与 mssql_clustered 选项结合使用以创建聚集列存储索引。

生成列存储索引：

Index("my_index", table.c.x, mssql_columnstore=True)

渲染索引为 CREATE COLUMNSTORE INDEX my_index ON table (x)。

要生成聚集列存储索引，请不提供列：

idx = Index("my_index", mssql_clustered=True, mssql_columnstore=True)
# required to associate the index with the table
table.append_constraint(idx)

上述将索引渲染为 CREATE CLUSTERED COLUMNSTORE INDEX my_index ON table。

版本 2.0.18 中新增。

MSSQL 特定的索引选项

除了聚集外，MSSQL 方言还支持其他特殊选项用于 Index。

包括

mssql_include 选项为给定的字符串名称渲染 INCLUDE(colname)：

Index("my_index", table.c.x, mssql_include=['y'])

将索引渲染为 CREATE INDEX my_index ON table (x) INCLUDE (y)

过滤索引

mssql_where 选项为给定的字符串名称渲染 WHERE(condition)：

Index("my_index", table.c.x, mssql_where=table.c.x > 10)

将索引渲染为 CREATE INDEX my_index ON table (x) WHERE x > 10。

版本 1.3.4 中新增。

索引排序

索引排序可通过功能表达式实现，例如：

Index("my_index", table.c.x.desc())

将索引渲染为 CREATE INDEX my_index ON table (x DESC)

另请参阅

功能索引

兼容性级别

MSSQL 支持在数据库级别设置兼容性级别的概念。例如，可以在运行在 SQL2005 数据库服务器上的数据库上运行与 SQL2000 兼容的数据库。server_version_info 将始终返回数据库服务器版本信息（在本例中为 SQL2005），而不是兼容性级别信息。因此，如果在向后兼容模式下运行，SQLAlchemy 可能会尝试使用数据库服务器无法解析的 T-SQL 语句。

触发器

SQLAlchemy 默认使用 OUTPUT INSERTED 来获取通过 IDENTITY 列或其他服务器端默认生成的新主键值。MS-SQL 不允许在具有触发器的表上使用 OUTPUT INSERTED。要在每个具有触发器的 Table 上禁用 OUTPUT INSERTED 的使用，为其指定 implicit_returning=False：

Table('mytable', metadata,
    Column('id', Integer, primary_key=True),
    # ...,
    implicit_returning=False
)

声明形式：

class MyClass(Base):
    # ...
    __table_args__ = {'implicit_returning':False}
```## 行数支持 / ORM 版本控制
SQL Server 驱动程序可能有限的能力返回从 UPDATE 或 DELETE 语句中更新的行数。
截至目前，PyODBC 驱动程序无法在使用 OUTPUT INSERTED 时返回行数。因此，之前的 SQLAlchemy 版本对于依赖于准确行数以将版本号与匹配行匹配的功能（如“ORM 版本控制”功能）存在限制。
SQLAlchemy 2.0 现在针对这些特定用例基于返回的行数手动检索“rowcount”；因此，虽然驱动程序仍然具有此限制，但 ORM 版本功能不再受其影响。从 SQLAlchemy 2.0.5 开始，ORM 版本控制已完全重新启用 pyodbc 驱动程序。
在版本 2.0.5 中更改：为 pyodbc 驱动程序恢复了 ORM 版本控制支持。之前，在 ORM 刷新期间会发出警告，说明不支持版本控制。
## 启用快照隔离
SQL Server 具有默认的事务隔离模式，它锁定整个表，并导致即使是轻度并发的应用程序也具有长时间的持有锁定和频繁的死锁。推荐为整个数据库启用快照隔离以支持现代的并发级别。这通过在 SQL 提示符下执行以下 ALTER DATABASE 命令来完成：
```py
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

关于 SQL Server 快照隔离的背景信息，请参阅 msdn.microsoft.com/en-us/library/ms175095.aspx。

SQL Server SQL 构造

function sqlalchemy.dialects.mssql.try_cast(expression: _ColumnExpressionOrLiteralArgument[Any], type_: _TypeEngineArgument[_T]) → TryCast[_T]

为支持它的后端生成一个 TRY_CAST 表达式；这是一个 CAST，对于不可转换的转换返回 NULL。

在 SQLAlchemy 中，此结构仅由 SQL Server 方言支持，并且如果在其他包含的后端上使用，将引发 CompileError。但是，第三方后端也可能支持此结构。

提示

由于 try_cast() 起源于 SQL Server 方言，因此可以从 sqlalchemy. 以及 sqlalchemy.dialects.mssql 导入。

try_cast() 返回一个 TryCast 实例，并且通常行为类似于 Cast 结构；在 SQL 层面，CAST 和 TRY_CAST 的区别在于 TRY_CAST 对于不可转换的表达式，如将字符串 "hi" 转换为整数值，将返回 NULL。

例如：

from sqlalchemy import select, try_cast, Numeric
stmt = select(
    try_cast(product_table.c.unit_price, Numeric(10, 4))
)

上述内容在 Microsoft SQL Server 上呈现为：

SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
FROM product_table

新版本 2.0.14 中：try_cast() 已从 SQL Server 方言广义化为一个可能由其他方言支持的通用结构。

SqlAlchemy 2.0 中文文档（五十二）（3）https://developer.aliyun.com/article/1563142