[DB-SIG] db module wrapper

[Marc Colosimo]

On Aug 19, 2004, at 11:57 AM, M.-A. Lemburg wrote:

> Marc Colosimo wrote:
>>>> 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
>
> I don't understand why you'd want to access the quoted
> SQL string with embedded parameters.

Gee, the second line of my original post (see above)!

> After all binding
> parameters exist to make all this quoting unnecessary and
> indeed, many drivers pass the SQL string and the parameters
> well separated to the database.

not pgdb (_quoteparams) or cx_oracle (Cursor_SetBindVariables). I 
couldn't check psycopg because the links from freshmeat are dead.

> The database then builds
> an access path using the SQL string (with binding parameters)
> and runs the access path against the parameters that are
> specified as separate part of the request.
>
>
Marc