Python Pandas write to sql with NaN values

by

Update: starting with pandas 0.15, to_sql supports writing NaN values (they will be written as NULL in the database), so the workaround described below should not be needed anymore (see https://github.com/pydata/pandas/pull/8208).
Pandas 0.15 will be released in coming October, and the feature is merged in the development version.

This is probably due to NaN values in your table, and this is a known shortcoming at the moment that the pandas sql functions don’t handle NaNs well (https://github.com/pydata/pandas/issues/2754, https://github.com/pydata/pandas/issues/4199)

As a workaround at this moment (for pandas versions 0.14.1 and lower), you can manually convert the nan values to None with:

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

and then write the dataframe to sql. This however converts all columns to object dtype. Because of this, you have to create the database table based on the original dataframe. Eg if your first row does not contain NaNs:

df[:1].to_sql('table_name', con)
df2[1:].to_sql('table_name', con, if_exists="append")

More Related Contents:

  1. SQL / Pandas equivalent
  2. Pandas read_sql with parameters
  3. Pandas DENSE RANK
  4. How to convert SQL Query result to PANDAS Data Structure?
  5. How to create a large pandas dataframe from an sql query without running out of memory?
  6. Not all parameters were used in the SQL statement (Python, MySQL)
  7. How to insert pandas dataframe via mysqldb into database?
  8. pip install mysqlclient returns “fatal error C1083: Cannot open file: ‘mysql.h’: No such file or directory
  9. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  10. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  11. Using a Python dict for a SQL INSERT statement
  12. Pandas left outer join multiple dataframes on multiple columns
  13. Get data from pandas into a SQL server with PYODBC
  14. Insert list into my database using Python [duplicate]
  15. Python Pandas to_sql, how to create a table with a primary key?
  16. Pandas query function not working with spaces in column names
  17. Dynamic SQL Queries with Python and mySQL
  18. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  19. Pandas analogue to SQL MINUS / EXCEPT operator, using multiple columns
  20. Pandas: Get Dummies
  21. Comparing previous row values in Pandas DataFrame
  22. What is the difference between join and merge in Pandas?
  23. pyodbc – How to perform a select statement using a variable for a parameter
  24. Python 3.4.0 with MySQL database
  25. Counting unique values in a column in pandas dataframe like in Qlik?
  26. Python pandas / matplotlib annotating labels above bar chart columns [duplicate]
  27. How to upsert pandas DataFrame to PostgreSQL table?
  28. How to plot multiple dataframes to the same plot axes
  29. Remove ends of string entries in pandas DataFrame column
  30. why is a sum of strings converted to floats

Leave a Comment