Mixin and Custom Base Classes

A common need when using declarative is toshare some functionality, such as a set of common columns, some commontable options, or other mapped properties, across manyclasses. The standard Python idioms for this is to have the classesinherit from a base which includes these common features.

When using declarative, this idiom is allowedvia the usage of a custom declarative base class, as well as a “mixin” classwhich is inherited from in addition to the primary base. Declarativeincludes several helper features to make this work in terms of howmappings are declared. An example of some commonly mixed-inidioms is below:

from sqlalchemy.ext.declarative import declared_attr
 
class MyMixin(object):
 
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()
 
    __table_args__ = {'mysql_engine': 'InnoDB'}
    __mapper_args__= {'always_refresh': True}
 
    id =  Column(Integer, primary_key=True)
 
class MyModel(MyMixin, Base):
    name = Column(String(1000))

Where above, the class MyModel will contain an “id” columnas the primary key, a tablename attribute that derivesfrom the name of the class itself, as well as table_argsand mapper_args defined by the MyMixin mixin class.

There’s no fixed convention over whether MyMixin precedesBase or not. Normal Python method resolution rules apply, andthe above example would work just as well with:

class MyModel(Base, MyMixin):
    name = Column(String(1000))

This works because Base here doesn’t define any of thevariables that MyMixin defines, i.e. tablename,table_args, id, etc. If the Base did definean attribute of the same name, the class placed first in theinherits list would determine which attribute is used on thenewly defined class.

Augmenting the Base

In addition to using a pure mixin, most of the techniques in thissection can also be applied to the base class itself, for patterns thatshould apply to all classes derived from a particular base. This is achievedusing the cls argument of the declarative_base() function:

from sqlalchemy.ext.declarative import declared_attr
 
class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()
 
    __table_args__ = {'mysql_engine': 'InnoDB'}
 
    id =  Column(Integer, primary_key=True)
 
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base(cls=Base)
 
class MyModel(Base):
    name = Column(String(1000))

Where above, MyModel and all other classes that derive from Base willhave a table name derived from the class name, an id primary key column,as well as the “InnoDB” engine for MySQL.

Mixing in Columns

The most basic way to specify a column on a mixin is by simpledeclaration:

class TimestampMixin(object):
    created_at = Column(DateTime, default=func.now())
 
class MyModel(TimestampMixin, Base):
    __tablename__ = 'test'
 
    id =  Column(Integer, primary_key=True)
    name = Column(String(1000))

Where above, all declarative classes that include TimestampMixinwill also have a column created_at that applies a timestamp toall row insertions.

Those familiar with the SQLAlchemy expression language know thatthe object identity of clause elements defines their role in a schema.Two Table objects a and b may both have a column calledid, but the way these are differentiated is that a.c.idand b.c.id are two distinct Python objects, referencing theirparent tables a and b respectively.

In the case of the mixin column, it seems that only oneColumn object is explicitly created, yet the ultimatecreated_at column above must exist as a distinct Python objectfor each separate destination class. To accomplish this, the declarativeextension creates a copy of each Column object encountered ona class that is detected as a mixin.

This copy mechanism is limited to simple columns that have no foreignkeys, as a ForeignKey itself contains references to columnswhich can’t be properly recreated at this level. For columns thathave foreign keys, as well as for the variety of mapper-level constructsthat require destination-explicit context, thedeclared_attr decorator is provided so thatpatterns common to many classes can be defined as callables:

from sqlalchemy.ext.declarative import declared_attr
 
class ReferenceAddressMixin(object):
    @declared_attr
    def address_id(cls):
        return Column(Integer, ForeignKey('address.id'))
 
class User(ReferenceAddressMixin, Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

Where above, the address_id class-level callable is executed at thepoint at which the User class is constructed, and the declarativeextension can use the resulting Column object as returned bythe method without the need to copy it.

Columns generated by declared_attr can also bereferenced by mapper_args to a limited degree, currentlyby polymorphic_on and version_id_col; the declarative extensionwill resolve them at class construction time:

class MyMixin:
    @declared_attr
    def type_(cls):
        return Column(String(50))
 
    __mapper_args__= {'polymorphic_on':type_}
 
class MyModel(MyMixin, Base):
    __tablename__='test'
    id =  Column(Integer, primary_key=True)

Mixing in Relationships

Relationships created by relationship() are providedwith declarative mixin classes exclusively using thedeclared_attr approach, eliminating any ambiguitywhich could arise when copying a relationship and its possibly column-boundcontents. Below is an example which combines a foreign key column and arelationship so that two classes Foo and Bar can both be configured toreference a common target class via many-to-one:

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))
 
    @declared_attr
    def target(cls):
        return relationship("Target")
 
class Foo(RefTargetMixin, Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
 
class Bar(RefTargetMixin, Base):
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)
 
