SQLAlchemy How to create a composite index between a polymorphic class and it's subclass

Question

I am trying to get a composite index working between a polymorphic subclass and it's parent.

Alembic autogenerate does not seem to detect Indexes outside of __table_args__.

I can't use __table_args__ because, being in the subclass, it does not count my class as having a __table__.

How do I create a composite Index between these?

class Main(Base, SomeMixin):
    __tablename__ = "main"
    __table_args__ = (
        # Some constraints and Indexes specific to main
    )

    id = Column(String, primary_key=True, default=func.generate_object_id())

    mtype = Column(String, nullable=False)

    __mapper_args__ = {"polymorphic_on": mtype}

class SubClass(Main):
    __mapper_args__ = {"polymorphic_identity": "subclass"}

    bid = Column(String, ForeignKey("other.id", ondelete="CASCADE"))

    # My index specific to Subclass
    Index(
        "ix_main_bid_mtype",
        "bid",
        "mtype",
    )

The goal is to have something like this pop with alembic autogenerate:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_index(
        "ix_main_bid_mtype",
        "main",
        ["bid", "mtype"],
        unique=False,
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f("ix_main_bid_mtype"), table_name="main")
    # ### end Alembic commands ###

Thank you for your time and potential future help.

EDIT: Note: The other fields are detected by autogenerate, only the index done this way does not seem to work.

Cruzcruzado

Answer 1

Create the index externally after both classes:

class Main(Base, SomeMixin):
    __tablename__ = "main"
    __table_args__ = (
        # Some constraints and Indexes specific to main
    )

    id = Column(String, primary_key=True, default=func.generate_object_id())

    mtype = Column(String, nullable=False)

    __mapper_args__ = {"polymorphic_on": mtype}


class SubClass(Main):
    __mapper_args__ = {"polymorphic_identity": "subclass"}

    bid = Column(String, ForeignKey("other.id", ondelete="CASCADE"))


Index("ix_main_bid_mtype", SubClass.bid, SubClass.mtype)

Randeerandel

This does work but just as it works for regular tables, I don't this it is the best way to do it. I am not so fond of declaring things globally like this. I am currently looking for another way, I'll post it if I find it. For regular tables I usually pass the indexes to __table_args__ but in this case I may have to play around a bit to make it work. – Exit

As I haven't had the time to test this out yet, will set this as answer for now. Thanks for the help 👍 – Exit

As far as I'm aware this is the only way since __table_args__ will be used to create __table__ during class definition. So, you can't do something like making Main.__table_args__ a declared attribute which incorporates things from Main's subclasses since the subclasses won't have been defined yet. – Randeerandel

Maybe then it'd be possible to create the table directly through __table__ and the Table object. I just don't know how this'd react in a polymorphic environment. Will probably have to play around with extend_existing=True but I doubt this is going the right way too. – Exit