Postgresql - Preferred Python Library

[Graeme Matthew]

Hi All


I have noticed that there are a numbe rof client libraries for connecting to
Postgres.

Can anyone tell me what is the recommended Python library for database
connections

many thanks

 

[Cliff Wells]

Hi All


I have noticed that there are a numbe rof client libraries for connecting to
Postgres.

Can anyone tell me what is the recommended Python library for database
connections

I'd strongly recommend psycopg. I've used both psycopg and the pgsql
module and found psycopg to be much more stable. Unfortunately pgsql is
the module shipped with PostgreSQL, so you'll probably have to download
and compile psycpg yourself (and it isn't always particularly
straightforward. Luckily it's worth the trouble.).

Regards,
Cliff

 

[Harald Massa]

I have noticed that there are a numbe rof client libraries for

connecting to Postgres.
Can anyone tell me what is the recommended Python library for database
connections

I recommend pyPgSQL - http://pypgsql.sourceforge.net/

it gives you high level (DBAPI2) and if you wish also low level access
(libpq)

Twisted seems to use it, to.


Be happy! There are only around 3 PostgreSQL connectors. There is still the
rumour that Python "autopromotion from int to long" was introduced because
a *bot wanted to write a skript to count Python Webframeworks. In the old
times everybody started his own webframework, nowaday everybody starts her
own Python like language.

Harald

 

[Matt Goodall]

I've just started experimenting with psycopg2
(http://wiki.initd.org/Projects/PsycopgTwo) and it's looking good.

As far as I know, psycopg2 is the only DB-API 2.0 implementation for
PostgreSQL that uses Python 2.3's datetime types by default. In fact, I
think it supports *all* native Python types that have a corresponding
database type. For me, this makes a big difference. I find moving data
from RDBMS to application to RDBMS quite irritating and it just makes
matters worse when I have to convert between data types too.

Assuming I don't come across any serious issues, I will be using
psycopg2 from now on.

(I also use dateutil, <https://moin.conectiva.com.br/DateUtil>, which
extends datetime with lots of useful stuff. So useful, it should
probably be part of the standard library.)

I recommend pyPgSQL - http://pypgsql.sourceforge.net/

it gives you high level (DBAPI2) and if you wish also low level access
(libpq)

Twisted seems to use it, to.

Twisted's adbapi will use any DB-API 2.0 implementation with a
threadsafety level of 1 or greater. I have tested one application with
pypgsql, pygresql, psycopg and psycopg2. Twisted was happy with all of
them, although differences in how python and column types are handled
means the application code is not necessarily portable by default.

See <http://twistedmatrix.com/documents/current/howto/enterprise> for
more information on using an RDMBS from a Twisted application.

Cheers, Matt

--
__
/ \__ Matt Goodall, Pollenation Internet Ltd
\__/ \ w: http://www.pollenation.net
__/ \__/ e: (e-mail address removed)
/ \__/ \ t: +44 (0)113 2252500
\__/ \__/
/ \ Any views expressed are my own and do not necessarily
\__/ reflect the views of my employer.

 

[Lee Harr]

I've just started experimenting with psycopg2
(http://wiki.initd.org/Projects/PsycopgTwo) and it's looking good.

As far as I know, psycopg2 is the only DB-API 2.0 implementation for
PostgreSQL that uses Python 2.3's datetime types by default.

ooh, aah. )

Yes, the datetime handling in the older psycopg was getting on my
nerves... Native datetime is going to make things much nicer.

That said, psycopg works very well for me.

The only other thing that is a bit strange is passing array
types in to the database. I have been using this:

alist = ['foo', 'bar']
query = 'SELECT ARRAY%(alist)s'
execute(query, locals())

It works perfectly well, but it seems a bit strange...

 

[David Cook]

Can anyone tell me what is the recommended Python library for database
connections

My preference is psycopg, but I also try to test with the standard one that
comes with postgres (the DBAPI module is pgdb) and pyPgSQL.

Dave Cook

 

[Tim Roberts]

I'd strongly recommend psycopg. I've used both psycopg and the pgsql
module and found psycopg to be much more stable.

So have I, and I wish I could quantify the difference. It's not like
pyPgSQL crashes on me every day, but there's something intangible about
psycopg that appeals to me. I've switched to it everywhere.

 

[Cliff Wells]

So have I, and I wish I could quantify the difference. It's not like
pyPgSQL crashes on me every day, but there's something intangible about
psycopg that appeals to me. I've switched to it everywhere.

My tangible difference was that a script I have that inserts about
40,000 records into a table (as a single transaction) could never
complete (hard crash) with the standard pgsql module but completes
without a hitch with psycopg. Other than that I don't have a problem
with it <wink>.

Cliff

 

[Harald Massa]

I've just started experimenting with psycopg2

(http://wiki.initd.org/Projects/PsycopgTwo) and it's looking good.

Matt,

on my research of Python connectors to postgresql I also saw psycopg. I had
to stop further testing because no win32 binaries were available.

My knowledge grew since then, and meanwhile I was able to compile pyPgSQL
myself using the instructions and MingW32.

I was not able to compile psycopg2 with MingW32. I assume you use it
outside windows, or do you have any binaries for Win32 and psycopg? The
type issue sounds promising, would like to test it ..

Harald

 

[Irmen de Jong]

Matt,

on my research of Python connectors to postgresql I also saw psycopg. I had
to stop further testing because no win32 binaries were available.

They are!
Look here: http://www.stickpeople.com/projects/python/psycopg/index.html

That link is on their site http://initd.org/software/initd/psycopg ....


--Irmen