学习笔记——测试进阶之路 工作笔记:基于微信 minium 框架的小程序自动化初步实践(4)

大海 · 2022年06月26日 · 4145 次阅读

创建数据库和数据表

数据库:XXXXX

数据表:device

存储数据信息

SQL 语句如下
CREATE TABLE `device` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `execid` int(11) NOT NULL,
  `device_number` int(11) DEFAULT NULL,
  `project_name` varchar(255) DEFAULT NULL,
  `case_field` varchar(255) DEFAULT NULL,
  `case_content` varchar(255) DEFAULT NULL,
  `triggertime` datetime DEFAULT NULL,
  `showtime` datetime DEFAULT NULL,
  `offlinetime` datetime DEFAULT NULL,
  `starttime` datetime DEFAULT NULL,
  `endtime` datetime DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=gb18030;

创建 Flask 服务

新建 db_func.py
# !/usr/bin/python
# -*- coding: utf-8 -*-

"""
@Author  : Charles
@File    : db_func.py
@Create Time: 2022-06-21 09:18
@Description:
"""

import time
import pymysql



def connect_db():
    url = "127.0.0.1"
    name = "root"
    pwd = "123456"
    dataBase = "XXXXX"
    return pymysql.connect(host=url, port=3306, user=name, passwd=pwd, db=dataBase)

def query_database(db, sql):
    cursor = db.cursor()
    try:
        if isinstance(sql, str):
            cursor.execute(sql)
            result = list(cursor.fetchall())
        else:
            result = []
            for sq in sql:
                cursor.execute(sq)
                result.append(list(cursor.fetchall()))
    except Exception as err:
        result = ''.join(('An db query exception happened: ', str(err)))
    # db.close()  # 关闭数据库连接
    return result

def update_db(db, sql):
    cursor = db.cursor()
    try:
        if isinstance(sql, str):
            cursor.execute(sql)
            db.commit()
        else:
            print('sql 不是一个合格的字符串:{}'.format(sql))
    except Exception as err:
        result = ''.join(('An db update exception happened: ', str(err)))
        db.rollback()
        print(result)

# 数据库数据插入更新
def db_insert(db, sql):
    cursor = db.cursor()
    i = 0
    try:
        cursor.execute(sql)
        db.commit()
        result = 'db insert success'
    except Exception as err:
        db.rollback()
        result = 'An db insert exception happened: ' + str(err) + '    ' + str(i)
    db.close()  # 关闭数据库连接
    return result

def close_db(db):
    try:
        db.close()
    except Exception as err:
        result = ''.join(('An db closed exception happened: ', str(err)))

def get_current_time():
    return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

新建 project_index.py
# !/usr/bin/python
# -*- coding: utf-8 -*-

"""
@Author  : Charles
@File    : project_index.py
@Create Time: 2022-06-21 09:53
@Description:

"""


import datetime
import time
from flask import *
from common import db_func


app = Flask(__name__)


