pandas连接SQL数据库的两种方式

直接上代码

import pandas as pd

# DBAPI构建数据库链接engine
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='',database='database_name')
df1 = pd.read_sql("select * from table_name", con=conn)


# sqlalchemy构建数据库链接engine
import sqlalchemy
from sqlalchemy import create_engine
connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DATABASE)

engine=create_engine(connect_info)
df2 =pd.read_sql("select * from table_name", con=engine)

tot_df = df2 .merge(df1, on='id', how='outer', suffixes=('_x', '_y'))


df = pd.DataFrame({'id': [1, 2, 3, 4], 'name': ['zhangsan', 'lisi', 'wangwu', 'zhuliu']})
# 将新建的DataFrame储存为MySQL中的数据表，储存index列
df.to_sql('mydf', engine, index=True)
print('Read from and write to Mysql table successfully!')

将DataFrame写入Mysql

Pandas用法总结