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 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 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 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 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 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_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 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_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 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 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_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 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 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 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 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