How to get column names from a SQL query?

Question

I need to put data from the SQL query into a Pandas dataframe. Please tell me is it possible to get column names the query results? I found that there is a keys() function in sqlalchemy for that but it does not work for me:

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' object has no attribute 'keys'

@sammywemmy is exactly right. Referencing OP's code to execute "df=pd.read_sql_query(Query, mydb)" will pull in a dataframe with the column names. The cursor is useful for other stuff like executing stored procedures, but for a straight read, this is the way to go. — Tom Renish
– Tom Renish, Commented Nov 1, 2021 at 15:23

Thiago Da Anunciação · Accepted Answer · 2020-07-03 13:41:21Z

4

I think that it your are searching for

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')

Hope this helps!

answered Jul 3, 2020 at 13:41

Thiago Da Anunciação

1706 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sumit_krm · Accepted Answer · 2020-07-03 12:24:08Z

2

SHOW COLUMNS FROM your-database-name.your-table-name

answered Jul 3, 2020 at 12:24

sumit_krm

211 bronze badge

Collectives™ on Stack Overflow

How to get column names from a SQL query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related