Open In App

Raw SQL queries in Django views

Last Updated : 24 Apr, 2025
Comments
Improve
Suggest changes
Like Article
Like
Report

Let's create a simple Django project that demonstrates the use of raw SQL queries in a view. In this example, we'll create a project to manage a list of books stored in a database. We'll create a view that retrieves books from the database using a raw SQL query and displays them on a webpage.

Setting up the project

We assume that Django is already installed in your system, If not follow the link

Starting the Project Folder

To start the project use this command:

django-admin startproject resumeuploader
cd resumeuploader

To start the app use this command

python manage.py startapp myapp

Now add this app to the 'settings.py'

Screenshot-from-2023-09-30-12-43-03

Setting up the Files

model.py: Here we have created a simple Book table where we have 3 fields title, author and publication.

Python3 
from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.CharField(max_length=100)
    publication_year = models.PositiveIntegerField()

    def __str__(self):
        return self.title

admin.py: Register your model here.

Python3 
from django.contrib import admin
from .models import Book

# Register your models here.
admin.site.register(Book)

views.py: Here we wil try to understand the two ways to execute SQL quries -

  • Executing custom SQL directly
  • Use of Manager.raw() to fetch Data in Django

Executing custom SQL directly

The custom_query_view function defines a raw SQL query, executes it using Django's database connection, processes the results, constructs an HTML table, and returns that table as an HTTP response. This allows you to display the queried data on a webpage when the view is accessed through a URL.

Python3 
from django.http import HttpResponse
from django.shortcuts import render
from django.db import connection

def custom_query_view(request):
  # books is app name and book is model
    query = "SELECT * FROM books_book;"

    with connection.cursor() as cursor:
        cursor.execute(query)
        results = cursor.fetchall()

    table_html = "<table><tr><th>Title</th><th>Author</th><th>Publication Year</th></tr>"
    for row in results:
        table_html += f"<tr><td>{row[1]}</td><td>{row[2]}</td><td>{row[3]}</td></tr>"
    table_html += "</table>"

    # Pass the table_html to the template
    return render(request, 'index.html', {'table_html': table_html})  
  

def home(request):
    return HttpResponse('Hello, World!')

Or Instead of using the database cursor, we use Book.objects.raw() to execute the raw SQL query and return a queryset of Book model instances.

Use of Manager.raw() to fetch Data in Django

Manager.raw() is a useful feature in Django that allows you to execute raw SQL queries and return model instances. Let me show you how to modify your code to use Manager.raw() instead of the direct database cursor approach:

Python3 
from django.http import HttpResponse
from django.shortcuts import render
from .models import Book  

def custom_query_view(request):
    # Use Manager.raw() to perform a raw SQL query and return model instances
    books = Book.objects.raw("SELECT * FROM books_book")

    # Pass the queryset to the template
    return render(request, 'index2.html', {'books': books})

def home(request):
    return HttpResponse('Hello, World!')

If you want to execute more SQL query then you can add as follows:

  • Update: "UPDATE books_book SET year=value,WHERE year=1959 "
  • Delete: "DELETE books_book WHERE year=1078".

You can also try to use Django shell command to fetch data from the database.

>>> Person.objects.raw(
...     """
...     SELECT first AS first_name,
...            last AS last_name,
...            bd AS birth_date,
...            pk AS id,
...     FROM some_other_table
...     """
... )

Creating GUI

index.html ( custom SQL directly ): Create an HTML template to display the books using the custom SQL.

HTML 
<!DOCTYPE html>
<html>
<head>
    <title>Book List</title>
</head>
<body>
    <h1>Book List</h1>
    <!-- Display the HTML table generated in the view -->
    {{ table_html | safe }}

</body>
</html>

index2.html ( Using Manager.raw() ): Create an HTML template to display the books using the Raw SQL.

HTML 
<table>
  <tr>
    <th>Title</th>
    <th>Author</th>
    <th>Publication Year</th>
  </tr>
  {% for book in books %}
    <tr>
      <td>{{ book.title }}</td>
      <td>{{ book.author }}</td>
      <td>{{ book.publication_year }}</td>
    </tr>
  {% endfor %}
</table>

books/urls.py: Create a URL pattern for this view in books/urls.py:

Python3 
from django.urls import path
from . import views

urlpatterns = [
    path('home/', views.home, name='home'),
    path('', views.custom_query_view, 
         name='custom_query_view'),
]

urls.py: Include the books/urls.py in the project's urls.py

Python3 
from django.contrib import admin
from django.urls import path, include


urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include('books.urls')),
]

Deployement of the project

Before running the development server, create a superuser and add an data in the Django admin panel and then run these commands to apply the migrations:

python manage.py createsuperuser
python3 manage.py makemigrations
python3 manage.py migrate

Run the server with the help of following command:

python3 manage.py runserver

Admin Pannel

Adding data from the admin pannel to the database.

Screenshot-from-2023-09-30-12-42-09


Output

Screenshot-from-2023-09-30-12-41-37



Next Article
How to implement history.back() in AngularJS?

P
prakritjvla
Improve
Article Tags :
Practice Tags :

Similar Reads

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox