Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>the after_insert() event is one way to do this, and you might notice it is passed a SQLAlchemy <code>Connection</code> object, instead of a <code>Session</code> as is the case with other flush related events. The mapper-level flush events are intended to be used normally to invoke SQL directly on the given <code>Connection</code>:</p> <pre><code>@event.listens_for(Comment, "after_insert") def after_insert(mapper, connection, target): thread_table = Thread.__table__ thread = target.thread connection.execute( thread_table.update(). where(thread_table.c.id==thread.id). values(word_count=sum(c.word_count for c in thread.comments)) ) print "updated cached word count to", thread.word_count </code></pre> <p>what is notable here is that invoking an UPDATE statement directly is also a lot more performant than running that attribute change through the whole unit of work process again. </p> <p>However, an event like after_insert() isn't really needed here, as we know the value of "word_count" before the flush even happens. We actually know it as Comment and Thread objects are associated with each other, and we could just as well keep Thread.word_count completely fresh in memory at all times using attribute events:</p> <pre><code>def _word_count(msg): return len(msg.split()) @event.listens_for(Comment.message, "set") def set(target, value, oldvalue, initiator): if target.thread is not None: target.thread.word_count += (_word_count(value) - _word_count(oldvalue)) @event.listens_for(Comment.thread, "set") def set(target, value, oldvalue, initiator): # the new Thread, if any if value is not None: value.word_count += _word_count(target.message) # the old Thread, if any if oldvalue is not None: oldvalue.word_count -= _word_count(target.message) </code></pre> <p>the great advantage of this method is that there's also no need to iterate through thread.comments, which for an unloaded collection means another SELECT is emitted.</p> <p>still another method is to do it in before_flush(). Below is a quick and dirty version, which can be refined to more carefully analyze what has changed in order to determine if the word_count needs to be updated or not:</p> <pre><code>@event.listens_for(Session, "before_flush") def before_flush(session, flush_context, instances): for obj in session.new | session.dirty: if isinstance(obj, Thread): obj.word_count = sum(c.word_count for c in obj.comments) elif isinstance(obj, Comment): obj.thread.word_count = sum(c.word_count for c in obj.comments) </code></pre> <p>I'd go with the attribute event method as it is the most performant and up-to-date.</p>
    singulars
    1. This table or related slice is empty.
    1. POCan SQLAlchemy events be used to update a denormalized data cache?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USrr-
    1. USzzzeek
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCan SQLAlchemy events be used to update a denormalized data cache?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COIt seems to me that the `after_insert` method also has the advantage of preventing race conditions. If it is the case am I correct in preferring them to attribute events in cases where it is business-critical that the data remains consistent at all times?
      singulars
      1. PO
      1. USedsioufi
    2. COif you really wanted the UPDATE to be "atomic" so that no race condition could occur even without serializable isolation, then you'd need to run it against a subquery on all the thread comments, rather than iterating in memory on thread.comments, which might not be the full collection.
      singulars
      1. PO
      1. USzzzeek
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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