Multiple columns index when using the declarative ORM extension of sqlalchemy

Question

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".

Solution 1

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"), )

Solution 2

To complete @zzzeek's answer.

If you like to add a composite index with DESC and use the ORM declarative method you can do as follows.

Furthermore, I was struggling with the Functional Indexes documentation of SQLAlchemy, trying to figure out a how to substitute mytable.c.somecol.

from sqlalchemy import Index

Index('someindex', mytable.c.somecol.desc())

We can just use the model property and call .desc() on it:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class GpsReport(db.Model):
    __tablename__ = 'gps_report'

    id = db.Column(db.Integer, db.Sequence('gps_report_id_seq'), nullable=False, autoincrement=True, server_default=db.text("nextval('gps_report_id_seq'::regclass)"))

    timestamp = db.Column(db.DateTime, nullable=False, primary_key=True)

    device_id = db.Column(db.Integer, db.ForeignKey('device.id'), primary_key=True, autoincrement=False)
    device = db.relationship("Device", back_populates="gps_reports")


    # Indexes

    __table_args__ = (
        db.Index('gps_report_timestamp_device_id_idx', timestamp.desc(), device_id),
    )

If you use Alembic, I'm using Flask-Migrate, it generates something like:

from alembic import op  
import sqlalchemy as sa
# Added manually this import
from sqlalchemy.schema import Sequence, CreateSequence


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # Manually added the Sequence creation
    op.execute(CreateSequence(Sequence('gps_report_id_seq')))

    op.create_table('gps_report',
    sa.Column('id', sa.Integer(), server_default=sa.text("nextval('gps_report_id_seq'::regclass)"), nullable=False),
    sa.Column('timestamp', sa.DateTime(), nullable=False))
    sa.Column('device_id', sa.Integer(), autoincrement=False, nullable=False),
    op.create_index('gps_report_timestamp_device_id_idx', 'gps_report', [sa.text('timestamp DESC'), 'device_id'], unique=False)


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('gps_report_timestamp_device_id_idx', table_name='gps_report')
    op.drop_table('gps_report')

    # Manually added the Sequence removal
    op.execute(sa.schema.DropSequence(sa.Sequence('gps_report_id_seq'))) 
    # ### end Alembic commands ###

Finally, you should have the following table and indexes in your PostgreSQL database:

psql> \d gps_report;
                                           Table "public.gps_report"
     Column      |            Type             | Collation | Nullable |                Default                 
-----------------+-----------------------------+-----------+----------+----------------------------------------
 id              | integer                     |           | not null | nextval('gps_report_id_seq'::regclass)
 timestamp       | timestamp without time zone |           | not null | 
 device_id       | integer                     |           | not null | 
Indexes:
    "gps_report_pkey" PRIMARY KEY, btree ("timestamp", device_id)
    "gps_report_timestamp_device_id_idx" btree ("timestamp" DESC, device_id)
Foreign-key constraints:
    "gps_report_device_id_fkey" FOREIGN KEY (device_id) REFERENCES device(id)

Comments

[Mark Amery]

The question is a bit unclear about whether you want multiple indexes or a single index on multiple columns (and was more confused before I edited it - originally it delightfully asked for "an index that contain multiple multiple index"). But no matter, I guess, since zzzeek's answer addresses both cases.

[Pynchia]

The question does not indicate whether the joint index should be unique or not

[yorjo]

Thanks, I updated to 0.7 and using the table_args works fine

[Nick Holden]

What happens if you have a dictionary for table_args like I currently do? table_args = {'mysql_engine':'InnoDB'}

[Joe Holloway]

@Nick sqlalchemy.org/docs/orm/extensions/…

[Nick Holden]

So I guess I can do table_args = (Index('my_index', "a", "b"),{'mysql_engine':'InnoDB'})

[Ellochka Cannibal]

I'm using sqlalchemy 0.8 with flask extension and third example causes me an error: AttributeError: Neither 'Function' object nor 'Comparator' object has an attribute 'key'

[zzzeek]

can't reproduce. Please produce a succinct and self contained test script and email the sqlalchemy mailing list - thanks.

[Ellochka Cannibal]

@zzzeek, i found my mistake, sorry! i declare my index as Index('email_substr_idx', func.substr('email', 0, 2)) instead of Index('email_substr_idx', func.substr(email, 0, 2))

[Ryan Chou]

@zzzeek What about mixed mysql_engine and Index('ix_foo', 'foo') in __table_args__? what is the right declarative statement?

[zzzeek]

@RyanChou docs.sqlalchemy.org/en/latest/orm/extensions/declarative/… "Keyword arguments can be specified with the above form by specifying the last argument as a dictionary"

[Ryan Chou]

@zzzeek Thanks a lot. I got it. While we couldn't set the first parameter as dict it would raise AttributedError when processing __table_args__

[Sebastian Wozny]

How is this so difficult to find anywhere? The links don't work.

[rickerp]

I'm not able to use the class attributes inside the Index function o.O

[Pynchia]

The answer does not indicate whether the joint index would be unique or not

[Pynchia]

Where does Index come from? Show where you need to import it from