Skip to content
Advertisement

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

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.

Advertisement

Answer

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)
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement