Re: [pydata] protocol stream is incorrect when using to_sql

[Joris Van den Bossche]

Hi Marc,

You are trying to write `NaN` ('not a number', or also used for 'not available' or missing data), which is not yet supported in the current to_sql function (but this will be added in the upcoming 0.15 version). NaN is a valid value for a float column in pandas.

You can for now remove the NaN values with dropna,

df.dropna()

or you can convert NaN to None (which will be recognized by the database):

df = df.astype(object).where(pd.notnull(df), None)

But for this option you first have to create the table in the database and then append the above dataframe (as everything will be converted to object dtype and so a table with all strings would be created).

See also https://github.com/pydata/pandas/issues/2754 and http://stackoverflow.com/questions/23353732/python-pandas-write-to-sql-with-nan-values

Joris

2014-09-11 16:58 GMT+02:00 <marc....@googlemail.com>:

Hi,

I'm new to Pandas and trying to push some data to a SQL table, just a single column, the SQL datatype is set to 'text'.

The error I receive is

09/11/2014 02:40:32 PM DEBUG:

(ProgrammingError) ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][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)') u'INSERT INTO [t] ([Level3]) VALUES (?)' (nan,)

I can see from the highlighted part of the error that the value isn't a float but I can't figure out why PANDAS has it set to a float.

 

>>> c[['Level3']]
   FRCLevel3
0        NaN
>>> v = c[['Level3']]
>>> v.dtypes
FRCLevel3    float64
dtype: object

I'm stumped as to why the PANDAS has the object set to a float64 or am I misunderstanding how PANDAS sets types?

 

Thanks, Marc.

--
You received this message because you are subscribed to the Google Groups "PyData" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pydata+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.