如何在python中获取表列名称/标题以进行SQL查询 [英] How to get table column-name/header for SQL query in python

问题描述

我将数据存储在使用Python存储在SQLITE数据库中的pandas数据框中.当我尝试查询其中的表时，我可以获得结果，但没有列名.有人可以引导我吗?

I have the data in pandas dataframe which I am storing in SQLITE database using Python. When I am trying to query the tables inside it, I am able to get the results but without the column names. Can someone please guide me.

sql_query = """Select date(report_date), insertion_order_id, sum(impressions), sum(clicks), (sum(clicks)+0.0)/sum(impressions)*100 as CTR
            from RawDailySummaries
            Group By report_date, insertion_order_id
            Having report_date like '2014-08-12%' """

cursor.execute(sql_query)
query1 = cursor.fetchall()

for i in query1:
    print i

下面是我得到的输出

(u'2014-08-12', 10187, 2024, 8, 0.3952569169960474)
(u'2014-08-12', 12419, 15054, 176, 1.1691244851866613)

我需要怎么做才能以表格形式显示结果以及列名称

What do I need to do to display the results in a tabular form with column names

推荐答案

在符合DB-API 2.0的客户端中，cursor.description是由7个项目组成的序列，序列形式为(<name>, <type_code>, <display_size>, <internal_size>, <precision>, <scale>, <null_ok>)，每列一个，如上所述. 此处.注意，如果execute语句的结果为空，则description将为None.

In DB-API 2.0 compliant clients, cursor.description is a sequence of 7-item sequences of the form (<name>, <type_code>, <display_size>, <internal_size>, <precision>, <scale>, <null_ok>), one for each column, as described here. Note description will be None if the result of the execute statement is empty.

如果要创建列名列表，则可以使用如下列表理解功能:column_names = [i[0] for i in cursor.description]然后根据需要对其进行处理.

If you want to create a list of the column names, you can use list comprehension like this: column_names = [i[0] for i in cursor.description] then do with them whatever you'd like.

或者，您可以将连接对象的row_factory参数设置为可为列名提供结果的内容.在此处，您将在下面看到有关sqlite3.Row类型的讨论.

Alternatively, you can set the row_factory parameter of the connection object to something that provides column names with the results. An example of a dictionary-based row factory for SQLite is found here, and you can see a discussion of the sqlite3.Row type below that.

这篇关于如何在python中获取表列名称/标题以进行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！