django-orm查询操作02

django-orm查询操作02

条件查询-方法

官网文档

包含某个和某个filter

# filter 多个条件 表示select 字段1 from  表名 where 条件1 and 条件2

In [1]: from bookstore.models import Book

In [2]: b1=Book.objects.filter(pub='清华大学出版社')

In [3]: b1
Out[3]: <QuerySet [<Book: python_清华大学出版社_20.00_25.00>, <Book: Django_清华大学出版社_70.00_75.00>, <Book: HTML5_清华大学出版社_90.00_105.00>]>

In [4]: b2=Book.objects.filter(pub='中信出版社')

In [6]: b2
Out[6]: <QuerySet []>

In [7]: b1.query
Out[7]: <django.db.models.sql.query.Query at 0x1e7b7dc3ee0>

In [8]: print(b1.query)
SELECT `Book`.`id`, `Book`.`title`, `Book`.`pub`, `Book`.`price`, `Book`.`market_price` FROM `Book` WHERE `Book`.`pub` = 清华大学出版社


In [10]: b2=Book.objects.filter(pub='清华大学出版社',price=25)

In [11]: b2
Out[11]: <QuerySet []>

In [12]: print(b2.query)
SELECT `Book`.`id`, `Book`.`title`, `Book`.`pub`, `Book`.`price`, `Book`.`market_price` FROM `Book` WHERE (`Book`.`price` = 25 AND `Book`.`pub` = 清华大学出版社)

不包含某个exclude(取非)

In [14]: b2=Book.objects.exclude(pub='机械工业出版社')

In [15]: b2
Out[15]: <QuerySet [<Book: python_清华大学出版社_20.00_25.00>, <Book: Django_清华大学出版社_70.00_75.00>, <Book: HTML5_ 清华大学出版社_90.00_105.00>]>

In [16]: print(b2.query)
SELECT `Book`.`id`, `Book`.`title`, `Book`.`pub`, `Book`.`price`, `Book`.`market_price` FROM `Book` WHERE NOT (`Book`.`pub` = 机械工业出版社)

get获取一条数据信息

#多条数据报错
In [17]: b3=Book.objects.get(pub='清华大学出版社')
---------------------------------------------------------------------------
MultipleObjectsReturned                   Traceback (most recent call last)
<ipython-input-17-ca9c3206c8d4> in <module>
----> 1 b3=Book.objects.get(pub='清华大学出版社')

~AppDataLocalProgramsPythonPython38libsite-packagesdjangodbmodelsmanager.py in manager_method(self, *args, **kwargs)
     80         def create_method(name, method):
     81             def manager_method(self, *args, **kwargs):
---> 82                 return getattr(self.get_queryset(), name)(*args, **kwargs)
     83             manager_method.__name__ = method.__name__
     84             manager_method.__doc__ = method.__doc__

~AppDataLocalProgramsPythonPython38libsite-packagesdjangodbmodelsquery.py in get(self, *args, **kwargs)
    408                 self.model._meta.object_name
    409             )
--> 410         raise self.model.MultipleObjectsReturned(
    411             "get() returned more than one %s -- it returned %s!" %
    412             (self.model._meta.object_name, num)

MultipleObjectsReturned: get() returned more than one Book -- it returned 3!

#不能存在报错
In [19]: b3=Book.objects.get(pub='中信出版社')
---------------------------------------------------------------------------
DoesNotExist                              Traceback (most recent call last)
<ipython-input-19-5291510e80e3> in <module>
----> 1 b3=Book.objects.get(pub='中信出版社')

~AppDataLocalProgramsPythonPython38libsite-packagesdjangodbmodelsmanager.py in manager_method(self, *args, **kwargs)
     80         def create_method(name, method):
     81             def manager_method(self, *args, **kwargs):
---> 82                 return getattr(self.get_queryset(), name)(*args, **kwargs)
     83             manager_method.__name__ = method.__name__
     84             manager_method.__doc__ = method.__doc__

~AppDataLocalProgramsPythonPython38libsite-packagesdjangodbmodelsquery.py in get(self, *args, **kwargs)
    404             return clone._result_cache[0]
    405         if not num:
--> 406             raise self.model.DoesNotExist(
    407                 "%s matching query does not exist." %
    408                 self.model._meta.object_name

DoesNotExist: Book matching query does not exist.

非等值过滤查询、查询谓词

双下滑线：字段__查询值

字符包含字符开头结尾（where 条件 %字符 、字符%）

数值大于小于(where 条件 >、<、>=、<=)

In [22]: b2=Book.objects.filter(price__gte=25)

In [23]: b2
Out[23]: <QuerySet [<Book: Django_清华大学出版社_70.00_75.00>, <Book: JQury_机械工业出版社_90.00_85.00>, <Book: Linux_机械工业出版社_80.00_65.00>, <Book: HTML5_清华大学出版社_90.00_105.00>]>

数值区间、所在查询（where 条件 in/between ）

查询参数：Django的 select_related 和 prefetch_related 函数对 QuerySet 查询的优化
链接