python 连接数据库 Mysql

#获取数据方法
import pymysql 
import pandas as pd 
from matplotlib import pyplot as plt
from matplotlib import font_manager

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

日期时间处理——获取年月日时维度

def time_data(data):
        
    data['下单年'] = data["下单时间"].astype("str").apply(lambda x: x.split('-')[0]) 
    data['下单年月'] = data["下单时间"].astype("str").apply(lambda x: x.split('-')[0]+"/"+x.split('-')[1])
    data['下单月'] = data["下单时间"].astype("str").apply(lambda x: x.split('-')[1])
    data['下单日'] = data["下单时间"].astype("str").apply(lambda x: x.split('-')[2]) 
    data['下单时'] = data["下单时间"].astype("str").apply(lambda x: x.split('-')[0]) 
    data['付款年'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[0]) 
    data['付款年月'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[0]+"/"+x.split('-')[1])
    data['付款月'] =data["付款日期"].astype("str").apply(lambda x: x.split('-')[1])
    data['付款日'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[2]) 
    data['付款时'] = data["付款日期"].astype("str").apply(lambda x: x.split('-')[0]) 
    return data



DB = { 'host':"127.0.0.1",'port':3306, 'user':'root','password':'password','dbname':'datebase'}

留存分析

# 留存分析
SQL='''select *,
concat(ROUND(100*次日留存用户数/日活跃用户数),"%") 次日留存率,
concat(ROUND(100*三日留存用户数/日活跃用户数),"%") 三日留存率,
concat(ROUND(100*七日留存用户数/日活跃用户数),"%") 七日留存率
from(
select date(a.`付款时间`) 日期 ,DATE_FORMAT(a.`付款时间`,"%H") 时间,
COUNT(DISTINCT a.`用户ID`) 日活跃用户数,
COUNT(DISTINCT b.`用户ID`) 次日留存用户数,
COUNT(DISTINCT c.`用户ID`) 三日留存用户数,
COUNT(DISTINCT d.`用户ID`) 七日留存用户数
from paper_data a 
left join paper_data b 
on  a.`用户ID`=b.`用户ID`
and date(b.`付款时间`)=date(a.`付款时间`)+1
left join paper_data c
on  a.`用户ID`=c.`用户ID`
and date(c.`付款时间`)=date(a.`付款时间`)+3
left join paper_data d
on  a.`用户ID`=d.`用户ID`
and date(d.`付款时间`)=date(a.`付款时间`)+7
where a.`付款时间` between "2020-01-01" and "2020-01-31"
GROUP BY date(a.`付款时间`)
)f;'''
liuchun = get_mysql_data(DB, SQL)
liuchun
x=liuchun['日期']
y1=liuchun['次日留存率']
y2=liuchun['三日留存率']
y3=liuchun['七日留存率']
plt.figure(figsize=(25,10),dpi=80)
my_font = font_manager.FontProperties(fname="C:/Users/hxy_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
rects1=plt.plot(x,y1,color="red",alpha=0.5,linestyle="-",linewidth=3,label="次日留存率")
rects2=plt.plot(x,y2,color="g",alpha=0.5,linestyle="-",linewidth=3,label="三日留存率")
rects3=plt.plot(x,y3,color="b",alpha=0.5,linestyle="-",linewidth=3,label="七日留存率")
rects4=plt.legend(prop=my_font,loc="best")
for i in range(len(x)):
        plt.text(x[i],y1[i],y1[i],fontsize=15,ha="center")

for i in range(len(x)):
        plt.text(x[i],y2[i],y2[i],fontsize=15,ha="center")
for i in range(len(x)):
        plt.text(x[i],y3[i],y3[i],fontsize=15,ha="center")
        
plt.show()

订单分析

# 1.根据下单的时间来统计订单量
# (1)年维度
SQL='''select date_format(付款时间,"%Y")下单年 ,count(订单ID) as 订单量 from paper_data group by date_format(付款时间,"%Y")'''
order_num_year=get_mysql_data(DB, SQL)

my_font = font_manager.FontProperties(fname="C:/Users/hxy_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
x=order_num_year["下单年"]
y=order_num_year["订单量"]
plt.figure(figsize=(20,8),dpi=80)
rects = plt.bar(x,y,width=0.3,color="b",label="2019")
# 标注
for rect in rects:
    height=rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,height+0.3,str(height),ha="center")
plt.title("年度订单量",FontProperties=my_font)
plt.xlabel("年份",FontProperties=my_font)
plt.ylabel("订单量",FontProperties=my_font)
plt.legend(prop=my_font,loc="best")
plt.show()

# 订单分析
# 1.根据下单的时间来统计订单量
# (2)年月维度
SQL='''select date_format(付款时间,"%Y/%m")下单年月 ,count(订单ID) as 订单量 from paper_data group by date_format(付款时间,"%Y/%m")'''
order_num_month=get_mysql_data(DB, SQL)

my_font = font_manager.FontProperties(fname="C:/Users/hxy_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
x=order_num_month["下单年月"]
y=order_num_month["订单量"]
plt.figure(figsize=(20,8),dpi=80)
rects = plt.bar(x,y,width=0.3,color=["r","g","b"])
# 标注
for rect in rects:
    height=rect.get_height()
    plt.text(rect.get_x()+rect.get_width()/2,height+0.3,str(height),ha="center")
plt.show()

# (3)年月、城市维度（折线图）
SQL='''select date_format(下单时间,"%Y/%m")下单年月 ,城市,count(订单ID) as 订单量 
from paper_data
group by date_format(下单时间,"%Y/%m"),城市
order by date_format(下单时间,"%Y/%m");
'''
order_num_month1=get_mysql_data(DB, SQL)



#把每个城市的数据取出来
order_month_city=order_num_month1.groupby(["城市"])
order_shanghai=order_month_city.get_group("上海").reset_index(drop="true")
order_beijing=order_month_city.get_group("北京").reset_index(drop="true")
order_hangzhou=order_month_city.get_group("杭州").reset_index(drop="true")
order_xian=order_month_city.get_group("西安").reset_index(drop="true")


# matplotlib画图部分
my_font = font_manager.FontProperties(fname="C:/Users/hxy_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
plt.figure(figsize=(25,10),dpi=80)

plt.plot(order_shanghai["下单年月"],order_shanghai["订单量"],marker='o',color="r",label="上海订单量")
plt.plot(order_beijing["下单年月"],order_beijing["订单量"],marker='v',color="g",label="北京订单量")
plt.plot(order_hangzhou["下单年月"],order_hangzhou["订单量"],marker='^',color="b",label="杭州订单量")
plt.plot(order_xian["下单年月"],order_xian["订单量"],marker='.',color="y",label="西安订单量")
plt.xlabel("时间",FontProperties=my_font)
plt.ylabel("订单量",FontProperties=my_font)
plt.title("各城市每月订单量",FontProperties=my_font)
plt.legend(prop=my_font,loc="best")



#  df.idxmax()默认值是0，求列最大值的行索引，1就是反过来
def city_max(df):
    return df.idxmax()



# 最大值标记
plt.text(order_shanghai["下单年月"].iloc[city_max(order_shanghai["订单量"])],order_shanghai.max()["订单量"]+5,"上海最大订单：{}".format(order_shanghai.max()["订单量"]),fontsize=12,ha="center",color="r",FontProperties=my_font)
plt.text(order_beijing["下单年月"].iloc[city_max(order_beijing["订单量"])],order_beijing.max()["订单量"]+5,"北京最大订单：{}".format(order_beijing.max()["订单量"]),fontsize=12,ha="center",color="g",FontProperties=my_font)
plt.text(order_hangzhou["下单年月"].iloc[city_max(order_hangzhou["订单量"])],order_hangzhou.max()["订单量"]+5,"杭州最大订单：{}".format(order_hangzhou.max()["订单量"]),fontsize=12,ha="center",color="b",FontProperties=my_font)
plt.text(order_xian["下单年月"].iloc[city_max(order_xian["订单量"])],order_xian.max()["订单量"]+5,"西安最大订单：{}".format(order_xian.max()["订单量"]),fontsize=12,ha="center",color="y",FontProperties=my_font)
plt.show()

# (3)年城市维度(柱状图)
SQL='''select date_format(付款时间,"%Y")下单年,城市,count(订单ID) as 订单量 
from paper_data
group by date_format(付款时间,"%Y"),城市
order by date_format(付款时间,"%Y");
'''
order_num_year=get_mysql_data(DB, SQL)
# order_num_month.describe()

# 按城市拆分数据
order_year_city=order_num_year.groupby(["城市"])
order_shanghai=order_year_city.get_group("上海").reset_index(drop="true")
order_beijing=order_year_city.get_group("北京").reset_index(drop="true")
order_hangzhou=order_year_city.get_group("杭州").reset_index(drop="true")
order_xian=order_year_city.get_group("西安").reset_index(drop="true")

# -----matplotlib画图部分------
my_font = font_manager.FontProperties(fname="C:/Users/hxy_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
plt.figure(figsize=(25,10),dpi=80)
# 设置柱子位置，通过偏移量a来实现，
# 由于时间是datetime类型或者object(str)类型的不能直接加偏移量所以这里用长度长度实现柱子的位置，然后把刻度值重新设置 
a=0.2 
x_01=list(range(len(order_shanghai["下单年"])))
x_02=[i+a for i in x_01]
x_03=[i-2*a for i in x_01]
x_04=[i-a for i in x_01]


# 画图
rects1=plt.bar(x_01,order_shanghai["订单量"],a,color="r",align='edge',label="上海订单量")
rects2=plt.bar(x_02,order_beijing["订单量"],a,color="g",align='edge',label="北京订单量")
rects3=plt.bar(x_03,order_hangzhou["订单量"],a,color="b",align='edge',label="杭州订单量")
rects4=plt.bar(x_04,order_xian["订单量"],a,color="y",align='edge',label="西安订单量")

# x轴刻度
plt.xticks(x_01,labels=order_shanghai["下单年"])

# 轴名称
plt.xlabel("年份",FontProperties=my_font)
plt.ylabel("订单量",FontProperties=my_font)
# 标题
plt.title("各城市每月订单量",FontProperties=my_font)
# 图例
plt.legend(prop=my_font,loc="best")
# 标记
def mark_bar(rects):
    for rect in rects:
        height=rect.get_height()
        plt.text(rect.get_x()+rect.get_width()/2,height+0.3,str(height),fontsize=15,ha="center")
mark_bar(rects1)
mark_bar(rects2)
mark_bar(rects3)
mark_bar(rects4)
plt.show()

# 3、小时订单量分布情况
SQL= '''SELECT date_format(下单时间,"%H")  下单时, count(`订单ID`) 订单量
from paper_data
where  date_format(下单时间,"%Y-%m")="2020-01"
GROUP BY date_format(下单时间,"%H") 
ORDER BY date_format(下单时间,"%H") 
;'''

h_dingdan=get_mysql_data(DB, SQL)


my_font = font_manager.FontProperties(fname="C:/Users/hxy_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 18)
plt.figure(figsize=(25,10),dpi=80)
x=h_dingdan["下单时"]
y=h_dingdan["订单量"]
plt.plot(x,y,color="r",label="每小时订单量")
# 设置标记
for a,b  in zip(x,y):
    #a x坐标, b坐标, (a,b) 一个点 
    # ha 水平方向  va 垂直方向  fontsize 大小 
    plt.text(a,b,b ,ha='center',va='bottom', fontsize=12)
#设置刻度
xtick_labels = ['{}:00'.format(i) for i in x]
plt.xticks(x,xtick_labels)
#设置坐标名称
plt.xlabel("时间",FontProperties=my_font)
plt.ylabel("订单量",FontProperties=my_font)
#设置表名
plt.title("1月每小时订单量",FontProperties=my_font)
#设置图例
plt.legend(prop=my_font,loc="best")
plt.show()

用户评分统计

# 评分 
#0分代表未参与评分，可以统计用户的参与度

SQL='''select date_format(付款时间,"%Y/%m") 付款年月,评分,count(distinct 用户ID) as user_num,count(*) as order_num 
                   from paper_data group by date_format(付款时间,"%Y/%m"),评分;'''


pinfen_num=get_mysql_data(DB,SQL)
pinfen_num["评分"]=pinfen_num["评分"].astype(int)
pinfen_num["参与度"]=pinfen_num["user_num"]/pinfen_num["order_num"]
pinfen_num.dtypes

# 用户评分参与度=有评分的用户/下单用户量
import numpy as np
# 按评分分组
pf_group=pinfen_num.groupby(["评分"])

# id_name
# 取出分组的值
# 方法一
list_pf=[]
for key,value in pf_group:
    list_pf.append(value.reset_index(drop="true"))
# 方法二格式化成列表或者字典，详见后面一个代码块

x=list_pf[0]["付款年月"]
y=[]
for i in range(len(list_pf)):
    y.append(list_pf[i]["参与度"])
# print("x:",x.values)
# print("y:",y[1])


my_font = font_manager.FontProperties(fname="C:/Users/hxy_pc/Anaconda3/Lib/site-packages/matplotlib/mpl-data/fonts/ttf/STSONG.TTF",size = 15)
fig=plt.figure(figsize=(20,40),dpi=80)

colors=["r","g","b"]
for i in range(1,12):
    ax=fig.add_subplot(11,1,i)
    ax.plot(x,y[i-1],color=colors[np.random.randint(3)],label="{}分".format(i-1))
    x_label="评分为{}的趋势".format(i-1)
    ax.set_xlabel(x_label,fontproperties=my_font)
    ax.set_ylabel("参与度",fontproperties=my_font)
    ax.legend()
 #调整子图间距    
plt.tight_layout()
plt.show()