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
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
Recipe 8.10 for a slicker and more elaborate approach to the same task.