Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!de-l.enfer-du-nord.net!feeder1.enfer-du-nord.net!cs.uu.nl!news.stack.nl!newsfeed.xs4all.nl!newsfeed6.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail Return-Path: 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; 'situation,': 0.09; 'server,': 0.12; 'case.': 0.15; '"frank': 0.16; 'clear.': 0.16; 'cursor': 0.16; 'dangerous.': 0.16; 'dbms': 0.16; 'iterator': 0.16; 'iterator.': 0.16; 'pipe,': 0.16; 'row': 0.16; 'sqlite3': 0.16; 'subject:iterable': 0.16; 'wrote': 0.19; 'seems': 0.19; 'suggest': 0.20; 'trying': 0.21; 'connections': 0.21; 'issue.': 0.21; 'subject:Question': 0.21; 'obviously': 0.23; 'correct,': 0.23; 'changed': 0.24; 'writes:': 0.25; 'extreme': 0.28; 'problem': 0.29; 'lines': 0.30; 'eat': 0.30; 'correct': 0.31; 'returning': 0.32; 'to:addr:python-list': 0.32; 'instead': 0.33; 'there': 0.33; 'it?': 0.33; 'header:X -Complaints-To:1': 0.33; 'retain': 0.34; 'usual': 0.34; 'thank': 0.35; 'data,': 0.35; 'unless': 0.35; 'connection': 0.36; 'optimization': 0.36; 'executing': 0.36; 'but': 0.37; 'using': 0.37; 'received:org': 0.37; 'open': 0.38; 'finished': 0.38; 'returned': 0.38; 'processing': 0.38; 'should': 0.39; 'subject:: ': 0.39; 'to:addr:python.org': 0.39; 'being': 0.40; 'more': 0.60; 'your': 0.61; 'frank': 0.64; 'view': 0.65; 'here': 0.65; 'benefit': 0.66; 'received:41': 0.70; 'offer': 0.71; 'connection,': 0.73; 'database.': 0.73; 'analysis': 0.75; 'connection.': 0.77; 'loses': 0.84; 'setting,': 0.84 X-Injected-Via-Gmane: http://gmane.org/ To: python-list@python.org From: "Frank Millman" Subject: Re: Question about 'iterable cursors' Date: Sun, 6 Nov 2011 11:39:56 +0200 References: <87pqh54nmh.fsf@dpt-info.u-strasbg.fr> 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 List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 53 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1320572413 news.xs4all.nl 6840 [2001:888:2000:d::a6]:52246 X-Complaints-To: abuse@xs4all.nl Xref: x330-a1.tempe.blueboxinc.net comp.lang.python:15373 "Alain Ketterlin" wrote > "Frank Millman" 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