Re: Which Python library - psycopg2 or pygresql?

So I thought, "lets learn a bit of Python", and I stumbled upon
a choice of these two libraries. Whch would you suggest?
How do they differ?

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.

Regards,
Dawid

PS: I don't want to start a flame war! I just feel I need a bit
of knowledge-push to get me going. ;-)

On Apr 15, 2008, at 10:27 AM, Dawid Kuroczko wrote:
> So I thought, "lets learn a bit of Python", and I stumbled upon
> a choice of these two libraries. Whch would you suggest?
> How do they differ?
>
> 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.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Dawid Kuroczko wrote:
> So I thought, "lets learn a bit of Python", and I stumbled upon
> a choice of these two libraries. Whch would you suggest?
> How do they differ?
>
Well, pygresql seems unmaintained since mid 2006 and the psycopg2 site
is currently and regularly down. Neither inspires confidence.

As to differences, here's one:

Using pygresql
...
result=db.query('select false as booltest')
boolean_result = result.dictresult()[0]['booltest']
print boolean_result
if boolean_result:
print "The result was true"
else:
print "The result was false"

This prints:
f
The result was true

Huh? Seems that pygresql treats boolean as character 't' or 'f', python
evaluates both as 'true' and hilarity ensues. (Yes, I just spent some
"quality time" tracking a bug in a script that used pygresql and had a
loop with a test of a boolean column.)

Using psycopg2:
...
cur.execute('select false as booltest')
boolean_result = cur.fetchall()[0][0]
print boolean_result
if boolean_result:
print "The result was true"
else:
print "The result was false"

This prints:
False
The result was false

There was a brief discussion at the PG users group last week and the
bias was toward psycopg2.

Cheers,
Steve

Just a side note:
pyPgSQL is broken with standard_conforming_strings = on
(see groups.google.com/group/trac-dev)

2008/4/15, Dawid Kuroczko <qnex42(at)gmail(dot)com>:
> So I thought, "lets learn a bit of Python", and I stumbled upon
> a choice of these two libraries. Whch would you suggest?
> How do they differ?
>
> 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.
>
> Regards,
> Dawid
>
> PS: I don't want to start a flame war! I just feel I need a bit
> of knowledge-push to get me going. ;-)
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Filip Rembiałkowski

On Tue, Apr 15, 2008 at 09:21:19AM -0700, Steve Crawford wrote:

>> So I thought, "lets learn a bit of Python", and I stumbled upon
>> a choice of these two libraries. Whch would you suggest?
>> How do they differ?
>>
> Well, pygresql seems unmaintained since mid 2006 and the psycopg2 site
> is currently and regularly down. Neither inspires confidence.
The psycopg2 site is (supposedly) actively being worked on
since the 2.0.7 release yesterday :-)

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, 15 Apr 2008, Dawid Kuroczko wrote:

> So I thought, "lets learn a bit of Python", and I stumbled upon
> a choice of these two libraries. Whch would you suggest?

Use psycopg2. It's better maintained and has a better feature set at this
point. I would specifically recommend that you look at all the example
programs that come with the software. There's lots of stuff there that is
easier to pick up that way than by reading the documentation, if it's even
covered in the docs at all.

> PS: I don't want to start a flame war!

Unless D'Arcy suddenly appears with a new pygresql rev that blows everyone
away I think this is a safe topic. Not impossible but not too likely I
think.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

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

On Thu, Apr 17, 2008 at 04:06:57AM -0700, Paul Boddie wrote:

> 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
If one wants to operate on one/a range of row(s) but the
code fetches "all" rows (for various values of all) then I'd
suspect there's something missing in the SQL statement, say,
a LIMIT or appropriate WHERE conditions - regardless of
whether a cursor is used or not.

If you refer to whether server-side cursors are used one
must explicitly request them from psycopg2 by using the
"name" argument to the connection.Cursor() call. Combine
that with a Python generator and one should end up with
truly on-demand single-row fetching.

Unfortunately, I am not entirely sure how and when psycopg2
uses (database) cursors when no name argument is supplied.

IMO the cursor concept of the DB-API is broken anyhow -
everything is forced to go through a (DB-API) cursor no
matter whether a database-side cursor would be wanted or not
and there's no provision for controlling the latter via the
API itself.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On 18 Apr, 14:12, Karsten(dot)Hilb(dot)(dot)(dot)(at)gmx(dot)net (Karsten Hilbert) wrote:
>
> If one wants to operate on one/a range of row(s) but the
> code fetches "all" rows (for various values of all) then I'd
> suspect there's something missing in the SQL statement, say,
> a LIMIT or appropriate WHERE conditions - regardless of
> whether a cursor is used or not.

But if you want to process all of the rows, and you don't want the
client to suck them all down at once, then you need to use the
database system's cursor support.

> If you refer to whether server-side cursors are used one
> must explicitly request them from psycopg2 by using the
> "name" argument to the connection.Cursor() call. Combine
> that with a Python generator and one should end up with
> truly on-demand single-row fetching.

As I noted, the problem is arguably shared between the database system
(because cursors don't work with certain statements that you might
use, and there's no way of finding out without trying) and the
database adapter (because it doesn't try to support the behaviour
implied by the DB-API). Inventing names for cursors, although tedious,
is the easy part in all this.

> Unfortunately, I am not entirely sure how and when psycopg2
> uses (database) cursors when no name argument is supplied.

It doesn't.

> IMO the cursor concept of the DB-API is broken anyhow -
> everything is forced to go through a (DB-API) cursor no
> matter whether a database-side cursor would be wanted or not
> and there's no provision for controlling the latter via the
> API itself.

Well, the DB-API doesn't seem to be moving in any real direction these
days, anyway. I've wanted and even proposed code for a single
parameter standard, and the progress on that matter has been glacial:
it's too controversial to do what ODBC and JDBC have been doing for
years, apparently. Still, I don't really see that doing the equivalent
of a cursor.fetchall for something like cursor.fetchone is appropriate
when "all" might be millions of rows, but that's just my view.

Paul

On Tuesday 15 April 2008 10:27:14 am Dawid Kuroczko wrote:
> Whch would you suggest?
> How do they differ?

Sorry to bring this back up (I try to keep up with this list but it's hard!),
but isn't licensing a concern?

If I understand correctly, pygresql is BSD-licensed, but depends on MX which
is GPL *incompatible* whereas psycopg is GPL.

If that is the case, which one you choose depends strictly on the license of
your project. If GPL, use psychopg. If anything else, use pygresql.

Is that accurate?

Micah,

psycopg2 has a license extensions which allows basically to use
psycopg2 binaries without distributing source code as long as there
are no modifications to the psycopg2 C code

best wishes

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!