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: http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/146462. 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 http://www.python.org/dev/peps/pep-0249/ 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.