"Martin Blais" <[EMAIL PROTECTED]> 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.
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.
We now return you to your normal schedule :-)
Paul.
--
The most effective way to get information from usenet is not to ask
a question; it is to post incorrect information. -- Aahz's Law
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
