python访问mysql数据库,并返回Dataframe类型的数据
import pymysql.cursorsimport pandas as pdimport numpy as npdef pyvisitMysql(params ,sql_text): ## 数据库参数信息 conn=pymysql.Connection(host = params.get('ip'), port = params.get('port'), user = params.get('username'), passwd = params.get('passwd'), charset = params.get('charset') ) cursor=conn.cursor() cursor.execute(sql_text) # 读取字段列名 index = cursor.description row = list() for i in range(len(index)): row.append(index[i][0]) #获取返回信息 data = cursor.fetchall() result = pd.Dataframe(list(data), columns = row) #关闭连接,释放资源 cursor.close() conn.close() return result if __name__=="__main__": import pandas as pd import numpy as np params = {'ip' : ip, 'port': 端口, -- 注意端口为数值变量 'username':用户名, 'passwd':密码, 'charset':字符集 }sql_text = 'select * from superhuang_md.att_res_base;' data_df = pyvisitMysql(params , sql_text)