sqlalchemy_mptt

Introduction:
Limitless classification is a common data format. The generation of organization structure, the generation of commodity classification information, and the detailed permission settings in permission management are inseparable from the management of limitless classification. There is a common linked list type, that is, a Pid points to the ID of the superior to set the structure. It is simple to write, and the effect is the same when you use it. For example, there is no way to manually reorder at the same level, so it is inconvenient to query all descendants. Therefore, there is a pre sort tree, which is a modified pre order traversal, that is, left-right value tree management.
Tree structure diagram:

Advantages: it can quickly determine the relationship, the shortest path, sort at the same level, and find all descendants (the best place)
Installation:
pip install sqlalchemy
pip install sqlalchemy_mptt
MySQL DB needs to be installed or PyMySQL can be used instead.
pip install PyMySQL
When using import sqlalchemy, add:
import pymysql
pymysql.install_as_MySQLdb()

use:

quote

import pymysql
pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_mptt import mptt_sessionmaker
from sqlalchemy_mptt.mixins import BaseNestedSets
Base = declarative_base()

Connect to database

'''
create_engine()Return a Engine And it represents the core interface for handling details through database syntax. In this case, database syntax will be interpreted as python Class method.
echo Parameter is True When, the of each execution is displayed SQL Statement can be closed.
mysql: Specify which database connection is
 first root:  user name
123456:  root Password corresponding to the user
localhost:  Database ip
mptt:  The database requires the name of the connection library
'''
engine = create_engine('mysql://root:123456@localhost/mptt', echo=False)
# conn = engine.connect()  # You can check whether the connection is successful
# sql = '''
# create table student(
#     id int not null primary key,
#     name varchar(50),
#     age int,
#     address varchar(100));
# '''
# conn.execute(sql)
# engine.connect() # Represents getting a connection to a database. Similar to the function of our cursor course in MySQL dB.
mptt_ession = mptt_sessionmaker(sessionmaker(bind=engine))
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

First define a data object
Define two fields: database Tree, primary key id, name and name

class Tree(Base, BaseNestedSets):
    __tablename__ = "tree"
    id = Column(Integer, primary_key=True)
    name = Column(String(8))
    def __repr__(self):
        return "<Node (%s)>" % self.id

Corresponding database structure

We only defined the primary key ID, name and name. But there are several more columns in the database: lft, rgt, level, tree_id，parent_id
These structures are used for left-right value trees, which can be easily found.

Add some data

if __name__ == '__main__':
    Base.metadata.create_all(bind=engine)
    node = Tree()
    node.name = "Pulse"   # When not to parent_ When ID is assigned, it is equivalent to creating a new tree, tree_id increment
    db_session.add(node)
    db_session.commit()
    parent_id1 = db_session.query(Tree.id).filter_by(name="Pulse").first()[0]
    level1_node = []
    level1_node_names = ["PN", "recruit", "advertisement", "increase"]
    for item in level1_node_names:
        node = Tree()
        node.name = item
        node.parent_id = parent_id1
        level1_node.append(node)
    db_session.add_all(level1_node)
    db_session.commit()
    level2_node = []
    level2_node_names = ["authentication", "network", "profile"]
    parent_id2 = db_session.query(Tree.id).filter_by(name="PN").first()[0]
    for item in level2_node_names:
        node = Tree()
        node.name = item
        node.parent_id = parent_id2
        level2_node.append(node)
    db_session.add_all(level2_node)
    db_session.commit()
    level3_node = []
    level3_node_names = ["sk", "lxx"]
    parent_id3 = db_session.query(Tree.id).filter_by(name="network").first()[0]
    for item in level3_node_names:
        node = Tree()
        node.name = item
        node.parent_id = parent_id3
        level3_node.append(node)
    db_session.add_all(level3_node)
    db_session.commit()
    print_tree("Pulse")

Output:

The order I added is not this order, but the data structure sqlalchemy_mptt helps us deal with the hierarchical data structure, which is displayed as a tree structure.
Database data

Data operation

query
Find descendants of pulse
"Pulse" as the reference point, the left value is 1 and the right value is 18. All descendants are those whose left value is greater than 1 and less than 18 in the database.
Find descendants of "PN"
"PN" is used as the reference point. The left value is 2 and the right value is 11. All descendants are those whose left value is greater than 2 and less than 11 in the database.
Find all child nodes (excluding grandchildren):
Find the child node of pulse as the reference point, level=1, tree_id=1. Then all child nodes are trees_ Id = 1, level=1 + 1, level 2.
Find shortest path:
It is generally used in navigation and combined display, because you need to know the path structure of the upper level and the upper N level: find the superior path of sk. SK is used as the reference point. The left value is 6 and the right value is 7. Then the path is the path with the left value less than 6 and the right value greater than 7 in the database. The result is pulse – > PN – > network – > sk

algorithm
increase
Press Sqlalchemy_ The use of mptt,
If there is no parent_id, then it is created as the root node of a new tree, parent_id is empty, level is 1, tree_ The ID is added upward according to the database.
If parent is provided_ ID, the child node created as a parent for so long_ ID is provided, level is parent's level+1, tree_id and parent are consistent.
At the same time, update other affected nodes.
The left value is processed again.
Greater than parent_ All lvalues of ID right value, + 2
Right value processing again.
Greater than or equal to parent_ All right values of ID right value, + 2
delete
It is similar to adding. After deleting a node, the affected left and right values should also be updated.
move
In fact, this is to delete an old node and create a new node.