No Need to Ever Write SQL Again: SQLAlchemy’s ORM for Absolute Beginners

With this ORM you can create a table, insert, read, delete and update data without writing a single line of SQL

Mike Huls

Nov 1, 2022 — 6 min read

How beautiful your life developing with an ORM will be (image by Christian Joudry on Unsplash)

There are numerous advantages to using SQLAlchemy’s ORM over executing raw SQL. In this article we’ll demonstrate the basics of using the ORM. After reading this article you’ll be able to create tables, insert, select, update, and delete data. Understanding the ORM will save you many errors and a lot of time debugging so let’s not waste any more time and let’s code!

What is an ORM and what does it do?

ORM stands for Object Relational Mapping. It’s a technique that serves as some sort of translator between your Python code and your SQL tables. It basically allows you to use records from a database table as Python objects in Python. Think of it as translating your table to a list where each entry is a Python-object that represents a row.

Using an ORM has many advantages:

database-agnostic: Your code works even when migrating databases
less error-prone: No syntax errors because the SQL is generated
extra functionalities via the models (e.g. validation)
organization: by keeping all models neatly organized in your project

Now we know it’s a good idea to use an ORM, let’s find out how to.

Using the ORM

In this part we’ll focus on the basics and use the SQLAlchemy ORM to:

create a table
Insert a record
Select a record
Delete a record
Update a record

Notice: SQLALchemy needs a database-engine that allows Python to read from our database. Check out the short article below on how to create one.

1. Creating a table

In this part we’ll define the model for our table. In the part below we create a Python object in the form of the Student class and then actually create the table using SQLAlchemy.

It’s easy as that! The Students class describes our table: the name of the table, the schema and the configuration of each column.

Next we import declarative_base. This is a function that returns a base class that all table models have to inherit from. Think of it as supplying toolbox to all of your models. Then we use the Base ‘toolbox’ to create_all tables using our database-connection.

Notice that this is the only place where we define our table structure. This means you can bundle all or your table models together in stead of having raw SQL all throughout your project.

2. INSERT a record using the ORM

In this part we’ll use the model that we’ve defined above to insert some new records in our newly created table.

We create a new student by creating a new instance of the Student class from before. Then we use the imported sessionmaker to add and commit it to our table. This is not only very easy; notice that we don’t write a single line of SQL!

3. SELECT records using the ORM

Selecting data is even easier than inserting:

Defining our statement is pretty easy using our model. Notice that we add a limit. When using a dbEngine that connects to a Postgres database this gets compiled to SELECT * FROM students LIMIT 5 .
If we switch to a SQL Server dbEngine this statement will be compiled to SELECT TOP 18 * FROM students . This is the power of the database-agnostic ORM!

Next we execute the statement in a session and loop through the results:Student mike is 33 years old

4. DELETE a record

Deleting data closely resembles selecting:

Notice that we apply some filtering to our delete statement. We can of course do the same in the select statement from the previous part.

5. UPDATE a record

Updating is also simple as this:

The difference with the delete and select statement is that we also add the new values that we’re updating the selected records with. These are neatly passed on line 6.

Even more advantages of the ORM: validation

Since we’ve defined our table in the class and also use the class to create new records we can add some pretty advanced validation. Image that someone tries to add a student that’s 300 years old. We can easily update out Student table model to validate the provided age:

As you see we check out if the provided age is larger than 110 if throw an error if so. This prevents this record from being inserted into our database.

What about relations?

At the moment we have just one table: students. Most applications have more tables that are inter-related. A student is part of a class and has multiple courses. Each course has one teacher etc.

The SQLAlchemy ORM easily allows you to query related tables in a single statement: selecting a student will then also return all classes, courses and teachers related to the student. Find out how this works in the next article by following me!

Next steps:

With our knowledge of the database-engine and the ORM we can focus on our next steps.

In future article’s we’ll explore how to dynamically generate models in the ORM and how to work with tables that are related via foreign keys so make sure to follow me.

Conclusion

In this article we’ve explored the start of SQL Alchemy; we know how to connect to the database and how to execute raw SQL. Also we’ve discussed the benefits of using the ORM over executing raw SQL. In the next article we’ll check out the ORM and how to use it.

I hope everything was as clear as I hope it to be but if this is not the case please let me know what I can do to clarify further. In the meantime, check out my other articles on all kinds of programming-related topics like these:

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!