How to save a calculated column in SQLAlchemy?

user_577669 0
Viewed 884

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!

sqlalchemy python
2 Answers

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.

user_50153560

You can use column_property feature in sqlalchemy instead hybrid_property.

column_property: The column_property() function can be used to map a SQL expression in a manner similar to a regularly mapped Column. With this technique, the attribute is loaded along with all other column-mapped attributes at load time.

Note:also you can query on column_property.

Base = declarative_base()

class Interval(Base):
    __tablename__ = 'interval'

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

query example:

session.query(Interval).filter(Interval.length == 10)
user_46537940
Related