CODEWITHSUNDEEP
pythondjangopostgresql
Michael
Michael

Reputation: 335

Django Raw SQL Query - How to pull value via column (instead of tuple index) from SELECT query

I'm executing a fetchall() raw SQL query in Django (1.11) with a PostgreSQL data source. The result returns a tuple.

My concern is that I can only seem to fetch a row's field via the tuple index i.e. row[2] (or in template I would do {{row.2}}). But I want to be able to use the actual field name instead of index i.e. row.itemno (or in template I would do {{row.itemno}})

I tried doing row.first_name but it says 'list' object has no attribute 'itemno'

Is it or isn't it possible to use field names with this result?

Here's a sample in python shell:

enter image description here

P.S. You might be wondering why I'm still using Django 1.11... It's because we are using DjangoCMS and unfortunately, it supports up to 1.11 only...

Upvotes: 1

Views: 1561

Answers (2)

Michael
Michael

Reputation: 335

I considered the suggestions to use namedtuple.

It's giving me what I want so far; I can now do something like {{row.field}} in my template.

But I think I need to make sure that my process isn't too complicated and tiresome. Maybe just a little more help to evaluate my code below if it's already ideal or not?

enter image description here

Upvotes: 0

Nalin Dobhal
Nalin Dobhal

Reputation: 2342

django documentation explains how you can execute custom SQL directly. From the Docs:

By default, the Python DB API will return results without their field names, which means you end up with a list of values, rather than a dict. At a small performance and memory cost, you can return results as a dict by using something like this:

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

Or you can you namedtuple as well.

from collections import namedtuple

def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

Upvotes: 3

Related Questions