[storm] Confused about connections, cursors, transactions (SQLite)

[James Henstridge]

On Wed, Jul 30, 2008 at 12:36 AM, Gerdus van Zyl <gerdusvanzyl at gmail.com> wrote:
> You are slightly doing things the wrong way you need to replace:
>
> for feed in store.find(Feed):
>    store.commit()
>
> with:
>
> store.flush() #send changes to database
> store.commit() #commit transaction

This is incorrect: Store.commit() will flush pending changes itself,
so the flush() call is superfluous.

The problem is holding a cursor open, reading a result set, over the
transaction boundaries.  A working replacement would be:

feed_names = list(store.find(Feed.name))
for name in feed_names:
    feed = store.get(Feed, name)
    ...
    store.commit()

One thing to note with constructs like this is that if other
connections to the database modify the table, some entries may
disappear and others be missed.  For the example originally given this
is a theoretical problem because it is an in-memory db, but sqlite
maintains the transactional isolation anyway.


James.