[DB-SIG] Proposed improvements to DBAPI 2.0 Cursor.execute() method.

[Martin Blais]

> "Martin Blais" <blais at furius.ca> writes:
>
> > I want to propose a few improvements on the DBAPI 2.0 Cursor.execute()
> > method interface.  You can find the details of my proposed changes
> > here:
> > http://furius.ca/pubcode/pub/conf/common/lib/python/dbapiext.html
>
> The model of query execution you are assuming is nothing like that
> used by Oracle (in cx_Oracle in particular). You can certainly build
> up bits of a query string using Python string formatting - this is
> nothing to do with the DB API, but on the other hand, it is also
> *extremely* uncommon in my experience.
>
> However, you assume that the "second stage", of adding variable
> bindings supplied in the cursor.execute call, is also a string
> formatting exercise (just with automatic escaping). This is most
> certainly not the case in Oracle - the query is sent to the DB engine
> as given, with variable placeholders intact, and the variable bindings
> are sent independently.
>
> This is a crucial optimisation for Oracle - with the code
>
>    c.execute("select * from emp where id = :id", 100)
>    c.execute("select * from emp where id = :id", 200)
>
> the DB engine only sees a SINGLE query, run twice with different
> bindings. The query plan can be cached and optimised on this basis. If
> the ID was interpolated by Python, Oracle would see 2 different
> queries, and would need to re-parse and re-optimise for each.
>
> So, your proposal for unifying the 2 steps you see does not make sense
> in the context of (cx_)Oracle - the steps are utterly different.

I think you are mistaken (either that or I do not understand what you
mean, or perhaps
you haven't read the proposed code).  My proposal does not modify the
way the escaped
parameters are to be sent to the client interface.  In fact, the test
implementation merely rewrites the query to take advantage of the Pythonic
interface, with the exception that it may create :parameters if needed, for
example, if you pass in a list or a dict.



> Sorry for going on at such length, but I get twitchy every time I see
> people assume that parameter binding is simply a client-side string
> interpolation exercise. That approach is the reason that huge numbers
> of poorly written Visual Basic programs exist, which destroy
> performance on Oracle databases. (It's also the cause of many SQL
> injection attacks, but I don't want to make too much of that, as I'd
> be getting perilously close to spreading FUD without providing more
> detail than anyone would be able to stand :-)) I'd hate to see Python
> end up falling into the same trap.

I did not assume that at all.  The proposed test implementation should work fine
with cx_Oracle, i.e. will maintain :id in the string, only it will provide a
more flexible interface, for example, you could pass a list and it would create
the necessary :parameters to be sent to cx_Oracle.