[DB-SIG] Prepared statements in python drivers

[M.-A. Lemburg]

On 26.03.2014 20:38, Tony Locke wrote:
> Hi Daniele, the latest release of pg8000 (1.9.7) has (experimental)
> support for prepared statements, but takes an implicit approach rather
> than an explicit one. In the connect() function there's a boolean
> use_cache parameter which tells pg8000 to cache prepared statements,
> keyed against the SQL query string. So when you do:
> 
> cursor.execute(sql_query, params)
> 
> it does a lookup on sql_query in a local cache, and executes the
> prepared statement if one is found, or creates and caches a new one if
> not. This implicit approach means that no extension to the DB-API is
> needed.

Hi Tony,

thanks for the feedback. Please note that one of the main reasons
for having a separate explicit API to prepare a statement is
to run the query parser on the statement (and prepare the query plan
on the server) without actually executing the statement.

This can be used to e.g. setup a pool of cursors with already
prepared statements for faster execution of commonly used
queries.

Your approach implements the standard caching mechanism
that is already documented in the DB-API. It only extend this
by caching not only the last statement, but a set of statements,
if I understand correctly.

The pool creation is also possible with this approach, but
only after the cursors were used at last once.

I don't think the use case is important enough to make .prepare()
a requirement in the DB-API, but it would be great if we could
come up with a standard extension definition.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Mar 27 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 ...           2 days to go
2014-04-09: PyCon 2014, Montreal, Canada ...               13 days to go
2014-04-29: Python Meeting Duesseldorf ...                 33 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/