Python : Insert dataframe data into MySQL table　

Overview

Dataframe type in python is so useful to data processing and it’s possible to insert data as dataframe into MySQL . There is a sample of that.

Environments

Python 3.7.3
MySQL 5.5.62

Step1 : Making the table

Defining a table like the following.

> CREATE DATABASE testdb;
> CREATE TABLE testdb.mysql_table(
	col1 int
	,col2 int
	,col3 int
  );

Step2 : Making data

Making data for insert from python. the data should be the same type as the table you will insert it. The column name of dataframe is also same as the table if they are different you will get some error when you execute insert. These are sample code to make the dataframe.

import pandas as pd
df = pd.DataFrame([[1,2,3],[4,5,6]],columns=['col1','col2','col3'])
print(df)

#Result
   0  1  2
0  1  2  3
1  4  5  6

Step3 : Inserting data into MySQL table

To insert dataframe into the table you have to import sqlalchemy. You can define the connection information with create_engine method of sqlalchemy. This is a sample code.

from sqlalchemy import create_engine

#engine = create_engine('mysql://<user>:<password>@<host>/<database>?charset=utf8')
engine = create_engine('mysql://user:password@localhost/test_db?charset=utf8')

#df.to_sql(<table_name>,con=engine, if_exists='append', index=False)
df.to_sql('testdb.mysq_table',con=engine, if_exists='append', index=False)

#Result
   col1  col2  col3
0     1     2     3
1     4     5     6

That’s all.

Additional explanation

If there is no table at the database you are inserting dataframe, sqlalchemy can create table automatically so it works.