如何从sql查询中获取列名？

p1tboqfb 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(630)

我需要将来自sql查询的数据放入一个dataframe中。请告诉我有没有可能从查询结果中得到列名？我发现有一个 keys() sqlalchemy中的函数，但它不适用于我：

import mysql.connector
import pandas as pd
mydb = mysql.connector.connect(
  host="SQLServer",
  user="sqlusr",
  password="usrpasswd",
  database="sqldb"
)
cursor = mydb.cursor()
Query="SELECT Title, Review, Rate FROM reviews;"
cursor.execute(Query)
df = pd.DataFrame(cursor.fetchall())
df.columns = cursor.keys()

attributeerror:“cmysqlcursor”对象没有属性“keys”

sql mysql pandas python-3.x mysql-connector

来源：https://stackoverflow.com/questions/62715267/how-to-get-column-names-from-a-sql-query

2条答案

按热度按时间

y53ybaqx1#

我想这是你在寻找的

cnx = mysql.connector.connect(user=DB_USER, password=DB_USER_PASSWORD, host=DB_HOST, database=DB_NAME)
cursor = cnx.cursor()
query = ("SELECT `name`, `ftp_name`, `created_at`, `status` AS `status_customer` FROM `customers"
         "WHERE `status` = %(status)s")
cursor.execute(query, { 'status': 1 })
# 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]
print(num_fields)
print(field_names)
>>> 4
>>> [u'name', u'ftp_name', 'created_at', u'status_customer']
# OR just use this cursor function:
print(cursor.column_names)
>>> (u'name', u'ftp_name', 'created_at', u'status_customer')