Debug School

rakesh kumar
rakesh kumar

Posted on

django-queryset-examples

django-queryset-examples
django-queryset-examples
django-queryset-examples

Django QuerySet Examples

In this article, you will find the most commonly used Django QuerySet examples with SQL codes.

A QuerySet describes a collection of objects/rows from your database. At some point, QuerySets are being "converted" to SQL queries. And one of the important facts about them is they are lazy. What does it mean? They can be constructed, filtered, sliced, and passed around without actually hitting the database. And only in the listed below cases, they are being evaluated:

Iteration: A QuerySet is iterable, and it executes a database query the first time we iterate over it.
Pickling/Caching: Evaulated if we pickle or cache a QuerySet.
repr(), len(), list(): A QuerySet is being evaluated when we call repr(), len(), list() on it.
Slicing: A QuerySet is being sliced with the step parameter will hit the database.

Contents

  1. How to get the SQL query from the QuerySet?
  2. How to do AND operation?
  3. How to do OR operation?
  4. How to do a not equal filtering?
  5. How to do IN filtering?
  6. How to do IS NULL or IS NOT NULL queries?
  7. How to do SQL “LIKE” equivalent queries?
  8. How to do comparison(>, >=, <, <=) operations?
  9. How to do BETWEEN operation?
  10. How to LIMIT the number of results in queryset?
  11. How to ORDER the results in queryset?
  12. How to get a single object from queryset?

How to get the SQL query from the QuerySet

?
Sometimes, we need to get the raw SQL query that is being generated and executed behind the scenes when we evaluate the QuerySet. This could help us with the debug process and not only. The string representation of the query attribute of the QuerySet object is the SQL query that we are talking about. See the example below:

In: str(User.objects.all().query)
Out: 'SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user"'
Enter fullscreen mode Exit fullscreen mode

How to do AND operation?
As an AND operation example let's limit(filter()) users whose first_name is equal to John and also make sure that they are active - is_active is true.

qs1 = User.objects.filter(first_name="John", is_active=True)
Enter fullscreen mode Exit fullscreen mode

the same thing we can do by chaining multiple filters:

qs2 = User.objects.filter(first_name="John").filter(is_active=True)
Enter fullscreen mode Exit fullscreen mode

alternatively, we can apply & operator on two or more QuerySets:

qs3 = User.objects.filter(first_name="John") & User.objects.filter(is_active=True)
Enter fullscreen mode Exit fullscreen mode

and finally, we can use the tool Q from django.db.models.

qs4 = User.objects.filter(Q(first_name="John") & Q(is_active=True))
Enter fullscreen mode Exit fullscreen mode

It's worth saying that all QuerySet objects(qs1, qs2, qs3, qs4) will generate the same SQL query in the end. The comparison below proves that:

In: str(qs1.query) == str(qs2.query) == str(qs3.query) == str(qs3.query)
Out: True
In SQL terms, they are evaluated to

SELECT "auth_user"."id", 
   ...
   "auth_user"."date_joined" 
FROM "auth_user" 
WHERE ("auth_user"."first_name" = John AND "auth_user"."is_active" = True)
Enter fullscreen mode Exit fullscreen mode

SQL query, all without exceptions.

How to do OR operation?
Very basic example of OR operation will be filtering users whose first_name is equal to John or Jane. We can achieve this with the help of Q:

qs1 = User.objects.filter(Q(first_name="John") | Q(first_name="Jane"))
Enter fullscreen mode Exit fullscreen mode

or applying | (pipe) operator on two or more querysets :

qs2 = User.objects.filter(first_name="John") | User.objects.filter(first_name="Jane")
Enter fullscreen mode Exit fullscreen mode

It's true that qs1 and qs2 have identical SQL queries:

In: str(qs1.query) == str(qs2.query)
Out: True
Enter fullscreen mode Exit fullscreen mode

and both produce:

SELECT "auth_user"."id", 
   ...
   "auth_user"."date_joined" 
