[DB-SIG] First suggestion for db-api 3.0

[Vernon Cole]

James:
Yes, I read the PEP, and the FAQ. (Since I am a dbapi maintainer, I thought
it would be a good idea.) Lots of the things that have been wished for in
the past were rejected because they would be hard for the api writer to
implement. BDFL has suggested that doing things the easy way for
implementers may not be the most pythonic answer. We (tool writers) are
supposed to make things easy for the user, not for ourselves. For example, I
think that the idea of telling the user which of four different types of
parameter markers your api is using, so that he can code his program four
different ways, is the height of laziness.  On the other hand, if the user
was SETTING .paramstyle to tell ME which style HE is using, then such an
attribute would be a good thing. I would like to implement that. But as long
as my new feature were an "extension" and not part of the standard, who
would use it? That is why we need an updated standard IMHO.

As to the two 'impossible' problems you quote:
 1) "Some databases don't support case-sensitive column names...". True. And
some operating systems don't support case-sensitive file names. So what?  If
a database designer includes two columns which have names differing only in
letter case, he should be shot, not catered to. Make the dbapi standard case
insensitve (like Internet urls) and be done with it.
  2) "...databases usually generate names ... in a very database specific
way." So if you want to control what name your db returns for a function,
you need to use an "AS" clause in your SQL.  My documentation says that "as"
is implemented as a standard in the Entry level of SQL-92. It should be
pretty much universal.

  Doesn't sound 'impossible' to me. If it is 'impossible', then how do those
'several existing tools' do it?

Now let me show you a simple case where you DO need something other than
counting columns. (I am including the code as an attachment, for those who
want to read it.) Snip.py opens Microsoft Active Directory as a table and
returns a row for each user. Note in the result below that the order of the
columns IS NOT the same as the order in the query!

(configuration for the following: Windows XP, Active Directory 2003, Python
2.5.2, adodbapi 2.2.1)

H:\Python>snip.py
Executing the command: "select title, displayName, sAMAccountName,
givenName, adsPath from 'LDAP://dc=wc,dc=peppermillcas,dc=com' where
objectCategory = 'User'"

result data description is:
(NAME TypeCd DispSize IntrnlSz Prec Scale Null?)
(u'adsPath', 202, 108, 256, 255, 255, False)
(u'givenName', 202, 0, 256, 255, 255, False)
(u'sAMAccountName', 202, 10, 256, 255, 255, False)
(u'displayName', 202, 0, 256, 255, 255, False)
(u'title', 202, 0, 256, 255, 255, False)

The column names are exactly what I asked for, but
the order is all wrong. If I asked for row[1] I will get the wrong thing. If
I could ask for row.displayName it would be correct.

After all is said and done, why should it be that I can say
"row.displayName" in COBOL, but not in Python?

(How can ANYTHING be worse than COBOL?)
--
Vernon Cole
former COBOL compiler tester.

On Tue, Sep 23, 2008 at 1:15 AM, James Henstridge <james at jamesh.id.au>wrote:

> 2008/9/22 Vernon Cole <vernondcole at gmail.com>:
> > Dear Pythonaholics:
> >
> > I have not been following the development of Python 2.6 and 3.0, so the
> > following took me by surprise when I read it this morning. It seems to me
> on
> > first glance, that this new feature, "Named Tuple", is exactly what is
> > needed to make fields in database records (or columns in database rows if
> > you prefer) more accessible to a python programmer.
> >
> > From: Discussion of IronPython <users at lists.ironpython.com>...
> >
> > On Fri, Sep 19, 2008 at 6:26 AM, Michael Foord <
> fuzzyman at voidspace.org.uk>
> > wrote:
> > Hello all,
> >
> > At PyCon UK Raymond Hettinger showed off the Named Tuple; a very useful
> > recipe for creating tuples with named fields. It is becoming part of the
> > standard library in Python 2.6.
> >
> > http://code.activestate.com/recipes/500261/
> >
> >>>> from namedtuple import namedtuple
> >>>> thing = namedtuple('thing', ('whizz', 'pop'))
> >>>> thing
> > <class '__builtin__.thing'>
> >>>> i = thing(1, 2)
> >>>> i.whizz
> > 1
> >>>> i.pop
> > 2
> >>>> w, p = i
> >>>> w, p
> > (1, 2)
> >>>> i
> > thing(whizz=1, pop=2)
> >>>>
> >
> > I would like to suggest that we start the process of creating a dbapi 3.0
> > specification, and that the new spec define the returned data as an
> iterator
> > of Named Tuples.
>
> Note that there has been development on DB-API since the 2.0 release
> in the form of extensions listed at the end of the spec.
>
> There is already an optional extension for retrieving a result set
> using iterator protocol on the cursor, so that bit is already handled.
>  Having the results returned as named tuples could also be handled as
> an optional extension.
>
> As for making it part of the core specification, I think the question
> in the PEP's FAQ about returning dictionaries applies:
>
>    Question:
>
>       How can I construct a dictionary out of the tuples returned by
>       .fetch*():
>
>    Answer:
>
>       There are several existing tools available which provide
>       helpers for this task. Most of them use the approach of using
>       the column names defined in the cursor attribute .description
>       as basis for the keys in the row dictionary.
>
>       Note that the reason for not extending the DB API specification
>       to also support dictionary return values for the .fetch*()
>       methods is that this approach has several drawbacks:
>
>       * Some databases don't support case-sensitive column names or
>         auto-convert them to all lowercase or all uppercase
>         characters.
>
>       * Columns in the result set which are generated by the query
>         (e.g.  using SQL functions) don't map to table column names
>         and databases usually generate names for these columns in a
>         very database specific way.
>
>       As a result, accessing the columns through dictionary keys
>       varies between databases and makes writing portable code
>       impossible.
>
> So such an API may not be implementable on all databases, and may not
> give useful results on others.
>
> James.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20080924/53b99837/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: snip.py
Type: text/x-java
Size: 801 bytes
Desc: not available
URL: <http://mail.python.org/pipermail/db-sig/attachments/20080924/53b99837/attachment.java>