使用MySQL的Pandas 0.20.2 to_sql() [英] Pandas 0.20.2 to_sql() using MySQL
问题描述
我正在尝试将数据帧写入MySQL表,但出现(111 Connection refused)
错误.
I'm trying to write a dataframe to a MySQL table but am getting a (111 Connection refused)
error.
我在这里遵循了公认的答案: 使用SQLAlchemy,to_sql和熊猫一起写入MySQL数据库
I followed the accepted answer here: Writing to MySQL database with pandas using SQLAlchemy, to_sql
答案的代码:
import pandas as pd
import mysql.connector
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)
...并且create_engine()
行正常运行,但to_sql()
行失败并出现以下错误:
...and the create_engine()
line worked without error, but the to_sql()
line failed with this error:
(mysql.connector.errors.InterfaceError) 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)
我与MySQL数据库/表的连接方式并不相关,因此完全可以接受完全不同的答案,但是要给出
How I connect to my MySQL database / table is not really relevant, so completely different answers are appreciated, but given the deprecation of the MySQL 'flavor' in pandas 0.20.2, what is the proper way to write a dataframe to MySQL?
推荐答案
感谢@AndyHayden的提示,此答案是诀窍.基本上,用mysqldb
代替mysqlconnector
是关键.
Thanks to a tip from @AndyHayden, this answer was the trick. Basically replacing mysqlconnector
with mysqldb
was the linchpin.
engine = create_engine('mysql+mysqldb://[user]:[pass]@[host]:[port]/[schema]', echo = False)
df.to_sql(name = 'my_table', con = engine, if_exists = 'append', index = False)
其中[schema]
是数据库名称,在我的特殊情况下,:[port]
被省略,而[host]
为localhost
.
Where [schema]
is the database name, and in my particular case, :[port]
is omitted with [host]
being localhost
.
这篇关于使用MySQL的Pandas 0.20.2 to_sql()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!