在django执行原生语句的方法。
1.extra:结果集修改器,一种提供额外查询参数的机制
2.raw:执行原始SQL返回模型实例
3.connection/connections:直接执行自定义SQL(此方法不依赖model)
4.sqlalchemy
1.extra 方法
# 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 test01_usertype where id>%s'},
select_params=[1,],
where = ['age>%s'],
params=[18,],
order_by=['-age'],
tables=['test01_usertype']
)
"""相当于:
select
test01_userinfo.id,
(select count(1) from test01_usertype where id>1) as newid
from test01_userinfo,test01_usertype
where
test01_userinfo.age > 18
order by
test01_userinfo.age desc
"""
2.raw方法例子
models.UserInfo.raw('select * from test01_UserInfo')
返回的是一个 可迭代对象,用for循环遍历
可以 用 .列名 提取具体内容
3.connection/connections:直接执行自定义SQL(此方法不依赖model)
# 执行原生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])
# 插入数据
cursor.execute("insert into table(field) value('xx')")
# 更新数据
cursor.execure("update table set name='xx' where name='xxx'")
# 删除数据
cursor.execure("delete from table where name='xx'")
# 查询操作
cursor.execute("select * from table")
# 返回结果行游标直读向前,读取一条
row = cursor.fetchone()
# 读取所有
cursor.fetchall()
文章来源:https://www.e-learn.cn/content/qita/2338596
4.sqlalchemy
参考文章:https://www.liaoxuefeng.com/wiki/1016959663602400/1017803857459008