Everything You Need to Know About Python and Object-Relational Maps

You may have heard of object-relational mapping (ORM). You may have even used one, but what exactly are they? And how do you use them in Python?

Here’s everything you need to know about ORMs and Python.

What Is an ORM?

Object-relational mapping (ORM) is a programming technique used to access a database. It exposes your database into a series of objects. You don’t have to write SQL commands to insert or retrieve data, you use a series of attributes and methods attached to objects.

It may sound complex and unnecessary, but they can save you a lot of time, and help to control access to your database.

Here’s an example. Say that whenever you insert a password into your database you want to hash it, as explained in website password security. This isn’t a problem for simple use cases—you do the calculation before inserting. But what if you need to insert a record in many places in the code? What if another programmer inserts into your table, and you don’t know about?

By using an ORM, you can write code to ensure that whenever and wherever any row or field in your database is accessed, your other, custom code is executed first.

This also acts as a “single source of truth”. If you want to change a custom calculation, you only have to change it in one place, not several. It’s possible to perform many of these principles with object orientated programming (OOP) in Python, but ORMs work in tandem with OOP principles to control access to a database.

There are certain things to watch out for when using an ORM, and there are circumstances where you may not want to use one, but they are generally considered to be a good thing to have, especially in a large codebase.

ORMs in Python Using SQLAlchemy

Like many tasks in Python, it’s quicker and easier to import a module than writing your own. Of course, it’s possible to write your own ORM, but why reinvent the wheel?

The following examples all use SQLAlchemy, a popular Python ORM, but many of the principles apply regardless of the implementation.

Setting Up Python for SQLAlchemy

Before jumping right in, you’re going to need to setup your machine for Python development with SQLAlchemy.

You’ll need to use Python 3.6 to follow along with these examples. While older versions will work, the code below will need some modification before it will run. Not sure on the differences? Our Python FAQ covers all the differences.

Before coding, you should set up a Python environment, which will prevent problems with other imported Python packages.

Make sure you have PIP, the Python package manager installed, which comes with most modern versions of Python.

Once you’re ready to go, you can begin by getting SQLAlchemy ready. From within your Python environment in the command line, install SQLAlchemy with the pip install command:

pip install SQLAlchemy-1.2.9

The 1.2.9 is the version number. You can leave this off to get the latest package, but it’s good practice to be specific. You don’t know when a new release may break your current code.

Now you’re ready to start coding. You may need to prepare your database to accept a Python connection, but the following examples all use an SQLite database created in-memory below.

Models in SQLAlchemy

One of the key components of an ORM is a model. This is a Python class which outlines what a table should look like, and how it should work. It’s the ORM version of the CREATE TABLE statement in SQL. You need a model for each table in your database.

Open up your favorite text editor or IDE, and create a new file called test.py. Enter this starter code, save the file, and run it:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('sqlite://') # Create the database in memory
Base.metadata.create_all(engine) # Create all the tables in the database

This code does several things. The imports are necessary so that Python understands where to find the SQLAlchemy modules it needs. Your models will use the declarative_base later on, and it configures any new models to work as expected.

The create_engine method creates a new connection to your database. If you have a database already, you’ll need to change sqlite:// to your database URI. As it is, this code will create a new database in memory only. The database is destroyed once your code finishes executing.

Finally, the create_all method creates all the tables defined in your modes in your database. As you haven’t defined any models yet, nothing will happen. Go ahead and run this code, to ensure you don’t have any problems or typos.

Let’s make a model. Add another import to the top of your file:

from sqlalchemy import Column, Integer, String

This imports the Column, Integer, and String modules from SQLAlchemy. They define how the database tables, fields, columns, and datatypes work.

Underneath the declarative_base, create your model class:

class Cars(Base):
  __tablename__ = 'cars'
  id = Column(Integer, primary_key=True)
  make = Column(String(50), nullable=False)
  color = Column(String(50), nullable=False)

This simple example uses cars, but your tables may contain any data.

