CODEWITHSUNDEEP
pythonmysqldataframe
bumbles
bumbles

Reputation: 191

Writing a Pandas Dataframe to MySQL

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!

Upvotes: 6

Views: 25302

Answers (2)

Rafael Valero
Rafael Valero

Reputation: 2816

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:

  1. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  2. How to insert pandas dataframe via mysqldb into database?

Upvotes: 4

n1tk
n1tk

Reputation: 2500

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.

Upvotes: 4

Related Questions