def findRef(nodes, parent, level, date):
_db = CommonDB()
sql = "SELECT * FROM sourcetreeref WHERE parent = %s AND level LIKE %s"
params = (parent, level)
try:
curYear = int(time.localtime()[0])
_db.execute(sql=sql, params=params)
res = _db.fetchall()
for entry in res:
isOK = False
title = entry[0]
_date = 0
if entry[2] != 'none':
_date = int(entry[2])
if date == 'Last 1 Year':
if abs(curYear - _date) <= 1:
isOK = True
elif date == 'Last 5 Years':
if abs(curYear - _date) <= 5:
isOK = True
elif date == 'Last 10 Years':
if abs(curYear - _date) <= 10:
isOK = True
else:
isOK = True
if isOK == True:
nodes.append({
"text": "* " + title + " *",
"parent": parent,
"href": "javascript:nodeNameClick('%s')" % title,
})
except Exception as e:
print(e)
def addNode(name, parent):
data = {}
if name is None or name == '':
return False
if parent is None or parent == '':
return False
if findNodeByName(name) == True:
data["res"] = "error"
data["msg"] = "Already Exists!"
return data
try:
db_hundler = CommonDB()
db_hundler.execute(
"INSERT INTO sourcetree(name, parent, needShow, haveProblem, theType) \
VALUES(%s, %s, %s, %s, %s)",
(name, parent, 1, 0, "custom"))
db_hundler.execute(
"INSERT INTO custom(name, parent, abbr) \
VALUES(%s, %s, %s)", (name, parent, ""))
db_hundler.commit()
data["res"] = "success"
data["msg"] = "添加子节点成功"
source_tree.build_source_tree.updateProblem('%', '%', False)
except Exception as e:
data["res"] = "error"
data["msg"] = str(e)
db_hundler.close()
return data
def get_source_tree_json(level, date, pid="Root"):
nodes = []
_db = CommonDB()
sql = "SELECT * FROM sourcetree WHERE parent = %s"
params = (pid, )
try:
_db.execute(sql=sql, params=params)
res = _db.fetchall()
for entry in res:
title = entry[0]
parent = entry[1]
if entry[2] == 0 or entry[3] == 0:
continue
node = {
"text":
title,
"parent":
parent,
"tags": [
NODE_DROPDOWN_MENU %
(str(title).replace(" ", ""), title, parent)
],
}
childNode = get_source_tree_json(level, date, title)
findRef(childNode, title, level, date)
if len(childNode) > 0:
node['nodes'] = childNode
nodes.append(node)
if pid == 'Root':
findRef(nodes, 'Root', level, date)
pass
except Exception as e:
print(e)
pass
return nodes
def init_db():
_db = CommonDB()
# 没有数据表, 则新建
sql = "SHOW TABLES"
_db.execute(sql)
flag = False
for _item in _db.fetchall():
if _item is not None and isinstance(_item, tuple):
for _i in _item:
if _i == DB_TABLE_NAME:
flag = True
pass
pass
# 如果没有数据表, 新建表
if not flag:
# id:自增字段
# pid: 父节点id
# name: 节点名称
# synonym: 节点名称同义词, 空格或者英文逗号分割
sql = "CREATE TABLE " + DB_TABLE_NAME + "(id INT NOT NULL AUTO_INCREMENT, pid INT NOT NULL, " \
"name VARCHAR(20), synonym VARCHAR(100), PRIMARY KEY(id, pid))"
try:
_db.execute(sql)
except Exception as e:
print("初始化数据库时出错: ", e)
raise Exception("初始化数据库时出错: " + str(e))
# 关闭数据库连接
_db.close()
pass
def rebuild_db():
_db = CommonDB()
# 没有数据表, 则新建
sql = "SHOW TABLES"
_db.execute(sql)
flag = False
for _item in _db.fetchall():
if _item is not None and isinstance(_item, tuple):
for _i in _item:
if _i == DB_TABLE_NAME:
flag = True
pass
pass
# 如果有数据表, 删除
if flag:
sql = "DROP TABLE %s" % DB_TABLE_NAME
try:
_db.execute(sql)
except Exception as e:
print("重建数据库时出错: ", e)
raise Exception("初始化数据库时出错: " + str(e))
# 关闭数据库连接
_db.close()
init_db()
pass
def get_knowledge_name():
_db = CommonDB()
names = []
sql = "SELECT name FROM " + DB_TABLE_NAME
try:
_db.execute(sql)
for item in _db.fetchall():
names.append(item[0])
except Exception as e:
print("查询知识树结构时出错: ", e)
return names
def get_name_by_id(_id):
"""给出节点id返回节点名称"""
_db = CommonDB()
if _id is None or _id < 0:
return None
sql = "SELECT name FROM " + DB_TABLE_NAME + " WHERE id=%s"
_db.execute(sql, (_id, ))
_result = _db.fetchone()
if isinstance(_result, tuple):
return _result[0]
pass
return None
pass
def get_id_by_name(_node_name):
"""给出节点名, 返回节点id"""
_db = CommonDB()
if _node_name is None or str(_node_name).strip() == "":
return None
sql = "SELECT id FROM " + DB_TABLE_NAME + " WHERE name=%s"
_db.execute(sql, (_node_name, ))
_result = _db.fetchone()
if isinstance(_result, tuple):
# print(_result[0])
return _result[0]
pass
return None
pass
def updateToSQL():
# 连接数据库
db_hundler = CommonDB()
# 更新目录表
for entry in G:
db_hundler.execute("UPDATE sourcetree SET needShow = %s, haveProblem = %s WHERE name = %s",\
(G[entry][1],G[entry][2],entry))
db_hundler.commit()
db_hundler.close()
def get_knowledge_tree_json(_pid=-1):
"""得到可以转换成json格式的树结构"""
_nodes = []
_db = CommonDB()
sql = "SELECT id,pid,name,synonym FROM " + DB_TABLE_NAME + " WHERE pid=%s"
params = (_pid, )
try:
_db.execute(sql=sql, params=params)
_reses = _db.fetchall()
_db.close()
for _res in _reses:
_node = {
"id": _res[0],
"pid": _res[1],
"text": _res[2],
"synonym": _res[3],
"href": "javascript:nodeNameClick(%s)" % _res[0],
# "tags": ["<a href='javascript:(nodeMenu(" + str(_res[0]) + ", " + str(_res[1]) + "))'>哈哈</a>"],
"tags": [NODE_DROPDOWN_MENU % (_res[0], _res[0], _res[1])],
}
_child_nodes = get_knowledge_tree_json(_res[0])
if len(_child_nodes) > 0:
_node["nodes"] = _child_nodes
_nodes.append(_node)
# print(_reses)
pass
except Exception as e:
print("获取知识树时出错：", e)
pass
return _nodes
pass
def restoreUserOp(sources, force=False):
data = []
try:
db_hundler = CommonDB()
for item in sources:
OldSource = simpleSource(item[0])
db_hundler.execute("SELECT * FROM sourcetreeref")
res = db_hundler.fetchall()
needRemove = False
for entry in res:
tempSource = simpleSource(entry[0])
parent = entry[3]
isUser = int(entry[4])
if tempSource == OldSource:
if isUser == 1 and force == False:
needRemove = True
fixProblemCount(parent, +1)
else:
fixProblemCount(parent, +1)
if needRemove == False:
data.append(item)
except Exception as e:
print(e)
db_hundler.close()
return data
def get_id_by_name_and_parent_name(_node_name, _parent_name):
"""给出节点名, 返回节点id"""
_db = CommonDB()
if _node_name is None or str(_node_name).strip() == "":
return None
sql = "SELECT id,pid FROM " + DB_TABLE_NAME + " WHERE name=%s"
_db.execute(sql, (_node_name, ))
_results = _db.fetchall()
for _result in _results:
if isinstance(_result, tuple):
_pid = _result[1]
_maybe_parent_name = get_name_by_id(_pid)
# 如果得到的父节点和给出的父节点名称相同, 则返回
if str_is_equal_ignore_case(_maybe_parent_name, _parent_name):
return _result[0]
pass
return None
pass
def get_knowledge_tree_nodes():
"""从数据库查询知识树结构, 返回节点集"""
_arr = []
_db = CommonDB()
sql = "SELECT id,pid,name,synonym FROM " + DB_TABLE_NAME
try:
_db.execute(sql)
for item in _db.fetchall():
_node = KnowledgeTreeNode(cid=item[0],
pid=item[1],
name=item[2],
synonym=item[3])
_arr.append(_node)
pass
except Exception as e:
print("查询知识树结构时出错: ", e)
return _arr
pass
def fixAnthor(oldAnthor, newAnthor, tableName):
if oldAnthor is None or oldAnthor == '':
return False
if newAnthor is None or newAnthor == '':
return False
if tableName is None or tableName == '':
return False
if oldAnthor == 'Root':
return True
try:
db_hundler = CommonDB()
db_hundler.execute(
"UPDATE " + tableName + " SET parent = %s \
WHERE parent = %s", (newAnthor, oldAnthor))
db_hundler.commit()
except Exception as e:
print(e)
db_hundler.close()
return False
db_hundler.close()
return True
def get_problems_by_source_title(source_title):
_res_list = []
_db = CommonDB()
sql = "SELECT id,ojname FROM sourcetreeref WHERE source=%s"
_db.execute(sql, (source_title, ))
_problems = _db.fetchall()
_db.close()
for _problem in _problems:
_t_problem = {"id": _problem[0], "ojname": _problem[1]}
_res_list.append(_t_problem)
return _res_list
pass
def deleteNodeByName(name):
data = {}
if name is None or name == '':
return False
parent = findParentByName(name)
type = findTypeByName(name)
if parent is None or type is None:
return False
try:
db_hundler = CommonDB()
db_hundler.execute("DELETE FROM sourcetree WHERE name = %s", (name))
db_hundler.execute("DELETE FROM " + type + " WHERE name = %s", (name))
db_hundler.commit()
fixAnthor(name, parent, type)
fixAnthor(name, parent, 'sourcetreeref')
fixAnthor(name, parent, 'sourcetree')
data["res"] = "success"
data["msg"] = "删除节点成功"
except Exception as e:
data["res"] = "error"
data["msg"] = str(e)
db_hundler.close()
return data
def findNodeByName(name):
if name is None or name == '':
return False
try:
db_hundler = CommonDB()
db_hundler.execute("SELECT * FROM sourcetree WHERE name = %s", (name))
res = db_hundler.fetchone()
if isinstance(res, tuple):
return True
except Exception as e:
print(e)
db_hundler.close()
return False
def findParentByName(name):
if name is None or name == '':
return None
try:
db_hundler = CommonDB()
db_hundler.execute("SELECT parent FROM sourcetree WHERE name = %s",
(name))
res = db_hundler.fetchone()
if isinstance(res, tuple):
return res[0]
except Exception as e:
print(e)
db_hundler.close()
return None
def get_all_knowledge_node_name():
_data = []
_db = CommonDB()
sql = "SELECT name FROM " + DB_TABLE_NAME
try:
_db.execute(sql)
_reses = _db.fetchall()
_db.close()
for _res in _reses:
_data.append(_res[0])
pass
except:
pass
return _data
pass
def get_parent_id_by_id(_node_id):
_data = None
_db = CommonDB()
sql = "SELECT pid FROM " + DB_TABLE_NAME + " WHERE id=%s"
params = (_node_id, )
try:
_db.execute(sql, params)
_res = _db.fetchall()
_db.close()
_data = _res[0][0]
except Exception as e:
import traceback
traceback.print_exc()
pass
return _data
pass
def add_node(_pid, _name, _synonym=""):
"""为知识树增加节点"""
# 初始化数据库
_db = CommonDB()
sql = "INSERT INTO " + DB_TABLE_NAME + "(pid, name, synonym) VALUES(%s, %s ,%s)"
params = (_pid, _name, _synonym)
try:
if get_id_by_pid_and_name(_pid, _name) is not None:
raise Exception("节点已存在")
pass
_db.execute(sql, params=params)
_db.commit()
except Exception as e:
print("新建节点时出错: ", e)
else:
pass
# print("成功建立节点: ", _name)
finally:
if _db is not None:
_db.close()
pass
def get_sources(id, oj_name, force = False):
# 获得已抓取题目的source
db_hundler = CommonDB()
if oj_name == 'All':
oj_name = '%'
db_hundler.execute("SELECT source FROM mixojapp_problem "
"WHERE sourceid LIKE %s AND ojname LIKE %s",(id, oj_name))
res = db_hundler.fetchall()
db_hundler.close()
sourceSet = []
entry = []
for item in res:
for word in item:
entry.append(word)
sourceSet.append(entry.copy())
entry.clear()
return sourceSet
pass
def get_chid_by_name(name, names):
names.append(name)
_db = CommonDB()
sql = "SELECT id FROM " + DB_TABLE_NAME + " WHERE name=%s"
try:
_db.execute(sql, (name))
_result = _db.fetchone()
if _result is not None:
id = _result[0]
print()
sql = "SELECT name FROM " + DB_TABLE_NAME + " WHERE pid=%s"
_db.execute(sql, (id))
_results = _db.fetchall()
if _results is not None:
for i in _results:
get_chid_by_name(i[0], names)
else:
print("无此节点")
except Exception as e:
print("查询知识树结构时出错: ", e)
pass
return names
def get_node_detail_by_id(_node_id):
_data = {}
_db = CommonDB()
sql = "SELECT id,pid,name,synonym FROM " + DB_TABLE_NAME + " WHERE id=%s"
params = (_node_id, )
try:
_db.execute(sql, params)
_res = _db.fetchone()
_db.close()
if _res is not None:
_data["id"] = _res[0]
_data["pid"] = _res[1]
_data["name"] = _res[2]
_data["synonym"] = _res[3]
pass
except:
pass
return _data
pass
def update_node(_node_id, _node_name, _node_synonym):
_data = {}
_db = CommonDB()
sql = "UPDATE " + DB_TABLE_NAME + " SET name=%s,synonym=%s WHERE id=%s"
params = (
_node_name,
_node_synonym,
_node_id,
)
try:
_db.execute(sql, params)
_db.commit()
_db.close()
_data["res"] = "success"
_data["msg"] = "更新成功"
except Exception as e:
# import traceback
# traceback.print_exc()
_data["res"] = "error"
_data["msg"] = str(e)
pass
return _data
pass
def add_child_node(_pid, _node_name, _node_synonym):
_data = {}
_db = CommonDB()
sql = "INSERT INTO " + DB_TABLE_NAME + "(pid,name,synonym) VALUES(%s,%s,%s)"
params = (
_pid,
_node_name,
_node_synonym,
)
try:
_db.execute(sql, params)
_db.commit()
_db.close()
_data["res"] = "success"
_data["msg"] = "添加子节点成功"
except Exception as e:
# import traceback
# traceback.print_exc()
_data["res"] = "error"
_data["msg"] = str(e)
pass
return _data
pass
def createDictTree():
# 词典格式 node(node0,node1,node2,...) : parent
# 加载中国大学名称词典
db_hundler = CommonDB()
try:
db_hundler.execute("SELECT * FROM chinauniversity")
res = db_hundler.fetchall()
for entry in res:
name = entry[0]
parent = entry[1]
abbr = entry[2]
G[name] = [parent, 0, 0]
Type[name] = 'chinauniversity'
dictTrie.add(name)
if abbr != "":
dictTrie.add(abbr, name)
except Exception as e:
print(e)
db_hundler.commit()
db_hundler.close()
# 加载城市名称词典
db_hundler = CommonDB()
try:
db_hundler.execute("SELECT * FROM city")
res = db_hundler.fetchall()
for entry in res:
name = entry[0]
parent = entry[1]
abbr = entry[2]
G[name] = [parent, 0, 0]
Type[name] = 'city'
dictTrie.add(name)
if abbr != "":
dictTrie.add(abbr, name)
except Exception as e:
print(e)
db_hundler.commit()
db_hundler.close()
# 加载国家名称词典
db_hundler = CommonDB()
try:
db_hundler.execute("SELECT * FROM country")
res = db_hundler.fetchall()
for entry in res:
name = entry[0]
parent = entry[1]
abbr = entry[2]
G[name] = [parent, 1, 0]
Type[name] = 'country'
dictTrie.add(name)
if abbr != "":
dictTrie.add(abbr, name)
except Exception as e:
print(e)
db_hundler.commit()
db_hundler.close()
# 加载大洲名称词典
db_hundler = CommonDB()
try:
db_hundler.execute("SELECT * FROM continent")
res = db_hundler.fetchall()
for entry in res:
name = entry[0]
parent = entry[1]
abbr = entry[2]
G[name] = [parent, 1, 0]
Type[name] = 'continent'
dictTrie.add(name)
if abbr != "":
dictTrie.add(abbr, name)
except Exception as e:
print(e)
db_hundler.commit()
db_hundler.close()
# 加载OJ词典
db_hundler = CommonDB()
try:
db_hundler.execute("SELECT * FROM oj")
res = db_hundler.fetchall()
for entry in res:
name = entry[0]
parent = entry[1]
abbr = entry[2]
G[name] = [parent, 0, 0]
Type[name] = 'oj'
dictTrie.add(name)
if abbr != "":
dictTrie.add(abbr, name)
ojList.append(name)
except Exception as e:
print(e)
db_hundler.commit()
db_hundler.close()
# 加载自定义词典
db_hundler = CommonDB()
try:
db_hundler.execute("SELECT * FROM custom")
res = db_hundler.fetchall()
for entry in res:
name = entry[0]
parent = entry[1]
abbr = entry[2]
G[name] = [parent, 1, 0]
Type[name] = 'custom'
dictTrie.add(name)
if abbr != "":
dictTrie.add(abbr, name)
except Exception as e:
print(e)
db_hundler.commit()
db_hundler.close()
pass
def createSQLTable():
db_hundler = CommonDB()
db_hundler.execute(
'create table if not exists sourcetreeref(source VARCHAR(200) PRIMARY KEY,\
level VARCHAR (100), date VARCHAR (20) ,parent VARCHAR (100), isUser INT)'
)
db_hundler.execute(
'create table if not exists sourcetree(name VARCHAR(100) PRIMARY KEY, parent VARCHAR (100), \
needShow INT, haveProblem INT,theType VARCHAR (100))')
for entry in G:
db_hundler.execute("SELECT * FROM sourcetree WHERE name = %s", (entry))
res = db_hundler.fetchall()
if len(res) == 0:
# 如果数据库中不存在该词条，则插入该词条
db_hundler.execute(
"INSERT INTO sourcetree(name,parent,needShow,haveProblem,theType) \
VALUES(%s, %s, %s, %s, %s)",
(entry, G[entry][0], G[entry][1], G[entry][2], Type[entry]))
else:
db_hundler.execute(
"UPDATE sourcetree SET needShow = %s, haveProblem = %s, theType = %s\
WHERE name = %s AND parent = %s",
(G[entry][1], G[entry][2], Type[entry], entry, G[entry][0]))
db_hundler.commit()
db_hundler.close()
pass
def processDict(entry, oldSource, newSource, year='none'):
tempDict = {}
maxLen = 0
path = []
for item in entry:
ret = processPath(item, tempDict)
if len(ret) > maxLen:
path = ret
maxLen = len(ret)
if len(path) == 0:
path.append('Unknown')
type = defType(newSource)
#找到挂载点
customAnthor = 'Root'
for item in path:
if item == 'Root' or item == 'Unknown':
continue
if G[item][1] == 1:
G[item][2] = G[item][2] + 1
if customAnthor == 'Root':
customAnthor = item
# 连接数据库
db_hundler = CommonDB()
# 更新映射表
db_hundler.execute("SELECT * FROM sourcetreeref WHERE source = %s",
(oldSource))
isExist = len(db_hundler.fetchall()) > 0
if isExist:
db_hundler.execute(
"UPDATE sourcetreeref SET level = %s, date = %s, parent = %s, isUser = %s \
WHERE source = %s", (type, year, customAnthor, 0, oldSource))
else:
db_hundler.execute(
"INSERT INTO sourcetreeref(source,level,date,parent,isUser) \
VALUES(%s, %s, %s, %s, %s)", (oldSource, type, year, customAnthor, 0))
db_hundler.commit()
#关闭数据库
db_hundler.close()
pass
def loadDefaultDict():
for item in defaultDict:
db_hundler = CommonDB()
db_hundler.execute('create table if not exists ' + item +
'(name VARCHAR(100) PRIMARY KEY,\
parent VARCHAR (100),abbr VARCHAR (100))')
db_hundler.commit()
db_hundler.close()
with open(defaultLoc + item + '.txt', 'r') as f:
for line in f:
temp = line.rstrip('\n').split(' : ')
name = temp[0]
parent = temp[1]
abbr = ""
if len(temp) == 3:
abbr = temp[2]
db_hundler = CommonDB()
db_hundler.execute(
'SELECT * FROM ' + item + ' WHERE name = %s', (name))
res = db_hundler.fetchone()
if isinstance(res, tuple):
db_hundler.execute(
"UPDATE " + item +
" SET parent = %s, abbr = %s WHERE name = %s",
(parent, abbr, name))
else:
db_hundler.execute(
"INSERT INTO " + item +
"(name, parent, abbr) VALUES(%s, %s, %s)",
(name, parent, abbr))
db_hundler.commit()
db_hundler.close()
pass