Database abstraction seems nice in Python, but the pygresql module doesn't go far enough. Since it doesn't have a OOP interface (maybe I missed that somewhere in the docs?), I wrapper'ized it to make usage easier. I added two (important to me) things: automatically calling db.close() on object destruction, and creating an iterable database reply.

Granted, the use of atexit.register to hook object destruction seems really wrong to me, but I don't know of any __destroy__ method or equivalent that gets automatically setup like __init__ does.

Update: 2007-05-07
The variable argument execute() method now works. ;) The __del__ method noted in the comments does indeed work. I've updated the code to reflect. Thanks!

Update: 2007-05-10
Added map() method for easy inserts.

from pgdb import connect """ Ease of use wrapper for pgdb XXX pgdb uses a dictionary to pass in params to execute!!! """ class Database: def __init__(self, db='default'): self.db = connect(user='me', password='Zucker1984', database=db) self.c = self.db.cursor() def __del__(self): self.db.close() def execute(self, query, *args): if len(args) > 0: self.c.execute(query, args[0]) else: self.c.execute(query) def iter(self): while True: try: r = self.c.fetchone() except pgdb.DatabaseError: break if r is None: break yield r def insert(self, table, dictionary): # drop data easily into a table with auto-commit q = ["insert into ", table, " ( " ] q.append( ','.join( dictionary.keys() ) ) q.append(" ) values ( ") q.append( ','.join( [ " %%(%s)s " % k for k in dictionary.keys() ] ) ) q.append(" ) ") self.execute( ''.join(q), dictionary ) self.execute("commit") if __name__ == '__main__': db = Database() queries = [ "create temporary table test (i int) ", "insert into test values ( 1 ) ", "insert into test values ( 2 ) ", ] for q in queries: db.execute(q) db.execute("insert into test values ( %(i)s ) ", {'i' : 5} ) db.insert( "test", dict(i=6) ) db.execute("select * from test") for r in db.iter(): print r

Found this: Didn't try it myself, but it looks like __del__ might be what you want.
Yeah! __del__ will be very handy for handling destruction of database connections. MySQLdb doesn't seem too bad - it seems very python-y. But I still don't understand why there isn't a python library for abstracting databases in the core yet - something similar to DBI in perl (or PEAR::DB, or MDB, or MDB2, or Adodb in PHP ;). -Doug
"map" seems like a confusing name for that function. -- David W
Python developers decree that database libraries should conform to a spec Either developers conform or people look down on them. ;) This has the effect of a DBI interface, as above I just need to change the import clause. -- Patrick.
What would be a better name than map? -- Patrick.
Hmm... I was thinking about that. I would probably go with "insert" or "insert_dict". Of course, both of those involve more typing, but when I think of "map" I think of a function which applies another function to a list to a list and then returns the result. -- David W
I see your point, I was thinking map dict keys to columns. I might add object detection and support being passed a list object as well. insert() sounds good though. -- Patrick.