FastAPI笔记应用实现“移至回收站”功能时数据插入失败问题

需求说明 #

开发笔记应用，基于FastAPI构建API，实现移至回收站功能：用户将帖子移至回收站后，若未在指定时间内恢复，帖子将被永久删除。现有两张表：page（原帖子表）和Deleted（回收站表），期望端点/posts/totrash/{id}实现以下逻辑：

• 将目标帖子插入Deleted表
• 从page表中删除该帖子

现有代码 #

路由处理代码 #

from fastapi import APIRouter, Depends, HTTPException, status, Response
# 假设Oathou2是自定义的认证模块
from . import Oathou2

router = APIRouter()

@router.delete("/posts/totrash/{id}", status_code=status.HTTP_204_NO_CONTENT)
def del_post(id: int, current_user=Depends(Oathou2.get_current_user)):
    user_id = current_user.id
    cur.execute("""select user_id from page where id = %s""", (str(id),))
    post_owner = cur.fetchone()
    
    if post_owner is None:
        raise HTTPException(status_code=status.HTTP_204_NO_CONTENT, detail=f"the post with the id : {id} doesn't exist")
    if int(user_id) != int(post_owner["user_id"]):
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="you are not the owner of this post")
    else:
        cur.execute("""select * from page where id=(%s)""", (str(id),))
        note = cur.fetchone()

        cur.execute("""insert into deleted(name, content, id, published , user_id , created_at)
         values(%s, %s, %s, %s, %s, %s) returning * """,
            (note["name"], note["content"], note["id"], note["published"], note["user_id"], note["created_at"])
                    )
        
        # 注释以下语句时，插入操作可正常执行
        cur.execute("""delete from page where id = %s""", (str(id),))
        conn.commit()
    return Response(status_code=status.HTTP_204_NO_CONTENT)

数据库连接代码 #

Database类定义

import psycopg2
from psycopg2.extras import RealDictCursor
import time
from . import settings

class Database:
    def connection(self):
        while True:
            try:
                conn = psycopg2.connect(
                    host=settings.host, 
                    database=settings.db_name, 
                    user=settings.db_user_name,
                    password=settings.db_password, 
                    cursor_factory=RealDictCursor
                )
                print("connected")
                break
            except Exception as error:
                print("connection faild")
                print(error)
                time.sleep(2)
        return conn

database = Database()

路由文件中的全局连接

from .dtbase import database

conn = database.connection()
cur = conn.cursor()

问题现象 #

执行端点请求后：

• 目标帖子成功从page表中删除
• Deleted表中未出现该帖子数据，插入操作失败
• 注释掉delete from page语句后，插入Deleted表的操作可正常执行

解决方案 #

1. 避免使用全局数据库连接和游标 #

FastAPI是并发框架，全局的conn和cur会导致多请求下的事务混乱（psycopg2的连接和游标并非线程安全）。应在每个请求内创建独立的连接和游标：
修改路由函数，将连接逻辑移入请求处理中：

@router.delete("/posts/totrash/{id}", status_code=status.HTTP_204_NO_CONTENT)
def del_post(id: int, current_user=Depends(Oathou2.get_current_user)):
    user_id = current_user.id
    # 每个请求创建独立连接和游标
    conn = database.connection()
    cur = conn.cursor()
    try:
        cur.execute("""select user_id from page where id = %s""", (str(id),))
        post_owner = cur.fetchone()
        
        if post_owner is None:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail=f"Post with id {id} does not exist")
        if int(user_id) != int(post_owner["user_id"]):
            raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="You are not the owner of this post")
        
        cur.execute("""select * from page where id=%s""", (str(id),))
        note = cur.fetchone()

        # 插入回收站表
        cur.execute("""insert into deleted(name, content, id, published , user_id , created_at)
         values(%s, %s, %s, %s, %s, %s)""",
            (note["name"], note["content"], note["id"], note["published"], note["user_id"], note["created_at"])
                    )
        
        # 删除原表数据
        cur.execute("""delete from page where id = %s""", (str(id),))
        conn.commit()
    except Exception as e:
        # 出现异常时回滚事务
        conn.rollback()
        raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=f"Operation failed: {str(e)}")
    finally:
        # 确保关闭游标和连接
        cur.close()
        conn.close()
    return Response(status_code=status.HTTP_204_NO_CONTENT)

2. 修复异常状态码错误 #

原代码中，当帖子不存在时返回204 NO CONTENT，不符合HTTP规范，应改为404 NOT FOUND，避免客户端混淆。

3. 检查Deleted表结构 #

确认Deleted表的id字段是否允许重复（若page表的id是主键，Deleted表的id若设为主键，当回收站已有相同id的记录时会导致插入失败）。可调整Deleted表结构，添加自增主键，将原帖子id存储为单独字段（如original_id）：

CREATE TABLE deleted (
    id SERIAL PRIMARY KEY,
    original_id INT NOT NULL,
    name VARCHAR NOT NULL,
    content TEXT,
    published BOOLEAN,
    user_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

同时修改插入语句对应的字段：

cur.execute("""insert into deleted(original_id, name, content, published , user_id , created_at)
 values(%s, %s, %s, %s, %s, %s)""",
    (note["id"], note["name"], note["content"], note["published"], note["user_id"], note["created_at"])
            )

4. 添加事务异常捕获 #

在操作中加入异常捕获和回滚逻辑，确保出现错误时不会只执行部分操作，同时能获取具体错误信息排查问题。

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