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/
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
