python读取mysql数据用多个机器学习预测结果，前端发请求并展示结果

#!/user/bin/env python3
# -*- coding: utf-8 -*-
from hdfs import InsecureClient
from dataclasses import dataclass
import pymysql
import pandas as pd
from flask import Flask, request, url_for, redirect, render_template, jsonify

app = Flask(__name__)
import warnings

warnings.filterwarnings("ignore")
import joblib
import datetime
import mysql.connector
import time
import random
import json
import collections
import random
import matplotlib.pyplot as plt
import os
import copy
from sklearn.metrics import confusion_matrix, classification_report
from sklearn.model_selection import train_test_split
import numpy as np
import pandas as pd
import xgboost as xgb
import pickle
import lightgbm as lgb
from joblib import dump, load
from sklearn.ensemble import GradientBoostingClassifier
from sklearn.linear_model import LogisticRegression
from sklearn.svm import SVC
from sklearn.svm import LinearSVC
from sklearn.ensemble import RandomForestClassifier
from sklearn.tree import DecisionTreeClassifier
from sklearn.metrics import accuracy_score
from sqlalchemy import create_engine


def train_model_xgb(train):
    print("%%%%训练_model_xgb%%%%")
    params = {'booster': 'gbtree',
              'objective': 'binary:logistic',
              'eval_metric': 'auc',
              'silent': 1,
              'eta': 0.01,
              'max_depth': 7,
              'min_child_weight': 5,
              'gamma': 0.2,
              'lambda': 1,
              'colsample_bylevel': 0.7,
              'colsample_bytree': 0.8,
              'subsample': 0.8,
              'scale_pos_weight': 1}
    dtrain = xgb.DMatrix(train.drop(['异常'], axis=1), label=train['异常'])
    watchlist = [(dtrain, 'train')]
    model = xgb.train(params, dtrain, num_boost_round=300, evals=watchlist)
    # 连接到MySQL数据库
    connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
    cursor = connection.cursor()
    current_time = datetime.datetime.now()
    time = current_time
    id = 1
    model_name = 'xgboost'
    model_description = 'xgboost_model'
    trainer = 'Gpb'
    training_parameters = joblib.dump(model, 'xgb_model.pkl')
    sql = "INSERT INTO models (time,id, model_name, model_description, trainer, training_parameters) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (time, id, model_name, model_description, trainer, training_parameters))
    connection.commit()
    connection.close()
    return id


def train_model_gbdt(train):
    print("%%%%训练_model_gbdt%%%%")
    gbdt_model = GradientBoostingClassifier(n_estimators=100, learning_rate=0.1, max_depth=3, random_state=42)
    X_train = train.drop(['异常'], axis=1)
    y_train = train['异常']
    gbdt_model.fit(X_train, y_train)
    # 连接到MySQL数据库
    connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
    cursor = connection.cursor()
    current_time = datetime.datetime.now()
    time = current_time
    id = 2
    model_name = 'gbdt'
    model_description = 'gbdt_model'
    trainer = 'Gpb'
    training_parameters = joblib.dump(gbdt_model, 'gbdt_model.pkl')
    sql = "INSERT INTO models (time,id, model_name, model_description, trainer, training_parameters) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (time, id, model_name, model_description, trainer, training_parameters))
    connection.commit()
    connection.close()
    return id


def train_model_lr(train):
    print("%%%%训练_model_lr%%%%")
    lr_model = LogisticRegression(random_state=42)
    X_train = train.drop(['异常'], axis=1)
    y_train = train['异常']
    lr_model.fit(X_train, y_train)
    # 连接到MySQL数据库
    connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
    cursor = connection.cursor()
    current_time = datetime.datetime.now()
    time = current_time
    id = 3
    model_name = 'lr'
    model_description = 'lr_model'
    trainer = 'Gpb'
    training_parameters = joblib.dump(lr_model, 'lr_model.pkl')
    sql = "INSERT INTO models (time,id, model_name, model_description, trainer, training_parameters) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (time, id, model_name, model_description, trainer, training_parameters))
    connection.commit()
    connection.close()
    return id


def train_model_svm(train):
    print("%%%%训练_model_svm%%%%")
    svm_model = LinearSVC(C=1.0, random_state=42)
    X_train = train.drop(['异常'], axis=1)
    y_train = train['异常']
    svm_model.fit(X_train, y_train)
    # 连接到MySQL数据库
    connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
    cursor = connection.cursor()
    current_time = datetime.datetime.now()
    time = current_time
    id = 4
    model_name = 'svm'
    model_description = 'svm_model'
    trainer = 'Gpb'
    training_parameters = joblib.dump(svm_model, 'svm_model.pkl')
    sql = "INSERT INTO models (time,id, model_name, model_description, trainer, training_parameters) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (time, id, model_name, model_description, trainer, training_parameters))
    connection.commit()
    connection.close()
    return id


