pandas写入、读取mysql数据

1、写入数据库

import pandas as pd
# 读取本地数据集
pdf = pd.read_csv("/root/my_data/train.csv")

# 写入数据库
from sqlalchemy import create_engine  
# 使用pymysql  
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/testdb')  
  
# 或者使用mysqlclient  
# engine = create_engine('mysql+mysqldb://user:password@localhost:3306/dbname')

# 写入mysql数据库 if_exists='replace'表存在 删除重建
pdf.to_sql('dm_xd_model_train', con=engine, index=False, if_exists='replace')

2、从mysql读取数据

import pandas as pd
# 读取mysql 数据
from sqlalchemy import create_engine  
# 使用pymysql  
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/testdb')  

# sql语句
query = "SELECT * FROM dm_xd_model_train limit 20000"  

# 使用read_sql_query读取数据  
train = pd.read_sql_query(query, con=engine)  

# 显示DataFrame  
train.head()