The world of Data Sciences’ is an ever-changing place, new applications and requirements appear on a daily basis. With all that, a professional SQL-guru who can optimize their interactions with databases is valued in his weight in gold. Luckily for us we have just such master.

 

In this post we hope to explore the world of ORMs (particularely Django ORM vs SQL Alchemy) with our Python specialist and get his opinion on which he prefers! And provide some nifty examples to boot.

 

Hello, my name is John

 

Hello World! Pleased to make your acquaintance; Friends call me Ion, Ivan, Vanea but you, stranger, can call me “Mr. John” 😀

 

 

A little bit about me – I am a developer worth 2 years of experience in various projects, proficient in: Django, FastAPI, Flask, Laravel, PostgresSQL, MySQL, MongoDB among other things. Lately, however, I focus most of my workflow on Django with my wonderful team, here at EBS Integrator.

 

So, there I am, day by day, perfecting my craft with Django Rest framework and I am super satisfied about my choice, but my curiosity got the best of me and I decided to try “FastAPI” with SQL Alchemy. And boy oh boy am I glad I did; because I liked it, A LOT, and certainly will make sure to make the best of my discovery in the future projects.

 

For this article, I decided to share my experience through “show and tell comparison” and make a simple database that contains tables with different object relationships. I will show examples of how SQL Alchemy and Django ORM generate SQL with the same end-goal, in different ways.

 

Choose your “ORM”

 

Here is a short summary of things we will discuss...

 

Object Relational Mapper (aka ORM)

 

In our case, an ORM is a tool, or better said an interpreter, used by developers to work with SQL and in turn, interact with data bases. Allowing you to pull/request – query – data from the database in your preferred programming language, in our case it’s Python. This doesn’t make vanilla SQL obsolete, in some cases with sufficiently complex application structures and requirements, you absolutely must write in your own SQL sequences.

 

In all other instances I suggest you use an ORM. A happy developer is a developer who knows what’s going on in his code. And using an ORM in your preferred language is one way to do it. Next It hides the SQL or any other database query away from application logic.

 

Finally, it helps in maintenance, sometimes you simply don’t have enough SQL specialists around, so it helps keeping everything in as close to Python as possible, for your teammates.

 

Django ORM

Django ORM is a part of a bigger Python framework called, you guessed it, Django. Built by experienced professionals, and designed for rapid development and clean design. Django takes away the hassle of web development, allowing you to focus on writing your app from the get-go.

 

It's lightweight, fast, secure, and scalable. And its integrated ORM is equally robust.

 

SQL Alchemy

 

 

SQL Alchemy (aka SQLA) is a dedicated Python SQL toolkit with an optional dedicated-ORM that gives your app the full benefit and flexibility of SQL for your Python code.

 

Designed with complementarity-oriented approach; It puts on the fore front what other ORM/tools want to hide. It exposes everything within series of composable tools and takes on the job of automating redundant tasks, giving larger degree of freedom to the developer of how the database is organised and how SQL is generated.

 

Differences – SQL Alchemy vs Django

 

Main difference is that Django ORM uses the “active record implementation”, and SQL Alchemy uses “data mapper implementation”.

 

​​​​​​​​​​​​​​

 

It means that Django ORM cannot use our models for queries if every row is not linked with the overall model object. This allows for more readability and allows not predefining the schema to use the properties in the code. Also you can call “just save()” as all properties are mapped to a specific row in the table.

 

Which is a different story with SQL Alchemy because there is a separation between the database structure and the object structure.

 

Consequentially, this separation makes it a little harder to save records in the DB but enables more flexibility, for there is no direct relationship between the code and the database.

 

Yet another difference between these two frameworks, is that Django can create primary keys automatically for your tables.

 

Whereas SQL Alchemy won’t do that. You will have to manually create them for each table. It’s both a pro and a con. When it comes to handling “relationship” Django ORM lacks in versatility compared to SQLA.

 

But I will show that in practice with our further examples.

 

