How to serialize list of SQLAlchemy models to json with Pydantic?

Question

I'm trying to serialize to json or dict list of models of SQLAlchemy to then assert it with response.json():

from datetime import date
from typing import Annotated, Generic, TypeVar

from pydantic import BaseModel, Field

ResultItemSchema = TypeVar("ResultItemSchema", bound=BaseModel)


class TtzSchema(BaseModel):
    id: int
    name: str
    start_date: date


class PaginatedResponseSchema(BaseModel, Generic[ResultItemSchema]):
    count: int
    results: list[ResultItemSchema]


class Ttz(Base):
    __tablename__ = "ttz"

    id: int
    name: str
    start_date: date


cnt = 3
results = [Ttz(...) for i in range(cnt)]

PaginatedResponseSchema[TtzSchema].model_validate({"count": cnt, "results": results})  # does not work. how to serialize to json?

But this does not work.

Answer 1

You just need to add model_config = ConfigDict(from_attributes=True) to your schema:

class TtzSchema(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    name: str
    start_date: date

Full code example:

from datetime import date, datetime
from typing import Generic, TypeVar

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from pydantic import BaseModel, ConfigDict

class Base(DeclarativeBase):
    pass


ResultItemSchema = TypeVar("ResultItemSchema", bound=BaseModel)


class TtzSchema(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    name: str
    start_date: date


class PaginatedResponseSchema(BaseModel, Generic[ResultItemSchema]):
    count: int
    results: list[ResultItemSchema]


class Ttz(Base):
    __tablename__ = "ttz"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    start_date: Mapped[date]


cnt = 3
results = [Ttz(id=i, name=f"name {i}", start_date=datetime.now().date()) for i in range(cnt)]

res = PaginatedResponseSchema[TtzSchema].model_validate({"count": cnt, "results": results})

print(res)