Pandas 0.20.2 to_sql() using MySQL
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)
Where [schema]
is the database name, and in my particular case, :[port]
is omitted with [host]
being localhost
.
Comments
-
elPastor almost 2 years
I'm trying to write a dataframe to a MySQL table but am getting a
(111 Connection refused)
error.I followed the accepted answer here: Writing to MySQL database with pandas using SQLAlchemy, to_sql
Answer's code:
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)
...and the
create_engine()
line worked without error, but theto_sql()
line failed with this error:(mysql.connector.errors.InterfaceError) 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)
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?
-
JD Nayak over 3 yearsPutting it here so that other people can avoid: On @elPastor answer engine = create_engine('mysql+mysqldb://root:password@localhost:3306/mydbname', echo = False)