python - The supplied value is not a valid instance of float. But I'm not asking for a float. Where have I gone wrong?

Question

The situation is I am pulling a table through an API into a python dataframe. This dataframe is being pulled directly into a SQL server. I'm running into an issue where I encounter an empty string or and empty cell that something expects to be of data type float. Even though I have the table prebuilt in SSMS with not a single float-type column, I'm still getting this error.

I pulled the same data into a CSV to troubleshoot and we can see that the 78th row of data has an empty cell in the 9th column. My python code successfully pushes 77 rows into SQL and then fails. If I simply give the column the type nvarchar then it shouldn't complain when an empty string comes along.

Can anyone tell me why I'm getting an error with the 12th parameter(When I only have 9 columns) and its desire to be a float, when it should be content with being a string?

Here as an image showing all the pieces come together, but not quite come together. A lot going on

Here is the code that may be applicable or helpful. I have commented out all the other columns for troubleshooting and did not copy them here.

for i in range(0, 5):
    now = datetime.now()
    print ('loading stage', i+1, 'of', number_of_locations, 'location:', all_unique_locations[i], ' current time:' , now.strftime ("%H:%M:%S"))
    current_stage = stageSummary.get_stage_summary_by_location_text(access_token, all_unique_locations[i], False )
    df = convert_string_to_dataframe(current_stage)
    all_stage_summary_df = all_stage_summary_df.append(df)

connStr = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER=<redacted>;DATABASE=AAV_DEVELOPMENT;Trusted_Connection=yes')
cursor = connStr.cursor()

for index,row in all_stage_summary_df.iterrows():
    print("Adding row ");
    cursor.execute("INSERT INTO dbo.StageSummaries([Primary_Location]
                                                  ,[JobNo]
                                                  ,[Stage]
                                                  ,[Top_Depth_m]
                                                  ,[Elapsed_Time_hh_mm_ss]
                                                  ,[Start_Time]
                                                  ,[End_Time]
                                                  ,[Pumping_Time_hh_mm_ss]
                                                  ,[Mainline_Pressure_Min_MPa]
                                                  )values (?,?,?,?,?,?,?,?,?)"
                                                  ,row['Primary Location']
                                                  ,row['JobNo']
                                                  ,row['Stage #']
                                                  ,row['Top Depth (m)']
                                                  ,row['Elapsed Time (hh:mm:ss)']
                                                  ,row['Start Time']
                                                  ,row['End Time']
                                                  ,row['Pumping Time (hh:mm:ss)']
                                                  ,row['Mainline Pressure Min (MPa)']
                                                    ) 
    connStr.commit()
cursor.close()
connStr.close()

I have seen a couple of questions with the same error but they don't have alot of detail or solutions that I have found.

The error

('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 12 (""): 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)')

question from:https://stackoverflow.com/questions/66068980/the-supplied-value-is-not-a-valid-instance-of-float-but-im-not-asking-for-a-fl

Answer 1

Take a look at the fillowing link: "Insert Into" statement causing errors due to "Parameter 7 (""): The supplied value is not a valid instance of data type float."

You could try to replace an empty field by 0 in your dataframe.