Object Relational Mapping in Python

That’s a mouthful

Literally mapping relations…on a map

ORM is a technique used by programmers to interact with database records as objects in an object oriented programming language (like Python). Setting up an ORM library automates most of the database interactions and makes it easier to work with databases in an object oriented way. There are a lot of ORM libraries already readily available, like SQLAlchemy, but before we try to figure out the shortcuts it’s probably a good idea to know where we started. Here I’ll give an overview of what Object Relational Mapping is and what it looks like, sticking to the fundamentals only (namely sqlite3) and not getting too fancy (looking at you SQLAlchemy).

Why ORMs?

Efficiency, efficiency, efficiency. If we use an ORM, we do not have to constantly write out full SQL statements. Database management is automated for the most part. Did I mention not writing out full SQL statements every time you need to access data? Less SQL = cleaner code.

What does it look like?

An ORM is essentially just a conventional way for programmers to set up programs so that they can connect to a database. Rather than typing out a thousand SQL INSERT INTO or SELECT queries, we create methods in the ORM to abstract out the database logic.

Example

First let’s say we have a program tracking which robots are on which planets in our solar system. We would like to persist our robot data to a database and access that data easily. Since we are constantly sending new robots out into space, we want to be able to add robots to our database without too much effort.

class Robot:

    all = {}

    def __init__(self, name, terrain):
        self.name = name
        self.terrain = terrain
        self.add_robot(self)
    
    @classmethod
    def add_robot(cls, robot):
      cls.all.append(robot)

    def save(self, cursor):
      cursor.execute(
        "INSERT INTO robots (name, terrain) VALUES (?, ?, ?)",
        (self.name, self.terrain)
      )

Now when we want to add new robots, we can simply call on the save method in order to handle our SQL INSERT queries.

db_connection = sqlite3.connect("db/robots.db")
db_cursor = db_connection.cursor()

Robot("Walle", "terrestrial")
Robot("Johnny Five", "terrestrial")

for robot in Robot.all:
  robot.save(db_cursor)

Conventions

As with everything in a society, there are currently some conventions that are important for us to understand. In Python, we equate a Python class with a database table and an instance of that class to a table row. Logically, this makes sense even before implementation. If we consider the example above regarding robots…one instance of the robot class does not merit it’s very own table. Instead, logically, we would add a new row for each instance of the robot into the larger table or database.

Ready for More?

Now that we have a basic grasp on the fundamentals of ORMs, we can move on to understanding how to use established ORM libraries. As I mentioned in the beginning, SQLAlchemy is one of, if not the most popular ORM library out there. Get out there and start expanding your own library!

If you’re not learning, you’re not living.