从pandas数据帧执行SQL更新语句

Question

上下文：我正在使用MSSQL、panda和pyodbc。

步骤：

• 使用pyodbc从查询中获取数据帧(没有问题(
• 处理列以生成新列(但已存在(的上下文
• 用UPDATE语句(即UPDATE t SET t.value = df.value FROM dbo.table t where t.ID = df.ID(填充辅助列

现在如何执行辅助列中的sql代码，而不遍历每一行？

样本数据

前两列是通过查询dbo.table获得的，第三列在数据库中存在但为空。第四列只存在于数据帧中，用于准备与更新dbo.table相对应的SQL语句

td style="text align=left；">2

ID
1	[e-mail]	lorum ipsum	[e-mail]	rumlo sumip	更新t设置t.processed='rumlo sumip'FROM dbo.table t WHERE t.ID=2
3

在我对该问题的评论中推荐了.executemany()之后，@Charlieface随后的评论表明，表值参数(TVP(将提供更好的性能。我没想到会有那么大的不同，但我错了。

对于名为MillionRows 的现有表

ID  TextField
--  ---------
1  foo
2  bar
3  baz
…

以及形式的示例数据

num_rows = 1_000_000
rows = [(f"text{x:06}", x + 1) for x in range(num_rows)]
print(rows)
# [('text000000', 1), ('text000001', 2), ('text000002', 3), …]

我的测试使用标准executemany()调用cnxn.autocommit = False和crsr.fast_executemany = True

crsr.executemany("UPDATE MillionRows SET TextField = ? WHERE ID = ?", rows)

耗时约180秒(3分钟(。

但是，通过创建用户定义的表类型

CREATE TYPE dbo.TextField_ID AS TABLE 
(
TextField nvarchar(255) NULL, 
ID int NOT NULL, 
PRIMARY KEY (ID)
)

和存储过程

CREATE PROCEDURE [dbo].[mr_update]
@tbl dbo.TextField_ID READONLY
AS
BEGIN
SET NOCOUNT ON;
UPDATE MillionRows SET TextField = t.TextField
FROM MillionRows mr INNER JOIN @tbl t ON mr.ID = t.ID
END

当我使用时

crsr.execute("{CALL mr_update (?)}", (rows,))

它在大约80秒内(不到一半的时间(完成了同样的更新。