How to Retrieve Column Names from SQL Queries in MySQL?

Retrieving Column Names from SQL Queries Using MySQL

In MySQL, extracting column names from query results can be achieved using the cursor.description attribute. This attribute returns a tuple of tuples, where each inner tuple represents a column header. The following Python code snippet demonstrates how to obtain both the column names and the number of columns in a query result:

<code class="python">import MySQLdb

# Connect to MySQL
try:
    db = MySQLdb.connect(host="myhost", user="myuser", passwd="mypass", db="mydb")
except MySQLdb.Error as e:
    print("Error %d: %s" % (e.args[0], e.args[1]))
    sys.exit(1)

# Execute a 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 names
field_names = [i[0] for i in cursor.description]

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

# Print column names
print("Column Names:")
for name in field_names:
    print(name)

# Print number of columns
print("Number of Columns:", num_fields)

# Close cursor and db
cursor.close()
db.close()</code>

In this example, suppose the query returns column names ext, totalsize, and filecount. The field_names list will contain these column names, and the num_fields variable will be set to 3, indicating the number of columns in the result set.

This approach provides an easy solution for obtaining column names without resorting to custom SQL parsing logic.

The above is the detailed content of How to Retrieve Column Names from SQL Queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!