1.环境
python3.9
django2.2.23
2.raw()的用法
raw() 执行原始sql,并返回模型实例对象。
可用于多表关联查询
返回结果必须包含实例对象对应数据库表的主键
虽然返回结果是实例对象，但是对于多表关联查询，返回结果可以包含非实例对象（关联查询表）的字段属性。
如果最终查询结果中有的字段不存在于任何实例对象或关联查询对象中则会报错[Cannot resolve keyword ‘XXX’ into field. Choices are: app_label, id, model…],即最终查询的字段必须在关联查询的表中，不能凭空捏造。
3.实例
数据库表：product(产品表) model_replace(产品型号替换表),两个表并没有外键关联关系，只是通过一个product_unique_sign去关联
product
| product_id | product_name | product_model | product_unique_sign |
|---|---|---|---|
| 1 | 产品1 | model01 | 1001 |
| 2 | 产品2 | model02 | 1002 |
| 3 | 产品3 | model03 | 1003 |
model_replace
| model_id | product_model | product_model_replace | product_unique_sign |
|---|---|---|---|
| 1 | model02 | model01 | 1002 |
| 2 | model02 | model03 | 1002 |
def search(request): params = "" # 根据自己的业务逻辑可以在params添加where的查询条件,拼接到sql_str字符串中 # 如果是字符串类型的参数，记得在{}的两侧加上单引号，否则会报错。 # 因为生成的sql语句 会是 and p.product_model = model02 。这种SQL语句肯定是错误的。 param_model = "model02" params += " and p.product_model = ‘{}‘ ".format(param_model) sql_str = """select p.product_id as product_id,p.product_name as product_name,p.product_model as product_model,p.product_unique_sign as product_unique_sign,m.product_model_replace as product_model_replace from product as p left join model_replace on p.product_unique_sign = m.product_unique_sign where 1=1 {}""".format(params) # 对应sqla_str中的查询条件的字段 def change_obj_to_dict(temp): dict = {} dict["product_id"] = temp.product_id dict["product_name"] = temp.product_name dict["product_model"] = temp.product_model dict["product_unique_sign"] = temp.product_unique_sign # 虽然产品表里没有替代型号，但是通过sql语句的raw()查询可以查到结果中是包含非实例对象的字段的 dict["product_model_replace"] = temp.product_model_replace return dict
4. like模糊查询
当使用 like 进行模糊查询时，必须对str_sql字符串中的 like 后面的 % 使用 % 进行转义。
示例如下：
# mysql # select * from product where product_name like ‘%产品01%‘ # python 写法 product_name = "产品01" param_sql = " and product_name like ‘%%{}%%‘ ".format(product_name) str_sql = ‘‘‘ select * from product where 1=1 ‘‘‘.format(param_sql) # product_name = "产品01" # param_sql = " and product_name like ‘%%{}%%‘ ".format(product_name) str_sql = ‘‘‘ select * from product where 1=1 ‘‘‘.format(param_sql) # 应该改成 param_sql = " and product_name like %s " sql_where = "%%" + 产品01 + "%%" str_sql = ‘select * from product where 1=1 {} ‘.format(param_sql) model.objects.raw(str_sql, [sql_where])
Django 利用 .raw() 执行原始 SQL
Manager.raw(raw_query, params=None, translations=None)
基本查询
In [40]: from users.models import UsersProfile as Users In [41]: Users.objects.raw("select * from user_profile") Out[41]: <RawQuerySet: select * from user_profile> In [42]: u = Users.objects.raw("select * from user_profile")
获取基本信息
1. 表模型
In [43]: u.model
Out[43]: users.models.UsersProfile
2. 所有的表字段名
In [45]: u.columns Out[45]: [‘id‘, ‘password‘, ‘last_login‘, ‘is_superuser‘, ‘username‘, ‘first_name‘, ‘last_name‘, ‘email‘, ‘is_staff‘, ‘is_active‘, ‘date_joined‘, ‘nick_name‘, ‘birday‘, ‘gender‘, ‘address‘, ‘mobile‘, ‘image‘]
3. Django 的那个数据库
In [46]: u.db Out[46]: ‘default‘
4. 重现查询语句
In [48]: u.raw_query Out[48]: ‘select * from user_profile‘
获取数据
raw 返回对象可迭代，可切片
In [52]: for user in u: ...: print(user.id,user.username) ...: 1 admin 2 author1 3 author2 4 xiguatain 5 shark1 6 sharksuper
指定字段查询
指定字段查询时候，必须包含表主键字段(比如 id 字段）
官方提示： Django使用主键来标识模型实例，因此它必须始终包含在原始查询中。
In [59]: uf = Users.objects.raw("select id, username from user_profile" ...: ...: ) In [60]: uf[1].id Out[60]: 1 In [61]: uf[1].username Out[61]: ‘admin‘ In [62]: uf[1].email # 注意这里会再次触发查询数据库的操作 Out[62]: ‘admin@shark.com‘
传递参数个 raw
传参时应该始终使用 params 参数
where 条件查询
In [66]: usig = Users.objects.raw("select id, username from user_profil ...: e where username=%s", [name])
注意： 上述的 %s 不应该写成带引号的 ‘%s‘。
这会导致 SQL 注入
得到的结果已然是个集合
In [67]: usig Out[67]: <RawQuerySet: select id, username from user_profile where username=admin>
获取数据
In [70]: usig[0].id Out[70]: 1 In [71]: usig[0].username Out[71]: ‘admin‘
直接执行自定义
直接执行 UPDATE，INSERT 或 DELETE
In [95]: with connection.cursor() as cursor: ...: cursor.execute("update user_profile set email=‘1@100.com‘ ...: where id=%s;", params=[2]) ...: cursor.execute("select id, email from user_profile where i ...: d=%s;", params=[2]) ...: raw = cursor.fetchone() ...: print(raw) ...: (2, ‘1@100.com‘)
修改结果集的类型
得到包含字典类型的结果集
定义函数
def dictfetchall(cursor): "Return all rows from a cursor as a dict" columns = [col[0] for col in cursor.description] return [ dict(zip(columns, row)) for row in cursor.fetchall() ]
使用
In [99]: c = cursor.cursor() In [100]: dictfetchall(c) Out[100]: [{‘id‘: 9, ‘username‘: ‘aa‘}, {‘id‘: 1, ‘username‘: ‘admin‘}]
得到包含对象类型的结果集
定义函数
from collections import namedtuple def namedtuplefetchall(cursor): "Return all rows from a cursor as a namedtuple" desc = cursor.description nt_result = namedtuple(‘Result‘, [col[0] for col in desc]) return [nt_result(*row) for row in cursor.fetchall()]
使用
In [113]: c.execute("select id,username from user_profile limit 2") Out[113]: 2 In [114]: results = namedtuplefetchall(c) In [115]: results Out[115]: [Result(id=9, username=‘aa‘), Result(id=1, username=‘admin‘)] In [116]: results[0].username Out[116]: ‘aa
django中用原生sql查询
问题：模块写完后数据库插入了近一百万条数据，结果orm的查询方法当然就拖不起，慢的一匹
解决方法：先将数据库中经常查询的哪些建索引如图
进入我们的项目把orm的查询改成原生的sql
先上表格渲染部分的吧
def api_back_news(request): # 资讯列表(包含分页、搜索、日期筛选) if request.method == ‘GET‘: # 获取游标对象 cursor = connection.cursor() # 获取分页（没有分页就默认第一页） page = int(request.GET.get(‘page‘, 1)) # 一页多少条 limit = int(request.GET.get(‘limit‘, 10)) # 获取所有的数据 data = find_data(cursor, f‘select * from (select id from quick_poll_alerts order by alerts_releasetime desc limit ‘ f‘{(page - 1) * limit},{limit}) a left join quick_poll_alerts b on a.id=b.id‘) # 用原生sql查询总数 cursor.execute(f‘select count(*) from quick_poll_alerts‘) # 获取所有的数据 count = cursor.fetchall() # 关闭游标 cursor.close() return JsonResponse({‘code‘: 200, ‘data‘: data, ‘count‘: count}) #这里是分页 if request.method == ‘POST‘: if request.POST.get(‘page‘) != None: cursor = connection.cursor() page = int(request.POST.get(‘page‘, 1)) limit = int(request.POST.get(‘limit‘, 10)) data = find_data(cursor, f‘select * from (select id from quick_poll_alerts order by alerts_releasetime desc limit ‘ f‘{(page - 1) * limit},{limit}) a left join quick_poll_alerts b on a.id=b.id‘) cursor.execute(f‘select count(*) from quick_poll_alerts‘) count = cursor.fetchall() cursor.close() print(data) return JsonResponse({‘code‘: 200, ‘data‘: data, ‘count‘: count})
你会发现有一个find_data，这个呢是吧我格式化sql输出，简单的说就是把输出的数据变成我想要的格式,由于前端的数据字段和数据库的数据字段不一样所以我查询拼了一下
def find_data(cursor, sql): # 格式化sql输出 cursor.execute(sql) res = cursor.fetchall() # label和文章是多对多的表所以需要查询出label重新赋值 list = [] for r in res: data1 = {} cursor.execute(f‘select quick_poll_alerts.id, quick_poll_label.* ‘ f‘from quick_poll_alerts, quick_poll_label, quick_poll_alerts_label ‘ f‘where quick_poll_alerts.id=quick_poll_alerts_label.alerts_id ‘ f‘and quick_poll_label.id=quick_poll_alerts_label.label_id ‘ f‘and quick_poll_alerts.id={r[0]};‘) list1 = cursor.fetchall() labels = [l[2] for l in list1] data1[‘id‘] = r[0] data1[‘img‘] = r[4] data1[‘labels‘] = labels data1[‘title‘] = r[13] data1[‘regional‘] = r[8] data1[‘source‘] = r[11] data1[‘total_views‘] = r[10] data1[‘time‘] = r[12].strftime("%Y-%m-%d") list.append(data1) return list
当然 如果前后端的数据字段一样 那就可以直接
def find_data(cursor, sql): # 处理sql cursor.execute(sql) # 获取所以的数据 res = cursor.fetchall() # 获取数据字段 description = cursor.description # 循环出数据字段 columns = [i[0] for i in description] # 返回格式为{‘字段‘: 数据, ‘字段2‘: 数据2} return [dict(zip(columns, r)) for r in res]
Django的raw方法执行SQL查询
Django中模型查询可以用filter和get方法。但这些方法有时候远远不能满足我们的需求。
虽然有时候可以用__in，__icontains，__year和annotate，aggregate等拓展查询，但不灵活。
最灵活的方式还是使用SQL语句查询。
还好，Django也可以支持用SQL查询，并且可以返回对应的模型对象。
例如，有以下两个简单的模型：
Blog（博客）：id, caption, content
Comment（评论）：id, blog_id, comment
注意，id是Django模型的默认字段，无需在创建模型的时候，声明该字段。
先来个简单的查询，查询caption标题包含python的Blog。
若使用filter方法查询，如下：
blogs = Blog.objects.filter(caption__icontains = ‘python‘)
filter方法得到的blogs，类型是QuerySet。
若使用SQL方法查询，如下：
sql = "select * from blog_blog where caption like ‘%python%‘" blogs = Blog.objects.raw(sql)
这里需要使用raw方法把sql语句转换成Blog对象，此处的blogs类型是RawQuerySet。
同样的效果，都可以查到caption标题包含python的记录。
也许，你会觉得raw方法执行sql语句要麻烦一些。还要查一下具体对应的表名。
那么再看看下一个查询：根据博客按照评论数倒序排序。
若用QuerySet方法，很复杂，需要用到annotate，并且还有联合Comment对象。至少我自己尝试写了几次都写不出来。
若用SQL方法查询，只需要懂得SQL联合查询和统计即可：
sql = """select blog_blog.* from blog_blog left join blog_comment on blog_blog.id = blog_comment.blog_id group by blog_blog.id order by count(blog_comment.id) desc """ blogs = Blog.objects.raw(sql)
blog_blog联合blog_comment表，再对blog_comment计数并倒序。
很明显，在复杂查询时，用原始的SQL查询有很明显的优势。
我们可以在简单查询的时候，用QuerySet方法。在复杂查询的时候，用SQL语句的方法。
使用SQL语句查询的raw方法有1个特点：SQL语句中的字段可以包含其他非该模型的字段，一样也会被解析成属性。
例如，我按照评论数排序时，也想顺便得到评论数：
sql = """select blog_blog.*, count(blog_comment.id) as comment_num from blog_blog left join blog_comment on blog_blog.id = blog_comment.blog_id group by blog_blog.id order by count(blog_comment.id) desc """ blogs = Blog.objects.raw(sql)
这里的count(blog_comment.id) as comment_num 也会被解析成comment_num属性。
若把blogs返回给前端模版页面，我们可以在前端模版中直接读取该属性：
{% for blog in blogs %}
标题：{{blog.caption}}
评论数：{{blog.comment_num}}
{% endfor %}