approach for assigning ODBC results to fieldnames as variables?

[Robin Thomas]

At 03:41 PM 2/27/01 +0000, fluxent at yahoo.com wrote:
>When you execute a SQL (select) query via ODBC and do a fetchall(),
>you get back a result set as a tuple (array). You can also ask for
>the cursor's description, which returns a separate tuple, the first
>column of which contains the fieldnames (or other labels calculated
>by SQL).

A common approach is to encapsulate the cursor so that the description is 
fetched once, and rows are turned into dictionaries with the values in 
description tuple used as keys.


>Is there a standard approach to assigning field names to columns, so
>that this output line could be changed to something like
>   Response.Write('<tr><td>%s<td>%s<td>%s<td><a
>href="matterEdit.asp?clientID=%s&matterID=%s">%s</a><td>%s<td>%
>s</tr>' % (maxDate, clientID, clientName, clientID, jobID, jobID,
>jobName, totalHours))

Dynamic name binding can be very problematic. Dictionaries give you the 
desired behavior without the name-binding mess. Especially when you use 
named placeholders in string formatting. A shortened example:

Response.Write('<tr><td>%(MAXDATE)s' % row_as_dictionary)

Be careful of the case of the column names returned by description().

>or even at least something like
>   Response.Write('<tr><td>%s<td>%s<td>%s<td><a href="matterEdit.asp?
>clientID=%s&matterID=%s">%s</a><td>%s<td>%s</tr>' % (maxDate[i],
>clientID[i], clientName[i], clientID[i], jobID[i], jobID[i], jobName
>[i], totalHours[i]))

Slicing the row tuples into new column value sequences is, happily, no 
faster than making dictionaries using the description tuple. Test script is 
below.

# script start
def get_description():
     return ('ONE', 'TWO', 'THREE', 'FOUR', 'FIVE')

def get_row():
     return (1,2,3,4,5)

def rows_as_dicts(rows, description):
     if not rows: return []
     length = len(rows[0])
     r = range(length)
     l = []
     for row in rows:
         d = {}
         for i in r:
             d[description[i]] = row[i]
         l.append(d)
     return l

def rows_as_columns(rows, description):
     if not rows: return []
     length = len(rows[0])
     r = range(length)
     l = []
     for i in r: l.append([])
     for row in rows:
         for i in r:
             l[i].append(row[i])
     return l

def test(funcs, rows=20, reps=1000):
     desc = get_description()
     rowlist = []
     for i in range(rows):
         rowlist.append(get_row())

     from time import time
     for func in funcs:
         n = func.__name__
         t = time()
         for i in range(reps):
             func(rowlist,desc)
         t = time() - t
         print "%s on %d rows: %s reps in %.04f s (%.05f s each)" % \
               (n, rows, reps, t, t/reps)

if __name__ == '__main__':
     for rowamt in (10,20,50,100):
         test( (rows_as_dicts, rows_as_columns), rowamt )

# output
rows_as_dicts on 10 rows: 1000 reps in 0.4665 s (0.00047 s each)
rows_as_columns on 10 rows: 1000 reps in 0.5636 s (0.00056 s each)
rows_as_dicts on 20 rows: 1000 reps in 0.9083 s (0.00091 s each)
rows_as_columns on 20 rows: 1000 reps in 1.0523 s (0.00105 s each)
rows_as_dicts on 50 rows: 1000 reps in 2.5213 s (0.00252 s each)
rows_as_columns on 50 rows: 1000 reps in 2.5186 s (0.00252 s each)
rows_as_dicts on 100 rows: 1000 reps in 4.5625 s (0.00456 s each)
rows_as_columns on 100 rows: 1000 reps in 5.0504 s (0.00505 s each)


--
Robin Thomas
Engineering
StarMedia Network, Inc.
robin.thomas at starmedia.net