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.
