[DB-SIG] DBI Commentary

[Timothy Grant]

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)

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.

-- 
Stand Fast,
    tjg.

Chief Technology Officer              tjg@exceptionalminds.com
Red Hat Certified Engineer            www.exceptionalminds.com
Avalon Technology Group, Inc.                   (503) 246-3630
>>>>>>>>>>>>EXCEPTIONAL MINDS, INNOVATIVE PRODUCTS<<<<<<<<<<<<