This is the 24th day of my participation in Gwen Challenge


The introduction

In the previous Django model design, we briefly introduced how to use model classes to query a database. In this article, we will focus on using model classes to query a database conditionally. To get you more familiar with djangos operating database.


The environment

Name of the environment version
Python 3.7.9
Django 3.1.2
MySql-Server 5.7.32
PyMySQL 0.10.1


Database table:

Test with the following table data:

id title author pub_date read comment
1 The Demi-Gods and the Semi-Devils Jin yong 1967-10-17 5000 3000
2 The Legend of The Condor Heroes Jin yong 1969-03-17 8000 5000
3 The Brave Archer and His Mate Jin yong 1959-10-17 6000 8000
4 Three young master’s sword cologne 1977-08-01 3000 1000
5 The Legendary Swordsman Jin yong 1969-03-17 7000 9000


A field

Implement SQL where function, call filter(), exclude(), get(), filter() as an example.

Attribute _id indicates the ID value of the object corresponding to the foreign key.

The syntax is as follows:

Attribute name __ Comparison operator = valueCopy the code

Note: Attribute names and comparison operators use two underscores, so attribute names cannot contain more than one underscore.


1) query, etc

“Exact” means “correct”.

Example: Query book 1.

book = BookInfo.objects.filter(id__exact=1Objects = bookinfo.objectsfilter(id=1)
Copy the code


2) Fuzzy query

Contains: Indicates whether it contains.

Note: If you want to include % without escaping, write directly.

Example: Query books whose titles contain biographies.

books = BookInfo.objects.filter(title__contains='the')
Copy the code


Startswith, endswith: starts or endswith a specified value.

Example: Query for books whose titles end in ‘part’

books = BookInfo.objects.filter(title__endswith='the')
Copy the code

All of the above operators are case sensitive and are preceded by I to indicate that they are case insensitive

Iexact, icontains, istartswith, iendswith.


3) empty query

Isnull: indicates whether the value isnull.

Example: Query for books whose titles are not empty.

books = BookInfo.objects.filter(title__isnull=False)
Copy the code


4) Range query

In: Indicates whether it is included in the range.

Example: Query for books numbered 1 or 3 or 5

books = BookInfo.objects.filter(id__in=[1.3.5])
Copy the code


5) Comparison query

Gt, GTE, LT, and LTE: Greater than, less than, less than or equal to.

The name of the The full name meaning
gt greater than Is greater than
gte greater than equal Greater than or equal to
lt less than Less than
lte less than Less than or equal to

Full names are easier to understand and remember.

Example: Query books whose number is greater than 3

books = BookInfo.objects.filter(id__gt=3)
Copy the code


For operators that are not equal to, use the exclude() filter.

Example: Query for books whose number is not equal to 3

books = BookInfo.objects.exclude(id=3)
Copy the code


6) Date query

Year, month, day, week_day, hour, minute, second: computes date-time attributes.

Example: Search for books published in 1969.

books = BookInfo.objects.filter(pub_date__year=1969)
Copy the code


Example: Query for books published after January 1, 1960.

books = BookInfo.objects.filter(pub_date__gt=date(1960.1.1))
Copy the code


F the object

The previous query is all object properties and constant value comparison, two properties how to compare?

A: Use F objects, defined in django.db.models.

The syntax is as follows:

F(Attribute name)Copy the code

Example: Query the number of books that have been read or reviewed.

from django.db.models import F
...
books = BookInfo.objects.filter(read__gte=F('comment'))
Copy the code


You can use arithmetic operations on F objects.

Example: Query books that have been read twice as many times as many reviews.

books = BookInfo.objects.filter(read__gt=F('comment') * 2)
Copy the code


Q object

Multiple filters are called one by one to represent logic and relationships, like the AND keyword in the WHERE part of the SQL statement.

Example: Query books whose number is less than 3 and whose number is greater than 20.

books = BookInfo.objects.filter(read__gt=20, id__lt=3) or books = bookinfo.objects.filter(read__gt=20).filter(id__lt=3)
Copy the code

If need to implement logic or or query, you need to use the object of Q | operator, Q object defined in the django. The models.

The syntax is as follows:

Q(Attribute name __ operator = value)Copy the code


Example: Query the number of books that can be read more than 5000.

from django.db.models import Q
...
books = BookInfo.objects.filter(read__gt=5000Books = bookinfo.objects.filter(Q(read__gt=5000))
Copy the code


Q object can use &, | connection, & presentation logic and, or | said logic.

Example: Query the books whose reading quantity is greater than 5000 or whose number is less than 3. You can only use the Q object

books = BookInfo.objects.filter(Q(read__gt=5000) | Q(pk__lt=3))
Copy the code


Q objects can be preceded by the ~ operator to indicate not or not.

Example: Query for books whose number is not equal to 3.

books = BookInfo.objects.filter(~Q(pk=3))
Copy the code

Note: PK, the full name of primary key stands for primary key ID


Aggregation function

Call the aggregate function using the aggregate() filter. The aggregate functions include Avg, Count, Max, Min, and Sum, which are defined in Django.db.models.

Example: Query the total number of books read.

from django.db.models import Sum
...
books = BookInfo.objects.aggregate(Sum('read'))
Copy the code

Note: The return value of aggregate is a dictionary type in the following format:

{'Aggregate class lowercase __ property name': value} such as: {'sum__read': 29000}
Copy the code


Generally, an aggregate() filter is not used when count is used.

Example: Query the total number of books.

books = BookInfo.objects.count()
Copy the code

Note: The count function returns a number.


The public,

Create a new folder X

Nature took tens of billions of years to create our real world, while programmers took hundreds of years to create a completely different virtual world. We knock out brick by brick with a keyboard and build everything with our brains. People see 1000 as authority. We defend 1024. We are not keyboard warriors, we are just extraordinary builders of ordinary world.