Retrieving column values by column name with MySQLdb
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? Thanks very much. -- http://mail.python.org/mailman/listinfo/python-list
Re: Retrieving column values by column name with MySQLdb
On Fri, Jun 19, 2009 at 8:16 PM, jorma kalajjk...@gmail.com 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? Thanks very much. Use a DictCursor: import MySQLdb.cursors . . . cursor = db.cursor (MySQLdb.cursors.DictCursor) cursor.execute (query) rows = cursor.fetchall () for row in rows: print row['employee_id'] -- kushal -- http://mail.python.org/mailman/listinfo/python-list
Re: Retrieving column values by column name with MySQLdb
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Retrieving column values by column name with MySQLdb
On Fri, 19 Jun 2009 15:46:46 +0100 jorma kala jjk...@gmail.com wrote: 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? Try this: db = MySQLdb.Connection(host=host,user=user,passwd=passwd,db=database) db.query(query) result = db.store_result() data = result.fetch_row(maxrows=0, how=1) -- Mike Kazantsev // fraggod.net signature.asc Description: PGP signature -- http://mail.python.org/mailman/listinfo/python-list
Re: Retrieving column values by column name with MySQLdb
On Fri, 19 Jun 2009 10:32:32 -0500 Tim Chase python.l...@tim.thechases.com wrote: Mike gave you a good answer, though I think it's MySQL specific. I don't have to deal with MySQL frequently but I've remembered that I used got the fields out somehow, and now, looking at the code, I wonder myself, why how is 1 and wtf is this how, anyway!? ;) I can't seem to find any mention of such methods in documentation and even python source, guess they are implemented directly in underlying C lib. Hope I learned to abstract from such syntax since then, I sure do... -- Mike Kazantsev // fraggod.net signature.asc Description: PGP signature -- http://mail.python.org/mailman/listinfo/python-list