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, Dec 27 '21 at 09: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, Dec 27 '21 at 09:40
when you say that the above code is not working, is it producing an error or not giving you the expected output? — Jayvee, Dec 27 '21 at 10:25
@Jayvee I already solved it, see my answer, thank you for your effort! — vojtam, Dec 27 '21 at 10:31

score 4 · Answer 1 · answered May 28 '22 at 17:26

4

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 '22 at 17:26

Andy Z

406
3
4

1

THANK YOU! Why does this not have more likes. You saved me, thanks! – Nicicalu Oct 28 '22 at 15:49

score 2 · Accepted Answer · answered Dec 27 '21 at 10:30

2

What work to me is:

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

answered Dec 27 '21 at 10:30

vojtam

1,157
9
34

Jayvee · Answer 3 · 2021-12-27T10:16:11.937

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 '21 at 10:16

answered Dec 27 '21 at 09:44

Jayvee

10,670
3
29
40

I tried, but `AttributeError: 'Cursor' object has no attribute 'column_names'` – vojtam Dec 27 '21 at 10:02
are you connecting to a mysql DB? – Jayvee Dec 27 '21 at 10:11
yes, using `import pymysql as mysql` and `self.connection = mysql.connect()` – vojtam Dec 27 '21 at 10:12
that's odd, are you qualifying it with "self" , I've updated the answer. – Jayvee Dec 27 '21 at 10:17
yes, I do it with `self` within the method.. Same error – vojtam Dec 27 '21 at 10:21

score 0 · Answer 4 · answered Dec 27 '21 at 09:56

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 '21 at 09:56

kiran kumar

110
4

ok, can you write it so it pass to my example above? I don`t know how to modify it to my case. – vojtam Dec 27 '21 at 10:11

How to get column names SQL in python

4 Answers4