[Tutor] Python & PostgreSQL

[Scott Chapman]

Tutorial here:
http://www.amk.ca/python/writing/DB-API.html

Here's my notes from digging around the internet to make this decision.  I 
settled on pyscopg.  These notes start with a quote from someone else's email 
and some replies to it, etc. Then it goes into the specific libraries.  
There's a couple web pages cited that do comparisons but the info from those 
pages is included below also so it's not needed to consult them.  I don't 
have dates on the email quotes but they are relevant.  I did this research in 
early April of this year. Hope this helps:
========
Basically my deciding factor is whether i want to use libpq (pgsql c client
lib) or python db-api or if i want to use zope, if db-api i generally would
recommend pyscopg as it implements db-api compliance at the c level over
libpq. pypgsql is a c extension binding over libpq, with db-api interface
written in python. if zope, i would use psycopg as it has extensive testing
in that area, and converts to mxDateTimes to ZopeTimes. imho, the two
bindings serve different audiences.

> imho, i would only use pyscopg or pypgsql. of the four they are under the 
> most active development.
Wrong! PoPy is continuing but slowly its development, exploring new ways
to make the driver more powerful. I remind you that PoPy's development
has begun in 2000, so adding quickly more and more features becomes
difficult. When we began PoPy there was only pygresql. That's right,
today there are too much drivers for postgresql, and the PoPy team is
ready to merge its work w/ others teams like psycopg team, to have less
drivers but more powerful, more featured and more stable!

reply:
my apologies if i offended, but i wouldn't consider my statement wrong, as i
said imho, those two drivers under the most active development. a statement
which does not preclude development in the other drivers, just their degree.
as the most recent release of popy was over a year ago, and the public cvs is
unchanged in over a year, its not hard to see why such a distinction is easy
to make.

thank you for the history lesson. having just looked at the public cvs of
popy, it appears that both pypgsql and psycopg offer more features, i'm
curious of what benefit from a *code* perspective you think merging the
codebases if feasible will bring.
===
>What is so important
> with PyGreSQL if "everybody" thinks that it shouldn't
> be used?

several things, when i last used pygresql a couple of years ago, i found its
db-api interface to be horribly broken. i believer there have been some fixes
to this, in the interim *years*.  perhaps most signifigantly from my own
perspective is that it plays *very* poorly with zope. to wrt, datetime
conversions, and the fact that its zope adapter executed queries serially
(which is not nesc. the fault of  pygresql) .

====
http://www.python.org/cgi-bin/moinmoin/DbApiModuleComparison?action=show
====
http://www.python.org/cgi-bin/moinmoin/PostgreSQL
====
DB API 2.0 Drivers:
========
PyGresQL - Come with PostgreSQL.
========
URL
http://www.druid.net/pygresql/

licence
BSD-like

platforms
Unix, win32

Python versions
1.5.2 thru 2.2

Extensions to DB API: none.

Alternative old nonstandard PostgreSQL interface
========
PoPy
========
Latest release:
2.0.8 (Stable)
2001/08/27
37786 bytes

URL
http://popy.sourceforge.net

licence
GPL

platforms
Unix

Has Zope DA (Database Adapter) written on top of it.
========
psycopg
========
Latest release:
2003-04-02  Federico Di Gregorio  <fog@debian.org>  Release 1.3.

URL
http://initd.org/Software/psycopg
http://www.initd.org/software/initd/psycopg

licence
GPL

Python:
1.5.2-2.2+

platforms
Unix, win32 (beta)
Unix, Windows, MacOS X

Extensions to DB API
dictfetchone(), dictfetchmany(), dictfechall() methods on the cursor object; 
these will return dictionaries instead of tuples, so you can access rows by 
name in stead of by index

Quoting styles: format,pyformat

Comments
ZopeDA available.
Written for heavily multithreaded applications in mind.
Threading: yes (conn. pooling)
Extras: typecasting objects

========
pyPgSQL
========
2002-12-07: pyPgSQL 2.3

URL
http://pypgsql.sourceforge.net

licence
BSD-like

platforms
Unix, win32
Unix, Windows, MacOS X

Python versions
2.0 thru 2.2
2.1+

Extensions to DB API

The fetch methods on cursors return an instances of PgResultSet, which you can 
use to access rows by index (like in DB-API), dictionary-like or with 
attributes. This feature can be turned off for a slight performance boost. 
Support for PostgreSQL notifications in the low-level API.

Extras:
PgResultSet, typecasting objects, notifications.

Quoting styles: pyformat

Threads: None.