【Flask】测试平台开发，数据看板开发-第二十一篇

概述：

在前面我们已经实现了我们的产品创建管理，应用管理管理，需求提测管理但是每周提测了多少需求，创建了哪些产品，我们是不是看着不是很直观，接下来我们就需要开发一个数据看板功能，实现能够看到产品下创建的需求，提测数据

先看看实现效果

后端接口源码：

# -*- coding:utf-8 -*-
# application.py
import datetime
import traceback

from flask import Blueprint, current_app
from dbutils.pooled_db import PooledDB
from apis.product import connectDB
from configs import config, format
from flask import request
import pymysql.cursors
import json
# from utils.jwt import login_required

# 使用数据库连接池的方式链接数据库，提高资源利用率
pool = PooledDB(pymysql, mincached=2, maxcached=5, host=config.MYSQL_HOST, port=config.MYSQL_PORT,
                user=config.MYSQL_USER, passwd=config.MYSQL_PASSWORD, database=config.MYSQL_DATABASE,
                cursorclass=pymysql.cursors.DictCursor)

test_dashboard = Blueprint("test_dashboard", __name__)


@test_dashboard.route("/api/dashboard/stacked", methods=['POST'])
def get_request_stacked():
    connection = None
    try:
        connection = pool.connection()
        with connection.cursor() as cursor:
            sql_select = '''
                SELECT DATE_FORMAT(request.createDate,"%Y%u") weeks, 
                       apps.note, 
                       COUNT(apps.id) counts 
                FROM request 
                LEFT JOIN apps ON request.appId = apps.id 
                GROUP BY weeks, apps.note;
            '''
            cursor.execute(sql_select)
            table_data = cursor.fetchall()  # 数据库返回的结果，包含 note 为 NULL 的行

        # === 核心修复：处理 NULL 值 ===
        weeks = []
        notes = []
        key_value = {}
        for row in table_data:
            # 1. 处理 weeks 可能为 NULL 的情况（如 createDate 为 NULL 时）
            week = row['weeks'] or 'No_Week'  # 转为默认字符串
            # 2. 处理 note 为 NULL 的情况（关键修复！）
            note = row['note'] or 'No_App'    # 将 NULL 转为 'Unknown_App'
            counts = row['counts'] or 0            # 确保 counts 不为 NULL

            # 后续逻辑保持不变，但使用处理后的 week 和 note
            if week not in weeks:
                weeks.append(week)
            if note not in notes:
                notes.append(note)
            # 使用处理后的 week 和 note 拼接键名，避免 NULL 导致的 TypeError
            key_value[f"{week}_{note}"] = counts  # 建议用下划线分隔，避免歧义（如 week=202534, note=23 变为 20253423）

        weeks.sort()  # 排序周数

        # 生成 series 数据（保持不变，但 note 已无 NULL）
        series = {}
        for note in notes:
            series[note] = []
            for week in weeks:
                # 使用处理后的键名（带下划线）
                series[note].append(key_value.get(f"{week}_{note}", 0))

        resp_data = {
            'weeks': weeks,
            'notes': notes,  # 已包含处理后的 'Unknown_App'
            'series': series
        }

        resp = format.resp_format_success
        resp['data'] = resp_data
        return resp

    except Exception as e:
        # current_app.logger.error(f"Error in get_request_stacked: {str(e)}")  # 记录错误日志，方便调试
        resp = format.resp_format_error
        resp['message'] = "Failed to process stacked data"
        return resp, 500
    finally:
        if connection:
            connection.close()  # 释放连接


