python 在 django 中运行普通 sql 查询时如何获取字段名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/805660/
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 field names when running plain sql query in django
提问by Sergey Golovchenko
In one of my django views I query database using plain sql (not orm) and return results.
在我的 Django 视图之一中，我使用普通 sql（不是 orm）查询数据库并返回结果。
sql = "select * from foo_bar"
cursor = connection.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
I am getting the data fine, but not the column names. How can I get the field names of the result set that is returned?
我得到的数据很好，但不是列名。如何获取返回的结果集的字段名称？
回答by ZAD-Man
On the Django docs, there's a pretty simple method provided (which does indeed use cursor.description, as Ignacio answered).
在Django docs 上，提供了一个非常简单的方法（它确实使用了cursor.description，正如 Ignacio 回答的那样）。
def dictfetchall(cursor):
"Return all rows from a cursor as a dict"
columns = [col[0] for col in cursor.description]
return [
dict(zip(columns, row))
for row in cursor.fetchall()
]
回答by Ignacio Vazquez-Abrams
回答by Hey Teacher
I have found a nice solution in Doug Hellmann's blog:
我在 Doug Hellmann 的博客中找到了一个不错的解决方案：
http://doughellmann.com/2007/12/30/using-raw-sql-in-django.html
http://doughellmann.com/2007/12/30/using-raw-sql-in-django.html
from itertools import *
from django.db import connection
def query_to_dicts(query_string, *query_args):
"""Run a simple query and produce a generator
that returns the results as a bunch of dictionaries
with keys for the column values selected.
"""
cursor = connection.cursor()
cursor.execute(query_string, query_args)
col_names = [desc[0] for desc in cursor.description]
while True:
row = cursor.fetchone()
if row is None:
break
row_dict = dict(izip(col_names, row))
yield row_dict
return
Example usage:
用法示例：
row_dicts = query_to_dicts("""select * from table""")
回答by user12231804
try the following code :
尝试以下代码：
def read_data(db_name,tbl_name):
details = sfconfig_1.dbdetails
connect_string = 'DRIVER=ODBC Driver 17 for SQL Server;SERVER={server}; DATABASE={database};UID={username}\
;PWD={password};Encrypt=YES;TrustServerCertificate=YES'.format(**details)
connection = pyodbc.connect(connect_string)#connecting to the server
print("connencted to db")
# query syntax
query = 'select top 100 * from '+'[{}].[dbo].[{}]'.format(db_name,tbl_name) + ' t where t.chargeid ='+ "'622102*3'"+';'
#print(query,"\n")
df = pd.read_sql_query(query,con=connection)
print(df.iloc[0])
return "connected to db...................."
