Insert pandas dataframe to mysql using sqlalchemy
Question: 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 <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](http://stackoverflow.com/questions/30631325/writing-to-mysql-database-
with-pandas-using-sqlalchemy-to-sql) but no actual reason was explained


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

