How to get column names SQL in python-CodePudding

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

CodePudding user response：

What work to me is:

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

CodePudding user response：

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

CodePudding user response：

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