如何用Python以参数化方式向SQL Server批量处理元组列表

问题分析 #

你原代码无法运行的核心原因有三点：

1. SQL Server未定义对应结构的用户定义表类型，直接传Python列表无法被识别
2. 参数传递格式错误，pymssql处理表值参数需要特定的传递方式
3. 代码中存在变量名错误（定义了sql却用query调用execute）

解决步骤 #

第一步：在SQL Server端准备表类型和存储过程 #

首先需要创建与你的元组结构匹配的用户定义表类型，再修改存储过程接收该类型参数：

-- 创建用户定义表类型（字段类型、数量需和Python元组完全对应）
CREATE TYPE [dbo].[MyTableType] AS TABLE(
    Col1 VARCHAR(50),
    Col2 VARCHAR(50),
    Col3 VARCHAR(50)
)
GO

-- 修改存储过程，使用表类型作为参数
CREATE PROCEDURE [dbo].[mystrored_proc]
    @table [dbo].[MyTableType] READONLY
AS
BEGIN
    -- 示例：将传入的表数据插入到目标表
    INSERT INTO YourTargetTable (Col1, Col2, Col3)
    SELECT Col1, Col2, Col3 FROM @table
END
GO

第二步：Python端正确实现批量参数化调用 #

修正后的Python代码如下：

import pymssql

# 待处理的元组列表
lt = [('a','temp_a','001'),('b','temp_b','002')]

# 数据库连接配置
server = "你的服务器地址"
user = "用户名"
password = "密码"
database = "目标数据库"

# 建立连接并执行
conn = pymssql.connect(server=server, user=user, password=password, database=database)
cursor = conn.cursor()

# 调用存储过程，将元组列表作为单个参数传入
sql = "EXEC [dbo].[mystrored_proc] @table = %s"
cursor.execute(sql, (lt,))

# 提交事务（写操作必须提交才能生效）
conn.commit()

# 关闭资源
cursor.close()
conn.close()

关键注意事项 #

• 确保用户定义表类型的字段顺序、数据类型与Python元组完全一致，否则会出现类型不匹配错误
• 如果存储过程需要其他参数，可以在SQL语句中追加，比如EXEC [dbo].[mystrored_proc] @table = %s, @other_param = %s，并在execute的参数元组中对应添加
• 若涉及查询操作，无需commit；但写操作必须执行conn.commit()才能将数据持久化到数据库

内容的提问来源于stack exchange，提问作者Sekhar