Show column names in dataframe from Python MySQL connector

Question

I am importing data from a MySQL database into a Python project and the columns are printing as a series of a numbers instead of the actual column names.

This is my code so far for reference:

#Establish connection to database
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="xxxx",
  database="database"
)

#Use select statement on table
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM results LIMIT 5")
myresult = mycursor.fetchall()

#Convert to pandas dataframe
import pandas as pd
df = pd.DataFrame(myresult)
df

The output of which is the following table:

0	1	2	3
4252764	100595	SP123	12543
4252765	100595	SP124	12544
4252766	100595	SP125	12545
4252767	100595	SP126	12546
4252768	100595	SP127	12547

However it is missing the column names and should look like this ideally:

Id	StepId	name	ElementId
4252764	100595	SP123	12543
4252765	100595	SP124	12544
4252766	100595	SP125	12545
4252767	100595	SP126	12546
4252768	100595	SP127	12547

Am I missing something? Any help is appreciated.

There's a pd.read_sql command. Try df = pd.read_sql("SELECT * FROM results LIMIT 5", mydb)? — Quang Hoang
– Quang Hoang, Commented Dec 17, 2020 at 4:23

Frightera · Accepted Answer · 2021-06-10 10:26:09Z

2

Instead of using cursor and then passing results as DataFrame using Pandas read_sql function. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html

 query = "SELECT * FROM results LIMIT 5"
 df_mysql = pd.read_sql(query, con = mydb)

 print(df_mysql)

edited Jun 10, 2021 at 10:26

Frightera

5,1542 gold badges15 silver badges31 bronze badges

answered Jun 10, 2021 at 6:56

Avinash Gawali

212 bronze badges

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

Collectives™ on Stack Overflow

Show column names in dataframe from Python MySQL connector

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related