How Django Does the Heavy Lifting for SQL


Python, and particularly Django, are very important for development extra environment friendly programs, with much less code, and attached to a highly-scalable database. I’m right here to speak to you about decreasing the on a regular basis friction that incorporates development and supporting trendy programs the usage of SQL and Python to summary away complexity and make our jobs and lives a little bit bit more straightforward.

With out coming into the weeds, we will be able to suppose:

  • SQL is optimized for SQL databases
  • Python is now not optimized for SQL databases

This workout immediately helps the Figuring out Databases e book and my new Linode LIVE! tutorial sequence the usage of Python and Pythonic tooling to execute instructions in uncooked SQL with out the want to in fact write SQL. I’m the usage of Django’s knowledge modeling, however the syntax is similar to the SQLAlchemy package deal in Python.

Let’s get began!
Right here’s an instance of a Django knowledge style:

elegance BlogArticle(fashions.Fashion):
    consumer = fashions.ForeignKey(Person, default=1, on_delete=fashions.SET_DEFAULT)
    name = fashions.CharField(max_length=120)
    slug = fashions.SlugField(clean=True, null=True)
    content material = fashions.TextField(clean=True, null=True)
    publish_timestamp = fashions.DateTimeField(

Let’s suppose this style lives in a Django app referred to as Articles (Django apps are necessarily parts that make up the whole lot of a Django undertaking).

So now we’ve two names to paintings with:

  • Articles (app title)
  • BlogArticle (style title)

Together, those translate to the SQL Desk title:


Django does this magic for us.

If we had been the usage of the MySQL shell we’d see:

| Tables_in_cfe_django_blog_db |
| articles_blog_article        |
| auth_group                   |
| auth_group_permissions       |
| auth_permission              |
| auth_user                    |
| auth_user_groups             |
| auth_user_user_permissions   |
| django_admin_log             |
| django_content_type          |
| django_migrations            |
| django_session               |
11 rows in set (0.01 sec)

Now let’s take a look at the columns in our Django style:

mysql> DESCRIBE articles_blog_article;
| Box                  | Kind         | Null | Key | Default | Additional          |
| identity                     | bigint       | NO   | PRI | NULL    | auto_increment |
| name                  | varchar(120) | NO   |     | NULL    |                |
| slug                   | varchar(50)  | YES  | MUL | NULL    |                |
| content material                | longtext     | YES  |     | NULL    |                |
| publish_timestamp      | datetime(6)  | YES  |     | NULL    |                |
| user_id                | int          | NO   | MUL | NULL    |                |
12 rows in set (0.00 sec)

With the exception of writing a fundamental configuration, Django did all the SQL paintings for us on this MySQL database.At this level, Django hasn’t executed anything else very spectacular. Actually, maximum of this workout isn’t designed to spotlight Django or Python as an SQL alternative, however to remind you of the truth that abstractions paintings.

The Upward push of Python and the Value of Friction

Let’s communicate concerning the trail of least resistance and why I imagine Python is without doubt one of the perfect tactics to leverage SQL.

It’s really easy to write down, learn, run, AND send Python. Trade “Python” to just about some other programming paradigm and it’s virtually unattainable to make the similar commentary. JavaScript could also be a contender, nevertheless it’s additionally persistently at a loss for words for Java. I perceive those are generalizations, and aren’t at all times true, however are not unusual problems that arise when creating your app.

I imagine those generalizations have a tendency to be true because of Python’s English-like syntax for doing issues.

Let’s examine a SQL commentary to a Python and Django commentary:

  • SQL: SELECT * from articles_blog_article;
  • Python and Django: pieces = BlogArticle.items.all()

Each statements yield the very same knowledge. The Python commentary, on the other hand, returns a listing of Python items (pieces) that almost any Python developer throughout a spread of revel in can use. The uncooked SQL effects would want to be transformed ahead of being utilized in a Python app.

Box Descriptions
If we take a better take a look at this SQL box description:

| Box                  | Kind         | Null | Key | Default | Additional          |
| name                  | varchar(120) | NO   |     | NULL    |                |

As opposed to this Django box description:

name = fashions.CharField(max_length=120)

  • Which one has extra friction?
  • Which one is more straightforward to grasp what’s happening?

Which gives simply sufficient data?

If you weren’t a coder and noticed varchar(120), what would you’re making of that? I’m lovely certain it’s worthwhile to a minimum of bet what max_length=120 way. The cool factor is they imply the very same factor: prohibit this box to 120 characters or much less.

Including Knowledge to the Database

With Django:

    name="Hi Global",
    content material="Coming Quickly",

With SQL:

INSERT INTO `articles_blog_article` (`user_id`, `name`, `slug`, `content material`, `publish_timestamp`) 
VALUES (1, 'Hi Global', 'hello-world', 'Coming Quickly', NULL);

In relation to simplicity and readability, I believe the winner above is undoubtedly Django and Python. name = "Hi Global" is more straightforward than understanding what’s happening with the similar column (box) worth in SQL. Make no mistake, the way in which that is written in SQL could be very efficient while you know what you’re doing.

Including More than one Traces
With Django:

pieces = [
    BlogArticle(title="Hello Again 0", slug='hello-again-0', content="Coming Soon"),
    BlogArticle(title="Hello Again 1", slug='hello-again-1', content="Coming Soon"),
    BlogArticle(title="Hello Again 2", slug='hello-again-2', content="Coming Soon"),
    BlogArticle(title="Hello Again 3", slug='hello-again-3', content="Coming Soon"),
    BlogArticle(title="Hello Again 4", slug='hello-again-4', content="Coming Soon"),

With SQL:

INSERT INTO `articles_blog_article` (`user_id`, `name`, `slug`, `content material`, `publish_timestamp`) 
VALUES (1, 'Hi Once more 0', 'hello-again-0', 'Coming Quickly', NULL),
    (1, 'Hi Once more 1', 'hello-again-1', 'Coming Quickly', NULL),
    (1, 'Hi Once more 2', 'hello-again-2', 'Coming Quickly', NULL),
    (1, 'Hi Once more 3', 'hello-again-3', 'Coming Quickly', NULL),
    (1, 'Hi Once more 4', 'hello-again-4', 'Coming Quickly', NULL);

Once more, the Python code is extra readable whilst the SQL code provides extra perception into the true knowledge. And, all over again, Python is penning this SQL code for us the usage of the Django code above. Beautiful neat, huh?

The explanation I dove into this side-by-side comparability isn’t to select, which is one of the simplest ways to leverage SQL databases, however to spotlight Python’s talent to lend a hand scale back the overhead of finding out to write down uncooked SQL immediately.

There are a number of Python applications they necessarily write the uncooked SQL for you, right here’s a couple of of them:

  • Django
  • Pandas
  • SQLAlchemy
  • Polars
  • Dask
  • Vaex
  • Python’s integrated CSV Module
  • Tortoise ORM
  • Pony ORM
  • SQLObject

Django Does the Heavy Lifting
Object-relational mapping applications (regularly known as ORMs) are the name of the game sauce for a way Python can leverage SQL databases. I bring to mind an ORM as a intermediary that is helping transfer knowledge round in any given programming language’s local syntax.

Previous on this workout, we began to look how this translated to Django, however let’s extend on that now.

Think we’ve knowledge in our database, we will be able to write a command like this:

my_post = BlogArticle.items.first()
This commentary will question our database, extract the knowledge, load it into an example of a Python Elegance, then assign it to the variable my_post.

From right here, we will be able to now do one thing like this:

# the usage of a django-managed python shell
# by way of python set shell
>>> print(
Hi Global

On this case, we used dot notation to get entry to the name box that was once declared within the BlogPost Django style from the former segment. This box corresponds to a column in our SQL database desk articles_blog_article.

Because of the ORM, we will be able to do that:

>>> = "any other name"

Inside of this Python shell consultation instance, the will now at all times be "any other name". The underlying SQL database, on the other hand, nonetheless acknowledges this very same knowledge as Hi Global. The database will stay the unique knowledge till Python in the end commits (aka .save()) this information trade to the database. If Python by no means commits this information, it’s going to by no means be up to date within the database.This is a part of the magic of the ORM. We will use and trade the knowledge with out affecting the true saved knowledge. After we wish to trade what’s in fact taking place within the database we run:

>>> = "any other name back"

After working .save() our database, for this actual row, will replace the column name to check precisely what’s written as a Python string above. Don’t omit that the .save() approach is in particular for making commits to the database in Django fashions. .save() doesn’t in fact imply anything else to a Python Elegance with out it first inheriting a kind Django Fashion Elegance.

Construction with Django, MySQL, and Linode

That is one of the examples of ways Django does the heavy lifting for you. When you’re thinking about a contemporary way to deploying Django programs on Linode at the side of a controlled MySQL database, GitHub Movements for CI/CD, Terraform, and Ansible, take a look at the next content material now to be had on Linode:

To lend a hand get you began, the Coding for Marketers GitHub has a repository of code that is going with every step within the sequence. Excellent success, and you’ll want to let me know the way issues are going by way of Twitter @JustinMitchel.


Supply hyperlink


Leave a Reply

Your email address will not be published. Required fields are marked *