How to Manipulate Raw Queries in Django

We will learn, with this explanation, about what the raw() method does and how we can manipulate raw queries, and we will also learn how to inject SQL within our applications in Django.

Manipulate Raw Queries With the Help of the raw() Method in Django

Using the raw() method in Django allows us to take or build SQL queries and execute them. But this is not the only way of running SQL queries in Django; instead of utilizing the default form setup, we can also, if we want to, bypass the form and just run SQL queries.

But why did we mention that? Because the model manager raw() method should generally be your first option when you want to execute raw SQL queries.

That is because the structure of the raw query set class instance is very similar to what you have been working with the query set class instance. We can perform other actions in the raw queries, such as indexing and slicing.

So let’s start with a really simple example. Let’s go ahead and create the new function called STUDENT_DATA().

We will use the simple data set and the Student model in our models.py file.

class Student(models.Model):

    FIRST_NAME = models.CharField(max_length=100)
    SR_NAME = models.CharField(max_length=100)
    age = models.IntegerField()
    CLASS_ROOM = models.IntegerField()
    TEACHER = models.CharField(max_length=100)

    def __str__(self):
        return self.FIRST_NAME

Now we’ll go back to the views.py file and create a function called STUDENT_DATA() to demonstrate the raw SQL.

Inside the function, we will take all the data from the Student table using Student.objects.all(). This will return all the student data from this table and store it in SD_DATA.

Let’s print this object. We will also utilize connection.queries that will give the output of SQL and some performance measurements.

def STUDENT_DATA(request):

    SD_DATA = Student.objects.all()

    print(SD_DATA)
    print(connection.queries)
    return render(request, "output.html", {"data": SD_DATA})

Let’s go ahead and run the server. The browser will display all the data, and then below, we can see that we got the SQL query we executed.

Let’s go ahead and create an equivalent using the raw() method. So, again we will use student and its objects, but this time we will use raw().

We will need to run the SQL query inside this method: SELECT * FROM student_student.

We do not need to select all these individual items, so we can use the star to select all the table fields, and then the next clause is FROM, which helps to find the table. Then we will define the table name that is called student_student.

SD_DATA = Student.objects.raw("SELECT * FROM student_student")

Let’s go back into the browser and refresh it. Then we will see that we are returning these items from the database using a Select statement.

Now we will move forward and expand this slightly and select an individual item. To do this, we will need to use the WHERE clause; after one space, we select the age attribute and pass it as a value of 21.

SD_DATA = Student.objects.raw("SELECT * FROM student_student WHERE age=21")

Let’s rerun the Django server and refresh the browser. Then we will see the SQL is working and returns a single item from the database.

We have seen how to inject our SQL into the raw() function of Django and how we can perform actions on the database.

In the Django documentation, if you look through the Model instant reference and read through, it will give you some information about a deferred model. The term deferred model instances mean the fields emitted from the query until we load them on demand.

To print the data, we need to specify what data we want to see in the output, so to do that, we will need to use a for loop.

for d in Student.objects.raw("SELECT * FROM student_student"):
    print(d)

If we rerun this, look into the console because we are printing this out and returning the three names from the table.

You can find more complex queries here. For example, a concept called making a query set means we can limit the number of objects we want to return.

Now we will declare a new variable called sql, then store SQL query in it and pass it inside the raw() function. After the raw() function, we will limit two rows by slicing the objects.

sql = "SELECT * FROM student_student"
SD_DATA = Student.objects.raw(sql)[:2]

When we have a look at the output, it is just returning the two rows.

Complete Source Code of the views.py File:

from django.shortcuts import render
from .models import Student
from django.db import connection


def STUDENT_DATA(request):

    SD_DATA = Student.objects.all()
    sql = "SELECT * FROM student_student"
    SD_DATA = Student.objects.raw(sql)[:2]
    # for d in Student.objects.raw('SELECT * FROM student_student'):
    #     print(d)

    print(SD_DATA)
    # print(connection.queries)
    return render(request, "output.html", {"data": SD_DATA})

We used the following code inside our template.

{{data}}

<hr/>

{% for i in data %}
    <div>{{ i.FIRST_NAME }} - {{ i.age }}</div>
{% endfor %}