Must COMMIT after SELECT

[Paul Boddie]

On 7 Feb, 14:29, Steve Holden <st... at holdenweb.com> wrote:
>
> That's true, and your remarks clarify cursor usage in the DB API very
> well. Most people most of the time tend to ignore the existence of
> cursor.fetchmany() in the DB API, despite the fact that it can provide
> huge efficiency gains over both .fetchone() (can slow processing by
> requiring too many database interactions per query) and .fetchmany()

fetchall ;-)

> (which can generate large memory overhead in the case of huge result sets).

Indeed. I managed to run into a problem with DB-API-compliant code and
psycopg2 in this respect: select a large number of rows, watch
PostgreSQL do its work, see the Python process suck down the entire
result set. It's a situation reminiscent of that incident involving a
python and an alligator in the Florida swamps, but where the alligator
survives.

I don't use psycopg2 at the moment, but I did patch it to allow more
transparent usage of cursors, and there's an unapplied patch for this
floating around in the bug tracker. Lately, I've been using pyPgSQL
instead and not really doing huge selects from Python code anyway, but
I'm still using fetchmany for one or two things.

Paul