Flask¶
Initialize Flask app and sqlalchemy
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)
Make models.
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)
Represent data of tree in table¶
Add data to table with tree.
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),
]
)
db.drop_all()
db.create_all()
db.session.commit()
The database entries are added:
"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
Lft of root element every time \(1\).
\(root_{lft} = 1\)
Rgt of root element always equal 2 * quantity of tree nodes.
\(root_{rgt} = 2 * | P |\)
\(root_{rgt} = 2 * 7 = 14\)
The tree that displays the records in the database is represented schematically below:
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
Drilldown¶
Drilldown tree for a given node.
A drilldown tree consists of a node’s ancestors, itself and its immediate
children. For example, a drilldown tree for a foo1 category might look
something like:
Drilldown for foo1 node
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
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>}]
Represent it to JSON format:
def cat_to_json(item):
return {
'id': item.id,
'name': item.name
}
for item in categories:
pprint(item.drilldown_tree(json=True, json_fields=cat_to_json))
print()
[{'children': [{'children': [{'children': [{'id': 4,
'label': '<Category baz>',
'name': 'baz'}],
'id': 3,
'label': '<Category bar>',
'name': 'bar'}],
'id': 2,
'label': '<Category foo>',
'name': 'foo'},
{'children': [{'id': 6,
'label': '<Category bar1>',
'name': 'bar1'},
{'id': 7,
'label': '<Category baz1>',
'name': 'baz1'}],
'id': 5,
'label': '<Category foo1>',
'name': 'foo1'}],
'id': 1,
'label': '<Category root>',
'name': 'root'}]
[{'children': [{'children': [{'id': 4,
'label': '<Category baz>',
'name': 'baz'}],
'id': 3,
'label': '<Category bar>',
'name': 'bar'}],
'id': 2,
'label': '<Category foo>',
'name': 'foo'}]
[{'children': [{'id': 4, 'label': '<Category baz>', 'name': 'baz'}],
'id': 3,
'label': '<Category bar>',
'name': 'bar'}]
[{'id': 4, 'label': '<Category baz>', 'name': 'baz'}]
[{'children': [{'id': 6, 'label': '<Category bar1>', 'name': 'bar1'},
{'id': 7, 'label': '<Category baz1>', 'name': 'baz1'}],
'id': 5,
'label': '<Category foo1>',
'name': 'foo1'}]
[{'id': 6, 'label': '<Category bar1>', 'name': 'bar1'}]
[{'id': 7, 'label': '<Category baz1>', 'name': 'baz1'}]
Path to root¶
Returns a list containing the ancestors and the node itself in tree order.
Path to root of bar node
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
for item in categories:
print(item)
print(item.path_to_root()[-1]) # get root
# last element in list
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>]
Full code¶
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),
]
)
'''
"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>]
'''