Exercise 45: Creating an ORM

The final exercise in the SQL part of the book is a big jump. You know the basics of the SQL language with one database. You also should be proficient with Python's OOP. It's now time to combine these two and create an Object Relational Manager (ORM). An ORM's job is to take plain old Python classes and translate them into stored rows in a database's tables. If you've ever used Django, you've used their ORM to store your data. In this exercise you'll attempt to reverse engineer how to do that.

Exercise Challenge

In the real world if a programmer working for me asked to create their own ORM I'd say, "No way. Use an existing one." Work situations are different from educational situations because someone is paying you to get things working. You can't really justify using your work time to create things that honestly do not benefit your employer. However, your own personal time is all for you and, being a beginner, you should be attempting to re-create as many classic pieces of software as you can.

Creating an ORM will teach you about many problems related to the mismatch between object-oriented concepts and SQL. There's many things that SQL can model which classes tend to stumble on. There's also the problem of everything is a table in SQL. Attempting to create your own ORM will teach you so much about both SQL and OOP that I recommend spending a good amount of time crafting the best one you can.

Some key features you should have in your ORM are:

• It should be safe to pass a string to your ORM from outside. If you are using f-strings to craft your SQL, you are doing it wrong. The reason is, if you do f"SELECT * FROM {table_name} then someone can externally set table_name to SQL like person; DROP TABLE person. Your database will most likely run this and destroy everything or worse. Some databases even let you run system commands inside SQL. This is called a "SQL injection," and you should not have it in your ORM.

• All the CRUD operations but in Python. I recommend you skip the CREATE TABLE portion until you get everything else to work. Simple INSERT, SELECT, UPDATE, and DELETE are easy to craft, but creating the scheme of a database from class definitions involves some major Python voodoo to really work. Use a hand crafted .sql file to create your database, and then once you have everything else working you can attempt a scheme system to replace the .sql file.

• Matching Python types to SQL types as well as new types to handle SQL types. You may find that you have to do some juggling to fit Python's data types into your SQL tables. Maybe that's too much of a pain so you end up making all your own data types. This is what Django did.

• Transactions would be an advanced topic, but attempt it if you can get through this.

I'll also say that in this exercise you can steal features from as many projects as you like. Feel free to look at Django's ORM when working on your design. Finally, I highly recommend you start with only getting an ORM that works with the little database you created in this part of the book. Once you get something working with that database, you can then work on generalizing it to work with any database.