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.

Note that the proper way is to pass the Index in __table_args__ if defining it in the class body, though your example would also work due to using the actual Column object in defining the index. — Ilja Everilä
– Ilja Everilä, Commented May 1, 2019 at 7:34

Mark Benningfield · Accepted Answer · 2019-04-30 16:59:05Z

5

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.

answered Apr 30, 2019 at 16:59

Mark Benningfield

2,9419 gold badges37 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create an ordered Index in sqlite db using SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related