How to use sql statement to query and return dictionary results in django

Introduction¶

In Django, some of the more complex methods are not suitable for using orm, and you need to use SQL statements to query. By default, Django uses SQL statement query to directly return a list of meta-ancestor data, which is not very convenient to use.

Django uses the sql statement method:

from django.db import connection

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])
        cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
        row = cursor.fetchone()

    return row

By default, the Python DB API will return results without field names, meaning that you end up with a list value, not a dict. With low performance and memory cost, the following content can be used to return the result dict.

method 1¶

def dictfetchall(cursor):
     # Get all the row data from the cursor and convert it into a dictionary 
     columns = [col[0] for col in cursor.description]
     return [
         dict(zip(columns, row))
         for row in cursor.fetchall()
     ]

method 2¶

method 2: is to use the python standard library: collections.namedtuple().

A amedtuple is a tuple-like object whose fields can be accessed through attribute lookup; it is also indexable and iterable. But it cannot be modified.

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

The following three data access

# Default
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> cursor.fetchall()
((54360982, None), (54360880, None))

# Conversion dictionary
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> dictfetchall(cursor)
[{'parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]

# Convert to nametuple data type
>>> cursor.execute("SELECT id, parent_id FROM test LIMIT 2");
>>> results = namedtuplefetchall(cursor)
>>> results
[Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)]
>>> results[0].id
54360982
>>> results[0][0]
54360982