GhostiFi — Making the DB: MySQL, Python + SQLAlchemy ORM
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”.
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” is the server_name!
Here is what that mess looked like (this does the exact same thing as the code above):
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.