Python Cookbook 2Nd Edition Jun 1002005 [Electronic resources] نسخه متنی

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.

See Also

Recipe 7.14 for a slicker
and more elaborate approach to a very similar task, facilitated by
reusing the third-party dbtuple module.