[DB-SIG] db module wrapper

[Ian Bicking]

Randall Smith wrote:
> I have created a db module wrapper for hiding the differences between 
> the DB API 2 implementations.  I currently testing it on psycopg and 
> cx_Oracle.  Some of the things I have done are:
> 
> * Standardize time objects to use Python datetime.datetime and 
> datetime.date classes for both input and output.  The module converts 
> between types transparently.  This is working on both psycopg and 
> cx_Oracle.

That sounds like a good idea.  It would be important that you can also 
make it uniformly use mxDateTime or Zope's DateTime, as there are many 
legacy applications that expect those.  And I think there are some 
advantages of mxDateTime over datetime, though I don't know what they are.

It would be nice to have a more extendable way to decode and encode 
types.  Psycopg has this, but there's no standard.

> * Standardize the params input to use the ? operator and a list.  The 
> module translates input params and query into the one appropriate for 
> the  native module.  For example with cx_Oracle: "Select foo from table 
> where goo > ?" with params [myparam] converts to "Select foo from table 
> where goo > :var1" with params {'var1':myparam}.  This is working with 
> both psycopg and cx_Oracle.

Does this work with all the cases?  This can be hard, especially the 
(Postgres) cases:

SELECT * FROM questions WHERE answer = 'maybe?' AND user_id = ?
SELECT * FROM questions WHERE answer = '''maybe?''' AND user_id = ?
SELECT * FROM questions WHERE answer = '\'maybe?\'' AND user_id = ?
SELECT * FROM questions WHERE answer = '
maybe
?
' AND user_id = ?
SELECT * FROM "questions?" WHERE answer = 'maybe' AND user_id = ?


For MySQL support, you'd need to recognize double quotes in addition to 
single quotes, with the backquote for quoted columns.


> * In addition, I also used Kevin Jacob's db_row module to give access by 
> name to the rows returned from a query.
> 
> Features can be turned off.

That's good.  db_row is definitely nice if you are dealing directly with 
results, but if you have some wrapper it seems unnecessary.

> First of all, am I reinventing the wheel here?  Does a wrapper already 
> exist that hides API implementation differences?  Second, do you have 
> any suggestions for standardizing?  I used the ? operator for params. Is 
> there a better option?  How do you feel about using the datetime 
> module?  I know it is not available in earlier versions of Python, but I 
> want to stay in the Python standard library if possible.  I would love 
> feedback.

Those are all good features, and there's definitely a desire for this 
out there -- people bring this up both on this list and elsewhere. 
There's other people that have various solutions, but nothing that seems 
to be actively released, promoted, and maintained.

The other big issue is exceptions, so that you can catch an exception 
(e.g, ProgrammingError) no matter which database it came from.  People 
have mentioned low-impact ways of doing this, but I don't know the 
mechanics.  It would certainly fit with the rest of what you are doing.

   Ian