美文网首页自动化框架
Python+Flask项目实战：接口测试数据收集平台

Python+Flask项目实战：接口测试数据收集平台

作者: 西瓜加糖 | 来源:发表于2019-03-03 16:55 被阅读0次

背景

Boss：那谁，收集汇总下每个小组把接口测试情况，做个页面展示下。
我： (＃ﾟдﾟﾒ)
话说作为一个萌萌的测试，我为啥又收到这种要求。然而，反抗不得只好 走起~

需求分析

当然不能Boss随便一句话，我们就埋头苦干，毕竟曾经也吃过亏，啥都不清楚没想明白就开干，结果做得又墨迹， 成果却也四不像的经历，说多了都是泪，后人谨记。

跟BOSS明确下要求，然后确定下使用的开发工具与模块，理一下流程，确定下主要函数名称与功能，如下

创建数据库与表
  • 创建数据库：create_database，库名data_collection_platform
  • 创建表：create_table，表data存上报数据，表report统计数据
  • data：系统名称、用例名称、请求名称、请求结果、执行耗时、日期
  • report：系统名称、用例数量、请求数量、成功数、失败数、成功率、执行耗时、日期
使用python3.6提供一个可调用的接口，用于各组上传各系统每个用例执行情况
  • 接口datacollect：POST
将以上数据保存到mysql数据库
  • 连接数据库：init_database
  • 插入数据：insert_database
  • 查询数据：select_data
新建另一个表统计接口测试执行情况
  • 更新report表：update_report
提供一个可调用的接口，用于展示报告，要求html展示
  • 接口datareport：GET
  • html模板：datareport（聚合报告）、datasearch（查询功能）、datasys（按系统查询）

开发流程

框架选择

python框架，有Flask ， Django，Pyramid，啥啥的，当然他们的区别百度可以搜到一箩筐
而我选择Flask的原因是，名字比较好听。。。(〃'▽'〃)

本次用到的模块
import flask      #框架
from flask import jsonify,request,render_template      #请求与报告
import datetime,time     #时间
import pymysql     #mysql数据库处理
入口
server = flask.Flask(__name__)
if __name__ == '__main__':
 #port可以指定端口，默认端口是5000
 #host写成0.0.0.0的话，其他人可以访问，代表监听多块网卡上面，默认是127.0.0.1
    create_database()       #创建数据库
    create_table()          #创建表
    server.run(debug=True, port=8899, host='0.0.0.0')

创建数据库与表

此次是用本地搭建的mysql进行开发，搭建方法请左转自行了解。
库名data_collection_platform

def create_database():#创建数据库
    try:
        conn, cursor = init_database()       
        cursor.execute('create database if NOT EXISTS data_collection_platform default character set utf8 COLLATE utf8_general_ci')
        conn.close()
        return True
    except:
        print('Error:创建数据库失败')
        return None

详细表data,统计表report

def create_table():#创建表详细表data,统计表report
    try:
        conn, cursor = init_database()
        cursor.execute('use data_collection_platform')
        cursor.execute('create table if NOT EXISTS data(System varchar(255),CaseName varchar(255), RequestName varchar(255), Result int(5), Time float(5,2), Date datetime,PRIMARY KEY (System,Date,RequestName))') #联合主键
        cursor.execute('create table if NOT EXISTS report(System varchar(255),CaseCount int(6), RequestCount int(6), Seccess int(6) default 0, Fail int(6) default 0,Rate int(5) default 1,Time float(5,2),Date date)')
        cursor.execute('SET SQL_SAFE_UPDATES = 0') #设置可以无条件update，用于计算成功率Rate
        conn.close()
        return True
    except:
        print('Error:创建表失败')
        return None

提供可调用接口，上传各小组的接口数据

获取datacollect接口数据，得到插入data表的sql，用于数据库操作
接口传值正确返回200，错误400

