Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

python 3.x - Is it possible to pass values to a table type parameter from PYODBC to SQL Server?

I created a test type as a table with the below columns:

CREATE TYPE [dbo].[TestType] AS TABLE
                                (
                                    [TestField] [varchar](10) NULL,
                                    [TestField2] [int] NULL
                                )

I then created a stored procedure the takes that table type as a parameter.

CREATE PROCEDURE TestTypeProcedure (@tt TestType READONLY)
AS
    SELECT *
    FROM @tt;

My goal is to be able to pass something like a list of lists as the parameter for the table type. Is that even possible?

myList = [['Hello!', 1], ['Goodbye!', 2]]
....
cursor.execute('{{Call {TestTypeProcedure} ({?})}}', myList)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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)

... 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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...