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!
Top comments (0)