Django 使用原生SQL操作sqlite3数据库

有一些复杂的查询，用orm实现比较复杂，故可以采用sql语句来实现查询。

使用游标实现：

    from django.db import connection
    cursor = connection.cursor()
    cursor.execute("select * from COVID_19Analyse_country")
    for row in cursor.fetchall():
        print(row)

复杂的SQL如下：

    from django.db import connection
    cursor = connection.cursor()
    cursor.execute("""
        select country.name,tmp.h heal,tmp.c confirm,round(tmp.h/tmp.c,4) healrate
        from COVID_19Analyse_country country,
            (
                select cast(heal as REAL) h,confirm c,country_id
                from COVID_19Analyse_countrydata
                where date = '2020-06-18' and c >1000
                order by h/c
                limit 0,10
            ) tmp
        where tmp.country_id=country.id;
        """)
    for row in cursor.fetchall():
        print(row)

获取表头：

    title = []
    for field in cursor.description:
        title.append(field[0])
    print(title)

综合以上，将数据处理为一个字典列表形式，类似于Django中的model_to_dict后的格式：

    from django.db import connection
    cursor = connection.cursor()
    cursor.execute("""
        select country.name,tmp.h heal,tmp.c confirm,round(tmp.h/tmp.c,4) healrate
        from COVID_19Analyse_country country,
            (
                select cast(heal as REAL) h,confirm c,country_id
                from COVID_19Analyse_countrydata
                where date = '2020-06-18' and c >1000
                order by h/c
                limit 0,10
            ) tmp
        where tmp.country_id=country.id;
        """)
    title = []
    for field in cursor.description:
        title.append(field[0])
    ans = []
    for row in cursor.fetchall():
        dictTmp = {}
        for i in range(len(title)):
            dictTmp[title[i]] = row[i]
        ans.append(dictTmp)
    print(ans)