Welcome, guest | Sign In | My Account | Store | Cart

When you get a set of rows from a cursor.fetch[one, many, all] call, it is sometimes helpful to be able to access a specific column in a row by the field name and not the column number. This function takes a DB API 2.0 cursor object and returns a dictionary with column numbers keyed to field names.

Python, 13 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
def fields(cursor):
    '''
    This fuction takes a DB API 2.0 cursor object that has been executed and returns a dictionary of the field names and column numbers.  Field names are the key, column numbers are the value.
    This lets you do a simple cursor_row[field_dict[fieldname]] to get the value of the column.
    Returns dictionary
    '''
    results = {}
    column = 0
    for d in cursor.description:
        results[d[0]] = column
        column = column + 1

    return results       

An example of usage would be as follows from a real system <grin>:

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

1 comment

Steve Holden 20 years, 5 months ago  # | flag

Script layout needs tweaking. In my browser (IE 5.5) the length of the docstring lines makes the page very wide. A nice idea, though it would be even nicer if database fields could be accessed as attributes (assuming legal Python names).

Created by Tom Jenkins on Wed, 21 Mar 2001 (PSF)
Python recipes (4591)
Tom Jenkins's recipes (1)

Required Modules

  • (none specified)

Other Information and Tasks