How to update the user.question_count when the user posted a question?

Question

I'm using sqlalchemy. The question is simple, but I have a big problem, because I want to use MapperExtension.

I have 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, so I use the MapperExtension of sqlalchemy. I put the logic of updating user.question_count in a after_insert callback of question. But I found I can't update the user.question_count, it will be ignored.

I don't know the reason. And, if you have the same requirement, how do you implement it?

Answer 1

The unit of work is computed before mapper extension is executed, so your changes to user object are left pending after transaction is committed first time. I suggest you not using ORM to update question_count, but execute SQL statement directly (use User.__table__.update(...) to generate statement).

Also there is a common idiom to atomically increment column: table.update(...).values(col=table.c.col+1) which is translated to UPDATE ... SET col=col+1, while your code just sets new value losing possible intermediate increments.