demonstration of functional index dynamic creation

func_index.py

import sys
from sqlalchemy import create_engine, Column, Integer, Index, event
from sqlalchemy.ext.declarative import declarative_base, declared_attr


engine = create_engine('postgresql://slava:slavapw@localhost:5432/test', echo=True)
Base = declarative_base()


class BaseModel(Base):

    __abstract__ = True

    id = Column(Integer, primary_key=True)
    rating = Column(Integer, nullable=False, default=0)

    @declared_attr
    def __tablename__(cls):
        return "table_%s" % cls.LANG

    @classmethod
    def build_indexes(cls):
        Index('ix__%s__rating' % cls.__tablename__, cls.__table__.c.rating.desc())


@event.listens_for(BaseModel, 'instrument_class', propagate=True)
def receive_mapper_configured(mapper, class_):
    class_.build_indexes()


class TableEn(BaseModel):
   LANG = 'en'


class TableUk(BaseModel):
   LANG = 'uk'


def usage():
    print("usage: script.py [create|drop]")
    sys.exit()


def main():
    if len(sys.argv) != 2:
        usage()

    arg = sys.argv[1]
    if arg == "create":
        Base.metadata.create_all(engine)
    elif arg == "drop":
        Base.metadata.drop_all(engine)
    else:
        usage()


if __name__ == "__main__":
    main()