[DB-SIG] Prepared statements in python drivers

[Vernon D. Cole]

Let me (as the maintainer of a competing product) be the one to say this:

If prepared statements were to become standardized (either by an extension
of PEP-249 or by a new Python3 compatible PEP) it should be simply a
codification of the existing API used by mxodbc.  (see
mxODBC.pdf<http://www.egenix.com/products/python/mxODBC/mxODBC.pdf>).
It is a good system, well thought out, simple, proven, and already
implemented for numerous databases.

Here is a brief description:

A cursor "X" has a method "X.prepare()" which does nothing except create
side effects. The required side effect is to set an attribute "X.command"
which is simply a reference to its first positional argument.  It may
optionally also have the side effect of sending an appropriate query
preparation statement to its database engine.  An optional third side
effect is that it may also populate the "X.description" array (my
anticipated implementation will not do that).

If the first positional argument of a subsequent "X.execute()" statement
also refers to the same query string, then the package may use the prepared
statement to query the database.  If the strings are different, it merely
starts over as if no "X.prepare()" had been executed.

In other words, the following three code snippets will have exactly the
same effect (except for timing):

<code>
crsr = some_connection.cursor()

# prepared statement
crsr.prepare("select * from SomeSQLtable")
crsr.execute(crsr.command)

# the way we do it now
crsr.execute("select * from SomeSQLtable")

# an otherwise harmless waste of time
crsr.prepare("select * from ThisIsNotTheTableYouAreLookingForMoveAlong")
crsr.execute("select * from SomeSQLtable")
</code>

The only standard needed is: "If a .prepare() method is provided, it must
define .command, and set .description to either None, or a valid result
from the query."

<code>
# this would be a compliant driver snippet
class MySqlApi(object):
    def prepare(self, sql):
        self.command = sql
        self.description = None
</code>
--
Vernon Cole

On Mon, Apr 7, 2014 at 3:21 PM, Peter Eisentraut <peter_e at gmx.net> wrote:

> On 3/24/14, 12:53 PM, Daniele Varrazzo wrote:
> > - is there enough consensus - not only in the Python world - about how
> > to implement a prepared statements interface on a db driver?
>
> In other languages, there is often a parameter on the connection handle
> saying whether (the analogue of) a prepare+execute call should be only a
> parameter substitution or a full prepared statement.  I think this is
> due to the historical confusion between these aspects and therefore not
> a good idea for a new API.  In Python, adding an explicit prepare
> function and having that actually prepare in all cases sounds like a
> decent idea to me.
>
> _______________________________________________
> 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/20140408/f6a4a040/attachment.html>