简介：直接对接禅道MySQL数据库，用Python脚本批量跑出测试团队日常需要的各类数据报表。每天早上自动出一份日报，每周一生成周报，内容包括缺陷新增/关闭趋势、各模块缺陷分布、缺陷平均修复时长、线上问题与日常缺陷对比、项目整体进度、任务完成率、成员工作量分布等。所有分析都基于真实数据库表结构，配套SQL文件已预置好常用查询逻辑（如daily_report.sql、bug_time_summary.sql、project_detail.sql等），Python脚本支持一键执行、Excel导出（read_excel.py）、邮件自动发送（common_mail_test.py）和通用表格处理（common_deal_table.py）。脚本模块化设计，daily_report.py、weekly_report.py、bug_time_detail.py、project_summary.py等可单独运行，也可组合调用；数据库连接配置统一放在common_mysql_config.py里，改一次密码全链路生效。适配禅道8.x及以上版本，部署前只需填入MySQL地址、账号、密码和禅道数据库名，无需额外安装服务或依赖复杂中间件。适合测试负责人、质量保障工程师、研发项目经理做日常数据盯盘、迭代复盘和质量度量。
1. 项目概述：为什么我放弃了“点点点导出+Excel手工整理”的日报周报模式
三年前，我还在用禅道网页端手动筛选、导出、复制粘贴——每天早上9:15准时打开禅道，切到“缺陷”页，按“创建日期=今天”筛选，导出CSV；再切到“任务”，按“截止日期=今天”导出；再切到“项目”，查进度条……然后打开Excel，把三份表格合并、去重、加透视表、画折线图。一套操作下来，固定耗时42分钟。更糟的是，每周一的周报要回溯7天数据，得重复7次筛选动作，中间只要漏掉一天，整个趋势图就断档。有次迭代复盘会上，研发总监指着PPT里一张“缺陷修复时长趋势图”问我：“这张图里‘平均修复时长’是按‘解决时间-创建时间’算的？还是‘关闭时间-创建时间’？有没有排除已取消/已拒绝的缺陷？”我当场卡壳——因为Excel里根本没留原始字段，只存了最终结果。
这就是我启动这个工具的直接动因：不是为了炫技，而是为了把“数据搬运工”的时间，换成“质量分析者”的思考时间。 这套工具不碰禅道API（8.x之后API权限收敛严重，且需Token轮换），也不依赖任何第三方BI平台（公司不允许外传数据），而是直连禅道底层MySQL数据库——这是所有禅道部署的标配，也是最稳定、最可控的数据源。它解决的不是“能不能拿到数据”，而是“能不能在5分钟内，拿到准确、可追溯、可对比、可归因的数据”。
核心关键词“禅道报表、缺陷分析、项目进度、Python脚本、MySQL查询”背后，其实是五个刚性需求：
- 时效性：日报必须在早会前生成，周报必须在周一上午10点前发出；
- 准确性：所有统计口径必须与禅道后台逻辑一致（比如“已解决”状态是否计入“已关闭”？“延期任务”如何定义？）；
- 可解释性：每张图表下方必须能点开明细表，看到每一行数据来自哪条SQL、哪个表、哪个字段；
- 低维护性：换新同事接手，30分钟内能看懂逻辑、改配置、跑通流程；
- 可扩展性：当测试经理突然说“下周开始要统计每个模块的自动化覆盖率”，我能用不到1小时新增一个module_automation_report.py，而不是重写整套框架。
它不是给程序员写的工具，而是给测试负责人、质量分析师、研发项目经理写的“数据杠杆”。你不需要懂Python语法，但需要理解“bug_time_summary.sql里AVG(TIMESTAMPDIFF(HOUR, t1.openedDate, t1.resolvedDate))”这句话意味着什么——它计算的是从缺陷创建到解决之间的平均小时数，且只统计状态为“已解决”的记录（t1.status = 'resolved'）。这种“SQL即文档”的设计，让业务人员也能参与逻辑校验，而不是把黑盒脚本交给开发后就失去话语权。
2. 整体架构与模块化设计：为什么选择“SQL先行+Python胶水”的组合
这套工具的骨架，是我在拆解禅道8.5.3数据库表结构后，反复推演三个月才定型的。很多人第一反应是“为什么不直接用Python ORM（如SQLAlchemy）写查询？”，答案很实在：ORM抽象层会掩盖业务语义，而质量分析的核心恰恰在于对每一个字段含义的精确控制。
举个典型例子：禅道里一个缺陷的“解决时间”，实际存储在zt_bug表的resolvedDate字段，但它的业务含义取决于status字段——只有当status IN ('resolved', 'closed')时，resolvedDate才是有效值；如果status = 'wait'（等待中），这个时间戳就是无效的。用ORM写，很容易写出Bug.query.filter(Bug.status.in_(['resolved','closed'])).avg(Bug.resolvedDate - Bug.openedDate)，看似简洁，但resolvedDate - openedDate在MySQL里是日期相减，在Python里是datetime对象运算，跨层转换极易出错，且无法利用MySQL原生的TIMESTAMPDIFF()函数做精准的小时/天级计算。而手写SQL，你可以明确写出：
SELECT
AVG(TIMESTAMPDIFF(HOUR, b.openedDate, b.resolvedDate)) AS avg_resolve_hour,
COUNT(*) AS resolved_count
FROM zt_bug b
WHERE b.status IN ('resolved', 'closed')
AND b.openedDate >= '2024-06-01'
AND b.openedDate < '2024-06-02';
这段SQL的每一行，都对应着质量度量报告里的一个关键指标，且可直接在Navicat里执行验证。这才是业务可信的起点。
因此，整个架构采用“三层洋葱模型”：
- 最内层：SQL文件（*.sql）——纯SQL语句，定义“查什么”。daily_report.sql负责日报核心指标（当日新增缺陷、关闭缺陷、进行中任务数等）；bug_time_summary.sql聚焦缺陷生命周期（平均创建到解决时长、解决到关闭时长）；project_detail.sql拉取项目级明细（每个项目名称、起止时间、当前进度%、剩余任务数）。这些SQL文件被当作“数据契约”来管理，每次禅道升级后，只需检查表结构变更（如zt_bug新增storyVersion字段），微调SQL即可，不影响上层Python逻辑。
- 中间层：Python脚本（*.py）——“胶水层”，定义“怎么查、查完怎么用”。common_read_sql.py负责安全读取SQL文件（自动过滤注释、处理多语句分隔）；common_mysql_config.py统一管理数据库连接参数（host/port/user/password/dbname），支持环境变量覆盖，避免密码硬编码；common_deal_table.py提供通用表格处理能力（如按某列求和、按状态分组计数、生成趋势折线图所需的时间序列格式）。这一层不包含任何业务逻辑，只做数据搬运和格式转换。
- 最外层：执行脚本（daily_report.py, weekly_report.py等）——“业务接口”，定义“谁来查、查完给谁”。daily_report.py加载daily_report.sql，执行后调用read_excel.py导出为daily_20240601.xlsx，再调用common_mail_test.py发邮件；bug_time_detail.py则加载bug_time_detail.sql（该SQL返回每一条已解决缺陷的ID、创建时间、解决时间、所属模块），生成明细表供质量分析师逐条下钻分析。
这种设计带来的直接好处是：当业务需求变化时，90%的修改发生在SQL层，而非Python层。 比如，测试经理要求“日报里增加‘高优缺陷占比’（priority=1 or priority=2的缺陷数/总缺陷数）”，你只需要在daily_report.sql里加两行SELECT子句，Python脚本完全不用动。这极大降低了维护成本，也使得SQL文件可以作为团队共享的知识库——新人入职，先看懂daily_report.sql，就等于看懂了日报的全部业务规则。
提示：所有SQL文件均采用“标准命名+版本号”方式管理，如
daily_report_v2.sql。v1版本只统计总数，v2版本增加了优先级分布。每次升级SQL，都在对应Python脚本头部添加注释说明变更点，例如# 2024-05-20: daily_report_v2.sql - 新增priority_distribution字段，用于计算高优缺陷占比。
3. 核心脚本解析与实操要点：从配置到执行的完整链路
3.1 数据库连接配置：common_mysql_config.py 是唯一可信源
这是整个工具链的“心脏”，所有脚本都通过它获取数据库连接。它的设计原则是：最小权限、最大隔离、零硬编码。
# common_mysql_config.py
import os
from urllib.parse import quote_plus
# 1. 优先从环境变量读取（适合Docker/K8s部署）
DB_HOST = os.getenv('ZENTAO_DB_HOST', '127.0.0.1')
DB_PORT = int(os.getenv('ZENTAO_DB_PORT', '3306'))
DB_USER = os.getenv('ZENTAO_DB_USER', 'zentao_reader')
DB_PASSWORD = quote_plus(os.getenv('ZENTAO_DB_PASSWORD', 'your_password_here')) # 处理特殊字符
DB_NAME = os.getenv('ZENTAO_DB_NAME', 'zentao')
# 2. 其次尝试读取本地配置文件（适合开发机）
if not DB_PASSWORD or DB_PASSWORD == 'your_password_here':
try:
with open('config/local_db.conf', 'r', encoding='utf-8') as f:
lines = [line.strip() for line in f if line.strip() and not line.startswith('#')]
for line in lines:
key, val = line.split('=', 1)
if key.strip() == 'DB_PASSWORD':
DB_PASSWORD = quote_plus(val.strip().strip("'\""))
except FileNotFoundError:
pass
# 3. 构建连接URL（使用pymysql，兼容禅道默认字符集）
DB_URL = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}?charset=utf8mb4"
这里的关键细节：
- quote_plus()处理密码：如果密码含@或/，不转义会导致URL解析失败。我曾因密码是P@ssw0rd/2024，导致脚本一直报pymysql.err.OperationalError: (1045, "Access denied")，排查了两天才发现是URL解析问题。
- 环境变量优先：生产环境部署时，通过K8s Secret注入环境变量，彻底避免密码出现在代码或配置文件中。
- 本地配置文件兜底：开发机上，新建config/local_db.conf，内容为：
DB_HOST=192.168.1.100 DB_PORT=3306 DB_USER=ztp_reader DB_PASSWORD='My$ecur3P@ss' DB_NAME=zentao_prod
注意：单引号包裹密码，脚本会自动去除。
注意：禅道8.x默认使用
utf8mb4字符集，若连接时未指定，遇到emoji或生僻字（如某些测试用例描述含中文符号）会报错。?charset=utf8mb4是必加参数。
3.2 SQL读取与执行：common_read_sql.py 如何安全加载SQL文件
common_read_sql.py是“胶水层”的核心，它解决了三个痛点：SQL注释干扰、多语句分割、参数化查询。
# common_read_sql.py
import re
import pymysql
def read_sql_file(file_path):
"""安全读取SQL文件，移除注释，分割多语句"""
with open(file_path, 'r', encoding='utf-8') as f:
content = f.read()
# 移除 -- 单行注释和 /* */ 多行注释
content = re.sub(r'--.*$', '', content, flags=re.MULTILINE)
content = re.sub(r'/\*.*?\*/', '', content, flags=re.DOTALL)
# 按分号分割语句，过滤空行
statements = [stmt.strip() for stmt in content.split(';') if stmt.strip()]
return statements
def execute_sql(conn, sql_content, params=None):
"""执行单条SQL，支持参数化查询（防SQL注入）"""
cursor = conn.cursor()
try:
cursor.execute(sql_content, params or ())
if sql_content.strip().upper().startswith('SELECT'):
result = cursor.fetchall()
columns = [col[0] for col in cursor.description]
return result, columns
else:
conn.commit()
return None, None
finally:
cursor.close()
实操中，read_sql_file()会将daily_report.sql中的：
-- 日报核心指标查询（v2）
SELECT
COUNT(CASE WHEN b.status = 'active' THEN 1 END) AS active_bug_count,
COUNT(CASE WHEN b.priority IN (1,2) THEN 1 END) AS high_priority_count,
COUNT(*) AS total_bug_count
FROM zt_bug b
WHERE b.openedDate >= '2024-06-01' AND b.openedDate < '2024-06-02';
清洗为纯净的SELECT ... FROM ... WHERE ...;，并正确分割。而execute_sql()的params参数，让你能在Python中动态传入日期：
# 在 daily_report.py 中
start_date = '2024-06-01'
end_date = '2024-06-02'
sql = "SELECT ... WHERE b.openedDate >= %s AND b.openedDate < %s;"
result, cols = execute_sql(conn, sql, (start_date, end_date))
这比字符串拼接f"WHERE b.openedDate >= '{start_date}'"安全得多，彻底杜绝SQL注入风险。
3.3 报表生成与导出：read_excel.py 的定制化输出逻辑
read_excel.py不是简单调用pandas.to_excel()，而是针对质量报表做了深度定制：
- 多Sheet组织：日报Excel包含
Summary（汇总卡片）、Bug_Detail（当日缺陷明细）、Task_Status（任务状态分布）三个Sheet； - 样式强化：关键指标单元格（如“高优缺陷占比”）自动标红（>30%）或绿色（<10%）；
- 冻结窗格：
Bug_DetailSheet首行冻结，方便横向滚动查看长字段； - 超链接支持：缺陷ID列自动生成禅道网页链接，点击直达详情页（
https://zentao.yourcompany.com/bug-view-{id}.html）。
核心代码片段：
# read_excel.py
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
def export_to_excel(data_dict, filename):
"""
data_dict: {sheet_name: {'data': df, 'highlight_col': 'high_priority_ratio'}}
"""
writer = pd.ExcelWriter(filename, engine='openpyxl')
for sheet_name, config in data_dict.items():
df = config['data']
df.to_excel(writer, sheet_name=sheet_name, index=False)
# 获取工作表对象
worksheet = writer.sheets[sheet_name]
# 冻结首行
worksheet.freeze_panes = 'A2'
# 高亮指定列
if 'highlight_col' in config:
col_idx = df.columns.get_loc(config['highlight_col']) + 1 # Excel列索引从1开始
for row in range(2, len(df)+2): # 从第2行开始（第1行是标题）
cell = worksheet.cell(row=row, column=col_idx)
val = df.iloc[row-2][config['highlight_col']]
if pd.notna(val):
if val > 0.3:
cell.font = Font(color="FF0000") # 红色
cell.fill = PatternFill("solid", fgColor="FFEB3B") # 浅黄底
elif val < 0.1:
cell.font = Font(color="009688") # 青色
cell.fill = PatternFill("solid", fgColor="C8E6C9") # 浅绿底
writer.close()
这个设计让报表不再是冷冰冰的数据堆砌，而是具备交互性和可读性的分析载体。测试负责人扫一眼Summary Sheet的红色数字，就能立刻定位风险点；点击Bug_Detail里的ID，3秒直达禅道缺陷页，无需再手动复制ID去搜索。
3.4 邮件自动发送：common_mail_test.py 的企业微信/钉钉兼容方案
common_mail_test.py名字带“test”，是因为它内置了双通道：SMTP邮件（传统）和Webhook推送（现代）。很多公司已禁用内部SMTP，但允许向企业微信/钉钉机器人发消息。
# common_mail_test.py
import smtplib
import json
import requests
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
def send_email(subject, body, to_emails, attachments=None):
"""发送SMTP邮件（需配置邮箱）"""
msg = MIMEMultipart()
msg['From'] = 'qa-report@yourcompany.com'
msg['To'] = ', '.join(to_emails)
msg['Subject'] = subject
msg.attach(MIMEText(body, 'plain', 'utf-8'))
# 添加附件
if attachments:
for file_path in attachments:
with open(file_path, 'rb') as f:
part = MIMEApplication(f.read(), Name=os.path.basename(file_path))
part['Content-Disposition'] = f'attachment; filename="{os.path.basename(file_path)}"'
msg.attach(part)
server = smtplib.SMTP('smtp.yourcompany.com', 587)
server.starttls()
server.login('qa-report@yourcompany.com', 'app_password_here')
server.send_message(msg)
server.quit()
def send_webhook(title, message, webhook_url):
"""发送企业微信/钉钉Webhook（JSON格式）"""
payload = {
"msgtype": "markdown",
"markdown": {
"content": f"### {title}\n\n> {message}\n\n- 报表已生成，请查收附件。"
}
}
requests.post(webhook_url, json=payload)
实操中，你在daily_report.py末尾这样调用：
# 发送邮件（传统方式）
send_email(
subject=f"[禅道日报] {today_str}",
body=f"今日缺陷趋势：新增{new_cnt}，关闭{close_cnt}，进行中{active_cnt}。",
to_emails=['test-leader@yourcompany.com', 'pm@yourcompany.com'],
attachments=['daily_report.xlsx']
)
# 同时推送企业微信（推荐）
send_webhook(
title="禅道日报已生成",
message=f"📅 {today_str} | 🐞 新增{new_cnt} | ✅ 关闭{close_cnt} | ⏳ 进行中{active_cnt}",
webhook_url=os.getenv('WECHAT_WEBHOOK', '')
)
实操心得：企业微信Webhook的
markdown格式对换行和缩进极其敏感。我踩过的坑是：content字符串里用了\n\n，但企业微信要求必须是\n>开头的引用块，否则渲染失败。现在所有Webhook消息都严格遵循>开头的格式，确保100%正确显示。
4. 核心SQL脚本详解：从daily_report.sql到project_summary.sql的业务逻辑穿透
4.1 daily_report.sql：日报的“黄金三角”指标体系
日报不是数据堆砌，而是围绕“交付健康度”构建的三个核心问题：
- Q1：今天有多少新问题冒出来？ → 新增缺陷数（active_bug_count）
- Q2：今天解决了多少老问题？ → 关闭缺陷数（closed_bug_count）
- Q3：今天有多少事在推进中？ → 进行中任务数（active_task_count）
daily_report.sql的完整逻辑如下（已简化注释）：
-- daily_report_v2.sql
-- 【指标1】当日新增缺陷（按模块、优先级分布）
SELECT
m.name AS module_name,
b.priority,
COUNT(*) AS bug_count
FROM zt_bug b
LEFT JOIN zt_module m ON b.module = m.id
WHERE b.openedDate >= '{start_date}' AND b.openedDate < '{end_date}'
GROUP BY m.name, b.priority
ORDER BY m.name, b.priority;
-- 【指标2】当日关闭缺陷（按解决人、解决方式分布）
SELECT
u.realname AS resolved_by,
b.resolution,
COUNT(*) AS closed_count
FROM zt_bug b
LEFT JOIN zt_user u ON b.resolvedBy = u.account
WHERE b.closedDate >= '{start_date}' AND b.closedDate < '{end_date}'
GROUP BY u.realname, b.resolution
ORDER BY closed_count DESC;
-- 【指标3】当日进行中任务（按执行人、延期状态分布）
SELECT
u.realname AS assigned_to,
CASE WHEN t.deadline < CURDATE() AND t.status NOT IN ('done','cancel') THEN 'delayed' ELSE 'on_time' END AS deadline_status,
COUNT(*) AS task_count
FROM zt_task t
LEFT JOIN zt_user u ON t.assignedTo = u.account
WHERE t.status NOT IN ('done','cancel','pause')
AND t.openedDate <= '{end_date}'
GROUP BY u.realname, deadline_status;
这里的关键业务规则：
- “进行中任务”的判定：状态不能是done/cancel/pause，且openedDate必须≤截止日（排除未来才开启的任务）；
- “延期任务”的判定：deadline < CURDATE()且状态非已完成/已取消，这比单纯看status='delayed'更准确，因为禅道里很多任务不会主动标记为延期；
- 模块名称关联：zt_module表可能有空模块（m.name IS NULL），SQL中用LEFT JOIN保留，后续Python脚本会将NULL模块归类为“未分配”。
4.2 bug_time_summary.sql：缺陷生命周期的“四段式”时长分析
缺陷分析不能只看总数，更要关注“流转效率”。我们定义缺陷生命周期为四个阶段：
1. 创建→解决（opened → resolved）：开发介入速度；
2. 解决→关闭（resolved → closed）：测试验证速度；
3. 创建→关闭（opened → closed）：端到端修复速度；
4. 关闭→重新激活（closed → active）：缺陷复发率。
bug_time_summary.sql用UNION ALL一次性拉取四段数据：
-- bug_time_summary_v1.sql
-- 阶段1：创建→解决（仅统计已解决缺陷）
SELECT
'opened_to_resolved' AS stage,
AVG(TIMESTAMPDIFF(HOUR, b.openedDate, b.resolvedDate)) AS avg_hour,
MIN(TIMESTAMPDIFF(HOUR, b.openedDate, b.resolvedDate)) AS min_hour,
MAX(TIMESTAMPDIFF(HOUR, b.openedDate, b.resolvedDate)) AS max_hour,
COUNT(*) AS count
FROM zt_bug b
WHERE b.status = 'resolved'
AND b.openedDate >= '{start_date}'
AND b.openedDate < '{end_date}'
UNION ALL
-- 阶段2：解决→关闭（仅统计已关闭缺陷）
SELECT
'resolved_to_closed' AS stage,
AVG(TIMESTAMPDIFF(HOUR, b.resolvedDate, b.closedDate)) AS avg_hour,
MIN(TIMESTAMPDIFF(HOUR, b.resolvedDate, b.closedDate)) AS min_hour,
MAX(TIMESTAMPDIFF(HOUR, b.resolvedDate, b.closedDate)) AS max_hour,
COUNT(*) AS count
FROM zt_bug b
WHERE b.status = 'closed'
AND b.resolvedDate IS NOT NULL
AND b.closedDate IS NOT NULL
AND b.resolvedDate >= '{start_date}'
AND b.closedDate < '{end_date}'
UNION ALL
-- 阶段3：创建→关闭（端到端）
SELECT
'opened_to_closed' AS stage,
AVG(TIMESTAMPDIFF(HOUR, b.openedDate, b.closedDate)) AS avg_hour,
MIN(TIMESTAMPDIFF(HOUR, b.openedDate, b.closedDate)) AS min_hour,
MAX(TIMESTAMPDIFF(HOUR, b.openedDate, b.closedDate)) AS max_hour,
COUNT(*) AS count
FROM zt_bug b
WHERE b.status = 'closed'
AND b.openedDate >= '{start_date}'
AND b.closedDate < '{end_date}'
UNION ALL
-- 阶段4：关闭→重新激活（复发缺陷）
SELECT
'closed_to_reactivated' AS stage,
AVG(TIMESTAMPDIFF(HOUR, b1.closedDate, b2.openedDate)) AS avg_hour,
COUNT(*) AS count
FROM zt_bug b1
INNER JOIN zt_bug b2 ON b1.id = b2.activatedBug AND b2.activatedBug IS NOT NULL
WHERE b1.status = 'closed'
AND b2.status = 'active'
AND b1.closedDate >= '{start_date}'
AND b2.openedDate < '{end_date}';
这个SQL的价值在于：它把分散在不同时间点的操作，用关联查询串成一条完整的流水线。当你发现“创建→解决”平均要48小时，但“解决→关闭”只要2小时，问题就清晰了——瓶颈在开发环节，而非测试环节。这种归因分析，是手工Excel永远做不到的。
4.3 project_summary.sql：项目进度的“三维度”透视
项目进度报表常犯的错误是只看“整体进度%”，而忽略背后的水分。project_summary.sql从三个维度交叉验证：
| 维度 | 查询逻辑 | 业务意义 |
|---|---|---|
| 计划进度 | SELECT progress FROM zt_project WHERE id = ? | 禅道后台手动填写的进度，主观性强 |
| 任务完成率 | COUNT(task WHERE status='done') / COUNT(task) | 客观数据，反映真实交付量 |
| 工时饱和度 | SUM(task.estimate) / (COUNT(user)*8*days) | 团队资源是否过载或闲置 |
完整SQL：
-- project_summary_v1.sql
SELECT
p.name AS project_name,
p.progress AS planned_progress,
ROUND(
(SELECT COUNT(*) FROM zt_task t WHERE t.project = p.id AND t.status = 'done') * 100.0 /
NULLIF((SELECT COUNT(*) FROM zt_task t WHERE t.project = p.id), 0), 2
) AS task_completion_rate,
ROUND(
(SELECT COALESCE(SUM(t.estimate), 0) FROM zt_task t WHERE t.project = p.id) * 100.0 /
NULLIF((SELECT COUNT(DISTINCT t.assignedTo) FROM zt_task t WHERE t.project = p.id AND t.assignedTo != '') * 8 * DATEDIFF(p.end, p.begin), 0), 2
) AS workload_saturation,
p.begin AS start_date,
p.end AS end_date,
DATEDIFF(p.end, p.begin) AS total_days
FROM zt_project p
WHERE p.status IN ('doing', 'wait', 'suspended')
AND p.deleted = '0'
ORDER BY p.progress DESC;
这里NULLIF()函数至关重要：当项目无任务（COUNT(*)=0）或无指派人（COUNT(DISTINCT)=0）时，避免除零错误，返回NULL，Python脚本会将其显示为N/A。这种防御性SQL写法，让报表在禅道数据异常时依然健壮。
5. 实操过程与自动化部署：从本地调试到定时任务的全流程
5.1 本地环境搭建：5分钟完成首次运行
部署不是“拷贝代码+改配置”那么简单，而是要建立一套可验证的本地沙箱。以下是标准流程：
-
安装依赖（建议使用虚拟环境）：
bash python -m venv zentao_env source zentao_env/bin/activate # Linux/Mac # zentao_env\Scripts\activate # Windows pip install pymysql openpyxl pandas requests -
准备禅道数据库快照（关键！）：
- 不要直接连生产库！从禅道备份中恢复一个最近的zentao.sql到本地MySQL（5.7+）；
- 或使用mysqldump导出测试环境库：mysqldump -h 192.168.1.100 -u reader -p zentao > zentao_test.sql；
- 导入本地：mysql -u root -p zentao_test < zentao_test.sql。 -
配置数据库连接：
- 编辑common_mysql_config.py，设置DB_HOST='127.0.0.1',DB_NAME='zentao_test'；
- 或更推荐：创建config/local_db.conf，内容为：
DB_HOST=127.0.0.1 DB_PORT=3306 DB_USER=root DB_PASSWORD='your_local_root_pass' DB_NAME=zentao_test -
首次运行日报脚本：
bash python daily_report.py --date 2024-06-01 # 输出：daily_report_20240601.xlsx 已生成 # 打开Excel，检查Summary Sheet的数字是否与禅道网页端一致
实操心得：首次运行务必用
--date参数指定一个历史日期（如昨天），而不是默认用today。因为禅道测试库的数据可能没有今天的记录，直接跑daily_report.py会报“无数据”，让你误以为脚本失败。用历史日期，能快速验证SQL逻辑和Python流程是否通畅。
5.2 自动化调度：Linux定时任务与Windows任务计划程序
自动化不是终点，而是起点。日报必须“无人值守”，否则就失去了意义。
Linux服务器（推荐）：
# 编辑crontab
crontab -e
# 每天早上8:30执行日报（生成昨日数据）
30 8 * * * cd /opt/zentao-report && /opt/zentao-env/bin/python daily_report.py >> /var/log/zentao-daily.log 2>&1
# 每周一早上9:00执行周报（生成上周一至周日数据）
0 9 * * 1 cd /opt/zentao-report && /opt/zentao-env/bin/python weekly_report.py >> /var/log/zentao-weekly.log 2>&1
Windows服务器（兼容方案）：
- 使用“任务计划程序”，创建基本任务；
- 触发器：每日/每周；
- 操作：启动程序 → C:\Python39\python.exe，参数：C:\zentao-report\daily_report.py；
- “使用最高权限运行”必须勾选，否则可能因权限不足无法写入Excel文件。
注意：Linux下
cd /path && python script.py是原子操作，确保在正确目录执行；Windows下需在“起始于”字段填入脚本所在目录路径，否则read_excel.py会找不到相对路径的模板文件。
5.3 邮件与Webhook配置：让报告真正触达决策者
邮件配置最容易出错的是SMTP认证。禅道常用的企业邮箱（如腾讯企业邮、阿里云邮箱）要求：
- SMTP服务器：smtp.exmail.qq.com（腾讯）或smtp.mxhichina.com（阿里）；
- 端口：587（TLS）或465（SSL）；
- 用户名：必须是完整邮箱地址（qa-report@yourcompany.com），而非用户名；
- 密码：必须是“客户端专用密码”，而非邮箱登录密码（在邮箱管理后台开启SMTP服务并生成）。
Webhook配置更简单，但要注意：
- 企业微信机器人需在群聊中添加，并复制Webhook URL（形如https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=xxx）；
- 钉钉机器人需在群设置中添加，并获取Webhook（形如https://oapi.dingtalk.com/robot/send?access_token=xxx）；
- 将URL存入环境变量：export WECHAT_WEBHOOK="https://qyapi.weixin.qq.com/..."，脚本自动读取。
5.4 常见问题与排查技巧实录
Q1：脚本报错 pymysql.err.OperationalError: (1045, "Access denied")
- 原因：数据库密码含特殊字符（如
@,/,:）未转义； - 排查：在
common_mysql_config.py中临时打印DB_URL，检查是否被截断； - 解决：确认
DB_PASSWORD已用quote_plus()处理，或改用环境变量注入。
Q2：Excel导出后，中文显示为乱码（）
- 原因：
pandas.to_excel()默认编码与系统不匹配； - 排查：检查
read_excel.py中是否使用了engine='openpyxl'（必须）； - 解决：确保安装
openpyxl（pip install openpyxl），而非xlsxwriter。
Q3：日报中“高优缺陷占比”为0，但禅道网页能看到高优缺陷
- 原因：SQL中
b.priority IN (1,2)条件错误，禅道8.x中priority字段是字符串（'1','2'），非数字； - 排查：在Navicat中执行
SELECT DISTINCT priority FROM zt_bug LIMIT 5，确认数据类型； - 解决：将SQL改为
b.priority IN ('1','2')。
Q4：weekly_report.py生成的周报，日期范围总是错一天
- 原因：脚本中
start_date = (today - timedelta(days=7)).strftime('%Y-%m-%d')，但禅道的“本周”通常指周一至周日，而timedelta(days=7)是自然日； - 排查：打印
start_date和end_date变量值； - 解决：改用
calendar模块计算周一：
python import calendar def get_last_monday(): today = datetime.date.today() last_monday = today - datetime.timedelta(days=today.weekday()) return last_monday.strftime('%Y-%m-%d')
Q5：企业微信消息不显示，或显示为空白
- 原因：
markdown内容中>后缺少空格，或换行符格式错误（Windows用\r\n，企业微信只认\n）； - 排查：用
print(repr(message))检查字符串实际内容； - 解决：统一用
\n，且确保每行以>开头（注意空格）。
最后分享一个小技巧：在所有脚本顶部添加
#!/usr/bin/env python3，并赋予执行权限chmod +x daily_report.py，即可直接./daily_report.py运行，省去python命令前缀，这对运维同学更友好。
6. 质量保障与持续演进：如何让这套工具活过三年
工具的生命力不在于首发有多炫，而在于能否随业务一起成长。过去三年，这套工具经历了三次重大迭代，每一次都源于一个真实的复盘会议：
-
第一次迭代（禅道8.0升级）：禅道从7.x升级到8.0，
zt_bug表新增storyVersion字段，zt_project表progress字段从整数变为小数。我们没有重写SQL，而是在common_read_sql.py中增加了schema_version_check()函数，自动检测表结构，对progress字段做ROUND(progress, 2)处理，确保报表数字不变。 -
第二次迭代（引入自动化覆盖率）：测试经理提出“要统计每个模块的自动化用例覆盖率”。我们没有大改框架，而是新增了
module_automation_report.py和module_automation.sql，后者关联zt_case（用例表）和zt_module，用COUNT(CASE WHEN c.type='auto' THEN 1 END)/COUNT(*)计算覆盖率。整个新增过程不到1小时。 -
第三次迭代（支持多环境）：公司有了测试、预发、生产三套禅道环境。我们在
common_mysql_config.py中增加了ENVIRONMENT环境变量，默认prod，可设为test或staging，自动加载对应数据库配置，daily_report.py无需任何修改。
这种演进能力，源于两个设计哲学：
1. SQL即契约：所有业务规则沉淀在SQL文件中，版本化管理，变更可追溯；
2. Python即胶水：Python脚本只做数据搬运和格式转换，不掺杂业务逻辑，替换成本趋近于零。
所以，如果你今天开始部署这套工具，我的建议是：
- 第一天：跑通daily_report.py，确保本地能生成正确Excel；
- 第一周：接入企业微信Webhook，让日报消息触达团队；
- 第一个月：根据团队复盘会反馈，新增1-2个定制SQL（如online_bug.sql统计线上问题）；
- 第一年：将common_deal_table.py中的通用函数（如趋势图生成）封装为独立包，供其他团队复用。
它不会一夜之间改变你的工作方式，但当你某天早上打开邮箱，看到那份自动生成的日报，上面清晰地标着“高优缺陷占比35%（↑12%），主要集中在支付模块”，而你只需花3分钟确认原因、@相关开发，然后继续推进今天的测试用例——那一刻，你会明白，真正的效率提升，从来不是更快地点击鼠标，而是让数据自己开口说话。
我个人在实际使用中发现，最有效的推广方式不是培训文档，而是带着测试工程师一起跑一次bug_time_detail.py，把生成的明细表导入禅道缺陷列表页，让他们亲眼看到：原来那个拖了5天没解决的缺陷，其实在第2天就已被开发标记为“已解决”，只是测试没及时验证关闭。这种“所见即所得”的冲击力，远胜千言万语。
简介：直接对接禅道MySQL数据库，用Python脚本批量跑出测试团队日常需要的各类数据报表。每天早上自动出一份日报，每周一生成周报，内容包括缺陷新增/关闭趋势、各模块缺陷分布、缺陷平均修复时长、线上问题与日常缺陷对比、项目整体进度、任务完成率、成员工作量分布等。所有分析都基于真实数据库表结构，配套SQL文件已预置好常用查询逻辑（如daily_report.sql、bug_time_summary.sql、project_detail.sql等），Python脚本支持一键执行、Excel导出（read_excel.py）、邮件自动发送（common_mail_test.py）和通用表格处理（common_deal_table.py）。脚本模块化设计，daily_report.py、weekly_report.py、bug_time_detail.py、project_summary.py等可单独运行，也可组合调用；数据库连接配置统一放在common_mysql_config.py里，改一次密码全链路生效。适配禅道8.x及以上版本，部署前只需填入MySQL地址、账号、密码和禅道数据库名，无需额外安装服务或依赖复杂中间件。适合测试负责人、质量保障工程师、研发项目经理做日常数据盯盘、迭代复盘和质量度量。
384
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
