首先安装pymysql库:
pip install -U pymysql -i https://pypi.douban.com/simple
安装其余模块如下:
pip3 install -U 模块名 -i https://pypi.douban.com/simple
获取数据方法
import pymysql
import pandas as pd
def get_mysql_data(DB, sql):
conn = pymysql.connect(host=DB['host'], port=DB['port'], user=DB['user'], password=DB['password'], database=DB['dbname'])
# 创建游标
cursor = conn.cursor()
# 执行sql语句
cursor.execute(sql)
# 调出数据
data = cursor.fetchall()
# cols为字段信息
cols = cursor.description
# 将数据转换为DataFrame
col = []
for i in cols:
col.append(i[0])
# data转成list形式
data = list(map(list, data))
data = pd.DataFrame(data,columns=col)
# 关闭游标以及连接
cursor.close()
conn.close()
return data
DB = { 'host':"127.0.0.1",'port':3306, 'user':'root','password':'mysql','dbname':'kkb_17'}
SQL = "select * from employee;"
res = get_mysql_data(DB, SQL)