def train_model_rf(train):
    print("%%%%训练_model_rf%%%%")
    rf_model = RandomForestClassifier(n_estimators=100, random_state=42)
    X_train = train.drop(['异常'], axis=1)
    y_train = train['异常']
    rf_model.fit(X_train, y_train)
    # 连接到MySQL数据库
    connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
    cursor = connection.cursor()
    current_time = datetime.datetime.now()
    time = current_time
    id = 5
    model_name = 'rf'
    model_description = 'rf_model'
    trainer = 'Gpb'
    training_parameters = joblib.dump(rf_model, 'rf_model.pkl')
    sql = "INSERT INTO models (time,id, model_name, model_description, trainer, training_parameters) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (time, id, model_name, model_description, trainer, training_parameters))
    connection.commit()
    connection.close()
    return id


def train_model_cart(train):
    print("%%%%训练_model_cart%%%%")
    cart_model = DecisionTreeClassifier(criterion='gini', max_depth=5, random_state=42)
    X_train = train.drop(['异常'], axis=1)
    y_train = train['异常']
    cart_model.fit(X_train, y_train)
    # 连接到MySQL数据库
    connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
    cursor = connection.cursor()
    current_time = datetime.datetime.now()
    time = current_time
    id = 6
    model_name = 'cart'
    model_description = 'cart_model'
    trainer = 'Gpb'
    training_parameters = joblib.dump(cart_model, 'cart_model.pkl')
    sql = "INSERT INTO models (time,id, model_name, model_description, trainer, training_parameters) VALUES (%s, %s, %s, %s, %s, %s)"
    cursor.execute(sql, (time, id, model_name, model_description, trainer, training_parameters))
    connection.commit()
    connection.close()
    return id


# 读取原始MySQL数据
def get_data():
    cnx = mysql.connector.connect(user='root', password='000000', host='localhost', database='localstreamdata')
    cursor = cnx.cursor()
    query = "SELECT * FROM test"
    cursor.execute(query)
    rows = cursor.fetchall()
    column_names = [desc[0] for desc in cursor.description]
    normal_data = pd.DataFrame(rows, columns=column_names)
    query = "SELECT * FROM train"
    cursor.execute(query)
    rows = cursor.fetchall()
    column_names = [desc[0] for desc in cursor.description]
    train_data_process = pd.DataFrame(rows, columns=column_names)
    cursor.close()
    cnx.close()
    return normal_data, train_data_process


############################################################主页
@app.route('/', methods=['get', 'post'])
def register_post_list():
    return render_template("系统_配电网主页.html")


@app.route('/sc')
def index():
    return render_template('系统_配电网主页.html')


@app.route('/cool_form', methods=['GET', 'POST'])
def cool_form():
    if request.method == 'POST':
        return redirect(url_for('index'))
    return render_template('系统_配电网主页.html')


############################################################模型训练
@app.route('/xl', methods=['GET', 'POST'])
def xl():
    if request.method == 'POST':
        return redirect(url_for('index'))
    return render_template('模型训练.html')


@app.route("/model/train", methods=["POST"])
def model_train():
    ################################# 创建models表
    engine = create_engine('mysql+pymysql://root:000000@localhost/localstreamdata')
    models = 'models'
    with engine.connect() as connection:
        exists = connection.execute(f"SHOW TABLES LIKE '{models}'").fetchall()
    if not exists:
        sql_create_table = f'''
        CREATE TABLE {models} (
          number INT AUTO_INCREMENT PRIMARY KEY,
          time DATETIME,
          id INT ,
          model_name VARCHAR(255),
          model_description VARCHAR(255),
          trainer VARCHAR(255),
          training_parameters BLOB
        );
        '''
        with engine.connect() as connection:
            connection.execute(sql_create_table)
    ################################# 接收用户通过post形式发送的数据
    print(request.form)
    dataIds = request.form.get("dataIds")
    modelId = request.form.get("modelId")
    modelId = int(modelId)
    print(dataIds)
    print(modelId)
    test_new, train_new = get_data()
    print('test_new.dtypes')
    print(test_new.dtypes)
    print('train_new.dtypes')
    print(train_new.dtypes)
    if modelId == 1:
        results_xgb = train_model_xgb(train_new)
    elif modelId == 2:
        results_gbdt = train_model_gbdt(train_new)
    elif modelId == 3:
        results_lr = train_model_lr(train_new)
    elif modelId == 4:
        results_svm = train_model_svm(train_new)
    elif modelId == 5:
        results_rf = train_model_rf(train_new)
    elif modelId == 6:
        results_cart = train_model_cart(train_new)
    else:
        print("无效的 modelId")
    print("%%%%训练完%%%%")
    return render_template('系统_运行.html')


