获取数据库表中节点的层级关系并打印输出

表数据

from sqlalchemy import create_engine
from sqlalchemy import Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 生成orm基类
Base = declarative_base()


def init_sqlalchemy(host, database):
    """初始化sqlalchemy"""
    url = "mysql+pymysql://root:aware@" + host + "/" + database + "?charset=utf8"     # 拼接数据库串
    print(url)
    engine = create_engine(url, encoding='utf-8', echo=False)       # 创建引擎
    DBSession = sessionmaker(bind=engine)                           # 创建DBSession类型:
    session = DBSession()                                           # 创建session对象:
    return session


class T_domain(Base):
    """定义T_domain对象:"""
    __tablename__ = 't_domain_copy'  # 表的名字:
    # 表的结构:
    DomainID = Column(Integer, primary_key=True)
    DomainName = Column(String(255))
    DomainParentID = Column(Integer)


class DomainMap(object):
    def __init__(self, host, database):
        self.session = init_sqlalchemy(host, database)  # 获取session对象

    def domain_map(self):
        """获取节点层级关系"""
        # 存放每个节点及对应的名称
        domainInfos = {}
        # 存放每个节点与父节点的对应关系
        domainIDToParentIDs = {}
        # 查询数据库表
        res = self.session.query(T_domain).order_by(T_domain.DomainParentID).all()
        # 遍历所有记录
        for r in res:
            # 获取记录获取节点ID和节点名称
            if r.DomainID not in domainInfos.keys():
                domainInfos[r.DomainID] = r.DomainName

            # 获取所有子节点与该父节点的对应关系
            if r.DomainID not in domainIDToParentIDs:
                domainIDToParentIDs[r.DomainID] = r.DomainParentID

        # 获取根节点的父节点
        rootParentID = list(set(domainIDToParentIDs.values()) - set(domainIDToParentIDs.keys()))[0]
        # 获取根节点
        rootID = list(domainIDToParentIDs.keys())[list(domainIDToParentIDs.values()).index(rootParentID)]
        # print("根节点", rootID)

        # 获取层级关系
        domainMaps = {rootID: {}}
        domainMaps = self.__map(domainIDToParentIDs, domainMaps)

        return domainMaps, domainInfos

    def __map(self, domainIDToParentIDs, maps):
        """递归遍历所有节点记录"""
        for k in maps.keys():
            domainIDs = {k: {}}
            for j in domainIDToParentIDs.keys():
                if domainIDToParentIDs[j] == k:
                    domainIDs[k][j] = {}
            maps[k] = domainIDs[k]
            self.__map(domainIDToParentIDs, maps[k])
        return maps

    def print_info(self, domainMap, domainInfos, num):
        """打印节点信息"""
        if type(domainMap) == dict or len(domainMap) != 0:
            for k in domainMap.keys():
                print('\t' * num, domainInfos[k])
                self.print_info(domainMap[k], domainInfos, num+1)

    def main(self):
        """主程序"""
        # 获取域层级关系
        domainMaps, domainInfos = self.domain_map()
        num = 0
        self.print_info(domainMaps, domainInfos, num)

if __name__ == '__main__':
    host = 'localhost'
    databases = ['ykdhg']
    for database in databases:
        print('  %s  '.center(100, '*') % database)
        test = DomainMap(host, database)
        test.main()