SQLAlchemy is a Better Way to Run Queries

When I first started writing Python code, very few of my projects involved
connecting to a database. This was at a time in my coding career where I
didn’t see a use for storing large amounts of data. Therefore, database
usage mainly went ignored. It wasn’t until I started my internship that
I finally found the need to leverage this power. However, even then, it
took quite a while to integrate them into my applications.

Like all coding noobs, my first queries heavily relied on the use of
hard-coded SQL expressions in my code. Fortunately, in Python, you can
have something as simple as this:

query = "SELECT * FROM BookList;"

This query isn’t too bad. However, that quickly changes when you want to start passing arguments to it. For example:

query = "SELECT * FROM BookList WHERE Author = " + authorVar + ";"

Expressions like this were all too common in my early days. What I didn’t realize, was that there is a really big problem when using this.

As you probably noticed, there is a variable called “authorVar”. Pretty much anything can be assigned to it, which means that the query could potentially return unexpected results. More importantly, this kind of expression leaves you wide open for SQL Injection attacks.

As my experience with querying databases in Python improved, I learned that a query like this was much safer:

query = "SELECT * FROM BookList WHERE Author = '%s'"
authorVar = "An Amazing Author"
cursor.execute(query, authorVar)

This method is known as “Parameterized Querying”. It separates the parameters from the query (a.k.a. Prepared Statement) and helps prevent against SQL Injection attacks.

A bonus is that it also helps improve query speed. Querying databases like this proved to be very effective for quite a while. I could apply the same tactic of separating the query from the parameter to insert, update, and delete statements as well.

However, in college, I discovered a new and even better way to run queries, SQLAlchemy. Since I had more of a C# background, queries in SQLAlchemy looked very similar to LINQ queries:

books = session.query(BookList).filter(BookList.Author == authorVar)

After making the switch to SQLAlchemy, I was able to heavily increase development times for my applications. It’s also a lot more secure than hardcoded SQL expressions. What’s even better, is that I was able to utilize this in insert and update statements too!

Looking at the example code I created, we have an Insert and an Update example. For an insert, we start by creating a “BookList” object and pass our data to it.

After that, we pass our BookList object to the “add()” function and commit it to the database using the “commit()” function. Keep in mind, it also possible to do bulk insert statements too. Using SQLAlchemy seems a lot easier and safer than using something like this:

query = "INSERT INTO BookList(Book, Author) VALUES ("A Book", "An Author");"

Parameterized Query version

query = "INSERT INTO BookList(Book, Author) VALUES (%s, %s);"
cursor.execute(query, ("Test Book", "Test Author"))

As for the example update statement that I created, it’s pretty straightforward. You start by using SQLAlchemy’s way of selecting records. The output is in the form of an object and that will get assigned to the “bookToUpdate” variable. You can then make your necessary changes using “bookToUpdate.Book” or “bookToUpdate.Author”. The last thing that needs to be done is to run the “commit()” function. You can see what it would look like when used in an unsafe way:

query = "UPDATE BookList SET Book = 'Changing Title' WHERE Book = 'A Really Good Book';"

Parameterized Querying version

query = "UPDATE BookList SET Book = %s WHERE Book = %s"
cursor.execute(query, ("Changing Title", "A Really Good Book"))

While I was experimenting, I did discover some downsides. First off, SQLAlchemy is an Object Relational Mapper (ORM for short), and does a lot of “Black Magic”. In other words, it does a lot of stuff under the hood that isn’t viewable to the developer. Knowing SQL will help in knowing what’s going on. Next, there is a little bit of a learning curve when creating your statements. Select, Insert, Update, and Delete are pretty straight forward, but Joins can be a little more of a pain.

To sum things up, I have become completely sold on SQLAlchemy. Like I mentioned earlier, development time has heavily increased positively. The evolution of getting here though took quite a few years and multiple iterations using different techniques. However, this current result is one that will last for a long time. That is until the next new technology comes along. Cheers!

Previously published behind a paywall.