Python, and especially Django, are essential for building more efficient applications, with less code, and connected to a highly-scalable database. I’m here to talk to you about reducing the everyday friction that comes with building and supporting modern applications using SQL and Python to abstract away complexity and make our jobs and lives a little bit easier.
Without getting into the weeds, we can assume:
- SQL is optimized for SQL databases
- Python is not optimized for SQL databases
This exercise directly supports the Understanding Databases ebook and my new Linode LIVE! educational series using Python and Pythonic tooling to execute commands in raw SQL without the need to actually write SQL. I’m using Django’s data modeling, but the syntax is very similar to the SQLAlchemy package in Python.
Let’s get started!
Here’s an example of a Django data model:
class BlogArticle(models.Model):
user = models.ForeignKey(User, default=1, on_delete=models.SET_DEFAULT)
title = models.CharField(max_length=120)
slug = models.SlugField(blank=True, null=True)
content = models.TextField(blank=True, null=True)
publish_timestamp = models.DateTimeField(
auto_now_add=False,
auto_now=False,
blank=True,
null=True,
)
Let’s assume this model lives in a Django app called Articles (Django apps are essentially components that make up the entirety of a Django project).
So now we have two names to work with:
Articles(app name)BlogArticle(model name)
In combination, these translate to the SQL Table name:
articles_blog_article
Django does this magic for us.
If we were using the MySQL shell we’d see:
mysql> SHOW TABLES;
+------------------------------+
| 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 look at the columns in our Django model:
mysql> DESCRIBE articles_blog_article;
+------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| title | varchar(120) | NO | | NULL | |
| slug | varchar(50) | YES | MUL | NULL | |
| content | longtext | YES | | NULL | |
| publish_timestamp | datetime(6) | YES | | NULL | |
| user_id | int | NO | MUL | NULL | |
+------------------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)Aside from writing a basic configuration, Django did all of the SQL work for us in this MySQL database.At this point, Django hasn’t done anything very impressive. In fact, most of this exercise is not designed to highlight Django or Python as an SQL replacement, but to remind you of the fact that abstractions work.
The Rise of Python and the Cost of Friction
Let’s talk about the path of least resistance and why I believe Python is one of the best ways to leverage SQL.
It’s very easy to write, read, run, AND ship Python. Change “Python” to nearly any other programming paradigm and it’s almost impossible to make the same statement. JavaScript is also a contender, but it’s also consistently confused for Java. I understand these are generalizations, and aren’t always true, but are common issues that come up when developing your app.
I believe these generalizations tend to be true due to Python’s English-like syntax for doing things.
Let’s compare a SQL statement to a Python and Django statement:
- SQL:
SELECT * from articles_blog_article; - Python and Django:
items = BlogArticle.objects.all()
Both statements yield the exact same data. The Python statement, however, returns a list of Python objects (items) that nearly any Python developer across a range of experience can use. The raw SQL results would need to be converted before being used in a Python app.
Field Descriptions
If we take a closer look at this SQL field description:
+------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+----------------+
| title | varchar(120) | NO | | NULL | |
+------------------------+--------------+------+-----+---------+----------------+
Versus this Django field description:
title = models.CharField(max_length=120)
- Which one has more friction?
- Which one is easier to understand what’s going on?
Which provides just enough information?
If you were not a coder and saw varchar(120), what would you make of that? I’m pretty sure you could at least guess what max_length=120 means. The cool thing is that they mean the exact same thing: limit this field to 120 characters or less.
Adding Data to the Database
With Django:
BlogArticle.objects.create(
title="Hello World",
content="Coming Soon",
slug="hello-world",
publish_timestamp=None,
)With SQL:
INSERT INTO `articles_blog_article` (`user_id`, `title`, `slug`, `content`, `publish_timestamp`)
VALUES (1, 'Hello World', 'hello-world', 'Coming Soon', NULL);When it comes to simplicity and clarity, I think the winner above is definitely Django and Python. title = "Hello World" is easier than figuring out what’s going on with the equivalent column (field) value in SQL. Make no mistake, the way this is written in SQL is very effective when you know what you’re doing.
Adding Multiple Lines
With Django:
items = [
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"),
]
BlogArticle.objects.bulk_create(items)With SQL:
INSERT INTO `articles_blog_article` (`user_id`, `title`, `slug`, `content`, `publish_timestamp`)
VALUES (1, 'Hello Again 0', 'hello-again-0', 'Coming Soon', NULL),
(1, 'Hello Again 1', 'hello-again-1', 'Coming Soon', NULL),
(1, 'Hello Again 2', 'hello-again-2', 'Coming Soon', NULL),
(1, 'Hello Again 3', 'hello-again-3', 'Coming Soon', NULL),
(1, 'Hello Again 4', 'hello-again-4', 'Coming Soon', NULL);Again, the Python code is more readable while the SQL code gives more insight into the actual data. And, yet again, Python is writing this SQL code for us using the Django code above. Pretty neat, huh?
The reason I dove into this side-by-side comparison is not to pick, which is the best way to leverage SQL databases, but to highlight Python’s ability to help reduce the overhead of learning to write raw SQL directly.
There are several Python packages they essentially write the raw SQL for you, here’s a few of them:
- Django
- Pandas
- SQLAlchemy
- Polars
- Dask
- Vaex
- Python’s built-in CSV Module
- Tortoise ORM
- Pony ORM
- SQLObject
Django Does the Heavy Lifting
Object-relational mapping packages (commonly referred to as ORMs) are the secret sauce for how Python can leverage SQL databases. I think of an ORM as a middleman that helps move data around in any given programming language’s native syntax.
Earlier in this exercise, we started to see how this translated to Django, but let’s expand on that now.
Assume we have data in our database, we can write a command like this:
my_post = BlogArticle.objects.first()
This statement will query our database, extract the data, load it into an instance of a Python Class, then assign it to the variable my_post.
From here, we can now do something like this:
# using a django-managed python shell
# via python manage.py shell
>>> print(my_post.title)
Hello WorldIn this case, we used dot notation to access the title field that was declared in the BlogPost Django model from the previous section. This field corresponds to a column in our SQL database table articles_blog_article.
Thanks to the ORM, we can do this:
>>> my_post.title = "some other title"
Within this Python shell session example, the my_post.title will now always be "some other title". The underlying SQL database, however, still recognizes this exact same data as Hello World. The database will keep the original data until Python finally commits (aka .save()) this data change to the database. If Python never commits this data, it will never be updated in the database.This is part of the magic of the ORM. We can use and change the data without affecting the actual stored data. When we want to change what’s actually happening in the database we run:
>>> my_post.title = "some other title again"
>>> my_post.save()After running .save() our database, for this particular row, will update the column title to match exactly what is written as a Python string above. Don’t forget that the .save() method is specifically for making commits to the database in Django models. .save() doesn’t actually mean anything to a Python Class without it first inheriting a form Django Model Class.
Building with Django, MySQL, and Linode
This is one of many examples of how Django does the heavy lifting for you. If you’re interested in a modern approach to deploying Django applications on Linode along with a managed MySQL database, GitHub Actions for CI/CD, Terraform, and Ansible, check out the following content now available on Linode:
- Watch our Understanding Databases Video Tutorial Series on Linode LIVE!
- Download the Understanding Databases: Extended Edition Ebook
To help get you started, the Coding for Entrepreneurs GitHub has a repository of code that goes with each step in the series. Good luck, and be sure to let me know how things are going via Twitter @JustinMitchel.
Comments