so24947583.py

# This is the complete code for the StackOverflow answer:
# https://stackoverflow.com/a/24947583
from typing import List
from typing import Literal
from typing import get_args
from sqlalchemy import Enum
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy import and_
from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import Session
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

PictureTypes = Literal['main', 'related', 'option']

class Base(DeclarativeBase):
    ...

class ProductPicture(Base):
    __tablename__ = 'product_pictures'

    picture_id: Mapped[int] = mapped_column(primary_key=True)
    product_id: Mapped[int] = mapped_column(ForeignKey('products.product_id'))
    picture_type: Mapped[PictureTypes] = mapped_column(Enum(*get_args(PictureTypes)))
    url: Mapped[str] = mapped_column(String(120))

class Product(Base):
    __tablename__ = 'products'

    product_id: Mapped[int] = mapped_column(primary_key=True)
    product_name: Mapped[str] = mapped_column(String(100))
    product_pictures: Mapped[List["ProductPicture"]] = relationship()
    main_pictures: Mapped[List["ProductPicture"]] = relationship(
        viewonly=True,
        primaryjoin=lambda: and_(
            Product.product_id == ProductPicture.product_id,
            ProductPicture.picture_type == 'main'
        ),
    )
    option_pictures: Mapped[List["ProductPicture"]] = relationship(
        viewonly=True,
        primaryjoin=lambda: and_(
            Product.product_id == ProductPicture.product_id,
            ProductPicture.picture_type == 'option'
        ),
    )

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

with Session(engine) as session:
    product = Product(
        product_name='test product',
        product_pictures=[
            ProductPicture(picture_type='main', url='p1.main.png'),
            ProductPicture(picture_type='option', url='p1.option1.png'),
            ProductPicture(picture_type='option', url='p1.option2.png'),
        ],
    )
    session.add(product)
    session.commit()
    print("product_pictures:",
        [(pic.picture_type, pic.url) for pic in product.product_pictures])
    print("main_pictures:",
        [(pic.picture_type, pic.url) for pic in product.main_pictures])
    print("option_pictures:",
        [(pic.picture_type, pic.url) for pic in product.option_pictures])