[DB-SIG] Improved support for prepared SQL statements

Vernon D. Cole vernondcole at gmail.com
Thu Dec 18 20:37:10 CET 2014

1) There is already an established usual practice (or quasi-standard)
for prepared statements:
>>> sql_stmt = "some sql string"
>>> cursr.prepare(sql_stmt)
>>> cursr.execute(sql_stmt, args)  # note: the statement string must be identical
Any change in sql_stmt undoes the prepared status and the "execute"
method continues as if the "prepare" method had not been executed.

2)  At least two implementations enhance this by having the "prepare"
method store a reference to the sql statement as the read-only cursor
attribute "cursr.command". The api must store this reference, in order
to see whether the sql statement is changed, so making it available is
inexpensive. The calling sequence then becomes:
>>> cursr.prepare("some sql string")
>>> cursr.execute(cursr.command, args)
How can you get simpler, or easier to understand, than that? It is
also easy to implement, at least, it was on the platform I use (i.e.
Microsoft ADO).

3) If a different way of handling prepared statements is invented and
standardized, then those of us who have already implemented this
method will have to support both -- since we have existing customers
already using this one.

4) How much will prepared statements really help?  Published Microsoft
sources indicate that it will make no significant difference on their
databases. [One wonders whether they are already so slow that they
cannot get any worse?]  In my case, it saves some work in Python, such
as converting between format types -- but the actual database query is
just as inefficient both ways.

More information about the DB-SIG mailing list