@server.route('/datacollect', methods=['post'])
def datacollect():
#判断接口的请求方式是GET还是POST,POST传值，GET获取统计
    if request.method == 'POST':
        # 获取请求参数是json格式，返回结果是字典
        #params = request.json
        system = request.values.get('system')
        caseName = request.values.get('caseName')
        requestName = request.values.get('requestName')
        result = request.values.get('result')
        time = request.values.get('time')
        date = request.values.get('date')        
        if (len(system)> 0 )&(len(caseName)> 0 )&(len(requestName)> 0 )&(len(result)> 0 )&(len(time)> 0 )&(len(date)> 0 ):      #判断不为空，则写入数据库
            sql_insert = 'insert into data value(%s,%s,%s,%s,%s,%s)'
            insert_database(sql_insert,system,caseName,requestName,result,time,date)
            return jsonify({"code": 200, "mesg": "Data upload resultful."})            
        else:
            return jsonify({"code": 400, "mesg": "Incomplete data , please check."})

更新report表的sql，用于数据库操作

#写入数据后更新统计表
            sql_update = ("insert into report(System,CaseCount,"
            "RequestCount,Seccess,Fail,Rate,Time,Date)"
            "select a.System,a.CaseCount,b.RequestCount,"
            "(CASE WHEN c.Seccess is NULL THEN 0 ELSE c.Seccess END),"
            "(CASE WHEN d.Fail is NULL THEN 0 ELSE d.Fail END),"
            "c.Seccess/(c.Seccess+d.Fail) as Rate,"
            "e.Time,date_format(a.tDate,'%Y-%m-%d') as Date from "
            "((((select System,count(distinct CaseName) as CaseCount,"
            "date_format(Date,'%Y-%m-%d') as tDate from data group by tDate,System asc) a "
            "left outer join"
            "(select System,count(*) as RequestCount,date_format(Date,'%Y-%m-%d') "
            "as tDate from (select System,casename,date from data group by Date,System,RequestName) a "
            "group by tDate,System asc) b "
            "on a.System=b.System and a.tDate=b.tDate) "
            "left outer join "
            "(select System,count(*) as Seccess,date_format(Date,'%Y-%m-%d') as "
            "tDate from data where Result = 1 group by tDate,System asc) c "
            "on a.System=c.System and a.tDate=c.tDate) "
            "left outer join "
            "(select System,count(*) as Fail,date_format(Date,'%Y-%m-%d') as "
            "tDate from data where Result = 0 group by tDate,System asc) d "
            "on a.System=d.System and a.tDate=d.tDate) "
            "left outer join "
            "(select System,sum(Time) as Time,date_format(Date,'%Y-%m-%d') as "
            "tDate from data group by tDate,System asc) e "
            "on a.System=e.System and a.tDate=e.tDate "
            "group by Date,System asc")
            update_report(sql_update)

数据库相关操作

连接数据库

def init_database(): #连接数据库
    try:
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456',db='mysql', charset='gbk')
        cursor = conn.cursor()
        return conn,cursor
    except:
        print('Error:数据库连接失败')
        return None

插入数据

def insert_database(sql,*args):#插入数据
    conn, cursor = init_database()
    cursor.execute('use data_collection_platform')
    try:
        cursor.execute(sql,args)
        conn.commit()
        cursor.close()
        conn.close()
        return True
    except:
        print("Error:数据插入失败")
        return None

更新report统计表

def update_report(sql): #更新report统计表
    try:
        conn, cursor = init_database()
        cursor.execute('use data_collection_platform')
        cursor.execute('truncate table report')
        cursor.execute(sql)   
        cursor.execute('update report set Rate =  Seccess/(Seccess+Fail)*100') #成功或者失败数有可能为NULL，需要重新计算
        conn.commit()
        cursor.close()
        conn.close()
        return True
    except:
        print("Error:更新统计表失败")
        return None

查询数据用于html的查找功能

def select_data(sql, *args):#查询数据
    try:
        conn, cursor = init_database()
        cursor.execute('use data_collection_platform')
        cursor.execute(sql, args)   
        datas =  cursor.fetchall()
        cursor.close()
        conn.close()
        return datas
    except:
        print("Error:数据查找错误")
        return None

提供接口，展示HTML报告

原理是准备一个html的模板，然后把数据库查到相应数据，用% for i in n %插入到模板中
调接口datareport展示聚合报告

