[DB-SIG] API 3.0 limiting paramstyle to ['named', 'qmark'] is okay. ('format' is not desirable)

[M.-A. Lemburg]

On 18.05.2013 03:29, Daniele Varrazzo wrote:
> On Fri, May 17, 2013 at 5:57 PM, M.-A. Lemburg <mal at egenix.com> wrote:
>>
>>> Reading back the upstream thread I also see proposal to leave support
>>> for all the current placeholders but mandate drivers to implement
>>> qmark and named too. I think this is only going to complicate the
>>> implementation of the drivers and of the programs using it.
>>
>> Why is that ?
> 
> I'll try to explain that again. Let's say I work in a postgres-only
> shop: currently there is no interoperability problems, and people
> writes python-postgres programs and py-pg libraries. Let's say
> psycopg3 (implementing dbapi3) implements a method to switch
> paramstyle, whatever it is.
> 
> Alice is writing a script program, reading the database, doing some
> business-related math and generating a report. Of course the business
> model is already implemented in the well tested and uniformly used
> "shoplib" library, written by Bob: this library takes a psycopg2
> connection and runs whatever complicated query they need to implement
> such model.
> 
> In dbapi2 this is a piece of cake. In dbapi3 Alice is free to have her
> opinions: she is hip and dbapi3 is all the rage, so she will create a
> psycopg3 pyformat connection and pass that to the shoplib: of course
> the program will crash and burn immediately. Alice may point out that
> Bob is not providing complete support to a valid psycopg connection.
>
> There are two possible outcomes: 1. most likely Alice will be laughed
> at and the shop will assume a policy of not using different paramstyle
> than pyformat, hence making impossible to use libraries or frameworks
> (sqlalchemy, django) that would of course benefit of the uniformly
> used named format;

Sticking with the DB-API 2.0 way of doing things is always
an option, yes.

I don't think we should change the paramstyle default of
database modules to avoid excessive breakage.

> 2. Alice is good enough into talking everybody that
> dbapi3 is the future: now Bob in order to offer the shoplib service to
> psycopg connection in either mode will have to jump through hoops: he
> will either store the paramstyle of the connection received in a
> temporary variable, switch it to pyformat and finally restore it (with
> all the concurrency problems this would involve, as the connection may
> be shared across threads); or he will have to dynamically generate
> queries that would have otherwise been expressed as string constants.

Bob's library would have to be updated
to also handle connections that have a different .paramstyle
setting, but the change is simple: he'd only have to make
sure that when creating a cursor, he sets the cursor.paramstyle
to whatever his library supports.

There are no concurrency problems, as far as I can see.

> This is the reason why I firmly believe that giving excessive freedom
> to the users when designing a standard only means troubles not only
> for who implements the service (the dbapi driver in our case) that has
> to implement several equivalent options, but also for the users of
> such driver, because writing a function library accepting connections
> that may demand different parameter style is much more complicated
> than one where you would just have done with a string, and offers new
> and unexpected modes of failure. Doing that only to second stylistic
> matters would be just irresponsible.

Well, so far, we have given database module authors excessive freedom.
DB-API 3.0 would move that freedom on to the users to make their
lives easier :-)

> If we are designing a standard we have to make decisions: every
> decision we don't make and leave to somebody else only increases the
> complexity, doesn't definitely add democracy and peace in the world.
> And open choices don't increment complexity in additive way: they do
> it rather in multiplicative way.
> 
> My thoughts about the specific issue in this thread (not my wishlist
> for dbapi3, with which I'll be glad to bore you to death in another
> thread) is that
> 
> 1. demanding a single paramstyle for the driver will only make
> millions of people angry
> 
> 2. making the paramstyle switchable will result in programs more
> complicated, libraries *way* more complicated and database drivers
> *way, way* more complicated, inevitably causing bugs on either side,
> making millions of people angry
> 
> 3. leaving the things as they are w.r.t. the paramstyle (and fix the
> several shortcomings that dbapi 1 and 2 have shown in the about 10
> years they have been around) would make a dbapi3 more gradually
> acceptable and not a watershed event. 

Fact is, we already have adjustable .paramstyles now in DB-API 2.0
modules. What we're trying to do here is standardize this a bit
more, so that users can benefit from having multiple .paramstyles
available.

I don't think that you often run into a situation where you
pass around database connection objects between libraries.
If you do, those libraries will usually either be written for
a single backend, or have knowledge of a few select databases
and database modules. In the first case, they will adapt
to whatever is standard for the used database module,
in the second case, the library can be simplified to only
have to deal with two paramstyles.

Indeed, I believe that simplifying the paramstyle story
will make libraries that work directly on connection objects
easier to implement.

If you look at JDBC, we're actually converging to an industry
standard here :-)

JDBC used to only support qmark style (since it built on the
ODBC standard which mandates qmark style). In JDBC 3.0 they
added named style as additional option. I can't say why,
but from my experience with qmark style, named style does have
its advantages when dealing with SQL statements that have
lots of parameters, say e.g. INSERTs.

So JDBC now has two paramstyles.

The DB-API has had 5 paramstyles for many many years, and
we're now trying to simplify this in a backwards compatible
way to encourage use of only two paramstyles.

> Oh, yes, and there was 4. "have the driver automatically detect the
> paramstyle". Please, don't joke: this idea is so brittle it's not even
> funny to demand it as part of a standard.

Agreed :-)

>> I think it would greatly simplify the use of the DB-API, since
>> applications could then be written to use a single paramstyle,
>> even when using multiple database modules.
> 
> Come on, you are a database expert: do you really believe that because
> two commands can be written with the same paramstyle it would be
> possible to send the same query to different databases? With so
> different sql syntaxes, so different data models, so different runtime
> models? I don't think interoperability across database is a problem
> that can be solved at dbapi level: the aim of the dbapi (and the
> benefit it has provided in its not short life) is to provide
> uniformity in the python code to access a database; I don't think it
> being a database abstraction layer has ever been its scope.

I was only referring to the parameter binding logic, not
the SQL or data types involved.

With adjustable paramstyles, people can greatly simplify
their porting efforts when moving from one database module
to another.

E.g. take the reasoning why we added "named"
paramstyle support to mxODBC in release 3.2: we had requests
from customers who wanted to switch from a native Oracle driver
to the Oracle Instant Client ODBC driver. Instead of having them
rewrite thousands of lines of code to adapt
from Oracle named to qmark paramstyle used in ODBC, we added
automatic conversion and they are now able to run (mostly)
the same code using the Oracle ODBC driver.

-- 
Marc-Andre Lemburg
eGenix.com

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

::::: 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/