Generating a Dictionary Mapping from Field Names to Column Numbers

Credit: Tom Jenkins


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.


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


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 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_ID': 0,
>>> row = c.fetchone(  )
>>> pp(row)
(45, 3, '2000', None, 'HR', '26', 48509.0)
>>> ctry_code = row[field_dict['CRG_COUNTRY_CODE']]
>>> print ctry_code
>>> fund = row[field_dict['CRG_FUNDING_AMOUNT']]
>>> print fund

See Also

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

Get Python Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.