ActiveState Code

Recipe 171463: MySQL and accessing database results by field

Accessing database results by field name rather than field number is easy. I prefer standard, stable library like MySQLdb rather than private solutions. Look at the following code.

Python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import MySQLdb
import MySQLdb.cursors

conn = MySQLdb.Connect(
    host='localhost', user='root',
    passwd='', db='test',compress=1,
    cursorclass=MySQLdb.cursors.DictCursor) # <- important
cursor = conn.cursor()
cursor.execute("SELECT name, sometext txt FROM foo")
rows = cursor.fetchall()
cursor.close()
conn.close()

for row in rows:
    print row['name'], row['txt'] # bingo!

# another (even better) way is:

conn = MySQLdb.Connect(
    host='localhost', user='root',
    passwd='', db='test',compress=1)
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
# ...
# results by field name
cursor = conn.cursor()
# ...
# ...results by field number

Discussion

Other values for cursorclass are:

Cursor This is the standard Cursor class that returns rows as tuples and stores the result set in the client. Warnings are raised as necessary.

SSCursor This is a Cursor class that returns rows as tuples and stores the result set in the server. Warnings are raised as necessary.

etc.

Look at cursors.py source file of MySQLdb

Comments

  1. 1. At 9:04 p.m. on 31 jan 2003, Justin Shaw said:

    Hmmmm... I shy away from this one for big sets because each row is going to take up space with the dict keys. I do however combine this with Marteli's Struct class for struct like access ... cursorclass=StructCursor) ...

    c.execute(sql) s = c.fetchone() s.col1 + s.col2

Sign in to comment