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

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('desc开发者_Go百科ription', 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)
    })

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)

I'm not sure it's possible to achieve what you want using sqlalchemy.orm.synonym. Propably not given the fact how sqlalchemy keeps track of which instances are dirty and need to be updated during flush.

But there is other way how you can get this functionality - SessionExtensions (notice the engine_string variable at the top that needs to be filled):

(env)zifot@localhost:~/stackoverflow$ cat stackoverflow.py

engine_string = ''

from sqlalchemy import Table, Column, String, Integer, MetaData, create_engine
import sqlalchemy.orm as orm
import datetime

engine = create_engine(engine_string, echo = True)
meta = MetaData(bind = engine)

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

meta.drop_all()
meta.create_all()

class MyExt(orm.interfaces.SessionExtension):
    def before_commit(self, session):
        for obj in session:
            if isinstance(obj, Foo):
                obj.calculated_value = datetime.datetime.now().second + 10

Session = orm.sessionmaker(extension = MyExt())()
Session.configure(bind = engine)

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

orm.mapper(Foo, foo_table)

(env)zifot@localhost:~/stackoverflow$ ipython
Python 2.5.2 (r252:60911, Jan  4 2009, 17:40:26)
Type "copyright", "credits" or "license" for more information.

IPython 0.10 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object'. ?object also works, ?? prints more.

In [1]: from stackoverflow import *
2010-06-11 13:19:30,925 INFO sqlalchemy.engine.base.Engine.0x...11cc select version()
2010-06-11 13:19:30,927 INFO sqlalchemy.engine.base.Engine.0x...11cc {}
2010-06-11 13:19:30,935 INFO sqlalchemy.engine.base.Engine.0x...11cc select current_schema()
2010-06-11 13:19:30,936 INFO sqlalchemy.engine.base.Engine.0x...11cc {}
2010-06-11 13:19:30,965 INFO sqlalchemy.engine.base.Engine.0x...11cc select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2010-06-11 13:19:30,966 INFO sqlalchemy.engine.base.Engine.0x...11cc {'name': u'foo'}
2010-06-11 13:19:30,979 INFO sqlalchemy.engine.base.Engine.0x...11cc
DROP TABLE foo
2010-06-11 13:19:30,980 INFO sqlalchemy.engine.base.Engine.0x...11cc {}
2010-06-11 13:19:30,988 INFO sqlalchemy.engine.base.Engine.0x...11cc COMMIT
2010-06-11 13:19:30,997 INFO sqlalchemy.engine.base.Engine.0x...11cc select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2010-06-11 13:19:30,999 INFO sqlalchemy.engine.base.Engine.0x...11cc {'name': u'foo'}
2010-06-11 13:19:31,007 INFO sqlalchemy.engine.base.Engine.0x...11cc
CREATE TABLE foo (
        id VARCHAR(3) NOT NULL,
        description VARCHAR(64) NOT NULL,
        calculated_value INTEGER NOT NULL,
        PRIMARY KEY (id)
)


2010-06-11 13:19:31,009 INFO sqlalchemy.engine.base.Engine.0x...11cc {}
2010-06-11 13:19:31,025 INFO sqlalchemy.engine.base.Engine.0x...11cc COMMIT

In [2]: f = Foo('idx', 'foo')

In [3]: f.calculated_value

In [4]: Session.add(f)

In [5]: f.calculated_value

In [6]: Session.commit()
2010-06-11 13:19:57,668 INFO sqlalchemy.engine.base.Engine.0x...11cc BEGIN
2010-06-11 13:19:57,674 INFO sqlalchemy.engine.base.Engine.0x...11cc INSERT INTO foo (id, description, calculated_value) VALUES (%(id)s, %(description)s, %(calculated_value)s)
2010-06-11 13:19:57,675 INFO sqlalchemy.engine.base.Engine.0x...11cc {'description': 'foo', 'calculated_value': 67, 'id': 'idx'}
2010-06-11 13:19:57,683 INFO sqlalchemy.engine.base.Engine.0x...11cc COMMIT

In [7]: f.calculated_value
2010-06-11 13:20:00,755 INFO sqlalchemy.engine.base.Engine.0x...11cc BEGIN
2010-06-11 13:20:00,759 INFO sqlalchemy.engine.base.Engine.0x...11cc SELECT foo.id AS foo_id, foo.description AS foo_description, foo.calculated_value AS foo_calculated_value
FROM foo
WHERE foo.id = %(param_1)s
2010-06-11 13:20:00,761 INFO sqlalchemy.engine.base.Engine.0x...11cc {'param_1': 'idx'}
Out[7]: 67

In [8]: f.calculated_value
Out[8]: 67

In [9]: Session.commit()
2010-06-11 13:20:08,366 INFO sqlalchemy.engine.base.Engine.0x...11cc UPDATE foo SET calculated_value=%(calculated_value)s WHERE foo.id = %(foo_id)s
2010-06-11 13:20:08,367 INFO sqlalchemy.engine.base.Engine.0x...11cc {'foo_id': u'idx', 'calculated_value': 18}
2010-06-11 13:20:08,373 INFO sqlalchemy.engine.base.Engine.0x...11cc COMMIT

In [10]: f.calculated_value
2010-06-11 13:20:10,475 INFO sqlalchemy.engine.base.Engine.0x...11cc BEGIN
2010-06-11 13:20:10,479 INFO sqlalchemy.engine.base.Engine.0x...11cc SELECT foo.id AS foo_id, foo.description AS foo_description, foo.calculated_value AS foo_calculated_value
FROM foo
WHERE foo.id = %(param_1)s
2010-06-11 13:20:10,481 INFO sqlalchemy.engine.base.Engine.0x...11cc {'param_1': 'idx'}
Out[10]: 18

More on SessionExtensions: sqlalchemy.orm.interfaces.SessionExtension.

Newer versions of SQLAlchemy support something called Hybrid Properties, which let you define methods as setters for saving calculated values to the DB.

I'm not sure I understand the problem you're trying to solve well enough to give example code, but posting here for anyone who stumbles across this through Google.