############################################################模型预测
@app.route('/yc', methods=['GET', 'POST'])
def yc():
    if request.method == 'POST':
        return redirect(url_for('index'))
    return render_template('模型预测.html')


@app.route("/model/test", methods=["POST"])
def model_test():
    ################################# 接收用户通过post形式发送的数据
    print(request.form)
    dataIds = request.form.get("dataIds")
    modelId = request.form.get("modelId")
    modelId = int(modelId)
    print(dataIds)
    print(modelId)
    data_ids = [int(id) for id in dataIds.split(",")]
    ################################# 在DataFrame中选择符合条件的数据行
    cnx = mysql.connector.connect(user='root', password='000000', host='localhost', database='localstreamdata')
    cursor = cnx.cursor()
    query = "SELECT * FROM test"
    cursor.execute(query)
    rows = cursor.fetchall()
    column_names = [desc[0] for desc in cursor.description]
    normal_data = pd.DataFrame(rows, columns=column_names)
    cursor.close()
    cnx.close()
    print(normal_data)
    test = normal_data[normal_data["stream_id"].isin(data_ids)]
    print(test)
    print(data_ids)
    ################################# 根据 modelId 执行对应的模型
    connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
    cursor = connection.cursor()
    sql = f"SELECT id FROM models WHERE number = {modelId}"
    cursor.execute(sql)
    result_id = cursor.fetchone()
    if result_id is not None:
        id = result_id[0]
    print('id号')
    print(id)
    if id == 1:
        print("%%%%运行model_xgb%%%%")
        connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
        cursor = connection.cursor()
        sql = "SELECT training_parameters FROM models WHERE model_name = 'xgboost' ORDER BY time DESC LIMIT 1"
        cursor.execute(sql)
        result = cursor.fetchone()
        if result is not None:
            training_parameters = result[0]
            model_load = joblib.load(training_parameters)
        dtest = xgb.DMatrix(test.drop(['异常'], axis=1))
        predict = model_load.predict(dtest)
        predict = pd.DataFrame(predict, columns=['prob'])
        print('predict')
        print(predict)
        test = test.reset_index()
        result = pd.concat([test, predict], axis=1)
        print('result')
        print(result)
        result['异常'] = result.apply(lambda x: 1 if x['prob'] > 0.5 else 0, axis=1)
        result['prob'] = result['异常']
        # 关闭数据库连接
        cursor.close()
        connection.close()
        results = result.drop(['异常', '是否新模拟'], axis=1)
    else:
        print("%%%%运行model23456%%%%")
        print(modelId)
        connection = pymysql.connect(host='localhost', user='root', password='000000', db='localstreamdata')
        cursor = connection.cursor()
        sql = f"SELECT training_parameters FROM models WHERE number = {modelId}"
        cursor.execute(sql)
        result = cursor.fetchone()
        if result is not None:
            training_parameters = result[0]
            print(result)
            print(result[0])
            model_load = joblib.load(training_parameters)
        x_test = test.drop(['异常'], axis=1)
        print(x_test)
        y_pred = model_load.predict(x_test)
        y_pred = pd.DataFrame(y_pred, columns=['prob'])
        print('y_pred')
        print(y_pred)
        test = test.reset_index()
        result = pd.concat([test, y_pred], axis=1)
        print('result')
        print(result)
        # 关闭数据库连接
        cursor.close()
        connection.close()
        results = result.drop(['异常', '是否新模拟'], axis=1)
    results['stream_is_normal'] = results['prob']
    results = results.sort_values(by='stream_id', ascending=True)
    results['stream_is_normal'] = [1 if x == 0 else 0 if x == 1 else x for x in results['stream_is_normal']]
    ################################# 上传结果
    engine = create_engine('mysql+pymysql://root:000000@localhost/localstreamdata')
    with engine.connect() as connection:
        conn = connection.connect()
        # 遍历 results['stream_id','stream_is_normal'] 的每一行数据，并更新 normal_data 表的相应行的 stream_is_normal 字段
        for index, row in results[['stream_id', 'stream_is_normal']].iterrows():
            stream_id = row['stream_id']
            stream_is_normal = row['stream_is_normal']
            sql_update = f"UPDATE normal_data SET stream_is_normal = {stream_is_normal} WHERE stream_id = {stream_id}"
            conn.execute(sql_update)
    conn.close()
    counts = results['stream_is_normal'].value_counts()
    if len(counts) > 1:
        ratio_0 = counts[0] / len(results['stream_is_normal'])
        ratio_1 = counts[1] / len(results['stream_is_normal'])
        print("0的比例:", ratio_0)
        print("1的比例:", ratio_1)
    else:
        print("0的比例:100%")
    return render_template('系统_运行.html')


if __name__ == '__main__':
    app.run(host='localhost', port=8088)