MySQL:从查询中获取列名或别名 [英] MySQL: Get column name or alias from query

问题描述

我不是要求 SHOW COLUMNS 命令.

我想创建一个与 heidisql 类似的应用程序，您可以在其中指定 SQL 查询，并在执行时返回一个结果集，其中的行和列代表您的查询结果.结果集中的列名应与您在 SQL 查询中定义的所选列相匹配.

I want to create an application that works similarly to heidisql, where you can specify an SQL query and when executed, returns a result set with rows and columns representing your query result. The column names in the result set should match your selected columns as defined in your SQL query.

在我的 Python 程序(使用 MySQLdb)中，我的查询仅返回行和列结果，而不返回列名称.在以下示例中，列名称将是 ext、totalsize 和 filecount.SQL 最终会在程序之外.

In my Python program (using MySQLdb) my query returns only the row and column results, but not the column names. In the following example the column names would be ext, totalsize, and filecount. The SQL would eventually be external from the program.

我能想到的唯一方法是编写我自己的 SQL 解析器逻辑来提取选定的列名.

The only way I can figure to make this work, is to write my own SQL parser logic to extract the selected column names.

是否有一种简单的方法可以获取所提供 SQL 的列名?接下来我需要知道查询返回多少列?

Is there an easy way to get the column names for the provided SQL? Next I'll need to know how many columns does the query return?

# Python

import MySQLdb

#===================================================================
# connect to mysql
#===================================================================

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

#===================================================================
# query select from table
#===================================================================

cursor = db.cursor ()   

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

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

cursor.close()
db.close()      

推荐答案

cursor.description 将为您提供一个元组元组，其中每个 [0] 是列标题.

cursor.description will give you a tuple of tuples where [0] for each is the column header.

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

这篇关于MySQL:从查询中获取列名或别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！