flask, mptt and sqlalchemy

flask-mptt.py

from pprint import pprint
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy_mptt.mixins import BaseNestedSets

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class Category(db.Model, BaseNestedSets):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(400), index=True, unique=True)
    items = db.relationship("Product", backref='item', lazy='dynamic')

    def __repr__(self):
        return '<Category {}>'.format(self.name)


class Product(db.Model):
    __tablename__ = 'products'
    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    name = db.Column(db.String(475), index=True)

db.session.add(Category(name="root"))  # root node
db.session.add_all(  # first branch of tree
    [
        Category(name="foo", parent_id=1),
        Category(name="bar", parent_id=2),
        Category(name="baz", parent_id=3),
    ]
)
db.session.add_all(  # second branch of tree
    [
        Category(name="foo1", parent_id=1),
        Category(name="bar1", parent_id=5),
        Category(name="baz1", parent_id=5),
    ]
)

'''
.. code-block:: text

    "id"  "name"  "lft"  "rgt"  "level"  "parent_id"  "tree_id"
    1     "root"  1      14     1        1
    2     "foo"   2      7      2        1            1
    3     "bar"   3      6      3        2            1
    4     "baz"   4      5      4        3            1
    5     "foo1"  8      13     2        1            1
    6     "bar1"  9      10     3        5            1
    7     "baz1"  11     12     3        5            1

root lft everytime = 1
root rgt = qty_nodes * 2

level
  1                  1(root)14
                         |
                ---------------------
                |                   |
  2          2(foo)7             8(foo1)13
                |               /         \
  3          3(bar)6        9(bar1)10   11(baz1)12
                |
  4          4(baz)5
'''

db.drop_all()
db.create_all()
db.session.commit()

categories = Category.query.all()

for item in categories:
    print(item)
    pprint(item.drilldown_tree())
    print()

'''
<Category root>
[{'children': [{'children': [{'children': [{'node': <Category baz>}],
                              'node': <Category bar>}],
                'node': <Category foo>},
               {'children': [{'node': <Category bar1>},
                             {'node': <Category baz1>}],
                'node': <Category foo1>}],
  'node': <Category root>}]

<Category foo>
[{'children': [{'children': [{'node': <Category baz>}],
                'node': <Category bar>}],
  'node': <Category foo>}]

<Category bar>
[{'children': [{'node': <Category baz>}], 'node': <Category bar>}]

<Category baz>
[{'node': <Category baz>}]

<Category foo1>
[{'children': [{'node': <Category bar1>}, {'node': <Category baz1>}],
  'node': <Category foo1>}]

<Category bar1>
[{'node': <Category bar1>}]

<Category baz1>
[{'node': <Category baz1>}]
'''

for item in categories:
    print(item)
    print(item.path_to_root()[-1])
    pprint(item.path_to_root().all())
    print()

'''
<Category root>
<Category root>
[<Category root>]

<Category foo>
<Category root>
[<Category foo>, <Category root>]

<Category bar>
<Category root>
[<Category bar>, <Category foo>, <Category root>]

<Category baz>
<Category root>
[<Category baz>, <Category bar>, <Category foo>, <Category root>]

<Category foo1>
<Category root>
[<Category foo1>, <Category root>]

<Category bar1>
<Category root>
[<Category bar1>, <Category foo1>, <Category root>]

<Category baz1>
<Category root>
[<Category baz1>, <Category foo1>, <Category root>]

'''