How to create order by index in flask-sqlalchemy

Question

There are the following: Flask, Flask-sqlalchemy, Flask-migrate

There is a class describing the table:

class Student(db.Model):
    __tablename__ = 'student'

    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.Unicode(250), nullable=False, index=True)
    last_name = db.Column(db.Unicode(250), nullable=False, index=True)
    surname = db.Column(db.Unicode(250), nullable=False, index=True)
    group = db.Column(db.Unicode(250), nullable=False, index=True)
    fio = db.Column(db.Unicode(250*3))

    add_info = db.relationship('AddInfo', uselist=False, backref='student', cascade="all, delete-orphan")

    __table_args__ = (
        db.Index('fio_like', 'fio', postgresql_ops={'fio': 'text_pattern_ops'}),
        db.Index('first_name_like', 'first_name', postgresql_ops={'first_name': 'text_pattern_ops'}),
        db.Index('last_like', 'last_name', postgresql_ops={'last': 'text_pattern_ops'}),
        db.Index('surname_like', 'surname', postgresql_ops={'surname': 'text_pattern_ops'}),
        db.Index('order_fio_desc', fio.desc(), postgresql_using='btree'),
    )

    @classmethod
    def create(cls, first_name, last_name, surname, address, date_of_birth, group):
        obj = cls()

        obj.first_name = first_name
        obj.last_name = last_name
        obj.surname = surname
        obj.group = group
        obj.fio = ' '.join([last_name, first_name, surname]).strip()

        if not obj.add_info:
            obj.add_info = AddInfo()

        obj.add_info.address = address
        obj.add_info.date_of_birth = date_of_birth

        return obj

    @property
    def serialize(self):
        return {
            'id': self.id,
            'fio': self.fio,
            'group': self.group
        }

When you migrate this model to create an index order_fio_desc using this query:

CREATE INDEX order_fio_desc ON student USING btree (student.fio DESC)

Due to the fact that in the name of the field name is present PostgreSQL database does not allow to create such an index. But if the name field to remove the name of the table, the index is created without problems:

CREATE INDEX order_fio_desc ON student USING btree (fio DESC)

How to make a request to create an index generated right?

score 1 · Answer 1 · edited Sep 10 '20 at 16:25

You can specify ordering using the postgresql_ops keyword argument as you did in the other indices. That would make the following valid:

db.Index(
    'order_fio_desc',
    'fio',
    postgresql_using='btree',
    postgresql_ops={'fio': 'DESC'},
),

This gives the following output in the migration file:

op.create_index('order_fio_desc', 'student', ['fio'], unique=False, postgresql_using='btree', postgresql_ops={'fio': 'DESC'})

Which in turn results in the required SQL:

CREATE INDEX order_fio_desc ON student USING btree (fio DESC);

How to create order by index in flask-sqlalchemy

1 Answers1