[DB-SIG] db module wrapper

Marc Colosimo mcolosimo at mitre.org
Thu Aug 19 15:22:07 CEST 2004


On Aug 18, 2004, at 8:04 PM, Ian Bicking wrote:

> 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.
>

I would like to make the suggestion of adding something like 
quoteparams (the name in pgdb) to the DB API 3 spec. I have a hack 
which I use this directly to quote params when I write them out to a 
file to be loaded in later. By exporting this, the above wrapper can 
call that and, hopefully, get the correct result back no mater what the 
DB is.

Marc





More information about the DB-SIG mailing list