from datetime import datetime  # 正确的导入方式
@test_dashboard.route("/api/dashboard/metadata", methods=['POST'])
def get_request_stacked_metadata():
    connection = None
    try:
        # === 1. 解析请求体 ===
        if not request.data:
            return {"code": 40001, "message": "Request body is empty", "data": [], "total": 0}, 400

        body = request.get_json()
        if body is None:
            return {"code": 40002, "message": "Invalid JSON format", "data": [], "total": 0}, 400

        current_app.logger.info(f"Request body: {body}")

        date_range = body.get('date', [])
        start_date_param = body.get('start_date')
        end_date_param = body.get('end_date')

        # 初始化变量
        start_date = None
        end_date = None
        valid = False

        # === 2. 日期参数处理 ===
        if date_range and len(date_range) == 2:
            start_str, end_str = date_range[0], date_range[1]
            date_format = '%Y-%m-%d %H:%M:%S'
            try:
                # 使用正确的 datetime.datetime.strptime
                datetime.strptime(start_str, date_format)
                datetime.strptime(end_str, date_format)
                if start_str <= end_str:
                    start_date = start_str
                    end_date = end_str
                    valid = True
                    current_app.logger.info(f"Valid date range: {start_date} to {end_date}")
            except ValueError:
                current_app.logger.warning("Invalid date format in date_range")
                valid = False

        elif start_date_param and end_date_param:
            date_format = '%Y-%m-%d %H:%M:%S'
            try:
                datetime.strptime(start_date_param, date_format)
                datetime.strptime(end_date_param, date_format)
                if start_date_param <= end_date_param:
                    start_date = start_date_param
                    end_date = end_date_param
                    valid = True
                    current_app.logger.info(f"Valid date params: {start_date} to {end_date}")
            except ValueError:
                current_app.logger.warning("Invalid date format in start_date/end_date")
                valid = False
        else:
            current_app.logger.info("No date filter applied, querying all data")

        # === 3. 构建SQL查询 ===
        connection = pool.connection()
        with connection.cursor() as cursor:

            # 临时禁用ONLY_FULL_GROUP_BY
            try:
                cursor.execute("SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))")
            except Exception as mode_error:
                current_app.logger.warning(f"Could not modify SQL mode: {mode_error}")

            if valid and start_date and end_date:
                # 带日期过滤的查询
                sql = """
                      SELECT CONCAT(YEAR(r.createDate), '-', LPAD(WEEK(r.createDate), 2, '0')) as week_key, \
                             COALESCE(a.note, 'No_App')                                   AS app_name, \
                             COUNT(*)                                                          as request_count
                      FROM request r
                               LEFT JOIN apps a ON r.appId = a.id
                      WHERE r.createDate BETWEEN %s AND %s
                      GROUP BY YEAR (r.createDate), WEEK(r.createDate), a.note
                      ORDER BY week_key, app_name \
                      """
                current_app.logger.info(f"Executing filtered query: {start_date} to {end_date}")
                cursor.execute(sql, (start_date, end_date))
            else:
                # 查询所有数据的查询
                sql = """
                      SELECT CONCAT(YEAR(r.createDate), '-', LPAD(WEEK(r.createDate), 2, '0')) as week_key, \
                             COALESCE(a.note, 'No_App')                                   AS app_name, \
                             COUNT(*)                                                          as request_count
                      FROM request r
                               LEFT JOIN apps a ON r.appId = a.id
                      GROUP BY YEAR (r.createDate), WEEK(r.createDate), a.note
                      ORDER BY week_key, app_name \
                      """
                current_app.logger.info("Executing full data query")
                cursor.execute(sql)

            table_data = cursor.fetchall()
            current_app.logger.info(f"Query returned {len(table_data)} rows")

        # === 4. 处理返回数据 ===
        cleaned_data = []
        for row in table_data:
            cleaned_data.append({
                'weeks': row.get('week_key', 'No_Week'),
                'note': row.get('app_name', 'No_App'),
                'counts': row.get('request_count', 0)
            })

        # 成功响应
        return {
            "code": 20000,
            "message": "success",
            "data": cleaned_data,
            "total": len(cleaned_data)
        }

    except json.JSONDecodeError:
        return {"code": 40000, "message": "Invalid JSON format", "data": [], "total": 0}, 400

    except Exception as e:
        current_app.logger.error(
            f"Metadata API Error:\n"
            f"Error: {str(e)}\n"
            f"Stacktrace: {traceback.format_exc()}"
        )
        return {
            "code": 50000,
            "message": "Internal server error",
            "data": [],
            "total": 0
        }, 500

    finally:
        if connection:
            try:
                connection.close()
            except Exception:
                pass

前端图标部分主要分别是 Echats 和 G2Plot 组件，从个人使用上来讲前者应用更广、自定义开发更灵活，后者使用上更简单尤其是在数据绑的格式和方式上更友好，在我们使用 Element vue admin 集成分支项目中有关图表的例子基础就是Echats，比如其中的混合图表（柱形+折线）

