Raw query data/result to dictionary or grouped result set – Django

Many times we need to execute raw query instead of using built-in ORM. In this case it will return a tuple containing selected database fields in the query. We can access the output data with index values. But if we create a dictionary from the query result set where selected columns will be dictionary keys, it will be more easier to access the data as well as it will be more understandable when you read the code. Here is how we can create a dictionary from the resultSet.

packageQuery = """SELECT package_id, customer_id FROM user_packages"""
response = []
with connection.cursor() as cursor:
    cursor.execute(packageQuery)
    columns = [col[0] for col in cursor.description]
    print("columns: ", columns)

    resultSet = cursor.fetchall()
    print("resultSet:", resultSet)
    print("type of resultSet:", type(resultSet))

    for row in resultSet:
        data = {}
        for index, item in enumerate(row):
            # create dict with selected columns as dict keys
            if not data.get(row[0], False):
                data[columns[index]] = row[index]
        response.append(data)
print(response)

Here is the print statement output from the above code.

columns:  [‘package_id’, ‘customer_id’]

resultSet: ((1, 2), (2, 3), (1, 4), (1, 5), (1, 6), (2, 8), (1, 9), (1, 11), (1, 12), (1, 15))

type of resultSet: <class ‘tuple’>

response Data

[{‘package_id’: 1, ‘customer_id’: 2}, {‘package_id’: 2, ‘customer_id’: 3}, {‘package_id’: 1, ‘customer_id’: 4}, {‘package_id’: 1, ‘customer_id’: 5}, {‘package_id’: 1, ‘customer_id’: 6}, {‘package_id’: 2, ‘customer_id’: 8}, {‘package_id’: 1, ‘customer_id’: 9}, {‘package_id’: 1, ‘customer_id’: 11}, {‘package_id’: 1, ‘customer_id’: 12}, {‘package_id’: 1, ‘customer_id’: 15}]

Create grouped data set from the raw query result

# one more use case is when you need to create a group of notifiers by package_id or in other words, a group of customers associated with same package_id

# get index from list of dictionaries by value

l1 = [{'package_id': 1, 'customer_id': 2}, {'package_id': 2, 'customer_id': 1}, {'package_id': 2, 'customer_id': 3}]

searchedIndex = [i for i, dataDict in enumerate(l1) if 2 == dataDict['package_id']]
print("searchedIndex", searchedIndex[0])

 

packageQuery = """SELECT package_id, customer_id FROM user_packages"""

response = []
with connection.cursor() as cursor:
   cursor.execute(packageQuery)
   columns = [col[0] for col in cursor.description]
   print("columns: ", columns)

   resultSet = cursor.fetchall()
   print("resultSet:", resultSet)
   print("type of resultSet:", type(resultSet))
  
   for row in resultSet:
       data = {}
       for index, item in enumerate(row):
           # get/search index for existing package_id
           existingIndex = [i for i, dataDict in enumerate(response) if row[0] == dataDict['package_id']]

           if len(existingIndex) == 0:
               #print("columns[index]", columns[index])
               data[columns[index]] = row[index]
               data['notifiers'] = []
               response.append(data)
           elif index > 0 and columns[index]=='customer_id' :
               response[existingIndex[0]]['notifiers'].append(row[index])

print(response)

Here is the print statement output from the above code.

columns:  [‘package_id’, ‘customer_id’]

resultSet: ((1, 2), (2, 3), (1, 4), (1, 5), (1, 6), (2, 8), (1, 9), (1, 11), (1, 12), (1, 15))

type of resultSet: <class ‘tuple’>

response Data: [{‘package_id’: 1, ‘notifiers’: [2, 4, 5, 6, 9, 11, 12, 15]}, {‘package_id’: 2, ‘notifiers’: [3, 8]}]

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top
Shares