MySQL:从查询中获取列名或别名

问题描述

我不是在要求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:从查询中获取列名或别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！