Re: Which Python library - psycopg2 or pygresql?

On 15 Apr, 17:53, e(dot)(dot)(dot)(at)myemma(dot)com (Erik Jones) wrote:
> On Apr 15, 2008, at 10:27 AM, Dawid Kuroczko wrote:
>
> > By the looks of descriptions I am slightly inclined towards
> > psycopg2, but I would feel better if I talked with people
> > who actually used these libraries.
>
> Most definitely psycopg2, it's pretty much the standard dbapi
> compliant Postgres driver library for Python.

One caveat: psycopg2 doesn't (or didn't) use cursors in a transparent
fashion like pyPgSQL does. If you're traversing potentially large data
sets, this will mean that psycopg2 will download all the result data
into the client process unless you start introducing explicit DECLARE
CURSOR statements in all the right places. Although this might not be
an issue if you're determined to only support PostgreSQL and psycopg2,
it's worth noting that the behaviour is somewhat counter-intuitive
from the perspective of people with experience of other database
systems: attempting to fetch a single row (or a limited number of
rows) may cause you to discover that the client has acquired all of
them and has taken over the job of feeding them to your code, instead
of leaving that to the database system.

Admittedly, the cause of the lack of such support in psycopg2 is the
uncertainty regarding cursor-capable statements in PostgreSQL: pyPgSQL
uses potentially awkward and fairly simplistic techniques to guess
whether the issued statement can be used with cursors, and I can
understand that the psycopg2 developers want to steer away from such
practices.

Paul