本文利用pymysql模块连接MySQL数据库,并汇总查询所需数据,同时对数据进行类型转换。
# -*- conding:utf-8 -*-
import pymysql
def mysql_func(sql):
db = pymysql.connect(host="110.109.200.***",port=3306, user="lilei",passwd= "lileicode",database="mydatabase")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用预处理语句创建表
cursor.execute(sql)
result_value = cursor.fetchall()
result_names = cursor.description
tt = pd.DataFrame()
# 循环每一天的数据
for ii in range(len(result_value)):
# 循环每天的每个变量
for j in range(len(result_value[ii])):
j_name = result_names[j][0]
tt.loc[ii, j_name] = result_value[ii][j]
# 特殊字段不进行处理,数值型的字段判断是否是浮点,否则是int
try:
if j_name not in ['date','cus_type','channel']:
if str(result_value[ii][j]).find('.')>0:
tt[j_name] = tt[j_name].astype(np.float)
else:
tt[j_name] = tt[j_name].astype(np.int)
except Exception as ExceptionError:
print('mysql_func',ExceptionError)
pass
db.close()
return tt
if __name__ == '__main__':
sql = 'select
date,
cus_type,
channel,
count(*) cnt,
sum(if(cus_type='old',1,0)) old_cnt,
sum(if(cus_type='old',1,0))/count(*) old_pct
from orderdata
groupby date,cus_type,channel limit 10'
mysql_func(sql)