How can I retrieve column headers from a MySQL query using Python?

Retrieving Column Information from MySQL Queries

When developing a Python application using MySQLdb, one common task is to execute SQL queries and retrieve the resulting column names and values. By default, cursor.fetchall() returns only the data without column information.

To obtain the column headers, utilize the cursor.description attribute. It provides a tuple of tuples, where each inner tuple contains information about a column. The first element of each inner tuple is the column header.

To accurately reflect the user-specified columns in SQL queries, consider the following steps:

<code class="python">cursor.execute(sql)
num_fields = len(cursor.description)
field_names = [i[0] for i in cursor.description]</code>

This code retrieves the number of columns in the query result and creates a list of column headers from the cursor.description attribute.

In your specific Python example, you can modify the code to extract the column names:

<code class="python">import MySQLdb

# Connect to MySQL

db = MySQLdb.connect(...)

# Execute the query

cursor = db.cursor()
cursor.execute("""
select ext,
       sum(size) as totalsize,
       count(*) as filecount
from fileindex
group by ext
order by totalsize desc;
""")

# Get column headers

num_fields = len(cursor.description)
field_names = [i[0] for i in cursor.description]

# Fetch and print the results

while (1):
    row = cursor.fetchone()
    if row == None:
        break
    print(" ".join("{}={}".format(field_names[i], row[i]) for i in range(num_fields)))

cursor.close()
db.close()</code>

This enhanced code now correctly retrieves and prints both the column headers and the corresponding data values for each row.

The above is the detailed content of How can I retrieve column headers from a MySQL query using Python?. For more information, please follow other related articles on the PHP Chinese website!