更新时间:2022-11-27 10:33:38
pyodbc.ProgrammingError: ('SQL 包含 1 个参数标记,但提供了 2 个参数', 'HY000')
pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000')
您收到该错误是因为表值参数是一个可迭代列表(***是元组)...
You are getting that error because a table-valued parameter is a list of iterables (preferably tuples) ...
my_tvp = [('Hello!', 1), ('Goodbye!', 2)]
print(f"my_tvp contains {len(my_tvp)} row(s)")
# my_tvp contains 2 row(s)
...如果你直接将它传递给 .execute()
那么每一行都被解释为一个参数值:
... and if you pass that directly to .execute()
then each row is interpreted as a parameter value:
sql = "{CALL TestTypeProcedure (?)}"
params = my_tvp
print(f"calling SP with {len(params)} parameter value(s)")
# calling SP with 2 parameter value(s)
crsr.execute(sql, params) # error
因此,您需要将您的 tvp 包裹在一个元组中以使其成为单个参数值
Therefore, you need to wrap your tvp inside a tuple to make it a single parameter value
sql = "{CALL TestTypeProcedure (?)}"
params = (my_tvp, ) # tuple containing a single tvp "object"
print(f"calling SP with {len(params)} parameter value(s)")
# calling SP with 1 parameter value(s)
crsr.execute(sql, params) # no error