CODEWITHSUNDEEP
pythondjango
Sergey Golovchenko
Sergey Golovchenko

Reputation: 18661

How to get field names when running plain sql query in django

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?

Upvotes: 18

Views: 20771

Answers (3)

ZAD-Man
ZAD-Man

Reputation: 1406

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()
    ]

Upvotes: 29

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798626

According to PEP 249, you can try using cursor.description, but this is not entirely reliable.

Upvotes: 16

Hey Teacher
Hey Teacher

Reputation: 1225

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""")

Upvotes: 7

Related Questions