对应源代码中代码位置依据可从 /views/chats 看到导入的是 echats 也就是说此组件的使用方式，同样是通过添加依赖和导入使用。

结合提测平台的后台数据，接下来就体验下 Echarts 的图表的如何使用。

VUE项目使用步骤

步骤一：项目进行依赖安装

npm install echarts --save

执行完成后可以在 package.json 的 dependencies 配置项目看到 "echarts": "^5.2.2"引依赖被添加。

步骤二：页面添加组件引用和定义一个容器

<template>
  <div class="app-container">
    <div ref="pieChartDemo" style="width: 600px;height:400px;"></div>
  </div>
</template>

<script>
import * as echarts from 'echarts'
...
</script>

步骤三：使用 echarts.init 方法初始化一个 echarts 实例并通过setOption方法生成一个简单饼图，余下配置代码如下（注意查看几处注解说明）：

export default {
  name: 'EchartsDemo',
  // 使用mounted在页面控件加载在完成后mounted方法进行echart初始化非created
  mounted() {
    this.initPieChart()
  },
  methods: {
    initPieChart() {
      // 采用的是vue ref的方式获取容器
      var chartDom = this.$refs['pieChartDemo']
      var myChart = echarts.init(chartDom)
      var option = {
        title: {
          text: '测试开发',
          subtext: '文章类型分布',
          left: 'center'
        },
        tooltip: {
          trigger: 'item'
        },
        legend: {
          orient: 'vertical',
          left: 'left'
        },
        series: [
          {
            name: 'Access From',
            type: 'pie',
            radius: '50%',
            data: [
              { value: 20, name: '提测平台' },
              { value: 2, name: '性能测试' },
              { value: 1, name: '流量' },
              { value: 3, name: '分享' },
              { value: 5, name: '其他' }
            ]
          }
        ]
      }
      option && myChart.setOption(option);
    }
  }
}

堆叠面积图

在掌握了Echar表的渲染方法和接口数据格式化的充分准备之后，就可以直接上在 src/views/dashboard/index.vue 编写代码，注意实现里有个额外的 series 数据处理，已经标注在代码注解里了。

<template>
  <div class="dashboard-container">
    <div ref="LineChartBoard" style="width: 95%;height:500px;"></div>
  </div>
</template>

<script>
import * as echarts from 'echarts'
import { requestStacked } from '@/api/board'

export default {
  name: 'Dashboard',
  mounted() {
    this.getApList()
  },
  methods: {
    getApList() {
      requestStacked().then(resp => {
        this.initStackedChart(resp.data)
      })
    },
    initStackedChart(data) {
      const chartDom = this.$refs['LineChartBoard']
      const myChart = echarts.init(chartDom)
      const series = []
      // 唯一处理需要额外逻辑处理的地方，根据接口数据动态生成series数据
      for (var key in data.series) {
        series.push(
          {
            name: key,
            type: 'line',
            stack: 'Total',
            areaStyle: {},
            emphasis: {
              focus: 'series'
            },
            data: data.series[key]
          }
        )
      }
      var option = {
        title: {
          text: '周需求提测趋势'
        },
        tooltip: {
          trigger: 'axis',
          axisPointer: {
            type: 'cross',
            label: {
              backgroundColor: '#6a7985'
            }
          }
        },
        legend: {
          // 数据标题展示
          data: data.note
        },
        toolbox: {
          feature: {
            saveAsImage: {}
          }
        },
        grid: {
          left: '3%',
          right: '4%',
          bottom: '3%',
          containLabel: true
        },
        xAxis: [
          {
            type: 'category',
            boundaryGap: false,
            data: data.weeks
          }
        ],
        yAxis: [
          {
            type: 'value'
          }
        ],
        series: series
      }
      option && myChart.setOption(option)
    }
  }
}
</script>

完整的vue前端源码

