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

M.-A. Lemburg mal at egenix.com
Wed Sep 24 22:36:34 CEST 2008

On 2008-09-24 20:28, Vernon Cole wrote:
> 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. 

Feel free to do so. There has been a lot of discussion about these
parameter styles. The last round concluded that we should strip down
the number of possible styles to 1 or 2 ('?' and ':1' IIRC).

> 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.

The DB-API standard defines a set of features that all compatible
database modules must implement, plus a set of extensions that they
may implement, but don't have to or indeed may not be able to due to
the backends or interfaces not supporting e.g. two phase commit.

If your DB-API module can do more than the standard spec that's
perfectly fine. We created the set of standard extensions so that
those extensions are not implemented in 10 different ways.

Also note that the spec does indeed allow the module to return
namedtuples, but it doesn't require this. The spec deliberately
uses the term "sequence" instead of tuple or list.

Note that the implementation of namedtuples is not a particularly
nice one (they use dynamic programming). There's also a different
implementation available via the C API called structseq (this is used
e.g. by the time module).

Both create subclasses of the standard Python tuple and are
sequences, so both are permitted to be used by the DB-API to
represent rows.

However, using the Python version in an API that potentially
returns a few thousand rows is not exactly what I'd recommend
to do, since it will slow down the interface a lot.

Due to the nature of the namedtuples, you will also see many
different objects as row object (each query will return a
different object type).

> 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.
> ------------------------------------------------------------------------
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig

Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, Sep 24 2008)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX 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 mailing list