Each class must inherit Base. Your database table name is defined in __tablename__. This should be the same as the class name, but this is just a recommendation, and nothing will break if they don’t match.

Finally, each column is defined as a python variable within the class. Different data types are used, and the primary_key attribute tells SQLAlchemy to create the id column as a primary key.

Go ahead and add one last import, this time for the ForeignKey module. Add this alongside your Column import:

from sqlalchemy import Column, ForeignKey, Integer, String

Now create a second model class. This class is called CarOwners, and stores owner details of specific cars stored in the Cars table:

class CarOwners(Base):
  __tablename__ = 'carowners'
  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False)
  age = Column(Integer, nullable=False)
  car_id = Column(Integer, ForeignKey('cars.id'))

  car = relationship(Cars)

There are several new attributes introduced here. The car_id field is defined as a foreign key. It is linked to the id in the cars table. Notice how the lower case table name is used, insted of the uppercase class name.

Finally, an attribute of car is defined as a relationship. This allows your model to access the Cars table through this variable. This is demonstrated below.

If you run this code now, you’ll see that nothing happens. This is because you haven’t told it to do anything noticeable yet.

Objects in SQLAlchemy

Now that your models are created, you can start to access the objects, and read and write data. It’s a good idea to place your logic into its own class and file, but for now, it can stay alongside the models.

Writing Data

In this example, you need to insert some data into the database before you can read it. If you’re using an existing database, you may have data already. Either way, it’s still very useful to know how to insert data.

You may be used to writing INSERT statements in SQL. SQLAlchemy handles this for you. Here’s how to insert one row into the Cars model. Start with a new import for sessionmaker:

from sqlalchemy.orm import sessionmaker

This is needed to create the session and DBSession objects, which are used to read and write data:

DBSession = sessionmaker(bind=engine)
session = DBSession()

Now put this underneath your create_all statement:

car1 = Cars(
  make="Ford",
  color="silver"
)
session.add(car1)
session.commit()

Let’s break down that code. The variable car1 is defined as an object based on the Cars model. Its make and color are set as parameters. This is like saying “make me a car, but don’t write it to the database yet”. This car exists in memory but is waiting to be written.

Add the car to the session with session.add, and then write it to the database with session.commit.

Now let’s add an owner:

owner1 = CarOwners(
  name="Joe",
  age="99",
  car_id=(car1.id)
)
session.add(owner1)
session.commit()

This code is almost identical to the previous insert for the Cars model. The main difference here is that car_id is a foreign key so needs a row id that exists in the other table. This is accessed through the car1.id property.

You don’t have to query the database or return any ids, as SQLAlchemy handles this for you (as long as you commit the data first).

Reading Data

Once you have written some data, you can begin to read it back. Here’s how to query the Cars and CarOwners tables:

result = session.query(Cars).all()

It is that simple. By using the query method found in the session, you specify the model, and then use the all method to retrieve all the results. If you know there will only be one result, then you can use the first method:

result = session.query(Cars).first()

Once you’ve queried the model, and stored your returned results in a variable, you can access the data through the object:

print(result[0].color)

This prints the color “silver”, as that record is the first row. You can loop over the result object if you want to.

As you defined the relationship in your model, it’s possible to access data in related tables without specifying a join:

result = session.query(CarOwners).all()
print(result[0].name)
print(result[0].car.color)

This works because your model contains details of your table structure, and the car attribute was defined as a link to the cars table.

What’s Not to Like About ORMs?

This tutorial only covered the very basics, but once you’ve got the hang of those, you can move on the advanced topics. There are some potential downsides to ORMs:

• You have to write your model before any queries can run.
• It’s another new syntax to learn.
• It may be too complex for simple needs.
• You must have a good database design to begin with.

These issues aren’t a big problem on their own, but they are things to watch out for. If you’re working with an existing database, you may get caught out.

If you’re not convinced an ORM is the right tool for you, then make sure you read about the important SQL commands programmers should know.

Read the full article: Everything You Need to Know About Python and Object-Relational Maps

Read Full Article