Is it possible to pass values to a table type parameter from PYODBC to SQL Server?

Question

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)

Gord Thompson · Accepted Answer · 2020-04-11 12:00:06Z

3

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

answered Apr 11, 2020 at 12:00

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Criqui Over a year ago

Ahhh, that makes so much sense. It's working now, so thanks for your help. Also, I've seen your name around here a lot and so many questions I've had have been answered by you already. Thanks for all the help you give to people around here.

Ronen Ariely · Accepted Answer · 2020-04-11 02:50:10Z

0

Table Value Parameters (TVP) is supported in Python: 3.7.4 and pyodbc: 4.0.26.

There is an open feedback that claims (I did not tested it) that you must create the type, in the SQL Server side, using the schema dbo

You can read more about it in the following GitHub feedback: https://github.com/mkleehammer/pyodbc/issues/595

Update: You can follow Microsoft "Getting Started" document here: https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-ver15

edited Apr 11, 2020 at 2:50

answered Apr 10, 2020 at 20:35

Ronen Ariely

2,45414 silver badges23 bronze badges

2 Comments

Robert Criqui Over a year ago

Hmmm, I can't seem to get it to work. It seems to be reading each list inside the list as a parameter because I keep getting this issue: pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000')

Ronen Ariely Over a year ago

Follow Microsoft "Getting Started|" document here: learn.microsoft.com/en-us/sql/connect/python/pyodbc/…

Collectives™ on Stack Overflow

Is it possible to pass values to a table type parameter from PYODBC to SQL Server?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related