Sqlalchemy declarative inheritance of __table_args__

Question

Suppose I have such table

class Base(object):
    id = Column(Integer, primary_key=True)
    date_updated = Column(Date, nullable=True)
    rating = Column(Integer, nullable=False, default=0)
    status = Column(SmallInteger, nullable=False, default=0)

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

    @declared_attr
    def __table_args__(cls):
        return (
            Index('ix__%s__rating' % cls.__tablename__, 'rating'),
            Index(
                'ix__%s__status' % cls.__tablename__,
                'status',
                postgresql_where=column('status') == 0,
            ),
            Index(
                'ix__%s__date_updated' % cls.__tablename__,
                'date_updated',
                postgresql_where=column('date_updated') != None,
            ),
        )

   class TableEn(Base, db.Model):
       LANG = 'en'


   class TableUk(Base, db.Model):
       LANG = 'uk'

Some way I've found how to create such (partial) indexes as status and date_updated in parent __table_args__.

But I need to create desc sorting index rating, like func.desc(rating), but I do not know how to do that.

None of variants worked for me (variant and it's error):

Index('ix__%s__rating' % cls.__tablename__, 'rating desc')

KeyError: 'rating desc'
Index('ix__%s__rating' % cls.__tablename__, cls.rating.desc())

sqlalchemy.exc.ArgumentError: Can't add unnamed column to column collection
Index('ix__%s__rating' % cls.__tablename__, desc('rating'))

while creating schema in db

sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near ")" LINE 1: CREATE INDEX ix__table_en__rating ON table_en ()

Of course I can create that index manually with direct SQL, but I'm sure solution exists somewhere.

Thanks in advance!

Slava Bacherikov · Accepted Answer · 2014-12-04 17:32:36Z

Here simplified code that doing what you want:

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'

Full code here.

Here you are having 2 problems. First, you should call desc against table column, not an mapper object attribute. But if you do it you fill have problem, because __table__ attribute is created after getting info from __table__args__. So you need create indexes after creating __table__ field. One way to do it is via sqlalchemy events.

Collectives™ on Stack Overflow

Sqlalchemy declarative inheritance of __table_args__

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