routes.py 7.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268
  1. import sqlite3
  2. from flask import Blueprint, request, jsonify, g, current_app
  3. from .model import predict
  4. import pandas as pd
  5. from flask_sqlalchemy import SQLAlchemy
  6. # 创建蓝图 (Blueprint),用于分离路由
  7. bp = Blueprint('routes', __name__)
  8. def get_db():
  9. db = getattr(g, '_database', None)
  10. if db is None:
  11. # database_url = current_app.config['DATABASE']
  12. # db = g._database = sqlite3.connect(database_url)
  13. db = SQLAlchemy(current_app)
  14. return db
  15. @bp.route('/predict', methods=['POST'])
  16. def predict_route():
  17. try:
  18. data = request.get_json()
  19. model_name = data.get('model_name', 'RF_filt') # 提取模型名称
  20. parameters = data.get('parameters', {}) # 提取所有参数
  21. input_data = pd.DataFrame([parameters]) # 转换参数为DataFrame
  22. predictions = predict(input_data, model_name) # 调用预测函数
  23. return jsonify({'predictions': predictions}), 200
  24. except Exception as e:
  25. return jsonify({'error': str(e)}), 400
  26. # 定义添加数据库记录的 API 接口
  27. @bp.route('/add_item', methods=['POST'])
  28. def add_item():
  29. """
  30. 接收 JSON 格式的请求体,包含表名和要插入的数据。
  31. 尝试将数据插入到指定的表中。
  32. :return:
  33. """
  34. db = get_db()
  35. try:
  36. # 确保请求体是JSON格式
  37. data = request.get_json()
  38. if not data:
  39. raise ValueError("No JSON data provided")
  40. table_name = data.get('table')
  41. item_data = data.get('item')
  42. if not table_name or not item_data:
  43. return jsonify({'error': 'Missing table name or item data'}), 400
  44. cur = db.cursor()
  45. # 动态构建 SQL 语句
  46. columns = ', '.join(item_data.keys())
  47. placeholders = ', '.join(['?'] * len(item_data))
  48. sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
  49. cur.execute(sql, tuple(item_data.values()))
  50. db.commit()
  51. # 返回更详细的成功响应
  52. return jsonify({'success': True, 'message': 'Item added successfully'}), 201
  53. except ValueError as e:
  54. return jsonify({'error': str(e)}), 400
  55. except KeyError as e:
  56. return jsonify({'error': f'Missing data field: {e}'}), 400
  57. except sqlite3.IntegrityError as e:
  58. # 处理例如唯一性约束违反等数据库完整性错误
  59. return jsonify({'error': 'Database integrity error', 'details': str(e)}), 409
  60. except sqlite3.Error as e:
  61. # 处理其他数据库错误
  62. return jsonify({'error': 'Database error', 'details': str(e)}), 500
  63. finally:
  64. db.close()
  65. # 定义删除数据库记录的 API 接口
  66. @bp.route('/delete_item', methods=['POST'])
  67. def delete_item():
  68. data = request.get_json()
  69. table_name = data.get('table')
  70. condition = data.get('condition')
  71. # 检查表名和条件是否提供
  72. if not table_name or not condition:
  73. return jsonify({
  74. "success": False,
  75. "message": "缺少表名或条件参数"
  76. }), 400
  77. # 尝试从条件字符串中分离键和值
  78. try:
  79. key, value = condition.split('=')
  80. except ValueError:
  81. return jsonify({
  82. "success": False,
  83. "message": "条件格式错误,应为 'key=value'"
  84. }), 400
  85. db = get_db()
  86. cur = db.cursor()
  87. try:
  88. # 执行删除操作
  89. cur.execute(f"DELETE FROM {table_name} WHERE {key} = ?", (value,))
  90. db.commit()
  91. # 如果没有错误发生,返回成功响应
  92. return jsonify({
  93. "success": True,
  94. "message": "记录删除成功"
  95. }), 200
  96. except sqlite3.Error as e:
  97. # 发生错误,回滚事务
  98. db.rollback()
  99. # 返回失败响应,并包含错误信息
  100. return jsonify({
  101. "success": False,
  102. "message": f"删除失败: {e}"
  103. }), 400
  104. # 定义修改数据库记录的 API 接口
  105. @bp.route('/update_item', methods=['PUT'])
  106. def update_record():
  107. data = request.get_json()
  108. # 检查必要的数据是否提供
  109. if not data or 'table' not in data or 'item' not in data:
  110. return jsonify({
  111. "success": False,
  112. "message": "请求数据不完整"
  113. }), 400
  114. table_name = data['table']
  115. item = data['item']
  116. # 假设 item 的第一个元素是 ID
  117. if not item or next(iter(item.keys())) is None:
  118. return jsonify({
  119. "success": False,
  120. "message": "记录数据为空"
  121. }), 400
  122. # 获取 ID 和其他字段值
  123. id_key = next(iter(item.keys()))
  124. record_id = item[id_key]
  125. updates = {key: value for key, value in item.items() if key != id_key} # 排除 ID
  126. db = get_db()
  127. cur = db.cursor()
  128. try:
  129. record_id = int(record_id) # 确保 ID 是整数
  130. except ValueError:
  131. return jsonify({
  132. "success": False,
  133. "message": "ID 必须是整数"
  134. }), 400
  135. # 准备参数列表,包括更新的值和 ID
  136. parameters = list(updates.values()) + [record_id]
  137. # 执行更新操作
  138. set_clause = ','.join([f"{k} = ?" for k in updates.keys()])
  139. sql = f"UPDATE {table_name} SET {set_clause} WHERE {id_key} = ?"
  140. try:
  141. cur.execute(sql, parameters)
  142. db.commit()
  143. if cur.rowcount == 0:
  144. return jsonify({
  145. "success": False,
  146. "message": "未找到要更新的记录"
  147. }), 404
  148. return jsonify({
  149. "success": True,
  150. "message": "数据更新成功"
  151. }), 200
  152. except sqlite3.Error as e:
  153. db.rollback()
  154. return jsonify({
  155. "success": False,
  156. "message": f"更新失败: {e}"
  157. }), 400
  158. # 定义查询数据库记录的 API 接口
  159. @bp.route('/search/record', methods=['GET'])
  160. def sql_search():
  161. """
  162. 接收 JSON 格式的请求体,包含表名和要查询的 ID。
  163. 尝试查询指定 ID 的记录并返回结果。
  164. :return:
  165. """
  166. try:
  167. data = request.get_json()
  168. # 表名
  169. sql_table = data['table']
  170. # 要搜索的 ID
  171. Id = data['id']
  172. # 连接到数据库
  173. db = get_db()
  174. cur = db.cursor()
  175. # 构造查询语句
  176. sql = f"SELECT * FROM {sql_table} WHERE id = ?"
  177. # 执行查询
  178. cur.execute(sql, (Id,))
  179. # 获取查询结果
  180. rows = cur.fetchall()
  181. column_names = [desc[0] for desc in cur.description]
  182. # 检查是否有结果
  183. if not rows:
  184. return jsonify({'error': '未查找到对应数据。'}), 400
  185. # 构造响应数据
  186. results = []
  187. for row in rows:
  188. result = {column_names[i]: row[i] for i in range(len(row))}
  189. results.append(result)
  190. # 关闭游标和数据库连接
  191. cur.close()
  192. db.close()
  193. # 返回 JSON 响应
  194. return jsonify(results), 200
  195. except sqlite3.Error as e:
  196. # 如果发生数据库错误,返回错误信息
  197. return jsonify({'error': str(e)}), 400
  198. except KeyError as e:
  199. # 如果请求数据中缺少必要的键,返回错误信息
  200. return jsonify({'error': f'缺少必要的数据字段: {e}'}), 400
  201. # 定义提供数据库列表,用于展示表格的 API 接口
  202. @bp.route('/tables', methods=['POST'])
  203. def get_table():
  204. data = request.get_json()
  205. table_name = data.get('table')
  206. if not table_name:
  207. return jsonify({'error': '需要表名'}), 400
  208. db = get_db()
  209. try:
  210. cur = db.cursor()
  211. cur.execute(f"SELECT * FROM {table_name}")
  212. rows = cur.fetchall()
  213. if not rows:
  214. return jsonify({'error': '表为空或不存在'}), 400
  215. headers = [description[0] for description in cur.description]
  216. return jsonify(rows=rows, headers=headers), 200
  217. except sqlite3.Error as e:
  218. return jsonify({'error': str(e)}), 400
  219. finally:
  220. db.close()