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))