django使用原生Sql语句实现多表连接查询，并向前端返回结果集的json

用到一个重要元素，叫cursor，导包from django.db import connection

    cursor = connection.cursor() #定义游标

    # 下面执行具体的sql语句

    cursor.execute("SELECT * FROM game_scheduledcourses, game_coursecatalog WHERE game_scheduledcourses.courseNo = game_coursecatalog.courseNo;") 

执行完上面这句话后，我们调用cursor.fetchall()就可以获取出查询出的结果，但是，查询出的结果都是长下面这样的，没有列名，显然不符合我们的需求

我们需要用下面这个函数将元组转为带有列名的列表

   dictfetchall(cursor)

这里需要用到下面这个自定义函数，它的作用是从cursor中取出每个列名，并且和每个列元素打包，如下所示：

from collections import namedtuple

def dictfetchall(cursor):
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

转换完之后，结果呈现下面这样

最后将json结果返回给前台即可

rs = json.dumps(dictfetchall(cursor))
    return JsonResponse({
        'result': "success",
        'data': rs
    })

完整代码如下：

from django.http import HttpResponse, JsonResponse
from django.shortcuts import render
from game.models.people.people import Scheduledcourses, CourseCatalog
from django.core import serializers
from django.db import connection
from collections import namedtuple
import json

def dictfetchall(cursor):
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]

def namedtuplefetchall(cursor):
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

def show_page(request):
    return render(request, "SelectCourse.html")

def show_all_course(request):
    cursor = connection.cursor() 
    cursor.execute("SELECT * FROM game_scheduledcourses, game_coursecatalog WHERE game_scheduledcourses.courseNo = game_coursecatalog.courseNo;") 
    rs = json.dumps(dictfetchall(cursor))
    return JsonResponse({
        'result': "success",
        'data': rs
    })