[DB-SIG] Improved support for prepared SQL statements
mal at egenix.com
Sun Dec 21 21:17:23 CET 2014
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:
> 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
I think the first two can be done as standard optional
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.
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
More information about the DB-SIG