CODEWITHSUNDEEP
sql-serverpython-3.xpyodbc
Robert Criqui
Robert Criqui

Reputation: 218

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)

Upvotes: 2

Views: 3608

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123849

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

Upvotes: 3

Ronen Ariely
Ronen Ariely

Reputation: 2434

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

Upvotes: 0

Related Questions