表数据
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()