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
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 addProblem(id, ojname, newParent):
data = {}
res = get_sources(id, ojname)
source = ""
if len(res) > 0:
source = source_tree.build_source_tree.simpleSource(res[0][0])
print(source)
try:
db_hundler = CommonDB()
db_hundler.execute("SELECT parent FROM sourcetreeref WHERE source = %s", \
(source))
res = db_hundler.fetchall()
length = len(res)
if length > 0:
oldParent = res[0][0]
if oldParent == newParent:
data["res"] = "error"
data["msg"] = "无效位置"
return data
db_hundler.execute("UPDATE sourcetreeref SET parent = %s, isUser = %s WHERE source = %s", \
(newParent, 1, source))
db_hundler.commit()
db_hundler.close()
fixProblemCount(oldParent, -length)
fixProblemCount(newParent, +length)
data["res"] = "success"
data["msg"] = "更新题目成功"
except Exception as e:
data["res"] = "error"
data["msg"] = str(e)
return data
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 delete_knowledge_tree_node(_node_id):
_data = {}
try:
_db = CommonDB()
# 找子节点并删除
sql = "SELECT id FROM " + DB_TABLE_NAME + " WHERE pid=%s"
params = (_node_id, )
_db.execute(sql, params)
_reses = _db.fetchall()
_db.close()
for _res in _reses:
_cid = _res[0]
delete_knowledge_tree_node(_cid)
# print("cid:", _cid)
pass
# 删除该节点
_db = CommonDB()
sql = "DELETE FROM " + DB_TABLE_NAME + " WHERE id=%s"
params = (_node_id, )
_db.execute(sql, params)
_db.commit()
_db.close()
_data["res"] = "success"
_data["msg"] = "删除节点成功"
except Exception as e:
_data["res"] = "error"
_data["msg"] = "删除节点时出错：" + str(e)
import traceback
traceback.print_exc()
pass
return _data
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 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 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 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 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 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 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 fixProblemCount(name, num):
if name is None or name == "":
return False
try:
db_hundler = CommonDB()
db_hundler.execute(
"SELECT parent, haveProblem FROM sourcetree WHERE name = %s",
(name))
res = db_hundler.fetchone()
db_hundler.close()
if isinstance(res, tuple):
cnt = max(0, int(res[1]) + num)
db_hundler = CommonDB()
db_hundler.execute(
"UPDATE sourcetree SET haveProblem = %s WHERE name = %s",
(cnt, name))
db_hundler.commit()
db_hundler.close()
if name == 'Root':
return True
return fixProblemCount(res[0], num)
except Exception as e:
print(e)
return False
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
