Pass TVP data to a SQL query in Python (pyodbc)

Question

I am trying to pass a table valued parameter from Python to SQL Server query.

This is my table value that I want pass as a param.

table_values = [
        'table_type_name',
        'dbo',
        (1,None,'Test')
    ]

Here is my param syntax.

params = [(table_values,None)]

Here I am executing this with pyodbc connection

execute(SAVE_QUERY, params)

Here is my SQL query

SAVE_QUERY= """
    SET NOCOUNT ON;
    SET ANSI_NULLS ON;

    DECLARE @table_values TABLE = ?;

    UPDATE 
        table_name
    SET 
        contacts.is_active = 0,
    FROM
        table_name
    LEFT JOIN
        @table_values CC
    ON
        CC.id = contacts.id
    WHERE
        CC.email IS NULL;

"""

it is giving me the below error

[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near '='. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)

What's the best way to receive the TVP data in this query and store it in variable for using it later part of query.

I have tried removing DECLARE statement but its not working.

DECLARE @table_values TABLE = ?; is nonsense, there's no such syntax. LEFT JOIN ? CC makes sense. — Charlieface
– Charlieface, Commented Nov 4, 2024 at 14:35
Thanks @Charlieface, i was trying to find any docs at microsoft which don't mention procedure but rather stand-alone TVP, but nothing there. — siggemannen
– siggemannen, Commented Nov 4, 2024 at 14:37
@siggemannen Well ad-hoc parameterized SQL is actually passed through via EXEC sp_executesql so it comes to the same thing. — Charlieface
– Charlieface, Commented Nov 4, 2024 at 14:39
The LEFT JOIN ? CC suggestion by @Charlieface should work provided that you don't run into this limitation in pyodbc. — Gord Thompson
– Gord Thompson, Commented Nov 4, 2024 at 17:33

Gord Thompson · Accepted Answer · 2024-11-05 21:38:18Z

0

As @Charlieface suggests in a comment to the question, for a user-defined table type

CREATE TYPE [dbo].[table_type_name] AS TABLE(
    [id] [int] NOT NULL,
    [email] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

the following code would work

table_values = [
    "table_type_name",
    "dbo",
    (1, None, "Test"),
]

save_query = """\
    SET NOCOUNT ON;
    UPDATE contacts
        SET contacts.is_active = 0
        FROM contacts LEFT JOIN ? CC ON CC.id = contacts.id
        WHERE CC.email IS NULL;
"""
crsr.execute(save_query, (table_values,))

except that the first row of the table data contains a None value and a known issue with pyodbc raises the error

pyodbc.Error: ('HY004', '[HY004] [Microsoft][ODBC Driver 18 for SQL Server]Invalid SQL data type (0) (SQLBindParameter)')

The workaround is to use OPENJSON() like so:

table_rows_as_tuples = [
    (1, None, "Test"),
]
table_rows_as_dicts = [
    dict(zip(["id", "email", "name"], tup)) for tup in table_rows_as_tuples
]

save_query = """\
    SET NOCOUNT ON;
    DECLARE @table_values [dbo].[table_type_name];
    INSERT INTO @table_values
        SELECT id, email, name
        FROM OPENJSON(?)
        WITH (
            id int,
            email nvarchar(255),
            name nvarchar(255)
        );
    UPDATE contacts
        SET contacts.is_active = 0
        FROM contacts LEFT JOIN @table_values CC ON CC.id = contacts.id
        WHERE CC.email IS NULL;
"""
crsr.execute(save_query, (json.dumps(table_rows_as_dicts, default=str),))

edited Nov 5, 2024 at 21:38

answered Nov 5, 2024 at 15:29

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

Thom A Over a year ago

I must admit, using OPENJSON as a crutch like that is really yuck... It adds so much bloat to the code, and is not user friendly.

Collectives™ on Stack Overflow

Pass TVP data to a SQL query in Python (pyodbc)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related