Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!news.stack.nl!newsfeed.xs4all.nl!newsfeed6.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Return-Path: <python-python-list@m.gmane.org>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
X-Spam-Status: OK 0.001
X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'sure.': 0.05; 'happily': 0.07; 'postgresql,': 0.07; 'executes': 0.09; 'fetch': 0.09; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:80.91.229.12': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; 'received:lo.gmane.org': 0.09; 'rows': 0.09; 'server,': 0.12; 'cursor': 0.16; 'dangerous.': 0.16; 'iterator': 0.16; 'iterator.': 0.16; 'row': 0.16; 'sqlite3': 0.16; 'subject:iterable': 0.16; 'seems': 0.19; 'connections': 0.21; 'issue.': 0.21; 'subject:Question': 0.21; 'obviously': 0.23; 'changed': 0.24; 'eat': 0.30; 'thanks': 0.31; 'correct': 0.31; 'returning': 0.32; 'to:addr:python-list': 0.32; 'there': 0.33; 'it?': 0.33; 'header:X-Complaints-To:1': 0.33; 'connection': 0.36; 'but': 0.37; 'using': 0.37; 'received:org': 0.37; 'open': 0.38; 'returned': 0.38; 'processing': 0.38; 'to:addr:python.org': 0.39; 'frank': 0.64; 'benefit': 0.66; 'received:41': 0.70; 'offer': 0.71; 'connection,': 0.73; 'database.': 0.73; 'connection.': 0.77; 'loses': 0.84
X-Injected-Via-Gmane: http://gmane.org/
To: python-list@python.org
From: "Frank Millman" <frank@chagford.com>
Subject: Question about 'iterable cursors'
Date: Sun, 6 Nov 2011 10:54:48 +0200
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Gmane-NNTP-Posting-Host: 41-135-212-48.dsl.mweb.co.za
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.4657
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4862
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.12
Precedence: list
List-Id: General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe: <http://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <http://mail.python.org/pipermail/python-list>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <http://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Newsgroups: comp.lang.python
Message-ID: <mailman.2460.1320569710.27778.python-list@python.org>
Lines: 27
NNTP-Posting-Host: 2001:888:2000:d::a6
X-Trace: 1320569710 news.xs4all.nl 6899 [2001:888:2000:d::a6]:44163
X-Complaints-To: abuse@xs4all.nl
Xref: x330-a1.tempe.blueboxinc.net comp.lang.python:15371

Hi all

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?

Thanks

Frank Millman