django执行原生sql

extra

在QuerySet的基础上继续执行子语句

extra(self, select=None, where=None, params=None, tables=None, order_by=None, select_params=None)

select和select_params是一组，where和params是一组，tables用来设置from哪个表

Entry.objects.extra(select={'new_id': "select col from sometable where othercol > %s"}, select_params=(1,))
Entry.objects.extra(where=['headline=%s'], params=['Lennon'])
Entry.objects.extra(where=["foo='a' OR bar = 'a'", "baz = 'a'"])
Entry.objects.extra(select={'new_id': "select id from tb where id > %s"}, select_params=(1,), order_by=['-nid'])

举个例子：

models.UserInfo.objects.extra(
                    select={'newid':'select count(1) from app01_usertype where id>%s'},
                    select_params=[1,],
                    where = ['age>%s'],
                    params=[18,],
                    order_by=['-age'],
                    tables=['app01_usertype']
                )
                """
                select 
                    app01_userinfo.id,
                    (select count(1) from app01_usertype where id>1) as newid
                from app01_userinfo,app01_usertype
                where 
                    app01_userinfo.age > 18
                order by 
                    app01_userinfo.age desc
                """

raw

raw()管理器方法用于原始的SQL查询，并返回模型的实例。
需要注意的是：raw()语法查询必须包含主键，如果SQL是其他表时，需要设置列名
这个方法执行原始的SQL查询，并返回一个django.db.models.query.RawQuerySet 实例。 这个RawQuerySet 实例可以像一般的QuerySet那样，通过迭代来提供对象实例。

dic = {'city':'C'}
ret = Book.objects.raw('select id as nid,name from book where id >%s',
                       translations=dic,params=[22,])
for i in ret:
    print(i.id,i.name,i.C)

connection

完全避开模型层，直接执行自定义的SQL语句，更高灵活度的方式执行原生SQL语句

from django.db import connection, connections
cursor = connection.cursor()  # cursor = connections['default'].cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1])
row = cursor.fetchone() # fetchall()/fetchmany(..)