使用SQLAlchemy将Pandas DataFrame插入mysql [英] Insert pandas dataframe to mysql using sqlalchemy

问题描述

我只是尝试将熊猫数据框写入ubuntu上的本地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')

并弹出错误

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'

数据帧不为空，数据库已准备好没有表，我尝试了其他方法在mysqldb中使用python创建表，并且效果很好.

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.

一个相关的问题: 使用SQLAlchemy，to_sql和熊猫一起写入MySQL数据库 但没有解释任何实际原因

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

推荐答案

您似乎正在使用较旧版本的熊猫.我做了一个快速的git bisect，以查找第53行包含con.rollback()的熊猫的版本，并在v0.12处找到了熊猫，这是在将SQLAlchemy支持添加到execute函数之前的.

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.

如果您坚持使用此版本的熊猫，则需要使用原始DBAPI连接:

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

否则，请更新熊猫并按预期使用引擎.请注意，使用SQLAlchemy时不需要使用flavor参数:

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

这篇关于使用SQLAlchemy将Pandas DataFrame插入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！