FROM "auth_user" 
WHERE ("auth_user"."first_name" = John OR "auth_user"."first_name" = Jane)
Enter fullscreen mode Exit fullscreen mode

SQL query.

How to do a not equal filtering?
As an example, we will select all the users except ones whose

first_name is equal to John:

qs1 = User.objects.filter(~Q(first_name="John"))
Enter fullscreen mode Exit fullscreen mode

another way of doing the same query is using the exclude() method:

qs2 = User.objects.exclude(first_name="John")
Enter fullscreen mode Exit fullscreen mode

As you may guess, SQL queries are identical in this case too:

In: str(qs1.query) == str(qs2.query)
Out: True
Enter fullscreen mode Exit fullscreen mode

and the produced SQL query is:

SELECT "auth_user"."id", 
   ...
   "auth_user"."date_joined" 
FROM "auth_user" 
WHERE NOT ("auth_user"."first_name" = John)
Enter fullscreen mode Exit fullscreen mode

How to do IN filtering?
A typical example could be filtering users by the given list of IDs. Below is an example with the in expression being used:

qs = User.objects.filter(pk__in=[1, 4, 7])
Enter fullscreen mode Exit fullscreen mode
SELECT "auth_user"."id", 
   ...
   "auth_user"."date_joined" 
FROM "auth_user" 
WHERE "auth_user"."id" IN (1, 4, 7)
Enter fullscreen mode Exit fullscreen mode

In some cases it might be benefical to use in_bulk() method.
in_bulk(id_list=None, field_name='pk') takes a list of field values (id_list) and the field_name for those values, and returns a dictionary mapping each value to an instance of the object with the given field value. If id_list isn’t provided, all objects in the queryset are returned.
IMPORTANT! field_name must be a unique field, and it defaults to the primary key.
Example:

In: User.objects.in_bulk([1, 4, 7])
Out: {1: < User: John >, 4: < User: Jane >, 7: < User: Jemery >}
Enter fullscreen mode Exit fullscreen mode

How to do IS NULL or IS NOT NULL queries?
As an example, we will select only those users whose first_name is not specified(in other words it is NULL):

qs = User.objects.filter(first_name__isnull=True)
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
WHERE "auth_user"."first_name" IS NULL
Enter fullscreen mode Exit fullscreen mode

the opposite QuerySet:

qs = User.objects.filter(first_name__isnull=False)
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
WHERE "auth_user"."first_name" IS NOT NULL
Enter fullscreen mode Exit fullscreen mode

So, isnull takes either True or False, which correspond to SQL queries of IS NULL and IS NOT NULL, respectively.

How to do SQL “LIKE” equivalent queries?
The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
We will cover different cases one by one.
The first case will be selecting users whose first_name starts with Jo string:

qs = User.objects.filter(first_name__startswith="Jo")
Enter fullscreen mode Exit fullscreen mode

SQL equivalent:

SELECT ...
FROM "auth_user" 
WHERE "auth_user"."first_name"::text LIKE Jo%
Enter fullscreen mode Exit fullscreen mode

now let's select those users whose last_name ends with yan string:

qs = User.objects.filter(last_name__endswith="yan")
Enter fullscreen mode Exit fullscreen mode

SQL equivalent:

SELECT ...
FROM "auth_user" 
WHERE "auth_user"."last_name"::text LIKE %yan
Enter fullscreen mode Exit fullscreen mode

To demonstrate the last example for the % wildcard, we will select users whose last_name contains oh string:

qs = User.objects.filter(last_name__contains="oh")
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
WHERE "auth_user"."last_name"::text LIKE %oh%
Enter fullscreen mode Exit fullscreen mode

Unfortunatelly, there is no built-in tool for _(underscore wildcard). But, there is an alternative solution with the use of the regex lookup tool.

As an example, we will filter users whose last_name matches D_e patterns, which means that it should start with the letter D then follow with any single character and end with the letter e:

