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!

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.

https://gist.githubusercontent.com/reillychase/260e832609c22575c57f37b2c3608022/raw/da981d756aef7f7dfbee3d91d13153d405ab7cfb/server.py

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).

https://gist.githubusercontent.com/reillychase/df0fe45e405e381e9c78fd76b4cee867/raw/be9697365acd2fd671bdedee2916e4282522b272/hostifi-server.py

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.