class Target(Base):
    __tablename__ = 'target'
    id = Column(Integer, primary_key=True)

Using Advanced Relationship Arguments (e.g. primaryjoin, etc.)

relationship() definitions which require explicitprimaryjoin, orderby etc. expressions should in all but the mostsimplistic cases use late bound formsfor these arguments, meaning, using either the string form or a lambda.The reason for this is that the related Column objects which are tobe configured using @declared_attr are not available to another@declared_attr attribute; while the methods will work and return newColumn objects, those are not the Column objects thatDeclarative will be using as it calls the methods on its own, thus using_differentColumn objects.

The canonical example is the primaryjoin condition that depends uponanother mixed-in column:

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))
 
    @declared_attr
    def target(cls):
        return relationship(Target,
            primaryjoin=Target.id==cls.target_id   # this is *incorrect*
        )

Mapping a class using the above mixin, we will get an error like:

sqlalchemy.exc.InvalidRequestError: this ForeignKey's parent column is not
yet associated with a Table.

This is because the target_id Column we’ve called upon in ourtarget() method is not the same Column that declarative isactually going to map to our table.

The condition above is resolved using a lambda:

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))
 
    @declared_attr
    def target(cls):
        return relationship(Target,
            primaryjoin=lambda: Target.id==cls.target_id
        )

or alternatively, the string form (which ultimately generates a lambda):

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))
 
    @declared_attr
    def target(cls):
        return relationship("Target",
            primaryjoin="Target.id==%s.target_id" % cls.__name__
        )

Mixing in deferred(), column_property(), and other MapperProperty classes

Like relationship(), allMapperProperty subclasses such asdeferred(), column_property(),etc. ultimately involve references to columns, and therefore, whenused with declarative mixins, have the declared_attrrequirement so that no reliance on copying is needed:

class SomethingMixin(object):
 
    @declared_attr
    def dprop(cls):
        return deferred(Column(Integer))
 
class Something(SomethingMixin, Base):
    __tablename__ = "something"

The column_property() or other construct may referto other columns from the mixin. These are copied ahead of time beforethe declared_attr is invoked:

class SomethingMixin(object):
    x = Column(Integer)
 
    y = Column(Integer)
 
    @declared_attr
    def x_plus_y(cls):
        return column_property(cls.x + cls.y)

Changed in version 1.0.0: mixin columns are copied to the final mapped classso that declared_attr methods can access the actual columnthat will be mapped.

Mixing in Association Proxy and Other Attributes

Mixins can specify user-defined attributes as well as other extensionunits such as association_proxy(). The usage ofdeclared_attr is required in those cases where the attribute mustbe tailored specifically to the target subclass. An example is whenconstructing multiple association_proxy() attributes which eachtarget a different type of child object. Below is anassociation_proxy() / mixin example which provides a scalar list ofstring values to an implementing class:

from sqlalchemy import Column, Integer, ForeignKey, String
from sqlalchemy.orm import relationship
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base, declared_attr
 
Base = declarative_base()
 
class HasStringCollection(object):
    @declared_attr
    def _strings(cls):
        class StringAttribute(Base):
            __tablename__ = cls.string_table_name
            id = Column(Integer, primary_key=True)
            value = Column(String(50), nullable=False)
            parent_id = Column(Integer,
                            ForeignKey('%s.id' % cls.__tablename__),
                            nullable=False)
            def __init__(self, value):
                self.value = value
 
        return relationship(StringAttribute)
 
    @declared_attr
    def strings(cls):
        return association_proxy('_strings', 'value')
 
class TypeA(HasStringCollection, Base):
    __tablename__ = 'type_a'
    string_table_name = 'type_a_strings'
    id = Column(Integer(), primary_key=True)
 
class TypeB(HasStringCollection, Base):
    __tablename__ = 'type_b'
    string_table_name = 'type_b_strings'
    id = Column(Integer(), primary_key=True)

Above, the HasStringCollection mixin produces a relationship()which refers to a newly generated class called StringAttribute. TheStringAttribute class is generated with its own Tabledefinition which is local to the parent class making usage of theHasStringCollection mixin. It also produces an association_proxy()object which proxies references to the strings attribute onto the valueattribute of each StringAttribute instance.

TypeA or TypeB can be instantiated given the constructorargument strings, a list of strings:

ta = TypeA(strings=['foo', 'bar'])
tb = TypeA(strings=['bat', 'bar'])

This list will generate a collectionof StringAttribute objects, which are persisted into a table that’slocal to either the type_a_strings or type_b_strings table:

>>> print(ta._strings)
[<__main__.StringAttribute object at 0x10151cd90>,
    <__main__.StringAttribute object at 0x10151ce10>]

When constructing the association_proxy(), thedeclared_attr decorator must be used so that a distinctassociation_proxy() object is created for each of the TypeAand TypeB classes.

