Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #15373

Re: Question about 'iterable cursors'

Show key headers only | View raw

"Alain Ketterlin" <alain@dpt-info.u-strasbg.fr> wrote
> "Frank Millman" <frank@chagford.com> writes:
>
>> I am using a few DB_API adaptors - ceODBC for Sql Server, psycopg2 for
>> PostgreSQL, and sqlite3 for sqlite3.
>>
>> They all offer the feature that if a cursor executes a SELECT, the
>> cursor returns an iterator which can be used to fetch one row at a
>> time. I have been using this feature for a while and it seems like a
>> good thing'.
>>
>> Now I am not so sure. I am using a connection pool to maintain
>> connections to the database. A principle I am following is that a
>> connection must be returned quickly, so that it is available for
>> reuse.
>>
>> I have been happily returning the connection, but keeping the cursor
>> open while processing the rows selected. I now realise that this is
>> dangerous. Therefore I have changed my system to execute fetchall() on
>> the cursor before returning the connection. This obviously loses the
>> benefit of the iterator.
>>
>> I would appreciate confirmation that my thinking is correct on this
>> issue. Or is there any way that I can have my cake and eat it?
>
> Your thinking is correct: you need to keep the connection while
> processing the cursor. Databases are made to scale, you may well be
> processing the first lines of the result before the DBMS has even
> finished scanning tables. View this as a pipe, the cursor being one end
> of the pipe. The usual setting, fetching one line at a time, lets you
> overlap your processing with the network transfers.
>
> Fetching all data, returning the connection, and then start processing
> only makes sense if the processing take a lot of time (I mean: a lot
> more than fetching results), which is a rare case. Unless you are in
> such an extreme situation, I would suggest leaving the optimization to
> the connection pool, which is here to solve what you are trying to
> solve.
>

Thank you, Alain. That is very clear.

So my analysis of the problem is correct, but my solution is wrong.

Instead of executing fetchall() and returning the connection, I should 
retain the connection until I have exhausted the cursor.

That makes a lot of sense.

Frank

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Question about 'iterable cursors' "Frank Millman" <frank@chagford.com> - 2011-11-06 10:54 +0200
  Re: Question about 'iterable cursors' Alain Ketterlin <alain@dpt-info.u-strasbg.fr> - 2011-11-06 10:16 +0100
    Re: Question about 'iterable cursors' "Frank Millman" <frank@chagford.com> - 2011-11-06 11:39 +0200
    Re: Question about 'iterable cursors' Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2011-11-06 12:04 -0800
      Re: Question about 'iterable cursors' John Nagle <nagle@animats.com> - 2011-11-06 22:04 -0800
        Re: Question about 'iterable cursors' Lie Ryan <lie.1296@gmail.com> - 2011-11-08 16:29 +1100

csiph-web