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

[James Henstridge]

On Wed, Jul 30, 2008 at 4:38 PM, Michael Elsdörfer <elsdoerfer at gmail.com> wrote:
>> indeed associated with the connection, as I believe is usually the case on
>> most databases.  Storm isn't playing any special role there.
>
> I simply saw that the sqlite backend overrides raw_execute and appears
> to sends the BEGIN (and later the COMMIT) to the connection object,
> rather than the cursor object that the base would use, and thought
> that might make a difference. Apparently it doesn't, then.

The point of multiple cursors is to allow you to work with multiple
result sets at once.  The queries are still issued in a serial fashion
on the connection.

>> There are many ways of doing it.  SQLite just doesn't want to provide
>> you with interaction over a cursor while you commit the transaction.
>
> Ok, I see. I would very much appreciate you (or anyone) giving me a
> some pointers, if at all possible. The only alternative I think of is
> using a separate connection/Store object (which is no problem per se,
> so maybe that's indeed what I should do).

Well, there are a number of options:

1. determine what rows you want to process in the first transaction by
materialising a list.

2. don't commit inside your loop.

3. Rather than iterating over the result set, index into the result
set to get the objects:
    result = store.find(Feed)
    for i in range(result.count()):
        feed = result[i]
        ...
        store.commit()

This is only reliable if the result set has a deterministic order, and
has similar downsides to (1) if there are other concurrent
transactions.


James.