I'm attempting to insert data from a pandas dataframe into an MSSQL database table.
This is my code that's throwing an error
resultDf.to_sql(name='obscured',schema='dbo',if_exists='append', con=write_engine, index=True,chunksize=1500, method='multi')
This code worked/works
resultDf.head().to_sql(name='obscured',schema='dbo',if_exists='append', con=write_engine, index=True)
This is the error that I'm getting which according to sql alchemy's documentation is thrown by pyodbc, which is the driver I'm using for MSSQL.
DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')
[SQL: INSERT INTO dbo.obscured ([obscuredcolumn1], [obscuredcolumn2], [obscuredcolumn3], [obscuredcolumn4], [obscuredcolumn5], [obscuredcolumn6], [obscuredcolumn7], [obscuredcolumn8], [obscuredcolumn9]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?)
there's probably 1500 or so tuples of ?, then 1500 or so tuples of actual values:
[parameters: (datetime.datetime(2022, 8, 9, 15, 0), 7179747, 868, 920, 0, 0, 0, 0, 0, datetime.datetime(2022, 8, 9, 15, 0), 7179748, 0, 430, 0, 0, 0, 0, 0, datetime.datetime(2022, 8, 9, 15, 0)
Where have I gone wrong? What should I be testing to troubleshoot?
resultDfcontains any empty strings''? It seems that thePYODBCdriver can't handle empty strings. Maybe the reason why you can pushresultDf.head()to the MSSQL database table is that because the first five rows ofresultDfdoesn't carry any empty string.