How to retrieve column names from a MySQL query result set in Python using MySQLdb?

MySQL: Retrieve Column Headers from Query Results

Question:

How can we retrieve column names or aliases from a MySQL query result set in Python using MySQLdb? The requirement is that the column names in the result set should match the selected columns specified in the SQL query.

Answer:

To retrieve column names or aliases from a MySQL query result set using MySQLdb, follow these steps:

1. Execute the MySQL query statement using cursor.execute().
2. Use cursor.description to obtain a tuple of tuples representing the metadata of each column in the result set. The first item in each tuple ([0]) is the column name or alias.
3. To get the number of columns returned by the query, use len(cursor.description).

Code Example:

<code class="python"># Python

import MySQLdb

# Connect to MySQL
db = MySQLdb.connect(host="myhost", user="myuser", passwd="mypass",db="mydb")

# Execute the query
sql = """
    SELECT ext,
        SUM(size) AS totalsize,
        COUNT(*) AS filecount
    FROM fileindex
    GROUP BY ext
    ORDER BY totalsize DESC;
"""
cursor = db.cursor()
cursor.execute(sql)

# Get column names
column_names = [i[0] for i in cursor.description]

# Print results
while (1):
    row = cursor.fetchone ()
    if row == None:
        break
    print("%s %s %s\n" % (row[0], row[1], row[2]))

# Get number of columns
num_fields = len(cursor.description)

# Close cursor and database connection
cursor.close()
db.close()      

print("Number of columns:", num_fields)
print("Column names:", column_names)</code>

The above is the detailed content of How to retrieve column names from a MySQL query result set in Python using MySQLdb?. For more information, please follow other related articles on the PHP Chinese website!