Which Python module for PostgreSQL?

[W. Borgert]

Hi,

I'm not new to Python, but new to databases and PostgreSQL.
I like to write a PostgreSQL client application (not code
running inside of the RDBMS), and Debian has four modules:

python2.3-pgsql - A Python DB-API 2.0 interface to PostgreSQL v7.x
python2.3-popy - module providing access to PostgreSQL from Python2.3
python2.3-psycopg - Python 2.3 module for PostgreSQL
python2.3-pygresql - PostgreSQL module for Python

Now I'm confused: Which one should I use? I'm going to try
python2.3-psycopg, but maybe the other variants are "easier
to use"? Or faster? Or more complete? Thread-safe?

Thanks in advance for your hints!

Cheers,

 

[Ganesan R]

Hi,
I'm not new to Python, but new to databases and PostgreSQL.
I like to write a PostgreSQL client application (not code
running inside of the RDBMS), and Debian has four modules:

python2.3-pgsql - A Python DB-API 2.0 interface to PostgreSQL v7.x
python2.3-popy - module providing access to PostgreSQL from Python2.3
python2.3-psycopg - Python 2.3 module for PostgreSQL
python2.3-pygresql - PostgreSQL module for Python

I prefer the first one - it's BSD licensed, actively maintained and
supported. It's also very compatible with the python SQLite binding which I
use regularly.

Ganesan

 

[simo]

I'd like to know too - I had a play with psychog but couldn't get it
to connect, and couldn't find any documentation for it.

I don't have a huge amount of PostgreSQL knowledge (more of a MySQL
person myself) so I could have it setup wrong, but I couldn't figure
anything out as the error message was useless (something about IDENT
user).

 

[Rene Pijlman]

W. Borgert:

python2.3-pgsql - A Python DB-API 2.0 interface to PostgreSQL v7.x
python2.3-popy - module providing access to PostgreSQL from Python2.3
python2.3-psycopg - Python 2.3 module for PostgreSQL
python2.3-pygresql - PostgreSQL module for Python

Now I'm confused: Which one should I use?

I scanned the newsgroups and documentation with the same question some
time ago. I can't remember all the reasons, but I chose pgsql. No regrets.

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

I'd like to know too - I had a play with psychog but couldn't get it
to connect, and couldn't find any documentation for it.

I don't have a huge amount of PostgreSQL knowledge (more of a MySQL
person myself) so I could have it setup wrong, but I couldn't figure
anything out as the error message was useless (something about IDENT
user).

It's a PostgreSQL question, really. It sounds like the user account you're trying
to connect to the database from doesn't have the necessary permissions. I can
hardly imagine that a copy-and-paste strategy of the exact error message into
Google wouldn't have led you further ;-)

-- Gerhard

 

[Skip Montanaro]

simo> I'd like to know too - I had a play with psychog but couldn't get
simo> it to connect, and couldn't find any documentation for it.

If you're familiar with SQL programming in Python in general, it's pretty
straightforward. It does adhere to v2 of the DB API, so PEP-249 is useful
reading. Also, see the doc/examples directory. Here's a psycopg "hello
world" (change the connect arg to suit your server):

import psycopg
conn = psycopg.connect('dbname=test user=test')
curs = conn.cursor()
curs.execute("""select 'hello world'""")
print curs.fetchall()

Skip

 

[David M. Cook]

I prefer the first one - it's BSD licensed, actively maintained and
supported. It's also very compatible with the python SQLite binding which I
use regularly.

PyGreSQL (the first one), doesn't seem to handle boolean's correctly in
Python 2.3. Is there a workaround (aside from translating back and forth
before saving)? This is the version that ships with Red Hat 9.

I use psycopg usually, though I try to test my progs with all of them
(except popy). It's not too hard to accomadate all of them with just a few
tweaks (usually in the way they handle booleans), since they all use
pyformat.

pyPgSQL does seem to have the best win32 support.

Dave Cook

 

[Ganesan R]

I was referring to python2.3-pgsql which is the same as pyPgSQL.

PyGreSQL (the first one), doesn't seem to handle boolean's correctly in
Python 2.3. Is there a workaround (aside from translating back and forth
before saving)? This is the version that ships with Red Hat 9.

I presume you meant to say "the last one" .

Ganesan

 

[Gandalf]

I use psycopg usually, though I try to test my progs with all of them
(except popy). It's not too hard to accomadate all of them with just a few
tweaks (usually in the way they handle booleans), since they all use
pyformat.

Hmm, this is why I use C-style booleans (e.g. an integer where 0 means
false and not 0 means true).
It is totally portable (can be used with any DBMS and with any DB-API
module). I can be wrong doing
this but it works fine for me.

G

 

[David M. Cook]

I was referring to python2.3-pgsql which is the same as pyPgSQL.

Oh, those wacky Debian kids and their renamings. I assumed pgsql was the
"standard" module that ships with postgres.

pyPgSQL seems to handle booleans quite transparently.

Dave Cook

 

[David M. Cook]

Hmm, this is why I use C-style booleans (e.g. an integer where 0 means
false and not 0 means true).

You mean in your SQL schema? I guess I could start doing that. I'm moving
to sqlite anyway, which doesn't have datatypes at all.

Dave Cook