使用FastAPI+SQLAlchemy创建按钮时遇UnmappedInstanceError求助

使用Pydantic、FastAPI+SQLAlchemy和PostgreSQL开发项目，尝试创建新按钮（或分类）时遇到UnmappedInstanceError错误。

以下是相关代码：

routes目录下的button.py #

from fastapi import HTTPException, APIRouter
from fastapi_sqlalchemy import db

from .models.button import ButtonCreate, ButtonUpdate, ButtonGet
from ..models.database import Button
from .category import get_category

button = APIRouter(
    tags=["button"],
    responses={200: {"description": "Ok"}}
)


@button.post("/", response_model=ButtonCreate)
def create_button(button: ButtonCreate):
    db_category = get_category(category_id=button.category_id)
    if db_category is None:
        raise HTTPException(status_code=404, detail="Category does not exist")
    db_button = ButtonCreate(category_id=button.category_id, name=button.name,
                             icon=button.icon)
    db.session.add(db_button)
    return db_button


@button.get("/", response_model=list[ButtonGet])
def get_buttons(skip: int = 0, limit: int = 100):
    return db.session.query(Button).offset(skip).limit(limit).all()


@button.get("/{button_id}", response_model=ButtonGet)
def get_button(button_id: int):
    db_button = db.session.query(Button).filter(Button.id == button_id).first()
    if db_button is None:
        raise HTTPException(status_code=404, detail="Button does not exist")
    return db_button


@button.delete("/")
def remove_button(button_id: int):
    db_button = get_button(button_id=button_id)
    if db_button is None:
        raise HTTPException(status_code=404, detail="Button does not exist")
    db.session.query(Button).filter(Button.id == button_id).first()


@button.patch("/", response_model=ButtonUpdate)
def update_button(button: ButtonUpdate):
    db_old_button = get_button(button_id=button.id)
    if db_old_button is None:
        raise HTTPException(status_code=404, detail="Button does not exist")
    return db.session.query(Button).update(button)

models目录下的button.py（数据模型） #

from .base import Base
from typing import Optional


class ButtonCreate(Base):
    category_id: int
    icon: Optional[str]
    name: Optional[str]


class ButtonUpdate(Base):
    id: int
    category_id: Optional[int]
    icon: Optional[str]
    name: Optional[str]


class ButtonGet(Base):
    id: int

自定义Base类（继承自Pydantic BaseModel） #

from pydantic import BaseModel


class Base(BaseModel):
    def __repr__(self) -> str:
        attrs = []
        for k, v in self.__class__.schema().items():
            attrs.append(f"{k}={v}")
        return "{}({})".format(self.__class__.__name__, ', '.join(attrs))

    class Config:
        orm_mode = True

问题原因 #

UnmappedInstanceError的核心原因：你试图将Pydantic模型实例（ButtonCreate）添加到SQLAlchemy的session中，但SQLAlchemy仅能管理映射到数据库表的ORM模型实例（即models.database.Button）。Pydantic模型仅用于数据验证和序列化，并非数据库实体。

此外代码还有其他问题：

• 创建按钮时未提交事务（db.session.commit()），即使实例正确也无法写入数据库
• 删除按钮的函数仅查询实例，未执行删除操作
• 更新按钮时直接传入Pydantic实例，SQLAlchemy需要字典格式的数据

修复方案 #

1. 修正创建按钮逻辑：实例化SQLAlchemy的Button模型而非Pydantic的ButtonCreate，并提交事务：

@button.post("/", response_model=ButtonCreate)
def create_button(button: ButtonCreate):
    db_category = get_category(category_id=button.category_id)
    if db_category is None:
        raise HTTPException(status_code=404, detail="Category does not exist")
    # 实例化SQLAlchemy的ORM模型
    db_button = Button(category_id=button.category_id, name=button.name, icon=button.icon)
    db.session.add(db_button)
    db.session.commit()  # 提交事务
    db.session.refresh(db_button)  # 刷新实例获取自动生成的ID等字段
    return db_button

2. 修复删除按钮函数：查询到实例后执行删除并提交事务：

@button.delete("/")
def remove_button(button_id: int):
    db_button = get_button(button_id=button_id)
    if db_button is None:
        raise HTTPException(status_code=404, detail="Button does not exist")
    db.session.delete(db_button)
    db.session.commit()
    return {"detail": "Button deleted successfully"}

3. 修复更新按钮函数：将Pydantic实例转为字典，提交事务并返回更新后的实例：

@button.patch("/", response_model=ButtonUpdate)
def update_button(button: ButtonUpdate):
    db_old_button = get_button(button_id=button.id)
    if db_old_button is None:
        raise HTTPException(status_code=404, detail="Button does not exist")
    # 将Pydantic实例转为字典，只更新设置过的字段
    update_data = button.dict(exclude_unset=True)
    db.session.query(Button).filter(Button.id == button.id).update(update_data)
    db.session.commit()
    db.session.refresh(db_old_button)
    return db_old_button

内容的提问来源于stack exchange，提问作者Станислав Рославцев