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

MySQL and accessing database results by field (Python recipe) by Jaroslaw Zabiello
ActiveState Code (http://code.activestate.com/recipes/171463/)

4

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, 27 lines
Download
 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

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

Tags: database

1 comment

Justin Shaw 10 years, 3 months ago  # | flag

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

Add a comment

Sign in to comment