5 Examples – Django vs SQLA

 

For our bout. Let us create a data model, both in SQL Alchemy and Django ORM.

 

As a use-case, we will consider the most basic process that happens in a university: evaluating the academic performance of a student. Of course, we are taking a barebone model here that allows us to register all the students for a course, compute their grades for different courses and pull relevant information in regards to their achievements.

 

We’ll make use of “one to many” and “many to many” relationship queries to illustrate in what instances Django and SQL Alchemy differ and give my subjective opinion on who does it better.

 

Here’s how our Django ORM model looks like:

 

​​​​​​​

 

And here’s our SQLA sample:

 

 

And right off the bat you can see the difference in syntax, and ultimately that’s the whole gist of this piece. But let us go round by round.

 

Round 1: A simple select of students’ table of contents

 

Our first example will fall under the “one to many” relationship. Simply put, it will screen all registered students and the “Title” with his register.

 

 

 

 

In this case, I personally think that Django is more approachable/readable but SQL Alchemy gives us more opportunities such as “lazy=’joined’”.

 

# all options available here

 

• lazy = ‘select’ (or True)

 

lazy = ‘select’ is the default setting. It emits a SELECT statement when loading.

 

• lazy = ‘dynamic’

 

When querying with lazy = ‘dynamic’, a separate query gets generated for the related object.

 

• lazy = ‘joined’ (orFalse)

 

lazy = ‘joined’ literally joins the two tables and returns the results.

 

• lazy = ‘subquery’

​​​​​​​

• lazy = ‘subquery’ is the same as ‘joined’. **

 

** Except that subquery uses subquery directly in model property in case it needs to be loaded every time when “model is query”.

 

In Django’s case you can do this only by overriding the model object manager.

 

The original code might be more appealing in Django but if we look at the “generated SQL” we notice that SQLA does a better job by providing more information regarding object relationship.

 

So, let’s give both contenders a point and call it draw for first round!

 

Round 2: Let us find a student that goes into the register for “English”.

 

Yep, we are exploiting another “one-to-many” relationship here to screen how many students took-up the “English course”:

 

 

 

 

 

There is not much to say here, aside the fact that Django is not as verbose hence I prefer it in this case, so point goes to Django!

 

Round 3: Let’s look for students that got at least one grade equal to 10, regardless of the course

 

 

 

 

​​​​​​​

 

Same as the example before, except we’re exploiting a “many to many” relationship, but the point stands that Django simply is more understandable and straightforward.

Point to Django.

 

Round 4: Now, let’s select all the students, and for each, screen the courses where that student got a 10

 

​​​​​​​

 

 

 

 

In this example we can see that SQL Alchemy is better, and what is surprising – more elegant when it comes to retrieving SQL Data for “many to many” relationships.

 

Django generates two “selects” for one attribute, requiring more performance, more code to accomplish the same task SQLA does it, admirably, in just 1 “select”.

 

Point goes to SQL Alchemy.

 

Round 5: Identifying the “high rollers” in academic achievements

 

Here we will pull students on a requirement of at least 3 courses with a grade gte=10.

 

 

 

​​​​​​​

 

​​​​​​​

 

First, notice how Django creates redundant statements for the same result, we get the COUNT line twice and it just looks messy.

 

SQL Alchemy’s generated sequence, looks great and readable, no redundancies.

 

Point to SQLA!

 

Conclusion

 

And the winner is = Friendship!

 

Most of the time SQL Alchemy is more functional and flexible with what you want, allowing greater precision, working with databases. And can even generate some queries that Django cannot.

 

Django on other hand, looks more readable and prettier, and it’s a massive pro when it comes to us, Pythonists! However, it seems that is not always as you might have noticed in this bout.

 

I like both ORMs and I predict I will be using them throughout different projects and even both for specific circumstances, and I advise you to do the same!

 

But, end of the day, when it comes to super complex tasks nothing trumps down vanilla SQL.