需求
- 数据库里有这样的二维表
(id,country,province,city)
(1,"a","aa","aa1"),
("2","a","aa","aa2"),
("3","b","bb","bb1"),
("4","b","bb","bb2"),
("5","b","bb","bb3"),
("6","c","cc","cc1"),
转换成在json中可用的树状结构
[{
"id": "a",
"parent_id": "",
"text": "a",
"leaf": false,
"children": [{
"id": "a|aa",
"parent_id": "a",
"text": "aa",
"leaf": false,
"children": [{
"id": 1,
"parent_id": "a|aa",
"text": "aa1",
"leaf": true
}, {
"id": "2",
"parent_id": "a|aa",
"text": "aa2",
"leaf": true
}]
}]
}, {
"id": "b",
"parent_id": "",
"text": "b",
"leaf": false,
"children": [{
"id": "b|bb",
"parent_id": "b",
"text": "bb",
"leaf": false,
"children": [{
"id": "3",
"parent_id": "b|bb",
"text": "bb1",
"leaf": true
}, {
"id": "4",
"parent_id": "b|bb",
"text": "bb2",
"leaf": true
}, {
"id": "5",
"parent_id": "b|bb",
"text": "bb3",
"leaf": true
}]
}]
}, {
"id": "c",
"parent_id": "",
"text": "c",
"leaf": false,
"children": [{
"id": "c|cc",
"parent_id": "c",
"text": "cc",
"leaf": false,
"children": [{
"id": "6",
"parent_id": "c|cc",
"text": "cc1",
"leaf": true
}]
}]
}]
- 本来以为很好写的一小段,写起来发现还挺麻烦的
难点
- 二维表转json tree 还是比较常见的写法,但是这个二维表里没有parentid,所以上下级关系需要用country,province 两列来对齐生成
代码
- 我写了一个python版的实现
class jsontree_str_():
def __init__(self) -> None:
pass
def get_jsonstr_parentid(self,rows,columns):
sb_rows=[]
columnsi=len(columns)
if len(rows)==0 or columnsi<3:
return sb_rows
dict_ids={}
for row in rows:
for i in range(1,columnsi):
idstr="|".join('%s' %id for id in row[1:i+1]) if i <columnsi-1 else row[0]
if idstr in dict_ids:
continue
else:
dict_ids[idstr]=True
sb_rows.append({
"id":idstr,
"parent_id":"|".join('%s' %id for id in row[1:i]) if i > 1 else "",
"text":str(row[i]),
"leaf":i == columnsi - 1})
return sb_rows
def get_jsonstr_tree(self,data: list) -> list:
mapping: dict = dict(zip([i['id'] for i in data], data))
resultlist: list = []
for d in data:
# 如果找不到父级项，则是根节点
parent: dict = mapping.get(d['parent_id'])
if parent is None:
resultlist.append(d)
else:
children: list = parent.get('children')
if not children:
children = []
children.append(d)
parent.update({'children': children})
return resultlist
def remove_dict_node(self,listobj,remotekeys=[]):
if len(remotekeys)==0:
return
for dict_list in listobj:
for delkey in remotekeys:
if delkey in dict_list:
dict_list.pop(delkey)
if "children" in dict_list:
self.remove_dict_node(dict_list["children"])
def make_treejson(self,rows,columns,remotekeys=[]):
sb_rows=self.get_jsonstr_parentid(rows,columns)
listobj=self.get_jsonstr_tree(sb_rows)
if len(remotekeys)>0:
self.remove_dict_node(listobj)
return json.dumps(listobj)
def test(self,):
rows=[
(1,"a","aa","aa1"),
("2","a","aa","aa2"),
("3","b","bb","bb1"),
("4","b","bb","bb2"),
("5","b","bb","bb3"),
("6","c","cc","cc1"),
]
columns=("id","text","text1","text2")
jsonstr=self.make_treejson(rows,columns)
return jsonstr
if __name__ == "__main__":
oostr=jsontree_str_()
print(oostr.test())
