Paginate SQLalchemy ORM model and return a Custom Pydantic Model as response

[damilareisaac]

I have 2 models for an entity. One is a pydantic model and the other is an Sqlachemy ORM model.

@mapper_registry.mapped
@dataclass
class SampleORMUser:
    id: float = field(
        init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)}
    )
   name: field()
   email: field()
   technical_code: field()
    site: Optional[Site] = field()

class SamplePydanticUser(BaseModel):
    id: float
    name: str

Page = Page.with_custom_options(size=Query(20, ge=1, le=50))

This works fine:

    @router.get("/")
async def get_all_users(db: Session = Depends(get_db)) -> Page[SampleORMUser]:
    return paginate(db, select(SampleORMUser).order_by(SampleORMUser.id))

But I needed the responds to be a Pydantic response; this use some fields from the database models and not all the fields

    @router.get("/")
async def get_all_users(db: Session = Depends(get_db)) -> Page[SamplePydanticUser]:
    return paginate(db, select(SampleORMUser).order_by(SampleORMUser.id))

Got an error pydantic_core._pydantic_core.ValidationError: validation errors for CustomizedPage[SamplePydanticUser] Not sure what to change to make this work. From using transformer but not working

[uriyyo]

Hi @damilareisaac,

Could you please show exception traceback? And also what version of pydantic are you using?

[uriyyo]

Is it the correct definition of orm model? Could you update your SampleORMUser definition:

@mapper_registry.mapped
@dataclass
class SampleORMUser:
    id: float = field(
        init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)}
    )
   name: str = field()  # here
   email: str = field() # here
   technical_code: str = field() # here
   site: Optional[Site] = field()

[damilareisaac]

from __future__ import annotations

from dataclasses import field
from typing import Optional

from pydantic.dataclasses import dataclass
from sqlalchemy import (
    CHAR,
    VARCHAR,
    CheckConstraint,
    Column,
    ForeignKeyConstraint,
    Index,
    PrimaryKeyConstraint,
    UniqueConstraint,
    text,
)
from sqlalchemy.dialects.oracle import NUMBER
from sqlalchemy.orm import relationship

from .site import Site

@mapper_registry.mapped
@dataclass
class SampleORMUser:
    __tablename__ = "biologics_user"
    __table_args__ = (
        CheckConstraint("account = lower(account)", name="sys_c0048503"),
       # some other contstraint
      ###
        )
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(
        init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)}
    )
    account: str = field(metadata={"sa": Column(VARCHAR(64), nullable=False)})
    name: str = field(metadata={"sa": Column(VARCHAR(100), nullable=False)})
    active: str = field(
        metadata={"sa": Column(CHAR(1), nullable=False, server_default=text("'Y' "))}
    )
    authentication_hash: Optional[str] = field(
        default=None, metadata={"sa": Column(VARCHAR(128))}
    )
    email: Optional[str] = field(default=None, metadata={"sa": Column(VARCHAR(100))})
    site_id: Optional[float] = field(
        default=None, metadata={"sa": Column(NUMBER(15, 0, False))}
    )
    organization_id: Optional[float] = field(
        default=None, metadata={"sa": Column(NUMBER(15, 0, False))}
    )
    department_id: Optional[float] = field(
        default=None, metadata={"sa": Column(NUMBER(15, 0, False))}
    )
    technician_code_id: Optional[float] = field(
        default=None, metadata={"sa": Column(NUMBER(15, 0, False))}
    )
    laboratory_code_id: Optional[float] = field(
        default=None, metadata={"sa": Column(NUMBER(15, 0, False))}
    )
    business_function_id: Optional[float] = field(
        default=None, metadata={"sa": Column(NUMBER(15, 0, False))}
    )
    site: Optional[Site] = field(
        default=None, metadata={"sa": relationship("Site", backref="biologics_user")}
    )

class SamplePydanticUser(BaseModel):
    id: float
    account: str
    name: str
    active: str
    email: str | None = None
    site: SitePydantic | None = None

[damilareisaac]

Thank you for your quick response, I am able to get pass it using the transformers as mentioned earlier.

passed to transformer fixed it

def transform_schema(x, response_model=None):
    return [SampleUserPydantic(**i.__dict__) for i in x]

return paginate(db, select(SampleORMUser), transformer=partial(transform_schema, response_model=SamplePydanticUser))

Please let me know if there are other approach

[uriyyo]

It should work without transformer.

Are you using pydantic v1 or v2?

