[DB-SIG] db module wrapper
wnvcole at peppermillcas.com
Thu Aug 19 21:19:14 CEST 2004
Marc-Andre is right, as usual. The other reason we pass parameters to the
SQL engine, rather than just plug them in using a normal python string
substitution, is that the SQL server can run a repeated query faster (in
some cases MUCH faster) if we pass it parameters. If it were not for this
performance benefit, we could use the existing language tools to do the job.
The given example can be run in standard python as:
sql = """Select foo from table where goo > %d and foo = '%s'"""
param1 = 10
param2 = "bar"
cursor.execute(sql % (param1, param2))
and the requested feature done like:
st = sql % (param1, param2)
Okay, everybody knew that already, but I want to be sure we are all working
from the same page here...
The simple case like this is not the problem. The problem is that we are
using a languge designed by IBM as a human interface tool, and trying to
make it work as a data retrieval engine. SQL is clearly the wrong tool for
the job. Unfortunatly, it is the only tool we have. So lets examine the
Our computer holds lots of structures containing binary data of various
kinds. It is held in efficient internal representations. The SQL server
contains binary data of similar kinds, but the internal representation may
be different. Using the SQL language, in any of its various dialects,
demands that the API programmer must find a way to communicate between these
two binary representations using text strings. (I almost said ASCII strings,
but even THAT is not standard. DB2 uses EBCDIC strings and MsSQL uses
unicode!) The application programmer will pass in a control string and a
(possibly very complex) structure of binary data.
So Marc C. is right, also. We need a way -- if only for debugging and error
handling -- to reach in and retrieve the string which the API ultimatly
sends to the SQL engine to make the magic happen. (This string should not be
fed back into a query -- the SQL will be a different dialect from the API
input syntax.) Here is my suggestion for the way to specify that method:
<drum roll here...>
and for the raw result data of a query:
The double-underline syntax is intended to show that the returned result is
highly implementation dependent.
Everybody keep in mind the goal here. DbAPI 2.0 gave a unified way for a
programmer to deduce from the API how he could BUILD a query string for any
given engine. 3.0 should make it possible to write a moderatly complex
query -- in a standard syntax -- which will EXECUTE correctly on any SQL
engine. That's a different kind of animal entirely.
From: M.-A. Lemburg [mailto:mal at egenix.com]
Sent: Thursday, August 19, 2004 9:58 AM
To: Marc Colosimo
Cc: db-sig at python.org; Ian Bicking
Subject: Re: [DB-SIG] db module wrapper
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
[[ much text clipped out ]]
> 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
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.
More information about the DB-SIG