Fix Python – Multiple columns index when using the declarative ORM extension of sqlalchemy

Question 1

According to the documentation and the comments in the sqlalchemy.Column class, we should use the class sqlalchemy.schema.Index to specify an index that contains multiple columns.

However, the example shows how to do it by directly using the Table object like this:

meta = MetaData()
mytable = Table('mytable', meta,
    # an indexed column, with index "ix_mytable_col1"
    Column('col1', Integer, index=True),

    # a uniquely indexed column with index "ix_mytable_col2"
    Column('col2', Integer, index=True, unique=True),

    Column('col3', Integer),
    Column('col4', Integer),

    Column('col5', Integer),
    Column('col6', Integer),
    )

# place an index on col3, col4
Index('idx_col34', mytable.c.col3, mytable.c.col4)

How should we do it if we use the declarative ORM extension?

class A(Base):
    __tablename__ = 'table_A'
    id = Column(Integer, , primary_key=True)
    a = Column(String(32))
    b = Column(String(32))

I would like an index on column “a” and “b”.

Question 2

those are just Column objects, index=True flag works normally:

class A(Base):
    __tablename__ = 'table_A'
    id = Column(Integer, primary_key=True)
    a = Column(String(32), index=True)
    b = Column(String(32), index=True)

if you’d like a composite index, again Table is present here as usual you just don’t have to declare it, everything works the same (make sure you’re on recent 0.6 or 0.7 for the declarative A.a wrapper to be interpreted as a Column after the class declaration is complete):

class A(Base):
    __tablename__ = 'table_A'
    id = Column(Integer, primary_key=True)
    a = Column(String(32))
    b = Column(String(32))

Index('my_index', A.a, A.b)

In 0.7 the Index can be in the Table arguments too, which with declarative is via __table_args__:

class A(Base):
    __tablename__ = 'table_A'
    id = Column(Integer, primary_key=True)
    a = Column(String(32))
    b = Column(String(32))
    __table_args__ = (Index('my_index', "a", "b"), )

Question

Answer