Django ORM to SQL Query

Introduction

"Ever wondered how Django, the magic wand of web development, talks to databases behind the scenes? In this article, we're taking a peek under the hood to see how Django's secret language, known as Object-Relational Mapping (ORM), transforms our friendly Python queries into something called SQL. Ready to demystify the magic? Let's dive in!"

Let's start by creating a virtual environment and installing all the packages that we will need.

# create the virtual environment
virtualenv venv
# Activate the venv
source venv/bin/activate
# install the packages
pip install django
pip install pygments
pip install sqlparse

Let's create a Django Project & Django Application

django-admin startproject django_orm
python manage.py startapp recipes

After we create our Project and App, Let's create our Model in recipes/models.py

from django.db import models
from django.utils.translation import gettext_lazy as _

class Recipe(models.Model):
    name = models.CharField(_("Recipe Name"), max_length=150)
    description = models.TextField(_("Recipe Description"))

    def __str__(self) -> str:
        return self.name

let's create migrations to apply these changes to our database

python manage.py makemigrations
python manage.py migrate

We have our Database Model and are ready to dive into the Python shell and see how ORM Query decoded into SQL Statement.

python manage.py shell

# Import Our Model and packages into our python shell  
from recipes.models import Recipe
from pygments import highlight
from pygments.formatters import TerminalFormatter
from pygments.lexers import PostgresLexer
from sqlparse import format

Now let's use the shell to create some ORM Queries

recipe = Recipe.objects.create(name="Recipe 1 Name", description="Recipe 1 Description")
# select all recipes
recipes = Recipe.objects.all()

After we had objects in our model and a query that gets all recipes, we now can see how it decoded into SQL Query.
In our shell:

sql_query = str(recipes.query)
sqlformatted = format(sql_query, reindent=True)
# we can pretty print our sql query with TerminalFormatter & PostgrsLexer
print(highlight(formatted_sql, PostgresLexer(), TerminalFormatter() ))

And Ta Da, You will see a pretty printed SQL query for our recipes query

SELECT "recipes_recipe"."id",
       "recipes_recipe"."name",
       "recipes_recipe"."description"
FROM "recipes_recipe"

Conclusion

"In this exploration, we've taken a glimpse into the inner workings of our Django ORM, witnessing how it seamlessly translates high-level object-oriented queries into the language of databases through SQL statements. This brief example serves as a window into the powerful yet often hidden process of ORM decoding, offering developers valuable insights into the SQL queries that underlie their Django applications. As we continue to demystify the connections between ORM and SQL, we unlock a deeper understanding of our data interactions and pave the way for more effective and optimized database interactions in our projects."