Invalid SQL data type (0) (SQLBindParameter)')

Question

I am trying to insert Pandas Dataframe to SQL Table using PYODBC Package. But when I run the code it says

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

The column types are ok.

Following are the details

Code

for index, row in final_df.iterrows():
 print(row)
 cursor.execute("INSERT INTO CourseOutline ([ActivityTypeID] ,[ActivityID],[Sequence],[CourseTypeID] ,[Duration], [MinistryCode] ,[FGCCode] ,[Percentage],[ModuleID] ,[PageNo]) values(?,?,?,?,?,?,?,?,?,?)", final_df.ActivityType, final_df.DBID,final_df.Seq,final_df.CourseType,1,'MinistryCodes','FGCCodes',final_df.Percentage,final_df.Module,final_df.PgNo)

 cnxn.commit()
cursor.close()

dataframe column types

SQL Column types

I think that the 'Duration' value should be 1.0 (which is a float) and not 1 (which is an integer) — Ze'ev Ben-Tsvi, Jun 09 '22 at 14:37
@Ze'evBen-Tsvi thanks for reply. Duration is already in float64 — Usman Rafiq, Jun 09 '22 at 15:05
probably related: https://stackoverflow.com/a/72419953/2144390 TL;DR - just use pandas' [to_sql()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html) method — Gord Thompson, Jun 09 '22 at 16:49

vindevoy · Answer 1 · 2023-08-04T14:56:54.190

I faced the same error and for me the answer in this question worked:

Inserting rows into Microsoft SQL Server using pandas raises precision error

I changed my driver from "SQL Server" to "ODBC Driver 17 for SQL Server"

Also, originally, this worked for me in my Python environment with Pandas 1.x, but it stopped working when I switched to Pandas 2.0. I had to change the driver to make it work in both versions of Pandas

Invalid SQL data type (0) (SQLBindParameter)')

1 Answers1