In one of my django views I query database using plain sql (not orm) and return results.
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?
On the Django docs, there's a pretty simple method provided (which does indeed use cursor.description, as Ignacio answered).
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()
]
According to PEP 249, you can try using cursor.description, but this is not entirely reliable.
I have found a nice solution in Doug Hellmann's blog:
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""")
