Multiple columns index when using the declarative ORM extension of sqlalchemy
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"), )
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 IndexIndex('someindex', mytable.c.somecol.desc())We can just use the model property and call .desc() on it:
from flask_sqlalchemy import SQLAlchemydb = 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 importfrom sqlalchemy.schema import Sequence, CreateSequencedef 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)