【Django】Django ORM - 查询数据

Posted by 西维蜀黍 on 2019-11-10, Last Modified on 2021-09-21

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