Search code examples
pythonsqlalchemyormddl

SqlAlchemy 2.0: Issues using `mapped_column()` with `Computed()` columns in ORM Table Definitions (not working on MappedAsDataclass)

(Note: In line with Stack Overflow's encouragement to enrich the community by answering one's own questions, especially after investing significant effort in troubleshooting, I am providing an answer below. I eagerly welcome further comments, additional insights, or alternative approaches! I would also like to acknowledge the valuable assistance from F. Caselli on a SQLAlchemy forum, which helped me to narrow down the search for a solution.)

Hello, I'm trying to create a COMPUTED column directly in the ORM definition using mapped_column() in SqlAlchemy 2.0, but I haven't been successful (I've checked Google and tried to find the answer in the documentation).

I know that for the core model, it's enough to declare a table in the following way (Toy example where "area" is a COMPUTED column):

square = Table(
    "square",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("side", Integer),
    Column("area", Integer, Computed("side * side")),
)

This is equivalent to the following SQL DDL statement (Postgresql >12):

CREATE TABLE square (
    id SERIAL NOT NULL,
    side INTEGER,
    area INTEGER GENERATED ALWAYS AS (side * side) STORED,
    PRIMARY KEY (id)
)

However, when trying to do the equivalent in ORM using Mapped[] and mapped_column(), I can't seem to find the solution.

I imagined something like the following might work, but it didn’t:

class Square(Base):
    ...
    area: Mapped[int] = mapped_column(Computed("side*side"))

Before that, I also tried other configurations, such as passing Computed() as server_default=, and using FetchedValue, but in all cases, I encountered errors.

I also came to @hybrid_property, but I understand that it's not the same (since I need the computed column to be part of the table's DDL definition and @hybrid_property seems to leave the computation to be done during selects but not as DDL).

I would appreciate if someone could guide me on the correct use of mapped_column() with Computed()!

Solution

  • We discovered that the missing part was only the parameter:

    init=False

    So, in order to make the following work:

    area: Mapped[int] = mapped_column(Computed("side*side"))

    it must be changed to:

    area: Mapped[int] = mapped_column(Computed("side*side"), init=False)

    Why was not working the original form?

    It turns out that my Base class is derived from MappedAsDataclass, and the creation of the __init__() method of the dataclass was not handled properly as required by this type of Base class.

    Apparently, the MappedAsDataclass machinery does not automatically exclude the Computed() column from the __init__() method, so we must explicitly set init=False in the mapped_column() definition.

    Summary

    To summarize, the solution in my case was to declare the area column as follows:

    
class Base(MappedAsDataclass, DeclarativeBase):
    pass

class Square(Base):
    ...
    # for computed column, use init=False if Base is a MappedAsDataclass
    area: Mapped[int] = mapped_column(Computed("side*side"), init=False) 
    ...

    Special Thanks

    Thanks to F. Caselli for helping narrow the search for a solution in the sqlalchemy forum!!