Python Friday #78: Relationship Patterns in SQLAlchemy Core

Relational databases are great to protect your data integrity. However, that only works when you tell the database which objects have a relationship. Let us look what SQLAlchemy Core offers us to create foreign keys through metadata.

This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub

 

Our little bookstore

To work with related tables, we create a little bookstore that allows us to persist books. The great benefit of SQLite is that we only need to use another file an SQLAlchemy creates a new database for us.

At the end of this post we have a database with these related tables:

If you code along, add this code at the top of your Python script:

 

One to many

A book has a publisher and a publisher has one or more books to promote, print and sell. The one to many pattern allows us to represent this relation between the Publisher and the Book table:

Only the Book table (the many side) has a reference to the Publisher table, but not the other way around. Whenever we want to create a book we first need to create a publisher.

 

One to one

The one to one pattern is great if we want to have additional data to a table but do not want to work with them all the time. For our Book table we can create a BookDetail table and store things like the cover or a more detailed description. Since every row in BookDetail matches exactly one row in the Books table, we can use the same Id for both primary keys.

 

Many to many

An author writes one or more books and a book can have one or more authors. For this relationship we can use the many to many pattern with the mapping-table BookAuthor to link Book with Author:

We want only one row that connects a specific author to a specific book. To enforce that, we create a primary key over those two columns and give it a name.

 

Create tables for your metadata

SQLAlchemy allows us to turn our metadata into tables with this line of code:

Put this line at the end of your table definitions and SQLAlchemy will create the tables that do not yet exist. Existing tables will not be modified when you add new fields or change existing ones in your metadata. For that we will need a special tool that I will introduce after the ORM part.

 

Insert data in your tables

Inserting data in tables with foreign keys is the same as in regular CRUD operations. The only restriction is that you need to persist your objects in the right order:

• You need to have a publisher in your database before you can create a book.
• You need a book in your database before you can create a BookDetail entry.
• You need a book and an author before you can join them in the BookAuthor table.

At least this is how it should work with most database systems. For the SQLite driver we need to make sure we execute the pragma foreign_keys=on as the first action in our connection block to enforce our foreign keys:

When this pragma is turned on and we use a value that does not match an id in the related table, SQLAlchemy will throw an error like this one (where I did not have a publisher for a book):

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed

It is not the most helpful error message, but you get a stack trace around it to pinpoint the line that throw this exception.

 

Next

Having foreign keys and inserted data is a good start. To fully benefit from the relational patterns, you need a way to load the data using those foreign keys. Next week we look how SQLAlchemy Core handles JOIN statements.