Thank you, Mike!
Your answer is really what I want to know, many thanks to you!
On Sep 6, 5:17 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Sep 5, 2010, at 2:23 PM, Freewind wrote:
>
>
>
>
>
> > I post the detail class here:
>
> > Session = scoped_session(sessionmaker())
> > Base = declarative_base()
>
> > class User(Base):
> > __tablename__ = 'users'
> > id = Column(Integer, primary_key=True)
> > question_count = Column(Integer)
> > questions = relationship('Question', backref='user',
> > primaryjoin='User.id==Question.user_id')
>
> > class QuestionCallback(MapperExtension):
> > def after_insert(self, mapper, connection, instance):
> > user = instance.user
> > user.question_count += 1
>
> > class Question(Base):
> > __tablename__ = 'questions'
> > id = Column(Integer, primary_key=True)
> > title = Column(String)
> > content = Column(String)
> > user_id = Column(Integer, ForeignKey('users.id'))
>
> > Now, a user post a new question, after the question has been inserted
> > to database, the 'user.question_count' should be updated too.
>
> > # load user
> > question = Question(title='aa', content='bb', user_id=user_id)
> > Session.add(question)
> > Session.commit()
>
> > What I want is: the question is added to database, and after inserted,
> > the user of this question should have its 'question_count' column
> > updated too.
>
> > I think the extension should be on the Question class. Because the
> > flow is:
>
> > Insert a question first, then update the associated user. If I write
> > 'before_insert()' on user, it won't have chance to be invoked.
>
> If the "user" row does not already exist, the "question" cannot be inserted
> first. It references the "id" column of the "users" table, so the database
> would raise a constraint exception if question row referenced a nonexistent
> user row.
>
> Because this relationship is simple and has no per-row dependencies, the unit
> of work will perform INSERT or UPDATE statements on the "User" class
> unconditionally before any which occur upon "Question".
>
> 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.
>
>
>
>
>
> > On 9月6日, 上午2时05分, Michael Bayer <mike...@zzzcomputing.com> wrote:
> >> On Sep 5, 2010, at 1:27 PM, Freewind wrote:
>
> >>> 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
>
> >> Not really sure why question.user would be None here if you had set it (in
> >> your example above, its not set, unless you have some odd join condition
> >> going on), but it would also be more appropriate here for the extension to
> >> be on the User class, not Question, using before_insert().
>
> >>> 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.
>
> >> So, if you were to emit the INSERT statements for both your User and your
> >> Question, assuming Question has a foreign key to User, which would INSERT
> >> would need to occur first ? The "User" row would already have been
> >> inserted here by the time your Question after_insert is invoked (and the
> >> fact that you can look it up proves it). People usually use
> >> before_insert() which states in its doc that you can't manipulate the
> >> flush plan or assume it will change in any way at that point, but I guess
> >> I'll make this message much more explicit for all the intra-flush() hooks.
>
> >>> 3. I try to add 'Session.flush()' or 'Session.commit()' in the
> >>> 'after_insert()' method, but both cause errors.
>
> >> The error should be clear here - "Session is already flushing". The
> >> extension points are within a transaction, within a flush. Invoking
> >> flush() within flush() is not something that is possible, and invoking
> >> commit() within flush(), assuming the reentrant flush() issue weren't
> >> present, would mean half of the flush is committed, half not, and the
> >> transaction would then be incorrectly committed before it was actually
> >> committed on the outside.- 隐藏被引用文字 -
>
> >> - 显示引用的文字 -
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/sqlalchemy?hl=en.- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
