As part of my development activity in project I need to update or insert the output table. I am trying to pass a DF as table valued parameter in store procedure. By using a stored procedure, I want to achieve this. Could someone please help me here?
I have DF with 33 columns. It has varchar
, nvarchar
, int
, smallint
, float
and datetime
columns.
For example, I tried as mentioned here - https://www.sqlshack.com/table-valued-parameters-in-sql-server/ created, table, type table and procedure as in the above link. BUT only difference is INSTEAD of INSERTING as declaration I tried to pass from a DF.
Sample DF as below.
data = {'No':[1,2,3],'Name':['A','B','C','D']}
df=pd.DataFrame(data)
Changing DF into list -> tuple as in the link -> Is it possible to pass values to a table type parameter from PYODBC to SQL Server?
rec= df.to_records(index=False)
param=[tuple(rec)]
query="{call Usp_InsertLessonMemOpt (?) }"
**PYODBC connection**
cursor=py_con.cursor()
cursor.execute(query,param)
py_con.commit()
py_con.close()
Error : ('HY003', '[HY003] [Microsoft] [ODBC Driver Manager] Program type out of range (0) (SQLBindParametr)')
If I tried with turbodbc
Runtime error Error ! unsupported type identifier for column parameter_1 NULLABLE UNKNOWN TYPE (precision 0, scale 0))
Using: ODBC Driver 13 for SQL Server and python 3.7.9
Thanks in advance.