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