CODEWITHSUNDEEP
pythonmysqlpandassqlalchemy
biggreyhairboy
biggreyhairboy

Reputation: 53

Insert pandas dataframe to mysql using sqlalchemy

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:[email protected]/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 <module>
   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.

a related question: Writing to MySQL database with pandas using SQLAlchemy, to_sql but no actual reason was explained

Upvotes: 2

Views: 7030

Answers (1)

RazerM
RazerM

Reputation: 5482

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')

Upvotes: 8

Related Questions