Open in app

Sign in

Write

Sign in

Object Relational Mapping: From Python to SQL and Back

Joseph Lee
7 min readNov 8, 2023

--

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:

  1. Import a special library that allows our Python code to connect to a SQL database.
  2. 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].

Python
Sql
Object Relational Mapping
Programming
Software Development

--

--

Joseph Lee

Written by Joseph Lee

7 Followers

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams