AcidificationModel/api/app/routes.py

import sqlite3

from flask import Blueprint, request, jsonify, current_app
from .model import predict, train_and_save_model
import pandas as pd
from . import db  # 从 app 包导入 db 实例
from sqlalchemy.engine.reflection import Inspector
from .database_models import Model, ModelParameters, Dataset
import os
from .utils import create_dynamic_table, allowed_file
from sqlalchemy.orm import sessionmaker

# 创建蓝图 (Blueprint)，用于分离路由
bp = Blueprint('routes', __name__)
DATABASE = 'SoilAcidification.db'


@bp.route('/upload-dataset', methods=['POST'])
def upload_dataset():
    try:
        # 检查是否包含文件
        if 'file' not in request.files:
            return jsonify({'error': 'No file part'}), 400
        file = request.files['file']

        # 如果没有文件或者文件名为空
        if file.filename == '':
            return jsonify({'error': 'No selected file'}), 400

        # 检查文件类型是否允许
        if file and allowed_file(file.filename):
            # 获取数据集的元数据
            dataset_name = request.form.get('dataset_name')
            dataset_description = request.form.get('dataset_description', 'No description provided')
            dataset_type = request.form.get('dataset_type')  # 新增字段：数据集类型

            # 校验 dataset_type 是否存在
            if not dataset_type:
                return jsonify({'error': 'Dataset type is required'}), 400

            # 创建 Dataset 实体并保存到数据库
            new_dataset = Dataset(
                DatasetName=dataset_name,
                DatasetDescription=dataset_description,
                RowCount=0,  # 初步创建数据集时，行数先置为0
                Status='pending',  # 状态默认为 'pending'
                DatasetType=dataset_type  # 保存数据集类型
            )
            db.session.add(new_dataset)
            db.session.commit()

            # 获取数据集的 ID
            dataset_id = new_dataset.DatasetID

            # 保存文件时使用数据库的 DatasetID 作为文件名
            unique_filename = f"dataset_{dataset_id}.xlsx"
            upload_folder = current_app.config['UPLOAD_FOLDER']
            file_path = os.path.join(upload_folder, unique_filename)

            # 保存文件
            file.save(file_path)

            # 读取 Excel 文件内容
            dataset_df = pd.read_excel(file_path)

            # 更新数据集的行数
            row_count = len(dataset_df)
            new_dataset.RowCount = row_count
            new_dataset.Status = 'processed'  # 状态更新为 processed
            db.session.commit()

            # 动态创建数据表
            columns = {}
            for col in dataset_df.columns:
                if dataset_df[col].dtype == 'int64':
                    columns[col] = 'int'
                elif dataset_df[col].dtype == 'float64':
                    columns[col] = 'float'
                else:
                    columns[col] = 'str'

            # 创建新表格（动态表格）
            dynamic_table_class = create_dynamic_table(dataset_id, columns)

            # 创建新的数据库会话
            Session = sessionmaker(bind=db.engine)
            session = Session()

            # 将每一行数据插入到动态创建的表格中
            for _, row in dataset_df.iterrows():
                record_data = row.to_dict()
                # 将数据插入到新表格中
                session.execute(dynamic_table_class.__table__.insert(), [record_data])

            session.commit()
            session.close()

            return jsonify({
                'message': f'Dataset {dataset_name} uploaded successfully!',
                'dataset_id': new_dataset.DatasetID,
                'filename': unique_filename
            }), 201

        else:
            return jsonify({'error': 'Invalid file type'}), 400

    except Exception as e:
        return jsonify({'error': str(e)}), 500


@bp.route('/tables', methods=['GET'])
def list_tables():
    engine = db.engine  # 使用 db 实例的 engine
    inspector = Inspector.from_engine(engine)  # 创建 Inspector 对象
    table_names = inspector.get_table_names()  # 获取所有表名
    return jsonify(table_names)  # 以 JSON 形式返回表名列表

@bp.route('/models/<int:model_id>', methods=['GET'])
def get_model(model_id):
    try:
        model = Model.query.filter_by(ModelID=model_id).first()
        if model:
            return jsonify({
                'ModelID': model.ModelID,
                'ModelName': model.ModelName,
                'ModelType': model.ModelType,
                'CreatedAt': model.CreatedAt.strftime('%Y-%m-%d %H:%M:%S'),
                'Description': model.Description
            })
        else:
            return jsonify({'message': 'Model not found'}), 404
    except Exception as e:
        return jsonify({'error': 'Internal server error', 'message': str(e)}), 500

