CODEWITHSUNDEEP
pythonsqlalchemy
Steven Van Ingelgem
Steven Van Ingelgem

Reputation: 1009

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!

Upvotes: 2

Views: 5094

Answers (4)

pregmatch
pregmatch

Reputation: 2647

from sqlalchemy import Computed
class Interval(Base):
    __tablename__ = 'interval'

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

    length = Column(Integer, Computed("end - start"))  # Computed column

alembic revision --autogenerate -m "Add length computed column to Interval table"

and that will give you in migration script among other stuff

sa.Column('length', sa.Integer(), sa.Computed('end - start', ), nullable=True),

and simple alembic upgrade head

or to downgrade

alembic downgrade -1

Upvotes: 0

Bhavesh Achhada
Bhavesh Achhada

Reputation: 323

You may use Computed column as written in sqlalchemy/init.py line 65

You can use it like this:

from sqlalchemy import Column, Computed, Integer, ForeignKey

class Order(Base):
  item_id = Column(Integer, ForeignKey("item.id"), primary_key=True)
  unit_price = Column(Integer, nullable=False)
  quantity = Column(Integer, nullable=False)
  total_amount = Column(Integer, Computed(unit_price * quantity))  # this is computed column

Upvotes: 2

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

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.

Upvotes: 4

Mohammad Sheikhian
Mohammad Sheikhian

Reputation: 59

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)

Upvotes: -1

Related Questions