pandas 如何在python中获取SQL查询的表列名/表头
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34214031/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get table column-name/header for SQL query in python
提问by bshah
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.
我有 Pandas 数据帧中的数据，我使用 Python 将其存储在 SQLITE 数据库中。当我尝试查询其中的表时，我能够获得结果但没有列名。有人可以指导我。
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
Below is the output that I get
下面是我得到的输出
(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
我需要做什么才能以带有列名的表格形式显示结果
回答by James J.
In DB-API 2.0 compliant clients, cursor.descriptionis 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 descriptionwill be Noneif the result of the execute statement is empty.
在DB-API 2.0兼容的客户端，cursor.description是形式的7项序列的序列(<name>, <type_code>, <display_size>, <internal_size>, <precision>, <scale>, <null_ok>)，每列一个，如所描述这里。注description会None，如果执行语句的结果是空的。
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.
如果你想创建一个列名列表，你可以像这样使用列表理解：column_names = [i[0] for i in cursor.description]然后随心所欲地处理它们。
Alternatively, you can set the row_factoryparameter 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.Rowtype below that.
或者，您可以将row_factory连接对象的参数设置为提供列名和结果的参数。在这里可以找到 SQLite 基于字典的行工厂的示例，您可以在sqlite3.Row下面看到有关该类型的讨论。
回答by Ezer K
Try Pandas .read_sql(), I can't check it right now but it should be something like:
尝试 Pandas .read_sql()，我现在无法检查它，但它应该是这样的：
pd.read_sql( Q , connection)
回答by delphisharp
my english is poor i write a sample, see it ,use cx_Oracle, but mysql the same it .
我的英语很差，我写了一个示例，看看它，使用 cx_Oracle，但 mysql 也一样。
import cx_Oracle
def test_oracle():
connection = cx_Oracle.connect('user', 'password', 'tns')
try:
cursor = connection.cursor()
cursor.execute('SELECT day_no,area_code ,start_date from dic.b_td_m_area where rownum<10')
#only print head
title = [i[0] for i in cursor.description]
print(title)
# column info
for x in cursor.description:
print(x)
finally:
cursor.close()
if __name__ == "__main__":
test_oracle();
回答by Harry_pb
Step 1: Select your engine like pyodbc, SQLAlchemy etc.
第 1 步：选择您的引擎，如 pyodbc、SQLAlchemy 等。
Step 2: Establish connection
cursor = connection.cursor()
第二步：建立连接
cursor = connection.cursor()
Step 3: Execute SQL statement
cursor.execute("Select * from db.table where condition=1")
第三步：执行SQL语句
cursor.execute("Select * from db.table where condition=1")
Step 4: Extract Header from connection variable description
第 4 步：从连接变量描述中提取 Header
headers = [i[0] for i in cursor.description]
print(headers)
