FASTAPI异步删除预订函数异常：返回200但数据库无变更

核心问题：未提交数据库事务 #

你的代码执行了删除语句，但没有提交事务，导致数据库不会持久化删除操作。SQLAlchemy异步会话默认不会自动提交，必须手动触发提交或者使用事务上下文管理。

修改方案1：使用自动事务上下文 #

@classmethod
async def remove(cls, user_id: int, room_id: int):
        async with async_session_maker() as session:
            async with session.begin():  # 开启自动提交的事务上下文
                query = select(Rooms).filter_by(id=room_id)
                result = await session.execute(query)
                result = result.scalar_one_or_none()
                if result is None:
                    raise HTTPException(status_code=404, detail="Room not found")

                delete_query = delete(Bookings).filter_by(room_id=room_id, user_id=user_id)
                print(delete_query)
                await session.execute(delete_query)
                # 无需手动commit，session.begin()会自动完成提交

修改方案2：手动提交事务 #

@classmethod
async def remove(cls, user_id: int, room_id: int):
        async with async_session_maker() as session:
            query = select(Rooms).filter_by(id=room_id)
            result = await session.execute(query)
            result = result.scalar_one_or_none()
            if result is None:
                raise HTTPException(status_code=404, detail="Room not found")

            delete_query = delete(Bookings).filter_by(room_id=room_id, user_id=user_id)
            print(delete_query)
            await session.execute(delete_query)
            await session.commit()  # 手动提交事务

其他优化点 #

• 规范异常处理：不要直接抛出字典，改用FastAPI的HTTPException返回标准HTTP错误响应，让客户端能正确识别错误类型。
• 验证删除有效性：可以获取删除操作影响的行数，确认是否有匹配记录被删除：

result = await session.execute(delete_query)
row_count = result.rowcount
if row_count == 0:
    raise HTTPException(status_code=404, detail="该用户未预订此房间")

• 路由参数命名优化：把路由里的{id}改为{room_id}，让代码逻辑更清晰，避免参数歧义：

@router.delete("/{room_id}")
async def remove_booking(
        room_id: int,
        user: Users = Depends(get_current_user),
):
    await BookingDAO.remove(user.id, room_id)

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