Overview

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:

[shell]
$ pip install sqlamp
Downloading/unpacking sqlamp
...
Successfully installed sqlamp
Cleaning up...
[/shell]

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

[python]
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, Float
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)

class ShoppingCart(Base):
__tablename__ = 'shopping_cart'
id = Column(Integer, primary_key=True)
owner_id = Column(Integer, ForeignKey(User.id))
owner = relationship(
User, backref=backref('shopping_carts', uselist=True)
)
products = relationship(
'Product',
secondary='shopping_cart_product_link'
)
def __repr__(self):
return '( {0}:{1.owner.name}:{1.products!r} )'.format(ShoppingCart, self)

class Product(Base):
__tablename__ = 'product'
id = Column(Integer, primary_key=True)
name = Column(String)
# Using a Float is not the right way of modeling a currency value.
# We will investigate that topic in a different article.
price = Column(Float)
shopping_carts = relationship(
'ShoppingCart',
secondary='shopping_cart_product_link'
)
def __repr__(self):
return '( {0}:{1.name!r}:{1.price!r} )'.format(Product, self)

class ShoppingCartProductLink(Base):
__tablename__ = 'shopping_cart_product_link'
shopping_cart_id = Column(Integer, ForeignKey('shopping_cart.id'), primary_key=True)
product_id = Column(Integer, ForeignKey('product.id'), primary_key=True)
[/python]

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.

[python]
import sqlamp

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, Float
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base(metaclass=sqlamp.DeclarativeMeta)

class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)

class ShoppingCart(Base):
__tablename__ = 'shopping_cart'
id = Column(Integer, primary_key=True)
owner_id = Column(Integer, ForeignKey(User.id))
owner = relationship(
User, backref=backref('shopping_carts', uselist=True)
)
products = relationship(
'Product',
secondary='shopping_cart_product_link'
)
def __repr__(self):
return '( {0}:{1.owner.name}:{1.products!r} )'.format(ShoppingCart, self)

class Product(Base):
__tablename__ = 'product'
# __mp_manager__ specifies which field of Product is the materialized path manager,
# which is used to manage the query of children and ancestors of products.
__mp_manager__ = 'mp'
id = Column(Integer, primary_key=True)
name = Column(String)
# Using a Float is not the right way of modeling a currency value.
# We will investigate that topic in a different article.
price = Column(Float)
shopping_carts = relationship(
'ShoppingCart',
secondary='shopping_cart_product_link'
)
# Using a self-referential ForeignKey to refer to the parent product
# that contains this product.
parent_id = Column(Integer, ForeignKey('product.id'))
parent = relationship('Product', remote_side=[id])
def __repr__(self):
return '( {0}:{1.name!r}:{1.price!r} )'.format(Product, self)

class ShoppingCartProductLink(Base):
__tablename__ = 'shopping_cart_product_link'
shopping_cart_id = Column(Integer, ForeignKey('shopping_cart.id'), primary_key=True)
product_id = Column(Integer, ForeignKey('product.id'), primary_key=True)
[/python]

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.

[python]
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///')
>>>
>>>
>>> from sqlalchemy.orm import sessionmaker
>>> DBSession = sessionmaker()
>>> DBSession.configure(bind=engine)
>>> Base.metadata.create_all(engine)
>>>
>>>
>>> camera_package = Product(name='DSLR Camera Package', price=1600.00)
>>> tripod = Product(name='Camera Tripod', price=200.00, parent=camera_package)
>>> body = Product(name='Camera Body', price=400.00, parent=camera_package)
>>> lens = Product(name='Camera Lens', price=1000.00, parent=camera_package)
>>> session = DBSession()
>>> session.add_all([camera_package, tripod, body, lens])
>>> session.commit()
[/python]

[python]
>>> camera_package.mp.query_children().all()
[( :u'Camera Tripod':200.0 ), ( :u'Camera Body':400.0 ), ( :u'Camera Lens':1000.0 )]
>>> tripod.mp.query_ancestors().all()
[( :u'DSLR Camera Package':1600.0 )]
>>> lens.mp.query_ancestors().all()
[( :u'DSLR Camera Package':1600.0 )]
[/python]

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.

[python]
>>> def recursive_tree_processor(nodes):
... for node, children in nodes:
... print('{0}'.format(node.name))
... if children:
... recursive_tree_processor(children)
...
>>> query = camera_package.mp.query_descendants(and_self=True)
>>> recursive_tree_processor(
... sqlamp.tree_recursive_iterator(query, Product.mp)
... )
DSLR Camera Package
Camera Tripod
Camera Body
Camera Lens
[/python]

Summary

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.