Error while trying to insert Python dataframe into SQL table-CodePudding

I am trying to insert a .csv file's contents into a SQL Server database. I am able insert columns which has no nulls in my csv files. There are few columns which has few blanks due to which I am unable to insert those specific columns along with others. I have created a table like this;

CREATE TABLE [main].[table1](
    [Record_ID] [int] IDENTITY(1,1) NOT NULL,
    [account_id] [nvarchar](max) NULL,
    [business_name] [nvarchar] (max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

As stated above, I have few blanks in account_id and business_name columns. I could able to insert other columns with no errors. When I try to insert these two columns I get an error like this,

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 (""): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. (8023) (SQLExecDirectW)')

Not sure why I am getting this every time I try to insert csv file contents. I have already took care of that in my insert command where null will be allowed.

Insert command:

cursor.execute("INSERT INTO main.table1(account_id,business_name) values(?,?)", row.account_id, row.business_name)

Can anyone tell me how to fix this?

Any help would be appreciated.

CodePudding user response：

I simply included the function to replace NaN with 0 and it now loads all rows successfully using the following line of code right after I create the dataframe I want to load (df)

df = df.fillna(value=0)