【matplot画图】查询数据库，并将结果显示为图形

 画图python代码

# 画图的基本类
# 2019-12-18 14:47:46
# author：
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import matplotlib as mpl
import numpy as np
from matplotlib.pyplot import rc
import time

def DrawLinePic(InputDict,titleName,ylabelName,filehead):
    # 中文乱码和坐标轴负号处理
    mpl.rc('font', family='SimHei', weight='bold')
    # plt.rcParams['axes.unicode_minus'] = False

    #横轴为软件Version，对应InputDictionary的 key，纵轴为数据 对应为 value
    
    versions = []
    data = []
    InputDict = dict(InputDict)
    for key in InputDict:
        versions.append(key)
        data.append(InputDict[key])
    #绘图
    labels = versions
    lineOne,=plt.plot(labels,data,label=ylabelName)
    plt.xlabel('')
    plt.ylabel('')
    plt.title(titleName)
    plt.legend()
    # plt.show()


#画垂直柱状图
def DrawVerticalBar(InputDict,titleName,ylabelName,filehead):
    # 中文乱码和坐标轴负号处理
    mpl.rc('font', family='SimHei', weight='bold')
    # plt.rcParams['axes.unicode_minus'] = False

    #横轴为软件Version，对应InputDictionary的 key，纵轴为数据 对应为 value
    
    versions = []
    data = []
    InputDict = dict(InputDict)
    for key in InputDict:
        versions.append(key)
        data.append(InputDict[key])
    #绘图
    labels = versions

    x = np.arange(len(labels))  # the label locations
    width = 0.2  # the width of the bars
    fig, ax = plt.subplots()
    rects1 = ax.bar(x - width/2, data, width, label='')    

    # Add some text for labels, title and custom x-axis tick labels, etc.
    ax.set_ylabel(ylabelName)
    ax.set_title(titleName,loc='center')
    ax.set_xticks(x)
    ax.set_xticklabels(labels)
    ax.legend()
    plt.xticks(rotation='vertical')
    
    # 调节plot显示的边距显示文本问题
    plt.subplots_adjust(bottom=0.45,right=0.96,top=0.9)

    def autolabel(rects):
        """Attach a text label above each bar in *rects*, displaying its height."""
        for rect in rects:
            height = rect.get_height()
            ax.annotate('{}'.format(height),
                        xy=(rect.get_x() + rect.get_width() / 2, height),
                        xytext=(0, 3),  # 3 points vertical offset
                        textcoords="offset points",
                        ha='center', va='bottom')


    autolabel(rects1)
            
    # 返回图片保存路径
    timeStap = time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))
    picSaveName = 'D:/02.Work/01.MyWork/00.自动化工厂/00.自动化脚本/06.auto-mail/pic/VerBar'+filehead+timeStap+'.jpg'
    
    figure = plt.gcf() # get current figure
    figure.set_size_inches(6, 5)    
    figure.savefig(picSaveName, dpi=100)
    
    # plt.show()
    return picSaveName


#画水平柱状图
def DrawHorizonBar(InputDict,titleName):
    # 中文乱码和坐标轴负号处理
    mpl.rc('font', family='SimHei', weight='bold')
    plt.rcParams['axes.unicode_minus'] = False


    #排序倒序，为了呈现数据方便
    InputDict = (dict)(sorted(InputDict.items(), key=lambda item:item[1], reverse=False))
    

    taskname = []
    data = []
    for key in InputDict:        
    # 纵坐标
        if key == 'daily-ci-no-wifi':
            taskname.append("CI门槛:")
        elif key == 'daily-ci-wifi':
            taskname.append("CI门槛-WIFI:")
        else:
            taskname.append(key)        
            
        #装载数据。
        data.append(InputDict[key])

    #绘图
    fig, ax = plt.subplots()
    b = ax.barh(range(len(taskname)), data, color='#0fd4e7')
    
    #为横向水平的柱图右侧添加数据标签。
    for rect in b:
        w = rect.get_width()
        ax.text(w, rect.get_y()+rect.get_height()/2, '%d' %
                int(w), ha='left', va='center')
    
    #设置Y轴纵坐标上的刻度线标签。
    ax.set_yticks(range(len(taskname)))
    ax.set_yticklabels(taskname)
    
    #不要X横坐标上的label标签。
    plt.xticks(())
    # 微调左边文本显示
    plt.subplots_adjust(left=0.15)
    
    plt.title(titleName, loc='center')    

    # 返回图片保存路径
    timeStap = time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))
    picSaveName = 'D:/02.Work/01.MyWork/00.自动化工厂/00.自动化脚本/06.auto-mail/pic/HorBar'+timeStap+'.jpg'   
    
    figure = plt.gcf() # get current figure
    figure.set_size_inches(6, 3)    
    figure.savefig(picSaveName, dpi=100)

    plt.savefig(picSaveName)
    # plt.show()
    return picSaveName



