StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan SQLAlchemy events be used to update a denormalized data cache?
text
Body
copied!<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="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 "updated cached word count to", thread.word_count event.listen(Comment, "after_insert", 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="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="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'l like to do everything that way instead of having to write database triggers.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload