[DB-SIG] Improved support for prepared SQL statements

[M.-A. Lemburg]

On 21.12.2014 20:56, Michael Bayer wrote:
> 
> 
> M.-A. Lemburg <mal at egenix.com> wrote:
> 
>>
>> Whether or not a cursor is client or server side depends on many
>> things and is generally database backend specific. MS SQL Server
>> for example will default to server side cursors and revert to
>> client side for some special cases:
>>
>> http://msdn.microsoft.com/en-us/library/ms131331.aspx
> 
> this is why I’m so troubled by the concept that a user of the DBAPI needs to
> be aware of this backend-specific issue as a guide as to whether or not the
> complicated and itself-expensive pooling of cursors on the application side
> is necessary in order to gain performance (performance gains that need to be
> greater than the performance lost by the complexity of pooling the cursors
> behind some facade); not just at the per-database level, but at the use-case
> level, as you state here with SQL Server’s driver choosing server- or
> client- side cursors based on different conditions. I would much prefer that
> DBAPIs handle the optimization of this underlying backend detail for us. It
> is too low-level an issue to be exposed in a Python API as a normal matter
> of course. 

This is hardly something a Python DB-API module could manage or
abstract away. As you can see in the above article, the database itself
decides which variant to use. AFAIK, the driver does not provide a way to
tell the database to use one or the other.

> One of the jobs of SQLAlchemy is to try to abstract over issues like this in
> the DBAPI, as the DBAPI makes so many of them apparent; the six parameter
> formats, the totally idiosyncratic behavior of unicode objects, the many
> ways to set up isolation levels, the many ways to set up autocommit, the
> great lack of common, specific datatypes, etc. I’d like there to be a DBAPI
> someday that tries to reduce the number of idiosyncrasies exposed in favor
> of the drivers themselves using standard, documented techniques to handle
> them behind the scenes.

Some of those things should be standardized, yes, but please do
note that the DB-API is still meant as API guideline, which is
meant to be adaptable to database features.

Database module authors are free to add features to their module.
We'll then have a look every now and then and if several modules
implement similar things, discuss a standard way of doing this.

Some things from your list which we should tackle in the coming months
(using new threads :-)):

 * standard way of enabling/disabled auto-commit

 * standard way to set transaction isolation level

 * reduce the number of parameter formats

 * standard way to support Unicode for parameters and
   statements

I think the first two can be done as standard optional
DB-API extensions.

The last two will likely have to put into DB-API 3.0,
since they may require changes to database module
default behavior or additional support.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Dec 21 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/