使用pandas连接mysql数据库并获取数据

需要先安装3个第三方包 

首先，连接数据库并执行sql

import pymysql
import pandas as pd
from sqlalchemy import create_engine  #Python中一个通过 ORM 操作数据库的框架

def reader(sql):
    engine = create_engine('mysql+pymysql://root:123456@localhost:3306/crm_info?charset=utf8')
    data = pd.read_sql(sql,engine)
    return data

pddata = reader("select * from zj_zjxx")
pddata.head()

 

 

使用pandas中DataFrame.to_sql()可以将数据导入到数据库表中

import pymysql
import pandas as pd

ym = pd.read_csv("C:\\Users\\52745\\Jupyter_doc\\ym\\广东省.csv")

ym.to_sql(name = 'ym2',con = 'mysql+pymysql://root:123456@localhost:3306/crm_info?charset=utf8',if_exists="append",index=False)