[DB-SIG] db module wrapper

Marc Colosimo mcolosimo at mitre.org
Thu Aug 19 16:05:46 CEST 2004


On Aug 19, 2004, at 9:36 AM, M.-A. Lemburg wrote:

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

The adaptor modules will deal with this just as they do now (psycopg, 
pgdb, and cx_Oracle and others), which is why I said the DB API. The 
developers for each of these modules already had to deal with this. I 
don't want to think about the insanity between quoting. And as you just 
pointed out, this is why I want to export it in the API. The execute 
query function is already  standardize, all I am asking for is a 
function that just returns the string and doesn't execute it.

sql = """Select foo from table where goo > %s and foo = %s"""
param1 = 10
param2 = "bar"
cursor.execute(sql, (param1, param2))			# quotes and executes sql 
statement

st = cursor.quoteparams(sql, (param1, param2))      	# returns the 
quoted sql statement , goo > 10 and foo = 'bar'
cursor.execute(st)							# executes already quoted sql statement

marc







More information about the DB-SIG mailing list