[DB-SIG] Improved support for prepared SQL statements

[M.-A. Lemburg]

On 18.12.2014 16:59, Michael Bayer wrote:
> 
>> On Dec 18, 2014, at 5:39 AM, M.-A. Lemburg <mal at egenix.com> wrote:
>>
>> On 17.12.2014 19:13, INADA Naoki wrote:
>>> As I said before, prepared statement is normally bound to connection.
>>> So `.prepare()` method should be connection's method, not cursor's.
>>>
>>> prepared = conn.prepare("SELECT ?+?")
>>> ...
>>> cur = conn.cursor()
>>> cur.execute(prepared, (1, 2))
>>> cur.fetchone()  # returns (3,)
>>
>> I'm not sure which database you are talking about, but in terms
>> of concepts, statements are run on cursors, so adding the method
>> to connections doesn't look right (we dropped this logic when moving
>> from DB-API 1.0 to 2.0 a long time ago).
>>
>> Also note that the prepare step may need access to the
>> cursor configuration settings to be correctly interpreted
>> by the database.
> 
> oh, so are you saying that if one produces a prepared statement from a cursor, and then that cursor is closed, I have no option to re-use that prepared statement anywhere else, is that right?  

Yes, that's what happens if you close a cursor.

Note that cursors are normally kept open to run multiple statements
or multiple runs of the same statement.

> That would make the entire feature a non-starter for me.    SQLAlchemy doesn’t hold cursors open beyond a single statement.    My users would very much want a prepared-statement-per-transaction object.

Perhaps you ought to reconsider this approach. Creating and closing
cursors all the time does involve somewhat of an overhead.

> JDBC provides prepared statements as a service of the Connection.    I think DBAPI should be doing the same.

In order to make use of those prepared statements you will
have to create a cursor one way or another anyway, so I don't
see an advantage of having the method on connections.

It's possible for database modules to provide a .prepare()
method on connections, but since fewer databases provide this
functionality than having one on cursors, the DB-API should
go with what's easy for module authors to implement.

Note that the use of cached prepared cursors for performance
reasons is only one use of having the prepare step available
on cursors. In practice, the more important one is to be able
check SQL statements for errors without executing them and
possibly causing a rollback on the transaction.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 18 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
               http://www.egenix.com/company/contact/