【Flask】測試平臺開發，數據看板開發-第二十一篇

概述：

在前面我們已經實現了我們的產品創建管理，應用管理管理，需求提測管理但是每周提測了多少需求，創建了哪些產品，我們是不是看著不是很直觀，接下來我們就需要開發一個數據看板功能，實現能夠看到產品下創建的需求，提測數據

先看看實現效果

后端接口源碼：

# -*- coding:utf-8 -*-
# application.py
import datetime
import tracebackfrom 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 = Nonetry: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 和 noteif week not in weeks:weeks.append(week)if note not in notes:notes.append(note)# 使用處理后的 week 和 note 拼接鍵名，避免 NULL 導致的 TypeErrorkey_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_successresp['data'] = resp_datareturn respexcept Exception as e:# current_app.logger.error(f"Error in get_request_stacked: {str(e)}")  # 記錄錯誤日志，方便調試resp = format.resp_format_errorresp['message'] = "Failed to process stacked data"return resp, 500finally:if connection:connection.close()  # 釋放連接from datetime import datetime  # 正確的導入方式
@test_dashboard.route("/api/dashboard/metadata", methods=['POST'])
def get_request_stacked_metadata():connection = Nonetry:# === 1. 解析請求體 ===if not request.data:return {"code": 40001, "message": "Request body is empty", "data": [], "total": 0}, 400body = request.get_json()if body is None:return {"code": 40002, "message": "Invalid JSON format", "data": [], "total": 0}, 400current_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 = Noneend_date = Nonevalid = 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.strptimedatetime.strptime(start_str, date_format)datetime.strptime(end_str, date_format)if start_str <= end_str:start_date = start_strend_date = end_strvalid = Truecurrent_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 = Falseelif 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_paramend_date = end_date_paramvalid = Truecurrent_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 = Falseelse:current_app.logger.info("No date filter applied, querying all data")# === 3. 構建SQL查詢 ===connection = pool.connection()with connection.cursor() as cursor:# 臨時禁用ONLY_FULL_GROUP_BYtry: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_countFROM request rLEFT JOIN apps a ON r.appId = a.idWHERE r.createDate BETWEEN %s AND %sGROUP BY YEAR (r.createDate), WEEK(r.createDate), a.noteORDER 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_countFROM request rLEFT JOIN apps a ON r.appId = a.idGROUP BY YEAR (r.createDate), WEEK(r.createDate), a.noteORDER 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}, 400except 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}, 500finally: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初始化非createdmounted() {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-pickerv-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-switchv-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.datathis.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>

最終實現后就是我們一開始截圖后的實現效果