This article is part 7 of 11 in the series Python SQLAlchemy Tutorial

Association Tables

In our previous articles, we used an association table to model many-to-many relationships between tables, such as the relationship between Department and Employee. In this article, we are going to dive deeper into the association table concept and see how we can use it to further solve more complicated problems.

DepartmentEmployeeLink and Extra Data

In our previous article, we created the following SQLAlchemy models:

Notice that the DepartmentEmployeeLink class contains two foreign key columns which are enough to model the many-to-many relationship between Department and Employee. Now let's add one more column extra_data and two more relationships department and employee.

With one more extra column and two more relationships on the DepartmentEmployeeLink association model, we can store more information and be more liberal with how we want to use it. For example, suppose we have an employee John who works part-time in the IT department, we can insert the string 'part-time' into the column extra_data and create an DepartmentEmployeeLink object to represent this relationship.

Then, we can find John by querying the IT department or the DepartmentEmployeeLink model.

Finally, adding an IT employee using the relationship Department.employees still works, as shown in the previous article:

Linking Relationships with Backref

One common keyword argument we have used so far in relationship definitions is backref. A backref is a common shortcut to place a second relationship() onto the destination table. For example, the following code puts a second relationship() "posts" onto the user table by specifying a backref on Post.owner:

This is equivalent to the following definition:

Now we have a one-to-many relationship between User and Post. We can interact with these two models in the following way:

One-to-One

Creating a one-to-one relationship between models is very similar to creating many-to-one relationships. By modifying the uselist argument's value to False in a backref(), we force the database models to be mapped to each other in a one-to-one relationship.

Then, we can use the models in the following way:

Relationship Update Cascades

In a relational database, referential integrity guarantees that when the primary key of a referenced object in a one-to-many or many-to-many relationship changes, the refering objects' foreign keys that reference the primary key will change as well. However, for databases that do not support referential integrity, such as SQLite or MySQL with their referential integrity option turned off, changing the primary key values of a referenced object does not trigger updates of the refering objects. In this case, we can use the passive_updates flag in relationship or backref to inform the database to execute extra SELECT and UPDATE statements that will update the values of the refering objects' foreign keys.

In the following example, we construct a one-to-many relationship between User and Address and not specifying the passive_updates flag in the relationship. The database backend is SQLite.

Then, when we change the primary key value of a User object, its Address objects' user_id foreign key values will not change. Therefore, when you want to access an address's user object again, you will get an AttributeError.

If we specify the passive_updates flag in the Address model, then we can change the primary key of john and expect SQLAlchemy to issue extra SELECT and UPDATE statements to keep home_of_john.user and office_of_john.user up-to-date.

Summary

In this article, we dig a little deeper into SQLAlchemy's association tables and the backref keyword argument. It's often crucial to understand the mechanism behind these two concepts to fully master complex join queries, as what will be shown in future articles.

About The Author

  • RM

    Hi. I am newbie to SQLAlchemy. This line creates an employee attribute and links it with the Employee class. I did not understand the backref part. Can you please explain, why does backref points to department_assoc?
    employee = relationship(Employee, backref=backref(“department_assoc”))

    And, when the following line is called, department_id and employee_id fields in DepartmentEmployeeLink gets automatically initialized?
    John_working_part_time_at_IT = DepartmentEmployeeLink(department=IT, employee=John, extra_data=’part-time’)