Database:Multiple columns index when using the declarative ORM extension of sqlalchemy?

Database:Multiple columns index when using the declarative ORM extension of sqlalchemy?

Asked on October 26, 2018 in Database.
Add Comment


  • 3 Answer(s)

    These are just Column objects, index=True flag works normally:

    class A(Base):
        __tablename__ = 'table_A'
        id = Column(Integer, primary_key=True)
        a = Column(String(32), index=True)
        b = Column(String(32), index=True)
    

    if we’d a composite index, Table is present as usual we just do not have to declare it, everything works the same (make sure you’re on recent 0.6 or 0.7 for the declarative A.a wrapper to be interpreted as a Column after the class declaration is complete):

    class A(Base):
        __tablename__ = 'table_A'
        id = Column(Integer, primary_key=True)
        a = Column(String(32))
        b = Column(String(32))
    Index('my_index', A.a, A.b)
    

    In 0.7 the Index can be in the Table arguments too, which with declarative is via table_args:

    class A(Base):
        __tablename__ = 'table_A'
        id = Column(Integer, primary_key=True)
        a = Column(String(32))
        b = Column(String(32))
        __table_args__ = (Index('my_index', "a", "b"), )
    

     

     

    Answered on October 26, 2018.
    Add Comment

    Declarative

    The Declarative system is the typically used system provided by the SQLAlchemy ORM in order to define classes mapped to relational database tables. However, as noted in Classical Mappings, Declarative is in fact a series of extensions that ride on top of the SQLAlchemy mapper() construct.

    While the documentation typically refers to Declarative for most examples, the following sections will provide detailed information on how the Declarative API interacts with the basic mapper() and Core Table systems, as well as how sophisticated patterns can be built using systems such as mixins.

    Answered on January 14, 2019.
    Add Comment

    Define attributes on ORM-mapped classes that have “index” attributes for columns with Indexable types.

    “index” means the attribute is associated with an element of anIndexable column with the predefined index to access it. The Indexable types include types such as ARRAYJSON and HSTORE.

    The indexable extension providesColumn-like interface for any element of an Indexable typed column. In simple cases.

    Multiple index_property objects can be chained to produce multiple levels of indexing:

    from sqlalchemy import Column, JSON, Integer
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.ext.indexable import index_property
    
    Base = declarative_base()
    
    class Person(Base):
        __tablename__ = 'person'
    
        id = Column(Integer, primary_key=True)
        data = Column(JSON)
    
        birthday = index_property('data', 'birthday')
        year = index_property('birthday', 'year')
        month = index_property('birthday', 'month')
        day = index_property('birthday', 'day')
    Answered on January 14, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.