Writing a Pandas Dataframe to MySQL

Question

I'm trying to write a Python Pandas Dataframe to a MySQL database. I realize that it's possible to use sqlalchemy for this, but I'm wondering if there is another way that may be easier, preferably already built into Pandas. I've spent quite some time trying to do it with a For loop, but it's not realiable.

If anyone knows of a better way, it would be greatly appreciated.

Thanks a lot!

to_sql method http://stackoverflow.com/a/16477603/2027457 or http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html — n1tk, Oct 09 '16 at 03:30
The to_sql method works for me. However, keep in mind that the it looks like it's going to be deprecated in favor of SQLAlchemy: FutureWarning: The 'mysql' flavor with DBAPI connection is deprecated and will be removed in future versions. MySQL will be further supported with SQLAlchemy connectables. chunksize=chunksize, dtype=dtype) — bumbles, Oct 09 '16 at 15:55
so far still is active so till will be deprecated than will take some time and if you are not upgrading pandas than should be working. posting the answer than. — n1tk, Oct 09 '16 at 18:53
Why is sqlalchemy being avoided? All you need is to initialize the engine instead of raw connection, replacing flavor with it. — Parfait, Oct 09 '16 at 19:42

score 4 · Accepted Answer · answered Oct 09 '16 at 19:00

The other option to sqlalchemy can be used to_sql but in future released will be deprecated but now in version pandas 0.18.1 documentation is still active.

According to pandas documentation pandas.DataFrame.to_sql you can use following syntax:

DataFrame.to_sql(name, con, flavor='sqlite', schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)

you specify the con type/mode and flavor ‘mysql’, here is some description:

con : SQLAlchemy engine or DBAPI2 connection (legacy mode) Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.

flavor : {‘sqlite’, ‘mysql’}, default ‘sqlite’ The flavor of SQL to use. Ignored when using SQLAlchemy engine. ‘mysql’ is deprecated and will be removed in future versions, but it will be further supported through SQLAlchemy engines.

score 4 · Answer 2 · answered Jan 05 '18 at 15:24

You can do it by using pymysql:

For example, let's suppose you have a MySQL database with the next user, password, host and port and you want to write in the database 'data_2'.

import pymysql
user = 'root'
passw = 'my-secret-pw-for-mysql-12ud'
host =  '172.17.0.2'
port = 3306
database = 'data_2'

If you already have the database created:

conn = pymysql.connect(host=host,
                       port=port,
                       user=user, 
                       passwd=passw,  
                       db=database,
                       charset='utf8')

data.to_sql(name=database, con=conn, if_exists = 'replace', index=False, flavor = 'mysql')

If you do NOT have the database created, also valid when the database is already there:

conn = pymysql.connect(host=host, port=port, user=user, passwd=passw)

conn.cursor().execute("CREATE DATABASE IF NOT EXISTS {0} ".format(database))
conn = pymysql.connect(host=host,
                       port=port,
                       user=user, 
                       passwd=passw,  
                       db=database,
                       charset='utf8')

data.to_sql(name=database, con=conn, if_exists = 'replace', index=False, flavor = 'mysql')

Similar threads:

It appears this doesn't work in 0.25. The to_sql call no longer supports flavor= and assumes it's a sqlite connection if it's not a sqlalchemy connectable. — totalhack, Jul 30 '19 at 19:44

Writing a Pandas Dataframe to MySQL

2 Answers2

Linked