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

[John Kennedy]

Dearest DBAPI authors,

I have lurked with great interest on this board for quite some time, as the
topics are generally near and dear to my heart.  I believe I can say that I
have used nearly all of your drivers over the last ten years.  I have
purchased Marc's products at least a dozen times on behalf of my customers,
and I will generally favor a python DBAPI solution to the data
transportation issue du jeur if one is available.

I'm not sure that my opinion is even worthy of consideration in this
discussion, but I offer it nonetheless.

My company specializes in the development of interfaces and "glue"
applications.  These are typically in the form of either a web or desktop
application, or a background service that serves to automate, merge, and
facilitate the presentation of various disparate internal systems into a
more or less seamless facade.  Python is our tool of choice because it
allows us to construct our systems using tried-and-true modular design
patterns, quickly, and without unnecessary licensing or technical
restrictions.  Our projects range in size from several hours of development
to 1000 hours or so, with the typical project being best expressed in terms
of days or a few weeks.

As such, over the years our internal subversion repository has several
thousand one-off tools developed with DBAPI in all different environments,
primarily for data munging and conversion, and perhaps 40 or 50 "systems"
that are deployed throughout our base of customers.

I have developers ranging from young and inexperienced to old and wise on
my team.  My present concerns revolve around adopting internal standards
that maximize the value of any code we write.  Naturally, maximizing the
opportunity for reuse of both techniques and entire code modules is
important.

This may not be the case throughout your user base, but in my case I am
faced with the fact that human time is expensive, and machine time is
cheap.  So all of my effort is in the ongoing net reduction of human time,
thereby increasing the value to my customers.

It is in this context that I perceive 100% of the value of even having such
a thing as DBAPI.  With all due respect to the many years of effort that
many of you have spent to assist me and people like me, I must confess that
in my case the DBAPI itself is only as valuable as its ability to allow me
to supplant the underlying database with minimal impact on my code.
 Perhaps I am in a fortunate niche, because I am frequently able to get by
with a very minimal subset of standard SQL, and minimal use of database
specific features.  This is by choice, so as to maximize my ability to
handle the things that are frequently outside of my control such as
wholesale change in one or more of the domain specific systems that I need
to integrate with.

All of you, as developers of DBAPI compliant modules, have undertaken to
supply me with some level of consistency across what can only be described
as an exceedingly non-standard set of underlying databases.  The harsh
facts revolve around what you cannot offer me:

- any sort of consistency of data types, particularly numeric types
- very little in terms of help, despite your efforts, with metadata
- any ability to unify the various forms of fully qualified table references
- any ability to get consistent locking behavior
- any ability to get consistent query cache behavior
- any ability to have reusable stored procedures
- any ability to NOT have SQL and its inherent impedance mismatches in my
code
- a fixed point data type with speed comparable to floats

But if not the hope for some sort of database independence, the only thing
it seems DBAPI gives me to date is that I will have a module, a connection
object, a cursor object with some execute methods, and a description of
fields in my cursor for which I can only really count on the field name
being consistently available.

Through no fault of any of yours, I cannot even find a reasonable way to
write routines that do datetime manipulation in a reusable fashion, because
I don't know ahead of time whether I will be using an mxDateTime (which I
consider to be unfortunately superior as well as non-standard) or a python
datetime, or a java.util.date or a java.sql.date, or a CLR System.DateTime.

>From my humble perspective Daniele made the most salient point of the
discussion in reference to optional behavior making things more difficult
for everyone rather than simpler.  I believe that in reference to "standard
behavior" such as proposed behavior for DBAPI this is true.  The purpose of
a standard should be to standardize.

As such, I fully support the mandate that DBAPI have SOME SORT of standard
parameter substitution behavior that I can rely upon consistently --
because selfishly I know this will inherently increase the value of any
code for which I am able to develop to the standard.

The unfortunate reality is that any application developer who competently
deals with real life data where it lives can easily code around any
differences in your drivers.  I have a humorous set of wrappers, proxies,
and custom modules for this purpose.

So humbly, I suggest perhaps categorizing users along the classification of
whether or not their intent is relative database independence.  For those
who are using your driver merely because it is the de-facto tool one must
use to get access to the database, sadly I believe that DBAPI itself is
just another layer of throwaway middleware -- and more likely to stop me,
as an app developer, from utilizing the special feature I need, than to
help me.

But from the perspective of a user who could benefit a great deal from
database independence, the DBAPI is my only hope.

As to WHICH paramstyle is best, my opinion is nearly worthless, but I will
give it anyway.  Again, humbly, I prefer qmark.  My reasoning for this is
that I find myself doing a large amount of string manipulation in my code,
and python is quite powerful in this respect.  It would be trivial (albeit
spectacularly time consuming) for me to adjust all of my code to NEVER need
to pass parameters to a DBAPI method call.  However, a large subset of my
code set involves translations from another language or environment, and
these are most frequently ODBC.

The real reason I use parameters is in the hope that the SQL engine will
prepare my statement and reduce my round trip time.  But in reality in most
environments there are either better alternatives (i.e. inserts and updates
with a list of parameters instead of a loop with single set of parameters),
or the gain is trivial (i.e. a query cache is used anyway, or I am
front-ending with memcache because ALL DB access is too slow).

The qmark, being positional, allows me to write the most abstract form of
statements, and at least makes it clear in my code which "layer" is
performing the substitution.

Again I wish to express my gratitude for all of you in this sometimes
thankless task!

Your Fan,
J. Kennedy
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20130519/7924b0b8/attachment-0001.html>