Object Relational Mapping: From Python to SQL and Back
As an up-and-coming programmer, you may have heard others discussing ORMs. Apart from being a fancy-sounding acronym that makes the user feel smart, what exactly is an ORM? ORM stands for ‘object relational mapping’, which basically the process of taking data from a Python object [aka class instance] and converting it into a table row format to be stored inside a SQL database.
Conversely, ORM is also used to query table data from a SQL database and convert it into an object instance to be used in Python code.
Does this sound intimidating? Lucky for us, it is not! While Python objects and SQL table rows may look completely different at first glance, they both follow a similar schema for storing data.
Let’s walk through an example together. This will require some pre-existing knowledge about how OOP and classes work. If you need to review this information, here is a fantastic blog post on the topic.
Okay. Ready to dive into our example?
Great!
Say we have a Python class called Pet
. All objects instantiated from the Pet
class will possess a pre-defined set of attributes. For the sake of our example, let’s say that every Pet
instance will have the attributes name
, species
, age
, weight
, and favorite_food
. We can use our Python class
to create [instantiate] several different pets and assign them unique values for each of these attributes.
class Pet:
def __init__(self, name, species, age, weight_in_kg, favorite_food):
self.name = name
self.species = species
self.age = age
self.weight_in_kg = weight_in_kg
self.favorite_food = favorite_food
pet_1 = Pet('Pocket', 'dog', 3, 22.5, 'Kibbles & Bits')
pet_2 = Pet('Mittens', 'cat', 7, 8.0, 'Fancy Feast: Salmon Edition')
pet_3 = Pet('Mrs. Birdy III', 'bird', 22, 0.5, 'Froot Loops')
If you were to print()
each of these objects as a dictionary using vars()
, you would see the following data structures:
#pet_1
{
'name': 'Pocket',
'species': 'dog',
'age': 3,
'weight_in_kg': 22.5,
'favorite_food': 'Kibbles & Bits'
}
#pet_2
{
'name': 'Mittens',
'species': 'cat',
'age': 7,
'weight_in_kg': 8.0,
'favorite_food': 'Fancy Feast: Salmon Edition'
}
#pet_3
{
'name': 'Mrs. Birdy III',
'species': 'bird',
'age': 22,
'weight_in_kg': 0.5,
'favorite_food': 'Froot Loops'
}
If we were to take this same data and display it as a SQL table, the table name would be ‘pets’, and the rows would look something like this:
Pretty intuitive, right? The table name would correspond to the class name [lowercased and pluralized], the column names would correspond to the attribute names of each Pet
instance, and the value in each cell would correspond to the values of each attribute.
Okay. Now here comes the tricky part. In order to successfully map a Python object to a SQL table, we need to:
- Import a special library that allows our Python code to connect to a SQL database.
- Use these library methods to create SQL queries, populate them with our object data, and query the database.
These special methods will also be used later to retrieve table data from a SQL database and instantiate Python objects from that data.
Step One: Import the library. For the sake of our example, we will be using SQLite3.
import sqlite3
#sqlite3 has a connect() method which accepts a .db file as a destination
CONN = sqlite3.connect('our_database.db')
#once we establish our connection and assign it to CONN, we create a CURSOR
CURSOR = CONN.cursor()
#this CURSOR object contains all the methods we will need for our ORM tasks
Step Two: Create class methods in our Pet
class that will allow us to create and drop tables in our_database.db
, the destination we established by passing as a string argument to our sqlite3.connect()
method.
Note: If you need to brush up on class methods, feel free to take a moment to do so. This next step also requires some knowledge about constructing SQL queries. Take as much time as you need to become comfortable with the syntax.
class Pet:
def __init__(self, name, species, age, weight_in_kg, favorite_food, id_=None):
self.name = name
self.species = species
self.age = age
self.weight_in_kg = weight_in_kg
self.favorite_food = favorite_food
self.id_ = id_
#id_ is initially set to None, but will automatically be assigned
#by the database once the object is persisted to a table
@classmethod
def create_table(cls):
#use the triple quotes """ syntax to construct a SQL query
sql = """
CREATE TABLE IF NOT EXISTS pets (
id INTEGER PRIMARY KEY,
name TEXT,
species TEXT,
age INTEGER,
weight_in_kg REAL,
favorite_food TEXT)
"""
#call CURSOR.execute and pass in our SQL query
CURSOR.execute(sql)
#finally, fire off the database request
CONN.commit()
@classmethod
def drop_table(cls):
#construct another SQL query to drop tables
sql = """
DROP TABLE IF EXISTS pets;
"""
CURSOR.execute(sql)
CONN.commit()
Great! Now our our_database.db
should have a new, empty table called ‘pets’, with the column names set to name
, species
, age
, weight
, and favorite_food
. You can call these class methods by running Pet.create_table()
and Pet.drop_table()
.
Step Three: Define some additional class and instance methods in our Pet
class. These will be used to instantiate classes and save them to our database.
Class methods will be called directly by our Pet
class. Instance methods will be called by our instances, not by the Pet
class.
class Pet:
#THE __init__ FUNCTION GOES HERE...
#THE CREATE AND DROP CLASS METHODS GO HERE...
#our save() method will be called on an instance to create a SQL query
def save(self):
#construct a SQL query that references the database column names
sql = """
INSERT INTO pets (name, species, age, weight_in_kg, favorite_food)
VALUES (?, ?, ?, ?, ?)
"""
#CURSOR.execute() will execute our SQL query with the desired values
CURSOR.execute(sql, (self.name, self.species, self.age, self.weight_in_kg, self.favorite_food))
#this will fire off the SQL query using CONN.commit()
CONN.commit()
#we assign our database generated id to our instance
self.id = CURSOR.lastrowid
#class method create() will instantiate an object and save() it to our database
@classmethod
def create(cls, name, species, age, weight_in_kg, favorite_food):
#we then call the cls constructor [Pet] and pass in those values
new_instance = cls(name, species, age, weight_in_kg, favorite_food)
#our instance method save() is what actually will query the database
new_instance.save()
#we return the created object so it can be used elsewhere in our code
return new_instance
Now let’s put this into action!
#instantiate some new Pet instances using our create() class method
pet_1 = Pet.create('Pocket', 'dog', 3, 22.5, 'Kibbles & Bits')
pet_2 = Pet.create('Mittens', 'cat', 7, 8.0, 'Fancy Feast: Salmon Edition')
pet_3 = Pet.create('Mrs. Birdy III', 'bird', 22, 0.5, 'Froot Loops')
#Pet.create() will create the a new instance AND call save() on it
Our newly populated SQL database should look exactly like this:
Okely dokely. We now have rows in our SQL database. So, how do we convert that data back into objects?
Step Four: Create additional class methods that fetch table rows using a SQL SELECT
query and instantiate objects from that row data.
class Pet:
#THE __init__ FUNCTION GOES HERE...
#THE CREATE AND DROP CLASS METHODS GO HERE...
#THE save() AND create() CLASS METHODS GO HERE...
#this method will create and populate an object from our table data
@classmethod
def create_from_db(cls, table_row):
#table_row is a tuple containing the values from each queried row, which we pass to our constructor
new_instance = cls(table_row[1], table_row[2], table_row[3], table_row[4], table_row[5], table_row[6])
#table_row[0] is the newly generated 'id' attribute we must assign
new_instance.id = table_row[0]
#now we can return the newly instantiated object
return new_instance
#this method will fetch table data from our SQL database
@classmethod
def get_table_rows(cls):
#format a SQL query that will fetch table rows
sql = """
SELECT * FROM cats
"""
#fetchall() returns queried rows as a collection of tuples
table_rows = CURSOR.execute(sql).fetchall()
#use our create_from_db() method to create objects and return a list comprehension
return [cls.create_from_db(row) for row in table_rows]
Here is what our final code looks like, without comments:
class Pet:
def __init__(self, name, species, age, weight_in_kg, favorite_food):
self.name = name
self.species = species
self.age = age
self.weight_in_kg = weight_in_kg
self.favorite_food = favorite_food
@classmethod
def create_table(cls):
sql = """
CREATE TABLE IF NOT EXISTS pets (
id INTEGER PRIMARY KEY,
name TEXT,
species TEXT,
age INTEGER,
weight_in_kg REAL,
favorite_food TEXT)
"""
CURSOR.execute(sql)
CONN.commit()
@classmethod
def drop_table(cls):
sql = """
DROP TABLE IF EXISTS pets;
"""
CURSOR.execute(sql)
CONN.commit()
def save(self):
sql = """
INSERT INTO pets (name, species, age, weight_in_kg, favorite_food)
VALUES (?, ?, ?, ?, ?)
"""
CURSOR.execute(sql, (self.name, self.species, self.age, self.weight_in_kg, self.favorite_food))
CONN.commit()
self.id = CURSOR.lastrowid
@classmethod
def create(cls, name, species, age, weight_in_kg, favorite_food):
new_instance = cls(name, species, age, weight_in_kg, favorite_food)
new_instance.save()
return new_instance
@classmethod
def create_from_db(cls, table_row):
new_instance = cls(table_row[1], table_row[2], table_row[3], table_row[4], table_row[5], table_row[6])
new_instance.id = table_row[0]
return new_instance
@classmethod
def get_table_rows(cls):
sql = """
SELECT * FROM cats
"""
table_rows = CURSOR.execute(sql).fetchall()
return [cls.create_from_db(row) for row in table_rows]
Okay. So now, you know how to use basic ORM methods to create SQL tables, use Python objects to populate SQL queries and save them to the database, and instantiate new Python objects from fetched table rows.
That was quite a bit of information. Congratulate yourself for getting this far. Maybe go treat yourself to a chicken fried steak. With scrambled eggs and hash browns. All smothered in a copious amount of artery-clogging, cardiac-arrest-inducing sausage gravy. Mmm.
But wait, there’s more!
Using this same schema, you can create additional Python instance methods that will UPDATE
and DELETE
data from our SQL table. And the power of ORMs is not just limited to basic CRUD operations. You can essentially use your Python code to create and execute any kind of SQL query using the CONN
and CURSOR
objects. You can SELECT
table rows by id
, name
, age
, or any other relevant column values that exist. You can implement JOIN
and WHERE
parameters to make your SQL queries as specific and intricate as you need. The sky is the limit!
Now get out there and use your programming skills to make the world a better place [or at least keep it from becoming worse].