pandas mysql insert,使用SQLAlchemy将Pandas DataFrame插入mysql

I simply try to write a pandas dataframe to local mysql database on ubuntu.

from sqlalchemy import create_engine

import tushare as ts

df = ts.get_tick_data('600848', date='2014-12-22')

engine = create_engine('mysql://user:passwd@127.0.0.1/db_name?charset=utf8')

df.to_sql('tick_data',engine, flavor = 'mysql', if_exists= 'append')

and it pop the error

biggreyhairboy@ubuntu:~/git/python/fjb$ python tushareDB.py

Error on sql SHOW TABLES LIKE 'tick_data'

Traceback (most recent call last):

File "tushareDB.py", line 13, in

df.to_sql('tick_data', con = engine,flavor ='mysql', if_exists= 'append')

File "/usr/lib/python2.7/dist-packages/pandas/core/frame.py", line 1261, in to_sql

self, name, con, flavor=flavor, if_exists=if_exists, **kwargs)

File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 207, in write_frame

exists = table_exists(name, con, flavor)

File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 275, in table_exists

return len(tquery(query, con)) > 0

File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 90, in tquery

cur = execute(sql, con, cur=cur)

File "/usr/lib/python2.7/dist-packages/pandas/io/sql.py", line 53, in execute

con.rollback()

AttributeError: 'Engine' object has no attribute 'rollback'

the dataframe is not empty, database is ready without tables, i have tried other method to create table in python with mysqldb and it works fine.

解决方案

You appear to be using an older version of pandas. I did a quick git bisect to find the version of pandas where line 53 contains con.rollback(), and found pandas at v0.12, which is before SQLAlchemy support was added to the execute function.

If you're stuck on this version of pandas, you'll need to use a raw DBAPI connection:

df.to_sql('tick_data', engine.raw_connection(), flavor='mysql', if_exists='append')

Otherwise, update pandas and use the engine as you intend to. Note that you don't need to use the flavor parameter when using SQLAlchemy:

df.to_sql('tick_data', engine, if_exists='append')