Accessing SQL Result Column Values by Column Name in Python

When dealing with a substantial number of columns in a database, relying on column indices for data retrieval can become cumbersome. This article addresses the need for a more intuitive approach by presenting a method to retrieve SQL result column values using the column name in Python.

Solution: Utilizing the DictCursor

Python's MySQLdb module offers the DictCursor class, which enables the retrieval of SQL results as a dictionary. This technique eliminates the need to handle column indices entirely:

import MySQLdb

cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT name, category FROM animal")

result_set = cursor.fetchall()
for row in result_set:
    print("%s, %s" % (row["name"], row["category"]))

Additional Compatibility

As per user1305650, this approach is also compatible with the pymysql module. To ensure its functionality, utilize the following code:

import pymysql

cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT name, category FROM animal")

result_set = cursor.fetchall()
for row in result_set:
    print("%s, %s" % (row["name"], row["category"]))

By adopting the techniques outlined in this article, developers can streamline their Python code for SQL result retrieval. The DictCursor offers a convenient and efficient method to access data by column name, enhancing the readability and maintainability of their scripts.