I would like to convert a SQLAlchemy model to a custom Pydantic model.

For instance, here is my SQLAlchemy one of my models:

class Program(Base):
    __tablename__ = "programs"

    id: Mapped[int] = mapped_column(primary_key=True)
    name_id: Mapped[int] = mapped_column(ForeignKey("localizations.id"))
    name: Mapped["Localization"] = relationship(foreign_keys="Program.name_id")

class Language(Base):
    __tablename__ = "languages"

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


class Localization(Base):
    __tablename__ = "localizations"

    id: Mapped[int] = mapped_column(primary_key=True)
    translations: Mapped[List["Translation"]] = relationship(back_populates="localization")


class Translation(Base):
    __tablename__ = "translations"

    id: Mapped[int] = mapped_column(primary_key=True)
    localization_id: Mapped[int] = mapped_column(ForeignKey("localizations.id"))
    localization: Mapped["Localization"] = relationship(back_populates="translations")
    text: Mapped[str] = mapped_column()
    language_id: Mapped[str] = mapped_column(ForeignKey("languages.id"))

I would like to create a response in the following format for a program:

{
    id: 1,
    name: "My localized name"
}

I found a way to do it using dictionaries, but I would like to know if it is possible to create a Pydantic model indicating that my name field would be a Translation of a Localization of the Program.name.

I hope I am clear!

Thanks for reading! :)

I would like to convert a SQLAlchemy model to a custom Pydantic model.

For instance, here is my SQLAlchemy one of my models:

class Program(Base):
    __tablename__ = "programs"

    id: Mapped[int] = mapped_column(primary_key=True)
    name_id: Mapped[int] = mapped_column(ForeignKey("localizations.id"))
    name: Mapped["Localization"] = relationship(foreign_keys="Program.name_id")

class Language(Base):
    __tablename__ = "languages"

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


class Localization(Base):
    __tablename__ = "localizations"

    id: Mapped[int] = mapped_column(primary_key=True)
    translations: Mapped[List["Translation"]] = relationship(back_populates="localization")


class Translation(Base):
    __tablename__ = "translations"

    id: Mapped[int] = mapped_column(primary_key=True)
    localization_id: Mapped[int] = mapped_column(ForeignKey("localizations.id"))
    localization: Mapped["Localization"] = relationship(back_populates="translations")
    text: Mapped[str] = mapped_column()
    language_id: Mapped[str] = mapped_column(ForeignKey("languages.id"))

I would like to create a response in the following format for a program:

{
    id: 1,
    name: "My localized name"
}

I found a way to do it using dictionaries, but I would like to know if it is possible to create a Pydantic model indicating that my name field would be a Translation of a Localization of the Program.name.

I hope I am clear!

Thanks for reading! :)

• sqlalchemy
• fastapi
• pydantic

• I don't get what you want. You can create every pydantic model you want, you just have to fill it with senseful data. Which part from "Got my DB response" to "Create API response" do you have problems with? – lord_haffi Commented Nov 20, 2024 at 13:51
• @lord_haffi I would like to be able to automatically map a model instance into a specific form. – TheJof Commented Nov 21, 2024 at 9:16
• What kind of automation? Why can't you just write a function which takes the ORMs, extracts the information you want and creates a respective response model? – lord_haffi Commented Nov 21, 2024 at 21:26
• Of course, you could also create a view for this but I don't know if SQLAlchemy supports ORM for database views. – lord_haffi Commented Nov 21, 2024 at 21:29
• I did write a function for that, but as I have many base classes using a localization, I would like to be able to write it more efficiently. – TheJof Commented Nov 24, 2024 at 18:58

1 Answer 1

As I wrote in the comments, I'd suggest using database views. I found an example of how you can tweak SQLAlchemy to do this here: https://github/sqlalchemy/sqlalchemy/wiki/Views

I didn't test the code and I don't really understand your model, so you have to write the join query yourself. But try something along these lines:

import sqlalchemy as sa
from sqlalchemy.ext import compiler
from sqlalchemy.schema import DDLElement
from sqlalchemy.sql import table


class CreateView(DDLElement):
    def __init__(self, name, selectable):
        self.name = name
        self.selectable = selectable


class DropView(DDLElement):
    def __init__(self, name):
        self.name = name


@compilerpiles(CreateView)
def _create_view(element, compiler, **kw):
    return "CREATE VIEW %s AS %s" % (
        element.name,
        compiler.sql_compiler.process(element.selectable, literal_binds=True),
    )


@compilerpiles(DropView)
def _drop_view(element, compiler, **kw):
    return "DROP VIEW %s" % (element.name)


def view_exists(ddl, target, connection, **kw):
    return ddl.name in sa.inspect(connection).get_view_names()


def view_doesnt_exist(ddl, target, connection, **kw):
    return not view_exists(ddl, target, connection, **kw)


def view(name, metadata, selectable):
    t = table(name)

    t._columns._populate_separate_keys(
        col._make_proxy(t) for col in selectable.selected_columns
    )

    sa.event.listen(
        metadata,
        "after_create",
        CreateView(name, selectable).execute_if(callable_=view_doesnt_exist),
    )
    sa.event.listen(
        metadata, "before_drop", DropView(name).execute_if(callable_=view_exists)
    )
    return t


class LocalizedName(Base):
    __table__ = view(
            "localized_view",
            Base.metadata,
            sa.select(  # Build your join query here
                # ...
            )
        )
    
    if TYPE_CHECKING:
        # Don't know if this type checking branch is necessary here.
        # You may want to play around with it.
        id: Mapped[int] = mapped_column(primary_key=True)
        name: Mapped[str] = mapped_column()


    def __repr__(self):
        return f"LocalizedName({self.id!r}, {self.name!r})"