CODEWITHSUNDEEP
pythonsqlsqlalchemy
Logan Byers
Logan Byers

Reputation: 1573

Inherit and add indexes from sqlalchemy mixins

Given a mixin with an index, how is it possible to add additional indexes to a model inheriting that mixin? With an index name like idx__TABLENAME__COLUMN the mixin is unable to obtain the TABLENAME for itself. If a __tablename__ is specified for the mixin, then duplicate index names arise.

Example code follows.

import sqlalchemy as sql
from sqlalchemy import Column, Index, String, Integer
from sqlalchemy.ext.declarative import declared_attr, declarative_base


Base = declarative_base()

class MixinOwner(object):
    id = Column('id', Integer, primary_key=True)
    owner = Column('owner', String)

    @declared_attr
    def __table_args__(cls):
        return (Index('idx__%s__owner' % cls.__tablename__, 'owner'), )


class Letter(MixinOwner, Base):
    __tablename__ = 'letter'
    a = Column('a', String)
    b = Column('b', String)

    @declared_attr
    def __table_args__(cls):
        mixin_indexes = list(MixinOwner.__table_args__)  # <--- Error (MixinOwner does not have attribute __tablename__)
        mixin_indexes.extend([
            Index('idx__letter__a', 'a'),
            Index('idx__letter__b', 'b'),
        ])
        return tuple(mixin_indexes)


class Word(MixinOwner, Base):
    __tablename__ = 'word'
    apple = Column('apple', String)
    banana = Column('banana', String)

    @declared_attr
    def __table_args__(cls):
        mixin_indexes = list(MixinOwner.__table_args__)
        mixin_indexes.extend([
            Index('idx__word__apple', 'apple'),
            Index('idx__word__banana', 'banana'),
        ])
        return tuple(mixin_indexes)




engine = sqlalchemy.create_engine('sqlite:///:memory:')
engine.connect()

Base.metadata.bind = engine
Base.metadata.create_all()

Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()

Upvotes: 2

Views: 1254

Answers (1)

Logan Byers
Logan Byers

Reputation: 1573

To incorporate indexes from mixins you need to use super to access the methods of base classes within the context of the subclass. In addition to this fix, which solves the original question I posed, there springs another question -- how to incorporate indexes from multiple mixins not just one (as was setup in the original question). The following code explains this larger issue as well. The solution is to iterate over the MRO and acquire the __table_args__ for each base class with the context of the model.

import sqlalchemy as sql
from sqlalchemy import Column, Index, String, Integer, Date
from sqlalchemy.ext.declarative import declared_attr, declarative_base


Base = declarative_base()

class MixinOwner(object):
    id = Column('id', Integer, primary_key=True)
    owner = Column('owner', String)

    @declared_attr
    def __table_args__(cls):
        return (Index('idx__%s__owner' % cls.__tablename__, 'owner'), )

class MixinDate(object):
    date = Column('date', Date)

    @declared_attr
    def __table_args__(cls):
        return (Index('idx__%s__date' % cls.__tablename__, 'date'), )



# single mixin inheritance (original question) -- use super(cls, cls)
class Word(MixinOwner, Base):
    __tablename__ = 'word'
    apple = Column('apple', String)
    banana = Column('banana', String)

    @declared_attr
    def __table_args__(cls):
        mixin_indexes = list((super(cls, cls).__table_args__))
        mixin_indexes.extend([
            Index('idx__word__apple', 'apple'),
            Index('idx__word__banana', 'banana'),
        ])
        return tuple(mixin_indexes)


# multiple mixin iheritance (not in original question)
# iterate through __mro__ and aggregate __table_args__ from each base
class Letter(MixinOwner, MixinDate, Base):
    __tablename__ = 'letter'
    a = Column('a', String)
    b = Column('b', String)

    @declared_attr
    def __table_args__(cls):
        mixin_indexes = []
        for base_class in cls.__mro__:
            try:
                mixin_indexes.extend(super(base_class, cls).__table_args__)
            except AttributeError:
                pass
        mixin_indexes.extend([
            Index('idx__letter__a', 'a'),
            Index('idx__letter__b', 'b'),
        ])
        return tuple(mixin_indexes)




engine = sql.create_engine('sqlite:///:memory:')
engine.connect()

Base.metadata.bind = engine
Base.metadata.create_all()

Session = sql.orm.sessionmaker(bind=engine)
session = Session()

Upvotes: 6

Related Questions