Pros/cons of various PostGres modules

[Gerhard =?unknown-8bit?Q?H=E4ring?=]

* Jim Hefferon <jhefferon at smcvt.edu> [2002-07-13 09:52 -0700]:
> Hello,
> 
> I'm starting on a project that will have Python interfacing with
> PostGres.  I see that there are now four modules listed on the Python web
> site.  I'd be very interested in any suggestions as to which ones are
> best.
> 
> I've used PyPgSQL with good luck in the recent past.  But there are two
> that are newer

Newer? AFAIK pyPgSQL and psycopg are the youngest projects of the four
DB-API adapters (pyPgSQL, psycopg, PoPy, PyGreSQL). And the latest
pyPgSQL release was on 2002-06-04, so I don't quite understand what you
mean here :)

> that are thread safe.

pyPgSQL has a threadsafety of 1, which means you can't safely share
connections among threads, but you can use the module in a multithreaded
application. I'd never do otherwise, anyway. psycopg, however, is
/designed/ for heavily multithreaded apps, and has threadsafety=2 (can
share connections among threads), IIRC.

> Will I notice on a moderately-loaded application?

In my experience, the only solution to get a sensible answer is to fire
use the time, hotshot or profile profile modules yourself to find out.

> Are they debugged?

AFAIK pyPgSQL and psycopg are both relatively bugfree and well
maintained. I can't speak for PoPy, except that they haven't got around
to republish at their new website for over a year now.

PyGreSQL is IMO very badly maintained: there was a serious bug with
cursor.fetchone() always returning the first row of the resultset, which
made cursors basically unusable. It was known for over a year until
somebody (me) got annoyed enough to take the half-an hour to look into
the Python and C code, write a patch and submit it to the PostgreSQL
developers.

> Is one faster than the others?

psycopg is faster than pyPgSQL in my benchmarks. The bottlenecks in my
apps, however, were never the overhead of the database interface. I'd
suggest to profile the usage of the modules you consider in a real app.

You can use code like this to do so:

from pyPgSQL import PgSQL as mydbmodule
#import psycopg as mydbmodule
conn = mydbmodule.connect("")
# ...

> Any other experiences? They all look good to me!

As one of the pyPgSQL developers, I'm not unbiased, but I'd stay with
pyPgSQL and only consider psycopg as an alternative :-)

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id 86AB43C0
public key fingerprint: DEC1 1D02 5743 1159 CD20  A4B6 7B22 6575 86AB 43C0
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))