How to get column names SQL in python

Question

I want get a db into pandas df in Python. I use a following code:

self.cursor = self.connection.cursor()
query = """
        SELECT * FROM `an_visit` AS `visit`                 
        JOIN `an_ip` AS `ip`  ON  (`visit`.`ip_id` = `ip`.`ip_id`)
        JOIN `an_useragent` AS `useragent` ON (`visit`.`useragent_id` = `useragent`.`useragent_id`)                 
        JOIN `an_pageview` AS `pageview`  ON (`visit`.`visit_id` = `pageview`.`visit_id`)       
        WHERE `visit`.`visit_id` BETWEEN  %s AND %s
        """
self.cursor.execute(query, (start_id, end_id))

df = pd.DataFrame(self.cursor.fetchall())

This code works, but I want to get column names as well. I tried this question MySQL: Get column name or alias from query

but this did not work:

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

How can I get column names from db into df? Thanks

Can you explain a bit more what do you mean by "get column names"? What is your desired output? — sophocles
– sophocles, Commented Dec 27, 2021 at 9:36
@sophocles now my df has names caled 1, 2, 3, ..., 25. I would like to have the same names, asi in the DB, which is ip_id, useragent_i etc. — vojtam
– vojtam, Commented Dec 27, 2021 at 9:40
when you say that the above code is not working, is it producing an error or not giving you the expected output? — Jayvee
– Jayvee, Commented Dec 27, 2021 at 10:25
@Jayvee I already solved it, see my answer, thank you for your effort! — vojtam
– vojtam, Commented Dec 27, 2021 at 10:31

Andy Z · Accepted Answer · 2022-05-28 17:26:06Z

5

The easy way to include column names within recordset is to set dictionary=True as following:

self.cursor = self.connection.cursor(dictionary=True)

Then, all of fetch(), fetchall() and fetchone() are return dictionary with column name and data

check out links: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

https://mariadb-corporation.github.io/mariadb-connector-python/connection.html

answered May 28, 2022 at 17:26

Andy Z

6067 silver badges4 bronze badges

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

1 Comment

Nicicalu Over a year ago

THANK YOU! Why does this not have more likes. You saved me, thanks!

vojtam · Accepted Answer · 2021-12-27 10:30:38Z

4

What work to me is:

field_names = [i[0] for i in self.cursor.description ]

answered Dec 27, 2021 at 10:30

vojtam

1,26517 silver badges43 bronze badges

Comments

kiran kumar · Accepted Answer · 2021-12-27 09:56:56Z

0

the best practice to list all the columns in the database is to execute this query form the connection cursor

SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA='<schema>' AND TABLE_NAME = '<table_name>'

answered Dec 27, 2021 at 9:56

kiran kumar

1104 bronze badges

1 Comment

vojtam Over a year ago

ok, can you write it so it pass to my example above? I don`t know how to modify it to my case.

Jayvee · Accepted Answer · 2021-12-27 10:16:11Z

0

There is a column_names properties in MySql cursor that you can use:

row = dict(zip(self.cursor.column_names, self.cursor.fetchone()))

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-column-names.html

edited Dec 27, 2021 at 10:16

answered Dec 27, 2021 at 9:44

Jayvee

10.9k3 gold badges35 silver badges43 bronze badges

5 Comments

vojtam Over a year ago

I tried, but AttributeError: 'Cursor' object has no attribute 'column_names'

Jayvee Over a year ago

are you connecting to a mysql DB?

vojtam Over a year ago

yes, using import pymysql as mysql and self.connection = mysql.connect()

Jayvee Over a year ago

that's odd, are you qualifying it with "self" , I've updated the answer.

vojtam Over a year ago

yes, I do it with self within the method.. Same error

Collectives™ on Stack Overflow

How to get column names SQL in python

4 Answers 4

1 Comment

Comments

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related