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

Question

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.

I'm just guessing, but `for c in inspect(obj).mapper.column_attrs if c.key != 'id'` seems like it would work. — John Gordon, Nov 20 '22 at 17:41
I don't understand the problem here. `UserResponseBody(**{'id': 1, 'name': 'Alice', 'age': 42}).json` -> `'{"name": "Alice", "age": 42}'`. `id` is not present in the Pydantic model or its serialised output. — snakecharmerb, Nov 20 '22 at 17:44
You can have a separate output model without that field. See the [documentation](https://fastapi.tiangolo.com/tutorial/response-model/#add-an-output-model). — Chris, Nov 20 '22 at 18:17

score 3 · Accepted Answer · answered Nov 20 '22 at 18:56

3

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.

answered Nov 20 '22 at 18:56

MatsLindh

49,529
4
53
84

Is there a way to unittest models to see that this behavior works? Like take the schema and the db model and check if the `response_model` triggers a `ValidationError`? – Joe Nov 21 '22 at 13:03
What kind of validation error are you looking for? i.e. what is the case you want to test? You can use `TestClient` to make requests to the endpoint that you can test and have requests that fail/etc. depending on what you're giving as information (i.e. do it with an empty database and get `None` back, which hasn't been indicated as a valid response here - you'd probably do that by wrapping `UserResponseBody` in `Optional`). – MatsLindh Nov 21 '22 at 13:15
I would like to have tests without the actual endpoints and TestClients, just with instances of schemas and model. So far I have to spin up the complete app to test this and this is a bit tedious. I would like to have a function like `convert_object_to_response_model(obj, response_model)` which would maybe throw an `ValidationError` if the conversion does not work. – Joe Nov 21 '22 at 14:10
You can use `UserResponseBody.from_orm(...)` on the model class directly to attempt to load an ORM result object, or you can use `parse_obj` or `parse_obj_as` from the Pydantic helper functions (availble directly under `pydantic`). – MatsLindh Nov 21 '22 at 14:12
Sorry, I don't have an MCVE at the moment, but I get a `ValidationError extra fields not permitted` when there is `extra = Extra.forbid` set in `UserResponseBody` and it is used as `response_model=UserResponseBody`. The extra field is the primary key of the database and I would have expected for it to be dropped if it is not used in the `response_model`. Could you maye shed some light on this? The object used to initialize it is the database query. – Joe Nov 21 '22 at 15:32
Does the dropping of fields in the `response_model` only work with `extra = Extra.ignore`? – Joe Nov 21 '22 at 15:45
1

If you explicitly tell Pydantic that fields that aren't converted are forbidden, yes, that will be an error. That's the point of saying `extra.forbid` - that if there are extra fields outside of those defined, that's an error. If you want extra fields to be ignored, leave it at its default (`Extra.ignore`). If you're telling Pydantic that additional fields to those that you've defined aren't allowed, it shouldn't come as a surprise that Pydantic raises an error if that occurs. – MatsLindh Nov 21 '22 at 20:51

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

1 Answers1