如何用Python Web开发模块可视化MySQL中的Linux服务器补丁状态

技术选型 #

• 后端：Flask（轻量易上手，适合快速搭建内部运维工具） + SQLAlchemy（ORM简化MySQL操作）
• 前端：Bootstrap（快速构建响应式界面） + Chart.js（简单实现数据可视化）
• 数据库：MySQL（复用现有数据存储）

步骤1：环境依赖安装 #

在虚拟环境中安装所需包：

pip install flask flask-sqlalchemy mysqlclient

注：如果mysqlclient安装失败，可改用pymysql，并在数据库配置中指定driver=pymysql

步骤2：数据库模型与连接配置 #

假设你的MySQL中有两张核心表：

• servers：存储服务器基础信息（hostname, ip, os_version等）
• patches：存储补丁状态（关联server_id, patch_name, cve_id, status(installed/pending/failed), install_date等）

对应Flask的ORM模型：

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 配置MySQL连接，替换为你的数据库账号、密码、库名
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost/db_name'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class Server(db.Model):
    __tablename__ = 'servers'
    id = db.Column(db.Integer, primary_key=True)
    hostname = db.Column(db.String(100), unique=True, nullable=False)
    ip = db.Column(db.String(50), nullable=False)
    os_version = db.Column(db.String(50))
    patches = db.relationship('Patch', backref='server', lazy=True)

class Patch(db.Model):
    __tablename__ = 'patches'
    id = db.Column(db.Integer, primary_key=True)
    server_id = db.Column(db.Integer, db.ForeignKey('servers.id'), nullable=False)
    patch_name = db.Column(db.String(200), nullable=False)
    cve_id = db.Column(db.String(50))
    status = db.Column(db.String(20), nullable=False)  # installed/pending/failed
    install_date = db.Column(db.DateTime)

步骤3：后端路由实现 #

写两个核心路由：首页展示整体统计，详情页展示单台服务器的补丁明细

@app.route('/')
def dashboard():
    # 统计核心数据
    total_servers = Server.query.count()
    installed_patches = Patch.query.filter_by(status='installed').count()
    pending_patches = Patch.query.filter_by(status='pending').count()
    failed_patches = Patch.query.filter_by(status='failed').count()
    # 获取服务器列表用于跳转详情
    servers = Server.query.all()
    return render_template('dashboard.html', 
                           total_servers=total_servers,
                           installed=installed_patches,
                           pending=pending_patches,
                           failed=failed_patches,
                           servers=servers)

@app.route('/server/<int:server_id>')
def server_detail(server_id):
    server = Server.query.get_or_404(server_id)
    patches = Patch.query.filter_by(server_id=server_id).order_by(Patch.status.desc()).all()
    return render_template('server_detail.html', server=server, patches=patches)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=False)  # 生产环境必须关闭debug

步骤4：前端模板实现 #

仪表盘模板（templates/dashboard.html） #

用Chart.js做饼图展示补丁状态分布，Bootstrap做响应式布局：

<!DOCTYPE html>
<html>
<head>
    <title>Linux补丁状态监控</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <div class="container mt-4">
        <h1>Linux服务器补丁状态仪表盘</h1>
        <div class="row mt-4">
            <div class="col-md-3">
                <div class="card text-center">
                    <div class="card-body">
                        <h5 class="card-title">总服务器数</h5>
                        <p class="card-text display-4">{{ total_servers }}</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-center bg-success text-white">
                    <div class="card-body">
                        <h5 class="card-title">已安装补丁</h5>
                        <p class="card-text display-4">{{ installed }}</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-center bg-warning text-dark">
                    <div class="card-body">
                        <h5 class="card-title">待安装补丁</h5>
                        <p class="card-text display-4">{{ pending }}</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="card text-center bg-danger text-white">
                    <div class="card-body">
                        <h5 class="card-title">安装失败补丁</h5>
                        <p class="card-text display-4">{{ failed }}</p>
                    </div>
                </div>
            </div>
        </div>
        <div class="row mt-4">
            <div class="col-md-6">
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title">补丁状态分布</h5>
                        <canvas id="patchChart"></canvas>
                    </div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="card">
                    <div class="card-body">
                        <h5 class="card-title">服务器列表</h5>
                        <ul class="list-group">
                            {% for server in servers %}
                            <li class="list-group-item">
                                <a href="/server/{{ server.id }}">{{ server.hostname }} ({{ server.ip }})</a>
                            </li>
                            {% endfor %}
                        </ul>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script>
        const ctx = document.getElementById('patchChart').getContext('2d');
        new Chart(ctx, {
            type: 'pie',
            data: {
                labels: ['已安装', '待安装', '安装失败'],
                datasets: [{
                    data: [{{ installed }}, {{ pending }}, {{ failed }}],
                    backgroundColor: ['#28a745', '#ffc107', '#dc3545']
                }]
            }
        });
    </script>
</body>
</html>

服务器详情模板（templates/server_detail.html） #

展示单台服务器的补丁明细表格：

<!DOCTYPE html>
<html>
<head>
    <title>{{ server.hostname }} 补丁详情</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-4">
        <h1>{{ server.hostname }} ({{ server.ip }})</h1>
        <p class="text-muted">操作系统：{{ server.os_version }}</p>
        <a href="/" class="btn btn-primary mb-3">返回仪表盘</a>
        <table class="table table-striped">
            <thead>
                <tr>
                    <th>补丁名称</th>
                    <th>CVE编号</th>
                    <th>状态</th>
                    <th>安装日期</th>
                </tr>
            </thead>
            <tbody>
                {% for patch in patches %}
                <tr>
                    <td>{{ patch.patch_name }}</td>
                    <td>{{ patch.cve_id or '无' }}</td>
                    <td>
                        {% if patch.status == 'installed' %}
                        <span class="badge bg-success">已安装</span>
                        {% elif patch.status == 'pending' %}
                        <span class="badge bg-warning">待安装</span>
                        {% else %}
                        <span class="badge bg-danger">安装失败</span>
                        {% endif %}
                    </td>
                    <td>{{ patch.install_date.strftime('%Y-%m-%d %H:%M') if patch.install_date else '无' }}</td>
                </tr>
                {% endfor %}
            </tbody>
        </table>
    </div>
</body>
</html>

步骤5：部署到生产环境 #

1. 用Gunicorn启动Flask应用：

gunicorn -w 4 -b 0.0.0.0:5000 app:app

2. 配置Nginx反向代理（示例配置）：

server {
    listen 80;
    server_name your-server-ip;

    location / {
        proxy_pass http://127.0.0.1:5000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
}

3. 启动Nginx服务，团队成员即可通过服务器IP访问网站。

优化建议 #

• 添加定时任务（比如用APScheduler）自动同步补丁状态到MySQL，确保数据实时性
• 给页面添加简单的身份验证（比如Flask-Login），避免外部无关访问
• 支持补丁状态筛选、搜索功能，方便快速定位问题

内容的提问来源于stack exchange，提问作者Suthakaran Velumani