Connecting to a PostGreSQL 7.3 Database in Python

Question

I need to connect to an old PostGreSQL database (version 7.3), and I am using Python 3.2 on an XP machine. The py-postgresql package seemed appropriate, but it seems that it only uses the 3.0 protocol, which was implemented in version 7.4. I'd like to connect to this database using Python, but when I've tried I have received an unexpected EOF error, which I believe is the result of the package using the newer protocol.

I can use the pgAdminIII program to connect to the database just fine. Any ideas?

Answer 1

Try with PyGreSQL:

PyGreSQL is a Python module that interfaces to a PostgreSQL database. It embeds the PostgreSQL query library to allow easy use of the powerful PostgreSQL features from a Python script.

The current version PyGreSQL 4.0 needs PostgreSQL 7.2 and Python 2.3 or above.

EDIT:

I made some testing against PostgreSQL 7.3 (compiled from source on Debian Squeeze) and I see it works well. I only changed one property in postgresql.conf to make local connections work well under Python:

unix_socket_directory = '/var/run/postgresql'

After that using python-pygresql package (version 4.0.2):

import pg
con1 = pg.connect('postgres', None, 5440, None, None, 'postgres', None)
con1.query('SELECT version()')
version
--------------------------------------------------------------------------------
PostgreSQL 7.3.21 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.4.5-8) 4.4
(1 row)
con1.query('SELECT width, height FROM box')
width|height
-----+------
10   |15    
18   |25    
(2 rows)

Answer 2

Unfortunately for you, both py-postgresql and psycopg2 only support the v3 protocol.

I think your only option is to use the Postgres ODBC driver, and connect to it using pyODBC or mxODBC. There is a Python 3.x port of pyodbc linked from their main project page.