Creating a relational category model

In our previous recipe, we created a simple product model that had a couple of fields. However, in practice, applications are much more complex and have various relationships among their tables. These relationships can be one-to-one, one-to-many, many-to-one, or many-to-many. We will try to understand some of them in this recipe with the help of an example.

How to do it…

Let's say we want to have product categories where each category can have multiple products, but each product should have at least one category. Let's do this by modifying some files from the preceding application. We will make modifications to both models and views. In models, we will add a Category model, and in views, we will add new methods to handle category-related calls and also modify the existing methods to accommodate the newly added feature.

First, we will modify our models.py file to add the Category model and some modifications to the Product model:

from my_app import db

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    price = db.Column(db.Float)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship(
        'Category', backref=db.backref('products', lazy='dynamic')
    )

    def __init__(self, name, price, category):
        self.name = name
        self.price = price
        self.category = category

    def __repr__(self):
        return '<Product %d>' % self.id

In the preceding Product model, notice the newly added fields for category_id and category. The category_id field is the foreign key to the Category model, and category represents the relationship table. As evident from the definitions themselves, one of them is a relationship, and the other uses this relationship to store the foreign key value in the database. This is a simple many-to-one relationship from product to category. Also, notice the backref argument in the category field; this argument allows us to access products from the Category model by writing something as simple as category.products in our views. This acts like the one-to-many relationship from the other end. Consider the following code:

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '<Category %d>' % self.id

The preceding code is the Category model, which has just one field called name.

Now, we will modify our views.py file to accommodate the changes in our models:

from my_app.catalog.models import Product, Category

@catalog.route('/products')
def products():
    products = Product.query.all()
    res = {}
    for product in products:
        res[product.id] = {
            'name': product.name,
            'price': product.price,
            'category': product.category.name
        }
    return jsonify(res)

Here, we have just one change where we are sending the category name and the product's JSON data is being generated to be returned. Consider the following code:

@catalog.route('/product-create', methods=['POST',])
def create_product():
    name = request.form.get('name')
    price = request.form.get('price')
    categ_name = request.form.get('category')
    category = Category.query.filter_by(name=categ_name).first()
    if not category:
        category = Category(categ_name)
    product = Product(name, price, category)
    db.session.add(product)
    db.session.commit()
    return 'Product created.'

Check out how we are looking for the category before creating the product. We will first search for an existing category with the category name in the request. If an existing category is found, we will use it for product creation; otherwise, we will create a new category. Consider the following code:

@catalog.route('/category-create', methods=['POST',])
def create_category():
    name = request.form.get('name')
    category = Category(name)
    db.session.add(category)
    db.session.commit()
    return 'Category created.'

The preceding code is a relatively simple method for creating a category using the name provided in the request. Consider the following code:

@catalog.route('/categories')
def categories():
    categories = Category.query.all()
    res = {}
    for category in categories:
        res[category.id] = {
            'name': category.name
        }
        for product in category.products:
            res[category.id]['products'] = {
                'id': product.id,
                'name': product.name,
                'price': product.price
            }
    return jsonify(res)

The preceding method does a bit of tricky stuff. Here, we fetched all the categories from the database, and then for each category, we fetched all the products and then returned all the data as a JSON dump.

See also

• Read through the Creating a basic product model recipe to understand the context of this recipe and how this recipe works for a browser