Pandas 将表写入 MySQL：“无法回滚”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29356076/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pandas Write table to MySQL: "unable to rollback"
提问by Amrita Sawant
I need help to get this working. I have a pd.DataFrame (df), which I need to load to a MySQL database. I don't understand what the error message means and how to fix it.
我需要帮助才能完成这项工作。我有一个pd.DataFrame (df)，我需要将其加载到 MySQL 数据库中。我不明白错误消息的含义以及如何修复它。
Any help will be highly appreciated.
任何帮助将不胜感激。
This is what I tried:
这是我尝试过的：
import MySQLdb
from pandas.io import sql
#METHOD 1
db=MySQLdb.connect(host="***",port=***,user="***",passwd="***",db="***")
df.to_sql(con=db, name='forecast', if_exists='replace', flavor='mysql')
##Also tried
sql.write_frame(df, con=db, name='forecast', if_exists='replace', flavor='mysql')
**DatabaseError**: Execution failed on sql: SHOW TABLES LIKE %s
(2006, 'MySQL server has gone away')
unable to rollback
#METHOD 2: using sqlalchemy
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqldb://**username***:**passwd**@***host***:3306/**dbname**")
conn = engine.raw_connection()
df.to_sql(name='demand_forecast_t', con=conn,if_exists='replace', flavor='mysql',index=False, index_label='rowID')
conn.close()
The error message is:
错误信息是：
**OperationalError**: DatabaseError: Execution failed on sql: SHOW TABLES LIKE %s
(2006, 'MySQL server has gone away') unable to rollback
回答by joris
When using sqlalchemy, you should pass the engine and not the raw connection:
使用 sqlalchemy 时，您应该传递引擎而不是原始连接：
engine = create_engine("mysql+mysqldb://...")
df.to_sql('demand_forecast_t', engine, if_exists='replace', index=False)
Writing to MySQL without sqlalchemy (so with specifying flavor='mysql') is deprecated.
flavor='mysql'不推荐在没有 sqlalchemy 的情况下写入 MySQL（因此指定）已被弃用。
When the problem is that you have a too large frame to write at once, you can use the chunksizekeyword (see the docstring). Eg:
当问题是您的框架太大而无法一次写入时，您可以使用chunksize关键字（请参阅docstring）。例如：
df.to_sql('demand_forecast_t', engine, if_exists='replace', chunksize=10000)
回答by Amrita Sawant
I was able to resolve this issue. I was trying to load a large table into MySQL and as a result of which was getting the error. A simple for-loop to upload data in chunks solved the issue ! Many thanks to everyone who replied.
我能够解决这个问题。我试图将一个大表加载到 MySQL 中，结果出现错误。一个简单的循环上传数据块解决了这个问题！非常感谢所有回复的人。
回答by citynorman
For me this was fixed using
对我来说这是固定的
MySQLdb.connect("127.0.0.1","root","","db" )
instead of
代替
MySQLdb.connect("localhost","root","","db" )
and then
进而
df.to_sql('df',sql_cnxn,flavor='mysql',if_exists='replace', chunksize=100)
回答by pyAddict
You can write pandas dataframe in mysql table using mysql flavour(with DBAPI connection) in following ways
您可以通过以下方式使用 mysql 风格（使用 DBAPI 连接）在 mysql 表中写入 Pandas 数据框
step1: install mysqldb module -
$ sudo apt-get install python-dev libmysqlclient-devthen
$ pip install MySQL-python
step1：安装mysqldb模块——
$ sudo apt-get install python-dev libmysqlclient-dev然后
$ pip install MySQL-python
step2: make a connection with mysql
import MySQLdbcon = MySQLdb.connect("hostname","username","password","databasename")
step2：与mysql建立连接
import MySQLdbcon = MySQLdb.connect("hostname","username","password","databasename")
step3: write pandas dataframe in mysql table by using df.to_sql
df.to_sql('TableName',con = con,flavor='mysql',if_exists='replace', chunksize=100)
step3：使用df.to_sql在mysql表中写入pandas数据框
df.to_sql('TableName',con = con,flavor='mysql',if_exists='replace', chunksize=100)
