SQLAlchemy - how to map against a read-only (or calculated) property

Question

I'm trying to figure out how to map against a simple read-only property and have that property fire when I save to the database.

A contrived example should make this more clear. First, a simple table:

meta = MetaData()
foo_table = Table('foo', meta,
    Column('id', String(3), primary_key=True),
    Column('description', String(64), nullable=False),
    Column('calculated_value', Integer, nullable=False),
    )

What I want to do is set up a class with a read-only property that will insert into the calculated_value column for me when I call session.commit()...

import datetime
def Foo(object):  
    def __init__(self, id, description):
        self.id = id
        self.description = description

    @property
    def calculated_value(self):
        self._calculated_value = datetime.datetime.now().second + 10
        return self._calculated_value

According to the sqlalchemy docs, I think I am supposed to map this like so:

mapper(Foo, foo_table, properties = {
    'calculated_value' : synonym('_calculated_value', map_column=True)
    })

The problem with this is that _calculated_value is None until you access the calculated_value property. It appears that SQLAlchemy is not calling the property on insertion into the database, so I'm getting a None value instead. What is the correct way to map this so that the result of the "calculated_value" property is inserted into the foo table's "calculated_value" column?

OK - I am editing this post in case someone else has the same question. What I ended up doing was using a MapperExtension. Let me give you a better example along with usage of the extension:

class UpdatePropertiesExtension(MapperExtension):
    def __init__(self, properties):
        self.properties = properties

    def _update_properties(self, instance):
        # We simply need to access our read only property one time before it gets
        # inserted into the database.
        for property in self.properties:
            getattr(instance, property)

    def before_insert(self, mapper, connection, instance):
        self._update_properties(instance)

    def before_update(self, mapper, connection, instance):
        self._update_properties(instance)

And this is how you use this. Lets say you have a class with several read only properties that must fire before insertion into the database. I am assuming here that for each one of these read only properties, you have a corresponding column in the database that you want populated with the value of the property. You are still going to set up a synonym for each property, but you use the mapper extension above when you map the object:

class Foo(object):
    def __init__(self, id, description):
        self.id = id
        self.description = description
        self.items = []
        self.some_other_items = []

    @property
    def item_sum(self):
        self._item_sum = 0
        for item in self.items:
            self._item_sum += item.some_value
        return self._item_sum

    @property
    def some_other_property(self):
        self._some_other_property = 0
        .... code to generate _some_other_property on the fly....
        return self._some_other_property

mapper(Foo, metadata,
    extension = UpdatePropertiesExtension(['item_sum', 'some_other_property']),
    properties = {
        'item_sum' : synonym('_item_sum', map_column=True),
        'some_other_property' : synonym('_some_other_property', map_column = True)
    })

Answer 1

Thanks for editing with your answer, Jeff. I had the exact same problem and solved it using your code, here's something similar for those using a declarative base. Might save you a few minutes looking up how to specify the mapper arguments and synonyms:

from sqlalchemy.ext.declarative import declarative_base

class Users(Base):
  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)
  name = Column(String)
  _calculated_value = Column('calculated_value', String)

  __mapper_args__ = {'extension': UpdatePropertiesExtension(['calculated_value'])}

  def __init__(self, name):
    self.name = name

  @property
  def calculated_value(self):
    self._calculated_value = "foobar"
    return self._calculated_value

  calculated_value = synonym('_calculated_value', descriptor=calculated_value)