124

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

Mark Amery
  • 143,130
  • 81
  • 406
  • 459
yorjo
  • 1,699
  • 3
  • 12
  • 14
  • 1
    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. – Mark Amery Jan 05 '18 at 14:12
  • The question does not indicate whether the joint index should be unique or not – Pynchia Nov 18 '21 at 09:11

2 Answers2

176

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"), )
zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • 2
    Thanks, I updated to 0.7 and using the __table_args__ works fine – yorjo Jul 12 '11 at 14:57
  • 8
    What happens if you have a dictionary for table_args like I currently do? __table_args__ = {'mysql_engine':'InnoDB'} – Nick Holden Sep 01 '11 at 08:39
  • @Nick http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#table-configuration – Joe Holloway Oct 12 '11 at 20:30
  • 9
    So I guess I can do __table_args__ = (Index('my_index', "a", "b"),{'mysql_engine':'InnoDB'}) – Nick Holden Oct 19 '11 at 11:21
  • 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' – Ellochka Cannibal Feb 03 '14 at 10:02
  • can't reproduce. Please produce a succinct and self contained test script and email the [sqlalchemy mailing list](https://groups.google.com/forum/#!forum/sqlalchemy) - thanks. – zzzeek Feb 03 '14 at 16:31
  • @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))` – Ellochka Cannibal Apr 16 '14 at 11:18
  • @zzzeek What about mixed `mysql_engine` and Index('ix_foo', 'foo') in `__table_args__`? what is the right declarative statement? – Ryan Chou Oct 18 '17 at 09:44
  • 4
    @RyanChou http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/table_config.html#table-configuration "Keyword arguments can be specified with the above form by specifying the last argument as a dictionary" – zzzeek Oct 23 '17 at 16:18
  • @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__` – Ryan Chou Oct 24 '17 at 03:07
  • How is this so difficult to find anywhere? The links don't work. – Sebastian Wozny Jan 17 '18 at 10:41
  • The answer does not indicate whether the joint index would be unique or not – Pynchia Nov 18 '21 at 09:12
  • Where does `Index` come from? Show where you need to import it from – Pynchia Nov 18 '21 at 09:15
24

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)
Karol Zlot
  • 2,887
  • 2
  • 20
  • 37
Mickael
  • 829
  • 7
  • 9