Django_数据库增删改查——查
查：
filter 支持很多的过滤条件，我们来看下：
contains
包含，相当于sql的like条件
1 | Entry.objects.get(headline__contains=‘Lennon‘) |
SQL equivalent:
1 | SELECT ... WHERE headline LIKE ‘%Lennon%‘; |
Note this will match the headline ‘Lennon honored today‘ but not ‘lennon honored today‘.
icontains 大小写不敏感
in
In a given iterable; often a list, tuple, or queryset.
1 | Entry.objects.filter(id__in=[1, 3, 4]) |
SQL equivalent:
1 | SELECT ... WHERE id IN (1, 3, 4); |
You can also use a queryset to dynamically evaluate the list of values instead of providing a list of literal values:
1 2 | inner_qs = Blog.objects.filter(name__contains=‘Cheddar‘)entries = Entry.objects.filter(blog__in=inner_qs) |
This queryset will be evaluated as subselect statement:
1 | SELECT ... WHERE blog.id IN (SELECT id FROM ... WHERE NAME LIKE ‘%Cheddar%‘) |
gt
1 | Entry.objects.filter(id__gt=4) |
SQL equivalent:
1 | SELECT ... WHERE id > 4; |
gte
Greater than or equal to.
lt
Less than.
lte
Less than or equal to.
startswith
Case-sensitive starts-with.
1 | Entry.objects.filter(headline__startswith=‘Lennon‘) |
SQL equivalent:
1 | SELECT ... WHERE headline LIKE ‘Lennon%‘; |
SQLite doesn’t support case-sensitive LIKE statements; startswith acts like istartswith for SQLite
istartswith
Case-insensitive starts-with.
endswith
Case-sensitive ends-with.
iendswith
Case-insensitive ends-with
range
区间过渡，可对数字、日期进行过滤
1 2 3 4 | import datetimestart_date = datetime.date(2005, 1, 1)end_date = datetime.date(2005, 3, 31)Entry.objects.filter(pub_date__range=(start_date, end_date)) |
SQL equivalent:
1 | SELECT ... WHERE pub_date BETWEEN ‘2005-01-01‘ and ‘2005-03-31‘; |
Warning！
Filtering a DateTimeField with dates won’t include items on the last day, because the bounds are interpreted as “0am on the given date”. If pub_date was a DateTimeField, the above expression would be turned into this SQL:
SELECT ... WHERE pub_date BETWEEN ‘2005-01-01 00:00:00‘ and ‘2005-03-31 00:00:00‘;
Generally speaking, you can’t mix dates and datetimes.
date
For datetime fields, casts the value as date. Allows chaining additional field lookups. Takes a date value.
1 2 | Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1)) |
year
For date and datetime fields, an exact year match. Allows chaining additional field lookups. Takes an integer year.
1 2 | Entry.objects.filter(pub_date__year=2005)Entry.objects.filter(pub_date__year__gte=2005) |
SQL equivalent:
1 2 | SELECT ... WHERE pub_date BETWEEN ‘2005-01-01‘ and ‘2005-12-31‘;SELECT ... WHERE pub_date >= ‘2005-01-01‘; |
When USE_TZ is True, datetime fields are converted to the current time zone before filtering. 简单解决办法是把USE_TZ=False
month
For date and datetime fields, an exact month match. Allows chaining additional field lookups. Takes an integer 1 (January) through 12 (December).
1 2 | Entry.objects.filter(pub_date__month=12)Entry.objects.filter(pub_date__month__gte=6) |
When <span>USE_TZ</span> is <span>True</span>, datetime fields are converted to the current time zone before filtering. This requires time zone definitions in the database.
SQL equivalent:
1 2 | SELECT ... WHERE EXTRACT(‘month‘ FROM pub_date) = ‘12‘;SELECT ... WHERE EXTRACT(‘month‘ FROM pub_date) >= ‘6‘; |
day
For date and datetime fields, an exact day match. Allows chaining additional field lookups. Takes an integer day.
1 2 | Entry.objects.filter(pub_date__day=3)Entry.objects.filter(pub_date__day__gte=3) |
SQL equivalent:
1 2 | SELECT ... WHERE EXTRACT(‘day‘ FROM pub_date) = ‘3‘;SELECT ... WHERE EXTRACT(‘day‘ FROM pub_date) >= ‘3‘; |
week
For date and datetime fields, return the week number (1-52 or 53) according to ISO-8601, i.e., weeks start on a Monday and the first week contains the year’s first Thursday.
Example:
1 2 | Entry.objects.filter(pub_date__week=52)Entry.objects.filter(pub_date__week__gte=32, pub_date__week__lte=38) |
week_day
For date and datetime fields, a ‘day of the week’ match. Allows chaining additional field lookups.
Takes an integer value representing the day of week from 1 (Sunday) to 7 (Saturday).
Example:
1 2 | Entry.objects.filter(pub_date__week_day=2)Entry.objects.filter(pub_date__week_day__gte=2) |
hour
For datetime and time fields, an exact hour match. Allows chaining additional field lookups. Takes an integer between 0 and 23.
Example:
1 2 3 | Event.objects.filter(timestamp__hour=23)Event.objects.filter(time__hour=5)Event.objects.filter(timestamp__hour__gte=12) |
SQL equivalent:
1 2 3 | SELECT ... WHERE EXTRACT(‘hour‘ FROM timestamp) = ‘23‘;SELECT ... WHERE EXTRACT(‘hour‘ FROM time) = ‘5‘;SELECT ... WHERE EXTRACT(‘hour‘ FROM timestamp) >= ‘12‘;同 |
同时，还支持mintue,second
1 2 3 4 | Event.objects.filter(time__minute=46)Event.objects.filter(timestamp__second=31) |