@bp.route('/models', methods=['GET'])
def get_all_models():
    try:
        models = Model.query.all()  # 获取所有模型数据
        if models:
            result = [
                {
                    'ModelID': model.ModelID,
                    'ModelName': model.ModelName,
                    'ModelType': model.ModelType,
                    'CreatedAt': model.CreatedAt.strftime('%Y-%m-%d %H:%M:%S'),
                    'Description': model.Description
                }
                for model in models
            ]
            return jsonify(result)
        else:
            return jsonify({'message': 'No models found'}), 404
    except Exception as e:
        return jsonify({'error': 'Internal server error', 'message': str(e)}), 500

@bp.route('/model-parameters', methods=['GET'])
def get_all_model_parameters():
    try:
        parameters = ModelParameters.query.all()  # 获取所有参数数据
        if parameters:
            result = [
                {
                    'ParamID': param.ParamID,
                    'ModelID': param.ModelID,
                    'ParamName': param.ParamName,
                    'ParamValue': param.ParamValue
                }
                for param in parameters
            ]
            return jsonify(result)
        else:
            return jsonify({'message': 'No parameters found'}), 404
    except Exception as e:
        return jsonify({'error': 'Internal server error', 'message': str(e)}), 500

@bp.route('/models/<int:model_id>/parameters', methods=['GET'])
def get_model_parameters(model_id):
    try:
        model = Model.query.filter_by(ModelID=model_id).first()
        if model:
            # 获取该模型的所有参数
            parameters = [
                {
                    'ParamID': param.ParamID,
                    'ParamName': param.ParamName,
                    'ParamValue': param.ParamValue
                }
                for param in model.parameters
            ]
            
            # 返回模型参数信息
            return jsonify({
                'ModelID': model.ModelID,
                'ModelName': model.ModelName,
                'ModelType': model.ModelType,
                'CreatedAt': model.CreatedAt.strftime('%Y-%m-%d %H:%M:%S'),
                'Description': model.Description,
                'Parameters': parameters
            })
        else:
            return jsonify({'message': 'Model not found'}), 404
    except Exception as e:
        return jsonify({'error': 'Internal server error', 'message': str(e)}), 500


