[Prophesy] Postgres and Python

[Daniel Phillips]

Hi all (especially Rasmus),

I have not forgotten about our design project here, in case you were 
wondering.  In fact, in light of recent developments, specifically, the 
threat of further enroachment of commercialism on core open source projects 
leads me to believe more than ever that we must follow through on what we set 
out to do.  (And I will remark here, that my attitude is pro-commerce in the 
sense that core open source projects are a commons on which even commercial 
users rely.  It is in the interest of commerce, as well as lovers of 
intellectual freedom, to protect our core projects.)

So today's topic is Postgres.  We must leverage our advantages, and having 
free use of a full SQL database that we can, if we need to, customize in any 
way we want, is one of them.  So I have been working with postgres and 
python, to see how well they hold up together.  The answer is 'very well'.

There are a number of packages that provide Python access to Postgres.  All 
these packages implement a standard database interface class called the
"Python Database API Specification":

   http://www.python.org/topics/database/DatabaseAPI-2.0.html

Basically, this lets you pass SQL query strings to a database, provides 
convenient methods for retrieving the results, and miscellaneous functions 
for controlling such things as commit/rollback.  The particular 
implementation that worked for me is a package called "psycopg".  For Debian 
users:

   apt-get install python2.2-psycopg.

Here's a sample session:

su postgres         # normally the postgres superuser, can create other users
createdb mydb       # somewhere to start
psql                # make sure everything worked
\q                  # out of here
python
>>> import psycopg
>>> db=psycopg.connect("dbname=mydb")
>>> cursor = db.cursor
>>> db.cursor().__methods__
>>> cursor.execute("CREATE table foo(bar int, zot date)")
>>> cursor.execute("INSERT INTO foo VALUES (123, '1/2/2002')")
>>> cursor.execute("INSERT INTO foo VALUES (456, '2/4/2002')")
>>> cursor.execute("select * from foo")
>>> data=cursor.fetchone()
>>> print data
>>> print cursor.fetchall()
>>>

(caveat: I haven't actually tried this example to make sure it works)

I found psycopg (psycho pig?) very nice to work with.  As far as complaints 
go, there is good support for result retrieval, but no support for data 
insertion - it seems, you just make up SQL strings containing the data and 
submit them.  This needs to be strengthened.  Good thing we have the source, 
right?

Note that the Python db interface does not tie you to SQL, however, all the 
SQL strings you have to write in order to get anything done certainly do tie 
you.  So, in my opinion this all has to be abstracted more, and every 
application should start off by doing that.  Oh well, the fact remains that 
this is an excellent place to get started, and Python with this package is a 
far more capable interactive interface to a db than, for instance, psql is, 
or a graphical database shell would be.

-- 
Daniel