从查询中获取列名或别名

I'm not asking for the SHOW COLUMNS command.

我并不是要求使用SHOW COLUMNS命令。

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.

我想创建一个类似于heidisql的应用程序，在这个应用程序中，您可以指定一个SQL查询，并在执行时返回一个带有行和列表示查询结果的结果集。结果集中的列名应该与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.

在我的Python程序(使用MySQLdb)中，我的查询只返回行和列结果，而不返回列名。在下面的示例中，列名将是ext、totalsize和filecount。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?

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

# 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()

6 个解决方案

#1

166

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

描述将给您一个元组，其中每个元组[0]是列标头。

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

#2

This is the same as thefreeman but more in pythonic way using list and dictionary comprehension

这和thefreeman一样，但更多的是用python的方式使用列表和字典理解

columns = cursor.description 
result = [{columns[index][0]:column for index, column in enumerate(value)} for value in cursor.fetchall()]

pprint.pprint(result)

#3

Similar to @James answer, a more pythonic way can be:

类似于@James的回答，一种更python化的方式可以是:

fields = map(lambda x:x[0], cursor.description)
result = [dict(zip(fields,row))   for row in cursor.fetchall()]

You can get a single column with map over the result:

你可以得到一个带有映射结果的列:

extensions = map(lambda x: x['ext'], result)

or filter results:

或过滤结果:

filter(lambda x: x['filesize'] > 1024 and x['filesize'] < 4096, result)

or accumulate values for filtered columns:

或累积过滤列的值:

totalTxtSize = reduce(
        lambda x,y: x+y,
        filter(lambda x: x['ext'].lower() == 'txt', result)
)

#4

I think this should do what you need (builds on the answer above) . I am sure theres a more pythony way to write it, but you should get the general idea.

我认为这应该做你所需要的(建立在上面的答案上)。我肯定会有更多的python方法来写它，但是你应该得到大意。

cursor.execute(query)
columns = cursor.description
result = []
for value in cursor.fetchall():
    tmp = {}
    for (index,column) in enumerate(value):
        tmp[columns[index][0]] = column
    result.append(tmp)
pprint.pprint(result)

#5

You could also use MySQLdb.cursors.DictCursor. This turns your result set into a python list of python dictionaries, although it uses a special cursor, thus technically less portable than the accepted answer. Not sure about speed. Here's the edited original code that uses this.

您还可以使用MySQLdb.cursors.DictCursor。这将您的结果集转换为python字典列表，尽管它使用了一个特殊的游标，因此在技术上比公认答案的可移植性差。不确定速度。下面是经过编辑的原始代码。

#!/usr/bin/python -u

import MySQLdb
import MySQLdb.cursors

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

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

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

cursor = db.cursor()

sql = 'SELECT ext, SUM(size) AS totalsize, COUNT(*) AS filecount FROM fileindex GROUP BY ext ORDER BY totalsize DESC;'

cursor.execute(sql)
all_rows = cursor.fetchall()

print len(all_rows) # How many rows are returned.
for row in all_rows: # While loops always make me shudder!
    print '%s %s %s\n' % (row['ext'], row['totalsize'], row['filecount'])

cursor.close()
db.close()

Standard dictionary functions apply, for example, len(row[0]) to count the number of columns for the first row, list(row[0]) for a list of column names (for the first row), etc. Hope this helps!

标准字典函数应用len(行[0])计算第一行的列数，列表(行[0])计算列名列表(行[0])，等等。

#6

Looks like MySQLdb doesn't actually provide a translation for that API call. The relevant C API call is mysql_fetch_fields, and there is no MySQLdb translation for that

看起来MySQLdb实际上并没有为那个API调用提供翻译。相关的C API调用是mysql_fetch_fields，对此没有MySQLdb转换

#1

166