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 <[email protected]> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
