SqlAlchemy 2.0 中文文档(五十二)(5)https://developer.aliyun.com/article/1563144
SQL Server 数据类型
与所有 SQLAlchemy 方言一样,所有已知在 SQL Server 中有效的大写类型都可以从顶级方言导入,无论其来源是sqlalchemy.types
还是来自本地方言:
from sqlalchemy.dialects.mssql import ( BIGINT, BINARY, BIT, CHAR, DATE, DATETIME, DATETIME2, DATETIMEOFFSET, DECIMAL, DOUBLE_PRECISION, FLOAT, IMAGE, INTEGER, JSON, MONEY, NCHAR, NTEXT, NUMERIC, NVARCHAR, REAL, SMALLDATETIME, SMALLINT, SMALLMONEY, SQL_VARIANT, TEXT, TIME, TIMESTAMP, TINYINT, UNIQUEIDENTIFIER, VARBINARY, VARCHAR, )
特定于 SQL Server 或具有 SQL Server 特定构造参数的类型如下:
对象名称 | 描述 |
BIT | MSSQL BIT 类型。 |
DATETIME2 | |
DATETIMEOFFSET | |
DOUBLE_PRECISION | SQL Server DOUBLE PRECISION 数据类型。 |
IMAGE | |
JSON | MSSQL JSON 类型。 |
MONEY | |
NTEXT | MSSQL NTEXT 类型,用于最多 2³⁰ 个字符的变长 unicode 文本。 |
REAL | SQL Server REAL 数据类型。 |
ROWVERSION | 实现 SQL Server ROWVERSION 类型。 |
SMALLDATETIME | |
SMALLMONEY | |
SQL_VARIANT | |
TIME | |
TIMESTAMP | 实现 SQL Server TIMESTAMP 类型。 |
TINYINT | |
UNIQUEIDENTIFIER | |
XML | MSSQL XML 类型。 |
class sqlalchemy.dialects.mssql.BIT
MSSQL BIT 类型。
pyodbc 和 pymssql 都将 BIT 列的值作为 Python 返回,因此只需对 Boolean 进行子类化。
成员
init()
类签名
类 sqlalchemy.dialects.mssql.BIT
(sqlalchemy.types.Boolean
)
method __init__(create_constraint: bool = False, name: str | None = None, _create_events: bool = True, _adapted_from: SchemaType | None = None)
从 Boolean
的 sqlalchemy.types.Boolean.__init__
方法继承
构造一个布尔值。
参数:
create_constraint
-
默认为 False。如果布尔值生成为 int/smallint,还会在表上创建 CHECK 约束,以确保值为 1 或 0。
注意
强烈建议 CHECK 约束具有明确的名称,以支持模式管理方面的考虑。这可以通过设置Boolean.name
参数或设置适当的命名约定来实现;请参阅 配置约束命名约定 了解背景信息。
从版本 1.4 开始更改:- 此标志现在默认为 False,意味着对于非本地枚举类型不生成 CHECK 约束。name
- 如果生成 CHECK 约束,请指定约束的名称。
class sqlalchemy.dialects.mssql.CHAR
SQL CHAR 类型。
类签名
类 sqlalchemy.dialects.mssql.CHAR
(sqlalchemy.types.String
)
method __init__(length: int | None = None, collation: str | None = None)
从 String
的 sqlalchemy.types.String.__init__
方法继承
创建一个保存字符串的类型。
参数:
length
– 可选,用于 DDL 和 CAST 表达式中的列长度。如果不会发出CREATE TABLE
,则可以安全地省略。某些数据库可能需要用于 DDL 的length
,如果包含了没有长度的VARCHAR
,则会在发出CREATE TABLE
DDL 时引发异常。值是作为字节还是字符解释是特定于数据库的。collation
–
可选,用于 DDL 和 CAST 表达式的列级别排序。使用 SQLite、MySQL 和 PostgreSQL 支持的 COLLATE 关键字进行呈现。例如:
>>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
- 注意
在大多数情况下,应该为预计存储非 ASCII 数据的Column
使用Unicode
或UnicodeText
数据类型。这些数据类型将确保在数据库上使用正确的类型。
class sqlalchemy.dialects.mssql.DATETIME2
类签名
类 sqlalchemy.dialects.mssql.DATETIME2
(sqlalchemy.dialects.mssql.base._DateTimeBase
,sqlalchemy.types.DateTime
)
class sqlalchemy.dialects.mssql.DATETIMEOFFSET
类签名
类 sqlalchemy.dialects.mssql.DATETIMEOFFSET
(sqlalchemy.dialects.mssql.base._DateTimeBase
,sqlalchemy.types.DateTime
)
class sqlalchemy.dialects.mssql.DOUBLE_PRECISION
SQL Server 的 DOUBLE PRECISION 数据类型。
新版 2.0.11 中新增。
类签名
类 sqlalchemy.dialects.mssql.DOUBLE_PRECISION
(sqlalchemy.types.DOUBLE_PRECISION
)
class sqlalchemy.dialects.mssql.IMAGE
成员
init()
类签名
类 sqlalchemy.dialects.mssql.IMAGE
(sqlalchemy.types.LargeBinary
)
method __init__(length: int | None = None)
继承自 LargeBinary
的 sqlalchemy.types.LargeBinary.__init__
方法
构造一个 LargeBinary 类型。
参数:
length – 可选,用于 DDL 语句中的列长度,对于那些接受长度的二进制类型,如 MySQL 的 BLOB 类型。
class sqlalchemy.dialects.mssql.JSON
MSSQL JSON 类型。
MSSQL 支持 JSON 格式的数据,从 SQL Server 2016 开始。
在 DDL 级别上,JSON
数据类型将表示为 NVARCHAR(max)
,但还提供了 JSON 级别的比较函数以及 Python 强制行为。
每当基本的 JSON
数据类型用于 SQL Server 后端时,都会自动使用 JSON
。
另请参阅
JSON
- 通用跨平台 JSON 数据类型的主要文档。
JSON
类型支持将 JSON 值持久化,同时通过调整操作以在数据库级别渲染 JSON_VALUE
或 JSON_QUERY
函数来提供 JSON
数据类型提供的核心索引操作。
SQL Server JSON
类型在查询 JSON 对象的元素时必然使用 JSON_QUERY
和 JSON_VALUE
函数。 这两个函数有一个主要限制,即它们基于要返回的对象类型是 互斥的。 JSON_QUERY
函数仅返回 JSON 字典或列表,而不是单个字符串、数字或布尔元素;JSON_VALUE
函数仅返回单个字符串、数字或布尔元素。 这两个函数都会在不使用预期正确的值时返回 NULL 或引发错误。
为了处理这个尴尬的要求,索引访问规则如下:
- 当从 JSON 中提取的子元素本身是 JSON 字典或列表时,应使用
Comparator.as_json()
访问器:
stmt = select( data_table.c.data["some key"].as_json() ).where( data_table.c.data["some key"].as_json() == {"sub": "structure"} )
- 当从 JSON 中提取为普通布尔值、字符串、整数或浮点数的子元素时,请使用以下适当的方法之一:
Comparator.as_boolean()
、Comparator.as_string()
、Comparator.as_integer()
、Comparator.as_float()
:
stmt = select( data_table.c.data["some key"].as_string() ).where( data_table.c.data["some key"].as_string() == "some string" )
版本 1.4 中的新功能。
成员
init()
类签名
类 sqlalchemy.dialects.mssql.JSON
(sqlalchemy.types.JSON
)
method __init__(none_as_null: bool = False)
继承自 JSON
的 sqlalchemy.types.JSON.__init__
方法
构造一个 JSON
类型。
参数:
none_as_null=False –
如果为 True,则将值 None
持久化为 SQL NULL 值,而不是 null
的 JSON 编码。请注意,当此标志为 False 时,null()
构造仍然可以用于持久化 NULL 值,可以直接作为参数值传递,由 JSON
类型特殊解释为 SQL NULL:
from sqlalchemy import null conn.execute(table.insert(), {"data": null()})
注意
JSON.none_as_null
不适用于传递给 Column.default
和 Column.server_default
的值;这些参数的值为 None
表示“没有默认值”。
此外,在 SQL 比较表达式中使用时,Python 值 None
仍然指的是 SQL 空值,而不是 JSON 的 NULL。JSON.none_as_null
标志明确指示了值在 INSERT 或 UPDATE 语句中的持久性。JSON.NULL
值应该用于希望与 JSON null 进行比较的 SQL 表达式。
另请参阅
JSON.NULL
class sqlalchemy.dialects.mssql.MONEY
类签名
类 sqlalchemy.dialects.mssql.MONEY
(sqlalchemy.types.TypeEngine
)
class sqlalchemy.dialects.mssql.NCHAR
SQL NCHAR 类型。
类签名
类 sqlalchemy.dialects.mssql.NCHAR
(sqlalchemy.types.Unicode
)
method __init__(length: int | None = None, collation: str | None = None)
继承自 String
的 sqlalchemy.types.String.__init__
方法
创建一个保存字符串的类型。
参数:
length
– 可选,用于 DDL 和 CAST 表达式中的列长度。如果不会发出CREATE TABLE
,则可以安全地省略。某些数据库可能要求在 DDL 中使用长度,并且如果包含没有长度的VARCHAR
,则在发出CREATE TABLE
DDL 时会引发异常。值是按字节还是按字符解释是数据库特定的。collation
–
可选,用于 DDL 和 CAST 表达式中的列级别排序。使用由 SQLite、MySQL 和 PostgreSQL 支持的 COLLATE 关键字呈现。例如:
>>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
- 注意
在大多数情况下,应该使用Unicode
或UnicodeText
数据类型来存储非 ASCII 数据的Column
。这些数据类型将确保在数据库上使用正确的类型。
class sqlalchemy.dialects.mssql.NTEXT
MSSQL NTEXT 类型,用于最多 2³⁰ 个字符的可变长度 Unicode 文本。
成员
init()
类签名
类 sqlalchemy.dialects.mssql.NTEXT
(sqlalchemy.types.UnicodeText
)
method __init__(length: int | None = None, collation: str | None = None)
继承自 String
的 sqlalchemy.types.String.__init__
方法
创建一个持有字符串的类型。
参数:
length
– 可选的,用于 DDL 和 CAST 表达式中的列的长度。如果不会发出CREATE TABLE
,则可以安全地省略。某些数据库可能需要在 DDL 中使用length
,如果包含一个没有长度的VARCHAR
,则会在发出CREATE TABLE
DDL 时引发异常。值是以字节还是字符解释是数据库特定的。collation
–
可选的,用于 DDL 和 CAST 表达式中的列级排序。使用 SQLite、MySQL 和 PostgreSQL 支持的 COLLATE 关键字进行渲染。例如:
>>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
- 注意
在大多数情况下,应该使用Unicode
或UnicodeText
数据类型来存储非 ASCII 数据的Column
。这些数据类型将确保在数据库上使用正确的类型。
class sqlalchemy.dialects.mssql.NVARCHAR
SQL NVARCHAR 类型。
类签名
类 sqlalchemy.dialects.mssql.NVARCHAR
(sqlalchemy.types.Unicode
)
method __init__(length: int | None = None, collation: str | None = None)
继承自 String
的 sqlalchemy.types.String.__init__
方法
创建一个持有字符串的类型。
参数:
length
– 可选的,用于 DDL 和 CAST 表达式中的列的长度。如果不会发出CREATE TABLE
,则可以安全地省略。某些数据库可能需要在 DDL 中使用length
,如果包含一个没有长度的VARCHAR
,则会在发出CREATE TABLE
DDL 时引发异常。值是以字节还是字符解释是与数据库相关的。collation
–
可选的,用于 DDL 和 CAST 表达式中的列级排序。使用 SQLite、MySQL 和 PostgreSQL 支持的 COLLATE 关键字进行渲染。例如:
>>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
- 注意
在大多数情况下,应该使用Column
期望存储非 ASCII 数据的Unicode
或UnicodeText
数据类型。这些数据类型将确保在数据库上使用正确的类型。
class sqlalchemy.dialects.mssql.REAL
SQL Server REAL 数据类型。
类签名
类 sqlalchemy.dialects.mssql.REAL
(sqlalchemy.types.REAL
)。
class sqlalchemy.dialects.mssql.ROWVERSION
实现 SQL Server ROWVERSION 类型。
ROWVERSION 数据类型是 SQL Server TIMESTAMP 数据类型的同义词,但当前的 SQL Server 文档建议将 ROWVERSION 用于未来新的数据类型。
ROWVERSION 数据类型 不会 作为自身反映(例如自省)从数据库中返回;返回的数据类型将是 TIMESTAMP
。
这是一个只读数据类型,不支持插入值。
新版本 1.2 中的新增功能。
另请参阅
TIMESTAMP
成员
init()
类签名
类 sqlalchemy.dialects.mssql.ROWVERSION
(sqlalchemy.dialects.mssql.base.TIMESTAMP
)。
method __init__(convert_int=False)
继承自 TIMESTAMP
的 sqlalchemy.dialects.mssql.base.TIMESTAMP.__init__
方法。
构造一个 TIMESTAMP 或 ROWVERSION 类型。
参数:
convert_int – 如果为 True,则在读取时将二进制整数值转换为整数。
新版本 1.2 中的新增功能。
class sqlalchemy.dialects.mssql.SMALLDATETIME
成员
init()
类签名
类 sqlalchemy.dialects.mssql.SMALLDATETIME
(sqlalchemy.dialects.mssql.base._DateTimeBase
, sqlalchemy.types.DateTime
)。
method __init__(timezone: bool = False)
继承自 DateTime
的 sqlalchemy.types.DateTime.__init__
方法。
构造一个新的 DateTime
。
参数:
timezone – 布尔值。指示日期/时间类型是否应启用时区支持,仅当基本日期/时间持有类型可用时。建议在使用此标志时直接使用 TIMESTAMP
数据类型,因为某些数据库包含与支持时区的 TIMESTAMP 数据类型不同的单独的通用日期/时间持有类型,例如 Oracle。
class sqlalchemy.dialects.mssql.SMALLMONEY
类签名
类sqlalchemy.dialects.mssql.SMALLMONEY
(sqlalchemy.types.TypeEngine
)
class sqlalchemy.dialects.mssql.SQL_VARIANT
类签名
类sqlalchemy.dialects.mssql.SQL_VARIANT
(sqlalchemy.types.TypeEngine
)
class sqlalchemy.dialects.mssql.TEXT
SQL TEXT 类型。
类签名
类 sqlalchemy.dialects.mssql.TEXT
(sqlalchemy.types.Text
)
method __init__(length: int | None = None, collation: str | None = None)
从 String
的 sqlalchemy.types.String.__init__
方法继承
创建一个持有字符串的类型。
参数:
length
– 可选项,用于 DDL 和 CAST 表达式中的列长度。如果不会发出CREATE TABLE
,则可以安全地省略。某些数据库可能要求在 DDL 中使用长度,并且如果包括没有长度的VARCHAR
,则在发出CREATE TABLE
DDL 时会引发异常。该值是以字节还是字符解释是数据库特定的。collation
–
可选项,用于 DDL 和 CAST 表达式中的列级排序。在 SQLite、MySQL 和 PostgreSQL 中使用 COLLATE 关键字进行呈现。例如:
>>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
- 注意
在大多数情况下,Unicode
或UnicodeText
数据类型应用于预期存储非 ASCII 数据的Column
。这些数据类型将确保在数据库上使用正确的类型。
class sqlalchemy.dialects.mssql.TIME
类签名
类sqlalchemy.dialects.mssql.TIME
(sqlalchemy.types.TIME
)
class sqlalchemy.dialects.mssql.TIMESTAMP
实现 SQL Server TIMESTAMP 类型。
注意,这与 SQL 标准的 TIMESTAMP 类型完全不同,该类型不受 SQL Server 支持。它是一个只读数据类型,不支持插入值。
版本 1.2 中的新功能。
另请参阅
ROWVERSION
成员
init()
类签名
类 sqlalchemy.dialects.mssql.TIMESTAMP
(sqlalchemy.types._Binary
)
method __init__(convert_int=False)
构造 TIMESTAMP 或 ROWVERSION 类型。
参数:
convert_int – 如果为 True,则二进制整数值将在读取时转换为整数。
新功能,版本 1.2。
class sqlalchemy.dialects.mssql.TINYINT
类签名
类 sqlalchemy.dialects.mssql.TINYINT
(sqlalchemy.types.Integer
)
class sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER
成员
init()
类签名
类 sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER
(sqlalchemy.types.Uuid
)
method __init__(as_uuid: bool = True)
构造一个 UNIQUEIDENTIFIER
类型。
参数:
as_uuid=True –
如果为 True,则将值解释为 Python uuid 对象,通过 DBAPI 转换为/从字符串。
class sqlalchemy.dialects.mssql.VARBINARY
MSSQL VARBINARY 类型。
此类型为核心 VARBINARY
类型添加了其他功能,包括“弃用大型类型”模式,在此模式下将呈现 VARBINARY(max)
或 IMAGE,以及 SQL Server FILESTREAM
选项。
另请参阅
大型文本/二进制类型弃用
类签名
类 sqlalchemy.dialects.mssql.VARBINARY
(sqlalchemy.types.VARBINARY
, sqlalchemy.types.LargeBinary
)
method __init__(length=None, filestream=False)
构造一个 VARBINARY 类型。
参数:
length
– 可选,用于 DDL 语句中的列的长度,用于那些接受长度的二进制类型,例如 MySQL BLOB 类型。filestream=False
–
如果为 True,在表定义中渲染FILESTREAM
关键字。在这种情况下,length
必须为None
或'max'
。
新功能,版本 1.4.31。
class sqlalchemy.dialects.mssql.VARCHAR
SQL VARCHAR 类型。
类签名
类 sqlalchemy.dialects.mssql.VARCHAR
(sqlalchemy.types.String
)
method __init__(length: int | None = None, collation: str | None = None)
从 String
的 sqlalchemy.types.String.__init__
方法继承
创建一个字符串持有类型。
参数:
length
– 可选,用于 DDL 和 CAST 表达式中的列长度。如果不会发出CREATE TABLE
,则可以安全地省略。某些数据库可能需要在 DDL 中使用长度,并且如果包含没有长度的VARCHAR
,则在发出CREATE TABLE
DDL 时会引发异常。该值是以字节还是字符解释的取决于数据库。collation
–
可选,用于 DDL 和 CAST 表达式中的列级排序。使用 SQLite、MySQL 和 PostgreSQL 支持的 COLLATE 关键字进行呈现。例如:
>>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
- 注意
在大多数情况下,应该使用Unicode
或UnicodeText
数据类型来存储非 ASCII 数据的Column
。这些数据类型将确保在数据库上使用正确的类型。
class sqlalchemy.dialects.mssql.XML
MSSQL XML 类型。
这是一个用于反射目的的占位符类型,不包括任何 Python 端数据类型支持。它也不支持额外的参数,如“CONTENT”、“DOCUMENT”、“xml_schema_collection”。
成员
init()
类签名
类 sqlalchemy.dialects.mssql.XML
(sqlalchemy.types.Text
)
method __init__(length: int | None = None, collation: str | None = None)
继承自 String
的 sqlalchemy.types.String.__init__
方法
创建一个持有字符串的类型。
参数:
length
– 可选,用于 DDL 和 CAST 表达式中的列长度。如果不会发出CREATE TABLE
,则可以安全地省略。某些数据库可能需要在 DDL 中使用长度,并且如果包含没有长度的VARCHAR
,则在发出CREATE TABLE
DDL 时会引发异常。该值是以字节还是字符解释的取决于数据库。collation
–
可选,用于 DDL 和 CAST 表达式中的列级排序。使用 SQLite、MySQL 和 PostgreSQL 支持的 COLLATE 关键字进行呈现。例如:
>>> from sqlalchemy import cast, select, String >>> print(select(cast('some string', String(collation='utf8')))) SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1
- 注意
在大多数情况下,应该使用Unicode
或UnicodeText
数据类型来存储非 ASCII 数据的Column
。这些数据类型将确保在数据库上使用正确的类型。
PyODBC
通过 PyODBC 驱动程序支持 Microsoft SQL Server 数据库。
DBAPI
PyODBC 的文档和下载信息(如果适用)可在此处找到:pypi.org/project/pyodbc/
连接
连接字符串:
mssql+pyodbc://<username>:<password>@<dsnname>
连接到 PyODBC
此处的 URL 将被翻译为 PyODBC 连接字符串,详细信息请参阅 ConnectionStrings。
DSN 连接
ODBC 中的 DSN 连接意味着在客户端机器上配置了预先存在的 ODBC 数据源。然后,应用程序指定此数据源的名称,其中包括诸如正在使用的特定 ODBC 驱动程序以及数据库的网络地址等详细信息。假设客户端已配置了数据源,则基本的基于 DSN 的连接如下所示:
engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")
以上内容将以下连接字符串传递给 PyODBC:
DSN=some_dsn;UID=scott;PWD=tiger
如果省略了用户名和密码,则 DSN 表单还将向 ODBC 字符串添加 Trusted_Connection=yes
指令。
主机名连接
主机名连接也受到了 pyodbc 的支持。这通常比 DSN 更容易使用,并且具有另一个优势,即可以在 URL 中本地指定要连接的特定数据库名称,而不是作为数据源配置的一部分固定下来。
在使用主机名连接时,驱动程序名称也必须在 URL 的查询参数中指定。由于这些名称通常带有空格,因此名称必须进行 URL 编码,这意味着使用加号代替空格:
engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")
driver
关键字对于 pyodbc 方言很重要,必须以小写形式指定。
在查询字符串中传递的任何其他名称都会在 pyodbc 连接字符串中传递,例如authentication
、TrustServerCertificate
等。多个关键字参数必须用与号(&
)分隔;这些在生成内部 pyodbc 连接字符串时将被翻译为分号:
e = create_engine( "mssql+pyodbc://scott:tiger@mssql2017:1433/test?" "driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes" "&authentication=ActiveDirectoryIntegrated" )
可以使用 URL
构造等效的 URL:
from sqlalchemy.engine import URL connection_url = URL.create( "mssql+pyodbc", username="scott", password="tiger", host="mssql2017", port=1433, database="test", query={ "driver": "ODBC Driver 18 for SQL Server", "TrustServerCertificate": "yes", "authentication": "ActiveDirectoryIntegrated", }, )
通过精确的 Pyodbc 字符串
一个 PyODBC 连接字符串也可以直接以 pyodbc 的格式发送,如 PyODBC 文档 中所述,使用参数 odbc_connect
。一个 URL
对象可以帮助简化此过程:
from sqlalchemy.engine import URL connection_string = "DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password" connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string}) engine = create_engine(connection_url)
使用访问令牌连接数据库
某些数据库服务器只设置为仅接受访问令牌进行登录。例如,SQL Server 允许使用 Azure Active Directory 令牌连接到数据库。这需要使用 azure-identity
库创建凭据对象。有关身份验证步骤的更多信息,请参阅 Microsoft 文档。
获得引擎后,每次请求连接时都需要将凭证发送到pyodbc.connect
。一种方法是在引擎上设置事件监听器,该监听器将凭证令牌添加到方言的连接调用中。更详细地讨论了这一点,可以参考生成动态认证令牌。特别是对于 SQL Server,这是作为一个 ODBC 连接属性传递的,具有由微软描述的数据结构。
以下代码片段将创建一个连接到 Azure SQL 数据库的引擎,使用 Azure 凭据连接:
import struct from sqlalchemy import create_engine, event from sqlalchemy.engine.url import URL from azure import identity SQL_COPT_SS_ACCESS_TOKEN = 1256 # Connection option for access tokens, as defined in msodbcsql.h TOKEN_URL = "https://database.windows.net/" # The token URL for any Azure SQL database connection_string = "mssql+pyodbc://@my-server.database.windows.net/myDb?driver=ODBC+Driver+17+for+SQL+Server" engine = create_engine(connection_string) azure_credentials = identity.DefaultAzureCredential() @event.listens_for(engine, "do_connect") def provide_token(dialect, conn_rec, cargs, cparams): # remove the "Trusted_Connection" parameter that SQLAlchemy adds cargs[0] = cargs[0].replace(";Trusted_Connection=Yes", "") # create token credential raw_token = azure_credentials.get_token(TOKEN_URL).token.encode("utf-16-le") token_struct = struct.pack(f"<I{len(raw_token)}s", len(raw_token), raw_token) # apply it to keyword arguments cparams["attrs_before"] = {SQL_COPT_SS_ACCESS_TOKEN: token_struct}
提示
当没有用户名或密码时,SQLAlchemy pyodbc 方言当前会添加Trusted_Connection
令牌。根据 Microsoft 的使用 Azure 访问令牌文档,这需要删除,该文档指出,使用访问令牌时的连接字符串不得包含UID
、PWD
、Authentication
或Trusted_Connection
参数。#### 避免在 Azure Synapse Analytics 上出现与事务相关的异常
Azure Synapse Analytics 在其事务处理方面与普通的 SQL Server 有显着的差异;在某些情况下,Synapse 事务内的错误可能导致服务器端任意终止,然后导致 DBAPI 的.rollback()
方法(以及.commit()
)失败。这个问题阻止了通常的 DBAPI 合同允许.rollback()
在没有事务存在时悄悄通过,因为驱动程序不期望出现这种情况。这种失败的症状是在尝试在某个操作失败后发出.rollback()
时出现的异常,消息类似于“找不到相应的事务。 (111214)”。
通过以下方式向 SQL Server 方言的create_engine()
函数传递ignore_no_transaction_on_rollback=True
参数,可以处理此特定情况:
engine = create_engine(connection_url, ignore_no_transaction_on_rollback=True)
使用上述参数,方言将捕获在connection.rollback()
期间引发的ProgrammingError
异常,并在错误消息中包含代码111214
时发出警告,但不会引发异常。
在版本 1.4.40 中新增了ignore_no_transaction_on_rollback=True
参数。
为 Azure SQL Data Warehouse (DW) 连接启用自动提交
Azure SQL Data Warehouse 不支持事务,这可能会导致 SQLAlchemy 的“自动开始”(以及隐式提交/回滚)行为出现问题。我们可以通过在 pyodbc 和引擎级别启用自动提交来避免这些问题:
connection_url = sa.engine.URL.create( "mssql+pyodbc", username="scott", password="tiger", host="dw.azure.example.com", database="mydb", query={ "driver": "ODBC Driver 17 for SQL Server", "autocommit": "True", }, ) engine = create_engine(connection_url).execution_options( isolation_level="AUTOCOMMIT" )
避免将大型字符串参数作为 TEXT/NTEXT 发送
出于历史原因,默认情况下,Microsoft 的 SQL Server ODBC 驱动程序将长字符串参数(大于 4000 个 SBCS 字符或 2000 个 Unicode 字符)发送为 TEXT/NTEXT 值。多年来,TEXT 和 NTEXT 已经被弃用,并且开始在新版本的 SQL_Server/Azure 中引起兼容性问题。例如,请参阅此问题。
从 ODBC Driver 18 for SQL Server 开始,我们可以通过使用LongAsMax=Yes
连接字符串参数覆盖传统行为,并将长字符串作为 varchar(max)/nvarchar(max) 传递:
connection_url = sa.engine.URL.create( "mssql+pyodbc", username="scott", password="tiger", host="mssqlserver.example.com", database="mydb", query={ "driver": "ODBC Driver 18 for SQL Server", "LongAsMax": "Yes", }, )
Pyodbc 连接池 / 连接关闭行为
PyODBC 默认使用内部连接池,这意味着连接的生命周期将比在 SQLAlchemy 中更长。由于 SQLAlchemy 有自己的连接池行为,通常最好禁用此行为。此行为只能在创建任何连接之前在 PyODBC 模块级别全局禁用,之前:
import pyodbc pyodbc.pooling = False # don't use the engine before pooling is set to False engine = create_engine("mssql+pyodbc://user:pass@dsn")
如果将此变量保留在其默认值True
,应用程序将继续保持活动数据库连接,即使 SQLAlchemy 引擎本身完全丢弃连接或引擎被处理。
另请参阅
连接池 - 在 PyODBC 文档中。
SqlAlchemy 2.0 中文文档(五十二)(7)https://developer.aliyun.com/article/1563146