Some problems with MapperExtension of sqlalchemy

Question

There are two classes: User and Question

A user may have many questions, and it also contains a question_count to record the the count of questions belong to him.

So, when I add a new question, I want update the question_count of the user. At first, I do as:

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


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

Everything goes well.

But I wan't to use event callback to do the same thing. As following:

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

1. Note in the "after_insert" method:

   instance.user # -> Get None!!!

   Why?

2. If I change that line to:

   Session.query(User).filter_by(id=instance.user_id).one()

   I can get the user successfully, But: the user can't be updated!

   Look I have modified the user:

   user.question_count += 1

   But there is no 'update' sql printed in the console, and the question_count are not updated.

3. I try to add Session.flush() or Session.commit() in the after_insert() method, but both cause errors.

Is there any important thing I'm missing? Please help me, thank you

Answer 1

user, being I assume a RelationshipProperty, is only populated after the flush (as it is only this point the ORM knows how to relate the two rows).

It looks like question_count is actually a derived property, being the number of Question rows for that user. If performance is not a concern, you could use a read-only property and let the mapper do the work:

@property
def question_count(self):
    return len(self.questions)

Otherwise you're looking at implementing a trigger, either at the database-level or in python (which modifies the flush plan so is more complicated).

Answer 2

The author of sqlalchemy gave me an useful answer in a forum, I copy it here:

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 .

And, as I tried, I found we should update the user.question_count in after_flush