[DB-SIG] Improved support for prepared SQL statements

M.-A. Lemburg mal at egenix.com
Wed Dec 17 15:21:43 CET 2014

On 17.12.2014 14:50, SF Markus Elfring wrote:
>> How about:
>> """
>> The prepared operation is only available until the next call to
>> one of the .execute*() methods or another call to the .prepare()
>> method using a different command string than the one used for
>> preparing the previous operation.
>> """
> Can the prepare() method return a handle for a SQL statement?

The proposal says:

Return values are not defined.

which we traditionally use on the DB-API to mean: the method
may return something, but whatever it returns is database module
specific and not something to rely on on general.

> Will prepared SQL statements be mapped to a specific class hierarchy
> in Python?

Not sure what you mean. Having prepared a statement only
means that the cursor has run it by the database to build
an access plan and, as a side-effect, has most likely run
the parser on it.

In practice, a database module may implement this method
as mostly no-op operation. Some databases don't support prepared
statements or don't work well in this mode, other may not
run the parser and thus not necessarily raise errors during
the call (only in the subsequent .execute()).

The most likely case, however, is that you can use .prepare()
to check for errors (and hopefully, without causing the
transaction to be canceled, in case there are errors).

>> It is possible to open a few cursors, prepare frequently
>> used statements on them and them make them accessible via
>> a cursor pool. This works well (I know since I've implemented
>> such a logic some 14-15 years ago in a large application).
> How should the application programming interface look like
> in Python for this use case?

Depends on the application and what you want to achieve.
There's no general answer to this.

>> The DB-API standard extensions were added to address proliferation
>> of such additions. If possible, the database modules should try
>> to standardize on these extensions, which is why discussions such
>> as these are useful.
> Are there more software improvements in a waiting queue?

There are a few extensions that we've been discussing off and
on. The cursor.prepare() method is one of the more recent ones.

One of these days, I'll have to go through the archives and
collect a list :-)

Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, Dec 17 2014)
>>> Python Projects, Coaching and Consulting ...  http://www.egenix.com/
>>> mxODBC Plone/Zope Database Adapter ...       http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
2014-12-11: Released mxODBC Plone/Zope DA 2.2.0   http://egenix.com/go67

::::: Try our mxODBC.Connect Python Database Interface for free ! ::::::

   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

More information about the DB-SIG mailing list