routes.py 56 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592
  1. import sqlite3
  2. from flask import Blueprint, request, jsonify, current_app
  3. from .model import predict, train_and_save_model, calculate_model_score
  4. import pandas as pd
  5. from . import db # 从 app 包导入 db 实例
  6. from sqlalchemy.engine.reflection import Inspector
  7. from .database_models import Models, ModelParameters, Datasets, CurrentReduce, CurrentReflux
  8. import os
  9. from .utils import create_dynamic_table, allowed_file, infer_column_types, rename_columns_for_model_predict, \
  10. clean_column_names, rename_columns_for_model, insert_data_into_dynamic_table, insert_data_into_existing_table, \
  11. predict_to_Q, Q_to_t_ha
  12. from sqlalchemy.orm import sessionmaker
  13. import logging
  14. from sqlalchemy import text, select, MetaData, Table, func
  15. from .tasks import train_model_task
  16. from datetime import datetime
  17. # 配置日志
  18. logging.basicConfig(level=logging.DEBUG)
  19. logger = logging.getLogger(__name__)
  20. # 创建蓝图 (Blueprint),用于分离路由
  21. bp = Blueprint('routes', __name__)
  22. # 密码加密
  23. def hash_password(password):
  24. return generate_password_hash(password)
  25. def get_db():
  26. """ 获取数据库连接 """
  27. return sqlite3.connect(app.config['DATABASE'])
  28. # 添加一个新的辅助函数来检查数据集大小并触发训练
  29. def check_and_trigger_training(session, dataset_type, dataset_df):
  30. """
  31. 检查当前数据集大小是否跨越新的阈值点并触发训练
  32. Args:
  33. session: 数据库会话
  34. dataset_type: 数据集类型 ('reduce' 或 'reflux')
  35. dataset_df: 数据集 DataFrame
  36. Returns:
  37. tuple: (是否触发训练, 任务ID)
  38. """
  39. try:
  40. # 根据数据集类型选择表
  41. table = CurrentReduce if dataset_type == 'reduce' else CurrentReflux
  42. # 获取当前记录数
  43. current_count = session.query(func.count()).select_from(table).scalar()
  44. # 获取新增的记录数(从request.files中获取的DataFrame长度)
  45. new_records = len(dataset_df) # 需要从上层函数传入
  46. # 计算新增数据前的记录数
  47. previous_count = current_count - new_records
  48. # 设置阈值
  49. THRESHOLD = current_app.config['THRESHOLD']
  50. # 计算上一个阈值点(基于新增前的数据量)
  51. last_threshold = previous_count // THRESHOLD * THRESHOLD
  52. # 计算当前所在阈值点
  53. current_threshold = current_count // THRESHOLD * THRESHOLD
  54. # 检查是否跨越了新的阈值点
  55. if current_threshold > last_threshold and current_count >= THRESHOLD:
  56. # 触发异步训练任务
  57. task = train_model_task.delay(
  58. model_type=current_app.config['DEFAULT_MODEL_TYPE'],
  59. model_name=f'auto_trained_{dataset_type}_{current_threshold}',
  60. model_description=f'Auto trained model at {current_threshold} records threshold',
  61. data_type=dataset_type
  62. )
  63. return True, task.id
  64. return False, None
  65. except Exception as e:
  66. logging.error(f"检查并触发训练失败: {str(e)}")
  67. return False, None
  68. @bp.route('/upload-dataset', methods=['POST'])
  69. def upload_dataset():
  70. # 创建 session
  71. Session = sessionmaker(bind=db.engine)
  72. session = Session()
  73. try:
  74. if 'file' not in request.files:
  75. return jsonify({'error': 'No file part'}), 400
  76. file = request.files['file']
  77. if file.filename == '' or not allowed_file(file.filename):
  78. return jsonify({'error': 'No selected file or invalid file type'}), 400
  79. dataset_name = request.form.get('dataset_name')
  80. dataset_description = request.form.get('dataset_description', 'No description provided')
  81. dataset_type = request.form.get('dataset_type')
  82. if not dataset_type:
  83. return jsonify({'error': 'Dataset type is required'}), 400
  84. new_dataset = Datasets(
  85. Dataset_name=dataset_name,
  86. Dataset_description=dataset_description,
  87. Row_count=0,
  88. Status='Datasets_upgraded',
  89. Dataset_type=dataset_type,
  90. Uploaded_at=datetime.now()
  91. )
  92. session.add(new_dataset)
  93. session.commit()
  94. unique_filename = f"dataset_{new_dataset.Dataset_ID}.xlsx"
  95. upload_folder = current_app.config['UPLOAD_FOLDER']
  96. file_path = os.path.join(upload_folder, unique_filename)
  97. file.save(file_path)
  98. dataset_df = pd.read_excel(file_path)
  99. new_dataset.Row_count = len(dataset_df)
  100. new_dataset.Status = 'excel_file_saved success'
  101. session.commit()
  102. # 处理列名
  103. dataset_df = clean_column_names(dataset_df)
  104. dataset_df = rename_columns_for_model(dataset_df, dataset_type)
  105. column_types = infer_column_types(dataset_df)
  106. dynamic_table_class = create_dynamic_table(new_dataset.Dataset_ID, column_types)
  107. insert_data_into_dynamic_table(session, dataset_df, dynamic_table_class)
  108. # 根据 dataset_type 决定插入到哪个已有表
  109. if dataset_type == 'reduce':
  110. insert_data_into_existing_table(session, dataset_df, CurrentReduce)
  111. elif dataset_type == 'reflux':
  112. insert_data_into_existing_table(session, dataset_df, CurrentReflux)
  113. session.commit()
  114. # 在完成数据插入后,检查是否需要触发训练
  115. training_triggered, task_id = check_and_trigger_training(session, dataset_type, dataset_df)
  116. response_data = {
  117. 'message': f'Dataset {dataset_name} uploaded successfully!',
  118. 'dataset_id': new_dataset.Dataset_ID,
  119. 'filename': unique_filename,
  120. 'training_triggered': training_triggered
  121. }
  122. if training_triggered:
  123. response_data['task_id'] = task_id
  124. response_data['message'] += ' Auto-training has been triggered.'
  125. return jsonify(response_data), 201
  126. except Exception as e:
  127. session.rollback()
  128. logging.error('Failed to process the dataset upload:', exc_info=True)
  129. return jsonify({'error': str(e)}), 500
  130. finally:
  131. # 确保 session 总是被关闭
  132. if session:
  133. session.close()
  134. @bp.route('/train-and-save-model', methods=['POST'])
  135. def train_and_save_model_endpoint():
  136. # 创建 sessionmaker 实例
  137. Session = sessionmaker(bind=db.engine)
  138. session = Session()
  139. data = request.get_json()
  140. # 从请求中解析参数
  141. model_type = data.get('model_type')
  142. model_name = data.get('model_name')
  143. model_description = data.get('model_description')
  144. data_type = data.get('data_type')
  145. dataset_id = data.get('dataset_id', None) # 默认为 None,如果未提供
  146. try:
  147. # 调用训练和保存模型的函数
  148. result = train_and_save_model(session, model_type, model_name, model_description, data_type, dataset_id)
  149. model_id = result[1] if result else None
  150. # 计算模型评分
  151. if model_id:
  152. model_info = session.query(Models).filter(Models.ModelID == model_id).first()
  153. if model_info:
  154. score = calculate_model_score(model_info)
  155. # 更新模型评分
  156. model_info.Performance_score = score
  157. session.commit()
  158. result = {'model_id': model_id, 'model_score': score}
  159. # 返回成功响应
  160. return jsonify({
  161. 'message': 'Model trained and saved successfully',
  162. 'result': result
  163. }), 200
  164. except Exception as e:
  165. session.rollback()
  166. logging.error('Failed to process the model training:', exc_info=True)
  167. return jsonify({
  168. 'error': 'Failed to train and save model',
  169. 'message': str(e)
  170. }), 500
  171. finally:
  172. session.close()
  173. @bp.route('/predict', methods=['POST'])
  174. def predict_route():
  175. # 创建 sessionmaker 实例
  176. Session = sessionmaker(bind=db.engine)
  177. session = Session()
  178. try:
  179. data = request.get_json()
  180. model_id = data.get('model_id') # 提取模型名称
  181. parameters = data.get('parameters', {}) # 提取所有变量
  182. # 根据model_id获取模型Data_type
  183. model_info = session.query(Models).filter(Models.ModelID == model_id).first()
  184. if not model_info:
  185. return jsonify({'error': 'Model not found'}), 404
  186. data_type = model_info.Data_type
  187. input_data = pd.DataFrame([parameters]) # 转换参数为DataFrame
  188. # 如果为reduce,则不需要传入target_ph
  189. if data_type == 'reduce':
  190. # 获取传入的init_ph、target_ph参数
  191. init_ph = float(parameters.get('init_pH', 0.0)) # 默认值为0.0,防止None导致错误
  192. target_ph = float(parameters.get('target_pH', 0.0)) # 默认值为0.0,防止None导致错误
  193. # 从输入数据中删除'target_pH'列
  194. input_data = input_data.drop('target_pH', axis=1, errors='ignore') # 使用errors='ignore'防止列不存在时出错
  195. input_data_rename = rename_columns_for_model_predict(input_data, data_type) # 重命名列名以匹配模型字段
  196. predictions = predict(session, input_data_rename, model_id) # 调用预测函数
  197. if data_type == 'reduce':
  198. predictions = predictions[0]
  199. # 将预测结果转换为Q
  200. Q = predict_to_Q(predictions, init_ph, target_ph)
  201. predictions = Q_to_t_ha(Q) # 将Q转换为t/ha
  202. print(predictions)
  203. return jsonify({'result': predictions}), 200
  204. except Exception as e:
  205. logging.error('Failed to predict:', exc_info=True)
  206. return jsonify({'error': str(e)}), 400
  207. # 为指定模型计算评分Performance_score,需要提供model_id
  208. @bp.route('/score-model/<int:model_id>', methods=['POST'])
  209. def score_model(model_id):
  210. # 创建 sessionmaker 实例
  211. Session = sessionmaker(bind=db.engine)
  212. session = Session()
  213. try:
  214. model_info = session.query(Models).filter(Models.ModelID == model_id).first()
  215. if not model_info:
  216. return jsonify({'error': 'Model not found'}), 404
  217. # 计算模型评分
  218. score = calculate_model_score(model_info)
  219. # 更新模型记录中的评分
  220. model_info.Performance_score = score
  221. session.commit()
  222. return jsonify({'message': 'Model scored successfully', 'score': score}), 200
  223. except Exception as e:
  224. logging.error('Failed to process the dataset upload:', exc_info=True)
  225. return jsonify({'error': str(e)}), 400
  226. finally:
  227. session.close()
  228. @bp.route('/delete-dataset/<int:dataset_id>', methods=['DELETE'])
  229. def delete_dataset_endpoint(dataset_id):
  230. """
  231. 删除数据集的API接口
  232. @param dataset_id: 要删除的数据集ID
  233. @return: JSON响应
  234. """
  235. # 创建 sessionmaker 实例
  236. Session = sessionmaker(bind=db.engine)
  237. session = Session()
  238. try:
  239. # 查询数据集
  240. dataset = session.query(Datasets).filter_by(Dataset_ID=dataset_id).first()
  241. if not dataset:
  242. return jsonify({'error': '未找到数据集'}), 404
  243. # 检查是否有模型使用了该数据集
  244. models_using_dataset = session.query(Models).filter_by(DatasetID=dataset_id).all()
  245. if models_using_dataset:
  246. models_info = [{'ModelID': model.ModelID, 'Model_name': model.Model_name} for model in models_using_dataset]
  247. return jsonify({
  248. 'error': '无法删除数据集,因为以下模型正在使用它',
  249. 'models': models_info
  250. }), 400
  251. # 删除Excel文件
  252. filename = f"dataset_{dataset.Dataset_ID}.xlsx"
  253. file_path = os.path.join(current_app.config['UPLOAD_FOLDER'], filename)
  254. if os.path.exists(file_path):
  255. try:
  256. os.remove(file_path)
  257. except OSError as e:
  258. logger.error(f'删除文件失败: {str(e)}')
  259. return jsonify({'error': f'删除文件失败: {str(e)}'}), 500
  260. # 删除数据表
  261. table_name = f"dataset_{dataset.Dataset_ID}"
  262. session.execute(text(f"DROP TABLE IF EXISTS {table_name}"))
  263. # 删除数据集记录
  264. session.delete(dataset)
  265. session.commit()
  266. return jsonify({
  267. 'message': '数据集删除成功',
  268. 'deleted_files': [filename]
  269. }), 200
  270. except Exception as e:
  271. session.rollback()
  272. logger.error(f'删除数据集 {dataset_id} 失败:', exc_info=True)
  273. return jsonify({'error': str(e)}), 500
  274. finally:
  275. session.close()
  276. @bp.route('/tables', methods=['GET'])
  277. def list_tables():
  278. engine = db.engine # 使用 db 实例的 engine
  279. inspector = Inspector.from_engine(engine) # 创建 Inspector 对象
  280. table_names = inspector.get_table_names() # 获取所有表名
  281. return jsonify(table_names) # 以 JSON 形式返回表名列表
  282. @bp.route('/models/<int:model_id>', methods=['GET'])
  283. def get_model(model_id):
  284. try:
  285. model = Models.query.filter_by(ModelID=model_id).first()
  286. if model:
  287. return jsonify({
  288. 'ModelID': model.ModelID,
  289. 'ModelName': model.ModelName,
  290. 'ModelType': model.ModelType,
  291. 'CreatedAt': model.CreatedAt.strftime('%Y-%m-%d %H:%M:%S'),
  292. 'Description': model.Description
  293. })
  294. else:
  295. return jsonify({'message': 'Model not found'}), 404
  296. except Exception as e:
  297. return jsonify({'error': 'Internal server error', 'message': str(e)}), 500
  298. @bp.route('/models', methods=['GET'])
  299. def get_all_models():
  300. try:
  301. models = Models.query.all() # 获取所有模型数据
  302. if models:
  303. result = [
  304. {
  305. 'ModelID': model.ModelID,
  306. 'ModelName': model.ModelName,
  307. 'ModelType': model.ModelType,
  308. 'CreatedAt': model.CreatedAt.strftime('%Y-%m-%d %H:%M:%S'),
  309. 'Description': model.Description
  310. }
  311. for model in models
  312. ]
  313. return jsonify(result)
  314. else:
  315. return jsonify({'message': 'No models found'}), 404
  316. except Exception as e:
  317. return jsonify({'error': 'Internal server error', 'message': str(e)}), 500
  318. @bp.route('/model-parameters', methods=['GET'])
  319. def get_all_model_parameters():
  320. try:
  321. parameters = ModelParameters.query.all() # 获取所有参数数据
  322. if parameters:
  323. result = [
  324. {
  325. 'ParamID': param.ParamID,
  326. 'ModelID': param.ModelID,
  327. 'ParamName': param.ParamName,
  328. 'ParamValue': param.ParamValue
  329. }
  330. for param in parameters
  331. ]
  332. return jsonify(result)
  333. else:
  334. return jsonify({'message': 'No parameters found'}), 404
  335. except Exception as e:
  336. return jsonify({'error': 'Internal server error', 'message': str(e)}), 500
  337. @bp.route('/models/<int:model_id>/parameters', methods=['GET'])
  338. def get_model_parameters(model_id):
  339. try:
  340. model = Models.query.filter_by(ModelID=model_id).first()
  341. if model:
  342. # 获取该模型的所有参数
  343. parameters = [
  344. {
  345. 'ParamID': param.ParamID,
  346. 'ParamName': param.ParamName,
  347. 'ParamValue': param.ParamValue
  348. }
  349. for param in model.parameters
  350. ]
  351. # 返回模型参数信息
  352. return jsonify({
  353. 'ModelID': model.ModelID,
  354. 'ModelName': model.ModelName,
  355. 'ModelType': model.ModelType,
  356. 'CreatedAt': model.CreatedAt.strftime('%Y-%m-%d %H:%M:%S'),
  357. 'Description': model.Description,
  358. 'Parameters': parameters
  359. })
  360. else:
  361. return jsonify({'message': 'Model not found'}), 404
  362. except Exception as e:
  363. return jsonify({'error': 'Internal server error', 'message': str(e)}), 500
  364. # 定义添加数据库记录的 API 接口
  365. @bp.route('/add_item', methods=['POST'])
  366. def add_item():
  367. """
  368. 接收 JSON 格式的请求体,包含表名和要插入的数据。
  369. 尝试将数据插入到指定的表中,并进行字段查重。
  370. :return:
  371. """
  372. try:
  373. # 确保请求体是 JSON 格式
  374. data = request.get_json()
  375. if not data:
  376. raise ValueError("No JSON data provided")
  377. table_name = data.get('table')
  378. item_data = data.get('item')
  379. if not table_name or not item_data:
  380. return jsonify({'error': 'Missing table name or item data'}), 400
  381. # 定义各个表的字段查重规则
  382. duplicate_check_rules = {
  383. 'users': ['email', 'username'],
  384. 'products': ['product_code'],
  385. 'current_reduce': [ 'Q_over_b', 'pH', 'OM', 'CL', 'H', 'Al'],
  386. 'current_reflux': ['OM', 'CL', 'CEC', 'H_plus', 'N', 'Al3_plus', 'Delta_pH'],
  387. # 其他表和规则
  388. }
  389. # 获取该表的查重字段
  390. duplicate_columns = duplicate_check_rules.get(table_name)
  391. if not duplicate_columns:
  392. return jsonify({'error': 'No duplicate check rule for this table'}), 400
  393. # 动态构建查询条件,逐一检查是否有重复数据
  394. condition = ' AND '.join([f"{column} = :{column}" for column in duplicate_columns])
  395. duplicate_query = f"SELECT 1 FROM {table_name} WHERE {condition} LIMIT 1"
  396. result = db.session.execute(text(duplicate_query), item_data).fetchone()
  397. if result:
  398. return jsonify({'error': '重复数据,已有相同的数据项存在。'}), 409
  399. # 动态构建 SQL 语句,进行插入操作
  400. columns = ', '.join(item_data.keys())
  401. placeholders = ', '.join([f":{key}" for key in item_data.keys()])
  402. sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
  403. # 直接执行插入操作,无需显式的事务管理
  404. db.session.execute(text(sql), item_data)
  405. # 提交事务
  406. db.session.commit()
  407. # 返回成功响应
  408. return jsonify({'success': True, 'message': 'Item added successfully'}), 201
  409. except ValueError as e:
  410. return jsonify({'error': str(e)}), 400
  411. except KeyError as e:
  412. return jsonify({'error': f'Missing data field: {e}'}), 400
  413. except sqlite3.IntegrityError as e:
  414. return jsonify({'error': '数据库完整性错误', 'details': str(e)}), 409
  415. except sqlite3.Error as e:
  416. return jsonify({'error': '数据库错误', 'details': str(e)}), 500
  417. @bp.route('/delete_item', methods=['POST'])
  418. def delete_item():
  419. """
  420. 删除数据库记录的 API 接口
  421. """
  422. data = request.get_json()
  423. table_name = data.get('table')
  424. condition = data.get('condition')
  425. # 检查表名和条件是否提供
  426. if not table_name or not condition:
  427. return jsonify({
  428. "success": False,
  429. "message": "缺少表名或条件参数"
  430. }), 400
  431. # 尝试从条件字符串中解析键和值
  432. try:
  433. key, value = condition.split('=')
  434. key = key.strip() # 去除多余的空格
  435. value = value.strip().strip("'\"") # 去除多余的空格和引号
  436. except ValueError:
  437. return jsonify({
  438. "success": False,
  439. "message": "条件格式错误,应为 'key=value'"
  440. }), 400
  441. # 准备 SQL 删除语句
  442. sql = f"DELETE FROM {table_name} WHERE {key} = :value"
  443. try:
  444. # 使用 SQLAlchemy 执行删除
  445. with db.session.begin():
  446. result = db.session.execute(text(sql), {"value": value})
  447. # 检查是否有记录被删除
  448. if result.rowcount == 0:
  449. return jsonify({
  450. "success": False,
  451. "message": "未找到符合条件的记录"
  452. }), 404
  453. return jsonify({
  454. "success": True,
  455. "message": "记录删除成功"
  456. }), 200
  457. except Exception as e:
  458. return jsonify({
  459. "success": False,
  460. "message": f"删除失败: {e}"
  461. }), 500
  462. # 定义修改数据库记录的 API 接口
  463. @bp.route('/update_item', methods=['PUT'])
  464. def update_record():
  465. """
  466. 接收 JSON 格式的请求体,包含表名和更新的数据。
  467. 尝试更新指定的记录。
  468. """
  469. data = request.get_json()
  470. # 检查必要的数据是否提供
  471. if not data or 'table' not in data or 'item' not in data:
  472. return jsonify({
  473. "success": False,
  474. "message": "请求数据不完整"
  475. }), 400
  476. table_name = data['table']
  477. item = data['item']
  478. # 假设 item 的第一个键是 ID
  479. id_key = next(iter(item.keys())) # 获取第一个键
  480. record_id = item.get(id_key)
  481. if not record_id:
  482. return jsonify({
  483. "success": False,
  484. "message": "缺少记录 ID"
  485. }), 400
  486. # 获取更新的字段和值
  487. updates = {key: value for key, value in item.items() if key != id_key}
  488. if not updates:
  489. return jsonify({
  490. "success": False,
  491. "message": "没有提供需要更新的字段"
  492. }), 400
  493. # 动态构建 SQL
  494. set_clause = ', '.join([f"{key} = :{key}" for key in updates.keys()])
  495. sql = f"UPDATE {table_name} SET {set_clause} WHERE {id_key} = :id_value"
  496. # 添加 ID 到参数
  497. updates['id_value'] = record_id
  498. try:
  499. # 使用 SQLAlchemy 执行更新
  500. with db.session.begin():
  501. result = db.session.execute(text(sql), updates)
  502. # 检查是否有更新的记录
  503. if result.rowcount == 0:
  504. return jsonify({
  505. "success": False,
  506. "message": "未找到要更新的记录"
  507. }), 404
  508. return jsonify({
  509. "success": True,
  510. "message": "数据更新成功"
  511. }), 200
  512. except Exception as e:
  513. # 捕获所有异常并返回
  514. return jsonify({
  515. "success": False,
  516. "message": f"更新失败: {str(e)}"
  517. }), 500
  518. # 定义查询数据库记录的 API 接口
  519. @bp.route('/search/record', methods=['GET'])
  520. def sql_search():
  521. """
  522. 接收 JSON 格式的请求体,包含表名和要查询的 ID。
  523. 尝试查询指定 ID 的记录并返回结果。
  524. :return:
  525. """
  526. try:
  527. data = request.get_json()
  528. # 表名
  529. sql_table = data['table']
  530. # 要搜索的 ID
  531. Id = data['id']
  532. # 连接到数据库
  533. cur = db.cursor()
  534. # 构造查询语句
  535. sql = f"SELECT * FROM {sql_table} WHERE id = ?"
  536. # 执行查询
  537. cur.execute(sql, (Id,))
  538. # 获取查询结果
  539. rows = cur.fetchall()
  540. column_names = [desc[0] for desc in cur.description]
  541. # 检查是否有结果
  542. if not rows:
  543. return jsonify({'error': '未查找到对应数据。'}), 400
  544. # 构造响应数据
  545. results = []
  546. for row in rows:
  547. result = {column_names[i]: row[i] for i in range(len(row))}
  548. results.append(result)
  549. # 关闭游标和数据库连接
  550. cur.close()
  551. db.close()
  552. # 返回 JSON 响应
  553. return jsonify(results), 200
  554. except sqlite3.Error as e:
  555. # 如果发生数据库错误,返回错误信息
  556. return jsonify({'error': str(e)}), 400
  557. except KeyError as e:
  558. # 如果请求数据中缺少必要的键,返回错误信息
  559. return jsonify({'error': f'缺少必要的数据字段: {e}'}), 400
  560. # 定义提供数据库列表,用于展示表格的 API 接口
  561. @bp.route('/table', methods=['POST'])
  562. def get_table():
  563. data = request.get_json()
  564. table_name = data.get('table')
  565. if not table_name:
  566. return jsonify({'error': '需要表名'}), 400
  567. try:
  568. # 创建 sessionmaker 实例
  569. Session = sessionmaker(bind=db.engine)
  570. session = Session()
  571. # 动态获取表的元数据
  572. metadata = MetaData()
  573. table = Table(table_name, metadata, autoload_with=db.engine)
  574. # 从数据库中查询所有记录
  575. query = select(table)
  576. result = session.execute(query).fetchall()
  577. # 将结果转换为列表字典形式
  578. rows = [dict(zip([column.name for column in table.columns], row)) for row in result]
  579. # 获取列名
  580. headers = [column.name for column in table.columns]
  581. return jsonify(rows=rows, headers=headers), 200
  582. except Exception as e:
  583. return jsonify({'error': str(e)}), 400
  584. finally:
  585. # 关闭 session
  586. session.close()
  587. @bp.route('/train-model-async', methods=['POST'])
  588. def train_model_async():
  589. """
  590. 异步训练模型的API接口
  591. """
  592. try:
  593. data = request.get_json()
  594. # 从请求中获取参数
  595. model_type = data.get('model_type')
  596. model_name = data.get('model_name')
  597. model_description = data.get('model_description')
  598. data_type = data.get('data_type')
  599. dataset_id = data.get('dataset_id', None)
  600. # 验证必要参数
  601. if not all([model_type, model_name, data_type]):
  602. return jsonify({
  603. 'error': 'Missing required parameters'
  604. }), 400
  605. # 如果提供了dataset_id,验证数据集是否存在
  606. if dataset_id:
  607. Session = sessionmaker(bind=db.engine)
  608. session = Session()
  609. try:
  610. dataset = session.query(Datasets).filter_by(Dataset_ID=dataset_id).first()
  611. if not dataset:
  612. return jsonify({
  613. 'error': f'Dataset with ID {dataset_id} not found'
  614. }), 404
  615. finally:
  616. session.close()
  617. # 启动异步任务
  618. task = train_model_task.delay(
  619. model_type=model_type,
  620. model_name=model_name,
  621. model_description=model_description,
  622. data_type=data_type,
  623. dataset_id=dataset_id
  624. )
  625. # 返回任务ID
  626. return jsonify({
  627. 'task_id': task.id,
  628. 'message': 'Model training started'
  629. }), 202
  630. except Exception as e:
  631. logging.error('Failed to start async training task:', exc_info=True)
  632. return jsonify({
  633. 'error': str(e)
  634. }), 500
  635. @bp.route('/task-status/<task_id>', methods=['GET'])
  636. def get_task_status(task_id):
  637. """
  638. 获取异步任务状态的API接口
  639. """
  640. try:
  641. task = train_model_task.AsyncResult(task_id)
  642. if task.state == 'PENDING':
  643. response = {
  644. 'state': task.state,
  645. 'status': 'Task is waiting for execution'
  646. }
  647. elif task.state == 'FAILURE':
  648. response = {
  649. 'state': task.state,
  650. 'status': 'Task failed',
  651. 'error': task.info.get('error') if isinstance(task.info, dict) else str(task.info)
  652. }
  653. elif task.state == 'SUCCESS':
  654. response = {
  655. 'state': task.state,
  656. 'status': 'Task completed successfully',
  657. 'result': task.get()
  658. }
  659. else:
  660. response = {
  661. 'state': task.state,
  662. 'status': 'Task is in progress'
  663. }
  664. return jsonify(response), 200
  665. except Exception as e:
  666. return jsonify({
  667. 'error': str(e)
  668. }), 500
  669. @bp.route('/delete-model/<int:model_id>', methods=['DELETE'])
  670. def delete_model_route(model_id):
  671. # 将URL参数转换为布尔值
  672. delete_dataset_param = request.args.get('delete_dataset', 'False').lower() == 'true'
  673. # 调用原始函数
  674. return delete_model(model_id, delete_dataset=delete_dataset_param)
  675. def delete_model(model_id, delete_dataset=False):
  676. """
  677. 删除指定模型的API接口
  678. @param model_id: 要删除的模型ID
  679. @query_param delete_dataset: 布尔值,是否同时删除关联的数据集,默认为False
  680. @return: JSON响应
  681. """
  682. Session = sessionmaker(bind=db.engine)
  683. session = Session()
  684. try:
  685. # 查询模型信息
  686. model = session.query(Models).filter_by(ModelID=model_id).first()
  687. if not model:
  688. return jsonify({'error': '未找到指定模型'}), 404
  689. dataset_id = model.DatasetID
  690. # 1. 先删除模型记录
  691. session.delete(model)
  692. session.commit()
  693. # 2. 删除模型文件
  694. model_file = f"rf_model_{model_id}.pkl"
  695. model_path = os.path.join(current_app.config['MODEL_SAVE_PATH'], model_file)
  696. if os.path.exists(model_path):
  697. try:
  698. os.remove(model_path)
  699. except OSError as e:
  700. # 如果删除文件失败,回滚数据库操作
  701. session.rollback()
  702. logger.error(f'删除模型文件失败: {str(e)}')
  703. return jsonify({'error': f'删除模型文件失败: {str(e)}'}), 500
  704. # 3. 如果需要删除关联的数据集
  705. if delete_dataset and dataset_id:
  706. try:
  707. dataset_response = delete_dataset_endpoint(dataset_id)
  708. if not isinstance(dataset_response, tuple) or dataset_response[1] != 200:
  709. # 如果删除数据集失败,回滚之前的操作
  710. session.rollback()
  711. return jsonify({
  712. 'error': '删除关联数据集失败',
  713. 'dataset_error': dataset_response[0].get_json() if hasattr(dataset_response[0], 'get_json') else str(dataset_response[0])
  714. }), 500
  715. except Exception as e:
  716. session.rollback()
  717. logger.error(f'删除关联数据集失败: {str(e)}')
  718. return jsonify({'error': f'删除关联数据集失败: {str(e)}'}), 500
  719. response_data = {
  720. 'message': '模型删除成功',
  721. 'deleted_files': [model_file]
  722. }
  723. if delete_dataset:
  724. response_data['dataset_info'] = {
  725. 'dataset_id': dataset_id,
  726. 'message': '关联数据集已删除'
  727. }
  728. return jsonify(response_data), 200
  729. except Exception as e:
  730. session.rollback()
  731. logger.error(f'删除模型 {model_id} 失败:', exc_info=True)
  732. return jsonify({'error': str(e)}), 500
  733. finally:
  734. session.close()
  735. # 添加一个新的API端点来清空指定数据集
  736. @bp.route('/clear-dataset/<string:data_type>', methods=['DELETE'])
  737. def clear_dataset(data_type):
  738. """
  739. 清空指定类型的数据集并递增计数
  740. @param data_type: 数据集类型 ('reduce' 或 'reflux')
  741. @return: JSON响应
  742. """
  743. # 创建 sessionmaker 实例
  744. Session = sessionmaker(bind=db.engine)
  745. session = Session()
  746. try:
  747. # 根据数据集类型选择表
  748. if data_type == 'reduce':
  749. table = CurrentReduce
  750. table_name = 'current_reduce'
  751. elif data_type == 'reflux':
  752. table = CurrentReflux
  753. table_name = 'current_reflux'
  754. else:
  755. return jsonify({'error': '无效的数据集类型'}), 400
  756. # 清空表内容
  757. session.query(table).delete()
  758. # 重置自增主键计数器
  759. session.execute(text(f"DELETE FROM sqlite_sequence WHERE name='{table_name}'"))
  760. session.commit()
  761. return jsonify({'message': f'{data_type} 数据集已清空并重置计数器'}), 200
  762. except Exception as e:
  763. session.rollback()
  764. return jsonify({'error': str(e)}), 500
  765. finally:
  766. session.close()
  767. @bp.route('/login', methods=['POST'])
  768. def login_user():
  769. # 获取前端传来的数据
  770. data = request.get_json()
  771. name = data.get('name') # 用户名
  772. password = data.get('password') # 密码
  773. logger.info(f"Login request received: name={name}")
  774. # 检查用户名和密码是否为空
  775. if not name or not password:
  776. logger.warning("用户名和密码不能为空")
  777. return jsonify({"success": False, "message": "用户名和密码不能为空"}), 400
  778. try:
  779. # 查询数据库验证用户名
  780. query = "SELECT * FROM users WHERE name = :name"
  781. conn = get_db()
  782. user = conn.execute(query, {"name": name}).fetchone()
  783. if not user:
  784. logger.warning(f"用户名 '{name}' 不存在")
  785. return jsonify({"success": False, "message": "用户名不存在"}), 400
  786. # 获取数据库中存储的密码(假设密码是哈希存储的)
  787. stored_password = user[2] # 假设密码存储在数据库的第三列
  788. user_id = user[0] # 假设 id 存储在数据库的第一列
  789. # 校验密码是否正确
  790. if check_password_hash(stored_password, password):
  791. logger.info(f"User '{name}' logged in successfully.")
  792. return jsonify({
  793. "success": True,
  794. "message": "登录成功",
  795. "userId": user_id # 返回用户 ID
  796. })
  797. else:
  798. logger.warning(f"Invalid password for user '{name}'")
  799. return jsonify({"success": False, "message": "用户名或密码错误"}), 400
  800. except Exception as e:
  801. # 记录错误日志并返回错误信息
  802. logger.error(f"Error during login: {e}", exc_info=True)
  803. return jsonify({"success": False, "message": "登录失败"}), 500
  804. # 更新用户信息接口
  805. @bp.route('/update_user', methods=['POST'])
  806. def update_user():
  807. # 获取前端传来的数据
  808. data = request.get_json()
  809. # 打印收到的请求数据
  810. app.logger.info(f"Received data: {data}")
  811. user_id = data.get('userId') # 用户ID
  812. name = data.get('name') # 用户名
  813. old_password = data.get('oldPassword') # 旧密码
  814. new_password = data.get('newPassword') # 新密码
  815. logger.info(f"Update request received: user_id={user_id}, name={name}")
  816. # 校验传入的用户名和密码是否为空
  817. if not name or not old_password:
  818. logger.warning("用户名和旧密码不能为空")
  819. return jsonify({"success": False, "message": "用户名和旧密码不能为空"}), 400
  820. # 新密码和旧密码不能相同
  821. if new_password and old_password == new_password:
  822. logger.warning(f"新密码与旧密码相同:{name}")
  823. return jsonify({"success": False, "message": "新密码与旧密码不能相同"}), 400
  824. try:
  825. # 查询数据库验证用户ID
  826. query = "SELECT * FROM users WHERE id = :user_id"
  827. conn = get_db()
  828. user = conn.execute(query, {"user_id": user_id}).fetchone()
  829. if not user:
  830. logger.warning(f"用户ID '{user_id}' 不存在")
  831. return jsonify({"success": False, "message": "用户不存在"}), 400
  832. # 获取数据库中存储的密码(假设密码是哈希存储的)
  833. stored_password = user[2] # 假设密码存储在数据库的第三列
  834. # 校验旧密码是否正确
  835. if not check_password_hash(stored_password, old_password):
  836. logger.warning(f"旧密码错误:{name}")
  837. return jsonify({"success": False, "message": "旧密码错误"}), 400
  838. # 如果新密码非空,则更新新密码
  839. if new_password:
  840. hashed_new_password = hash_password(new_password)
  841. update_query = "UPDATE users SET password = :new_password WHERE id = :user_id"
  842. conn.execute(update_query, {"new_password": hashed_new_password, "user_id": user_id})
  843. conn.commit()
  844. logger.info(f"User ID '{user_id}' password updated successfully.")
  845. # 如果用户名发生更改,则更新用户名
  846. if name != user[1]:
  847. update_name_query = "UPDATE users SET name = :new_name WHERE id = :user_id"
  848. conn.execute(update_name_query, {"new_name": name, "user_id": user_id})
  849. conn.commit()
  850. logger.info(f"User ID '{user_id}' name updated to '{name}' successfully.")
  851. return jsonify({"success": True, "message": "用户信息更新成功"})
  852. except Exception as e:
  853. # 记录错误日志并返回错误信息
  854. logger.error(f"Error updating user: {e}", exc_info=True)
  855. return jsonify({"success": False, "message": "更新失败"}), 500
  856. # 注册用户
  857. @bp.route('/register', methods=['POST'])
  858. def register_user():
  859. # 获取前端传来的数据
  860. data = request.get_json()
  861. name = data.get('name') # 用户名
  862. password = data.get('password') # 密码
  863. logger.info(f"Register request received: name={name}")
  864. # 检查用户名和密码是否为空
  865. if not name or not password:
  866. logger.warning("用户名和密码不能为空")
  867. return jsonify({"success": False, "message": "用户名和密码不能为空"}), 400
  868. # 动态获取数据库表的列名
  869. columns = get_column_names('users')
  870. logger.info(f"Database columns for 'users' table: {columns}")
  871. # 检查前端传来的数据是否包含数据库表中所有的必填字段
  872. for column in ['name', 'password']:
  873. if column not in columns:
  874. logger.error(f"缺少必填字段:{column}")
  875. return jsonify({"success": False, "message": f"缺少必填字段:{column}"}), 400
  876. # 对密码进行哈希处理
  877. hashed_password = hash_password(password)
  878. logger.info(f"Password hashed for user: {name}")
  879. # 插入到数据库
  880. try:
  881. # 检查用户是否已经存在
  882. query = "SELECT * FROM users WHERE name = :name"
  883. conn = get_db()
  884. user = conn.execute(query, {"name": name}).fetchone()
  885. if user:
  886. logger.warning(f"用户名 '{name}' 已存在")
  887. return jsonify({"success": False, "message": "用户名已存在"}), 400
  888. # 向数据库插入数据
  889. query = "INSERT INTO users (name, password) VALUES (:name, :password)"
  890. conn.execute(query, {"name": name, "password": hashed_password})
  891. conn.commit()
  892. logger.info(f"User '{name}' registered successfully.")
  893. return jsonify({"success": True, "message": "注册成功"})
  894. except Exception as e:
  895. # 记录错误日志并返回错误信息
  896. logger.error(f"Error registering user: {e}", exc_info=True)
  897. return jsonify({"success": False, "message": "注册失败"}), 500
  898. def get_column_names(table_name):
  899. """
  900. 动态获取数据库表的列名。
  901. """
  902. try:
  903. conn = get_db()
  904. query = f"PRAGMA table_info({table_name});"
  905. result = conn.execute(query).fetchall()
  906. conn.close()
  907. return [row[1] for row in result] # 第二列是列名
  908. except Exception as e:
  909. logger.error(f"Error getting column names for table {table_name}: {e}", exc_info=True)
  910. return []
  911. # 导出数据
  912. @bp.route('/export_data', methods=['GET'])
  913. def export_data():
  914. table_name = request.args.get('table')
  915. file_format = request.args.get('format', 'excel').lower()
  916. if not table_name:
  917. return jsonify({'error': '缺少表名参数'}), 400
  918. if not table_name.isidentifier():
  919. return jsonify({'error': '无效的表名'}), 400
  920. try:
  921. conn = get_db()
  922. query = "SELECT name FROM sqlite_master WHERE type='table' AND name=?;"
  923. table_exists = conn.execute(query, (table_name,)).fetchone()
  924. if not table_exists:
  925. return jsonify({'error': f"表 {table_name} 不存在"}), 404
  926. query = f"SELECT * FROM {table_name};"
  927. df = pd.read_sql(query, conn)
  928. output = BytesIO()
  929. if file_format == 'csv':
  930. df.to_csv(output, index=False, encoding='utf-8')
  931. output.seek(0)
  932. return send_file(output, as_attachment=True, download_name=f'{table_name}_data.csv', mimetype='text/csv')
  933. elif file_format == 'excel':
  934. df.to_excel(output, index=False, engine='openpyxl')
  935. output.seek(0)
  936. return send_file(output, as_attachment=True, download_name=f'{table_name}_data.xlsx',
  937. mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
  938. else:
  939. return jsonify({'error': '不支持的文件格式,仅支持 CSV 和 Excel'}), 400
  940. except Exception as e:
  941. logger.error(f"Error in export_data: {e}", exc_info=True)
  942. return jsonify({'error': str(e)}), 500
  943. # 导入数据接口
  944. @bp.route('/import_data', methods=['POST'])
  945. def import_data():
  946. logger.debug("Import data endpoint accessed.")
  947. if 'file' not in request.files:
  948. logger.error("No file in request.")
  949. return jsonify({'success': False, 'message': '文件缺失'}), 400
  950. file = request.files['file']
  951. table_name = request.form.get('table')
  952. if not table_name:
  953. logger.error("Missing table name parameter.")
  954. return jsonify({'success': False, 'message': '缺少表名参数'}), 400
  955. if file.filename == '':
  956. logger.error("No file selected.")
  957. return jsonify({'success': False, 'message': '未选择文件'}), 400
  958. try:
  959. # 保存文件到临时路径
  960. temp_path = os.path.join(app.config['UPLOAD_FOLDER'], secure_filename(file.filename))
  961. file.save(temp_path)
  962. logger.debug(f"File saved to temporary path: {temp_path}")
  963. # 根据文件类型读取文件
  964. if file.filename.endswith('.xlsx'):
  965. df = pd.read_excel(temp_path)
  966. elif file.filename.endswith('.csv'):
  967. df = pd.read_csv(temp_path)
  968. else:
  969. logger.error("Unsupported file format.")
  970. return jsonify({'success': False, 'message': '仅支持 Excel 和 CSV 文件'}), 400
  971. # 获取数据库列名
  972. db_columns = get_column_names(table_name)
  973. if 'id' in db_columns:
  974. db_columns.remove('id') # 假设 id 列是自增的,不需要处理
  975. if not set(db_columns).issubset(set(df.columns)):
  976. logger.error(f"File columns do not match database columns. File columns: {df.columns.tolist()}, Expected: {db_columns}")
  977. return jsonify({'success': False, 'message': '文件列名与数据库表不匹配'}), 400
  978. # 清洗数据并删除空值行
  979. df_cleaned = df[db_columns].dropna()
  980. # 统一数据类型,避免 int 和 float 合并问题
  981. df_cleaned[db_columns] = df_cleaned[db_columns].apply(pd.to_numeric, errors='coerce')
  982. # 获取现有的数据
  983. conn = get_db()
  984. with conn:
  985. existing_data = pd.read_sql(f"SELECT * FROM {table_name}", conn)
  986. # 查找重复数据
  987. duplicates = df_cleaned.merge(existing_data, on=db_columns, how='inner')
  988. # 如果有重复数据,删除它们
  989. df_cleaned = df_cleaned[~df_cleaned.index.isin(duplicates.index)]
  990. logger.warning(f"Duplicate data detected and removed: {duplicates}")
  991. # 获取导入前后的数据量
  992. total_data = len(df_cleaned) + len(duplicates)
  993. new_data = len(df_cleaned)
  994. duplicate_data = len(duplicates)
  995. # 导入不重复的数据
  996. df_cleaned.to_sql(table_name, conn, if_exists='append', index=False)
  997. logger.debug(f"Imported {new_data} new records into the database.")
  998. # 删除临时文件
  999. os.remove(temp_path)
  1000. logger.debug(f"Temporary file removed: {temp_path}")
  1001. # 返回结果
  1002. return jsonify({
  1003. 'success': True,
  1004. 'message': '数据导入成功',
  1005. 'total_data': total_data,
  1006. 'new_data': new_data,
  1007. 'duplicate_data': duplicate_data
  1008. }), 200
  1009. except Exception as e:
  1010. logger.error(f"Import failed: {e}", exc_info=True)
  1011. return jsonify({'success': False, 'message': f'导入失败: {str(e)}'}), 500
  1012. # 模板下载接口
  1013. @bp.route('/download_template', methods=['GET'])
  1014. def download_template():
  1015. """
  1016. 根据给定的表名,下载表的模板(如 CSV 或 Excel 格式)。
  1017. """
  1018. table_name = request.args.get('table')
  1019. if not table_name:
  1020. return jsonify({'error': '表名参数缺失'}), 400
  1021. columns = get_column_names(table_name)
  1022. if not columns:
  1023. return jsonify({'error': f"Table '{table_name}' not found or empty."}), 404
  1024. # 不包括 ID 列
  1025. if 'id' in columns:
  1026. columns.remove('id')
  1027. df = pd.DataFrame(columns=columns)
  1028. file_format = request.args.get('format', 'excel').lower()
  1029. try:
  1030. if file_format == 'csv':
  1031. output = BytesIO()
  1032. df.to_csv(output, index=False, encoding='utf-8')
  1033. output.seek(0)
  1034. return send_file(output, as_attachment=True, download_name=f'{table_name}_template.csv',
  1035. mimetype='text/csv')
  1036. else:
  1037. output = BytesIO()
  1038. df.to_excel(output, index=False, engine='openpyxl')
  1039. output.seek(0)
  1040. return send_file(output, as_attachment=True, download_name=f'{table_name}_template.xlsx',
  1041. mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
  1042. except Exception as e:
  1043. logger.error(f"Failed to generate template: {e}", exc_info=True)
  1044. return jsonify({'error': '生成模板文件失败'}), 500
  1045. @bp.route('/update-threshold', methods=['POST'])
  1046. def update_threshold():
  1047. """
  1048. 更新训练阈值的API接口
  1049. @body_param threshold: 新的阈值值(整数)
  1050. @return: JSON响应
  1051. """
  1052. try:
  1053. data = request.get_json()
  1054. new_threshold = data.get('threshold')
  1055. # 验证新阈值
  1056. if not isinstance(new_threshold, (int, float)) or new_threshold <= 0:
  1057. return jsonify({
  1058. 'error': '无效的阈值值,必须为正数'
  1059. }), 400
  1060. # 更新当前应用的阈值配置
  1061. current_app.config['THRESHOLD'] = int(new_threshold)
  1062. return jsonify({
  1063. 'success': True,
  1064. 'message': f'阈值已更新为 {new_threshold}',
  1065. 'new_threshold': new_threshold
  1066. })
  1067. except Exception as e:
  1068. logging.error(f"更新阈值失败: {str(e)}")
  1069. return jsonify({
  1070. 'error': f'更新阈值失败: {str(e)}'
  1071. }), 500
  1072. @bp.route('/get-threshold', methods=['GET'])
  1073. def get_threshold():
  1074. """
  1075. 获取当前训练阈值的API接口
  1076. @return: JSON响应
  1077. """
  1078. try:
  1079. current_threshold = current_app.config['THRESHOLD']
  1080. default_threshold = current_app.config['DEFAULT_THRESHOLD']
  1081. return jsonify({
  1082. 'current_threshold': current_threshold,
  1083. 'default_threshold': default_threshold
  1084. })
  1085. except Exception as e:
  1086. logging.error(f"获取阈值失败: {str(e)}")
  1087. return jsonify({
  1088. 'error': f'获取阈值失败: {str(e)}'
  1089. }), 500
  1090. @bp.route('/set-current-dataset/<string:data_type>/<int:dataset_id>', methods=['POST'])
  1091. def set_current_dataset(data_type, dataset_id):
  1092. """
  1093. 将指定数据集设置为current数据集
  1094. @param data_type: 数据集类型 ('reduce' 或 'reflux')
  1095. @param dataset_id: 要设置为current的数据集ID
  1096. @return: JSON响应
  1097. """
  1098. Session = sessionmaker(bind=db.engine)
  1099. session = Session()
  1100. try:
  1101. # 验证数据集存在且类型匹配
  1102. dataset = session.query(Datasets)\
  1103. .filter_by(Dataset_ID=dataset_id, Dataset_type=data_type)\
  1104. .first()
  1105. if not dataset:
  1106. return jsonify({
  1107. 'error': f'未找到ID为 {dataset_id} 且类型为 {data_type} 的数据集'
  1108. }), 404
  1109. # 根据数据类型选择表
  1110. if data_type == 'reduce':
  1111. table = CurrentReduce
  1112. table_name = 'current_reduce'
  1113. elif data_type == 'reflux':
  1114. table = CurrentReflux
  1115. table_name = 'current_reflux'
  1116. else:
  1117. return jsonify({'error': '无效的数据集类型'}), 400
  1118. # 清空current表
  1119. session.query(table).delete()
  1120. # 重置自增主键计数器
  1121. session.execute(text(f"DELETE FROM sqlite_sequence WHERE name='{table_name}'"))
  1122. # 从指定数据集复制数据到current表
  1123. dataset_table_name = f"dataset_{dataset_id}"
  1124. copy_sql = text(f"INSERT INTO {table_name} SELECT * FROM {dataset_table_name}")
  1125. session.execute(copy_sql)
  1126. session.commit()
  1127. return jsonify({
  1128. 'message': f'{data_type} current数据集已设置为数据集 ID: {dataset_id}',
  1129. 'dataset_id': dataset_id,
  1130. 'dataset_name': dataset.Dataset_name,
  1131. 'row_count': dataset.Row_count
  1132. }), 200
  1133. except Exception as e:
  1134. session.rollback()
  1135. logger.error(f'设置current数据集失败: {str(e)}')
  1136. return jsonify({'error': str(e)}), 500
  1137. finally:
  1138. session.close()
  1139. @bp.route('/get-model-history/<string:data_type>', methods=['GET'])
  1140. def get_model_history(data_type):
  1141. """
  1142. 获取模型训练历史数据的API接口
  1143. @param data_type: 数据集类型 ('reduce' 或 'reflux')
  1144. @return: JSON响应,包含时间序列的模型性能数据
  1145. """
  1146. Session = sessionmaker(bind=db.engine)
  1147. session = Session()
  1148. try:
  1149. # 查询所有自动生成的数据集,按时间排序
  1150. datasets = session.query(Datasets).filter(
  1151. Datasets.Dataset_type == data_type,
  1152. Datasets.Dataset_description == f"Automatically generated dataset for type {data_type}"
  1153. ).order_by(Datasets.Uploaded_at).all()
  1154. history_data = []
  1155. for dataset in datasets:
  1156. # 查找对应的自动训练模型
  1157. model = session.query(Models).filter(
  1158. Models.DatasetID == dataset.Dataset_ID,
  1159. Models.Model_name.like(f'auto_trained_{data_type}_%')
  1160. ).first()
  1161. if model and model.Performance_score is not None:
  1162. # 直接使用数据库中的时间,不进行格式化(保持与created_at相同的时区)
  1163. created_at = model.Created_at.isoformat() if model.Created_at else None
  1164. history_data.append({
  1165. 'dataset_id': dataset.Dataset_ID,
  1166. 'row_count': dataset.Row_count,
  1167. 'model_id': model.ModelID,
  1168. 'model_name': model.Model_name,
  1169. 'performance_score': float(model.Performance_score),
  1170. 'timestamp': created_at
  1171. })
  1172. # 按时间戳排序
  1173. history_data.sort(key=lambda x: x['timestamp'] if x['timestamp'] else '')
  1174. # 构建返回数据,分离各个指标序列便于前端绘图
  1175. response_data = {
  1176. 'data_type': data_type,
  1177. 'timestamps': [item['timestamp'] for item in history_data],
  1178. 'row_counts': [item['row_count'] for item in history_data],
  1179. 'performance_scores': [item['performance_score'] for item in history_data],
  1180. 'model_details': history_data # 保留完整数据供前端使用
  1181. }
  1182. return jsonify(response_data), 200
  1183. except Exception as e:
  1184. logger.error(f'获取模型历史数据失败: {str(e)}', exc_info=True)
  1185. return jsonify({'error': str(e)}), 500
  1186. finally:
  1187. session.close()
  1188. @bp.route('/batch-delete-datasets', methods=['POST'])
  1189. def batch_delete_datasets():
  1190. """
  1191. 批量删除数据集的API接口
  1192. @body_param dataset_ids: 要删除的数据集ID列表
  1193. @return: JSON响应
  1194. """
  1195. try:
  1196. data = request.get_json()
  1197. dataset_ids = data.get('dataset_ids', [])
  1198. if not dataset_ids:
  1199. return jsonify({'error': '未提供数据集ID列表'}), 400
  1200. results = {
  1201. 'success': [],
  1202. 'failed': [],
  1203. 'protected': [] # 被模型使用的数据集
  1204. }
  1205. for dataset_id in dataset_ids:
  1206. try:
  1207. # 调用单个删除接口
  1208. response = delete_dataset_endpoint(dataset_id)
  1209. # 解析响应
  1210. if response[1] == 200:
  1211. results['success'].append(dataset_id)
  1212. elif response[1] == 400 and 'models' in response[0].json:
  1213. # 数据集被模型保护
  1214. results['protected'].append({
  1215. 'id': dataset_id,
  1216. 'models': response[0].json['models']
  1217. })
  1218. else:
  1219. results['failed'].append({
  1220. 'id': dataset_id,
  1221. 'reason': response[0].json.get('error', '删除失败')
  1222. })
  1223. except Exception as e:
  1224. logger.error(f'删除数据集 {dataset_id} 失败: {str(e)}')
  1225. results['failed'].append({
  1226. 'id': dataset_id,
  1227. 'reason': str(e)
  1228. })
  1229. # 构建响应消息
  1230. message = f"成功删除 {len(results['success'])} 个数据集"
  1231. if results['protected']:
  1232. message += f", {len(results['protected'])} 个数据集被保护"
  1233. if results['failed']:
  1234. message += f", {len(results['failed'])} 个数据集删除失败"
  1235. return jsonify({
  1236. 'message': message,
  1237. 'results': results
  1238. }), 200
  1239. except Exception as e:
  1240. logger.error(f'批量删除数据集失败: {str(e)}')
  1241. return jsonify({'error': str(e)}), 500
  1242. @bp.route('/batch-delete-models', methods=['POST'])
  1243. def batch_delete_models():
  1244. """
  1245. 批量删除模型的API接口
  1246. @body_param model_ids: 要删除的模型ID列表
  1247. @query_param delete_datasets: 布尔值,是否同时删除关联的数据集,默认为False
  1248. @return: JSON响应
  1249. """
  1250. try:
  1251. data = request.get_json()
  1252. model_ids = data.get('model_ids', [])
  1253. delete_datasets = request.args.get('delete_datasets', 'false').lower() == 'true'
  1254. if not model_ids:
  1255. return jsonify({'error': '未提供模型ID列表'}), 400
  1256. results = {
  1257. 'success': [],
  1258. 'failed': [],
  1259. 'datasets_deleted': [] # 如果delete_datasets为true,记录被删除的数据集
  1260. }
  1261. for model_id in model_ids:
  1262. try:
  1263. # 调用单个删除接口
  1264. response = delete_model(model_id, delete_dataset=delete_datasets)
  1265. # 解析响应
  1266. if response[1] == 200:
  1267. results['success'].append(model_id)
  1268. # 如果删除了关联数据集,记录数据集ID
  1269. if 'dataset_info' in response[0].json:
  1270. results['datasets_deleted'].append(
  1271. response[0].json['dataset_info']['dataset_id']
  1272. )
  1273. else:
  1274. results['failed'].append({
  1275. 'id': model_id,
  1276. 'reason': response[0].json.get('error', '删除失败')
  1277. })
  1278. except Exception as e:
  1279. logger.error(f'删除模型 {model_id} 失败: {str(e)}')
  1280. results['failed'].append({
  1281. 'id': model_id,
  1282. 'reason': str(e)
  1283. })
  1284. # 构建响应消息
  1285. message = f"成功删除 {len(results['success'])} 个模型"
  1286. if results['datasets_deleted']:
  1287. message += f", {len(results['datasets_deleted'])} 个关联数据集"
  1288. if results['failed']:
  1289. message += f", {len(results['failed'])} 个模型删除失败"
  1290. return jsonify({
  1291. 'message': message,
  1292. 'results': results
  1293. }), 200
  1294. except Exception as e:
  1295. logger.error(f'批量删除模型失败: {str(e)}')
  1296. return jsonify({'error': str(e)}), 500