Writing a dataframe from Python to MySQL

Question

I have a script that downloads data from an API and turns all of this info into a CSV. I need this data to be into a table in MySQL (I already created it and established the connection with MySQL Connector). Is there anyway to do this?

Answer 1

Pandas.DataFrame has a method to_sql which writes a dataframe into a sql table.

Simplest way to use the method is to create a connection with sqlalchemy.(You will need to install mysql-python) and use .to_sql to read the data into the table.

from sqlalchemy import create_engine
engine = create_engine('mysql://username:password@host:port/database') #change to connect your mysql
#if you want to append the data to an existing table
df.to_sql(name='SQL Table name',con=engine,if_exists='append',index=False) 

#if you want to create a new table 
df.to_sql(name='New SQL Table name',con=engine,if_exists='fail',index=False) 

Please note that you will need to use param dtype to define the dtype of the table columns if you have created the table before hand.