This article is part of in the series


In the previous article SQLAlchemy Expression Language, Advanced Usage, we learned the power of SQLAlchemy's expression language through a three table database including User, ShoppingCart, and Product. In this article, we are going to review the concept of materialised path in SQLAlchemy and use it to implement product containing relationships, where certain products may include others. For example, a DSLR camera package is one product that may contain a body, a tripod, a lens and a set of cleaning tools while each of the body, the tripod, the lens and the set of cleaning tools is a product as well. In this case, the DSLR camera package product contains other products.

Materialized Path

Materialized Path is a way to store a hierarchical data structure, often a tree, in a relational database. It can be used to handle hierarchical relationship between any types of entities in a database. sqlamp is a third-party SQLAlchemy library we will use to demonstrate how to set up a product containing relationship based hierarchical data structure. To install sqlamp, run the following command in your shell:

First, let's review what we have done in the previous article.

We have defined four models, User to represent a set of users, Product to represent a set of products, ShoppingCart to represent a set of shopping carts, each of which is owned by a User and contains multiple Products, and ShoppingCartProductLink which is a link table that connects Product and ShoppingCart.

Then, let's introduce sqlamp into the model class and see how we can use it to create a materialised path for Products.

Notice that we inserted a new foreign key parent_id and a new relationship parent into the Product model and introduced a new class member field __mp_manager__. Now we can use Product.mp to query the children and ancestors of any product.

Processing the Product Tree Recursively

To recursively walk down a Product tree, we can call sqlamp.tree_recursive_iterator and traverse all the descendants of the tree using a recursive function.


In this article, we used the previous article's Product to illustrate how to use sqlamp to implement materialised paths in SQLAlchemy. By simply inserting a self-referential foreign key and a __mp_manager__ field to Product, we are able to implement a hierarchical data structure for Product. Since sqlamp is written on top of SQLAlchemy, it should work with any database backends supported by SQLAlchemy.

About The Author