Could you please show me the whole exception message? It should look like this:

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/uvicorn/protocols/http/h11_impl.py", line 412, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/uvicorn/middleware/proxy_headers.py", line 84, in __call__
    return await self.app(scope, receive, send)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/fastapi/applications.py", line 1054, in __call__
    await super().__call__(scope, receive, send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/applications.py", line 123, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/middleware/errors.py", line 186, in __call__
    raise exc
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/middleware/errors.py", line 164, in __call__
    await self.app(scope, receive, _send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/middleware/exceptions.py", line 62, in __call__
    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/routing.py", line 758, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/routing.py", line 778, in app
    await route.handle(scope, receive, send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/routing.py", line 299, in handle
    await self.app(scope, receive, send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/routing.py", line 79, in app
    await wrap_app_handling_exceptions(app, request)(scope, receive, send)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/starlette/routing.py", line 74, in app
    response = await func(request)
               ^^^^^^^^^^^^^^^^^^^
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/fastapi/routing.py", line 299, in app
    raise e
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/fastapi/routing.py", line 294, in app
    raw_response = await run_endpoint_function(
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/fastapi/routing.py", line 191, in run_endpoint_function
    return await dependant.call(**values)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mnt/d/projects/fastapi-pagination/foo.py", line 11, in get_items
    return paginate([1, 2, 3, 4, 5, 6, 7, 8, "a"])
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mnt/d/projects/fastapi-pagination/fastapi_pagination/paginator.py", line 28, in paginate
    return create_page(
           ^^^^^^^^^^^^
  File "/mnt/d/projects/fastapi-pagination/fastapi_pagination/api.py", line 181, in create_page
    return _page_val.get().create(items, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mnt/d/projects/fastapi-pagination/fastapi_pagination/default.py", line 73, in create
    return create_pydantic_model(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/mnt/d/projects/fastapi-pagination/fastapi_pagination/utils.py", line 149, in create_pydantic_model
    return model_cls.model_validate(kwargs, from_attributes=True)  # type: ignore
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/uriyyo/.cache/pypoetry/virtualenvs/fastapi-pagination-qCLVCiBY-py3.11/lib/python3.11/site-packages/pydantic/main.py", line 509, in model_validate
    return cls.__pydantic_validator__.validate_python(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pydantic_core._pydantic_core.ValidationError: 1 validation error for Page[int]
items.8
  Input should be a valid integer, unable to parse string as an integer [type=int_parsing, input_value='a', input_type=str]
    For further information visit https://errors.pydantic.dev/2.6/v/int_parsing

[damilareisaac]

I am using pydantic 2.6.1 it is not working without transformer

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 412, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\uvicorn\middleware\proxy_headers.py", line 84, in __call__
    return await self.app(scope, receive, send)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi\applications.py", line 1054, in __call__
    await super().__call__(scope, receive, send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\applications.py", line 123, in __call__
    await self.middleware_stack(scope, receive, send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\middleware\errors.py", line 186, in __call__
    raise exc
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\middleware\errors.py", line 164, in __call__
    await self.app(scope, receive, _send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\middleware\exceptions.py", line 62, in __call__
    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\routing.py", line 758, in __call__
    await self.middleware_stack(scope, receive, send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\routing.py", line 778, in app
    await route.handle(scope, receive, send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\routing.py", line 299, in handle
    await self.app(scope, receive, send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\routing.py", line 79, in app
    await wrap_app_handling_exceptions(app, request)(scope, receive, send)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\starlette\routing.py", line 74, in app
    response = await func(request)
               ^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi\routing.py", line 299, in app
    raise e
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi\routing.py", line 294, in app
    raw_response = await run_endpoint_function(
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi\routing.py", line 191, in run_endpoint_function
    return await dependant.call(**values)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\src\routers\biologic_users.py", line 25, in get_all_biologics_users
    return paginate(db, select(SampleORMUser))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi_pagination\ext\sqlalchemy.py", line 221, in paginate
    return exec_pagination(query, params, conn, transformer, additional_data, subquery_count, unique, async_=False)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi_pagination\ext\sqlalchemy.py", line 134, in exec_pagination
    return create_page(
           ^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi_pagination\api.py", line 181, in create_page
    return _page_val.get().create(items, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi_pagination\default.py", line 73, in create
    return create_pydantic_model(
           ^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\fastapi_pagination\utils.py", line 149, in create_pydantic_model
    return model_cls.model_validate(kwargs, from_attributes=True)  # type: ignore
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "Documents\CWD\olap_api\.venv\Lib\site-packages\pydantic\main.py", line 509, in model_validate
    return cls.__pydantic_validator__.validate_python(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pydantic_core._pydantic_core.ValidationError: 11 validation errors for CustomizedPage[SamplePydanticUser]
items.1.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.2.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.3.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.4.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.5.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.6.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.7.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.8.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.13.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.15.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type
items.19.organization.id
  Input should be a valid string [type=string_type, input_value=141, input_type=int]
    For further information visit https://errors.pydantic.dev/2.6/v/string_type

[uriyyo]

@damilareisaac Could you please update your organization schema model config and add:

class YourOrganizationModel(BaseModel):
    model_config = {
        "coerce_numbers_to_str": True,
    }

[damilareisaac]

Thank you, this works

[uriyyo]

Great, happy to hear that!