使用Pyodbc向SQL自定义类型表批量插入数据报错求助

报错原因分析 #

第一个报错：参数数量不匹配 #

ProgrammingError: ('The SQL contains 3 parameter markers, but 7 parameters were supplied', 'HY000')
问题出在curs.execute(query, params)的参数传递逻辑：execute方法要求参数序列的长度和SQL中?的数量完全对应。你传入的params是包含7个元组的列表，这会被解析成7个独立参数，但SQL里只有3个?，因此触发参数数量不匹配的错误。

第二个报错：表变量作用域问题 #

ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Must declare the table variable "@input". (1087)...')
每个execute或executemany调用都是独立的SQL批处理，变量@input仅在声明它的那个批处理中有效。你先通过curs.execute('declare @input mytype;\n')声明变量，后续的executemany属于新的批处理，无法访问之前声明的@input，因此报错。

正确解决方案：使用表值参数（TVP） #

SQL Server支持表值参数（Table-Valued Parameter），Pyodbc可以直接将Python中的数据集映射为SQL自定义表类型，无需手动处理临时表变量的插入逻辑，完美解决上述两个问题。

示例代码 #

import pyodbc

db_env = "My_env"
params = [(1, '2022-04-28', '2022-05-27'), 
          (2, '2022-05-28', '2022-06-27'), 
          (3, '2022-06-28', '2022-07-27'), 
          (4, '2022-07-28', '2022-08-27'), 
          (5, '2022-08-28', '2022-09-27'), 
          (6, '2022-09-28', '2022-10-27'), 
          (7, '2022-10-28', '2022-11-27')]

# 将参数列表转换为SQL表值参数，第一个参数是自定义表类型名
tvp = pyodbc.TableValuedParam("mytype", params)

with cursor as curs:
    # 直接将表值参数传入函数，无需手动插入临时表
    query = f"""
    SET NOCOUNT ON;
    USE {db_env};
    SELECT PeriodStart, PeriodEnd, CAST(Distance / 1000.0 AS VARCHAR(30)) 
    FROM myfunction(?);
    """
    curs.execute(query, tvp)
    result = curs.fetchall()

关键注意事项 #

• 确保自定义表类型mytype的列顺序、数据类型与params中元组的顺序、类型完全匹配，否则会触发类型不兼容错误。
• 使用支持表值参数的ODBC驱动（如ODBC Driver 11 for SQL Server及以上版本），旧版本驱动可能不支持该特性。

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