How best to implement a child property that is a list of integers

Question

I have a 1-N relationship where the child property is just a list of integers. Something like:

class LotteryDraw(Base):
    __tablename__ = "draw"

    id: Mapped[int] = mapped_column(primary_key=True)
    draw_time: Mapped[dt.datetime]

    numbers: Mapped[List["DrawNumber"]] = relationship(
        back_populates="draw", cascade="all, delete-orphan"
    )

    def __repr__(self):
        return f"Draw(id={self.id!r}, draw_time={self.draw_time})"

class DrawNumber(Base):
    __tablename__ = "draw_numbers"

    id: Mapped[int] = mapped_column(primary_key=True)
    number: Mapped[int]
    draw_id: Mapped[int] = mapped_column(ForeignKey("draw.id"))

    draw: Mapped["LotteryDraw"] = relationship(back_populates="numbers")

    def __repr__(self):
        return f"{self.number}"

with Session(engine) as session:
    draw = LotteryDraw(
        draw_time=dt.datetime.now(),
        numbers=[DrawNumber(number=1), DrawNumber(number=39), DrawNumber(number=45), DrawNumber(number=46)],
        # I would like to be able to do this instead: numbers=[1, 39, 45, 46],
    )
    session.add_all([draw])
    session.commit()

As you can see, working with the numbers property of a LotteryDraw requires spelling out the full instantiation: DrawNumber(number=N). Is it possible to pretend in my code that they are just plain integers, both for getting and setting LotteryDraw.numbers? (I don't mind having them as DrawNumbers when working just with the draw_numbers table directly, I'm mainly after simplifying working with LotteryDraw.)

Answer 1

You can use an AssociationProxy to do this:

from __future__ import annotations

import datetime
from typing import Annotated

from sqlalchemy import ForeignKey, create_engine
from sqlalchemy.ext.associationproxy import AssociationProxy, association_proxy
from sqlalchemy.orm import (DeclarativeBase, Mapped, MappedAsDataclass,
                            mapped_column, relationship, sessionmaker)

DATABASE_URL = "postgresql+psycopg2://postgres:test_password@localhost:5432/test_db"

engine = create_engine(
    DATABASE_URL,
    echo=True,
)
Session = sessionmaker(
    bind=engine,
)


class Base(MappedAsDataclass, DeclarativeBase):
    pass


timestamp_auto = Annotated[
    datetime.datetime,
    mapped_column(
        default=datetime.datetime.now,
    ),
]


class LotteryDraw(Base):
    __tablename__ = "draw"

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    draw_time: Mapped[timestamp_auto] = mapped_column(init=False)

    numbers: Mapped[list[DrawNumber]] = relationship(
        back_populates="draw",
        cascade="all, delete-orphan",
        init=False,
        repr=False,
    )

    values: AssociationProxy[list[int]] = association_proxy(
        "numbers",
        "number",
        init=False,
    )


class DrawNumber(Base):
    __tablename__ = "draw_number"

    id: Mapped[int] = mapped_column(primary_key=True, init=False)
    number: Mapped[int]
    draw_id: Mapped[int] = mapped_column(ForeignKey("draw.id"), init=False)

    draw: Mapped[LotteryDraw] = relationship(
        back_populates="numbers",
        init=False,
        repr=False,
    )


if __name__ == "__main__":
    Base.metadata.create_all(engine)

    with Session() as session:
        draw = LotteryDraw()
        draw.values = [1, 39, 45, 46]
        session.add(draw)
        session.commit()

        print(draw)