[DB-SIG] Improved support for prepared SQL statements

[Tony Locke]

Mike, I agree. You quote:

"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.”

This is basically what pg8000 does but I've found you need a couple of
extra rules in practice:

* Any statements changing the structure of the database should cause the
cache of prepared statements to be invalidated.

* The key for the cache needs to include the parameter types as well as the
query.

Regards,

Tony.
On 21 Dec 2014 18:40, "Michael Bayer" <mike_mp at zzzcomputing.com> wrote:

>
>
> 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?
>
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> https://mail.python.org/mailman/listinfo/db-sig
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20141221/2b7745d2/attachment.html>