qs = User.objects.filter(last_name__regex=r"^D.e$")
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
WHERE "auth_user"."last_name"::text ~ ^D.e$
Enter fullscreen mode Exit fullscreen mode

As you can see, it's being converted to a regular expression syntax that is defined in the database backend.

All the tools in this section also support case insensitive lookups. Check startswith, istartswith, endswith, iendswith, contains, icontains, regex, iregex in the official docs for the details.

How to do comparison(>, >=, <, <=) operations?
Getting all users, whose id is greater than(>) 3 integer:

qs = User.objects.filter(id__gt=3)

SELECT ...
FROM "auth_user" 
WHERE "auth_user"."id" > 3
Enter fullscreen mode Exit fullscreen mode

Getting all users, whose id is less than(<) 4 integer:

qs = User.objects.filter(id__lt=4)

SELECT ...
FROM "auth_user" 
WHERE "auth_user"."id" < 4
Enter fullscreen mode Exit fullscreen mode

As you may guess, gt and lt are short aliases for greater than and less than, respectively.

And for doing greater than or equal or less than or equal lookups we just have to add e - equal at the end of the alias. So, gte will be converted to >= and lte will be converted to <=. More on this here.

How to do BETWEEN operation?
To implement BETWEEN operation we are going to use another Django's tool called range. And we are going to filter users whose date_joined(datetime) value is in a specific date time range. Before building the QuerySet let's prepare two timezone aware datetime objects:

import pytz
from django.utils import timezone

start = timezone.make_aware(timezone.datetime(year=2019, month=1, day=1), pytz.utc)
end = timezone.make_aware(timezone.datetime(year=2019, month=12, day=31), pytz.utc)
Enter fullscreen mode Exit fullscreen mode

the QuerySet:

qs = User.objects.filter(date_joined__range=[start, end])
Enter fullscreen mode Exit fullscreen mode

SQL equivalent:

SELECT ...
FROM "auth_user" 
WHERE "auth_user"."date_joined" BETWEEN 2019-01-01 00:00:00+00:00 AND 2019-12-31 00:00:00+00:00

FROM "auth_user" 
WHERE "auth_user"."date_joined" BETWEEN 2019-01-01 00:00:00+00:00 AND 2019-12-31 00:00:00+00:00
Enter fullscreen mode Exit fullscreen mode

FYI, you can use range anywhere you can use BETWEEN in SQL queries - for dates, numbers, and even characters. Note that the end of the range is inclusive.

How to LIMIT the number of results in queryset?
To implement SQL’s LIMIT and OFFSET clauses we should use a subset of Python’s array-slicing syntax on a QuerySet object. The example below returns the first 10 users(LIMIT 10):

qs = User.objects.all()[:10]
Enter fullscreen mode Exit fullscreen mode

SQL equivalent:

SELECT ...
FROM "auth_user" 
LIMIT 10
Enter fullscreen mode Exit fullscreen mode

tenth through twentieth users(LIMIT 10 OFFSET 10):

qs = User.objects.all()[10:20]
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
LIMIT 10 OFFSET 10
Enter fullscreen mode Exit fullscreen mode

At first glance you might think that this type of LIMIT OFFSET operations force to evaluate the QuerySet. But in general, slicing returns a new QuerySet object - it doesn’t evaluate the query. As we've noted at the beginning of this article, slices evaluate a QuerySet only if we use them with the step parameter. Both examples above are not evaluated - new QuerySets are being returned instead. An example of slicing with the step could be returning a list of every second object of the first 10 users:

list_of_every_second = User.objects.all()[:10:2]
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
LIMIT 10
Enter fullscreen mode Exit fullscreen mode

QuerySet for this operation is the same as for LIMIT 10 one above, but immediate evaluation and additional post-processing happened in this case in order to return every second object from the list of 10 objects.

To retrieve a single user rather than a list, use an index instead of a slice. For example, this returns the first User in the database:

