sqlalchemy Move mixin columns to end

user_72911 0
Viewed 5248

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?

sqlalchemy python
5 Answers

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)
user_5776690

I found that I could set the column order (to the last position) on the Mixin using:

@declared_attr
def notes(cls):
    col = sa.Column(sa.String(4000) , nullable=False, default='')
    # get highest column order of all Column objects of this class.
    last_position = max([value._creation_order
            for key, value in vars(cls).items()
            if isinstance(value, Column)])
    col._creation_order = last_position + 0.5
    return col

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)

To set the column order based on the location of another column (similar to

alter table `some_table` modify `some_colum` `some_type` after `some_other_column;

see https://stackoverflow.com/a/3822219/488331)

You can use:

@declared_attr
def notes(cls):
    col = sa.Column(sa.String(4000) , nullable=False, default='')
    col._creation_order = cls.some_other_column._creation_order + 0.5
    return col

NOTE: If you use + 1 you end up 2 columns back. I don't really understand why you can even use a decimal.

To set the column order based off of the location of the first column (make this always the 4th column) you could do:

@declared_attr
def notes(cls):
    col = sa.Column(sa.String(4000) , nullable=False, default='')
    # get lowest column order of all Column objects of this class.
    start_position = min([value._creation_order
            for key, value in vars(cls).items()
            if isinstance(value, Column)])
    col._creation_order = start_position + 3.5
    return col
user_4883310
Related