@server.route('/datareport', methods=['get'])
def datareport():  #结果展示
    if request.method == 'GET':             
        sql_search2 = 'select * from report  WHERE TO_DAYS(NOW()) - TO_DAYS(Date) <= 7 order by Date desc'  #展示7天report
        tmp2 = select_data(sql_search2)       
        sql_search3 = 'select sum(CaseCount),sum(RequestCount),sum(Seccess),sum(Fail),ROUND(sum(Seccess)/(sum(Seccess)+sum(Fail))*100,0),sum(Time) from report WHERE TO_DAYS(NOW()) - TO_DAYS(Date) <= 7 '  #展示所有系统的7天内汇总
        tmp3 = select_data(sql_search3)
        return render_template('data_collect_report.html',n=tmp2,m=tmp3) #生成html报告

data_collect_report.html是模板名（保存在.py文件平级的templates文件夹下）

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>接口测试报告</title>
  <style>
    body {
      background-color: #f2f2f2;
      color: #333;
      margin: 0 auto;
      width: 960px;
    }
    #summary {
      width: 960px;
      margin-bottom: 20px;
    }
    #summary th {
      background-color: skyblue;
      padding: 5px 12px;
    }
    #summary td {
      background-color: lightblue;
       text-align: center; <!-- -->
      padding: 4px 8px;
      }
    #details {
      width: 960px;
      margin-bottom: 20px;
    }
    #details th {
      background-color: skyblue;
      padding: 5px 12px;
    }
    #details td {
      background-color: lightblue;
       text-align: center; <!-- -->
      padding: 4px 8px;      
    }

  </style>
</head>
<body>
  <h1>测试报告</h1>
  <h2>汇总-7天内</h2>
    <table id="summary" border="1" cellspacing="0">
    <table class="table table-bordered" cellspacing="0" border="1" width="800px">
    <tr>
        <th bgcolor="skyblue">系统名称</th>
        <th bgcolor="skyblue">用例数量</th>
        <th bgcolor="skyblue">请求数量</th>
        <th bgcolor="skyblue">成功数</th>
        <th bgcolor="skyblue">失败数</th>        
        <th bgcolor="skyblue">成功率</th>
        <th bgcolor="skyblue">执行耗时(s)</th>
    </tr>
        {% for t in m %}
            <tr>
                <td>所有系统</td>
                <td>{{ t[0] }}</td>                
                <td>{{ t[1] }}</td>
                <td>{{ t[2] }}</td>
                <td>{{ t[3] }}</td>
                <td>{{ t[4] }}%</td>
                <td>{{ t[5] }}</td>               
            </tr>
    {% endfor %}
    </table>

  <p></p>
  <label>系统：<input type="text" placeholder="PSS-ESALES-PFS" id="sysObj" style="height:18px"></label>
  <label>起始时间：<input type="text" placeholder="2018-01-01" id="startTime" style="height:18px"></label>
  <label>终止时间：<input type="text" placeholder="2018-12-31" id="endTime" style="height:18px"></label>
    <a href="javascript:;" ><button type="Search" id="searchBtn" style="height:23px"> 查找 </button></a>

<h2>详细-7天内</h2>
  <table id="details" border="1" cellspacing="0">
    <table class="table table-bordered" cellspacing="0" border="1" width="800px">
    <tr>
        <th bgcolor="skyblue">系统名称</th>
        <th bgcolor="skyblue">用例数量</th>
        <th bgcolor="skyblue">请求数量</th>
        <th bgcolor="skyblue">成功数</th>
        <th bgcolor="skyblue">失败数</th> 
        <th bgcolor="skyblue">成功率</th>
        <th bgcolor="skyblue">执行耗时(s)</th>
        <th bgcolor="skyblue">日期</th>
    </tr>
        {% for i in n %}
            <tr>
                <td><a href="javascript:;" class="sysDetail" title="查询对应时间的执行情况"> {{ i[0] }} </a></td> 
                <td>{{ i[1] }}</td>
                <td>{{ i[2] }}</td>
                <td>{{ i[3] }}</td>
                <td>{{ i[4] }}</td>
                <td><a class="Rate">{{ i[5] }}% </a></td>  
                <td>{{ i[6] }}</td>
                <td><a class="choiceTime"> {{ i[7] }} </a></td>              
            </tr>
    {% endfor %}
    </table>

    <script>
        (function () {
            var search = document.querySelector('#searchBtn')
            search.addEventListener('click', function () {
                var sysVal = document.querySelector('#sysObj').value,
                    startTime = document.querySelector('#startTime').value,
                    endTime = document.querySelector('#endTime').value,
                    url = '/datasearch?System='+sysVal+'&Before='+startTime+'&After='+endTime
                
                window.location.href = url
                
            })
            
             var searchsys = document.querySelectorAll('.sysDetail')
             var choiceTime = document.querySelectorAll('.choiceTime')                
             for(var i = 0, len = searchsys.length; i < len; i ++) {
                (function (i) {
                    searchsys[i].addEventListener('click', function () {
                    console.log(searchsys[i],choiceTime[i])
                        var sysVal2 = searchsys[i].innerHTML.trim(),
                            chTime = choiceTime[i].innerHTML.trim(),
                            
                            url = '/datasys?System='+sysVal2+'&Date='+chTime

                        self.location.href  = url 
                    })
                })(i)
                
             }
             
            function toPercent(point){
            var str=Number(point*100).toFixed(1);
            str+="%";
            return str;
            }
                
        })()
           
       
    </script>
   
