sqlalchemy Move mixin columns to end

Question

I have a sqlalchemy model, where all most all tables/objects have a notes field. So to try follow the DRY principle, I moved the field to a mixin class.

class NotesMixin(object):
    notes = sa.Column(sa.String(4000) , nullable=False, default='')

class Service(Base, NotesMixin):
    __tablename__ =  "service"
    service_id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(255), nullable=False, index=True, unique=True)


class Datacenter(Base, NotesMixin):
    __tablename__ =  "datacenter"
    datacenter_id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(255), nullable=False, index=True, unique=True)


class Network(Base, NotesMixin, StatusMixin):
    __tablename__ =  "network"
    network_id = sa.Column(sa.Integer, primary_key=True)

etc...

Now the notes column is the first column in the model/db. I know it does not affect the functionality of my app, but it irritates me a bit to see notes before id, etc. Any way to move it to the end?

Answer 1

The easy answer: just create the database tables yourself, instead of having sqlalchemy do it with metadata.create_all().

If you don't find that acceptable, I'm afraid this would require a (small) change in sqlalchemy.ext.declarative itself, or you'd have to create your own metaclass and pass it to declarative_base() with the metaclass keyword argument. That class will then get used instead of the default DeclarativeMeta.

Explanation: sqlalchemy uses the creation order of the column properties, which it stores in the "private" attribute ._creation_order (generated when Column() is called). The declarative extension does mixin columns by creating a copy of the column object from your mixin class, and adding that to the class. The ._creation_order of this copy is set to the same value as the original property of the mixin class. As the mixin class is of course created first, it's column properties will have a lower creation order than the subclass.

So, to make your request possible, a new creation order should be assigned when the copy is made, rather than taking the original. You could try and make your own metaclass based on this explanation, and use that. But you might also try and ask the sqlalchemy developers. Maybe they are willing to accept this as a bug/feature request? At least, it seems like a minor (one line) change, that would not have a any effect other than the change you ask for (which arguably is better too).

Answer 2

Found a cleaner solution:

Use the sqlalchemy.ext.declarative.declared_attr decorator in sqlalchemy 0.6.5 (sqlalchemy.util.classproperty in sqlalchemy <= 0.6.4)

class NotesMixin(object):
    @declared_attr
    def notes(cls):
        return sa.Column(sa.String(4000) , nullable=False, default='')

According to the docs, this is "for columns that have foreign keys, as well as for the variety of mapper-level constructs that require destination-explicit context". While this is strictly speaking not the case here, it does so by calling the method (and creating the column) when the subclass is constructed, thus avoiding the need to make a copy. Which means the mixin column will come at the end. Probably a better solution than hacking _creation_order...