如需C++版本，请跳转：解析标准树状文件为sql语句及代码解析（c++版本，txt，mysql）
解析标准化的树状文件为数据库tree表insert语句，涉及：
- 自动编号指标id（NodeCode）；
- 获取节点间父子关系（ParentNode）；
- 所属层级（LevelNumber）；
- 是否为叶节点（NoteCategory：1中间节点，2叶子结点）；
- 设置同级节点间顺序（BrotherNo）
输入：树状文件片段（txt为例）
/clarksons/tree.txt
----Oil Tankers
------Trade & Demand
--------Monthly Global Seaborne Trade Indicators (Monthly)
----------Monthly Global Seaborne Crude Oil Trade Indicator % Yr/Yr
----------Monthly Global Seaborne Crude Oil Trade Indicator % Yr/Yr 3mma
----------Monthly Global Seaborne Crude Oil Trade Indicator Volume Index
----------Monthly Global Seaborne Oil Products Trade Indicator % Yr/Yr
----------Monthly Global Seaborne Oil Products Trade Indicator % Yr/Yr 3mma
----------Monthly Global Seaborne Oil Products Trade Indicator Volume Index
----------Monthly Global Seaborne Total Oil Trade Indicator % Yr/Yr
----------Monthly Global Seaborne Total Oil Trade Indicator % Yr/Yr 3mma
----------Monthly Global Seaborne Total Oil Trade Indicator Volume Index
------Port Call Activity
--------Country
----------Russia Port Calls - Product Tankers 25,000+ dwt (Beta)
----------Russia Port Calls - Product Tankers 25,000+ dwt (Beta) DWT million
----------Russia Port Calls - Crude Tankers (Beta)
----------Russia Port Calls - Crude Tankers (Beta) DWT million
输出：自动生成的入库语句（mysql语句为例）
/clarksons/tree_sql.txt
UPDATE c_in_indicatortree set NoteCategory = 1 where NodeCode = 701 ;
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701001, 701001, 'Oil Tankers', 'Oil Tankers', 'Oil Tankers', 701, 1, 3, 2, 1, now(), 1655703762299, 3502, NULL);
UPDATE c_in_indicatortree set NoteCategory = 1 where NodeCode = 701001 ;
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701002, 701002, 'Trade & Demand', 'Trade & Demand', 'Trade & Demand', 701001, 1, 4, 2, 1, now(), 1655703762305, 3502, NULL);
UPDATE c_in_indicatortree set NoteCategory = 1 where NodeCode = 701002 ;
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701003, 701003, 'Monthly Global Seaborne Trade Indicators (Monthly)', 'Monthly Global Seaborne Trade Indicators (Monthly)', 'Monthly Global Seaborne Trade Indicators (Monthly', 701002, 1, 5, 2, 1, now(), 1655703762312, 3502, NULL);
UPDATE c_in_indicatortree set NoteCategory = 1 where NodeCode = 701003 ;
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701004, 701004, 'Monthly Global Seaborne Crude Oil Trade Indicator % Yr/Yr ', 'Monthly Global Seaborne Crude Oil Trade Indicator % Yr/Yr ', 'Monthly Global Seaborne Crude Oil Trade Indicator', 701003, 1, 6, 2, 1, now(), 1655703762321, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701005, 701005, 'Monthly Global Seaborne Crude Oil Trade Indicator % Yr/Yr 3mma', 'Monthly Global Seaborne Crude Oil Trade Indicator % Yr/Yr 3mma', 'Monthly Global Seaborne Crude Oil Trade Indicator', 701003, 2, 6, 2, 1, now(), 1655703762329, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701006, 701006, 'Monthly Global Seaborne Crude Oil Trade Indicator Volume Index', 'Monthly Global Seaborne Crude Oil Trade Indicator Volume Index', 'Monthly Global Seaborne Crude Oil Trade Indicator', 701003, 3, 6, 2, 1, now(), 1655703762336, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701007, 701007, 'Monthly Global Seaborne Oil Products Trade Indicator % Yr/Yr ', 'Monthly Global Seaborne Oil Products Trade Indicator % Yr/Yr ', 'Monthly Global Seaborne Oil Products Trade Indica', 701003, 4, 6, 2, 1, now(), 1655703762346, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701008, 701008, 'Monthly Global Seaborne Oil Products Trade Indicator % Yr/Yr 3mma ', 'Monthly Global Seaborne Oil Products Trade Indicator % Yr/Yr 3mma ', 'Monthly Global Seaborne Oil Products Trade Indica', 701003, 5, 6, 2, 1, now(), 1655703762356, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701009, 701009, 'Monthly Global Seaborne Oil Products Trade Indicator Volume Index ', 'Monthly Global Seaborne Oil Products Trade Indicator Volume Index ', 'Monthly Global Seaborne Oil Products Trade Indica', 701003, 6, 6, 2, 1, now(), 1655703762369, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701010, 701010, 'Monthly Global Seaborne Total Oil Trade Indicator % Yr/Yr ', 'Monthly Global Seaborne Total Oil Trade Indicator % Yr/Yr ', 'Monthly Global Seaborne Total Oil Trade Indicator', 701003, 7, 6, 2, 1, now(), 1655703762378, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701011, 701011, 'Monthly Global Seaborne Total Oil Trade Indicator % Yr/Yr 3mma ', 'Monthly Global Seaborne Total Oil Trade Indicator % Yr/Yr 3mma ', 'Monthly Global Seaborne Total Oil Trade Indicator', 701003, 8, 6, 2, 1, now(), 1655703762385, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701012, 701012, 'Monthly Global Seaborne Total Oil Trade Indicator Volume Index ', 'Monthly Global Seaborne Total Oil Trade Indicator Volume Index ', 'Monthly Global Seaborne Total Oil Trade Indicator', 701003, 9, 6, 2, 1, now(), 1655703762395, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701013, 701013, 'Port Call Activity', 'Port Call Activity', 'Port Call Activity', 701001, 2, 4, 2, 1, now(), 1655703762407, 3502, NULL);
UPDATE c_in_indicatortree set NoteCategory = 1 where NodeCode = 701013 ;
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701014, 701014, 'Country', 'Country', 'Country', 701013, 1, 5, 2, 1, now(), 1655703762417, 3502, NULL);
UPDATE c_in_indicatortree set NoteCategory = 1 where NodeCode = 701014 ;
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701015, 701015, 'Russia Port Calls - Product Tankers 25,000+ dwt (Beta) ', 'Russia Port Calls - Product Tankers 25,000+ dwt (Beta) ', 'Russia Port Calls - Product Tankers 25,000+ dwt (', 701014, 1, 6, 2, 1, now(), 1655703762426, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701016, 701016, 'Russia Port Calls - Product Tankers 25,000+ dwt (Beta) DWT million', 'Russia Port Calls - Product Tankers 25,000+ dwt (Beta) DWT million', 'Russia Port Calls - Product Tankers 25,000+ dwt (', 701014, 2, 6, 2, 1, now(), 1655703762436, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701017, 701017, 'Russia Port Calls - Crude Tankers (Beta) ', 'Russia Port Calls - Crude Tankers (Beta) ', 'Russia Port Calls - Crude Tankers (Beta) ', 701014, 3, 6, 2, 1, now(), 1655703762449, 3502, NULL);
INSERT INTO `caldb`.`c_in_indicatortree` (`ID`, `NodeCode`, `NodeName`, `NodeAbbr`, `ChiSpelling`, `ParentNode`, `BrotherNo`, `LevelNumber`, `NoteCategory`, `NodeState`, `UpdateTime`, `JSID`, `Edition`, `IndiRemark`)VALUES (701018, 701018, 'Russia Port Calls - Crude Tankers (Beta) DWT million ', 'Russia Port Calls - Crude Tankers (Beta) DWT million ', 'Russia Port Calls - Crude Tankers (Beta) DWT mill', 701014, 4, 6, 2, 1, now(), 1655703762457, 3502, NULL);
import time
def get_first_not_of_(data):
if data.startswith("-"):
num = 0
for char in data:
if char == '-':
num += 1
else:
break
return num
else:
return -1
def gen_tree(source, table_name):
file_read = open("./" + source + "/tree.txt", "r", encoding="UTF-8")
file_write = open("./" + source + "/tree_sql.txt", "w", encoding="UTF-8")
# parent_indicatorId 记录在n级别上的父指标id
parent_indicator_id = [-1, 150000000, 801, 0, 0, 0, 0, 0]
# brotherNo 记录在n级别上存在的兄弟节点个数
brother_no = [-1, 1, 0, 0, 0, 0, 0, 0]
# current parent
parent_level = 2
# start from first node
cur_indicator_id = 801001
# cursor
last_indicator_id = parent_indicator_id[parent_level]
last_level_cursor = parent_level
# 遍历
read_lines = file_read.readlines()
for line in read_lines:
if line[-1] == '\n':
line = line[:-1]
_num = get_first_not_of_(line)
cur_level = _num // 2 + 1
print(line)
note_category = 2
# peer, son, not son
if cur_level == last_level_cursor:
brother_no[cur_level] += 1
cur_brother_no = brother_no[cur_level]
elif last_level_cursor < cur_level:
brother_no[cur_level] = 1
cur_brother_no = brother_no[cur_level]
update_sql = "UPDATE {} set NoteCategory = 1 where NodeCode = {};".format(table_name, last_indicator_id)
file_write.write(update_sql+'\n')
else:
brother_no[last_level_cursor] = 0
brother_no[cur_level] += 1
cur_brother_no = brother_no[cur_level]
# 从一维数组获取指标父节点，并更新当前级别父指标为当前指标
cur_parent_indicator_id = parent_indicator_id[cur_level - 1]
parent_indicator_id[cur_level] = cur_indicator_id
# 指标名称截取
cur_indicator_name = line[_num:]
# 指标简称、拼音简称等生成
node_abbr = cur_indicator_name[0:99]
chi_spelling = cur_indicator_name[0:49]
timestemp = time.time_ns() // 1000000
insert_sql = """INSERT INTO {} VALUES ({}, {}, '{}', '{}', '{}', {}, {}, {}, {}, 1
, now(), {}, 3502, NULL);"""\
.format(table_name, cur_indicator_id, cur_indicator_id, cur_indicator_name, node_abbr, chi_spelling
, cur_parent_indicator_id, cur_brother_no, cur_level, note_category, timestemp)
file_write.write(insert_sql+'\n')
last_indicator_id = cur_indicator_id
last_level_cursor = cur_level
cur_indicator_id += 1
time.sleep(0.005)
file_read.close()
file_write.close()
if __name__ == '__main__':
gen_tree("clarksons", "indicator_tree")