如何编写巡检数据汇总脚本

本文目录导读：

1. Python脚本（推荐）
2. Shell脚本（适合Linux环境）
3. SQL查询（数据库巡检数据）
4. 高级功能实现
5. 使用建议

Python脚本（推荐）

基础结构

import pandas as pd
import os
from datetime import datetime
class InspectionSummary:
    def __init__(self, data_dir='./inspection_data'):
        self.data_dir = data_dir
        self.all_data = []
    def read_data(self):
        """读取所有巡检数据文件"""
        for filename in os.listdir(self.data_dir):
            if filename.endswith(('.csv', '.xlsx', '.txt')):
                filepath = os.path.join(self.data_dir, filename)
                try:
                    if filename.endswith('.csv'):
                        df = pd.read_csv(filepath)
                    elif filename.endswith('.xlsx'):
                        df = pd.read_excel(filepath)
                    self.all_data.append(df)
                except Exception as e:
                    print(f"读取文件 {filename} 失败: {e}")
    def summarize(self):
        """生成汇总报告"""
        if not self.all_data:
            return pd.DataFrame()
        # 合并所有数据
        combined_df = pd.concat(self.all_data, ignore_index=True)
        # 生成汇总统计
        summary = {
            '总巡检次数': len(combined_df),
            '巡检日期范围': f"{combined_df['巡检日期'].min()} 至 {combined_df['巡检日期'].max()}",
            '异常数量': len(combined_df[combined_df['状态'] == '异常']),
            '正常数量': len(combined_df[combined_df['状态'] == '正常']),
            '异常率': f"{(len(combined_df[combined_df['状态'] == '异常']) / len(combined_df) * 100):.2f}%"
        }
        return summary
# 使用示例
if __name__ == "__main__":
    inspector = InspectionSummary('./inspection_data')
    inspector.read_data()
    summary = inspector.summarize()
    print("巡检汇总结果:", summary)

Shell脚本（适合Linux环境）

#!/bin/bash
# 巡检数据汇总脚本
DATA_DIR="./inspection_data"
SUMMARY_FILE="./summary_report.txt"
# 初始化汇总文件
echo "========== 巡检数据汇总报告 ==========" > $SUMMARY_FILE
echo "生成时间: $(date)" >> $SUMMARY_FILE
echo "" >> $SUMMARY_FILE
# 统计各类信息
total_files=0
total_records=0
abnormal_count=0
for file in $DATA_DIR/*.log; do
    if [ -f "$file" ]; then
        total_files=$((total_files + 1))
        records=$(wc -l < "$file")
        total_records=$((total_records + records))
        # 统计异常记录
        abnormal=$(grep -c "异常\|FAIL\|ERROR\|WARNING" "$file")
        abnormal_count=$((abnormal_count + abnormal))
    fi
done
# 写入汇总结果
echo "文件总数: $total_files" >> $SUMMARY_FILE
echo "总记录数: $total_records" >> $SUMMARY_FILE
echo "异常记录数: $abnormal_count" >> $SUMMARY_FILE
echo "异常率: $(echo "scale=2; $abnormal_count/$total_records*100" | bc)%" >> $SUMMARY_FILE
echo "" >> $SUMMARY_FILE
echo "详细文件列表:" >> $SUMMARY_FILE
ls -la $DATA_DIR/ >> $SUMMARY_FILE
echo "汇总报告已生成: $SUMMARY_FILE"

SQL查询（数据库巡检数据）

-- 巡检数据汇总查询
SELECT 
    DATE_FORMAT(inspection_date, '%Y-%m-%d') as 巡检日期,
    COUNT(*) as 巡检次数,
    SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) as 正常次数,
    SUM(CASE WHEN status = 'abnormal' THEN 1 ELSE 0 END) as 异常次数,
    CONCAT(
        ROUND(
            SUM(CASE WHEN status = 'abnormal' THEN 1 ELSE 0 END) / COUNT(*) * 100, 
            2
        ), 
        '%'
    ) as 异常率,
    GROUP_CONCAT(DISTINCT location) as 巡检地点
FROM inspection_records
WHERE inspection_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY DATE_FORMAT(inspection_date, '%Y-%m-%d')
ORDER BY inspection_date;
-- 汇总统计
SELECT 
    COUNT(DISTINCT inspection_date) as 巡检天数,
    COUNT(*) as 总巡检次数,
    SUM(CASE WHEN status = 'abnormal' THEN 1 ELSE 0 END) as 总异常次数,
    ROUND(AVG(
        CASE WHEN status = 'abnormal' THEN 1 ELSE 0 END
    ) * 100, 2) as 平均异常率
FROM inspection_records;

高级功能实现

带邮件通知的Python脚本

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
class InspectionReport:
    def __init__(self):
        self.data = []
        self.summary = {}
    def generate_report(self):
        """生成HTML格式报告"""
        html = """
        <html>
        <body>
        <h2>巡检数据汇总报告</h2>
        <table border="1">
            <tr>
                <th>指标</th>
                <th>数值</th>
            </tr>
        """
        for key, value in self.summary.items():
            html += f"<tr><td>{key}</td><td>{value}</td></tr>"
        html += """
        </table>
        </body>
        </html>
        """
        return html
    def send_email(self, subject, html_content):
        """发送邮件报告"""
        sender = 'your_email@example.com'
        receiver = 'manager@example.com'
        password = 'your_password'
        msg = MIMEMultipart('alternative')
        msg['Subject'] = subject
        msg['From'] = sender
        msg['To'] = receiver
        msg.attach(MIMEText(html_content, 'html'))
        with smtplib.SMTP_SSL('smtp.example.com', 465) as server:
            server.login(sender, password)
            server.sendmail(sender, receiver, msg.as_string())

定时执行脚本（crontab配置）

# 每天早上8点执行
0 8 * * * /usr/bin/python3 /path/to/inspection_summary.py
# 每周一执行
0 9 * * 1 /path/to/shell_script.sh
# 每月1号执行
0 10 1 * * /path/to/script.sh

使用建议

1. 数据格式：统一使用CSV或Excel格式存储巡检数据
2. 字段规范：包含日期、时间、地点、巡检人员、状态、备注等字段
3. 异常处理：添加try-catch处理文件读取异常
4. 日志记录：记录脚本执行日志便于排查问题
5. 权限控制：设置合适的文件读写权限
6. 备份机制：自动备份原始数据和汇总结果

根据你的具体需求（文件格式、输出要求、运行环境等），选择适合的方案进行修改和扩展。