FastAPI+SQLAlchemy写入PostgreSQL Player表触发500错误求助

使用FastAPI和SQLAlchemy向PostgreSQL数据库的Player表发送POST请求时，触发500内部服务器错误：

500: Internal Server Error: Object of type AttributeError is not JSON serializable

其他表（Questions和Choices）可正常工作，尝试创建类似新表仍遇到相同问题。

代码片段 #

models.py #

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from database import Base


class Players(Base):
    __tablename__ = "players"

    id = Column(Integer, primary_key=True)
    user_name = Column(String, index=True)
    # hashed_password = Column(String)
    # is_active = Column(Boolean, default=True)

    # items = relationship("Item", back_populates="owner")

class Questions(Base):
    __tablename__ = 'questions'
    #id (Eg: 69420)
    #question = Have u seen my scrunchie?
    id = Column(Integer, primary_key=True, index=True)
    question_text = Column(String, index=True)
    # answer = Column(String)

class Choices(Base):
    __tablename__ = 'choices'

    # id
    #choices: A: yes, B: No
    #is_correct: True/False
    #question_id: foreign key to questions table 
    id = Column(Integer, primary_key=True, index=True)
    choice_text = Column(String, index=True)
    #column with answer either True/False
    is_correct = Column(Boolean, default=False)
    #Links back to question (Eg: 69420)
    question_id = Column(Integer, ForeignKey("questions.id"))



# class User(Base):
#     __tablename__ = "users"

#     id = Column(Integer, primary_key=True, index=True)

main.py #

from fastapi import FastAPI, HTTPException, Depends, Request
from pydantic import BaseModel
from typing import List, Annotated

from fastapi.exceptions import RequestValidationError
from fastapi.responses import PlainTextResponse

import models, crud, schemas
from database import engine, SessionLocal
from sqlalchemy.orm import Session

from starlette.exceptions import HTTPException
from fastapi.exceptions import RequestValidationError
from exception_handlers import request_validation_exception_handler, http_exception_handler, unhandled_exception_handler
from middleware import log_request_middleware

app = FastAPI()

app.middleware("http")(log_request_middleware)
app.add_exception_handler(RequestValidationError, request_validation_exception_handler)
app.add_exception_handler(HTTPException, http_exception_handler)
app.add_exception_handler(Exception, unhandled_exception_handler)

models.Base.metadata.create_all(bind=engine)

class PlayerBase(BaseModel):
    user_name: str
#Model rep the choice user makes and whether its True/False
class ChoiceBase(BaseModel):
    choice_text: str
    is_correct: bool

#Model rep the possible choices a user can make A, B, C, D
class QuestionBase(BaseModel):
    question_text: str
    choices: List[ChoiceBase]
    

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

db_dependency = Annotated[Session, Depends(get_db)]

@app.exception_handler(RequestValidationError)
async def validation_exception_handler(request, exc):
    return PlainTextResponse(str(exc), status_code=400)

@app.post("/players")
async def create_player(user: PlayerBase, db: db_dependency):
    try:
        db_player = models.Player(user_name=user.user_name)
        db.add(db_player)
        db.commit()
        db.refresh(db_player)
    except Exception as e:
        print(e)
        raise HTTPException(status_code=404, detail=e)
        
# @app.get("/users/", response_model=list[schemas.User])
# def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
#     users = crud.get_users(db, skip=skip, limit=limit)
#     return users

@app.post("/questions")
async def create_questions(question: QuestionBase, db: db_dependency):
    db_question = models.Questions(question_text=question.question_text)
    print("This works for some stupid reason")
    db.add(db_question)
    db.commit()
    #Refresh
    db.refresh(db_question)
    for choice in question.choices:
        #quick filter for db choice, look into model. choices
        db_choice = models.Choices(choice_text=choice.choice_text, is_correct=choice.is_correct, question_id=db_question.id)
        db.add(db_choice)
    db.commit()

@app.get("/questions/{questions_id}")
async def read_question(question_id: int, db: db_dependency):
    result = db.query(models.Questions).filter(models.Questions.id == question_id).first()
    if not result:
        raise HTTPException(status_code=404, detail='Question is not found')
    return result

@app.get("/choices/{question_id}")
async def read_choices(question_id: int, db: db_dependency):
    result = db.query(models.Choices).filter(models.Choices.question_id == question_id).all()
    if not result:
        raise HTTPException(status_code=404, detail='Choices is not found')
    return result

解决方案 #

1. 修正模型类名引用错误 #

main.py的create_player函数中引用了models.Player，但models.py中定义的类是Players（复数形式），这会触发AttributeError（找不到Player类）。

修改create_player中的代码：

db_player = models.Players(user_name=user.user_name)

2. 修复异常处理的JSON序列化问题 #

捕获异常时直接将Exception对象e作为HTTPException的detail参数传入，FastAPI无法将异常对象序列化为JSON，导致出现Object of type AttributeError is not JSON serializable错误。

修改异常处理部分，将异常转为字符串：

except Exception as e:
    print(e)
    raise HTTPException(status_code=400, detail=str(e))

（注：将状态码改为400更合理，因为错误来自请求处理中的参数/模型错误，而非资源不存在）

3. 可选：添加返回模型 #

为规范返回格式，可给Players定义对应的Pydantic返回模型，并在路由中指定response_model：

# 在main.py中添加
class PlayerResponse(PlayerBase):
    id: int

    class Config:
        orm_mode = True

# 修改路由
@app.post("/players", response_model=PlayerResponse)
async def create_player(user: PlayerBase, db: db_dependency):
    try:
        db_player = models.Players(user_name=user.user_name)
        db.add(db_player)
        db.commit()
        db.refresh(db_player)
        return db_player
    except Exception as e:
        print(e)
        raise HTTPException(status_code=400, detail=str(e))

验证修改后 #

完成以上修改后，重新发送POST请求到/players，即可正常创建Player记录并返回正确的JSON响应，不再触发500错误。

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