[Tutor] Python & PostgreSQL

[Magnus Lyckå]

At 09:53 2003-05-08 -0400, Roger Merchberger wrote:
>1) which module I should use to access PostgreSQL thru Python?

In general, for Python database stuff, look at
http://www.python.org/topics/database/ particularly at
http://www.python.org/topics/database/modules.html

Other relevant sources of information are
http://py.vaults.ca/parnassus/apyllo.py/973100124
and http://www.python.org/pypi (which isn't so widely
used yet though).

Just for PostgreSQL, there are quite a number of options.
Beside all the ODBC-related options we have discussed today,
there are a number of competing DB-API drivers.

Obviously, they all have strong and weak sides, or they wouldn't
all exist.

First of all, PostgreSQL comes with its own Python interface. See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=pygresql.html
It has two APIs, a newer which is DB-API 2 compatible, and an
older, which is not. I guess this is the interface you must use
if you want to write PostgreSQL stored procedures in Python, see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpython.html
I don't know if this is really actively maintained though. Let's
just say that the alternatives would hardly exist if the one included
with PostgreSQL was problem-free.

Then there is the Italian choice: http://initd.org/Software/psycopg
"It was written from scratch with the aim of being very small and fast,
and stable as a rock. The main advantages of psycopg are that it supports
the full Python DBAPI-2.0 and being thread safe at level 2."
It uses the GPL license, which might be a hinder for a DB-API. I got
the impression that Federico meant that GPL is only "viral" if you
link directly to it, which you don't do in Python code, but I would not
use this in non-GPL code without a clear permission.

The next choice is at http://pypgsql.sourceforge.net/ and this is also
an actively maintained module which is fairly widely used. I don't have
personal experience, so I can't really compare it when it comes to
pewrformance or reliability etc. It uses the more liberal Python licence
though, which might be relevant for you.

There is also PoPy at http://www.zope.org/Members/tm/ but I don't think
this is actively developed any longer.

>2) if there are any good tutorials out there WRT PostgreSQL & Python?

Actually, any tutorial on Python and SQL databases via the
DB-API 2 would be relevant.

There are two ways in which the DB-API implementations differ
significantly. Connection strings might look different, but
more importantly, there are five different versions of parameter
styles, see http://www.python.org/peps/pep-0249.html

I don't know if all the PostgreSQL drivers use the same style.


--
Magnus Lycka (It's really Lyckå), magnus@thinkware.se
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program