CODEWITHSUNDEEP
pythonsql-serverpyodbctable-valued-parameters
Himanshu
Himanshu

Reputation: 1

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

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.

Upvotes: 0

Views: 114

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

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),))

Upvotes: 0

Related Questions