Recipe7.13.Generating a Dictionary Mapping Field Names to Column Numbers

Recipe 7.13. Generating a Dictionary Mapping Field Names to Column Numbers

Credit: Thomas T. 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 not very readable, nor is it robust should columns ever get reordered in a rework of the database's schema (a rare event, but it does occasionally happen). 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 = {  }     for column, desc in enumerate(cursor.description):         results[desc[0]] = column     return results

Discussion

When you get a set of rows from a call to any of a cursor's various fetch . . . methods (fetchone, fetchmany, fetchall), it is often helpful to be able to access a specific column in a row by field name and not by column number. This recipe shows a function that takes a DB API 2.0 cursor object and returns a dictionary with column numbers keyed by 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 Python sys.path). You must start with conn being a connection object for any DB API 2-compliant Python module.

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

If you find accesses such as row[field_dict['CRG_COUNTRY_CODE']] to be still inelegant, you may want to get fancier and wrap the row as well as the dictionary of fields into an object allowing more elegant accessa simple example might be:

class neater(object):     def _ _init_ _(self, row, field_dict):         self.r = row         self.d = field_dict     def _ _getattr_ _(self, name):         try:             return self.r[self.d[name]]         except LookupError:             raise AttributeError

If this neater class was also in your dubtils module, you could then continue the preceding interactive snippet with, for example:

>>> row = dbutils.neater(row, field_dict) >>> print row.CRG_FUNDING_AMOUNT 48509.0

However, if you're tempted by such fancier approaches, I suggest that, rather than rolling your own, you have a look at the dbtuple module showcased in Recipe 7.14. Reusing good, solid, proven code is a much smarter approach than writing your own infrastructure.

Recipe7.13.Generating a Dictionary Mapping Field Names to Column Numbers