Push python data to SQL-CodePudding

I am trying to push my data into SQL but it keeps telling me that one of my columns is an invalid data type float.

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 29 (""): 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)')
[SQL: INSERT INTO dbo.[bid_ask_EnergyFwdOutright_101] ([ServiceId], [SchemaVersion], [PricingDate], [PricingTime], [PublicationGroup], [AssetType], [Underlying], [PricingRegion], [Derivative], [ReferenceSource], [ReferenceContractType], [ContractPeriod], [SettlementFrequency], [ContractStartDate], [ContractEndDate], [QuoteConvention], [SubmitterDataType], [Ccy], [CcyScalar], [SubmitterDataUnits], [IsImplied], [SubmitterData], [SubmissionGroupId], [InstrumentId], [SubmitterDataStatus], [RejectionReason], [RejectionDependentOn], [HistoricDataStatus], [HistoricPublicationCount], [HistoricNonComparableStreak], [HistoricNonComparableCount], [HistoricRejectionStreak], [HistoricRejectionCount], [HistoricNonSubmissionCount], [HistoricComparableCount], [PublicationDateTimeUTC], [CountSubmitted], [CountAccepted], [ConsensusData], [RangeData], [StandardDeviationData], [Percentile10Data], [Percentile90Data], [CompositeData]) VALUES (?,.....?)

I am creating a dictionary to convert the data to object and I have also tried as string. Ideally the data should be a float. My data type in SQL is matching whatever I use in python. I don't know how to correct this.

CodePudding user response：

I figure out what it was. My data had some nan cells and for some reason it was not accepting it. Once I addressed the Nan cells using the below line of code it worked. EnergyFwdOutright_CM101 = EnergyFwdOutright_CM101.where(pd.notnull(EnergyFwdOutright_CM101), None)

CodePudding user response：

The SQL Standard doesn't allow NaN values. While some databases, eg PostgreSQL allows NaNs, SQL Server doesn't. Values like NaN, positive or negative Infinity or Zero make sense when processing floating point numbers, not storing them.

You'll have to replace NaN with None, eg using DataFrame.replace:

df=df.replace(to_replace=np.nan, value=None)