[DB-SIG] API suggestion: expose 'quote' method

[M.-A. Lemburg]

Magnus Lyckå wrote:
> cur.execute( *db.quote( "some SQL statement", parameters) )
> 
> Not that it would be of much use though...
> 
> Let's take a concrete example. Let's assume that we use either
> mxODBC or adodbapi on Windows, and connect to a System data source
> called "databaseX". It's just an ODBC source, and we don't know
> what kind of database it is. What do we care--it speaks ODBC.
> 
> Our SQL is "SELECT * FROM T WHERE D = ?" and we send in a single
> mxDateTime object as a parameter.
> 
> What these interfaces will actually do is to send the SQL string
> just as it is, ? and all, down to the ODBC level at least. It will
> convert the mxDateTime object to some suitable format for the
> platform, but it will never worry about how to get it into the SQL
> string, because it never will. After all, both Dynamic SQL and SQL
> CLI mandates that parameter passing with ? should work, so as far
> as I know, there will never be an SQL string with the date value
> stuffed in. The database server will read directly from the parameter
> list when is finds a ? in the SQL, instead of doing the labour of
> extracting a string literal that it perhaps needs to unquote and
> unescape. Seems like less work.

Even better: the database server can prepare the data access
paths from the abstract SQL statement and cache that access
path for subsequent queries -- that's hardly possible if you
have the arguments quoted and embedded directly into the
SQL. In the latter case, you'd also lose the DB-API feature
of caching the last used SQL statement on a database cursor.

Another argument against using embedded data types is that
of precision loss, e.g. ODBC interfaces C types directly
to the driver which assures that you don't lose precision
by converting them to a string representation. This happens
for Python floats, integers and even date/time values.

> If an interface is implemented in C or C++, it's not certain that
> the actual data type being passed to the next step can be described
> in Python at all? M-A? What about mxODBC for instance?

This depends on the support from the ODBC driver for querying
data types of input parameters: Most drivers can tell the interface
which data types they would like to see and mxODBC then converts
the Python types accordingly. Some drivers don't provide this
information, so mxODBC has to do an educated guess based on
the Python type passed in by the programmer -- if it doesn't
work, then the interface will generate an exception and the
programmer will have to adapt the program to pass in more
suitable types.

Quoting of data types only rarely becomes visible in the ODBC
layer interaction and most of the times you only need to
quote strings for e.g. table names etc. (note that these
are not string literals !).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Jun 04 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
EuroPython 2003, Charleroi, Belgium:                        20 days left