<template>
  <div class="dashboard-container">
    <div class="filter-container">
      <el-form :inline="true" :model="searchValue">
        <el-form-item label="日期选择">
          <el-date-picker
            v-model="searchValue.date"
            type="daterange"
            value-format="yyyy-MM-dd HH:mm:ss"
            range-separator="至"
            start-placeholder="开始日期"
            end-placeholder="结束日期">
          </el-date-picker>
        </el-form-item>
        <el-form-item>
          <el-button type="primary" @click="searchBoard">刷新查询</el-button>
        </el-form-item>
        <el-form-item>
          <el-switch
            v-model="stackedColumnMode"
            @change="changeBoardMode"
            active-text="分组模式"
            inactive-text="累积模式">
          </el-switch>
        </el-form-item>
      </el-form>
    </div>
    <el-card class="box-card">
      <div slot="header" class="clearfix">
        <span>周需求分组量</span>
      </div>
      <div id="ColumnBoard" style="width: 95%;height:360px;" />
    </el-card>
    <br>
    <el-card class="box-card">
      <div ref="LineChartBoard" style="width: 95%;height:500px;" />
    </el-card>
  </div>
</template>

<script>
import * as echarts from 'echarts'
import { Column } from '@antv/g2plot'

import { requestStacked, requestMetaData } from '@/api/board'

export default {
  name: 'Dashboard',
  created() {
    this.getAppList()
    this.getMetaDate()
  },
  mounted() {
    this.stackedColumnPlot = new Column('ColumnBoard', {
      data: this.stackedColumnData,
      xField: 'weeks',
      yField: 'counts',
      seriesField: 'note',
      isGroup: this.stackedColumnMode ? 'true' : 'false',
      columnStyle: {
        radius: [20, 20, 0, 0]
      }
    })
    this.stackedColumnPlot.render()
  },
  data() {
    return {
      stackedColumnPlot: undefined,
      stackedColumnData: [],
      stackedColumnMode: true,
      searchValue: {
        date: []
      }
    }
  },
  methods: {
    getAppList() {
      requestStacked().then(resp => {
        this.initStackedChart(resp.data)
      })
    },
    getMetaDate() {
      const params = {
        date: this.searchValue.date
      }
      requestMetaData(params).then(resp => {
        this.stackedColumnData = resp.data
        this.stackedColumnPlot.changeData(this.stackedColumnData)
        this.initStackedColumn(resp.data)
      })
    },
    // initStackedColumn(data) {
    //   const stackedColumnPlot = new Column('ColumnBoard', {
    //     data,
    //     xField: 'weeks',
    //     yField: 'counts',
    //     seriesField: 'note',
    //     isGroup: 'true',
    //     columnStyle: {
    //       radius: [20, 20, 0, 0]
    //     }
    //   })
    //   stackedColumnPlot.render()
    // },
    initStackedChart(data) {
      const chartDom = this.$refs['LineChartBoard']
      const myChart = echarts.init(chartDom)
      const series = []
      // 唯一处理需要额外逻辑处理的地方，根据接口数据动态生成series数据
      for (var key in data.series) {
        series.push(
          {
            name: key,
            type: 'line',
            stack: 'Total',
            areaStyle: {},
            emphasis: {
              focus: 'series'
            },
            data: data.series[key]
          }
        )
      }
      var option = {
        title: {
          text: '周需求提测趋势'
        },
        tooltip: {
          trigger: 'axis',
          axisPointer: {
            type: 'cross',
            label: {
              backgroundColor: '#6a7985'
            }
          }
        },
        legend: {
          data: data.note
        },
        toolbox: {
          feature: {
            saveAsImage: {}
          }
        },
        grid: {
          left: '3%',
          right: '4%',
          bottom: '3%',
          containLabel: true
        },
        xAxis: [
          {
            type: 'category',
            boundaryGap: false,
            data: data.weeks
          }
        ],
        yAxis: [
          {
            type: 'value'
          }
        ],
        series: series
      }
      option && myChart.setOption(option)
    },
    searchBoard() {
      this.getMetaDate()
    },
    // 更改显示类型
    changeBoardMode() {
      const options = {
        isGroup: this.stackedColumnMode
      }
      this.stackedColumnPlot.update(options)
    }
  }
}
</script>

<style lang="scss" scoped>
.dashboard {
  &-container {
    margin: 30px;
  }
  &-text {
    font-size: 30px;
    line-height: 46px;
  }
}
</style>

最终实现后就是我们一开始截图后的实现效果