# 画饼图
# 执行数保存的小数点格式
def func(pct, allvals):
    absolute = int(pct/100.*np.sum(allvals))
    return "{:.1f}%\n(执行数:{:d})".format(pct, absolute)

def DrawPieMap(Input,titleName):
    data = []
    recipe = []
    for key,value in Input.items():
        if key == 'daily-ci-no-wifi':
            recipe.append("CI门槛:")
        elif key == 'daily-ci-wifi':
            recipe.append("CI门槛-WIFI:")
        else:
            recipe.append(key)
        data.append(int(value))

    ingredients = recipe
    fig, ax = plt.subplots(figsize=(6, 3), subplot_kw=dict(aspect="equal"))
    # recipe = ["CI门槛",
    #         "视频通话",
    #         "智能家居",
    #         "基本功能"]
    # data = [1,2,3,4]
    # ingredients = ["CI门槛",
    #         "视频通话",
    #         "智能家居",
    #         "基本功能"]
    wedges, texts, autotexts = ax.pie(data, autopct=lambda pct: func(pct, data),
                                    textprops=dict(color="w"))

    ax.legend(wedges, ingredients,
            title="图例",
            loc="center right",
            bbox_to_anchor=(1, 0, 0.5, 1))
    plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
    plt.setp(autotexts, size=10, weight="bold")

    ax.set_title(titleName)
    

    # 返回图片保存路径
    timeStap = time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))
    picSaveName = 'D:/02.Work/01.MyWork/00.自动化工厂/00.自动化脚本/06.auto-mail/pic/piePic'+timeStap+'.jpg'

    plt.savefig(picSaveName)
    # plt.show()
    return picSaveName


# 画环形图
def DrawRingMap(Input,titleName,isVideoCall,filehead):
    InputDict = dict(Input)
    data = []
    recipe = []
    for key,value in InputDict.items():
        if(isVideoCall):
            if (key == ''):
                recipe.append("环境原因:"+str(InputDict[key]))
            else:
                recipe.append(key+":"+str(InputDict[key]))
        else:
            recipe.append(key+":"+str(InputDict[key]))
        data.append(str(InputDict[key]))

    fig, ax = plt.subplots(figsize=(6, 3), subplot_kw=dict(aspect="equal"))

    wedges, texts = ax.pie(data, wedgeprops=dict(width=0.5), startangle=-40)

    bbox_props = dict(boxstyle="square,pad=0.3", fc="w", ec="k", lw=0.72)
    kw = dict(arrowprops=dict(arrowstyle="-"),
            bbox=bbox_props, zorder=0, va="center")

    for i, p in enumerate(wedges):
        ang = (p.theta2 - p.theta1)/2. + p.theta1
        y = np.sin(np.deg2rad(ang))
        x = np.cos(np.deg2rad(ang))
        horizontalalignment = {-1: "right", 1: "left"}[int(np.sign(x))]
        connectionstyle = "angle,angleA=0,angleB={}".format(ang)
        kw["arrowprops"].update({"connectionstyle": connectionstyle})
        ax.annotate(recipe[i], xy=(x, y), xytext=(1.35*np.sign(x), 1.4*y),
                    horizontalalignment=horizontalalignment, **kw)

    ax.set_title(titleName)
    plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
    

    # 返回图片保存路径
    timeStap = time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))
    picSaveName = 'D:/02.Work/01.MyWork/00.自动化工厂/00.自动化脚本/06.auto-mail/pic/RingMap'+filehead+timeStap+'.jpg'

    plt.savefig(picSaveName)

    # plt.show()
    return picSaveName

调用画图示例：

1、查询数据库数据

2、将数据库数据画图保存，返回的是保存的文件名

import pymysql
import time
import sendMailWithImg
import datetime
import sunboDrawing
import matplotlib.pyplot as plt
from email.mime.base import MIMEBase
from email import encoders
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage

