Pros/cons of various PostGres modules

[Dave Reed]

> From: Gerhard =?unknown-8bit?Q?H=E4ring?= <gerhard at bigfoot.de>
> 
> * 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.


Thank you! I've been bit by this bug. PyGreSQL is the one that comes
with postgresql - correct? I found it worked fine on my Red Hat 7.3
system with their python 1.5.2 rpm, but as soon as I tried to get it
working with my installation of python2.2 I had this problem. Can you
tell me where the source for this patch is so I can make certain it's
included in any postgresql installations I make?


> > 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 :-)


As I understand it - please correct me if I'm wrong - I could install
pyPgSQL and it would work w/o any changes to my code (other than
importing that module) if I've stayed with the DB-API. Is this
correct?

Thanks,
Dave