to_sql引发模糊的KeyError: <class 'numpy.float64'>

Question

id,1,2,1ah,2ah,ah_line
13399,1.08,10.03,2.0,1.89,-13.5
13400,1.07,11.25,2.0,1.89,-19.5
13401,1.22,4.84,1.97,1.93,-9.0
13402,3.09,1.43,1.99,1.92,5.0

我正在尝试通过 pandas 的 DataFrame.to_sql 方法把数据填充到 MySQL 数据库里，但不知道为什么出现了 KeyError: <class 'numpy.float64'> 这个错误。

print(df.dtypes)
id           int64
1          float64
2          float64
1ah        float64
2ah        float64
ah_line    float64
dtype: object

我现在有点想不起来了，有什么想法吗？我之前做过很多次，从来没有遇到过这个错误。

编辑

奇怪的是，使用几乎相同的数据时，这个方法却能正常工作，比如：

id,q1,q2,q3,q4,ot1,ot2
13399,16.0,17.0,17.0,19.0,na,na
13400,20.0,17.0,20.0,16.0,na,na
13401,18.0,19.0,17.0,18.0,na,na
13402,15.0,17.0,17.0,16.0,na,na

信息：

pandas==0.13.1
cymysql==0.7.2

完整的错误追踪：

Traceback (most recent call last):
  File "/home/bartek/Desktop/buckypy3/plk-working.py", line 389, in <module>
    df.to_sql('plk_odds', con=conn, flavor='mysql', if_exists='append')
  File "/home/bartek/py3/lib/python3.3/site-packages/pandas/core/frame.py", line 1261, in to_sql
    self, name, con, flavor=flavor, if_exists=if_exists, **kwargs)
  File "/home/bartek/py3/lib/python3.3/site-packages/pandas/io/sql.py", line 238, in write_frame
    func(frame, name, safe_names, cur)
  File "/home/bartek/py3/lib/python3.3/site-packages/pandas/io/sql.py", line 264, in _write_mysql
    cur.executemany(insert_query, data)
  File "/home/bartek/py3/lib/python3.3/site-packages/cymysql/cursors.py", line 144, in executemany
    self.execute(query, params)
  File "/home/bartek/py3/lib/python3.3/site-packages/cymysql/cursors.py", line 118, in execute
    escaped_args = tuple(conn.escape(arg) for arg in args)
  File "/home/bartek/py3/lib/python3.3/site-packages/cymysql/cursors.py", line 118, in <genexpr>
    escaped_args = tuple(conn.escape(arg) for arg in args)
  File "/home/bartek/py3/lib/python3.3/site-packages/cymysql/connections.py", line 294, in escape
    return escape_item(obj, self.charset, self.encoders)
  File "/home/bartek/py3/lib/python3.3/site-packages/cymysql/converters.py", line 354, in escape_item
    encoder = encoders[type(val)]
KeyError: <class 'numpy.float64'>

mysql numpy 数据库操作 pandas keyerror to_sql

to_sql引发模糊的KeyError: <class 'numpy.float64'>

1 个回答

撰写回答