Creating a column from another column in SQLAlchemy

Question

I'd like to have a SQLAlchemy Column that would be computed from another column. For example, column month ({'Jan', ...}) from column date (like 2014-09-12), so technically it's a one-time parsing of 09 to Sep.

Is it possible to write this into the class of the object itself? Or just run updates each time records get updated?

PS: Months are taken just for example. I'm interested in a general case when the space of the derived column is infinite.

Answer 1

column_property may meet your needs. This allows you to create something that looks and acts like a normal mapped object column, but is calculated automatically from other columns.

Example:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

    # actual database columns
    firstname = Column(String(50))   
    lastname = Column(String(50))

    # calculated from the other two
    fullname = column_property(firstname + " " + lastname)   

In your example usage:

class Foo(Base):
    date = Column(DateTime)
    month_only = column_property(date.strptime("%b")) # 'Jan','Feb',etc.

Answer 2

Using database level triggers sounds like the most efficient solution to me. Read Triggered Columns for configuring such columns properly on the ORM side.