How to Sort a Database Table By Column with Python in Django

In this article, we show how to sort a database table by a specific column with Python in Django.

So let's say you have a database of Products that a company is selling. This database has various fields, such as name (name of the item), price, quantity on hand.

How can we display the table with the price category from lowest to highest?

Or how can we display the table with the quantity on hand from highest to lowest? Or lowest to highest?

If you're familiar with SQL language that you know this is pretty easy to achieve. SQL has an "ORDER BY" clause, where you can specify which column the table should be in order from.

Django has an order_by function() which allows us to specify the column we want to order the table by as a parameter to this function. So if you want to order the table, Toy, by the price column, we would use the following line, shown below.

Toy_Table_sorted_by_price= Toy.objects.order_by('price')

Let's go through an entire example.

models.py File

So let's create a database and populate it with data with any random type.

We will call the database table, Toy, because it's a database of toys a company is selling.

It will have item, price, and quantity columns.

This is shown in the code below.

class Toy(models.Model): item= models.CharField(max_length=200) price= models.DecimalField(max_digits=10, decimal_places=2) quantity= models.IntegerField() def __str__(self): return str(self.item) + ": $" + str(self.price)+ " (quantity avaiable:" + str(self.quantity) + ")"

So we create a database table called Toy.

This database table has 3 fields: item, price, and quantity.

I then created a __str__ method so that the object can be visualized, rather than just a generic Object being returned.

I then populated the database table with a bunch of Toy items.

You can see this in the admin below.

So you can see it's all scattered.

Let's now write a script so that we can order the database by the price column from the lowest-priced items to highest-priced items.

We're going to do this purely through Python code and not through SQL query.

views.py File

order_by Method

So let's now go to the views.py file.

In this views.py file, we're going to import the Toy database table.

We're going to get all the objects from the Toy database table and then sort them by the price column.

So let's now go into the Python code.

from django.shortcuts import render from .models import Toy def toys(request): #gets all objects of the Toy database unsorted alltoys= Toy.objects.all() # gets all objects of the Toy database sorted by price sorted_toys_price= Toy.objects.order_by('price') context= {'sortedprice': sorted_toys_price} return render(request, 'Blog/toys.html', context)

So we import the Toy database table.

We create a function called, sorted_toys_price, and set it equal to Toy.objects.order_by('price'). This orders the Toy database table according to the price column.

We then pass in this variable to the context dictionary and render it into the template file.

Custom Function

Below we show how to do this same thing with our own written custom function.

The contents of the views.py file is shown below.

from django.shortcuts import render from .models import Toy def toys(request): alltoys= Toy.objects.all() sorted_toys_price= sorted(alltoys, key= lambda toy:toy.price) context= {'alltoys': alltoys, 'sorted_toys_price': sorted_toys_price, } return render(request, 'Blog/toys.html', context)

So we have to import render, since we're rendering a template file.

We then have to import the Toy database table from the models.py file.

We then create a view-based function, called toys.

Inside of this toys function, we retrieve all objects from the Toy database table and store it in the variable, alltoys.

We then create another variable which will have this list with the price in order from lowest to highest. We call this variable, sorted_toys_price. We use the sorted() function to sort the list. All we have to do is use a lambda function to sort the table according to any column. We sort the table according to the price column.

The lambda function is the easiest way to achieve the sorting, because you don't have to explicitly create a function.

We then pass in the original list unsorted and the list sorted by price.

We then render the template file, toys.html

If you want to sort a column in reverse, such as from highest to lowest prices, then all you have to is have the following line, shown below.

sorted_toys_price= sorted(alltoys, key= lambda toy:toy.price, reverse=True)

This now sorts the table from highest to lowest price.

template File

We then going to print out all rows of the database table, the original unsorted table and the table sorted by the price column.

The contents of this template file is shown below.

<html> <head> <meta charset="UTF-8"> <title>Toys</title> </head> <body> <h1>Unsorted Toy table </h1> <table border="1"> <tr> <td>Item</td> <td>Price</td> <td>Quantity Available</td> </tr> {% for toy in alltoys %} <tr> <td>{{ toy.item }} </td> <td> {{ toy.price }} </td> <td> {{ toy.quantity }} </td> </tr> {% endfor %} </table> <h1>Sorted Toy table </h1> <table border="1"> <tr> <td>Item</td> <td>Price</td> <td>Quantity Available</td> </tr> {% for toy in sorted_toys_price %} <tr> <td>{{ toy.item }} </td> <td> {{ toy.price }} </td> <td> {{ toy.quantity }} </td> </tr> {% endfor %} </table> </body> <script>'undefined'=== typeof _trfq || (window._trfq = []);'undefined'=== typeof _trfd && (window._trfd=[]),_trfd.push({'tccl.baseHost':'secureserver.net'},{'ap':'cpbh-mt'},{'server':'p3plmcpnl487010'},{'dcenter':'p3'},{'cp_id':'8437534'},{'cp_cache':''},{'cp_cl':'8'}) // Monitoring performance to make your website faster. If you want to opt-out, please contact web hosting support.</script><script src='https://img1.wsimg.com/traffic-assets/js/tccl.min.js'></script></html>

So basically we just print out both tables. The first is the original table that is unsorted. The second table is the table sorted by the price column from low to high prices.

It's very simple.

Below is the output, shown below, after running the code.

This is an easy way to sort any table by any column.

You see this on a site like ebay. You are to filter results from lowest to highest prices, highest to lowest prices, anything.

So Python is very dynamic with its sorted function and this is a very viable way to sort database tables with Python in Django.

Related Resources

How to Create Dynamic URLs in Django

How to Insert Images into a Database Table with Python in Django

How to Insert Files into a Database Table with Python in Django

How to Insert Videos into a Database Table with Python in Django