How I created a mini ORM with python?

Introduction

When you code in a Django web framework, we all know that you wont 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.

Objectrelational mapping(ORM) incomputer scienceis atechnique for converting data between incompatibletype systemsusingobject-orientedprogramming 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 sqlite3con = 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 wont 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 theexample.dbfile (setup operation) and returns theConnectionobject to variableConnection.
• The __exit__ method takes care of closing the connection on exiting thewithblock(teardown operation).

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

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 classdef commit(operation):        def wrapper(self, *args,**kwargs):            operation(self, *args, **kwargs)            self.connection.commit()        return wrapper@commitdef 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 theModelclass. The model class defines a new kind of datastore entity and the properties the kind is expected to take.

I wont 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 lets 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 MigrationConnection started ...Model: created successfully!2022-01-04 02:29:53.402991: Commit is successful!!Connection ended ...

Conclusion

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