GhostiFi — Making the DB: MySQL, Python + SQLAlchemy ORM

Overview

In this post I create the database for GhostiFi’s server table in MySQL, and begin writing server.py by defining SQLAlchemy classes for Server and Subscription tables. In the end, I was able to do a few queries on them using the ORM!

I hope this real-world example is helpful for someone who is trying to understand what an ORM is, and how/why to use it.

Creating the MySQL Database

I used Adminer, a MySQL GUI similar to PHPMyAdmin, to create the server table which I defined in my last post:

Connecting Python + SQLAlchemy ORM

After that, I defined the subscription and server tables as SQLAlchemy Python classes:

This looks super clean, and is a huge improvement compared to what I was doing with HostiFi.

Why you should use an ORM

With HostiFi I wrote raw MySQL queries, and turned the results into arrays of arrays (lists of lists) instead of classes. Instead of “print server.server_name” like in the example above, I would write “print row[6]”.

It was way more code, and became very confusing to maintain and expand. I would have to scroll up and down and count the attributes just to figure out that “row[6]” is the server_name!

Here is what that mess looked like (this does the exact same thing as the code above):

Closing thoughts

If you have any feedback on how I could improve this please let me know in the comments section!

I am also looking for feedback on GhostiFi, as well as beta testers. Please sign up for the newsletter at https://ghostifi.net if you are interested.

If you want to know when I release another post about building GhostiFi, you can follow me on Twitter: @_rchase_ or Medium: Reilly Chase