Is there a way to remove the primary key from the SQLAlchemy query results?-CodePudding

I am working on a application with FastAPI, Pydantic and SQLAlchemy.

I want to return data matching a Pydantic scheme like

class UserResponseBody(BaseModel):
    name: str
    age: int

The database model looks like

class User(Base):

    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    age = Column(Integer)

When I query the users in CRUD the records also contain the primary_key id, which I don't want to expose to the user.

So far I am converting the query results to a dict and pop the primary key like

# https://stackoverflow.com/a/37350445/7919597
def object_as_dict(obj):
    return {c.key: getattr(obj, c.key)
            for c in inspect(obj).mapper.column_attrs}

query_result = db.query(models.User).first()

query_result_dict = object_as_dict(obj)
query_result_dict.pop("id", None)

return UserResponseBody(**query_result_dict)

But that feels kind of hacky and I would like to ask, if someone knows a better solution to this.

CodePudding user response：

You already have your response model defined, you just need to tell FastAPI that you want to use it, and that Pydantic should attempt to use .property-notation to resolve values as well:

class UserResponseBody(BaseModel):
    name: str
    age: int

    class Config:
        orm_mode = True


@app.get('/users/first', response_model=UserResponseBody)
def get_first_user():
    return db.query(models.User).first()

Only the fields defined in your response_model will be included, which doesn't include id in your case. No need to do the conversion manually, FastAPI and Pydantic does what you want as long as you've told them what you want.