SQLAlchemy - 如何映射只读（或计算）属性

Question

我正在尝试弄清楚如何对一个简单的只读属性进行映射，并在我保存到数据库时让这个属性触发。

下面是一个简单的例子，能让这个问题更清楚。首先，看看这个简单的表：

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),
    )

我想做的是设置一个类，里面有一个只读属性，当我调用 session.commit() 时，它会自动把值插入到 calculated_value 列中……

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

根据 sqlalchemy 的文档，我认为我应该这样进行映射：

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

问题在于，_calculated_value 在你访问 calculated_value 属性之前是 None。看起来 SQLAlchemy 在插入数据库时并没有调用这个属性，所以我得到了一个 None 值。那我该如何正确映射，以便将 "calculated_value" 属性的结果插入到 foo 表的 "calculated_value" 列中呢？

好的 - 我在编辑这个帖子，以防其他人有同样的问题。最后我使用了一个 MapperExtension。让我给你一个更好的例子，以及如何使用这个扩展：

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)

这就是如何使用它。假设你有一个类，里面有几个只读属性，这些属性必须在插入数据库之前触发。我假设每个只读属性在数据库中都有一个对应的列，你希望用属性的值填充这些列。你仍然会为每个属性设置一个同义词，但在映射对象时，你会使用上面的映射扩展：

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

新版本的SQLAlchemy支持一种叫做混合属性（Hybrid Properties）的功能，这个功能让你可以定义一些方法，用来保存计算出来的值到数据库里。

我不太确定我是否完全理解你想解决的问题，所以不能给出示例代码，不过我把这个信息发在这里，方便任何通过谷歌看到这个的人。

Answer 2

谢谢你用你的答案进行了编辑，Jeff。我遇到了完全一样的问题，并且用你的代码解决了。这里有一些类似的内容，适合那些使用声明式基础的人。这样可能能帮你节省几分钟去查找如何指定映射器参数和同义词：

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)

Answer 3

我不太确定你想要的功能是否可以通过sqlalchemy.orm.synonym来实现。考虑到sqlalchemy是如何跟踪哪些实例被修改并需要在刷新时更新的，这个方法可能行不通。

不过，还有其他方法可以实现你想要的功能，那就是使用SessionExtensions（注意顶部的engine_string变量需要填写）：

(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

关于SessionExtensions的更多信息：sqlalchemy.orm.interfaces.SessionExtension。