Using SQLAlchemy as an ORM


A common programming task is to abstract—abstract the inner logic until complex tasks can be encapsulated into simple functions. When it comes to SQL, for example, for a very long time, the only way to access and interact with data was to write out queries. You would find code like this sprinkled everywhere:

But this approach is not readable or maintainable, and terribly error-prone. The natural next step is to then abstract this out into ORM (Object Relational Mapping). ORM is a programming technique for converting data between incompatible systems in object-oriented programming languages. With ORM, you’ll be able to map class object relationships as table column relationships in SQL databases.

SQLAlchemy is an open source SQL toolkit and object-relational mapper (ORM) for the Python programming language released under the MIT License.

SQLAlchemy’s philosophy is that SQL databases behave less and less like object collections the more size and performance start to matter—while object collections behave less and less like tables and rows the more abstraction starts to matter. For this reason, it has adopted the data mapper pattern (like Hibernate for Java), rather than the active record pattern used by a number of other object-relational mappers.

However, I do not prefer the data mapper approach. I feel it requires far too much boilerplate to be written before you get to use it as a full-blown ORM. I think others also feel this way, which is why SQLAlchemy has a declarative approach that tries to map the active record pattern. That’s what we will be exploring in this article.

SQLAlchemy Essentials
1. Engine

The Engine is the starting point for any SQLAlchemy application. It’s “home base” for the actual database and its DBAPI, delivered to the SQLAlchemy application through a connection pool and a Dialect, which describes how to talk to a specific kind of database/DBAPI combination.

2. Declarative Base

You will be required to inherit all your custom classes from this base class. Declarative Base is used to connect the class and object attributes to the database’s tables and column relationships.

3. Session

The session establishes all conversations with the database and represents a “staging zone” for all the objects loaded into the database session object. Any change made against the objects in the session won’t be persisted into the database until you call session.commit(). If you’re not happy about the changes, you can revert all of them back to the last commit by calling session.rollback().

4. Binding the session to the engine
At the end, you will need to create all the tables and bind the current session to the database engine of your choice. This will ensure that all object attributes are reflected in the database tables and columns.

Building with SQLAlchemy

Let’s create a simple Python package that creates notes and categorizes them. We’ll use SQLAlchemy and MySQL for storing the notes and categories.

I always like starting out with a conf file that initializes the engine, session and base. It also has important functions to create, initialize, drop and save to the database.

Let’s create a Category class and link it to the categories MySQL table by inheriting from Base. Since the conf file has already initialized the Base class, we just have to import it.

The __tablename__ attribute defines the name of the MySQL table that the Category class will communicate to. id and name are the columns of the categories MySQL table and the attributes of the Category class.

If you want to query for all records that have a certain name, just abstract conf.session to the Category class and use the filter function.

If you want to limit the query to just one:

Relationships in SQLAlchemy

In almost every MySQL database, relationships are how tables talk to each other. It can be a one-to-many, one-to-one, many-to-many, or many-to-one.

We wanted to categorize notes. We have a Category class. We now need to create a Note class that can communicate with Category.

ProTip: Make sure to ‘import category’ as a file, not as a class (not ‘from category import Category’), or you’ll run into dependency issues during runtime.

Each note has a category_id column that is used to map the category this note belongs to. This defines the database relationship.

How do the classes communicate? Just import the category here and use the relationship method to define it.

Creating a Record with SQLAlchemy

Great. We’ve added the classes. Now, how do we create a record and commit it to the database?

Let’s start easy and create a Category object.

ProTip: If you try creating a Category called ‘tech’ again, you’ll run into an error. Why? We’ve defined a unique constraint on the ‘name’ attribute.

Now, how do we create a note that belongs to this category?

That’s it!

Scoping Database Relationships Within Instance Methods

Wouldn’t it be great to query all notes belonging to a category so that you can just call:

Just create a notes() instance method to your Category class and add this:

Where’s the Code?

You can find all of the code I used in this article in my GitHub repository.

Do you think you can beat this Sweet post?

If so, you may have what it takes to become a Sweetcode contributor... Learn More.

Swaathi Kakarla is the co-founder and CTO at Skcript She enjoys talking and writing about code efficiency, performance and startups. In her free time she finds solace in yoga, bicycling and contributing to open source.


Click on a tab to select how you'd like to leave your comment

Leave a Comment

Your email address will not be published. Required fields are marked *