[DB-SIG] Improved support for prepared SQL statements

[Michael Bayer]

M.-A. Lemburg <mal at egenix.com> wrote:

> 
> My experience is from working with ODBC and ODBC drivers.
> IBM DB2 and MS SQL Server use ODBC as their native database
> interface API. In ODBC, cursors are called "statements" and
> you have two modes of operation:
> 
> a) direct execution, which sends the SQL straight to the
>   database
> 
> b) prepare + execute, which separates the prepare from the
>   execute step
> 
> You can configure both connections and cursors in ODBC,
> e.g. the cursor type setting is configured on a per
> cursor basis and this has direct influence on what locking
> mechanisms need to be enabled in the database to run the
> SQL statement:
> 
> http://msdn.microsoft.com/en-us/library/ms712631%28v=vs.85%29.aspx
> 
> Note that the DB-API is modeled in many aspects after the ODBC
> API and its concepts, since ODBC is an industry standard and
> provides a good common denominator for how database APIs work
> and which concepts they can support.

/rant-on

In general, while ODBC is a good enough standard to serve as the basis for
general concepts, I hope it doesn’t remain the final arbiter for most/all
design decisions in the DBAPI, which I hope to see progress at some point to
even a DBAPI 3. ODBC, while an industry standard, IMHO is slowly becoming a
relic of the commercial closed-source software industry, of which Python
itself decidedly stands in contrast towards as a key member of open source
ecosystem. ODBC is of course widely used for Microsoft SQL Server, the
commercial database for which it was ultimately designed for, but for the
ever-more-popular open source databases such as Postgresql and MySQL, while
ODBC drivers certainly exist, their use is almost unheard of in the
non-Windows community. I’ve worked heavily with Oracle with Java and Perl
starting in the 1990s, and even then, ODBC use on non-windows platforms was
unheard of (specifically with Java we had started with the JDBC-ODBC bridge
which was terribly unstable, and long ago was trumped by the type-4 drivers
for JDBC/Oracle). Even the SQL Server and DB2 shops that I work with, again
because they are hosting their applications from a linux platform, don’t use
ODBC either, on Python they instead favor the pymssql driver and IBM’s own
non-ODBC-based IBM-DB driver (https://code.google.com/p/ibm-db/wiki/README).
ODBC is definitely a standard, but in my experience it’s a stogy old
standard created by and dominated by closed source solutions, and is
generally the last choice of the open source developer as specific open
source drivers are invariably always very difficult and buggy to deal with,
even hiding behind the DBAPI. IMO it does tend to expose a little too much
of underlying mechanics, as it is an API that is largely a product of the
80’s and 90’s before higher-level VM-based and/or scripting languages had
achieved wide dominance. To the degree that the DBAPI has already drawn upon
it is fine, but I would be concerned if the DBAPI is to continue to attempt
to mold itself as an ongoing ODBC-compatibility layer going forward. 

/rant-off

However, getting back to the issue of prepared statements, I will note that
specific to MySQL, MySQL’s own DBAPI driver, MySQL-connector-Python, does in
fact tie the prepared statement to the cursor:
http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html,
most expediently as a flag to the connection.cursor() call, “cursor =
connection.cursor(prepared=True)”, which is a shortcut for using their
CursorPrepared cursor class.     

I actually find MySQL-connector’s API quite appealing here: "The first time
you pass a statement to the cursor's execute() method, it prepares the
statement. For subsequent invocations of execute(), the preparation phase is
skipped if the statement is the same.”

That is, the cursor establishes that it will use prepared statements in the
background, and as long as you pass the same string each time, it uses the
same prepared statement. The moment you send a different string, now a new
prepared statement is produced which is used specific to that string. This
eliminates the need for an awkward “prepare()” step and the introduction of
new and potentially more awkward APIs. Does that appeal to anyone else here?