Some problems with MapperExtension sqlalchemy

Question

There are two classes: User and Question

A user can have many questions, and he also contains a question_count to record how many questions belong to him.

So, when I add a new question, I want to update the question_count 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 will not use the event callback to do the same. As below:

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

• Pay attention to the "after_insert" method:

  instance.user # -> Get None!!!

  Why?

• If I changed this line to:

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

  I can get the user successfully, but: the user cannot be updated!

  See that I changed the user:

  user.question_count += 1

  But there is no “update” sql in the console, and question_count not updated.

• I am trying to add Session.flush() or Session.commit() to after_insert() , but both of them cause errors.

Is there any important thing that I am missing? Please help me, thanks.

Answer 1

The sqlalchemy author gave me a useful answer on the forum, I will copy it here:

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

The simplest is what I have already suggested. use MapperExtension.before_insert () on the class "User" and set user.question_count = LEN (user.questions). This assumes that you are mutating user.questions rather than working with Question.user to establish relationships. if you happen to use a "dynamic" relationship (this is not the case) here), you will pull the history for user.questions and calculate what has been added and removed.

The next way is to do pretty much what you think you want here, that is, implement after_insert in the question, but issue the UPDATE statement yourself. This is why “connecting” one of the arguments to the cartographer is the 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 would not prefer this approach, since it is rather wasteful for many new Questions added to a single User. So, another option, if you cannot rely on user queries and you would like to avoid many special UPDATEs, actually affect the flash plan 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 "cumulative" approach of the "before_flush" method with the "emit SQL itself" method after_insert (), you can also use SessionExtension.after_flush, calculate everything and emit a single UPDATE mass statement with many parameters. We are probably good at the excess area for this particular situation, but I presented an example of such a scheme in Pycon last year, which you can see at http://bitbucket.org/zzzeek/pycon2010/src/tip/chap5/sessionextension.py .

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

Answer 2

user , since I assume the RelationshipProperty property, is populated only after the flush (since it is this ORM point that knows how to connect two lines).

It appears that question_count is actually a derived property, which is the number of Question lines for this user. If performance is not a concern, you can use the read-only property and let the mapper do the work:

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

Otherwise, you are looking at the implementation of a trigger, either at the database level or in python (which changes the flush plan, so it’s more difficult).