Python Cookbook

Generating a Dictionary Mapping from Field Names to Column Numbers

Credit: Tom Jenkins

Problem

You want to access data fetched from a DB API cursor object, but you want to access the columns by field name, not by number.

Solution

Accessing columns within a set of database-fetched rows by column index is neither readable nor robust if columns are ever reordered. This recipe exploits the description attribute of Python DB API’s cursor objects to build a dictionary that maps column names to index values, so you can use cursor_row[field_dict[fieldname]] to get the value of a named column:

def fields(cursor):
    """ Given a DB API 2.0 cursor object that has been executed, returns
    a dictionary that maps each field name to a column index; 0 and up. """
    results = {}
    column = 0
    for d in cursor.description:
        results[d[0]] = column
        column = column + 1

    return results

Discussion

When you get a set of rows from a call to:

cursor.fetch{one, many, all}

it is often helpful to be able to access a specific column in a row by the field name and not by the column number. This recipe shows a function that takes a DB API 2.0 cursor object and returns a dictionary with column numbers keyed to field names.

Here’s a usage example (assuming you put this recipe’s code in a module that you call dbutils.py somewhere on your sys.path):

>>> 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

See Also

Recipe 8.10 for a slicker and more elaborate approach to the same task.