Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Python: Python

Retrieving column values by column name with MySQLdb

 

 

Python python RSS feed   Index | Next | Previous | View Threaded


jjkk73 at gmail

Jun 19, 2009, 7:46 AM

Post #1 of 5 (8663 views)
Permalink
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.


mk.fraggod at gmail

Jun 19, 2009, 8:07 AM

Post #2 of 5 (8540 views)
Permalink
Re: Retrieving column values by column name with MySQLdb [In reply to]

On Fri, 19 Jun 2009 15:46:46 +0100
jorma kala <jjkk73 [at] gmail> 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
Attachments: signature.asc (0.19 KB)


kushal.kumaran+python at gmail

Jun 19, 2009, 8:25 AM

Post #3 of 5 (8543 views)
Permalink
Re: Retrieving column values by column name with MySQLdb [In reply to]

On Fri, Jun 19, 2009 at 8:16 PM, jorma kala<jjkk73 [at] gmail> 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


python.list at tim

Jun 19, 2009, 8:32 AM

Post #4 of 5 (8549 views)
Permalink
Re: Retrieving column values by column name with MySQLdb [In reply to]

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


mk.fraggod at gmail

Jun 19, 2009, 8:47 AM

Post #5 of 5 (8551 views)
Permalink
Re: Retrieving column values by column name with MySQLdb [In reply to]

On Fri, 19 Jun 2009 10:32:32 -0500
Tim Chase <python.list [at] tim> 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
Attachments: signature.asc (0.19 KB)

Python python RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.