insert - sqlalchemy 的 MapperExtension 的一些问题

Question

有两个类:用户和问题

一个用户可能有很多问题，其中也包含一个 question_count
记录属于他的问题数。

所以，当我添加一个新问题时，我想更新问题的 question_count
用户。起初，我这样做:

question = Question(title='aaa', content='bbb') 
Session.add(question) 
Session.flush() 


user = question.user 
### user is not None 
user.question_count += 1 
Session.commit() 

一切顺利。

但我不想使用事件回调来做同样的事情。如下:

from sqlalchemy.orm.interfaces import MapperExtension 
class Callback(MapperExtension): 
    def after_insert(self, mapper, connection, instance): 
         user = instance.user 
         ### user is None !!! 
         user.question_count += 1 


class Question(Base): 
    __tablename__ = "questions" 
    __mapper_args__ = {'extension':Callback()} 
    .... 

在“after_insert”方法中注意:
instance.user # -> Get None!!!
为什么？

如果我将该行更改为:
Session.query(User).filter_by(id=instance.user_id).one()
我可以成功获取用户，但是:用户无法更新!

看我修改了用户:
user.question_count += 1
但是控制台中没有打印“更新”sql，并且question_count没有更新。

我尝试添加 Session.flush()或 Session.commit()在里面after_insert()方法，但两者都会导致错误。

有什么重要的东西我错过了吗？请帮帮我，谢谢

Answer 1

sqlalchemy的作者在一个论坛给了我一个有用的答案，我复制到这里:

Additionally, a key concept of the unit of work pattern is that it organizes a full list of all INSERT,UPDATE, and DELETE statements which will be emitted, as well as the order in which they are emitted, before anything happens. When the before_insert() and after_insert() event hooks are called, this structure has been determined, and cannot be changed in any way. The documentation for before_insert() and before_update() mentions that the flush plan cannot be affected at this point - only individual attributes on the object at hand, and those which have not been inserted or updated yet, can be affected here. Any scheme which would like to change the flush plan must use SessionExtension.before_flush. However, there are several ways of accomplishing what you want here without modifiying the flush plan.

The simplest is what I already suggested. Use MapperExtension.before_insert() on the "User" class, and set user.question_count = len(user.questions). This assumes that you are mutating the user.questions collection, rather than working with Question.user to establish the relationship. If you happened to be using a "dynamic" relationship (which is not the case here), you'd pull the history for user.questions and count up what's been appended and removed.

The next way, is to do pretty much what you think you want here, that is implement after_insert on Question, but emit the UPDATE statement yourself. That's why "connection" is one of the arguments to the mapper extension methods:

def after_insert(self, mapper, connection, instance): 
    connection.execute(users_table.update().\ 
       values(question_count=users_table.c.question_count +1).\ 
             where(users_table.c.id==instance.user_id)) 

I wouldn't prefer that approach since it's quite wasteful for many new Questions being added to a single User. So yet another option, if User.questions cannot be relied upon and you'd like to avoid many ad-hoc UPDATE statements, is to actually affect the flush plan by using SessionExtension.before_flush:

class MySessionExtension(SessionExtension): def before_flush(self, session, flush_context): for obj in session.new: if isinstance(obj, Question): obj.user.question_count +=1

   for obj in session.deleted: 
       if isinstance(obj, Question): 
           obj.user.question_count -= 1 

To combine the "aggregate" approach of the "before_flush" method with the "emit the SQL yourself" approach of the after_insert() method, you can also use SessionExtension.after_flush, to count everything up and emit a single mass UPDATE statement with many parameters. We're likely well in the realm of overkill for this particular situation, but I presented an example of such a scheme at Pycon last year, which you can see at http://bitbucket.org/zzzeek/pycon2010/src/tip/chap5/sessionextension.py .

而且，当我尝试时，我发现我们应该更新 user.question_count在 after_flush