Error Calling Stored Procedure with UDT Parameter Using pyodbc

30 views Asked by At

I'm trying to use pyodbc to call a stored procedure which takes a UDT parameter but keep getting the following error: pyodbc.ProgrammingError: ('Invalid parameter type. param-index=4 param-type=NAType', 'HY105')

def uploadDataframeToSQL(inputData: pd.DataFrame):
    
    conn_str = (
        "DRIVER={ODBC Driver 17 for SQL Server};"
        "SERVER=<my_db_url>;"
        "DATABASE=<my_db>;"
        "UID=<my_uid>;"
        "PWD=<my_pwd>"
        )
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    
    data_to_insert = [tuple(row) for index, row in inputData.iterrows()]
    
    cursor.execute('call [dbo].MergeDemandUpsert (@Demand=?)', (data_to_insert,))

    conn.commit()
    
    cursor.close()
    conn.close()

The definition of my stored procedure and table type are as follows:

create type DemandType as table(
    eptTimeStamp DATETIME2 not null,
    amount  FLOAT,
    mid  FLOAT,
    peak  FLOAT,
    mid2  FLOAT,
    peak2  FLOAT,
    Total  FLOAT
);
CREATE PROCEDURE dbo.MergeDemandUpsert (
        @Demand DemandType READONLY
    )
AS   
BEGIN ...

Here is a sample of my data_to_insert: the datetime is represented as a string and all floats are in the proper format. Also to note: I was able to get this logic working using dotnet, so I believe the issue is with my unfamiliarity with pyodbc, not anything on the db end.

enter image description here

0

There are 0 answers