In: User.objects.all()[0]
Out: < User: John >
Enter fullscreen mode Exit fullscreen mode

SQL equivalent:

SELECT ...
FROM "auth_user" 
LIMIT 1
Enter fullscreen mode Exit fullscreen mode

Note, that this approach will raise IndexError if there are no rows in the database.

It's important to note that negative indexing (i.e. User.objects.all()[-1]) is not supported on QuerySets.

The implemenaion of slicing/indexing is written inside of getitem method that you can find in QuerySet class located in django.db.models.query module.

How to ORDER results with QuerySets?
To order a QuerySet you need to use the order_by() method. Additionally, you should pass the field(s) that are going to be used in sorting to that method:

qs = User.objects.order_by('date_joined')
Enter fullscreen mode Exit fullscreen mode

SQL equivalent:

SELECT ...
FROM "auth_user" 
ORDER BY "auth_user"."date_joined" ASC
Enter fullscreen mode Exit fullscreen mode

Query will return the list of users ordered by date_joined field ascending. We can pass as many fields as we need, also, adding the -(dash) at the beginning of the field will order results in descending order. Example:

qs = User.objects.order_by('date_joined', '-first_name')
Enter fullscreen mode Exit fullscreen mode

SQL equivalent:

SELECT ...
FROM "auth_user" 
ORDER BY "auth_user"."date_joined" ASC,
         "auth_user"."first_name" DESC
Enter fullscreen mode Exit fullscreen mode

reverse() method will reverse the existing order of QuerySet. Calling reverse() second time will restore the ordering back to the normal direction:

qs = User.objects.order_by('date_joined', '-first_name').reverse()
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
ORDER BY "auth_user"."date_joined" DESC,
         "auth_user"."first_name" ASC
Enter fullscreen mode Exit fullscreen mode
In [1]: qs1 = User.objects.order_by('date_joined')
In [2]: qs2 = User.objects.order_by('date_joined').reverse().reverse()
In [3]: str(qs1.query) == str(qs2.query)
Out [3]: True
Enter fullscreen mode Exit fullscreen mode

To order randomly, use '?' like below:

qs = User.objects.order_by('?')
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
ORDER BY RANDOM() ASC
Enter fullscreen mode Exit fullscreen mode

How to get a single object from QuerySet?
The first approach is to use indexing as we used in the LIMIT section above:

How to get a single object from QuerySet?
The first approach is to use indexing as we used in the LIMIT section above:

In: User.objects.all()[0]
Out: < User: John >
Enter fullscreen mode Exit fullscreen mode

Similiar QuerySet we can construct using the get() method. get() method returns the object matching the given lookup parameters, and raises DoesNotExist exception if there is no match. It raises MultipleObjectsReturned exception if more than one object was found. Example:

In: User.objects.get(pk=1)
Out: < User: John >
Enter fullscreen mode Exit fullscreen mode

Also, If you expect a QuerySet to return a single row, you can use get() without any arguments.

There are 4 other handy methods that exist to help us when we want to get a single row/object with some conditions.

Indexing and get() approaches are "alarmic" in some cases and we might want to get the object if it exists, or None if it does not exist without catching any exceptions. And the first() method is very beneficial for that case:

user = User.objects.order_by('date_joined', '-first_name').first()
Enter fullscreen mode Exit fullscreen mode

the equivalent example using indexing:

try:
    user = User.objects.order_by('date_joined', '-first_name')[0]
except IndexError:
    user = None
Enter fullscreen mode Exit fullscreen mode

The second method is the last(), which works like first(), but returns the last object in the QuerySet:

user = User.objects.order_by('first_name').last()
Enter fullscreen mode Exit fullscreen mode
user = User.objects.order_by('first_name').reverse().first()
Enter fullscreen mode Exit fullscreen mode

SQLs for both queries will be the same.

