Retrieving column values by column name with MySQLdb

[Tim Chase]

jorma kala wrote:
> Hi,
> Is there a way of retrieving the value of columns in the rows returned by
> fetchall, by column name instead of index on the row?
> Code Snippet:
> 
>      query="select * from employees"
>      db=MySQLdb.connect(host=host,user=user,passwd=passwd,db=database)
>      cursor = db.cursor ()
>      cursor.execute (query)
>      rows = cursor.fetchall ()
> 
>       for row in rows:
>                print row[0]
> 
> 
> Instead of specifying the index of the row to retrieve the first column
> (row[0]), I'd like to retrieve the value of the first column by column name.
> Something like row.get('employee_id')
> Is something of the sort possible with Mysqdb?


Mike gave you a good answer, though I think it's MySQL specific. 
For a more generic solution:

   cursor.execute(query)
   name_to_index = dict(
     (d[0], i)
     for i, d
     in enumerate(cursor.description)
     )
   rows = cursor.fetchall()
   for row in rows:
     print row[name_to_index['employee_id']]

Or in case you have lots of column-names, a simple lambda can 
ease the typing required:

   for row in rows:
     item = lambda col_name: row[name_to_index[col_name]]
     print item('employee_id')

The built-in sqlite3 module also has a way to tell results to 
come back as a dict[1]

Note in each case the column-name indexing is case-sensitive.


Hope this helps,

-tim

[1]
http://docs.python.org/library/sqlite3.html#sqlite3.Connection.row_factory