[DB-SIG] First suggestion for db-api 3.0
james at jamesh.id.au
Tue Sep 23 09:15:02 CEST 2008
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>
> 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.
>>>> from namedtuple import namedtuple
>>>> thing = namedtuple('thing', ('whizz', 'pop'))
> <class '__builtin__.thing'>
>>>> i = thing(1, 2)
>>>> w, p = i
>>>> w, p
> (1, 2)
> 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:
How can I construct a dictionary out of the tuples returned by
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
* 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
So such an API may not be implementable on all databases, and may not
give useful results on others.
More information about the DB-SIG