title: Can SQLAlchemy events be used to update a denormalized data cache?
body: <p>For performance reasons, I've got a denormalized database where some tables contain data which has been aggregated from many rows in other tables.  I'd like to maintain this denormalized data cache by using <a href="http://docs.sqlalchemy.org/en/rel_0_8/orm/events.html" rel="nofollow noreferrer">SQLAlchemy events</a>.  As an example, suppose I was writing forum software and wanted each <code>Thread</code> to have a column tracking the combined word count of all comments in the thread in order to efficiently display that information:</p>
  <pre><code>class Thread(Base):
      id = Column(UUID, primary_key=True, default=uuid.uuid4)
      title = Column(UnicodeText(), nullable=False)
      word_count = Column(Integer, nullable=False, default=0)
  
  class Comment(Base):
      id = Column(UUID, primary_key=True, default=uuid.uuid4)
      thread_id = Column(UUID, ForeignKey('thread.id', ondelete='CASCADE'), nullable=False)
      thread = relationship('Thread', backref='comments')
      message = Column(UnicodeText(), nullable=False)
      
      @property
      def word_count(self):
          return len(self.message.split())
  </code></pre>
  <p>So every time a comment is inserted (for the sake of simplicity let's say that comments are never edited or deleted), we want to update the <code>word_count</code> attribute on the associated <code>Thread</code> object.  So I'd want to do something like</p>
  <pre><code>def after_insert(mapper, connection, target):
      thread = target.thread
      thread.word_count = sum(c.word_count for c in thread.comments)
      print(&quot;updated cached word count to&quot;, thread.word_count)
  
  event.listen(Comment, &quot;after_insert&quot;, after_insert)
  </code></pre>
  <p>So when I insert a <code>Comment</code>, I can see the event firing and see that it has correctly calculated the word count, but that change is not saved to the <code>Thread</code> row in the database.  I don't see any caveats about updated other tables in the <a href="http://docs.sqlalchemy.org/en/rel_0_8/orm/events.html#sqlalchemy.orm.events.MapperEvents.after_insert" rel="nofollow noreferrer">after_insert documentation</a>, though I do see some caveats in some of the others, such as <a href="http://docs.sqlalchemy.org/en/rel_0_8/orm/events.html#sqlalchemy.orm.events.MapperEvents.after_delete" rel="nofollow noreferrer">after_delete</a>.</p>
  <p>So is there a supported way to do this with SQLAlchemy events?  I'm already using SQLAlchemy events for lots of other things, so I'd like to do everything that way instead of having to write database triggers.</p>