Contact Information

KodeBlog

We're Available 24/ 7. Drop us an email.

Django Database Queries

Most applications that you will work on will need to interact with a database in the background. Database operations may involve writing records, retrieving, updating and deleting. You may also want to order the results returned from the database in either ascending or descending order based on your needs. This lessons introduces beginners to database queries in Django.

Topics to be covered

We will cover the following topics in this lesson

  • Project Database
  • Inserting Records
  • Select Single Record
  • Select All Records
  • Ordering Query Results
  • Filtering Select Results
  • Updating Records
  • Deleting Records
  • Summary

Project Database

This lesson builds on the previous one where we created a database using models and migrations. We also created some dummy records into our database. We will use the previous database to run the queries in this lesson. If you have not completed the previous lesson then you should do so before proceeding with thos one.

To make things simple, we will work with the Django interactive shell program to perform all of our operations. Later on in the course, we will look at how to do that using views and models.

You can open the interactive shell by running the following command

python manage.py shell

You should be able to see the following program in your terminal

Django Interactive Console

The above program loads up the Django framework and allows us to execute the application code directly from the terminal.

Inserting Records

We will create some records in the Authors table. Run the following code

from authors.models import Author
a = Author(name='Uncle Bob')
a.save()

HERE,

  • from authors.models import Author imports the Author model from the authors app.
  • a = Author(name='Uncle Bob') creates an instance variable of the model Author with Uncle Bob as the value of the attribute name.
  • a.save() calls the save method of the instance variable a which persists the record to the database.

If you check the database table authors, you should be able to see an author record with the name of Uncle Bob.

You can also run the following command in the shell

print (a)

You should be able to see the following results

Uncle Bob

You can also create a record using the following method

Author.objects.create(name='Martin Fowler')

HERE,

  • Author.objects.create(name='Martin Fowler') calls the create method of the objects object passing in the value of name as an argument. This creates a record in the database.

You should be able to see the following result after executing the above command

<Author: Martin Fowler>

Select Single Record

In this section, we will query the database to retrieve a single record. The best practice is to use the primary key when you want to retrieve a single record. Django also provides a way of getting a single record using other columns but precautions must be taken when using this method.

We will get the author with the id value of 1.

Run the following command

Author.objects.get(id=1)

HERE,

  • Author.objects.get(id=1) calls the get method of objects passing in 1 as the argument to the parameter id.

You should be able to get the following output in the terminal

<Author: Uncle Bob>

Let's now look at how we can get a single record using the name field

Author.objects.get(name='Martin Fowler')

HERE,

  • Author.objects.get(name='Martin Fowler') retrieves a single record from the database that has the value of Martin Fowler. The downside of this method is if more than one record is found especially if the column is not unique, then Django will throw an error.

If you query for a record that does not exist like

Author.objects.get(id=-1)

The above code queries for an id value of -1 that does not exist. Django responds to this by throwing the following error

authors.models.Author.DoesNotExist: Author matching query does not exist.

Select All Records

Let's now look at how to retrieve all records from the database. Django ORM provides a simple method of doing this.

Run the following code

Author.objects.all()

HERE,

  • Author.objects.all() calls the all method of the objects object which runs the SELECT * FROM table query. This method returns a QuerySet result.

The above code produces the following results

<QuerySet [<Author: Uncle Bob>, <Author: Martin Fowler>]>

Ordering Query Results

Let's now look at how we can order results in either ascending or descending order.

Order By Ascending Order

By default, the method order_by orders records in ascending order so you just jave to call it like so.

Author.objects.order_by('name')

HERE,

  • Author.objects.order_by('name') the order_by method accepts column names that you want to order the results by.

You should be able to see the following results

<QuerySet [<Author: Martin Fowler>, <Author: Uncle Bob>]>

Order By Descending Order

In this section, we will work with the above example but order the results in descending order using the name column.

Author.objects.order_by('-name')

HERE,

  • Author.objects.order_by('-name') notice we placed a - before name. This tells Django ORM to order the results in descending order.

The above code produces the following results

<QuerySet [<Author: Uncle Bob>, <Author: Martin Fowler>]>

Filtering Select Results

It is very common to filter results based on a search query. For example, if you create an online store, users may search for a product that matches a specified term. You will then have to construct a query and return results that meet the criteria. In this section, we will cover that.

We will search for all records that contain the letter e in the author name.

Author.objects.filter(name__contains='e')

HERE,

  • Author.objects.filter(name__contains='e') the filter method is used to define condition for where clause. name__contains='e' adds a condition that retrieves authors with name value that contains the letter e. The SELECT query would be like SELECT * FROM authors WHERE name LIKE '%e%'

The above query returns the following results

<QuerySet [<Author: Uncle Bob>, <Author: Martin Fowler>]>

If you want to get record(s) with exact search term, you can do it like so

Author.objects.filter(name='Uncle Bob')

HERE,

  • Author.objects.filter(name='Uncle Bob') calls the filter method with the argument value of Uncle Bob to the parameter name. The SELECT query will be like SELECT * FROM authors WHERE name = 'Uncle Bob'.

The above code produces the following results

<QuerySet [<Author: Uncle Bob>]>

Updating Records

We will now update the author record with the id value of 1.

Author.objects.filter(id=1).update(name='Robert Cecil Martin')

HERE,

  • The above code calls the filter method to retrive a record with an id value of 1 then chains the update method on it which sets the value of the field name to Robert Cecil Martin then saves the record to the database.

Run the following command to get the name of the author record

Author.objects.get(id=1)

You should be able to get the following results

<Author: Robert Cecil Martin>

You can also check in the database and the record will also be updated there.

Deleting Records

We will now delete the record with the id value of 2

Author.objects.filter(id=2).delete()

HERE,

  • The delete method is used to permanently delete records from the database.

The above code produces the following results

(1, {'authors.Author': 1})

Let's now retrieve all records from the database like so

Author.objects.all()

The above code produces the following results

<QuerySet [<Author: Robert Cecil Martin>]>

Notice the record Martin Fowler is not included because it has been deleted.

Summary

In this lesson, we learnt how to query database records using Django ORM. We covered the basics of creating data, retrieving, updating and deleting records. Django ORM offers far much more powerful features that we did not cover in this lesson but it is a beginner's guide. We will look at more advanced features as we proceed with the lessons.

Author: Rodrick Kazembe

Rodrick is a developer who works on Desktop, Web and Mobile Applications. He is familiar with Python, Java, JavaScript, C++, C#, Kotlin, PHP, Python and the list goes on. Rodrick enjoys sharing knowledge especially when it comes to technology.

Django Models

Django Admin Interface

Leave A Reply