It's worth to mention 2 other methods. latest(), that returns the latest object in the table based on the given field(s) and earliest() that works the same way but the opposite direction.

user = User.objects.latest('date_joined', '-first_name')
Enter fullscreen mode Exit fullscreen mode
SELECT ...
FROM "auth_user" 
ORDER BY "auth_user"."date_joined" DESC,
         "auth_user"."first_name" ASC
Enter fullscreen mode Exit fullscreen mode

Like get(), earliest() and latest() raise DoesNotExist if there is no object with the given parameters. As mentioned in the official docs, earliest() and latest() exist purely for convenience and readability.

$ python manage.py shell_plus --print-sql
>>> User.objects.first()
SELECT "auth_user"."id",
       "auth_user"."password",
       "auth_user"."last_login",
       "auth_user"."is_superuser",
       "auth_user"."username",
       "auth_user"."first_name",
       "auth_user"."last_name",
       "auth_user"."email",
       "auth_user"."is_staff",
       "auth_user"."is_active",
       "auth_user"."date_joined"
  FROM "auth_user"
 ORDER BY "auth_user"."id" ASC
 LIMIT 1
Execution time: 0.011151s [Database: default]
Enter fullscreen mode Exit fullscreen mode

Note that DEBUG in project settings must be set to True.

You can also specify SHELL_PLUS_PRINT_SQL configuration option in settings to omit the above command-line option:

# will print SQL queries in shell_plus
SHELL_PLUS_PRINT_SQL = True
Enter fullscreen mode Exit fullscreen mode

Commonly used SQL queries using Django ORM

SQL queries using Django ORM

ORM stands for Object Relation Mapper. Django ORM is a powerful and elegant way to interact with the database. The Django ORM is an abstraction layer that allows us to play with the database. In the end, Django ORM will convert all operations into SQL statements. In this piece, We will learn ORM of some common SQL queries.

Sample Models
Let’s define the sample model User.

