前端拖拽重排任务卡片后SQLite数据库持久化失败求助

我开发了一款任务管理器应用，任务以行的形式存储在SQLite数据库中，前端以卡片形式展示任务，用户可通过拖拽卡片重新排序，但排序结果无法有效持久化到数据库，请求返回500内部服务器错误。

应用工作流程 #

• 每个任务对应SQLite数据库中的一行
• 前端用户可拖拽任务卡片重新排序
• 已实现JavaScript功能捕获任务新顺序并发送至FastAPI端点
• 在FastAPI端点尝试根据新顺序更新数据库

当前实现代码 #

前端JavaScript代码 #

document.addEventListener('DOMContentLoaded', function() {
      const taskboards = document.getElementById('taskboard-grid');
      let selectedCard = null; // Variable to store the selected card
  
      // Event listener for double-click to select a card
      taskboards.addEventListener('dblclick', function(e) {
          if (e.target.classList.contains('task-card')) {
              if (selectedCard) {
                  // Deselect previously selected card
                  selectedCard.classList.remove('selected');
                  console.log('Deselected card:', selectedCard); // Debugging log
              }
              // Mark new selected card and add any visual indication if needed
              selectedCard = e.target;
              selectedCard.classList.add('selected');
              console.log('Selected card:', selectedCard); // Debugging log
          }
      });
  
      // Event listener for click to place the selected card before clicked card
      taskboards.addEventListener('click', function(e) {
          if (selectedCard && e.target.classList.contains('task-card') && selectedCard !== e.target) {
              // Move selected card before the clicked card
              e.target.parentNode.insertBefore(selectedCard, e.target);
              selectedCard.classList.remove('selected'); // Remove selection from moved card
              console.log('Moved selected card before:', e.target); // Debugging log
              selectedCard = null; // Reset selected card variable
              updateTaskOrder(); // Call function to update order in the backend
          }
      });
  
      function updateTaskOrder() {

        if (!currentTaskboard) {
          alert("No taskboard selected.");
          return;
        }
        const tasks = document.querySelectorAll('.task-card');
        const taskOrder = Array.from(tasks)
            .map(task => task.dataset.taskId)
            .filter(taskId => taskId !== undefined); // Filter out undefined elements

        console.log(currentTaskboard)
    
        console.log(JSON.stringify({ taskBoardName: currentTaskboard, order: taskOrder }));
        fetch('/update-order', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json',
            },
            body: JSON.stringify({ taskBoardName: currentTaskboard, order: taskOrder }) // Include task board name
        })
        .then(response => response.json())
        .then(data => console.log('Order updated successfully'))
        .catch(error => console.error('Error updating order:', error));
    }
    
    
  });

FastAPI后端代码 #

@app.post("/update-order")
async def update_order(data: dict):
    try:
        order = data.get('order')
        taskBoardName = data.get('taskBoardName')
        print(order, taskBoardName)
        # Assuming db is an instance of YourDatabaseClass
        db.updateTaskOrder(taskBoardName, order)
        return {"message": "Order updated successfully."}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


 def updateTaskOrder(self, table_name, order):
        print(table_name, order)
        with self.global_db:
            cursor = self.global_db.cursor()
            for idx, task_id in enumerate(order, start=1):
                print(idx, task_id)
                cursor.execute(f"UPDATE {table_name} SET task_order = ? WHERE id = ?", (idx, task_id))
            self.global_db.commit()

问题排查与修复方法 #

1. 表名/字段名合法性检查 #

• 确认taskBoardName对应的表存在，且表中包含task_order字段。若表名含特殊字符或SQL关键字，需用反引号包裹：

  cursor.execute(f"UPDATE `{table_name}` SET task_order = ? WHERE id = ?", (idx, task_id))
  

• 验证前端传递的taskId与数据库中id字段类型一致（如都是整数，避免字符串转整数失败）。

2. 数据库事务与连接优化 #

• 若self.global_db是已打开的连接，with self.global_db可能无法正确处理事务，改为手动控制事务：

  def updateTaskOrder(self, table_name, order):
      print(table_name, order)
      cursor = self.global_db.cursor()
      try:
          for idx, task_id in enumerate(order, start=1):
              print(idx, task_id)
              cursor.execute(f"UPDATE `{table_name}` SET task_order = ? WHERE id = ?", (idx, task_id))
          self.global_db.commit()
      except Exception as e:
          self.global_db.rollback()
          raise e
      finally:
          cursor.close()
  

3. 前端数据传递验证 #

• 检查currentTaskboard是否为有效表名，taskOrder数组中是否存在无效taskId（通过前后端的console.log/print确认）。
• 用Pydantic模型规范请求数据结构，避免字段缺失：

  from pydantic import BaseModel
  
  class UpdateOrderRequest(BaseModel):
      taskBoardName: str
      order: list[str | int]  # 根据taskId类型调整
  
  @app.post("/update-order")
  async def update_order(data: UpdateOrderRequest):
      try:
          order = data.order
          taskBoardName = data.taskBoardName
          print(order, taskBoardName)
          db.updateTaskOrder(taskBoardName, order)
          return {"message": "Order updated successfully."}
      except Exception as e:
          raise HTTPException(status_code=500, detail=str(e))
  

4. SQLite锁与并发问题 #

• SQLite写入时会锁定整个数据库，若有其他操作同时访问会导致更新失败，需在updateTaskOrder中捕获具体异常信息排查。

最佳实践与优化建议 #

• 避免SQL注入风险：不要直接用前端传递的表名，后端维护看板ID与表名的映射关系，前端传递看板ID而非表名。
• 批量更新优化：当前循环更新方式可行，确保事务正确提交即可；若需进一步优化，可考虑用临时表批量更新，但SQLite原生支持有限。
• 前端反馈优化：请求成功后确认排序状态，失败时给出明确错误提示，避免用户感知不一致。
• 索引优化：在task_order字段创建索引，提升排序查询性能：

  CREATE INDEX idx_task_order ON your_table_name(task_order);
  

内容的提问来源于stack exchange，提问作者FFFFFFFFF