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

One can also change the order of columns upon CREATE TABLE compilation (here exemplified for the postgresql dialect):

from sqlalchemy.schema import CreateTable
from sqlalchemy.ext.compiler import compiles


@compiles(CreateTable, 'postgresql')
def _compile_create_table(element, compiler, **kwargs):
    element.columns = element.columns[::-1]   # reverse order of columns
    return compiler.visit_create_table(element)

This then works with metadata.create_all().

Answer 3

I know it has been a while, but I found a very simple solution for this:

class PriorityColumn(Column):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self._creation_order = 1

This is a drop-in replacement for Column, if you are working with Mixins and you want your Derived class' attributes to be first.

class A:
    a = Column(Integer)
    b = Column(String)

class B(A, Base):
    c = PriorityColumn(Integer)
    d = PriorityColumn(Float)

# Your table will look like this:
#   B(c, d, a, b)