from django.db import models
# Create your models here.
class User(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    age = models.IntegerField()
    city = models.CharField(max_length=255)
Enter fullscreen mode Exit fullscreen mode

I’ve created a few objects of the User table. Let’s start with commonly used Django ORMs.

Data in the User table

id | first_name | last_name | age | city
1  | sample     | 1         | 20  | city1 
2  | sample     | 2         | 25  | city2
3  | sample     | 3         | 30  | city3
Enter fullscreen mode Exit fullscreen mode

Common Django ORMs
Filter NULL values
‘__isnull’ is used to filter the null values in the Django ORM. It accepts True or False.

>>> User.objects.filter(age__isnull=True).values('id','age')
<QuerySet []>
>>> User.objects.filter(age__isnull=False).values('id','age')
<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>
Enter fullscreen mode Exit fullscreen mode

exists()
The exists() method is used to check the result of the query. Returns True if the queryset contains any results, and False if not.

>>> User.objects.filter(age__isnull=True).values('id','age').exists()
False
>>> User.objects.filter(age__isnull=False).values('id','age').exists()
True
Enter fullscreen mode Exit fullscreen mode

SQL ‘LIKE’ with Django ORM

>>> User.objects.filter(city__contains='city').values('id','city')
<QuerySet [{'id': 1, 'city': 'city1'}, {'id': 2, 'city': 'city2'}, {'id': 3, 'city': 'city3'}]>
Enter fullscreen mode Exit fullscreen mode

In the above query, __contains is used to search ‘city’ substring in the city column. Also, we can check the exact match using __exact.

>>> User.objects.filter(city__exact='city').values('id','city')
<QuerySet []>
Enter fullscreen mode Exit fullscreen mode

‘__startswith’ to check the start of the string.

‘__endswith’ to check the end of the string.

>>> User.objects.filter(city__startswith='city').values('id','city')
<QuerySet [{'id': 1, 'city': 'city1'}, {'id': 2, 'city': 'city2'}, {'id': 3, 'city': 'city3'}]>
>>> User.objects.filter(city__startswith='ity').values('id','city')
<QuerySet []>
>>> User.objects.filter(city__endswith='2').values('id','city')
<QuerySet [{'id': 2, 'city': 'city2'}]>
Enter fullscreen mode Exit fullscreen mode

Relational operators
gt -Greater than.

gte -Greater than or equal to.

lt -Less than.

lte -Less than or equal to.

>>> User.objects.filter(age__gt=20).values('id','age')
<QuerySet [{'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>
>>> User.objects.filter(age__gte=20).values('id','age')
<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>
>>> User.objects.filter(age__lt=25).values('id','age')
<QuerySet [{'id': 1, 'age': 20}]>
>>> User.objects.filter(age__lte=25).values('id','age')
<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}]>
Enter fullscreen mode Exit fullscreen mode

Select a few columns of the table
In Django ORM values() method is used to select a few column values of the table.

>>> User.objects.values('id')
<QuerySet [{'id': 1}, {'id': 2}, {'id': 3}]>
>>> User.objects.values('id','first_name','last_name')
<QuerySet [{'id': 1, 'first_name': 'sample', 'last_name': '1'}, {'id': 2, 'first_name': 'sample', 'last_name': '2'}, {'id': 3, 'first_name': 'sample', 'last_name': '3'}]>
Enter fullscreen mode Exit fullscreen mode

SQL ‘IN’ with Django ORM
‘__in’ is used to filter on multiple values.

>>> User.objects.filter(id__in=[1,2])
<QuerySet [<User: User object (1)>, <User: User object (2)>]>
Enter fullscreen mode Exit fullscreen mode

exclude()
Excludes objects from the queryset which match with the lookup parameters.

>>> User.objects.exclude(id=1)
<QuerySet [<User: User object (2)>, <User: User object (3)>]>
Enter fullscreen mode Exit fullscreen mode

Rename objects like ‘As’ in the SQL
The extra() method is used to rename columns in the ORM.

>>> User.objects.extra(select={'FirstName':'first_name','LastName':'last_name'}).values('FirstName','LastName')
<QuerySet [{'FirstName': 'sample', 'LastName': '1'}, {'FirstName': 'sample', 'LastName': '2'}, {'FirstName': 'sample', 'LastName': '3'}]>
Enter fullscreen mode Exit fullscreen mode

In this ORM, I’ve renamed first_name to FirstName and last_name to LastName.

Group By and Order By
The aggregate() function is used to perform aggregation operations like sum, average, min, max, etc.

>>> User.objects.aggregate(Sum('age'))
{'age__sum': 75}
>>> User.objects.aggregate(Avg('age'))
{'age__avg': 25.0}
>>> User.objects.aggregate(Max('age'))
{'age__max': 30}
>>> User.objects.aggregate(Min('age'))
{'age__min': 20}
Enter fullscreen mode Exit fullscreen mode

The aggregate() function works on the whole dataset only. Use annotate() instead of aggregate() if you want an average age group by city.

>>> User.objects.values('city').annotate(Sum('age'))
<QuerySet [{'city': 'city1', 'age__sum': 20}, {'city': 'city2', 'age__sum': 25}, {'city': 'city3', 'age__sum': 30}]>
Enter fullscreen mode Exit fullscreen mode

Also, we can sort a queryset using order_by().

>>> User.objects.values('city').annotate(total_age=Sum('age')).order_by('-total_age')
<QuerySet [{'city': 'city3', 'total_age': 30}, {'city': 'city2', 'total_age': 25}, {'city': 'city1', 'total_age': 20}]>
Enter fullscreen mode Exit fullscreen mode

We can rename the default name of the aggregation function output. Here, I’ve changed name total_age and used order_by on total_age. ‘-total_age’ is used for descending order.

Usually, in the database, we use the ‘HAVING’ clause with the group by queries. In the Django, we can use filter() function.

>>> User.objects.values('city').annotate(total_age=Sum('age')).filter(total_age__gt=20).order_by('-total_age')
<QuerySet [{'city': 'city3', 'total_age': 30}, {'city': 'city2', 'total_age': 25}]>
Enter fullscreen mode Exit fullscreen mode

In the output, city1 with a total_age 20 does not exist because of the filter.

Complex filters with Q objects
Q objects are used for AND, OR and NOT operations. Q objects provide complete control over the where clause of the query.

AND

‘&’ is used for AND operation between 2 Q expressions.

>>> User.objects.filter(Q(city='city1') & Q(age=25)).values('id','city','age')
<QuerySet []>
>>> User.objects.filter(Q(city='city1') & Q(age=20)).values('id','city','age')
<QuerySet [{'id': 1, 'city': 'city1', 'age': 20}]>
Enter fullscreen mode Exit fullscreen mode

This ORM will find all users with city=city1 and age= 25.

OR

‘|’ is used for OR operation between 2 Q expressions.

>>> User.objects.filter(Q(city='city1') | Q(age=25)).values('id','city','age')
<QuerySet [{'id': 1, 'city': 'city1', 'age': 20}, {'id': 2, 'city': 'city2', 'age': 25}]>
Enter fullscreen mode Exit fullscreen mode

This ORM will find all users having city=city1 or age= 25.

NOT

‘~’ is used for NOT operation with Q expression.

>>> User.objects.filter(Q(city='city1') & ~Q(age=20)).values('id','city','age')
<QuerySet []>
>>> User.objects.filter(Q(city='city2') & ~Q(age=20)).values('id','city','age')
<QuerySet [{'id': 2, 'city': 'city2', 'age': 25}]>
Enter fullscreen mode Exit fullscreen mode

The query will find all users with city=city2 and age is other than 20.

F() Expressions
In the Django QuerySet API, F() expressions are used to refer to model field values directly in the database.

>>> User.objects.annotate(new_age=F('age')*2).values('id','age','new_age')
<QuerySet [{'id': 1, 'age': 20, 'new_age': 40}, {'id': 2, 'age': 25, 'new_age': 50}, {'id': 3, 'age': 30, 'new_age': 60}]>
Enter fullscreen mode Exit fullscreen mode

We can also use F() expression in the filter.

>>> User.objects.filter(id__lt=F('age')).values('id','age')
<QuerySet [{'id': 1, 'age': 20}, {'id': 2, 'age': 25}, {'id': 3, 'age': 30}]>
>>> User.objects.filter(id__gt=F('age')).values('id','age')
Enter fullscreen mode Exit fullscreen mode


Update values
Performs an SQL update query for the specified fields, and returns the number of rows matched (which may not be equal to the number of rows updated if some rows already have the new value).

For example, multiple age column value with 2.

>>> User.objects.update(age=F('age')*2)
3
>>> User.objects.values('id','age')
<QuerySet [{'id': 1, 'age': 40}, {'id': 2, 'age': 50}, {'id': 3, 'age': 60}]>
Enter fullscreen mode Exit fullscreen mode

We can update a single object also.

>>> user=User.objects.get(id=1)
>>> user.age =100
>>> user.save()
Enter fullscreen mode Exit fullscreen mode

Delete object
Performs an SQL delete query on all rows in the queryset and returns the number of objects deleted and a dictionary with the number of deletions per object type.

Delete a single object

>>> user=User.objects.get(id=1)
>>> user.delete()
(1, {'django_orms.User': 1})
Enter fullscreen mode Exit fullscreen mode

Delete multiple objects

>>> User.objects.all().delete()
(2, {'django_orms.User': 2})
Enter fullscreen mode Exit fullscreen mode

By default, Django’s ForeignKey emulates the SQL constraint ON DELETE CASCADE — in other words, any objects with foreign keys pointing at the objects to be deleted will be deleted along with them. We can set the on_delete behavior in the models.

Top comments (0)