@bp.route('/predict', methods=['POST'])
def predict_route():
    try:
        data = request.get_json()
        model_name = data.get('model_name')  # 提取模型名称
        parameters = data.get('parameters', {})  # 提取所有参数

        input_data = pd.DataFrame([parameters])  # 转换参数为DataFrame
        predictions = predict(input_data, model_name)  # 调用预测函数
        return jsonify({'predictions': predictions}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 400


# 定义添加数据库记录的 API 接口
@bp.route('/add_item', methods=['POST'])
def add_item():
    """
    接收 JSON 格式的请求体，包含表名和要插入的数据。
    尝试将数据插入到指定的表中。
    :return:
    """
    db = get_db()
    try:
        # 确保请求体是JSON格式
        data = request.get_json()
        if not data:
            raise ValueError("No JSON data provided")

        table_name = data.get('table')
        item_data = data.get('item')

        if not table_name or not item_data:
            return jsonify({'error': 'Missing table name or item data'}), 400
        cur = db.cursor()

        # 动态构建 SQL 语句
        columns = ', '.join(item_data.keys())
        placeholders = ', '.join(['?'] * len(item_data))
        sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        cur.execute(sql, tuple(item_data.values()))
        db.commit()

        # 返回更详细的成功响应
        return jsonify({'success': True, 'message': 'Item added successfully'}), 201

    except ValueError as e:
        return jsonify({'error': str(e)}), 400
    except KeyError as e:
        return jsonify({'error': f'Missing data field: {e}'}), 400
    except sqlite3.IntegrityError as e:
        # 处理例如唯一性约束违反等数据库完整性错误
        return jsonify({'error': 'Database integrity error', 'details': str(e)}), 409
    except sqlite3.Error as e:
        # 处理其他数据库错误
        return jsonify({'error': 'Database error', 'details': str(e)}), 500
    finally:
        db.close()


# 定义删除数据库记录的 API 接口
@bp.route('/delete_item', methods=['POST'])
def delete_item():
    data = request.get_json()
    table_name = data.get('table')
    condition = data.get('condition')

    # 检查表名和条件是否提供
    if not table_name or not condition:
        return jsonify({
            "success": False,
            "message": "缺少表名或条件参数"
        }), 400

    # 尝试从条件字符串中分离键和值
    try:
        key, value = condition.split('=')
    except ValueError:
        return jsonify({
            "success": False,
            "message": "条件格式错误，应为 'key=value'"
        }), 400

    db = get_db()
    cur = db.cursor()

    try:
        # 执行删除操作
        cur.execute(f"DELETE FROM {table_name} WHERE {key} = ?", (value,))
        db.commit()
        # 如果没有错误发生，返回成功响应
        return jsonify({
            "success": True,
            "message": "记录删除成功"
        }), 200
    except sqlite3.Error as e:
        # 发生错误，回滚事务
        db.rollback()
        # 返回失败响应，并包含错误信息
        return jsonify({
            "success": False,
            "message": f"删除失败: {e}"
        }), 400


# 定义修改数据库记录的 API 接口
@bp.route('/update_item', methods=['PUT'])
def update_record():
    data = request.get_json()

    # 检查必要的数据是否提供
    if not data or 'table' not in data or 'item' not in data:
        return jsonify({
            "success": False,
            "message": "请求数据不完整"
        }), 400

    table_name = data['table']
    item = data['item']

    # 假设 item 的第一个元素是 ID
    if not item or next(iter(item.keys())) is None:
        return jsonify({
            "success": False,
            "message": "记录数据为空"
        }), 400

    # 获取 ID 和其他字段值
    id_key = next(iter(item.keys()))
    record_id = item[id_key]
    updates = {key: value for key, value in item.items() if key != id_key}  # 排除 ID

    db = get_db()
    cur = db.cursor()

    try:
        record_id = int(record_id)  # 确保 ID 是整数
    except ValueError:
        return jsonify({
            "success": False,
            "message": "ID 必须是整数"
        }), 400

    # 准备参数列表，包括更新的值和 ID
    parameters = list(updates.values()) + [record_id]

    # 执行更新操作
    set_clause = ','.join([f"{k} = ?" for k in updates.keys()])
    sql = f"UPDATE {table_name} SET {set_clause} WHERE {id_key} = ?"
    try:
        cur.execute(sql, parameters)
        db.commit()
        if cur.rowcount == 0:
            return jsonify({
                "success": False,
                "message": "未找到要更新的记录"
            }), 404
        return jsonify({
            "success": True,
            "message": "数据更新成功"
        }), 200
    except sqlite3.Error as e:
        db.rollback()
        return jsonify({
            "success": False,
            "message": f"更新失败: {e}"
        }), 400


# 定义查询数据库记录的 API 接口
@bp.route('/search/record', methods=['GET'])
def sql_search():
    """
    接收 JSON 格式的请求体，包含表名和要查询的 ID。
    尝试查询指定 ID 的记录并返回结果。
    :return:
    """
    try:
        data = request.get_json()

        # 表名
        sql_table = data['table']

        # 要搜索的 ID
        Id = data['id']

        # 连接到数据库
        db = get_db()
        cur = db.cursor()

        # 构造查询语句
        sql = f"SELECT * FROM {sql_table} WHERE id = ?"

        # 执行查询
        cur.execute(sql, (Id,))

        # 获取查询结果
        rows = cur.fetchall()
        column_names = [desc[0] for desc in cur.description]

        # 检查是否有结果
        if not rows:
            return jsonify({'error': '未查找到对应数据。'}), 400

        # 构造响应数据
        results = []
        for row in rows:
            result = {column_names[i]: row[i] for i in range(len(row))}
            results.append(result)

        # 关闭游标和数据库连接
        cur.close()
        db.close()

        # 返回 JSON 响应
        return jsonify(results), 200

    except sqlite3.Error as e:
        # 如果发生数据库错误，返回错误信息
        return jsonify({'error': str(e)}), 400
    except KeyError as e:
        # 如果请求数据中缺少必要的键，返回错误信息
        return jsonify({'error': f'缺少必要的数据字段: {e}'}), 400


# 定义提供数据库列表，用于展示表格的 API 接口
@bp.route('/tables', methods=['POST'])
def get_table():
    data = request.get_json()
    table_name = data.get('table')
    if not table_name:
        return jsonify({'error': '需要表名'}), 400

    db = get_db()
    try:
        cur = db.cursor()
        cur.execute(f"SELECT * FROM {table_name}")
        rows = cur.fetchall()

        if not rows:
            return jsonify({'error': '表为空或不存在'}), 400

        headers = [description[0] for description in cur.description]

        return jsonify(rows=rows, headers=headers), 200
    except sqlite3.Error as e:
        return jsonify({'error': str(e)}), 400
    finally:
        db.close()