How to properly handle join results from SQLAlchemy with Pydantic/FastAPI

Question

I would like a piece of advice on handling the result of a join operation performed in SQLAlchemy and do the serialization with Pydantic (in FastAPI).

If I am not mistaken, the result of the join on two table leads to a list of tuples of SQLAlchemy models. Here is a mock for it, like_a_join being my understanding for the result of a join query.

from pydantic import BaseModel
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(BaseModel):
    a: int

    class Config:
        orm_mode = True


class B(BaseModel):
    b: int

    class Config:
        orm_mode = True


class Am(Base):
    __tablename__ = "A"
    a = Column(Integer, primary_key=True, nullable=False)


class Bm(Base):
    __tablename__ = "B"
    b = Column(Integer, primary_key=True, nullable=False)


def like_a_join():
    return [(Am(a=1), Bm(b=1))]

While it is possible to pass a model object to Pydantic with from_orm for simple query (which is done for example on the FastAPI Postgres cookie cutter, here, this is not obvious to me how to best handle the join/tuple case.

Would it make sense to create a class for handling the tuple like the following?

from typing import List
from pydantic import parse_obj_as
from dataclasses import dataclass

@dataclass
class Cm:
    a: Am
    b: Bm

class C(BaseModel):
    a: A
    b: B

    class Config:
        orm_mode = True

def like_a_join_with_class():
    return [Cm(a=Am(a=1), b=Bm(b=1))]

print(parse_obj_as(List[C], like_a_join_with_class()))

Would it be better to use a dictionary?

def like_a_join_with_dict():
    return [{"a": Am(a=1), "b": Bm(b=1)}]

The idea behind would be to include the query result in a FastAPI endpoint with serialization handled automatically.

Thanks in advance for your help.

igoras1993 · Answer 1 · 2021-10-12T07:21:16.803

It is always good to have a pydantic model to handle and manipulate Your data, but it is especially practical when You want to return such join from an endpoint. In such case You can use a pydantic model to cover a path operation's response_model argument:

@router.get("/some_endpoint_path", response_model=SomePydanticModel)
def request_handler():
    ...

If this is Your case, then I would create a model that covers a whole query result (i.e. list of 2-tuple [A, B]). If ORM Am maps to pydantic A, and ORM Bm maps to pydantic B, then whole query result should successfully map to the following pydantic model:

import typing as t

class JoinResult(BaseModel):
    results: t.List[t.Tuple[A, B]]

    class Config:
        orm_mode = True

The exact form of sqlalchemy query depends on the sqlalchemy version You are using and the abstraction level You are choosing, but for modern 2.x style queries with async ORM session, it would look (more or less) like this:

statement = select(Am, Bm).join(<your_join_here>).where(<your_condition_here>)
result = await session.execute(statement)
scalar_results = result.scalars()

This scalar_results should be list-like object containing 2-tuples holding instances of sqlalchemy models Am and Bm. You should be able to parse it as follows:

jr = JoinResult(results=iter(scalar_results))

You can then return jr directly from Your path operation function.

Alternatively, if You really want to return query results directly, You can try the new project from tiangolo: SQLModel. This is a missing bridge between pydantic and sqlalchemy. It may bring You some ideas and solutions. But keep in mind that SQLModel is in very early stage of development, potentially with many bugs. Report them however, tiangolo will be happy :)

The shortcoming with this approach, if I am not mistaken, is that, in `request_handler` , one has to to do the `JoinResult(results=scalar_results)` in the function and not pass directly `scalar_results` as the model for `response_model` would bark that the field `results` is missing. — Flavien Lambert, Sep 30 '21 at 06:58
So, in that sense, it is different from the approach where the result of the query is returned directly as the examples given in the link I shared. — Flavien Lambert, Sep 30 '21 at 06:59
Thats right. I am fine with this shortcoming because I usually do not return raw dicts/list anyway (explicit is better than implicit here). If You really want to return raw dict You can always do something like `return {"results": scalar_results}`. — igoras1993, Oct 04 '21 at 09:32
I have edited my answer, corrected some code mistakes (yesterday I have done just the same thing and tested the code :p). I've added hint on SQLModel library, You can check it out. — igoras1993, Oct 12 '21 at 07:23

score 0 · Answer 2 · answered Aug 19 '23 at 09:23

I faced a similar issue where my table A held the Name of the item and B being a mediator held the item Value, A.id and C.ID where Table C held what values belong to a entry.

where I wanted to return merged results to the user on a expand items request.

Models

Table A:
    id: int
    name: str
    desc: str
    created_by: str
    created_by: datetime
    modified_by: str
    modified_on: datetime

Table B:
   id: int (autoincrement ) 
   item_id ( FK A.id )
   value: str
   belongs_id ( FK C.id )
   created_by: str
   created_by: datetime
   modified_by: str
   modified_on: datetime

Table C:
   id: int (autoincrement )
   name: str
   desc: str

joined schema

GetItemValueMerged(BaseModel)
    id: int
    name: str ( comes from Table A)
    value: str
    created_by: str
    created_by: datetime
    modified_by: str
    modified_on: datetime

I was able to join TableA and TableB, filter on C.id and select columns I want as result.

items_in_db.attributes = db.query(
    B.id,
    B.value,
    B.created_by,
    B.created_on,
    B.modified_by,
    B.modified_on,
    A.name).join(
        A, A.item_id == B.id
    ).filter(
    B.belongs_id == belongs_id ).all()

and remap the new data to preferred schema(GetCiAttributewithValue) before returning

attr_merged = []
for _tmpitem in items_in_db.attributes:
  attr_merged.append(GetCiAttributewithValue.model_validate(_tmpitem ._asdict()))

items_in_db.attributes = attr_merged 

return items_in_db

This way it was presenting a define model, which OpenAPI can intercept, which for me helped when generating stubs for TS/AXIOS with openapi generator.

How to properly handle join results from SQLAlchemy with Pydantic/FastAPI

2 Answers2