[DB-SIG] Python database API 2.0/returning Column names with data

Greg Stein gstein@lyra.org
Mon, 5 Nov 2001 23:18:17 -0800

On Fri, Nov 02, 2001 at 05:10:38PM -0500, David Bolen wrote:
>     result = []
>     rows = cursor.fetchall()
>     for currow in rows:
>         rowdict = {}
>         for curcol in range(0,len(cursor.description)):
>             rowdict[cursor.description[curcol][0]] = currow[curcol]
>         result.append(rowdict)
>     return result
> 	  - - - - - - - - - - - - - - - - - - - - - - - - -
> This is a specific implementation that uses precisely the same case
> for the dictionary keys as that of the column names.

However, unlike dtuple.py, you end up scanning the *entire* list of records,
creating a dictionary for every row, and then appending that to a list
(which may have to be resized multiple times).

When I wrote dtuple umpteen million years ago, I had approached the problem
similarly to what you did above. In real life scenarios, it just blew.
Profiling the code showed the bottleneck was that loop. So dtuple was built
to have O(1) for creation, and defer the costly work to access time. And
even then, it doesn't create objects -- it just looks them up.

> My understanding for why something like this isn't part of the DB API
> is that there is or was debate about various implementation details
> (e.g., choices about case sensitivity of dictionary keys versus column
> names) so it was easier to leave it to something layered on top of the
> API rather than as part of the API proper.

There are a million choices in implementation. Various tradeoffs in space
and time, selections of algorithms, etc.

And the simple fact: it is *easy* to implement helpers like this in Python.
There is no reason to make each and every DBAPI provider implement this
stuff. Then deal with the eventual bugs and (therefore) variances in the
resulting API.

DBAPI stresses simplicity for the provider's implementation. The more
complex stuff is left where it should be: in the hands of the client and the
Python-level code.

DBAPI also avoids the fallacy that you can create a single API that can
cover *all* databases. That isn't possible, so it takes a middle road of
flexibility and simplicity. Providers can implement that spec and provider a
very useful tool to Python users. They can also go well beyond that to
provide capabilities specific to their database (mxODBC and MySQLdb both do
this very well), allowing Python developers to custom-tailer their apps to
take advantage of the unique features of those modules.


Greg Stein, http://www.lyra.org/