vsbabu.org

PostgreSQL on Mandrake 9.1 with Python

This is a followup article to Sunday’s experiment on accessing PostgreSQL from Java. I quickly got tired of typing in all the curly braces, save-compile cycle with Java. In this article, I will try to explain how to get started with accessing data in our PostgreSQL from Python. Installing Python, necessary drivers and writing a small Python program are covered.

I. Pre-requisites

1. Basic knowledge of Python.
2. Working PostgreSQL setup.
3. Sockets enabled in PostgreSQL. See step II in the setup for Java.

II. Install Python

Login as root and issue the following commands.

urpmi python
urpmi postgresql-python

Second command installs Python driver for PostgreSQL. More drivers are listed in Python Wiki, but we will concentrate on the first one since that is what Mandrake ships with.

III. Try out a Python program

Create a file named connectPostgreSQL.py with the contents below:

from pg import connect
from string import join
from pprint import pprint

db = connect('vsbabu', 'localhost')
query = db.query("""SELECT state_code, state_name FROM states""")

print "All functions available:"
pprint(dir(query))

print "Results as a list of tuples:"
pprint(query.getresult())

print "Results as a list of dictionaries:"
pprint(query.dictresult())

print "Column names:"
pprint(query.listfields())

#let us print this as an html table
if len(query.getresult()) > 0:
    print "<table>"
    print "\t<tr>"
    for h in query.listfields():
        print "\t\t<th>%s</th>" % h
    print "\t</tr>"
    for r in query.getresult():
        print "\t<tr>"
        for d in r:
            print "\t\t<td>%s</td>" % str(d)
        print "\t</tr>"
    print "</table>"

Running it gives the following output on my system. To setup tables, database and data, go back to Pre-requisites section above.

All functions available:
['dictresult', 'fieldname', 'fieldnum', 'getresult', 'listfields', 'ntuples']
Results as a list of tuples:
[('VA', 'Virginia'), ('MD', 'Maryland'), ('DE', 'Delaware')]
Results as a list of dictionaries:
[{'state_code': 'VA', 'state_name': 'Virginia'},
 {'state_code': 'MD', 'state_name': 'Maryland'},
 {'state_code': 'DE', 'state_name': 'Delaware'}]
Column names:
('state_code', 'state_name')

	

		
state_code
		
state_name
	
	

		
VA
		
Virginia
	
	

		
MD
		
Maryland
	
	

		
DE
		
Delaware
	

The code is self-explanatory if you know Python. The last table is in plain HTML - view the page source if you are inquisitive

1. Apart from suspicions that the "bundled" pg database module isn't updated or fixed often enough (I can't confirm that, however), the main reason not to use that module is that it doesn't apparently support the DB-API. Consequently, I'd recommend pyPgSQL or psycopg.

   Even for databases where the available modules attempt to implement the DB-API, one can experience issues with portability, so I wouldn't myself even consider writing to a non-standard API like that supposedly used by the pg module. It may be more effort to install pyPgSQL and mxDateTime (although running python setup.py install isn't exactly that demanding), but it does save effort in the long run when/if you find that the module you've chosen doesn't quite perform as well as you would have liked.

   Well, those are my experiences, anyway.

   Posted by: Paul Boddie on April 22, 2003 09:29 AM