Create an ordered Index in sqlite db using SQLAlchemy

Question

I'm defining a table in SQLAlchemy using the declarative API. It's got a foreign key which I'd like to index. My question is: how do I define the index created from master_ref to be an ASC or DESC index (without resorting to doing it manually with SQL)?

class Item(Base):
    id = Column(INTEGER, primary_key=True)
    master_ref = Column(INTEGER, ForeignKey('master.id'), nullable=True, index=True)
    value = Column(REAL)

Looking at the documentation of SqlAlchemy, an alternative way to create the index would be:

class Item(Base):
    id = Column(INTEGER, primary_key=True)
    master_ref = Column(INTEGER, ForeignKey('master.id'))
    value = Column(REAL)
    Index('ix_name', master_ref)

but I cannot find any reference on how to define the ASC or DESC anywhere.

Answer 1

You can use a functional index, as specified in the documentation:

Functional Indexes

Index supports SQL and function expressions, as supported by the target backend. To create an index against a column using a descending value, the ColumnElement.desc() modifier may be used:

from sqlalchemy import Index

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

And likewise for an ascending value, use the ColumnElement.asc() modifier.