MySQL: 从 query 获取列名或别名

我不是要求 SHOW COLUMNS指令。

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

在 Python 程序中(使用 MySQLdb) ，查询只返回行和列结果，而不返回列名。在下面的示例中，列名称为 ext、 totalsize和 filecount。SQL 最终将是程序外部的。

我认为实现这一点的唯一方法是编写自己的 SQL 解析器逻辑来提取选定的列名。

有没有一种简单的方法来获得所提供的 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()
183338 次浏览
小开

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

小开
最佳答案

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]
小开

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.

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)
小开

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

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


pprint.pprint(result)
小开

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

fields = [field_md[0] for field_md in cursor.description]
result = [dict(zip(fields,row)) for row in cursor.fetchall()]

You can get a single column with list comprehension over the result:

extensions = [row['ext'] for row in result)

or filter results using an additional if in the list comprehension:

large = [row for row in result if row['filesize'] > 1024 and row['filesize'] < 4096]

or accumulate values for filtered columns:

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

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.

#!/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!

小开

This is only an add-on to the accepted answer:

def get_results(db_cursor):
desc = [d[0] for d in db_cursor.description]
results = [dotdict(dict(zip(desc, res))) for res in db_cursor.fetchall()]
return results

where dotdict is:

class dotdict(dict):
__getattr__ = dict.get
__setattr__ = dict.__setitem__
__delattr__ = dict.__delitem__

This will allow you to access much easier the values by column names.
Suppose you have a user table with columns name and email:

cursor.execute('select * from users')
results = get_results(cursor)
for res in results:
print(res.name, res.email)
小开

Try:

cursor.column_names

mysql connector version:

mysql.connector.__version__
'2.2.9'
小开

You can also do this to just get the field titles:

table = cursor.description
check = 0
for fields in table:
for name in fields:
if check < 1:
print(name),
check +=1
check =0
小开

Something similar to the proposed solutions, only the result is json with column_header : vaule for db_query ie sql.

cur = conn.cursor()
cur.execute(sql)
res = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall()]

output json example:

[
{
"FIRST_ROW":"Test 11",
"SECOND_ROW":"Test 12",
"THIRD_ROW":"Test 13"
},
{
"FIRST_ROW":"Test 21",
"SECOND_ROW":"Test 22",
"THIRD_ROW":"Test 23"
}
]
小开

column_names = cursor.field_names

小开

found an easy way of having colums like sql using pymysql and pandas

import pymysql
import pandas as pd


db  = pymysql.connect(host="myhost", user="myuser", passwd="mypass", db="mydb")


query  = """SELECT ext
SUM(size) as totalsize,
COUNT(*) as filecount
FROM fileindex
GROUP BY ext
ORDER BY totalsize DESC;
"""
df = pd.read_sql_query(query,db)

the DataFrame will have column names ext,totalsize,filecount by default no need to do additional stuff.

for example in my case: enter image description here


提交答案