Dear folks,
Martin Blais wrote:
[snipped attribution to Paul Moore restored]
> Paul Moore wrote:
"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
I just looked at Martin's suggestions and I find some theoretical
problems. I had been left with the impression that the "operation"
given as an argument to the .execute() method was SQL but on rereading
the specification (PEP 249) I do not find that made explicit. If it
were explicitly required to be SQL, then I would turn to a text like "A
Guide to THE SQL STANDARD", Fourth Edition, by C.J.Date with Hugh
Darwen and quote from section 20.3, "STATEMENT PREPARATION AND
EXECUTION", in the subsection on "Placeholders":
Placeholders (i.e., question marks) are permitted only where
literals are permitted.
Note in particular, therefore, that they cannot be used to represent
names (of tables,
columns, etc.). ...
The DBMS I am coding for requires all table names and column names be
explicit in the query before it can begin to process it, where as the
"proper" placeholders in query are never really filled in, the query
picks up the values to use at execution time. This would require
distinctly different treatment of the two placeholders in query like:
select Name from ? where City = ?
(and I dread having to parse the SQL in the interface to distinguish
between these two).
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.
My interface works similar to this one.
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.
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
Thank you all,
Arthur Protin
|
