django-orm聚合查询和原生数据库查询
2021/8/13 2:06:46
本文主要是介绍django-orm聚合查询和原生数据库查询,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
聚合查询 group by
整表聚合
In [1]: from django.db.models import * In [2]: from bookstore.models import Book In [3]: b1=Book.objects.aggregate(res=Count('price')) In [4]: b1 Out[4]: {'res': 4} In [5]: type(b1) Out[5]: dict
分组聚合 先分组再聚合
In [17]: bs=Book.objects.values('pub','price') In [19]: bs.annotate(res=Count('id')) Out[19]: <QuerySet [{'pub': '清华大学出版社', 'price': Decimal('1.00'), 'res': 2}, {'pub': '机械工业出版社', 'price': Decimal('90.00'), 'res': 1}, {'pub': '机械工业出版社', 'price': Decimal('80.00'), 'res': 1}]> In [20]: bs.annotate(res=Count('id')).filter(price__gt=50) #groupby 和having 操作 Out[20]: <QuerySet [{'pub': '机械工业出版社', 'price': Decimal('90.00'), 'res': 1}, {'pub': '机械工业出版社', 'price': Decimal('80.00'), 'res': 1}]> In [21]: a=bs.annotate(res=Count('id')).filter(price__gt=50) In [22]: print(a.query) SELECT `Book`.`pub`, `Book`.`price`, COUNT(`Book`.`id`) AS `res` FROM `Book` WHERE `Book`.`price` > 50 GROUP BY `Book`.`pub`, `Book`.`price` ORDER BY NULL
原生数据库查询
Mymodel.objects.raw()
#sql注入的情形-字符串拼接 In [34]: s1=Book.objects.raw('select * from book where id=%s'%('1 or 1=1')) In [35]: for i in s1: ...: print(i.title) ...: Django JQury Linux HTML5 #防止sql注入的情形 In [36]: s2=Book.objects.raw('select * from book where id=%s',['1 or 1=1']) In [37]: for k in s2: ...: print(k.title) ...: In [38]: s3=Book.objects.raw('select * from book where id=%s',['3 or 1=1']) In [39]: for k in s3: ...: print(k.title)
cursor
In [71]: from bookstore.models import Book In [72]: with connection.cursor() as cur: ...: cur.execute('select * from book where pub=%s',['清华大学出版社']) ...: content=cur.fetchall() ...: In [73]: content Out[73]: ((2, 'Django', Decimal('1.00'), Decimal('85.00'), '清华大学出版社', 0), (5, 'HTML5', Decimal('1.00'), Decimal('115.00'), '清华大学出版社', 1))
这篇关于django-orm聚合查询和原生数据库查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-26解决google chrome helper 内存占用较高!
- 2024-04-01got an unexpected keyword argument
- 2024-03-30维多利亚的秘密 golang入坑系统
- 2024-03-29mongodb sort by date
- 2024-03-29go swagger
- 2024-03-25mongodb cdc
- 2024-03-25how to use go in vscode
- 2024-03-22mongooseserverselectionerror: connect econnrefused ::1:27017
- 2024-03-21pymongo insert_many
- 2024-03-18projection mongodb