Python Web and App

Automated Object-Relational Mapping MySQL → Python

During my Data Management course at Howest University, we learned the ins and outs of the Data Manupilation Language. Translated: CRUD. Translated: Create Read Update Delete.

However, to access your database, and be able to execute these queries from your serivce (like a website), an exact replica of each database table has to be created. The emphasis here lies on exact. The main 3 parameters are:

  • Datatypes
  • Limits
  • Where are nulls allowed?

This sounds very straight forward, but we have to keep in mind that datatypes in MySQL are not exact matches of datatypes in Python.

Take for instance CHAR (50), or VARCHAR (50), or DECIMAL (8,2), or tinyint, smallint, mediumint, tinytext, mediumtext, … The list goes on and on. These have to be processed and validated carefully in the setter function of each property, which mirrors one column of one table. Below are some examples. These functions have been generated by the script and have not been altered.

decimal (8,2) datatype validation
decimal (10,2) datatype validation
datetime datatype validation
smallint datatype validation

When your database has a lot of tables, which have a lot of columns, this can rapidly turn into a lot of work. On top of that, there is also a very high risk to commit human-errors. Both of these I thought could by automation.

We had also learned you could export your database to the humanly-readable .sql format. This meant that all data to reconstruct the database was there, in plain text. This could then easily be converted using my personal favourite: Python.

Example of the content of a .sql file

After all data was extracted, it was just a matter of generating the correct Python code for the models, and their respective repositories.

For the repositories I included 2 basic MySQL queries

  • Read all
  • Read single (Using the primary key)

These are set up in a specific way, to eleminate any SQL injections. The screenshot below also shows you the unaltered output of the script.

Lastly, I have included 2 basic helper functions in the generation to process the result from the database repository.

If you are interesed in the code, and/or want to test it out yourself, this link will take you to the Github repo. Any suggestions / improvements are highly encouraged and appreciated.

If you would like to know more about this project, please contact me and we can talk about it in more detail.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *