How to save a calculated column in SQLAlchemy?

I would like to know how to save a calculated value in a clean way in the database:

An example (taken from the SQLAlchemy manual):

Base = declarative_base()

class Interval(Base):
    __tablename__ = 'interval'

    id = Column(Integer, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)

    @hybrid_property
    def length(self):
        return self.end - self.start

So length is calculated. But for easier query look-ups (in another database tool), I would like to have this length saved as well into the table.

So my first crude test was to simply add: length = Column(Float). Obviously this doesn’t work (but I thought I would try it nevertheless :p) as the length-method overwrites the length-Column.

Right now my only way I can think of is to calculate it in the init & defer to super(), like this:

Base = declarative_base()

class Interval(Base):
    __tablename__ = 'interval'

    id = Column(Integer, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)
    length = Column(Integer)

    def __init__(self, *args, **kwargs):
        kwargs['length'] = kwargs['end'] - kwargs['start']
        super().__init__(*args, **kwargs)

However, I feel there should be a cleaner way. Is there?

Thanks!

Answer

There’s several ways to handle this.

One is with before_update and before_insert triggers. These are a SQLAlchemy thing where changes to a model are detected automatically, and events are dispatched before the rows are written to the server. You can then use that to pre-empt the save, potentially see which columns have changed, and then update length accordingly.

Another one is using generated or computed column. These have been introduced in Postgres 12, and they’re also present in MySQL, Oracle, and MS SQL Server – although I know less about that. They do essentially the same thing, but you just add that logic to your table definition and you’re free to forget about it. SQLAlchemy supports them as of 1.3.11.