SQLAlchemy - 如何映射只读(或计算)属性 [英] 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('description', String(64), nullable=False),
    Column('calculated_value', Integer, nullable=False),
    )

我想要做的是设置一个具有只读属性的类，当我调用 session.commit() 时，它会为我插入到calculated_value 列中...

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

根据 sqlalchemy 文档，我认为我应该这样映射:

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

这样做的问题是 _calculated_value 是 None ，直到您访问calculated_value 属性.似乎 SQLAlchemy 在插入数据库时​​没有调用该属性，所以我得到的是 None 值.什么是正确的映射方法，以便将calculated_value"属性的结果插入到 foo 表的calculated_value"列中?

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?

好的 - 我正在编辑这篇文章，以防其他人有同样的问题.我最终做的是使用 MapperExtension.让我给你一个更好的例子以及扩展的用法:

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

推荐答案

我不确定使用 sqlalchemy.orm.synonym 是否可以实现您想要的.可能没有考虑到 sqlalchemy 如何跟踪哪些实例是脏的并且需要在刷新期间更新.

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.

但是还有其他方法可以获得此功能 - SessionExtensions(注意需要填充顶部的 engine_string 变量):

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

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

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

这篇关于SQLAlchemy - 如何映射只读(或计算)属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！