How I created a mini ORM with python?

Introduction

When you code in a Django web framework, we all know that you won’t directly work with databases. There is an ORM (Object relational mapping) who will interact with database using migration and SQL operation. So, I want in this tutorial to show you how to implement an ORM manager from scratch.

Object–relational mapping (ORM) in computer science is a technique for converting data between incompatible type systems using object-oriented programming languages in order to create virtual database objects.

Python is a programming language that enables you to design freely anything that you want.

And honestly I was able to implement a Mini ORM manager merely within 3 hours.

Agenda

• Project Design
• Database manager
• Model manager
• Migration manager
• Conclusion

Project Design

Our Project is divided into 3 main components:

• A connection manager to connect directly with a database using SQL commands (we used SQLite3 in our case).
• A model manager or model file that contains all the definition of the models we need to migrate to a database.
• A simple command manager that enables user to input a command (in the command prompt).

The Project files are also 3 python files (check this Github repository ):

Database manager

We mentioned before that we will use SQLite database. Thanks to python sqlite3 default library, we can connect directly with a database using a python script. If you check the documentation of sqlite3 library, you will notice that it is very easy to connect SQLite using python.

Just a few lines of python code will do the job. So, you need to instantiate an sqlite3 connection object with specifying the database file “example.db”, then make a cursor to execute SQL commands without forgetting to commit the changes and to close the connection.

First import the sqlite3 library and instantiate the connection:

import sqlite3
con = sqlite3.connect('example.db')

Then open a cursor, execute the commands you want and finally close the connection:

cursor= con.cursor()
cursor.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''')
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
connection.commit()
connection.close()

However, this kind of implementation won’t be reliable in our project, because there is too much redundancy of this block of code. Imagine I will repeat this code with every SQL operation I use including CREATE, DELETE, UPDATE and RETRIEVE.

In fact, this problem has a solution, there is a thing in python called context manager. Context manager is a way to manage resources precisely without any cost in complexity and memory.

In order to make a context manager that can connect with a database, we make a class called ConnectionSqliteManager that opens an SQL connection and instantiates the cursor object.

In this class, we need to write an __enter__ and __exit__ magic class methods as a necessary ingredient for database resource management.

So the structure of the context manager class is as follows :

• The __enter__ method connects the example.db file (setup operation) and returns the Connection object to variable Connection.
• The __exit__ method takes care of closing the connection on exiting the with block(teardown operation).

In the scope with , you manage database within the scope, without taking the bother of opening and closing the connection.

class ConnectionSqliteManager:
    def __init__(self,filename):
        self.filename = filename

    def __enter__(self):
        print("Connection started ...")
        self.connection = sql3.connect(self.filename)
        self.cursor = self.connection.cursor()
        return self  
    def __exit__(self, type, value, traceback):
        print("Connection ended ...")
        self.connection.close()

with ConnectionSqliteManager("example.db") as Connection:
    # Do what you want with Connection instance

TIP:

You can make a decorator (Inside “ConnectionSqliteManager” class) on top of each SQL operation method, to commit the changes.

Never forget to add some sqlite3 error exception in each command execution.

#inside ConnectionSqliteManager class
def commit(operation):
        def wrapper(self, *args,**kwargs):
            operation(self, *args, **kwargs)
            self.connection.commit()
        return wrapper
@commit
def Sql_operation(self):
    # execute an sql command here
    pass

Model manager

A model is a datastore entity that has a key and a set of properties. . A model is a Python class that inherits from the Model class. The model class defines a new kind of datastore entity and the properties the kind is expected to take.

I won’t dive into too much implementation here. all you have to do is to define a model class like the following:

class Model(baseModel):
    base_model = base
    tablename = "Model"
    fields = ("field_1", "field_2")

In this code, you need to specify the name of the table of the database and its fields.

In order to prepare your model for migration, you have to add it in the model_list list:

model_list = [Model,
              ]

Command manager

Now let’s prepare the user for a good interface in our command prompt. To implement it, we used the argparse default library to let the user input the arguments in the command prompt and execute the migration.

all details are in the migrate_manager.py file, there is no need to explain each line of code. I made it as simple as possible.

So all you have to do is to execute this command:

python migrate_manager.py migrate

The output is as following:

Begin database Migration ...

Model Migration
Connection started ...
Model: created successfully!
2022-01-04 02:29:53.402991: Commit is successful!!
Connection ended ...

Conclusion

It’s good to implement from scratch a technique like ORM, it will help you understand and learn quickly technologies without any difficulties with grasping the concept behind.