Exploring ORM - Object Relational Mapping
Published: 2024-06-15 12:48:39

Databases are an immensely powerful tool for developers, as they allow us to store and access information across multiple uses of a program. Without them, most of the information that we create ends up becoming inaccessible once the application is run and closed. Amongst the information that would otherwise be lost is data related to class instances. Let's say you have an application that displays information about an account at a bank. One of the ways we can represent this account is by using a class that has variables for information such as the name, balance, account number, etc. 

In order to store this data, and eventually retrieve and reassign it to an instance, we will need to implement **Object Relational Mapping**, or **ORM** for short. **ORM** simply refers to the method by which we set up classes to store and retrieve information to some database. For the case of this specific blog post, we will be looking at how to implement **ORM** using **Python** and **SQLite**.



## Setup

First and foremost, we need to do a little bit of setup. We start by installing **sqlite3** into our workspace. Once that is done, we need to import it into our file and initialize our connection to our database file and a cursor on this connection to execute commands like so:

```
import sqlite3

CONN = sqlite3.connect(database.db)
CURSOR = CONN.cursor()
```



## create_table

With that setup done, we can move on to actually creating our mapper. Let’s continue with our earlier example of a Customer class. First, we need to actually create a table to store the data in. We can create a class method that will handle that for us:

```
@classmethod
def create_table(cls):
	sql = “””
	CREATE IF NOT EXISTS customers (
		id INTEGER PRIMARY KEY , 
		name TEXT, 
		account_number INTEGER,
		balance REAL
		)
	”””

	CURSOR.execute(sql)
	CONN.commit()
```

Running this method on our Customer class will give us a table that we can actually store and retrieve data from. 



## delete_table

But before we start doing that, let’s make a method to delete our table in case we want to have a fresh start:

```
@classmethod
def delete_table(cls)
	sql = “””
		DROP TABLE IF EXISTS customers;
	”””

	CURSOR.execute(sql)
	CONN.commit()
```

Both of these class methods above have three main elements, the **SQLite** code that we want to run, our **CURSOR** to execute that code, and out **CONN** to commit the changes to our database.



## save and delete

Now that we have methods to create and delete our table, let’s create corresponding methods to create and delete table rows for our class attribute. Instead of class methods, these two methods are only meant to be called on an instance of a class, not the class object itself:

```
def save(self):
	sql = “””
		INSERT INTO customers (
			name, account_number, balance
		)
		VALUES (?, ?, ?)
	”””

	CURSOR.execute(sql, (
		self.name, self.account_number, self.balance
	))
	CONN.commit()

	self.id = CURSOR.lastrowid
	type(self).all[self.id] = self


def delete(self):
	sql = “””
		DELETE FROM customers
		WHERE id = ?
	“””

	CURSOR.execute(sql, (self.id,))
	CONN.commit()

	del type(self).all[self.id]
	self.id = None
```

In our save method, we are introducing a few new steps. First are the question marks in `sql`. These are there to act as place holders for the data we want to pass through. We pass the information through when we call execute as the second parameter. You may have noticed that there is an extra comma in the delete method after `self.id`. This is because the second argument for execute needs to be a **tuple**, so we have to include the comma even if we’re only passing a single variable.

After we add our information to the table, we set the `id` of our instance equal to its corresponding `id` in the table. We use that `id` to save our instance to a dictionary object that is a class attribute. Our attribute, called `all`, is defined as an empty dictionary at the top of our class and is used to keep track of all existing instances of our class. Without it, we could end up wasting a lot of memory by accidentally creating multiple identical instances. 

We also use `id` to find the row in our table that we want to delete. After deleting the info from the table, we need to remove the object from our all dictionary and remove the instance’s id.



## instance_from_db and find_by_id

Now that we have our methods to manipulate information within the table, the last of our necessary methods are going to handle the retrieval of data from the table and creation of a new instance. Both of these methods are going to be class methods:

```
@classmethod
def instance_from_db(cls, row):
	customer = cls.all.get(row[0])

	if customer:
		customer.name = row[1]
		customer.account_number = row[2]
		customer.balance = row[3]
	else:
		customer = cls(row[1], row[2], row[3])
		customer.id = row[0]
		cls.all[customer.id] = customer

	return customer 


@classmethod
def find_by_id(cls, id):
	sql = “””
		SELECT * FROM customers
		WHERE id = ?
	”””

	row = CURSOR.execute(sql, (id,)).fetchone()
	return cls.instance_from_db(row) if row else None
```

Let’s break these last two methods down. Starting with `instance_from_db`, we take our row provided from `find_by_id` and try to find a corresponding instance that already exists in our all dictionary. If one does exist, it takes the information from the table and updates the instance to make sure they match. If it doesn’t exist, it goes ahead and makes and returns a new instance.

Finally, there’s our `find_by_id` method which takes an id as a parameter and returns a customer instance. Most of the code in it should look familiar, but there is one new thing. The `fetchone` method at the end takes the executed code and returns a list containing the information from the first row that meets the `WHERE` condition.

We can write as many methods as there are different ways of interacting with the table using **SQLite**. However, with these methods, you have the bare bones of a basic **ORM**. Good luck and happy coding!