Python Pandas将NaN值写入SQL

Question

我正在尝试从ASCII格式读取几百个表格，然后把它们写入MySQL数据库。用Pandas来做这件事看起来很简单，但我遇到了一个让我困惑的错误：

我有一个包含8列的数据框。以下是列的名称列表：

metricDF.columns

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

然后我使用to_sql方法把数据追加到MySQL中。

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

结果我收到了一个奇怪的错误，提示某一列是“nan”：

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

如你所见，我所有的列都有名称。我意识到MySQL/SQL在写入方面的支持似乎还在开发中，所以这可能是原因？如果真是这样，有没有什么解决办法？任何建议都将非常感谢。

Answer 1

使用之前的解决方案会把列的数据类型从float64（浮点数）改成object_（对象）。

我找到了一种更好的方法，只需要添加下面的_write_mysql函数：

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)

然后在pandas中重写它的实现，如下所示：

sql._write_mysql = _write_mysql

使用这段代码，nan值（缺失值）会正确地保存到数据库中，而不会改变列的数据类型。

Answer 2

更新：从pandas 0.15开始，to_sql功能支持写入NaN值（这些值在数据库中会被写成NULL），所以下面提到的解决方法就不再需要了（详情请见https://github.com/pydata/pandas/pull/8208）。
pandas 0.15将在即将到来的十月发布，这个新功能已经合并到开发版本中。

---

这可能是因为你的表中有NaN值，而目前pandas的sql功能对NaN的处理并不好，这是一个已知的问题（参考https://github.com/pydata/pandas/issues/2754，https://github.com/pydata/pandas/issues/4199）

目前的解决方法是（适用于pandas 0.14.1及更早版本），你可以手动将nan值转换为None，方法是：

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

然后再将数据框写入sql。不过这样会把所有列都转换为对象类型。因此，你需要根据原始数据框来创建数据库表。例如，如果你的第一行没有包含NaN：

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