SQLAlchemy 2.0 文档
方言
- PostgreSQL
- MySQL 和 MariaDB
- SQLite
- Oracle
- Microsoft SQL Server¶
- 对 Microsoft SQL Server 数据库的支持。
- 外部方言
- 自动递增行为 / IDENTITY 列
- SEQUENCE 支持
- VARCHAR / NVARCHAR 上的 MAX
- 排序规则支持
- LIMIT/OFFSET 支持
- DDL 注释支持
- 事务隔离级别
- 临时表 / 连接池的资源重置
- 可空性
- 日期 / 时间处理
- 大型文本/二进制类型弃用
- 多部分模式名称
- 旧版模式
- 聚集索引支持
- 列存储索引支持
- MSSQL 特定的索引选项
- 兼容级别
- 触发器
- 行计数支持 / ORM 版本控制
- 启用快照隔离
- SQL Server SQL 构造
- SQL Server 数据类型
- PyODBC
- pymssql
- aioodbc
项目版本
- 上一篇: Oracle
- 下一篇: 常见问题解答
- 上一级: 首页
- 本页目录
- Microsoft SQL Server
- 对 Microsoft SQL Server 数据库的支持。
- 外部方言
- 自动递增行为 / IDENTITY 列
- SEQUENCE 支持
- VARCHAR / NVARCHAR 上的 MAX
- 排序规则支持
- LIMIT/OFFSET 支持
- DDL 注释支持
- 事务隔离级别
- 临时表 / 连接池的资源重置
- 可空性
- 日期 / 时间处理
- 大型文本/二进制类型弃用
- 多部分模式名称
- 旧版模式
- 聚集索引支持
- 列存储索引支持
- MSSQL 特定的索引选项
- 兼容级别
- 触发器
- 行计数支持 / ORM 版本控制
- 启用快照隔离
- SQL Server SQL 构造
- SQL Server 数据类型
- PyODBC
- pymssql
- aioodbc
Microsoft SQL Server¶
对 Microsoft SQL Server 数据库的支持。
下表总结了数据库发行版本的当前支持级别。
支持类型 |
版本 |
---|---|
2012+ |
|
2005+ |
DBAPI 支持¶
以下方言/DBAPI 选项可用。请参阅各个 DBAPI 部分以获取连接信息。
外部方言¶
除了上述具有原生 SQLAlchemy 支持的 DBAPI 层之外,还有与 SQL Server 兼容的其他 DBAPI 层的第三方方言。 请参阅方言页面上的 “外部方言” 列表。
自动递增行为 / IDENTITY 列¶
SQL Server 使用 IDENTITY
构造提供所谓的 “自动递增” 行为,该构造可以放置在表中的任何单个整数列上。SQLAlchemy 将 IDENTITY
视为整数主键列的默认 “自动递增” 行为,详见 Column.autoincrement
。 这意味着默认情况下,Table
中的第一个整数主键列将被视为 identity 列 - 除非它与 Sequence
关联 - 并且将生成如下 DDL
from sqlalchemy import Table, MetaData, Column, Integer
m = MetaData()
t = Table(
"t",
m,
Column("id", Integer, primary_key=True),
Column("x", Integer),
)
m.create_all(engine)
上面的例子将生成如下 DDL
CREATE TABLE t (
id INTEGER NOT NULL IDENTITY,
x INTEGER NULL,
PRIMARY KEY (id)
)
如果不需要默认生成 IDENTITY
,请为第一个整数主键列的 Column.autoincrement
标志指定 False
m = MetaData()
t = Table(
"t",
m,
Column("id", Integer, primary_key=True, autoincrement=False),
Column("x", Integer),
)
m.create_all(engine)
要将 IDENTITY
关键字添加到非主键列,请为所需的 Column.autoincrement
标志指定 True
,并确保在任何整数主键列上将 Column.autoincrement
设置为 False
m = MetaData()
t = Table(
"t",
m,
Column("id", Integer, primary_key=True, autoincrement=False),
Column("x", Integer, autoincrement=True),
)
m.create_all(engine)
版本 1.4 中变更: 在 Column
中添加了 Identity
构造,以指定 IDENTITY 的 start 和 increment 参数。这些参数取代了使用 Sequence
对象来指定这些值。
版本 1.4 中已弃用: Column
的 mssql_identity_start
和 mssql_identity_increment
参数已弃用,应替换为 Identity
对象。 以两种方式配置 IDENTITY 将导致编译错误。 这些选项也不再作为 Inspector.get_columns()
中 dialect_options
键的一部分返回。 请改用 identity
键中的信息。
版本 1.3 中已弃用: 使用 Sequence
指定 IDENTITY 特性已弃用,将在未来的版本中删除。 请使用 Identity
对象参数 Identity.start
和 Identity.increment
。
版本 1.4 中变更: 移除了使用 Sequence
对象修改 IDENTITY 特性的功能。 从 SQLAlchemy 版本 1.4 开始,Sequence
对象现在仅操作真正的 T-SQL SEQUENCE 类型。
注意
表中只能有一个 IDENTITY 列。当使用 autoincrement=True
启用 IDENTITY 关键字时,SQLAlchemy 不会阻止多个列同时指定该选项。SQL Server 数据库会拒绝 CREATE TABLE
语句。
注意
尝试为标记为 IDENTITY 的列提供值的 INSERT 语句将被 SQL Server 拒绝。为了使该值被接受,必须启用会话级选项 “SET IDENTITY_INSERT”。 当使用核心 Insert
构造时,SQLAlchemy SQL Server 方言将自动执行此操作;如果执行为 IDENTITY 列指定了值,则将在该语句调用期间启用 “IDENTITY_INSERT” 选项。 但是,这种情况的性能不高,不应在正常使用中依赖。 如果表实际上不需要其整数主键列中的 IDENTITY 行为,则应在创建表时禁用该关键字,方法是确保设置 autoincrement=False
。
控制 “Start” 和 “Increment”¶
可以使用传递给 Identity
对象的 Identity.start
和 Identity.increment
参数,对 IDENTITY
生成器的 “start” 和 “increment” 值进行特定控制
from sqlalchemy import Table, Integer, Column, Identity
test = Table(
"test",
metadata,
Column(
"id", Integer, primary_key=True, Identity(start=100, increment=10)
),
Column("name", String(20)),
)
上述 Table
对象的 CREATE TABLE 将是
CREATE TABLE test (
id INTEGER NOT NULL IDENTITY(100,10) PRIMARY KEY,
name VARCHAR(20) NULL,
)
注意
Identity
对象除了 start
和 increment
之外,还支持许多其他参数。SQL Server 不支持这些参数,并且在生成 CREATE TABLE ddl 时将被忽略。
将 IDENTITY 与非整数数值类型一起使用¶
SQL Server 还允许将 IDENTITY
与 NUMERIC
列一起使用。 为了在 SQLAlchemy 中平滑地实现此模式,列的主要数据类型应保持为 Integer
,但是可以使用 TypeEngine.with_variant()
将部署到 SQL Server 数据库的底层实现类型指定为 Numeric
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Numeric
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class TestTable(Base):
__tablename__ = "test"
id = Column(
Integer().with_variant(Numeric(10, 0), "mssql"),
primary_key=True,
autoincrement=True,
)
name = Column(String)
在上面的示例中,Integer().with_variant()
提供了清晰的用法信息,准确地描述了代码的意图。 autoincrement
仅适用于 Integer
的一般限制是在元数据级别而不是在每个方言级别建立的。
当使用上述模式时,从插入行返回的主键标识符(也是将分配给 ORM 对象(例如上面的 TestTable
)的值)将是 Decimal()
的实例,而不是在使用 SQL Server 时的 int
。 可以通过将 False 传递给 Numeric.asdecimal
来更改 Numeric
类型的数值返回类型以返回浮点数。 要将上述 Numeric(10, 0)
的返回类型规范化为返回 Python 整数(也支持 Python 3 中的 “long” 整数值),请按如下方式使用 TypeDecorator
from sqlalchemy import TypeDecorator
class NumericAsInteger(TypeDecorator):
"normalize floating point return values into ints"
impl = Numeric(10, 0, asdecimal=False)
cache_ok = True
def process_result_value(self, value, dialect):
if value is not None:
value = int(value)
return value
class TestTable(Base):
__tablename__ = "test"
id = Column(
Integer().with_variant(NumericAsInteger, "mssql"),
primary_key=True,
autoincrement=True,
)
name = Column(String)
INSERT 行为¶
INSERT 时 IDENTITY
列的处理涉及两个关键技术。 最常见的是能够获取给定 IDENTITY
列的 “最后插入的值”,SQLAlchemy 在许多情况下隐式执行此过程,最重要的是在 ORM 中。
获取此值的过程有几种变体
在绝大多数情况下,RETURNING 与 SQL Server 上的 INSERT 语句结合使用,以便获取新生成的主键值
INSERT INTO t (x) OUTPUT inserted.id VALUES (?)
从 SQLAlchemy 2.0 开始,INSERT 语句的 “插入多个值” 行为 功能也默认用于优化多行 INSERT 语句;对于 SQL Server,该功能适用于 RETURNING 和非 RETURNING INSERT 语句。
版本 2.0.10 中变更: 由于行排序问题,SQL Server 的 INSERT 语句的 “插入多个值” 行为 功能已在 SQLAlchemy 版本 2.0.9 中临时禁用。 从 2.0.10 开始,该功能已重新启用,并针对工作单元对 RETURNING 排序的要求进行了特殊情况处理。
当 RETURNING 不可用或已通过
implicit_returning=False
禁用时,将使用scope_identity()
函数或@@identity
变量;行为因后端而异当使用 PyODBC 时,短语
; select scope_identity()
将附加到 INSERT 语句的末尾;将获取第二个结果集以接收该值。 给定一个表,例如t = Table( "t", metadata, Column("id", Integer, primary_key=True), Column("x", Integer), implicit_returning=False, )
INSERT 将如下所示
INSERT INTO t (x) VALUES (?); select scope_identity()
其他方言(例如 pymssql)将在 INSERT 语句之后调用
SELECT scope_identity() AS lastrowid
。 如果将标志use_scope_identity=False
传递给create_engine()
,则会改用语句SELECT @@identity AS lastrowid
。
包含 IDENTITY
列的表将禁止显式引用 identity 列的 INSERT 语句。 SQLAlchemy 方言将检测到使用核心 insert()
构造(而不是纯字符串 SQL)创建的 INSERT 构造引用了 identity 列,在这种情况下,将在 insert 语句执行之前发出 SET IDENTITY_INSERT ON
,并在执行之后发出 SET IDENTITY_INSERT OFF
。 考虑以下示例
m = MetaData()
t = Table(
"t", m, Column("id", Integer, primary_key=True), Column("x", Integer)
)
m.create_all(engine)
with engine.begin() as conn:
conn.execute(t.insert(), {"id": 1, "x": 1}, {"id": 2, "x": 2})
上面的列将使用 IDENTITY 创建,但是我们发出的 INSERT 语句指定了显式值。 在回显输出中,我们可以看到 SQLAlchemy 如何处理这种情况
CREATE TABLE t (
id INTEGER NOT NULL IDENTITY(1,1),
x INTEGER NULL,
PRIMARY KEY (id)
)
COMMIT
SET IDENTITY_INSERT t ON
INSERT INTO t (id, x) VALUES (?, ?)
((1, 1), (2, 2))
SET IDENTITY_INSERT t OFF
COMMIT
这是一个适用于测试和批量插入场景的辅助用例。
SEQUENCE 支持¶
Sequence
对象创建 “真实” 序列,即 CREATE SEQUENCE
>>> from sqlalchemy import Sequence
>>> from sqlalchemy.schema import CreateSequence
>>> from sqlalchemy.dialects import mssql
>>> print(
... CreateSequence(Sequence("my_seq", start=1)).compile(
... dialect=mssql.dialect()
... )
... )
CREATE SEQUENCE my_seq START WITH 1
对于整数主键生成,通常应首选 SQL Server 的 IDENTITY
构造,而不是序列。
提示
T-SQL 的默认起始值是 -2**63
,而不是大多数其他 SQL 数据库中的 1。 用户应显式将 Sequence.start
设置为 1(如果这是预期的默认值)
seq = Sequence("my_sequence", start=1)
版本 1.4 中新增: 添加了 SQL Server 对 Sequence
的支持
版本 2.0 中变更: SQL Server 方言将不再为 CREATE SEQUENCE
隐式渲染 “START WITH 1”,这是版本 1.4 中首次实现的行为。
VARCHAR / NVARCHAR 上的 MAX¶
SQL Server 在 VARCHAR
和 NVARCHAR
数据类型中支持特殊字符串 “MAX”,以指示 “最大可能长度”。 方言当前将其作为基本类型中的 “None” 长度处理,而不是提供这些类型的特定于方言的版本,以便诸如 VARCHAR(None)
之类的基本类型可以在多个后端上采用 “无长度” 行为,而无需使用特定于方言的类型。
要构建具有 MAX 长度的 SQL Server VARCHAR 或 NVARCHAR,请使用 None
my_table = Table(
"my_table",
metadata,
Column("my_data", VARCHAR(None)),
Column("my_n_data", NVARCHAR(None)),
)
排序规则支持¶
基本字符串类型支持字符排序规则,由字符串参数 “collation” 指定
from sqlalchemy import VARCHAR
Column("login", VARCHAR(32, collation="Latin1_General_CI_AS"))
当此类列与 Table
关联时,此列的 CREATE TABLE 语句将产生
login VARCHAR(32) COLLATE Latin1_General_CI_AS NULL
LIMIT/OFFSET 支持¶
从 SQL Server 2012 开始,MSSQL 已通过 “OFFSET n ROWS” 和 “FETCH NEXT n ROWS” 子句添加了对 LIMIT / OFFSET 的支持。 如果检测到 SQL Server 2012 或更高版本,SQLAlchemy 会自动支持这些语法。
版本 1.4 中变更: 添加了对 SQL Server “OFFSET n ROWS” 和 “FETCH NEXT n ROWS” 语法的支持。
对于仅指定 LIMIT 而不指定 OFFSET 的语句,所有版本的 SQL Server 都支持 TOP 关键字。 当不存在 OFFSET 子句时,所有 SQL Server 版本都使用此语法。 诸如以下的语句
select(some_table).limit(5)
将呈现类似于
SELECT TOP 5 col1, col2.. FROM table
对于 SQL Server 2012 之前的 SQL Server 版本,使用 LIMIT 和 OFFSET 或仅使用 OFFSET 的语句将使用 ROW_NUMBER()
窗口函数呈现。 诸如以下的语句
select(some_table).order_by(some_table.c.col3).limit(5).offset(10)
将呈现类似于
SELECT anon_1.col1, anon_1.col2 FROM (SELECT col1, col2,
ROW_NUMBER() OVER (ORDER BY col3) AS
mssql_rn FROM table WHERE t.x = :x_1) AS
anon_1 WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1
请注意,当使用 LIMIT 和/或 OFFSET 时,无论是使用旧的还是新的 SQL Server 语法,该语句都必须同时具有 ORDER BY,否则会引发 CompileError
。
DDL 注释支持¶
假设使用了受支持的 SQL Server 版本,则支持注释支持,其中包括 Table.comment
和 Column.comment
等属性的 DDL 渲染,以及反映这些注释的能力。 如果在首次连接时检测到不受支持的版本(例如 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 <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 自动提交 中的讨论。
临时表 / 连接池的资源重置¶
SQLAlchemy Engine
对象使用的 QueuePool
连接池实现包括 返回时重置 行为,该行为将在连接返回到池时调用 DBAPI .rollback()
方法。 虽然此回滚将清除先前事务使用的直接状态,但它不涵盖更广泛的会话级状态,包括临时表以及其他服务器状态,例如预处理语句句柄和语句缓存。 众所周知,未记录的 SQL Server 过程 sp_reset_connection
是解决此问题的解决方法,它将重置连接上累积的大多数会话状态,包括临时表。
要安装 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^5HHH@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 的服务器版本,则这些类型的 DDL 将作为 DATETIME 发出。
大型文本/二进制类型弃用¶
根据 SQL Server 2012/2014 文档,NTEXT
、TEXT
和 IMAGE
数据类型将在未来的 SQL Server 版本中移除。SQLAlchemy 通常将这些类型关联到 UnicodeText
、TextClause
和 LargeBinary
数据类型。
为了适应此更改,方言中添加了一个新的标志 deprecate_large_types
,如果没有用户另行设置,该标志将根据对所用服务器版本的检测自动设置。此标志的行为如下:
当此标志为
True
时,UnicodeText
、TextClause
和LargeBinary
数据类型在用于渲染 DDL 时,将分别渲染为NVARCHAR(max)
、VARCHAR(max)
和VARBINARY(max)
类型。这是添加此标志后的新行为。当此标志为
False
时,UnicodeText
、TextClause
和LargeBinary
数据类型在用于渲染 DDL 时,将分别渲染为NTEXT
、TEXT
和IMAGE
类型。这是这些类型的长期行为。在建立数据库连接之前,该标志的初始值为
None
。如果方言用于渲染 DDL 而未设置该标志,则其解释方式与False
相同。首次连接时,方言会检测是否正在使用 SQL Server 2012 或更高版本;如果该标志仍为
None
,则会根据是否检测到 2012 或更高版本将其设置为True
或False
。创建方言时,通常通过
create_engine()
,可以将该标志设置为True
或False
。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
参数一次性设置这些多部分名称。
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
这种行为模式现在默认关闭,因为它似乎没有任何用途;但是,如果旧版应用程序依赖于它,则可以使用 create_engine()
的 legacy_schema_aliasing
参数来启用它,如上所示。
版本 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
的其他特殊选项。
INCLUDE¶
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。要在每个表的基础上禁用 OUTPUT INSERTED 的使用,请为每个具有触发器的 Table
指定 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 现在基于计算 RETURNING 中返回的行数,为这些特定用例手动检索 “rowcount”;因此,虽然驱动程序仍然存在此限制,但 ORM 版本控制功能不再受其影响。从 SQLAlchemy 2.0.5 开始,ORM 版本控制已为 pyodbc 驱动程序完全重新启用。
在版本 2.0.5 中更改: ORM 版本控制支持已为 pyodbc 驱动程序恢复。以前,在 ORM 刷新期间会发出警告,表明不支持版本控制。
启用快照隔离¶
SQL Server 具有默认事务隔离模式,该模式锁定整个表,并导致即使是轻微并发的应用程序也会长时间持有锁并频繁死锁。建议为整个数据库启用快照隔离,以获得现代级别的并发支持。这可以通过在 SQL 提示符下执行以下 ALTER DATABASE 命令来完成:
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
有关 SQL Server 快照隔离的背景信息,请访问 https://msdn.microsoft.com/en-us/library/ms175095.aspx。
SQL Server SQL 构造¶
对象名称 | 描述 |
---|---|
try_cast(expression, type_) |
为支持它的后端生成 |
- 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 方言推广为通用构造,可能受其他方言支持。
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 特定的构造参数的类型如下:
对象名称 | 描述 |
---|---|
MSSQL BIT 类型。 |
|
SQL Server DOUBLE PRECISION 数据类型。 |
|
MSSQL JSON 类型。 |
|
MSSQL NTEXT 类型,用于最大长度为 2^30 个字符的可变长度 Unicode 文本。 |
|
SQL Server REAL 数据类型。 |
|
实现 SQL Server ROWVERSION 类型。 |
|
实现 SQL Server TIMESTAMP 类型。 |
|
MSSQL XML 类型。 |
- class sqlalchemy.dialects.mssql.BIT¶
MSSQL BIT 类型。
pyodbc 和 pymssql 都从 BIT 列返回 Python <class ‘bool’> 类型的值,因此只需子类化 Boolean。
成员
-
method
sqlalchemy.dialects.mssql.BIT.
__init__(create_constraint: bool = False, name: str | None = None, _create_events: bool = True, _adapted_from: SchemaType | None = None)¶ 继承自
sqlalchemy.types.Boolean.__init__
的Boolean
方法构造一个 Boolean 类型。
- 参数:
create_constraint¶ –
默认为 False。如果 boolean 类型生成为 int/smallint,则还在表上创建一个 CHECK 约束,以确保值为 1 或 0。
注意
强烈建议 CHECK 约束具有显式名称,以便支持架构管理问题。这可以通过设置
Boolean.name
参数或设置适当的命名约定来建立;有关背景信息,请参阅 配置约束命名约定。在版本 1.4 中更改: - 此标志现在默认为 False,这意味着不会为非原生枚举类型生成 CHECK 约束。
name¶ – 如果生成 CHECK 约束,则指定约束的名称。
-
method
- class sqlalchemy.dialects.mssql.CHAR
SQL CHAR 类型。
类签名
class
sqlalchemy.dialects.mssql.CHAR
(sqlalchemy.types.String
)-
method
sqlalchemy.dialects.mssql.CHAR.
__init__(length: int | None = None, collation: str | None = None) 继承自
sqlalchemy.types.String.__init__
的String
方法创建字符串类型的持有器。
- 参数:
length¶ – 可选,列的长度,用于 DDL 和 CAST 表达式。如果不会发出
CREATE TABLE
,则可以安全地省略。某些数据库可能需要length
用于 DDL,并且如果在发出的CREATE TABLE
DDL 中包含没有长度的VARCHAR
,则会引发异常。该值被解释为字节还是字符取决于数据库。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
。这些数据类型将确保在数据库上使用正确的类型。
-
method
- class sqlalchemy.dialects.mssql.DATETIME2¶
类签名
class
sqlalchemy.dialects.mssql.DATETIME2
(sqlalchemy.dialects.mssql.base._DateTimeBase
,sqlalchemy.types.DateTime
)
- class sqlalchemy.dialects.mssql.DATETIMEOFFSET¶
类签名
class
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 中的新功能。
- class sqlalchemy.dialects.mssql.IMAGE¶
成员
-
method
sqlalchemy.dialects.mssql.IMAGE.
__init__(length: int | None = None)¶ 继承自
sqlalchemy.types.LargeBinary.__init__
的LargeBinary
方法构造一个 LargeBinary 类型。
- 参数:
length¶ – 可选,列的长度,用于 DDL 语句,对于接受长度的二进制类型,例如 MySQL BLOB 类型。
-
method
- class sqlalchemy.dialects.mssql.JSON¶
MSSQL JSON 类型。
MSSQL 从 SQL Server 2016 开始支持 JSON 格式的数据。
JSON
数据类型在 DDL 级别将数据类型表示为NVARCHAR(max)
,但提供 JSON 级别的比较函数以及 Python 强制行为。每当基础
JSON
数据类型用于 SQL Server 后端时,会自动使用JSON
。另请参阅
JSON
- 通用跨平台 JSON 数据类型的主要文档。JSON
类型支持 JSON 值的持久化以及JSON
数据类型提供的核心索引操作,通过调整操作以在数据库级别渲染JSON_VALUE
或JSON_QUERY
函数。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 版本新增。
成员
类签名
class
sqlalchemy.dialects.mssql.JSON
(sqlalchemy.types.JSON
)-
method
sqlalchemy.dialects.mssql.JSON.
__init__(none_as_null: bool = False)¶ 继承自
sqlalchemy.types.JSON.__init__
方法,来自JSON
构造一个
JSON
类型。- 参数:
none_as_null=False¶ –
如果为 True,则将值
None
持久化为 SQL NULL 值,而不是null
的 JSON 编码。请注意,当此标志为 False 时,null()
构造仍然可以用于持久化 NULL 值,该值可以作为参数值直接传递,并由JSON
类型特别解释为 SQL NULLfrom sqlalchemy import null conn.execute(table.insert(), {"data": null()})
注意
JSON.none_as_null
不 适用于传递给Column.default
和Column.server_default
的值;对于这些参数,None
值表示“没有默认值”。此外,当在 SQL 比较表达式中使用时,Python 值
None
继续引用 SQL null,而不是 JSON NULL。JSON.none_as_null
标志明确指的是在 INSERT 或 UPDATE 语句中值的持久化。JSON.NULL
值应用于希望与 JSON null 进行比较的 SQL 表达式。另请参阅
- class sqlalchemy.dialects.mssql.MONEY¶
- class sqlalchemy.dialects.mssql.NCHAR
SQL NCHAR 类型。
类签名
class
sqlalchemy.dialects.mssql.NCHAR
(sqlalchemy.types.Unicode
)-
method
sqlalchemy.dialects.mssql.NCHAR.
__init__(length: int | None = None, collation: str | None = None) 继承自
sqlalchemy.types.String.__init__
的String
方法创建字符串类型的持有器。
- 参数:
length¶ – 可选,列的长度,用于 DDL 和 CAST 表达式。如果不会发出
CREATE TABLE
,则可以安全地省略。某些数据库可能需要length
以用于 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
。这些数据类型将确保在数据库上使用正确的类型。
-
method
- class sqlalchemy.dialects.mssql.NTEXT¶
MSSQL NTEXT 类型,用于最大长度为 2^30 个字符的可变长度 Unicode 文本。
成员
-
method
sqlalchemy.dialects.mssql.NTEXT.
__init__(length: int | None = None, collation: str | None = None)¶ 继承自
sqlalchemy.types.String.__init__
的String
方法创建字符串类型的持有器。
- 参数:
length¶ – 可选,列的长度,用于 DDL 和 CAST 表达式。如果不会发出
CREATE TABLE
,则可以安全地省略。某些数据库可能需要length
以用于 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
。这些数据类型将确保在数据库上使用正确的类型。
-
method
- class sqlalchemy.dialects.mssql.NVARCHAR
SQL NVARCHAR 类型。
类签名
class
sqlalchemy.dialects.mssql.NVARCHAR
(sqlalchemy.types.Unicode
)-
method
sqlalchemy.dialects.mssql.NVARCHAR.
__init__(length: int | None = None, collation: str | None = None) 继承自
sqlalchemy.types.String.__init__
的String
方法创建字符串类型的持有器。
- 参数:
length¶ – 可选,列的长度,用于 DDL 和 CAST 表达式。如果不会发出
CREATE TABLE
,则可以安全地省略。某些数据库可能需要length
以用于 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
。这些数据类型将确保在数据库上使用正确的类型。
-
method
- class sqlalchemy.dialects.mssql.REAL¶
SQL Server REAL 数据类型。
类签名
class
sqlalchemy.dialects.mssql.REAL
(sqlalchemy.types.REAL
)
- class sqlalchemy.dialects.mssql.ROWVERSION¶
实现 SQL Server ROWVERSION 类型。
ROWVERSION 数据类型是 SQL Server 中 TIMESTAMP 数据类型的同义词,但当前的 SQL Server 文档建议在新数据类型中使用 ROWVERSION。
ROWVERSION 数据类型不会从数据库中反映(例如,内省)自身;返回的数据类型将是
TIMESTAMP
。这是一个只读数据类型,不支持 INSERT 值。
1.2 版本新增。
另请参阅
成员
-
method
sqlalchemy.dialects.mssql.ROWVERSION.
__init__(convert_int=False)¶ 继承自
sqlalchemy.dialects.mssql.base.TIMESTAMP.__init__
方法,来自TIMESTAMP
构造一个 TIMESTAMP 或 ROWVERSION 类型。
- 参数:
convert_int¶ – 如果为 True,则二进制整数值将在读取时转换为整数。
1.2 版本新增。
-
method
- class sqlalchemy.dialects.mssql.SMALLDATETIME¶
成员
类签名
class
sqlalchemy.dialects.mssql.SMALLDATETIME
(sqlalchemy.dialects.mssql.base._DateTimeBase
,sqlalchemy.types.DateTime
)-
method
sqlalchemy.dialects.mssql.SMALLDATETIME.
__init__(timezone: bool = False)¶ 继承自
sqlalchemy.types.DateTime.__init__
方法,来自DateTime
构造一个新的
DateTime
。
-
method
- class sqlalchemy.dialects.mssql.SMALLMONEY¶
- class sqlalchemy.dialects.mssql.SQL_VARIANT¶
- class sqlalchemy.dialects.mssql.TEXT
SQL TEXT 类型。
类签名
class
sqlalchemy.dialects.mssql.TEXT
(sqlalchemy.types.Text
)-
method
sqlalchemy.dialects.mssql.TEXT.
__init__(length: int | None = None, collation: str | None = None) 继承自
sqlalchemy.types.String.__init__
的String
方法创建字符串类型的持有器。
- 参数:
length¶ – 可选,列的长度,用于 DDL 和 CAST 表达式。如果不会发出
CREATE TABLE
,则可以安全地省略。某些数据库可能需要length
以用于 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
。这些数据类型将确保在数据库上使用正确的类型。
-
method
- class sqlalchemy.dialects.mssql.TIME¶
类签名
class
sqlalchemy.dialects.mssql.TIME
(sqlalchemy.types.TIME
)
- class sqlalchemy.dialects.mssql.TIMESTAMP¶
实现 SQL Server TIMESTAMP 类型。
请注意,这与 SQL 标准 TIMESTAMP 类型完全不同,SQL Server 不支持 SQL 标准 TIMESTAMP 类型。这是一个只读数据类型,不支持 INSERT 值。
1.2 版本新增。
另请参阅
成员
类签名
class
sqlalchemy.dialects.mssql.TIMESTAMP
(sqlalchemy.types._Binary
)-
method
sqlalchemy.dialects.mssql.TIMESTAMP.
__init__(convert_int=False)¶ 构造一个 TIMESTAMP 或 ROWVERSION 类型。
- 参数:
convert_int¶ – 如果为 True,则二进制整数值将在读取时转换为整数。
1.2 版本新增。
-
method
- class sqlalchemy.dialects.mssql.TINYINT¶
- class sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER¶
成员
-
method
sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER.
__init__(as_uuid: bool = True)¶ 构造一个
UNIQUEIDENTIFIER
类型。- 参数:
as_uuid=True¶ –
如果为 True,值将被解释为 Python uuid 对象,并通过 DBAPI 转换为/从字符串转换。
-
method
- class sqlalchemy.dialects.mssql.VARBINARY
MSSQL VARBINARY 类型。
此类型为核心
VARBINARY
类型添加了额外的功能,包括 “deprecate_large_types” 模式,在该模式下,会渲染VARBINARY(max)
或 IMAGE,以及 SQL ServerFILESTREAM
选项。另请参阅
类签名
class
sqlalchemy.dialects.mssql.VARBINARY
(sqlalchemy.types.VARBINARY
,sqlalchemy.types.LargeBinary
)-
method
sqlalchemy.dialects.mssql.VARBINARY.
__init__(length=None, filestream=False) 构造一个 VARBINARY 类型。
-
method
- class sqlalchemy.dialects.mssql.VARCHAR
SQL VARCHAR 类型。
类签名
class
sqlalchemy.dialects.mssql.VARCHAR
(sqlalchemy.types.String
)-
method
sqlalchemy.dialects.mssql.VARCHAR.
__init__(length: int | None = None, collation: str | None = None) 继承自
sqlalchemy.types.String.__init__
的String
方法创建字符串类型的持有器。
- 参数:
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
。这些数据类型将确保在数据库上使用正确的类型。
-
method
- class sqlalchemy.dialects.mssql.XML¶
MSSQL XML 类型。
这是一个用于反射目的的占位符类型,不包含任何 Python 端的 datatype 支持。它目前也不支持额外的参数,例如 “CONTENT”、“DOCUMENT”、“xml_schema_collection”。
成员
类签名
-
method
sqlalchemy.dialects.mssql.XML.
__init__(length: int | None = None, collation: str | None = None)¶ 继承自
sqlalchemy.types.String.__init__
的String
方法创建字符串类型的持有器。
- 参数:
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
。这些数据类型将确保在数据库上使用正确的类型。
-
method
PyODBC¶
通过 PyODBC 驱动程序支持 Microsoft SQL Server 数据库。
DBAPI¶
PyODBC 的文档和下载信息(如果适用)可在以下网址获取:https://pypi.ac.cn/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 连接属性传递,其数据结构由 Microsoft 描述。
以下代码片段将创建一个使用 Azure 凭据连接到 Azure SQL 数据库的引擎。
import struct
from sqlalchemy import create_engine, event
from sqlalchemy.engine.url import URL
from azure import identity
# Connection option for access tokens, as defined in msodbcsql.h
SQL_COPT_SS_ACCESS_TOKEN = 1256
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()
时,出现消息类似于 “No corresponding transaction found. (111214)” 的异常。
可以通过将 ignore_no_transaction_on_rollback=True
传递给 SQL Server 方言,通过 create_engine()
函数来处理此特定情况,如下所示:
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 数据仓库 (DW) 连接启用自动提交¶
Azure SQL 数据仓库不支持事务,这可能会导致 SQLAlchemy 的 “autobegin”(和隐式提交/回滚)行为出现问题。我们可以通过在 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 的兼容性问题。例如,请参阅 此问题。
从 SQL Server 的 ODBC Driver 18 开始,我们可以覆盖旧版行为,并使用 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 文档中。
驱动程序 / Unicode 支持¶
PyODBC 与 Microsoft ODBC 驱动程序配合使用效果最佳,尤其是在 Python 2 和 Python 3 上的 Unicode 支持方面。
不 建议在 Linux 或 OSX 上将 FreeTDS ODBC 驱动程序与 PyODBC 一起使用;历史上,该领域存在许多与 Unicode 相关的问题,包括在 Microsoft 为 Linux 和 OSX 提供 ODBC 驱动程序之前。既然 Microsoft 为所有平台提供了驱动程序,为了 PyODBC 支持,建议使用这些驱动程序。FreeTDS 对于非 ODBC 驱动程序(如 pymssql)仍然很有用,在这些驱动程序中,FreeTDS 运行良好。
RowCount 支持¶
SQLAlchemy ORM 的 “版本化行” 功能与 Pyodbc 之前的限制已在 SQLAlchemy 2.0.5 中得到解决。请参阅 RowCount 支持 / ORM 版本控制 中的说明。
快速 executemany 模式¶
PyODBC 驱动程序包括对 “快速 executemany” 执行模式的支持,当使用 Microsoft ODBC 驱动程序时,对于 DBAPI executemany()
调用,此模式大大减少了往返次数,适用于内存中有限大小的批处理。通过在使用 executemany 调用时在 DBAPI 游标上设置属性 .fast_executemany
来启用此功能。SQLAlchemy PyODBC SQL Server 方言通过将 fast_executemany
参数传递给 create_engine()
来支持此参数,仅在使用 Microsoft ODBC 驱动程序时。
engine = create_engine(
"mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Driver+17+for+SQL+Server",
fast_executemany=True,
)
在版本 2.0.9 中更改: - fast_executemany
参数现在具有其预期效果,即此 PyODBC 功能对于所有使用多个参数集执行的 INSERT 语句生效,这些语句不包括 RETURNING。以前,即使指定了 fast_executemany
,SQLAlchemy 2.0 的 insertmanyvalues 功能也会导致在大多数情况下不使用 fast_executemany
。
版本 1.3 新增。
另请参阅
fast executemany - 在 github 上
Setinputsizes 支持¶
从版本 2.0 开始,pyodbc cursor.setinputsizes()
方法用于所有语句执行,除了 cursor.executemany()
调用在 fast_executemany=True 时不支持的情况(假设 insertmanyvalues 保持启用状态,“fastexecutemany” 在任何情况下都不会对 INSERT 语句生效)。
可以通过将 use_setinputsizes=False
传递给 create_engine()
来禁用 cursor.setinputsizes()
的使用。
当 use_setinputsizes
保留其默认值 True
时,可以使用 DialectEvents.do_setinputsizes()
hook 以编程方式自定义传递给 cursor.setinputsizes()
的特定于类型的符号。 有关用法示例,请参阅该方法。
在版本 2.0 中更改: mssql+pyodbc 方言现在默认对所有语句执行使用 use_setinputsizes=True
,但 cursor.executemany() 调用在 fast_executemany=True 时除外。可以通过将 use_setinputsizes=False
传递给 create_engine()
来关闭此行为。
pymssql¶
通过 pymssql 驱动程序支持 Microsoft SQL Server 数据库。
连接¶
连接字符串
mssql+pymssql://<username>:<password>@<freetds_name>/?charset=utf8
pymssql 是一个 Python 模块,它围绕 FreeTDS 提供了 Python DBAPI 接口。
在版本 2.0.5 中更改: pymssql 已恢复到 SQLAlchemy 的持续集成测试中
aioodbc¶
通过 aioodbc 驱动程序支持 Microsoft SQL Server 数据库。
DBAPI¶
aioodbc 的文档和下载信息(如果适用)可在以下网址获取:https://pypi.ac.cn/project/aioodbc/
连接¶
连接字符串
mssql+aioodbc://<username>:<password>@<dsnname>
使用 aioodbc 驱动程序(本身是 pyodbc 的线程包装器)以 asyncio 样式支持 SQL Server 数据库。
版本 2.0.23 新增: 添加了 mssql+aioodbc 方言,该方言构建于 pyodbc 和通用 aio* 方言架构之上。
通过使用特殊的 asyncio 调解层,aioodbc 方言可用作 SQLAlchemy asyncio 扩展包的后端。
此驱动程序的大多数行为和注意事项与 SQL Server 上使用的 pyodbc 方言相同;有关一般背景信息,请参阅 PyODBC。
此方言通常应仅与 create_async_engine()
引擎创建函数一起使用;连接样式与其他在 pyodbc 部分中记录的样式等效。
from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine(
"mssql+aioodbc://scott:tiger@mssql2017:1433/test?"
"driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes"
)
flambé! 龙和 The Alchemist 图像设计由 Rotem Yaari 创作并慷慨捐赠。
使用 Sphinx 7.2.6 创建。文档上次生成时间:Tue 11 Mar 2025 02:40:17 PM EDT