development_of_web_application_systems/main.py

import math
import random
import psutil      ##使用psutil库获取系统和进程信息
import pymysql     ##提供Mysql数据库接口
import pytz
import datetime    ##pytz库配合datetime,用于创建本地时间，设置时间戳
from flask import Flask, render_template, jsonify, request, session, redirect, url_for ##导入flask库包括 Flask 主类、用于渲染模板和处理 JSON 的模块，会话管理 session，重定向 redirect，以及日期时间处理 datetime。


##创建flask应用实例
app = Flask(__name__)
app.secret_key = 'SecretKey'    #设置flask密钥，提高安全性


##定义各种路由，以及对应的视图函数，用于处理不同页面的请求和访问
####非管理者查看数据会显示404页面
@app.route("/404")     
def index_404():
    return render_template('page/404.html',)

####用户注销，重定向到登陆页面，重新登陆
@app.route("/logout") 
def logout():
    session.clear()          # 清除会话数据
    # session['logged_out'] = True
    return redirect(url_for('login'))

####渲染注册界面
@app.route("/register")
def register():
    return render_template('page/register.html',)

####渲染登录界面
@app.route("/login")
def login():
    return render_template('page/login.html',)

####API路由，使用GET方法同步登录请求
@app.route("/api_login", methods=['GET'])
def api_login():
    username =request.args.get('username')
    password = request.args.get('password')
    ######连接到MySQL数据库
    connection = pymysql.connect(host='192.168.153.165', user='yunwei', password='123456', database='yunwei', charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            ###### SQL查询相匹配用户
            sql = f"SELECT * FROM user WHERE username = '{username}' AND password = '{password}'"
            cursor.execute(sql)
            result = cursor.fetchone()
            ######设置会话数据
            if result:
                session['quanxian'] = result['quanxian']
                session['username'] = username
                data={'code':0,'msg':'登录成功'}
            else:
                data = {'code': 1, 'msg': '登录失败'}
    finally:
        connection.close() 
    return jsonify(data)

####API路由，同步处理注册请求
@app.route("/api_register", methods=['GET'])
def api_register():
    username =request.args.get('username')
    password = request.args.get('password')
    connection = pymysql.connect(host='192.168.153.165', user='yunwei', password='123456', database='yunwei', charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            ######执行插入操作，添加新用户
            sql = """
                INSERT INTO user(username, password,quanxian)
                VALUES (%s, %s, %s)
                """
            ######数据格式：【用户名，密码，用户权限（默认0）】
            data = (username,password,0)
            cursor.execute(sql, data)
            connection.commit()
            #######返回信息
            res_data = {
                'code': 0,
                'msg': '注册成功'
            }
    except:
        res_data = {
            'code': 1,
            'msg': '注册失败'
        }
    finally:
        connection.close()
    return jsonify(res_data)

###定义默认登录界面
@app.route("/")
def logins():
    return render_template('page/login.html',)

###定义主页面
@app.route("/index_echarts")
def index_echarts():
    return render_template('index.html',)

###检查用户的权限，如果是管理员（权限为1），返回告警页面，否则返回404页面
@app.route("/warings")
def warings():
    if session['quanxian'] == 1:
        return render_template('page/warings.html')
    else:
        return render_template('page/404.html')

###管理员页面
@app.route("/admin")
def admin():
    username = session['username']
    data={
        'username':username
    }
    return render_template('page/index.html', **data)

###日志界面
@app.route("/log")
def log():
    return render_template('page/table.html',)

###用户界面，检查用户的权限，如果是管理员（权限为1），返回用户页面，否则返回404页面
@app.route("/user")
def user():
    if session['quanxian'] ==1:
        return render_template('page/user.html')
    else:
        return render_template('page/404.html')

###创建数据库表
@app.route("/install")
def install():
    try:
        db = pymysql.connect(host='192.168.153.165', user='yunwei',      password='123456',database='yunwei',  charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
        with db.cursor() as cursor:
            ###### 创建表my_table，包含ID、CPU、内存、磁盘、上行、下行、机器号和时间戳字段
            sql = """
            CREATE TABLE IF NOT EXISTS my_table (
                ID INT AUTO_INCREMENT PRIMARY KEY,
                CPU FLOAT NOT NULL,
                neicun FLOAT NOT NULL,
                disk FLOAT NOT NULL,
                shang FLOAT NOT NULL,
                xia FLOAT NOT NULL,
                jiqihao VARCHAR(80),
                at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
            ###### 创建表user，包含ID、用户名、密码、权限和时间戳字段
            user_sql = """
                  CREATE TABLE IF NOT EXISTS user (
                      ID INT AUTO_INCREMENT PRIMARY KEY,
                      username VARCHAR(80) UNIQUE NOT NULL,
                      password VARCHAR(80),
                      quanxian INT,
    
                      at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
                  """
             ###### 创建表warings，包含ID、CPU、内存、磁盘、上行、下行、机器号、消息、IP和时间戳字段
            warings = """
            CREATE TABLE IF NOT EXISTS warings (
                ID INT AUTO_INCREMENT PRIMARY KEY,
                CPU FLOAT NOT NULL,
                neicun FLOAT NOT NULL,
                disk FLOAT NOT NULL,
                shang FLOAT NOT NULL,
                xia FLOAT NOT NULL,
                jiqihao VARCHAR(80),
                message VARCHAR(80),
                ip VARCHAR(80),
                at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
            cursor.execute(warings)
            cursor.execute(user_sql)
            cursor.execute(sql)
            db.commit()
    finally:
        db.close()
    return jsonify({'code':0,'msg':'数据库创建成功'})

###使用GET方法处理日志分页查询请求
@app.route("/api_log", methods=['GET'])
def api_log():
    ######获取请求参数中的煤业记录数LIMIT_PER_PAGE
    LIMIT_PER_PAGE =int(request.args.get('limit'))
    ######请求参数的当前页数PAGE
    PAGE = int(request.args.get('page'))
    ######计算偏移量OFFSET
    OFFSET = (PAGE - 1) * LIMIT_PER_PAGE
    connection = pymysql.connect(host='192.168.153.165', user='yunwei', password='123456', database='yunwei', charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            ######创建一个 SQL 查询，使用 LIMIT 和 OFFSET 进行翻页
            sql = "SELECT * FROM my_table LIMIT %s OFFSET %s"
            cursor.execute(sql, (LIMIT_PER_PAGE, OFFSET))
            ######查询并传递参数
            results = cursor.fetchall()
            sql_count = "SELECT COUNT(*) FROM my_table"
            cursor.execute(sql_count)
            result_count = cursor.fetchone()
            count = result_count['COUNT(*)']
            res=[]
            ######创建结果列表
            for row in results:
                print(row)
                res.append({
                    'id':row['ID'],
                    'cpu':row['CPU'],
                    'disk':row['disk'],
                    'shang':row['shang'],
                    'xia':row['xia'],
                    'neicun':row['neicun'],
                    'jiqihao': row['jiqihao'],
                    'at':row['at']
                })
    finally:
        connection.close()
    data={'code':0,'msg':'','count':count,'data':res}
    return jsonify(data)

###定义一个API路由，使用GET方法处理告警信息分页查询请求，与api_log相比处理了额外的message字段
@app.route("/api_warings", methods=['GET'])
def api_warings():
    LIMIT_PER_PAGE =int(request.args.get('limit'))
    PAGE = int(request.args.get('page'))
    OFFSET = (PAGE - 1) * LIMIT_PER_PAGE
    connection = pymysql.connect(host='192.168.153.165', user='yunwei', password='123456', database='yunwei', charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            sql = "SELECT * FROM warings LIMIT %s OFFSET %s"
            cursor.execute(sql, (LIMIT_PER_PAGE, OFFSET))
            results = cursor.fetchall()
            sql_count = "SELECT COUNT(*) FROM warings"
            cursor.execute(sql_count)
            result_count = cursor.fetchone()
            count = result_count['COUNT(*)']
            res=[]
            for row in results:
                res.append({
                    'id':row['ID'],
                    'cpu':row['CPU'],
                    'disk':row['disk'],
                    'shang':row['shang'],
                    'xia':row['xia'],
                    'neicun':row['neicun'],
                    'jiqihao': row['jiqihao'],
                    'message': row['message'],
                    'at':row['at']
                })
    finally:
        connection.close()
    data={'code':0,'msg':'','count':count,'data':res}
    return jsonify(data)

###使用GET方法处理用户信息分页查询请求
@app.route("/api_user", methods=['GET'])
def api_user():
    LIMIT_PER_PAGE =int(request.args.get('limit'))
    PAGE = int(request.args.get('page'))
    OFFSET = (PAGE - 1) * LIMIT_PER_PAGE
    connection = pymysql.connect(host='192.168.153.165', user='yunwei', password='123456', database='yunwei', charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            sql = "SELECT * FROM user LIMIT %s OFFSET %s"
            cursor.execute(sql, (LIMIT_PER_PAGE, OFFSET))
            results = cursor.fetchall()
            sql_count = "SELECT COUNT(*) FROM user"
            cursor.execute(sql_count)
            result_count = cursor.fetchone()
            count = result_count['COUNT(*)']
            res=[]
            ######根据用户权限字段，设置权限描述：quanxian==0为普通用户，quanxian==1为管理员
            for row in results:
                if row['quanxian']==0:
                    quanxian='普通用户'
                else:
                    quanxian = '超级管理员'
                res.append({
                    'id':row['ID'],
                    'username':row['username'],
                    'quanxian':quanxian,
                    'at':row['at']
                })
    finally:
        connection.close()
    data={'code':0,'msg':'','count':count,'data':res}
    return jsonify(data)

###使用GET方法处理系统资源使用情况查询请求
@app.route("/api_msg")
def api_msg():
    ###### 获取内存使用情况
    total_mem,used_mem,available_memory_gb,memory_percent=neicun()
    ###### 获取CPU使用率
    cpu=get_cpu_percent()
    ###### 获取磁盘使用情况
    used, free,usage_percent= get_disk_usage()
    disk_data=[
        {
            'name': '已用硬盘',
            'value': used
        },
        {
            'name': '未用硬盘',
            'value': free
        }
    ]
    cpu_data=[
        {
            'name': '已用CPU',
            'value': cpu
        }, {
            'name': '未用CPU',
            'value': 100-int(cpu)
        }
    ]
    shang= random.randint(0,100)
    xia= random.randint(0,100)
    kuandai_data=[
        {
            'name': '上行',
            'value':shang
        }, {
            'name': '下行',
            'value': xia
        }
    ]
    neicun_data=[{
        'name':'已用内存',
        'value':used_mem
    },{
        'name':'未用内存',
        'value':available_memory_gb
    }]
    ######插入数据到数据库
    try:
        db = pymysql.connect(host='192.168.153.165', user='yunwei',      password='123456',database='yunwei',  charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
        with db.cursor() as cursor:
            sql = """
            INSERT INTO my_table(CPU, neicun, disk, shang, xia, jiqihao)
            VALUES (%s, %s, %s, %s, %s, %s)
            """
            data = (int(cpu), memory_percent, usage_percent,shang, xia, '业务2号机')
            cursor.execute(sql, data)
            ######实现告警，这里设置的是CPU负载高于90%，插入到waring表
            if 100-int(cpu)>=90:
                sql = """
                          INSERT INTO warings(CPU, neicun, disk, shang, xia, jiqihao,message)
                          VALUES (%s, %s, %s, %s, %s, %s, %s)
                          """
                data = (int(cpu), memory_percent, usage_percent, shang, xia, '业务2号机','CPU负载过高')
                cursor.execute(sql, data)
            db.commit()
        print("数据插入成功！")
    ######捕获并打印数据库操作中任何错误
    except pymysql.MySQLError as e:
        print(f"发生错误: {e}")
    finally:
        db.close()
    data={
        'neicun':neicun_data,
        'cpu':cpu_data,
        'disk':disk_data,
        'kuandai':kuandai_data
    }
    return jsonify(data)

###删除用户
@app.route("/delete_username", methods=['GET'])
def delete_username():
    id =request.args.get('id')
    try:
        db = pymysql.connect(host='192.168.153.165', user='yunwei', password='123456', database='yunwei', charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
        with db.cursor() as cursor:
            sql = f"DELETE FROM user WHERE id = '{id}'"
            cursor.execute(sql)
            db.commit()
        data={
            'code':0,
            'msg':'用户删除成功'
        }
    except pymysql.MySQLError as e:
        print(f"发生错误: {e}")
        data = {
            'code': 1,
            'msg': f'发生错误: {e}'
        }
    finally:
        db.close()
    return jsonify(data)

###设置超级管理员
@app.route("/api_Superadmin", methods=['GET'])
def api_Superadmin():
    ######通过GET请求参数获取要设置超级管理员的用户ID
    id =request.args.get('id')
    ######尝试连接数据库并更新对应ID的用户权限
    try:
        db = pymysql.connect(host='192.168.153.165', user='yunwei', password='123456', database='yunwei', charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
        with db.cursor() as cursor:
            sql = f"UPDATE user SET quanxian = 1 WHERE id = '{id}'"
            cursor.execute(sql)
            db.commit()
        data={
            'code':0,
            'msg':'用户设置成功'
        }
    except pymysql.MySQLError as e:
        data = {
            'code': 1,
            'msg': f'发生错误: {e}'
        }
    finally:
        # 关闭连接
        db.close()
    return jsonify(data)


###获取资源使用情况的函数，主要借助psutil库
####获取内存总量、已用内存、可用内存和内存使用百分比。
def neicun():
    ###### 获取内存信息
    mem = psutil.virtual_memory()
    ###### 将字节转换为GB
    total_memory_gb = mem.total / (1024 ** 3)
    used_memory_gb = mem.used / (1024 ** 3)
    available_memory_gb = mem.available / (1024 ** 3)  # 空闲内存
    ###### 计算空间占比（已用内存占总内存的百分比）
    memory_percent = (mem.used / mem.total) * 100
    return total_memory_gb,used_memory_gb,available_memory_gb,memory_percent

####获取CPU使用百分比
def get_cpu_percent(interval=1):
    return psutil.cpu_percent(interval=interval)

####获取磁盘已用空间、未用空间、总空间和使用百分比
def get_disk_usage(disk_path='/'):
    disk_usage = psutil.disk_usage(disk_path)
    used = disk_usage.used 
    free = disk_usage.free 
    total = disk_usage.total
    ######计算已用空间与总空间的比例（以百分比表示）
    usage_percent = (used / total) * 100
    return used, free,usage_percent


###启动Flask应用程序，在本地开发环境中运行，监听127.0.0.1地址上的7000端口
if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port='7000')