# 处理数据，主程序调用
def process():    
    # 连接数据库
    db = pymysql.connect("10.178.229.165", "用户名", "密码", "iottest", charset='utf8')
    cursor = db.cursor()
    # 全局查询
    TimeGap = "7"  #设置数据查询的时间间隔、
    TotalDataQuery = "select count(*) from UITESTRESULT"
    AllDataQuery = "select count(*) from UITESTRESULT where DATE_SUB(CURDATE(), INTERVAL "+TimeGap+" DAY) <= date(testtime) "
    AllPassQuery =" select count(*) from UITESTRESULT where DATE_SUB(CURDATE(), INTERVAL "+TimeGap+" DAY) <= date(testtime) and result = 'pass' "
    AllFailQuery =" select count(*) from UITESTRESULT where DATE_SUB(CURDATE(), INTERVAL "+TimeGap+" DAY) <= date(testtime) and result != 'pass'"

    #将元组数据存进列表中
    cursor.execute(TotalDataQuery)
    TotalAll=cursor.fetchall()
    db.commit()
    TotalCount = (str(TotalAll[0])).replace("'","").replace(",","").replace("(","").replace(")","")


    cursor.execute(AllDataQuery)
    resultAll=cursor.fetchall() #周期内的全部数量
    db.commit()
    
    AllCount = (str(resultAll[0])).replace("'","").replace(",","").replace("(","").replace(")","")

    cursor.execute(AllPassQuery)
    PassAll=cursor.fetchall() #Pass 数量
    db.commit()
    AllPass = PassAll[0]

    cursor.execute(AllFailQuery)
    FailAll=cursor.fetchall() #Fail 数量
    db.commit()
    AllFail = FailAll[0]
    
    # Pass和Fail的饼图
    plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
    plt.subplots_adjust(top=0.6)#调节上下边距
    plt.figure(figsize=(6, 3))#resize
    sizes = [AllPass,AllFail]
    labels = ['Pass','Fail']
    colors = ['#53ff00','#ff3300']
    explodes = (0.2,0)
    plt.pie(sizes,explode=explodes,labels=labels,autopct='%1.1f%%',shadow=False,startangle=150,colors=colors)
    plt.title("自动化测试通过率总览(近"+TimeGap + "天)")


    # 保存图片
    timeStap = time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))
    picNameAllPassFail = 'D:/02.Work/01.MyWork/00.自动化工厂/00.自动化脚本/06.auto-mail/pic/allPassFail'+timeStap+'.jpg'

    plt.savefig(picNameAllPassFail)

    # picNameAllCount = 'D:/02.Work/01.MyWork/00.自动化工厂/00.自动化脚本/06.auto-mail/pic/allCount'+timeStap+'.jpg'
    # 显示图片
    # plt.show()


    # 查询周期内，用例执行分布
    AllDispatchCaseQuery= "select taskname,count(updatetime) as c from UITESTRESULT where DATE_SUB(CURDATE(), INTERVAL "+TimeGap+" DAY) <= date(testtime) group by taskname "
    cursor.execute(AllDispatchCaseQuery)
    AllDispatchResult = cursor.fetchall()
    db.commit()

    # 查询视频通话
    # 视频通话执行数量
    VideoCallAllQuery= "select version,count(result) as count from UITESTRESULT where taskname = '视频通话' group by version order by version asc limit 10"
    cursor.execute(VideoCallAllQuery)
    VideoCallAllResult =cursor.fetchall()
    db.commit()

    # 视频通话呼通率
    VideoCallPassQuery = "select version,count(result) as passcount from UITESTRESULT where taskname = '视频通话' and result = 'pass' group by version "
    cursor.execute(VideoCallPassQuery)
    VideoCallPassCount = cursor.fetchall()
    db.commit()

    # 视频通话失败原因占比
    VideoCallFailQuery = "select reason,count(result) as passcount from UITESTRESULT where taskname = '视频通话' and result = 'Fail' group by reason"
    cursor.execute(VideoCallFailQuery)
    VideoCallFailReasonCount= cursor.fetchall()
    db.commit()

    # 查询无线投屏
    # 无线投屏各版本执行数量
    CastScreenAllQuery= "select version,count(result) as count from UITESTRESULT where taskname = '投屏' group by version order by version asc"
    cursor.execute(CastScreenAllQuery)
    CastScreenAllResult =cursor.fetchall()
    db.commit()

    # 无线投屏各版本成功率
    CastScreenPassQuery = "select version,count(result) as passcount from UITESTRESULT where taskname = '投屏' and result = 'pass' group by version"
    cursor.execute(CastScreenPassQuery)
    CastScreenPassCount = cursor.fetchall()
    db.commit()

    # 无线投屏失败原因占比
    CastScreenFailQuery = "select reason,count(result) as passcount from UITESTRESULT where taskname = '投屏' and result = 'Fail' group by reason"
    cursor.execute(CastScreenFailQuery)
    CastScreenFailReasonCount= cursor.fetchall()
    db.commit()


    # 查询智能家居
    # 智能家居各版本执行数量
    SmartHomeAllQuery= "select version,count(result) as count from UITESTRESULT where taskname = '智能家居' group by version order by version asc limit 10"
    cursor.execute(SmartHomeAllQuery)
    SmartHomeAllResult =cursor.fetchall()
    db.commit()

    # 智能家居各版本成功率
    SmartHomePassQuery = "select version,count(result) as passcount from UITESTRESULT where taskname = '智能家居' and result = 'pass' group by version"
    cursor.execute(SmartHomePassQuery)
    SmartHomePassCount = cursor.fetchall()
    db.commit()

    # 智能家居失败原因占比
    SmartHomeFailQuery = "select reason,count(result) as passcount from UITESTRESULT where taskname = '智能家居' and result = 'Fail' group by reason"
    cursor.execute(SmartHomeFailQuery)
    SmartHomeFailReasonCount= cursor.fetchall()
    db.commit()


    # 视频通话呼通率统计
    VideoCallPassRate = {} #定义dict
    VideoCallAllResult = dict(VideoCallAllResult)
    VideoCallPassCount = dict(VideoCallPassCount)

    for key in VideoCallAllResult:
        if(key != ''):
            tempRate =  "{:.3}".format((int(VideoCallPassCount[key])/int(VideoCallAllResult[key])))
            VideoCallPassRate[key] = float(tempRate)
    
    # 投屏成功率统计
    CastScreenPassRate = {} #定义dict
    CastScreenAllResult = dict(CastScreenAllResult)
    CastScreenPassCount = dict(CastScreenPassCount)

    for key in CastScreenAllResult:
        if(key != ''):
            tempRate =  "{:.3}".format((int(CastScreenPassCount[key])/int(CastScreenAllResult[key])))
            CastScreenPassRate[key] = float(tempRate)


    # 智能家居成功率统计
    SmartHomePassRate = {} #定义dict
    SmartHomeAllResult = dict(SmartHomeAllResult)
    SmartHomePassCount = dict(SmartHomePassCount)
    
    for key in SmartHomeAllResult:
        if(key != ''):
            if(SmartHomePassCount.__contains__(key)):                
                tempRate =  "{:.3}".format((int(SmartHomePassCount[key])/int(SmartHomeAllResult[key])))
                SmartHomePassRate[key] = float(tempRate)


    # 查询后关闭数据库
    cursor.close()#关闭游标
    db.close()#关闭db

    AlldataToDraw = dict(AllDispatchResult)

    # 调用环形图函数
    picRingMapName = sunboDrawing.DrawRingMap(AlldataToDraw,"特性执行占比(近"+TimeGap + "天)",False,"ALL")

    # 调用饼图函数
    # DrawPieMap(dataToDraw)

    # 调用水平柱状图
    picBarMapName = sunboDrawing.DrawHorizonBar(AlldataToDraw,"特性执行数量分布(近"+TimeGap + "天)")        

    #描绘视频通话的特性
    picVideoCallAllBarMapName = sunboDrawing.DrawVerticalBar(VideoCallAllResult,"视频通话呼叫次数","执行次数--Top10","VCAll")

    #描绘视频通话呼通率    
    picVideoCallPassRateMapName = sunboDrawing.DrawVerticalBar(VideoCallPassRate,"视频通话呼通成功率","成功率","VCRate")

    # 视频通话失败原因占比
    picVideoCallFailReasonMapName = sunboDrawing.DrawRingMap(VideoCallFailReasonCount,"视频通话呼叫失败原因分布图",True,"VCFail")


    #描绘投屏的特性
    picCastScreenAllBarMapName = sunboDrawing.DrawVerticalBar(CastScreenAllResult,"投屏次数","执行次数--Top10","CSAll")

    #描绘投屏呼通率    
    picCastScreenPassRateMapName = sunboDrawing.DrawVerticalBar(CastScreenPassRate,"投屏成功率","成功率","CSRate")

    # 投屏失败原因占比
    picCastScreenFailReasonMapName = sunboDrawing.DrawRingMap(CastScreenFailReasonCount,"投屏失败原因分布图",True,"CSFail")


    #描绘智家登录的特性
    picSmartHomeAllBarMapName = sunboDrawing.DrawVerticalBar(SmartHomeAllResult,"智家登录执行数量总计","执行次数--Top10","SHAll")

    #描绘智家登录呼通率    
    picSmartHomePassRateMapName = sunboDrawing.DrawVerticalBar(SmartHomePassRate,"智家登录成功率","成功率","SHRate")

    # 智家登录失败原因占比
    picSmartHomeFailReasonMapName = sunboDrawing.DrawRingMap(SmartHomeFailReasonCount,"智家登录失败原因分布图",True,"SHFail")

    # plt.show()
    # 邮件内容
    # 抬头
    contentToSend ='<html><body><div style="width:100%; border: 1px solid black;"><div style="font-weight:bold;font-size:x-large;text-align:center;width:100%;border: 1px solid black;">EMUI HomeVision测试自动化防护网 - 关键功能近期测试报告</div><div style="font-size:x-medium;text-align:center;width:100%;border: 1px solid black;">消费者BG IoT软件解决方案测试部（联系人：孙博 s00383796）<i>----此报告自动生成</i>'
    weekly = False
    if(weekly):
        contentToSend += '</div></div><div style="font-weight:bold;width:100%;border: 1px solid black;">自动化用例执行数量:<font color="red">'+str(AllCount)+'</font>条(近'+TimeGap+'天)，'+'<font color="red">'+str(TotalCount)+'</font>条(全部)'+'</div><div><img src="cid:img1"><img src="cid:img2"><img src="cid:img3"></div><div style="font-weight:bold;width:100%;border: 1px solid black;">视频通话特性专项</div><div><img src="cid:img4"><img src="cid:img5"><img src="cid:img6"></div><div style="font-weight:bold;width:100%;border: 1px solid black;">无线投屏特性专项</div><div><img src="cid:img7"><img src="cid:img8"><img src="cid:img9"></div><div style="font-weight:bold;width:100%;border: 1px solid black;">智能家居特性专项</div><div><img src="cid:img10"><img src="cid:img11"><img src="cid:img12"></div>'
        imglist = [
        picNameAllPassFail,
        picRingMapName,
        picBarMapName,
        picVideoCallAllBarMapName,
        picVideoCallPassRateMapName,
        picVideoCallFailReasonMapName,
        picCastScreenAllBarMapName,
        picCastScreenPassRateMapName,
        picCastScreenFailReasonMapName,
        picSmartHomeAllBarMapName,
        picSmartHomePassRateMapName,
        picSmartHomeFailReasonMapName
    ]
    else:
        contentToSend += '</div></div><div style="font-weight:bold;width:100%;border: 1px solid black;">自动化用例执行数量:<font color="red">'+str(AllCount)+'</font>条(近'+TimeGap+'天)，'+'<font color="red">'+str(TotalCount)+'</font>条(全部)'+'<div style="font-weight:bold;width:100%;border: 1px solid black;background-color:#ded0b0;">视频通话特性专项</div><div><img src="cid:img1"><img src="cid:img2"><img src="cid:img3"></div><div style="font-weight:bold;width:100%;border: 1px solid black;background-color:#ded0b0;">无线投屏特性专项</div><div background-color:#000000;><img src="cid:img4"><img src="cid:img5"><img src="cid:img6"></div><div style="font-weight:bold;width:100%;border: 1px solid black;background-color:#ded0b0;">智能家居特性专项</div><div background-color:#ffffff;><img src="cid:img7"><img src="cid:img8"><img src="cid:img9"></div><div style="font-weight:bold;width:100%;border: 1px solid black;background-color:#ded0b0;">重点问题单</div><div background-color:#ffffff>问题单</div>'
        imglist = [
        picVideoCallAllBarMapName,
        picVideoCallPassRateMapName,
        picVideoCallFailReasonMapName,
        picCastScreenAllBarMapName,
        picCastScreenPassRateMapName,
        picCastScreenFailReasonMapName,
        picSmartHomeAllBarMapName,
        picSmartHomePassRateMapName,
        picSmartHomeFailReasonMapName
    ]
        
    #图片设计： 
    # img1-3：概览，PassFail率，执行数量分布
    # img4-6：视频通话执行数，呼通率，失败原因
    # img7-9：无线投屏执行数，成功率，失败原因
    # img10-12：智能家居执行数，成功率，失败原因

    # 发送邮件
    

if __name__ == "__main__":
    process()