Pandas operating mysql database

Pandas operating mysql database

Sometimes our requirement in the company is to operate the data in the mysql database. If we can use pandas to directly operate mysql, it will be more convenient.
So here to share with you the read and write operations of pandas on the mysql database:
pandas reads the mysql database:

[Python]
Plain text view
Copy code
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
#
import pandas as pd
from sqlalchemy import create_engine
# pymysql
# MySQL root, :147369, 3306, mydb
engine = create_engine('mysql+pymysql://root:mysql@localhost:3306/pandas')
# employee
sql = '''
select * from mydf;
'''
# read_sql_query : sql
df = pd.read_sql_query(sql, engine)
# employee
print(df)

pandas writes data to mysql:

[Python]
Plain text view
Copy code
1
2
3
4
5
6
7
# pandas DataFrame, id,num
df = pd.DataFrame({'id':[1,2,3,4],'num':[12,34,56,89]})
# DataFrame MySQL index
df.to_sql('mydf', engine, index= False)
print('Read from and write to Mysql table successfully!')

pandas reads csv data to mysql:

[Python]
Plain text view
Copy code
1
2
3
4
# CSV
df = pd.read_csv("lianjia.csv", sep=',')
# # DataFrame MySQL index
df.to_sql('mydata', engine, index= False)

For more technical information, please pay attention to: itheimaGZ