@app.route('/')
def display():
    """
    最新一轮回归测试的整体页面展示
    :return:
    """
    db = db_func.connect_db()

    # 如果实时数据正常显示,就把设备掉线时间默认设置为"1970-01-01 08:00:00" ,实时数据加载时长=数据显示时间-设备被触发时间
    # 如果设备显示掉线页面,就把实时数据时间默认设置为"1970-01-01 08:00:00" ,实时数据加载时长=设备掉线时间-设备被触发时间
    sql_all = 'SELECT device_number, project_name, case_field, case_content, ' \
              '(CASE WHEN offlinetime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,showtime) ' \
              'WHEN showtime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,offlinetime) else 0 ' \
              'END) as duration, triggertime, showtime, offlinetime, starttime, endtime, status FROM device ' \
              'where execid in (select max(execid) from device) ;'


    # 如果实时数据正常显示,就把设备掉线时间默认设置为"1970-01-01 08:00:00" ,实时数据加载时长=数据显示时间-设备被触发时间
    # 如果设备显示掉线页面,就把实时数据时间默认设置为"1970-01-01 08:00:00" ,实时数据加载时长=设备掉线时间-设备被触发时间
    sql_fail = 'SELECT device_number, project_name, case_field, case_content, ' \
               '(CASE WHEN offlinetime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,showtime) ' \
               'WHEN showtime ="1970-01-01 08:00:00" THEN TIMESTAMPDIFF(SECOND,triggertime,offlinetime) else 0 ' \
               'END) as duration, starttime, endtime, status FROM device ' \
               'where status="fail" and execid in (select max(execid) from device) ;'

    pass_total_duration = 'SELECT CAST(SUM(TIMESTAMPDIFF(SECOND,triggertime,showtime)) AS CHAR) AS duration FROM device ' \
                          'WHERE case_field="当前楼层" AND STATUS="pass" AND TIMESTAMPDIFF(SECOND,triggertime,showtime) <= "10" ' \
                          'AND execid IN (SELECT MAX(execid) FROM device) '

    fail_total_duration = 'SELECT CAST(SUM(TIMESTAMPDIFF(SECOND,triggertime,offlinetime)) AS CHAR) AS duration FROM device ' \
                          'WHERE case_field="当前楼层" AND STATUS="fail" AND TIMESTAMPDIFF(SECOND,triggertime,offlinetime) > "10" ' \
                          'AND execid IN (SELECT MAX(execid) FROM device) '

    device_total_number = 'SELECT COUNT(*) FROM device WHERE case_field="当前楼层" AND execid IN (SELECT MAX(execid) FROM device) '
    device_pass_number = 'SELECT COUNT(*) FROM device WHERE case_field="当前楼层" AND STATUS="pass" AND execid IN (SELECT MAX(execid) FROM device) '
    device_fail_number = 'SELECT COUNT(*) FROM device WHERE case_field="当前楼层" AND STATUS="fail" AND execid IN (SELECT MAX(execid) FROM device) '


    db_res_all = db_func.query_database(db, sql_all)
    de_res_fail = db_func.query_database(db, sql_fail)

    db_pass_total_duration = db_func.query_database(db, pass_total_duration)

    db_device_total_number = db_func.query_database(db, device_total_number)
    db_device_pass_number = db_func.query_database(db, device_pass_number)
    db_device_fail_number = db_func.query_database(db, device_fail_number)


    # Average Loading Time
    if db_device_pass_number[0][0] == 0:
        average_loading_time = "%.2f" % 0
    else:
        average_loading_time = "%.2f" % (int(db_pass_total_duration[0][0]) / int(db_device_pass_number[0][0]))
        print("Average_loading_time: ", average_loading_time)

    # Averavge Loading Time Less Than 10s
    if db_device_pass_number[0][0] == 0:
        average_less_than_10s = "%.2f" % 0
    else:
        average_less_than_10s = "%.2f" % (int(db_pass_lessthan_total_duration[0][0])/int(db_device_pass_number[0][0]))
        print("Averavge Loading Time Less Than 10s: ", average_less_than_10s)

    # Percentage of Loading Time Less Than 10s
    if db_device_pass_number[0][0] == 0:
        percentage_less_than_10s = "{:.2%}".format(0)
    else:
        percentage_less_than_10s = "{:.2%}".format(int(db_device_lessthan_pass_number[0][0])/int(db_device_pass_number[0][0]))
        print("Percentage of Loading Time Less Than 10s: ", percentage_less_than_10s)

    # Percentage of Loading Time Over 10s
    if db_device_pass_number[0][0] == 0:
        percentage_over_10s = "{:.2%}".format(0)
    else:
        percentage_over_10s = "{:.2%}".format(int(db_device_over_pass_number[0][0])/int(db_device_pass_number[0][0]))
        print("Percentage of Loading Time Over 10s: ", percentage_over_10s)

    # 获得SQL语句查询内容
    db_res = [average_loading_time, average_less_than_10s, percentage_less_than_10s, percentage_over_10s, db_res_fail, db_res_pass_over_10s, db_res_all]

    if db_res:
        return render_template("/koneview_page.html", content=db_res)
    else:
        pass



if __name__ == "__main__":
    app.run(host='xx.xx.xx.xx', port=5500, debug=True)