Controlling table inheritance with mixins

The tablename attribute may be used to provide a function thatwill determine the name of the table used for each class in an inheritancehierarchy, as well as whether a class has its own distinct table.

This is achieved using the declared_attr indicator in conjunctionwith a method named tablename(). Declarative will alwaysinvoke declared_attr for the special namestablename, mapper_args and table_argsfunction for each mapped class in the hierarchy, except if overriddenin a subclass. The function thereforeneeds to expect to receive each class individually and to provide thecorrect answer for each.

For example, to create a mixin that gives every class a simple tablename based on class name:

from sqlalchemy.ext.declarative import declared_attr
 
class Tablename:
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()
 
class Person(Tablename, Base):
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}
 
class Engineer(Person):
    __tablename__ = None
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    primary_language = Column(String(50))

Alternatively, we can modify our tablename function to returnNone for subclasses, using has_inherited_table(). This hasthe effect of those subclasses being mapped with single table inheritanceagainst the parent:

from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.ext.declarative import has_inherited_table
 
class Tablename(object):
    @declared_attr
    def __tablename__(cls):
        if has_inherited_table(cls):
            return None
        return cls.__name__.lower()
 
class Person(Tablename, Base):
    id = Column(Integer, primary_key=True)
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}
 
class Engineer(Person):
    primary_language = Column(String(50))
    __mapper_args__ = {'polymorphic_identity': 'engineer'}

Mixing in Columns in Inheritance Scenarios

In contrast to how tablename and other special names are handled whenused with declared_attr, when we mix in columns and properties (e.g.relationships, column properties, etc.), the function isinvoked for the base class only in the hierarchy. Below, only thePerson class will receive a columncalled id; the mapping will fail on Engineer, which is not givena primary key:

class HasId(object):
    @declared_attr
    def id(cls):
        return Column('id', Integer, primary_key=True)
 
class Person(HasId, Base):
    __tablename__ = 'person'
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}
 
class Engineer(Person):
    __tablename__ = 'engineer'
    primary_language = Column(String(50))
    __mapper_args__ = {'polymorphic_identity': 'engineer'}

It is usually the case in joined-table inheritance that we want distinctlynamed columns on each subclass. However in this case, we may want to havean id column on every table, and have them refer to each other viaforeign key. We can achieve this as a mixin by using thedeclaredattr.cascading modifier, which indicates that thefunction should be invoked for each class in the hierarchy, in _almost(see warning below) the same way as it does for tablename:

class HasIdMixin(object):
    @declared_attr.cascading
    def id(cls):
        if has_inherited_table(cls):
            return Column(ForeignKey('person.id'), primary_key=True)
        else:
            return Column(Integer, primary_key=True)
 
class Person(HasIdMixin, Base):
    __tablename__ = 'person'
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}
 
class Engineer(Person):
    __tablename__ = 'engineer'
    primary_language = Column(String(50))
    __mapper_args__ = {'polymorphic_identity': 'engineer'}

Warning

The declaredattr.cascading feature currently doesnot allow for a subclass to override the attribute with a differentfunction or value. This is a current limitation in the mechanics ofhow @declaredattr is resolved, and a warning is emitted ifthis condition is detected. This limitation does notexist for the special attribute names such as __tablename, whichresolve in a different way internally than that ofdeclared_attr.cascading.

New in version 1.0.0: added declared_attr.cascading.

Combining Table/Mapper Arguments from Multiple Mixins

In the case of table_args or mapper_argsspecified with declarative mixins, you may want to combinesome parameters from several mixins with those you wish todefine on the class itself. Thedeclared_attr decorator can be usedhere to create user-defined collation routines that pullfrom multiple collections:

from sqlalchemy.ext.declarative import declared_attr
 
class MySQLSettings(object):
    __table_args__ = {'mysql_engine':'InnoDB'}
 
class MyOtherMixin(object):
    __table_args__ = {'info':'foo'}
 
class MyModel(MySQLSettings, MyOtherMixin, Base):
    __tablename__='my_model'
 
    @declared_attr
    def __table_args__(cls):
        args = dict()
        args.update(MySQLSettings.__table_args__)
        args.update(MyOtherMixin.__table_args__)
        return args
 
    id =  Column(Integer, primary_key=True)

Creating Indexes with Mixins

To define a named, potentially multicolumn Index that applies to alltables derived from a mixin, use the “inline” form of Index andestablish it as part of table_args:

class MyMixin(object):
    a =  Column(Integer)
    b =  Column(Integer)
 
    @declared_attr
    def __table_args__(cls):
        return (Index('test_idx_%s' % cls.__tablename__, 'a', 'b'),)
 
class MyModel(MyMixin, Base):
    __tablename__ = 'atable'
    c =  Column(Integer,primary_key=True)