[DB-SIG] DBI Commentary

[M.-A. Lemburg]

Timothy Grant wrote:
> 
> Hi all,
> 
> Thought I would take an item that I have been working on and commenting
> on on the main list and move it here in the hope of being more on-topic.
> Please forgive this newbie for jumping in on the deepend of a discussion
> that has probably been going on for a long time with many minds more
> familiar with the subject than I.
> 
> There has been some discussion on the main list regarding the
> retrieving  of column headings using DBI databases. As far as I can tell
> from the spec, associating column names with data retrieved from a
> SELECT does not happen automatically.
> 
> Thanks particularly go to Gordon, Aahz and Lance as well as a number of
> others in my journey to understanding DBI. Now that I think I understand
> it, I'd like to make some recommendations (As I said last night, my PSA
> membership form is on its way today so I can fully participate in the DB
> sig).
> 
> First, embedding SQL in Python, even using the """SQL""" method is
> really ugly especially while keeping <tye4>indentation</tye4> consistent
> and it get's phenomenally ugly if the queries get at all complex. I
> don't think there is a solution to this problem, and I would hazzard a
> guess that no matter what language is in use it would be equally ugly.
> That said, anything that can be done to keep the ugliness to a minimum
> would be a good thing.
> 
> Aahz made the very valid point that specifying columns in your query
> *guarantees* tuple return order. I had not fully appreciated that, and I
> have put that into use in a number of places. However, I have to agree
> with Lance when he says that "SELECT * FROM table" is not as often a bad
> thing as Aahz claimed. My reasons for that are far less technical than
> Lance's, my reasoning is just that it adds to the ugliness mentioned in
> the prior paragraph. Using "SELECT field1, field2, field3 FROM table"
> simply becomes *far* to cumbersome and difficult to read/debug if you
> get beyond five or so fields. If you are dealing with a large number of
> columns (my current project has one table with 33). Debugging an error
> becomes an exercise in frustration (especially when using the """SQL"""
> method as the interpreter points the error message at the first line of
> the multi-line execute statement.
> 
> Lance posted some very nice code to build dictionaries out of the
> results from a query...
> 
> dsc = cur.description
> l = len(dsc)
> lst = cur.fetchall()
> rtn = []
> for data in lst:
>     dict = {}
>     for i2 in range(l):
>         dict[string.lower(dsc[i2][0])] = data[i2]
>         rtn.append(dict)
> return rtn
> 
> It seems to me that the DBI spec *should* mandate that the results of a
> fetchXXX() return not a tuple, but a dictionary, or a list of
> dictionaries. The problem is that a change like this would break a large
> amount of existing code. So, I'm trying to think of an appropriate
> extension (perhaps a new argument to the fetchXXX()'s)

Too much breakage, I suppose. These enhancements can easily
be implemented in the database abstraction layer of your
application.

Note that doing this in general will yield some confusion:
there oftern are columns which do not refer to a table column,
e.g. return values of SQL functions. Databases usually create
some wild names for them, so you'd still get the data, but
it would be hard to address the column due to those "wild
names" not being standardised.

Note that Jim Fulton implemented a special result set type
for storing the return values. These types (being sequences
of sequences) are in line with the DB API while still providing
some additional functionality. Perhaps that's the way to go...

The DB SIG would have to provide a reference implementation
which could then be made part of the next DB API version.

> It is possible that I am missing something already specified that has
> not been implemented in my tools, but I have been unable to find it.

-- 
Marc-Andre Lemburg
______________________________________________________________________
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/