创建 HMTL 网页

新建 project_page.html
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>MyProject Monitor Platform</title>
    <link rel="icon" href="https://www.myproject.cn/zh/Images/myproject-logo-76x52_tcm156-8930.png">
    <script src="static/lib/jquery-1.11.1.min.js" type="text/javascript"></script>
    <div class="navi-brand">
        <a class="logo" onclick="zhugeTrack('顶栏按钮点击',{'按钮名称':'LOGO'});" href="https://www.myproject.com/en/" target="_blank"> <img src="/static/images/myprojectLOGO.png" style="text-align: left; width: 265px; height: 50px; position: absolute; left: 62px; top: 30px; margin-left: -60px;margin-top: -20px;"></a>
    </div>

    <style type="text/css">
        .bg{
            background-image: url("https://pic4.zhimg.com/80/v2-052324a9dae7a6feb2e5ddf6f68ad8c6_720w.jpg");
            background-repeat: repeat;
        }
    em{
        color: red;
        font-style: normal;
    }
   table{
        border-spacing: 0;
        width: 100%;
        border: 1px solid black;
   }

   thead th {
       border: 1px solid black;
       /*text-align: center;*/
       width: auto;
       color: blueviolet;

   }
    th{
        border: 1px solid black;
        text-align: left;
        width: auto;
        background-color: #69ABD6;
    }
    td{
        border: 1px solid black;
        text-align: left;
    }
    </style>


