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

Reilly Chase
Nov 13, 2018 · 2 min read
Image for post
Image for post


In this post I create the database for GhostiFi’s server table in MySQL, and begin writing 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:

Image for post
Image for 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 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

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store