exclude()
Calling filter() again on a queryset clones it and adds new parameters with an AND operator. Note that multiple keyword parameters can be specified in a single function call.
Conversely, exclude() clones a queryset and adds any new parameters with an AND NOT operator:
>>> queryset = Employee.objects.filter(first_name="Roger", age=27)
>>> queryset2 = queryset.filter(last_name="Jolly")
>>> queryset3 = queryset.exclude(last_name="Jolly")
>>> any(employee.first_name != "Roger" for employee in queryset2)False
>>> any(employee.last_name != "Jolly" for employee in queryset2)False
>>> all(employee.last_name != "Jolly" for employee in queryset3)True
To retrieve all objects in the table with no parameters, use the all() function instead of filter(). all() can also be used to create a clone of an existing queryset (more on this later).
Iterating through querysets exposes the query results as instances of the associated Model.
Handling Foreign Keys
The fields of other models referred to in foreign key relationships can be referred to within querysets. Simply use double underscores to indicate a remote property to be queried:
# SELECT * FROM Dog INNER JOIN owner_tab ON (dog_tab.owner_id = owner_tab.id) WHERE dog_tab.name = "Yoko" AND owner.name = "Taro"
>>> queryset = Dog.objects.filter(name="Yoko", owner__name="Taro")
Doing this will usually perform a JOIN operation in MySQL. The one exception is referring to the primary key of the related model.
# SELECT * FROM Dog WHERE name = "Yoko" AND owner_id = 7>>> queryset = Dog.objects.filter(name="Yoko", owner__id=7)
>>> queryset = Dog.objects.filter(name="Yoko", owner__pk=7)
Query Parameter Suffixes
Apart from foreign key suffixes, Django also supports fixed-name suffixes to query parameters for specific types of checks. The table below lists all of them:
| Suffix | MySQL equivalent | Notes |
|---|---|---|
| __gt, __gte, __lt, __lte | >, >=, <, <= | Limited to numerical types, dates and datetimes |
| __exact | = ‘value’ | Acts the same as a regular query parameter. |
| __iexact | LIKE ‘value’ | Case insensitive. |
| __startswith | LIKE BINARY ‘value%’ | |
| __istartswith | LIKE ‘value%’ | Case insensitive. |
| __endswith | LIKE BINARY ‘%value’ | |
| __iendswith | LIKE ‘%value’ | Case insensitive. |
| __contains | LIKE BINARY ‘%value%’ | |
| __icontains | LIKE ‘%value%’ | Case insensitive. |
| __range | BETWEEN first_value AND second_value | Requires a tuple of two values to compare. This range test is inclusive.Can be used for numbers, dates/datetimes, and even characters. |
| __year | EXTRACT(YEAR FROM field_name) = value | For dates and datetimes. |
| __month | EXTRACT(MONTH FROM field_name) = value | For dates and datetimes. |
| __day | EXTRACT(DAY FROM field_name) = value | For dates and datetimes. |
| __week_day | DAYOFWEEK(field_name) = value | For dates and datetimes. |
| __hour | EXTRACT(HOUR FROM field_name) = value | For datetimes. |
| __minute | EXTRACT(MINUTE FROM field_name) = value | For datetimes. |
| __second | EXTRACT(SECOND FROM field_name) = value | For datetimes. |
| __isnull | IS NULL, IS NOT NULL | Accepts a boolean. |
| __search | MATCH (field_name) AGAINST (‘value’ IN BOOLEAN MODE) | Only supported by MySQL 5.6+, and for columns that have been set up properly.Refer to the documentation for details. |
| __regex | REGEXP BINARY ‘value’ | |
| __iregex | REGEXP ‘value’ |
The following examples illustrate their usage:
Employee.objects.filter(name__istartswith="Josh", age__lte=35)
Employee.objects.filter(age__range=(20, 25), hire_date__year=2018)
Employee.objects.filter(last_name__isnull=True)
get()
get() can be used as a shortcut to retrieve the first element of a queryset. The following statements are equivalent:
employee = Employee.objects.filter(first_name="Jolly", last_name="Rogers")[0]
employee = Employee.objects.get(first_name="Jolly", last_name="Rogers")
employee = Employee.objects.filter(first_name="Jolly").get(last_name="Rogers")
Naturally, if there are no results from the query, these statements will also raise an exception. However, get() will raise a DoesNotExist exception, which looks more readable when caught. get() can also raise a MultipleObjectsReturned exception if there are multiple possible results from the query:
try:
employee = Employee.objects.get(first_name="Jolly", last_name="Rogers")
catch Employee.DoesNotExist:
employee = None
logger.debug("Employee not found!")
catch Employee.MultipleObjectsReturned:
logger.debug("Query too vague, more than one result!")
raise Exception("Query too vague!")
A much easier way of retrieving a single result is to use the first() function instead. However, this will also not raise MultipleObjectsReturned, as first() simply retrieves the first item, and the uniqueness of the model instance returned cannot be assumed. Here’s an example with a query that returns no result:
>>> employee = Employee.objects.filter(first_name="Jolly", last_name="Rogers").first()
>>> employeeNone
Limiting Queries
The result of a queryset can be limited through Python slicing operators.
# LIMIT 10Employee.objects.filter(first_name="Jolly", age=25)[:10]
# OFFSET 25
Employee.objects.all()[25:]
# OFFSET 23 LIMIT 34
Employee.objects.exclude(last_name="Rogers")[23:57]
Similar to filter(), slicing will create a clone of the queryset, but this sliced clone will not accept additional filter parameters.
Using Different Operators
Although filter() and exclude() can cover simple AND queries, this does not cover the full spectrum of possible queries. Thus, Django provides the Q (query) object, to allow for more complex queries.
The Q object simply takes in the same arguments as the filter(), exclude() and get() functions, and must be supplied within one of the three functions to be applied to a query. This also means, by extension, the Q object can be supplied with an instance of itself.
Q objects can be used along with the bit-wise operators & (AND), | (OR) and ~ (NOT) to construct complex queries. Each operation returns a new Q object.
The following statements illustrate how Q objects can be used along with the bit-wise operators to construct equivalent queries in MySQL:
from django.db.models import Q
# SELECT * FROM employee_tab WHERE first_name = "Jolly" AND age = 25
Employee.objects.filter(Q(first_name="Jolly", age=25))
Employee.objects.filter(Q(first_name="Jolly") & Q(age=25))
# SELECT * FROM employee_tab WHERE first_name = "Jolly" OR NOT age = 25
Employee.objects.filter(Q(first_name="Jolly") | ~Q(age=25))
# SELECT * FROM employee_tab WHERE first_name = "Jolly" AND NOT (last_name = "Roger" OR (last_name = "Anderson" AND age = 30))
Employee.objects.filter(Q(first_name="Jolly") & ~Q(Q(last_name="Roger") | Q(last_name="Anderson", age=30))
Self-referencing Fields
Sometimes you need a query that can refer to the value in another column of the same row. Django provides the F (field) object for this purpose.
Use the F object to refer to the name of another column instead of providing the value directly:
from django.db.models import F
# SELECT * FROM employee_tab WHERE first_name = "last_name"
Employee.objects.filter(first_name="last_name")
# SELECT * FROM employee_tab WHERE first_name = last_name
Employee.objects.filter(first_name=F("last_name"))
Limiting the Columns Returned
To limit the performance overhead on the application server (both network load and memory usage), it’s possible for Django to limit the data it retrieves. The recommended way is to use the values() or values_list() functions. Both of these return modified clones of the queryset, with unique behavior when iterated over.
values() returns dictionaries when the queryset is iterated over, containing the keys specified, as well as their respective values for each object instance.
values_list() returns tuples when the queryset is iterated over, containing the values of the keys specified in the order given for each object instance. Additionally, if only a single key is specified, an optional flat keyword argument can be given. If flat is set to True, the values will be flattened instead of being wrapped in iterables. Naturally, attempting this with multiple keys is an error.
>>> employee_values = Employee.objects.filter(first_name="Charlie").values("first_name", "age")
>>> list(employee_values)
[{u"first_name": Charlie, u"age": 25}, {u"first_name": Charlie, u"age": 21}, {u"first_name": Charlie, u"age": 35}, ...]
>>> employee_values_list = Employee.objects.filter(last_name="Johnson").values_list("last_name", "first_name")
>>> list(employee_values_list)
[("Johnson", "Robert"), ("Johnson", "Charlie"), ...]
>>> employee_values_list_flat = Employee.objects.filter(last_name="Johnson").values_list("age", flat=True)
>>> list(employee_values_list_flat)
[26, 42, 31, ...]
values and values_list need not necessarily be the last function in the chain, as the cloned queryset they return will still expose most functions, such as filter, as well as slicing.
指定过滤条件（where）
获取一行
post = Post.objects.get(title="Sample title")
获取所有行
all_entries = eporter.objects.all()
<QuerySet [...]>
获取满足条件的所有行
Post.objects.filter(published_date__lte=timezone.now())
<QuerySet [...]>
获取满足条件的前 N 行
Post.objects.filter(published_date__lte=timezone.now())[0:2]
<QuerySet [...]>
这里是获取了第 1、2 行。python
指定列名来提取数据
指定一个字段（列） - values
>>> empList = Employee.objects.values("id")
<QuerySet [{'id': 1}, {'id': 10}, {'id': 100}, {'id': 11}, {'id': 12}, {'id': 13}, {'id': 14}, {'id': 15}, {'id': 16}, {'id': 17}, {'id': 18}, {'id': 19}, {'id': 2}, {'id': 20}, {'id': 21}, {'id': 22}, {'id': 23}, {'id': 24}, {'id': 25}, {'id': 26}, '......']>
只要第一条数据 - values
>>> empList = Employee.objects.values("id").first()
{dict} {'id': 1}
只要第一条数据 - values
>>> empList = Employee.objects.values_list("IP").first()
{tuple} ('192.168.1.111',)
只要特定数量的数据 - values
>>> empList = Employee.objects.values("id")[0:2]
<QuerySet [{'id': 1}, {'id': 10}>
只要特定数量的数据 - values_list
ipList = EmployeeIP.objects.values_list("IP")[0:2]
print(type(ipList))
# <class 'django.db.models.query.QuerySet'>
print(ipList)
# [('192.168.1.41',), ('192.168.1.44',)]
print(type(ipList[0]))
# <class 'tuple' >
print(ipList[0])
# 192.168.1.111
合并数据 - values_list，flat=True
相当于合并列数据到一个list 中：
ipList = EmployeeIP.objects.values_list("IP",flat=True)[0:2]
print(type(ipList))
# <class 'django.db.models.query.QuerySet'>
print(ipList)
# ['192.168.1.41','192.168.1.44']
print(type(ipList[0]))
# <class 'str' >
print(ipList[0])
# 192.168.1.111
指定多个字段 - values
>>> empList = Employee.objects.values("first_name", "last_name", "email")
>>> print(type(empList))
# <class 'django.db.models.query.QuerySet'>
>>> print(empList)
# [
# {'last_name': 'Wei', 'first_name': 'Vena', 'email': 'Vena@test.com'},
# {'last_name': 'Wan', 'first_name': 'Mark', 'email': 'mwan@test.com'}
# ]
values() 与 values_list()
values()
>>> empList = Employee.objects.values("id")[0:2]
<QuerySet [{'id': 1}, {'id': 10}>
调用 values() 时，对于结果集中的每一行，都用一个 dict 来表示。
values_list()
ipList = EmployeeIP.objects.values_list("IP")[0:2]
print(type(ipList))
# <class 'django.db.models.query.QuerySet'>
print(ipList)
# [('192.168.1.41',), ('192.168.1.44',)]
print(type(ipList[0]))
# <class 'tuple' >
print(ipList[0])
# 192.168.1.111
调用 values_list() 时，对于结果集中的每一行，都用一个 tuple 来表示。
同时，可以增加flat=True参数，返回某个字段的列表，例子如下：
Case_images.objects.filter(case=case_id).order_by('-created_time').values_list('url',flat=True)
总结
values()- 单条记录 -
<class 'dict'> - 多条记录 -
<class 'django.db.models.query.QuerySet'>
- 单条记录 -
values_list()- 单条记录 -
<class 'tuple'> - 多条记录 -
<class 'django.db.models.query.QuerySet'>
- 单条记录 -
Reference
- django获取指定列的数据 - https://www.cnblogs.com/wancy86/p/django_getfield.html