</html>

优化报告，提供查询和点系统名跳转功能

查找功能，模板data_collect_search.html，关注function()的用法

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Interface Test Results</title>
  <style>
    body {
      background-color: #f2f2f2;
      color: #333;
      margin: 0 auto;
      width: 960px;
    }
    #summary {
      width: 960px;
      margin-bottom: 20px;
    }
    #summary th {
      background-color: skyblue;
      padding: 5px 12px;
    }
    #summary td {
      background-color: lightblue;
       text-align: center; <!-- -->
      padding: 4px 8px;
      }
    #details {
      width: 960px;
      margin-bottom: 20px;
    }
    #details th {
      background-color: skyblue;
      padding: 5px 12px;
    }
    #details td {
      background-color: lightblue;
       text-align: center; <!-- -->
      padding: 4px 8px;      
    }

  </style>
</head>
<body>
  <h1>测试报告</h1>
  <h2>汇总-7天内</h2>
    <table id="summary" border="1" cellspacing="0">
    <table class="table table-bordered" cellspacing="0" border="1" width="800px">
    <tr>
        <th bgcolor="skyblue">系统名称</th>
        <th bgcolor="skyblue">用例数量</th>
        <th bgcolor="skyblue">请求数量</th>
        <th bgcolor="skyblue">成功数</th>
        <th bgcolor="skyblue">失败数</th>        
        <th bgcolor="skyblue">成功率</th>
        <th bgcolor="skyblue">执行耗时(s)</th>
    </tr>
        {% for t in m %}
            <tr>
                <td>所有系统</td>
                <td>{{ t[0] }}</td>                
                <td>{{ t[1] }}</td>
                <td>{{ t[2] }}</td>
                <td>{{ t[3] }}</td>
                <td>{{ t[4] }}%</td>
                <td>{{ t[5] }}</td>               
            </tr>
    {% endfor %}
    </table>

  <p></p>
  <label>系统：<input type="text" placeholder="PSS-ESALES-PFS" id="sysObj" style="height:18px"></label>
  <label>起始时间：<input type="text" placeholder="2018-01-01" id="startTime" style="height:18px"></label>
  <label>终止时间：<input type="text" placeholder="2018-12-31" id="endTime" style="height:18px"></label>
    <a href="javascript:;" ><button type="Search" id="searchBtn" style="height:23px"> 查找 </button></a>

