[DB-SIG] db module wrapper

[M.-A. Lemburg]

Marc Colosimo wrote:
> 
> 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.

How would you standardize this parameter ?

There are way too many different quoting rules out these - MySQL is
probably the worst example of them all, mostly because they
threw in all kinds of quoting and commenting rules extending
way beyond the SQL standard.

Note that standard SQL quoting only knows about using double
single quotes to quote a single quote, e.g. '' comes out as
literal ' in string literals. Same for comments: only '--' is
accepted as comment.

Things get even more complicated when you start to think
about different ways to write float literals, date and
time.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Aug 19 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::