django orm 执行原生SQL

• 使用connection执行SQL

@method_decorator(login_required, name='dispatch')
class UseageByDept(TemplateView):
    def dictFetchAll(self, cursor):
        desc = cursor.description
        return [
            dict(zip([col[0] for col in desc], row))
            for row in cursor.fetchall()
        ]

    def get(self, request, *args, **kwargs):
        SQL = "SELECT u.department_1 as department,sum(i.cpu_core) as cpu,sum(i.memory) as memory,sum(d.disk_size) " \
              "as disk,COUNT(i.instance_id) as instance_count FROM " \
              "(SELECT instance_id,sum(disk_size) disk_size FROM cmdb_disks GROUP BY instance_id) d " \
              "INNER JOIN cmdb_instances i ON i.uuid=d.instance_id " \
              "INNER JOIN system_users u ON i.owner_id=u.id " \
              "GROUP BY u.department_1 " \
              "ORDER BY sum(d.disk_size) " \
              "DESC"
        cursor = connection.cursor()
        cursor.execute(SQL)
        res = self.dictFetchAll(cursor)
        
        return  HttpResponse(res)

• 使用raw执行SQL

ret = models.Instances.objects.raw('
SELECT u.department_1,sum(i.cpu_core) as total_cpu,sum(i.memory) as total_memory,sum(d.disk_size) as total_disk_size\
 from (select instance_id,sum(disk_size) disk_size from cmdb_disks group by instance_id)d \
INNER JOIN cmdb_instances i on i.uuid = d.instance_id \
INNER JOIN system_users u on i.owner_id = u.id  \
GROUP BY u.department_1 \
ORDER BY sum(d.disk_size) DESC')