</head>
<body class="bg">
    <table style="margin-bottom: -1px" class="left"  id="table_top_header">
        <h1 align="center" style="color:#0071B9 ; font-size:30px">MyProject Monitor Platform</h1>
        <h3 align="left" style="color:#0071B9 ; font-size:20px">Basic Information</h3>
        <tr>
            <th>Owner</th>
            <th>Project Name</th>
            <th>Running Environment</th>
            <th>Running System</th>
            <th>Running Equipment</th>
            <th>Testing Phase</th>
            <th>Monitor Frequency</th>
            <th>Test Report</th>
        </tr>
        <tr>
            <td>Charles</td>
            <td>MyProject</td>
            <td>Production</td>
            <td>Windows 10 64bit</td>
            <td>Wechat DevTools</td>
            <td>Regression Test</td>
            <td>One day/time</td>
            <td><a style="background: #eee4a6" href="http://ip:port/" target="_blank">Minium</a></td>
        </tr>
    </table>

    <table style="margin-bottom: -1px" class="left"  id="table_analysis">
        <tbody>
        <h3 align="left" style="color:#0071B9 ; font-size:20px">LiveData Analysis</h3>
        <tr>
            <th>Average Loading Time(sec)</th>
            <th>Percentage of Loading Time Less Than 10s</th>
            <th>Percentage of Loading Time Over 10s</th>
        </tr>
        <div >
            <div id="ChangelistTable0">
                <tr>
                    <td  class="text-center" >
                        <div>
                            {% if content[2] %}
                                <span style="background: #e8e272"> <a>{{content[2]}}</a> </span>
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[3] %}
                                <span style="background: #7ad9f4"> <a>{{content[3]}}</a> </span>
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[4] %}
                                <span style="background: #e999d4"> <a>{{content[4]}}</a> </span>
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                </tr>
            </div>
        </div>
        </tbody>
    </table>

    <table style="margin-bottom: -1px" class="left"  id="table_header">
        <tbody>
        <h3 align="left" style="color:#0071B9 ; font-size:20px">TestCase Failed</h3>
        <tr>
            <th>No</th>
            <th>Device Number</th>
            <th>Project Name</th>
            <th>Monitor Field</th>
            <th>Live Data</th>
            <th>Duration(s)</th>
            <th>Latest Case Executed Time</th>
            <th>Latest Case Ended Time</th>
            <th>Latest Case Result</th>
        </tr>
        <div >
            <div id="ChangelistTable1">
                {% for i in range(content[1]| length) %}
                <tr>
                    <td>
                        {{ i+1 }}
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[1][i][0] %}
                            {{ content[1][i][0]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td>
                        <div>
                            {% if content[1][i][1] %}
                            {{ content[1][i][1]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[1][i][2] %}
                            {{ content[1][i][2]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[1][i][3] %}
                            {{ content[1][i][3]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[1][i][4] %}
                                {% if content[1][i][4]<=10 %}
                                    <span style="background: lightseagreen"> <a>{{content[1][i][4]}}</a> </span>
                                {% else %}
                                    <span style="background: lightcoral"> <a>{{content[1][i][4]}}</a></span>
                                {% endif  %}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[1][i][5] %}
                            {{ content[1][i][5]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[1][i][6] %}
                            {{ content[1][i][6]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[1][i][7] %}
                                {% if content[1][i][7]=='pass' %}
                                    <span style="background: mediumseagreen"> <a>PASS</a> </span>
<!--                                    <span style="background: #50d2c8"> <a href={{content[0][i][6]}} target="_blank">Pass</a> </span>-->
                                {% elif content[1][i][7]=='fail' %}
                                    <span style="background: orangered"> <a>FAIL</a> </span>
                            <!--        <span style="background: red"> <a href={{content[0][i][6]}} target="_blank">Fail</a> </span>-->
                                {% else %}
                                    <span style="background: sandybrown"> <a>Scheduled</a> </span>
                                {% endif  %}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                </tr>
                {% endfor %}
            </div>
        </div>
        </tbody>
    </table>

    <table style="margin-bottom: -1px" class="left"  id="table_list">
        <tbody>
        <h3 align="left" style="color:#0071B9 ; font-size:20px">TestCase Description</h3>
        <tr>
            <th>No</th>
            <th>Device Number</th>
            <th>Project Name</th>
            <th>Monitor Field</th>
            <th>Live Data</th>
            <th>Duration(s)</th>
            <th>Latest Triggered Time</th>
            <th>Latest Showed Time</th>
            <th>Latest Dropped Time</th>
            <th>Latest Case Executed Time</th>
            <th>Latest Case Ended Time</th>
            <th>Latest Case Result</th>
        </tr>
        <div >
            <div id="ChangelistTable">
                {% for i in range(content[0]| length) %}
                <tr>
                    <td>
                        {{ i+1 }}
                    </td>
                    <td  class="text-center" >
                        <div>{{ content[0][i][0]| safe }}</div>
                    </td>
                    <td>
                        <div>{{ content[0][i][1]| safe }}</div>
                    </td>
                    <td  class="text-center" >
                        <div>{{ content[0][i][2]| safe }}</div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][3] %}
                            {{ content[0][i][3]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][4] %}
                                {% if content[0][i][4]<=10 %}
                                    <span style="background: lightseagreen"> <a>{{content[0][i][4]}}</a> </span>
                                {% else %}
                                    <span style="background: lightcoral"> <a>{{content[0][i][4]}}</a></span>
                                {% endif  %}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][5] %}
                            {{ content[0][i][5]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][6] %}
                            {{ content[0][i][6]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][7] %}
                            {{ content[0][i][7]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][8] %}
                            {{ content[0][i][8]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][9] %}
                            {{ content[0][i][9]| safe }}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                    <td  class="text-center" >
                        <div>
                            {% if content[0][i][10] %}
                                {% if content[0][i][10]=='pass' %}
                                    <span style="background: mediumseagreen"> <a>PASS</a> </span>
<!--                                    <span style="background: #50d2c8"> <a href={{content[0][i][6]}} target="_blank">Pass</a> </span>-->
                                {% elif content[0][i][10]=='fail' %}
                                    <span style="background: orangered"> <a>FAIL</a> </span>
                            <!--        <span style="background: red"> <a href={{content[0][i][6]}} target="_blank">Fail</a> </span>-->
                                {% else %}
                                    <span style="background: sandybrown"> <a>Scheduled</a> </span>
                                {% endif  %}
                             {% else %}
                            {% endif  %}
                        </div>
                    </td>
                </tr>
                {% endfor %}
            </div>
        </div>
        </tbody>
    </table>
</body>
</html>

在线查看报告

打开http://IP:PORT/,在线查看网页报告

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
暂无回复。
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册