Django ORM and Queryset

This article covers all aspects of Django Queryset and its built-in ORM (Object Relational Mapper). Once you master Django Queryset, you can write one universal query for any database engine connected.

What is Django Queryset?

queryset is simply a list of objects from the Django Models. When using the Django ORM creating a new row in the table is simply like creating a new object of Model class. Django ORM maps these Model objects to Relational database query.

Any SQL query can be written easily as Django queryset. You can separately test the query for Create, Filter, Update, Order, etc. in Django Shell or in the views.py.

The syntax for a queryset is always like:

ModelName.objects.method_name(arguments)

For example, to get all data from the model Blog under app named myapp, start a Python shell and run the following:

python manage.py shell 

>>> from myapp.models import Blog
>>> queryset = Blog.objects.all()
QuerySet [...]
                                

Methods in Queryset API

There are several methods under the Django Queryset API. Some methods return another queryset object and thus can be followed by another methods.

queryset_object  = Model.objects.all().filter()

These queryset objects needs to be accessed iteratively in the template pages with the following syntax:

    { % for i in queryset_object % }
    { {i.fieldname} }
    ...
    { % endfor % }

On the other hand, some queryset methods do not return a new queryset or simply return data items. For example:

ueryset_object  = Model.objects.get(id=1)

The result of a get query can be accessed in the template pages without using any loop, or simply with the below syntax.

{ {queryset_object.fieldname} }

Django Queryset methods that return another queryset

  • filter()
  • exclude()
  • annotate()
  • alias()
  • order_by()
  • reverse()
  • distinct()
  • values()
  • values_list()
  • dates()
  • datetimes()
  • none()
  • all()
  • union()
  • intersection()
  • difference()
  • select_related()
  • prefetch_related()
  • extra()
  • defer()
  • only()
  • using()
  • select_for_update()
  • raw()

Django Queryset methods that do not return another queryset

  • get()
  • create()
  • get_or_create()
  • update_or_create()
  • bulk_create()
  • bulk_update()
  • count()
  • in_bulk()
  • iterator()
  • latest()
  • earliest()
  • first()
  • last()
  • aggregate()
  • exists()
  • contains()
  • update()
  • delete()
  • as_manager()
  • explain()

SQL query vs Django ORM queryset

SQL Query Django ORM Queryset
select * from table; Model.objects.all()
select * from table where fieldname=value; Model.objects.filter(fieldname=value)
select distinct column_names from table; Model.objects.distinct(column_names)
insert into table(field1, field2, …) values (value1, value2, …); Model.objects.create(field1=value1, field2=value2, …)
update table set column_name = value where condition; Model.objects.filter(condition).update( column_name=value)
delete from table where condition Model.objects.filter(condition).delete()
select columns from table order_by column asc|desc; Model.objects.filter(condition).order_by (column_for_asc, -column_for_desc)
select columns from table where conditon1 and condition2 Model.objects.filter(condition1, condition2)
select columns from table where conditon1 or condition2 Model.objects.filter(condition1) | Model.objects.filter(condition2)
select columns from table where not condition Model.obejcts.exclude(condition)
select max(column) from table; Model.obejects.filter(column).max()
select min(column) from table; Model.obejects.filter(column).min()
select avg(column) from table; Model.obejects.filter(column).avg()
select sum(column) from table; Model.obejects.filter(column).sum()
select count(column) from table; Model.obejects.filter(column).count()

CRUD Queryset Example using Todo App

models.py

    from django.db import models
    
    class Todo(models.Model):
        title = models.CharField(max_length=120, unique=True)
        description = models.TextField(blank=True)
        status = models.BooleanField(default=False)
        def __str__(self):
            return self.title
                                

views.py

    from django.shortcuts import render, redirect
    from .models import Todo
                                
    def add_task(request):
            if request.method=="POST":
                title = request.POST['title']
                q = Todo.objects.create(title=title)
                q.save()
                return redirect('/')
            return render(request, 'add_task.html', {})  
                                      
     def tasks(request):
            q = Todo.objects.all()
            return render(request, 'tasks.html', {'tasks':q})
      
                                      
     def update(request, id):
            selected_task = Todo.objects.get(id=id)
            if request.method=="POST":
                form = TodoForm(request.POST, instance=selected_task)
                if form.is_valid():
                    form.save()
                    return redirect('/tasks/')
            else:
                form = TodoForm(instance=selected_task)
            return render(request, 'update_task.html', {'form':form})
                                      
      def delete(request, id):
            task = Todo.objects.get(id=id)
            task.delete()
            return redirect('/tasks/')   
                                      

Search field

index.html

    <form> 
        <input type="text" name="search" autocomplete="off" required> 
        <input type="submit" value="Search"> 
    </form>
                                

views.py

    def search_function(request):
        search = request.GET.get('search')
        q = Model.objects.filter(fieldname=search)
        return render(request, 'index.html', {'q':q})