[DB-SIG] db module wrapper

M.-A. Lemburg mal at egenix.com
Thu Aug 19 17:57:59 CEST 2004

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

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 ! ::::

More information about the DB-SIG mailing list