<h2>详细-7天内</h2>
  <table id="details" border="1" cellspacing="0">
    <table class="table table-bordered" cellspacing="0" border="1" width="800px">
    <tr>
        <th bgcolor="skyblue">系统名称</th>
        <th bgcolor="skyblue">用例数量</th>
        <th bgcolor="skyblue">请求数量</th>
        <th bgcolor="skyblue">成功数</th>
        <th bgcolor="skyblue">失败数</th> 
        <th bgcolor="skyblue">成功率</th>
        <th bgcolor="skyblue">执行耗时(s)</th>
        <th bgcolor="skyblue">日期</th>
    </tr>
        {% for i in n %}
            <tr>
                <td><a href="javascript:;" class="sysDetail" title="查询对应时间的执行情况"> {{ i[0] }} </a></td> 
                <td>{{ i[1] }}</td>
                <td>{{ i[2] }}</td>
                <td>{{ i[3] }}</td>
                <td>{{ i[4] }}</td>
                <td><a class="Rate">{{ i[5] }}% </a></td>  
                <td>{{ i[6] }}</td>
                <td><a class="choiceTime"> {{ i[7] }} </a></td>              
            </tr>
    {% endfor %}
    </table>

    <script>
        (function () {
            var search = document.querySelector('#searchBtn')
            search.addEventListener('click', function () {
                var sysVal = document.querySelector('#sysObj').value,
                    startTime = document.querySelector('#startTime').value,
                    endTime = document.querySelector('#endTime').value,
                    url = '/datasearch?System='+sysVal+'&Before='+startTime+'&After='+endTime
                window.location.href = url
            })
            
             var searchsys = document.querySelectorAll('.sysDetail')
             var choiceTime = document.querySelectorAll('.choiceTime')                
             for(var i = 0, len = searchsys.length; i < len; i ++) {
                (function (i) {
                    searchsys[i].addEventListener('click', function () {
                    console.log(searchsys[i],choiceTime[i])
                        var sysVal2 = searchsys[i].innerHTML.trim(),
                            chTime = choiceTime[i].innerHTML.trim(),
                            url = '/datasys?System='+sysVal2+'&Date='+chTime
                        self.location.href  = url 
                    })
                })(i)
             }
            function toPercent(point){
            var str=Number(point*100).toFixed(1);
            str+="%";
            return str;
            }
        })()
    </script>
</html>

点击系统名跳转的功能

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Interface Test Results</title>
  <style>
    body {
      background-color: #f2f2f2;
      color: #333;
      margin: 0 auto;
      width: 960px;
    }
    #summary {
      width: 960px;
      margin-bottom: 20px;
    }
    #summary th {
      background-color: skyblue;
      padding: 5px 12px;
    }
    #summary td {
      background-color: lightblue;
       text-align: center; <!-- -->
      padding: 4px 8px;
      }
    #details {
      width: 960px;
      margin-bottom: 20px;
    }
    #details th {
      background-color: skyblue;
      padding: 5px 12px;
    }
    #details td {
      background-color: lightblue;
       text-align: center; <!-- -->
      padding: 4px 8px;      
    }

  </style>
</head>
<body>

  <h2>执行情况-7天内</h2>
  <table id="details" border="1" cellspacing="0">
    <table class="table table-bordered" cellspacing="0" border="1" width="800px">
    <tr>
        <th bgcolor="skyblue">系统名称</th>
        <th bgcolor="skyblue">用例名称</th>
        <th bgcolor="skyblue">请求名称</th>
        <th bgcolor="skyblue">请求结果</th>
        <th bgcolor="skyblue">执行耗时(s)</th>
        <th bgcolor="skyblue">日期</th>
    </tr>
           {% for i in u %} 
            <tr>
                <td><a href="javascript:;" class="sysDetail" title="查询7天内执行情况"> {{ i[0] }} </a></td> 
                <td>{{ i[1] }}</td>
                <td>{{ i[2] }}</td>
                <td>{{ i[3] }}</td>
                <td>{{ i[4] }}</td>
                <td><a class="choiceTime"> {{ i[5] }} </a></td>               
            </tr>
    {% endfor %}
    </table>
</body>    
  <script>
        (function () {            
             var searchsys = document.querySelectorAll('.sysDetail')               
             for(var i = 0, len = searchsys.length; i < len; i ++) {
                (function (i) {
                    searchsys[i].addEventListener('click', function () {
                        var sysVal2 = searchsys[i].innerHTML.trim(),                           
                            url = '/datasys?System='+sysVal2+'&Date='
                        self.location.href  = url 
                    })
                })(i)
             }          
        })()
    </script>
</html>

结语

到此一个数据收集平台，已经完成了，是不是嗖easy呢
感谢阅读~

相关文章

网友评论

    本文标题：Python+Flask项目实战：接口测试数据收集平台

    本文链接：https://www.haomeiwen.com/subject/pbgpuqtx.html

    延伸阅读

    深度阅读

    • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

    栏目导航

    热点阅读

    自动化框架

    关于我们|服务条款|联系我们|Python+Flask项目实战：接口测试数据收集平台|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！