ActiveState Code

Recipe 52293: Generate field name to column number dictionary

When you get a set of rows from a cursor.fetch[one, many, all] call, it is sometimes helpful to be able to access a specific column in a row by the field name and not the column number. This function takes a DB API 2.0 cursor object and returns a dictionary with column numbers keyed to field names.

Python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
def fields(cursor):
    '''
    This fuction takes a DB API 2.0 cursor object that has been executed and returns a dictionary of the field names and column numbers.  Field names are the key, column numbers are the value.
    This lets you do a simple cursor_row[field_dict[fieldname]] to get the value of the column.
    Returns dictionary
    '''
    results = {}
    column = 0
    for d in cursor.description:
        results[d[0]] = column
        column = column + 1

    return results

Discussion

An example of usage would be as follows from a real system <grin>:

>>> c = conn.cursor()
>>> c.execute('select * from country_region_goal where crg_region_code is null')
>>> import pprint
>>> pp = pprint.pprint
>>> pp(c.description)
(('CRG_ID', 4, None, None, 10, 0, 0),
 ('CRG_PROGRAM_ID', 4, None, None, 10, 0, 1),
 ('CRG_FISCAL_YEAR', 12, None, None, 4, 0, 1),
 ('CRG_REGION_CODE', 12, None, None, 3, 0, 1),
 ('CRG_COUNTRY_CODE', 12, None, None, 2, 0, 1),
 ('CRG_GOAL_CODE', 12, None, None, 2, 0, 1),
 ('CRG_FUNDING_AMOUNT', 8, None, None, 15, 0, 1))
>>> import dbutils
>>> field_dict = dbutils.fields(c)
>>> pp(field_dict)
{'CRG_COUNTRY_CODE': 4,
 'CRG_FISCAL_YEAR': 2,
 'CRG_FUNDING_AMOUNT': 6,
 'CRG_GOAL_CODE': 5,
 'CRG_ID': 0,
 'CRG_PROGRAM_ID': 1,
 'CRG_REGION_CODE': 3}
>>> row = c.fetchone()
>>> pp(row)
(45, 3, '2000', None, 'HR', '26', 48509.0)
>>> ctry_code = row[field_dict['CRG_COUNTRY_CODE']]
>>> print ctry_code
HR
>>> fund = row[field_dict['CRG_FUNDING_AMOUNT']]
>>> print fund
48509.0
>>>

Comments

  1. 1. At 1:39 a.m. on 7 jun 2001, Steve Holden said:

    Script layout needs tweaking. In my browser (IE 5.5) the length of the docstring lines makes the page very wide. A nice idea, though it would be even nicer if database fields could be accessed as attributes (assuming legal Python names).

Sign in to comment