37,719
社区成员
发帖
与我相关
我的任务
分享
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 在数据库中建数据表,并插入样例数据
import sqlite3
con = sqlite3.connect(":memory:") # 使用:memory:标识打开的是内存数据库
cur = con.cursor()
cur.execute('CREATE TABLE assets(id INT PRIMARY KEY NOT NULL, name TEXT NOT NULL, parent_id INT NOT NULL);')
DATA = [(1, '电工产品', 0),
(2, '电力A', 1),
(3, '电力B', 2),
(4, '电力C', 3),
(5, '电力D', 2),
(6, '电力E', 5),
(7, '电力F', 3)]
cur.executemany('INSERT INTO assets(id, name, parent_id) VALUES(?,?,?);', DATA)
# 定义数据结构
from collections import namedtuple
Node = namedtuple('Node', 'id, name, parent_id') # 单个结点
assets = {} # 存放所有结点
# 从数据库表中加载数据到数据结构中
rows = cur.execute('SELECT id, name, parent_id FROM assets;')
for row in rows:
print(row)
assets[row[0]] = Node(*row)
cur.close()
con.close()
def traversal(assets, id):
# 用递归的方法遍历结点
if assets[id].parent_id == 0:
return [assets[id]]
else:
return traversal(assets, assets[id].parent_id) + [assets[id]]
from operator import attrgetter
for node_id in (4,6,7):
result = traversal(assets, node_id)
print('/'.join(map(attrgetter('name'), result)), *map(attrgetter('id'), result))
(1, '电工产品', 0)
(2, '电力A', 1)
(3, '电力B', 2)
(4, '电力C', 3)
(5, '电力D', 2)
(6, '电力E', 5)
(7, '电力F', 3)
电工产品/电力A/电力B/电力C 1 2 3 4
电工产品/电力A/电力D/电力E 1 2 5 6
电工产品/电力A/电力B/电力F 1 2 3 7