20

I'm trying to read a few hundred tables from ascii and then write them to mySQL. It seems easy to do with Pandas but I hit an error that doesn't make sense to me:

I have a data frame of 8 columns. Here is the column list/index:

metricDF.columns

Index([u'FID', u'TYPE', u'CO', u'CITY', u'LINENO', u'SUBLINE', u'VALUE_010', u'VALUE2_015'], dtype=object)

I then use to_sql to append the data up to mySQL

metricDF.to_sql(con=con, name=seqFile, if_exists='append', flavor='mysql')

I get a strange error about a column being "nan":

OperationalError: (1054, "Unknown column 'nan' in 'field list'")

As you can see all my columns have names. I realize mysql/sql support for writing appears in development so perhaps that's the reason? If so is there a work around? Any suggestions would be greatly appreciated.

Improve this question

3 Answers 3

Reset to default
38

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')
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Awesome! Totally worked. You gotta love a simple one line solution like this. Thanks.
Note that this workaround will not remove NaT values from datetime64 columns (at least not when I have tried)
@aensm Thanks for noting, this bug will also be resolved in 0.15.
3

using the previous solution will change column dtype from float64 to object_.

I have found a better solution, just add the following _write_mysql function:

from pandas.io import sql

def _write_mysql(frame, table, names, cur):
    bracketed_names = ['`' + column + '`' for column in names]
    col_names = ','.join(bracketed_names)
    wildcards = ','.join([r'%s'] * len(names))
    insert_query = "INSERT INTO %s (%s) VALUES (%s)" % (
        table, col_names, wildcards)

    data = [[None if type(y) == float and np.isnan(y) else y for y in x] for x in frame.values]

    cur.executemany(insert_query, data)

And then override its implementation in pandas as below:

sql._write_mysql = _write_mysql

With this code, nan values will be saved correctly in the database without altering the column type.

Improve this answer

4 Comments

Note that this will not work with with pandas 0.14 and above (there was a refactor in pandas 0.14)
I have just discovered that. I am trying to find a similar solution for pandas 0.14
You can eg do the check in maybe_asscalar (github.com/pydata/pandas/blob/master/pandas/io/sql.py#L580)
I'm using python 3.6 with pandas version 1.1.5, sqlalchemy version 1.3.13 and cx_Oracle version 8.1.0. In my code I'm using Pandas method to_sql to insert the data from a dataframe into an existing Oracle table. I'm using the dtype argument to indicate what data types the various columns have. Sometimes the source data miss certain columns and I then insert those columns in the right place and I assign np.nan as values. Those columns are supposed to contain integers. The insert fails with this error message: expecting number. I also tried None as values but to no avail. No solution for Oracle?
0

I had a simillar situation, while converting some Postgres Databases into MySQL.
I tried other solutions, until I found your post.
The approach that worked clean and softly, was inserting this line, after saving the rows on a DataFrame variable:


data = data.astype(object).where(pandas.notnull(data), None)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.