[DB-SIG] Prepared statements in python drivers

[M.-A. Lemburg]

Hi Daniele,

On 24.03.2014 17:53, Daniele Varrazzo wrote:
> Hello,
> 
> lately there has been some interest in adding prepared statements
> support in Psycopg. It's a feature of which I see the usefulness but
> which I haven't used extensively enough to make my mind about be the
> best interface to present it to the driver clients.
> 
> A toy prototype that hasn't lead to a great deal of discussion is
> described in this article:
> <http://initd.org/psycopg/articles/2012/10/01/prepared-statements-psycopg/>.
> This implementation is explicit: the cursor has a prepare() method and
> execute can be run without the statement, only with the parameters,
> which would call the prepared statements.

In mxODBC we use the following approach, which is based on the
fact that cursor.execute*() methods may cache the command
argument to enhance performance:

cursor.prepare(command)
  prepare the command and set cursor.command to command

cursor.command
  last executed/prepared command

You then use this as follows, without having to change the DB-API
.execute*() method signatures:

cursor.prepare('select * from mytable where x = ?')
cursor.execute(cursor.command, [1])
results = cursor.fetchall()

> Other implementations are possible of course. A cursor may prepare
> automatically a statement and then execute it, but this adds a network
> roundtrip, uses more server resources and can lead to suboptimal plans
> (because the planner doesn't know the parameter so can't decide about
> a filter selectivity etc.). A cursor may support a single prepared
> statement or many, in which case a cache invalidation policy could be
> needed etc.
> 
> I was wondering a few things:
> 
> - is there enough consensus - not only in the Python world - about how
> to implement a prepared statements interface on a db driver?

It's a standard approach in the ODBC world, so should be wide-spread
enough as concept.

> - do other Python drivers implement stored procedures? Do they do it
> in a uniform way?

Hmm, what do stored procedures have to do with this ?

> - is the topic generic enough for the DB-SIG to suggest a DB-API
> interface or is it too database specific and the interface would be
> better left to the single driver?

We could add a standard extension for supporting a separate
prepare step.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Mar 24 2014)
>>> Python Projects, Consulting and Support ...   http://www.egenix.com/
>>> mxODBC.Zope/Plone.Database.Adapter ...       http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2014-03-29: PythonCamp 2014, Cologne, Germany ...           5 days to go
2014-04-09: PyCon 2014, Montreal, Canada ...               16 days to go
2014-04-29: Python Meeting Duesseldorf ...                 36 days to go

   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/