def login():
# 获取请求体
params = request.json #type:dict
username = params.get('username')
password = params.get('password')
# 检查数据库
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from user where username="******" and password="******"'.format(
username, password)
cursor.execute(sql)
res = cursor.fetchone()
cursor.close()
conn.close()
except Exception as e:
print(e)
return {'status': 400, 'msg': '系统错误'}
# 成功
if res:
token = create_token(username=username)
return {'status': 200, 'token': token, 'msg': '登录成功'}
else:
return {'status': 300, 'msg': '账号或密码错误！'}
def word():
document = Document()
document.styles['Normal'].font.name = '宋体'
document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')
conn = database_conn()
# 配置结果集为字典形式
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select clothes from clothes'
cursor.execute(sql)
res = cursor.fetchall()
clothes = []
for item in res:
clothes.append(item['clothes'])
print(clothes)
clothes = list(set(clothes))
print(clothes)
for item in clothes:
sql = 'select category from clothes WHERE clothes="{}"'.format(item)
cursor.execute(sql)
res = cursor.fetchall()
for cate in res:
cate_item = cate['category']
sql = 'select * from clothes WHERE clothes="{}" and category="{}"'.format(
item, cate_item)
cursor.execute(sql)
res = cursor.fetchone()
print(res)
def getCategorys():
#获取参数
# 数据库操作
try:
#连接
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#sql
sql = """
select * from category
"""
cursor.execute(sql)
categorys = cursor.fetchall()
# 关闭
cursor.close()
conn.close()
except Exception as e:
print(e)
return {
'status': 300,
'msg': '获取分类失败！'
}
return {
'status': 200,
'categorys': categorys
}
def deleteCategory():
category_list =request.json.get('select')
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
for item in category_list:
try:
sql = """
delete from category WHERE category='{}'
""".format(item)
cursor.execute(sql)
except:
cursor.close()
conn.close()
return {
'status':300,
'msg':'该分类下有博客'
}
cursor.close()
conn.close()
return {
'status': 200,
'msg': '删除成功'
}
def getMenu():
if request.method == 'GET':
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from menu'
cursor.execute(sql)
res = cursor.fetchall()
conn.close()
data_list = []
level_0 = []
for item in res:
if item['level_0'] not in level_0:
level_0.append(item['level_0'])
for item in level_0:
data_list.append({
'id': str(level_0.index(item)),
'name': item,
'children': []
})
for dict in res:
if dict['level_0'] == item:
data_list[level_0.index(item)]['children'].append({
'name':
dict['level_1'],
'path':
dict['path']
})
except:
return {'status': 400, 'data': [], 'msg': '获取菜单失败'}
return {'status': 200, 'data': data_list, 'msg': '获取菜单成功'}
def addCategory():
category=request.json.get('category')
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
try:
sql = """
insert into category(category) VALUES ('{}')
""".format(category)
cursor.execute(sql)
except:
cursor.close()
conn.close()
return {
'status':300,
'msg':'该分类已存在'
}
cursor.close()
conn.close()
return {
'status': 200,
'msg': '添加成功'
}
def getBooksList():
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select code,name from type'
cursor.execute(sql)
res = cursor.fetchall()
conn.close()
except:
return {'status': 400, 'data': [], 'msg': '获取译语失败'}
# 先取数据,再查询
data = []
query_word = request.args.get('query')
select_item = request.args.get('select')
pagenum = request.args.get('pagenum')
pagesize = request.args.get('pagesize')
if query_word:
if select_item:
for item in res:
if query_word in item[select_item]:
data.append(item)
elif select_item == '':
for item in res:
if query_word in item['code'] or query_word in item['name']:
data.append(item)
else:
data = res
total = len(data)
start = (int(pagenum) - 1) * int(pagesize)
end = int(pagenum) * int(pagesize)
data = data[start:end]
return {'status': 200, 'data': data, 'total': total, 'msg': '获取译语成功'}
def getCategory():
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = """
select category from category
"""
cursor.execute(sql)
all_data = cursor.fetchall()
sql = """
select category,count(*) as number from blogs GROUP by category
"""
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
conn.close()
has_data_list=[]
if(not data):
data=[]
for item in data:
has_data_list.append(item['category'])
for item in all_data:
if item['category'] not in has_data_list:
data.append({'category':item['category'],'number':0})
return {
'status':200,
'categorys':data
}
def getBlogInfo():
#获取参数
_id = int(request.args.get('id'))
# 数据库操作
try:
#连接
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#sql
sql = """
select * from blogs WHERE id={}
""".format(_id)
cursor.execute(sql)
data = cursor.fetchone()
# 关闭
cursor.close()
conn.close()
except Exception as e:
print(e)
return {'status': 300, 'msg': '获取数据失败！'}
if not data:
return {'status': 300, 'msg': '文章不存在！'}
blog = {
'id': data['id'],
'title': data['title'],
'date': data['date'].strftime("%Y-%m-%d"),
'content': data['content'],
'category': data['category'],
'completed': bool(data['completed'])
}
return {'status': 200, 'blog': blog}
def wordcloud():
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select name,value from word_count'
cursor.execute(sql)
res = cursor.fetchall()
conn.close()
except:
return {'status': 400, 'data': [], 'msg': '获取译语失败'}
return {'status': 400, 'data': res, 'msg': '获取译语失败'}
def saveBlog():
from time import time as getTime
'''获取参数'''
params = request.json
title = params.get('title')
content = params.get('content')
category = params.get('category')
_id = params.get('id')
# 防止sql语句引号错误
title = pymysql.escape_string(title)
content = pymysql.escape_string(content)
category = pymysql.escape_string(category)
# 保存时间
time = datetime.now().date()
date = time.strftime("%Y-%m-%d")
'''数据库操作'''
blog_id = _id
try:
# 连接
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#
if int(_id) >= 1:
# sql
sql = """
update blogs set title='{}',`date`='{}',content='{}',category='{}',completed={} WHERE id={}
""".format(title, date, content, category, 0, _id)
cursor.execute(sql)
cursor.execute(sql)
else:
sql = """
insert into blogs(title,`date`,content,category,completed) VALUES ('{}','{}','{}','{}',{})
""".format(title, date, content, category, 0)
cursor.execute(sql)
# 返回id
sql = 'SELECT LAST_INSERT_ID() as id'
cursor.execute(sql)
blog_id = (cursor.fetchone())['id']
print(blog_id)
cursor.close()
conn.close()
except Exception as e:
print(e)
return {
'status': 300,
'msg': '保存失败,数据库错误',
}
'''数据处理及返回'''
return {'status': 200, 'msg': "保存成功", 'id': blog_id}
def find_type():
conn = db.database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select `type` from word'
cursor.execute(sql)
res = cursor.fetchall()
conn.close()
li = []
for item in res:
li.append(item['type'])
li = list(set(li))
return li
def getBlogs():
#获取参数
pagenum=request.args.get('pagenum')
pagesize=request.args.get('pagesize')
category=request.args.get('category')
if category=='全部':
# sql
sql = """
select id,title,`date`,completed from blogs
"""
else:
# sql
sql = """
select id,title,`date`,completed from blogs WHERE category='{}'
""".format(category)
# 数据库操作
try:
#连接
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(sql)
data = cursor.fetchall()#type:list
# 关闭
cursor.close()
conn.close()
except Exception as e:
print(e)
return {
'status': 300,
'msg': '获取失败！'
}
"""处理数据"""
total=len(data)
if data:
data.reverse()
start = (int(pagenum) - 1) * int(pagesize)
end = int(pagenum) * int(pagesize)
blogs= data[start:end]
for i in range(0,len(blogs)):
blogs[i]['date']=blogs[i]['date'].strftime("%Y-%m-%d")
"""返回"""
return {
'status': 200,
'blogs': blogs,
'total':total,
}
def category():
li = find_type()
conn = db.database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
codes = []
for item in li:
sql = 'select code from `type` WHERE name="%s"' % (item)
cursor.execute(sql)
res = cursor.fetchall()
codes.append(res[0]['code'])
conn.close()
codes.sort()
return codes
def deleteBlog():
id =request.json.get('id')
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = """
select * from blogs WHERE id='{}'
""".format(id)
cursor.execute(sql)
data=cursor.fetchone()
sql = """
delete from blogs WHERE id='{}'
""".format(id)
cursor.execute(sql)
cursor.close()
conn.close()
except:
return {
'status':300,
'msg':'删除失败'
}
if not data:
return {
'status': 300,
'msg': '文章不存在'
}
"""删除图片"""
try:
pattern = '!\[image\.png\]\([\S]*?\)'
pattern = re.compile(pattern)
result = pattern.findall(string=data['content']) # type:list
imgPaths = []
for item in result:
imgPaths.append(item[13:-1])
for img in imgPaths:
path=os.path.join(baseDir,img)
os.remove(path)
except Exception as e:
print(e)
return {
'status': 200,
'msg': '删除成功'
}
def create_clothes():
conn = db.database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = """
CREATE TABLE IF NOT EXISTS clothes(Id INT PRIMARY KEY AUTO_INCREMENT,clothes VARCHAR(255) CHARACTER SET utf8mb4,
category VARCHAR(255) CHARACTER SET utf8mb4
"""
codes = category()
for item in codes:
s = ',li_' + item
s += ' VARCHAR(255) CHARACTER SET utf8mb4'
sql += s
sql += ')'
cursor.execute(sql)
conn.close()
def degree_1():
conn = database_conn()
# 配置结果集为字典形式
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from word_like'
cursor.execute(sql)
res = cursor.fetchall()
for item in res:
id = item['id']
degree = str(item['degree_num'])
print(degree)
sql = "update word_like set degree='%s' WHERE id=%d" % (degree, id)
cursor.execute(sql)
def deal():
conn = db.database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# book列表
books = book_cate()
books_name = []
for u in books:
sql = 'select name from type WHERE code="{}"'.format(u)
cursor.execute(sql)
res = cursor.fetchone()
books_name.append(res['name'])
print(books_name)
for item in data.keys():
clothes = item
for cate in data[item]:
category = cate
book_li = []
# 构造sql
sql = """
insert into clothes(clothes,category
"""
for i in books:
sql += "," + "li_" + i
sql += ") VALUES('{}'"
for j in range(0, 1 + len(books)):
sql += "," + "'{}'"
sql += ")"
for name in books_name:
sql_type = "select pron from word WHERE `type`='{}' and word='{}'".format(
name, category)
cursor.execute(sql_type)
res = cursor.fetchone()
if res:
book_li.append(res['pron'])
else:
book_li.append('')
print(book_li)
sql = sql.format(clothes, category, *book_li)
cursor.execute(sql)
conn.commit()
conn.close()
def commitBlog():
'''获取参数'''
params = request.json
title = params.get('title')
content = params.get('content')
category = params.get('category')
_id = params.get('id')
# 防止sql语句引号错误
title = pymysql.escape_string(title)
content = pymysql.escape_string(content)
category = pymysql.escape_string(category)
# 保存时间
time = datetime.now().date()
date = time.strftime("%Y-%m-%d")
'''数据库操作'''
try:
# 连接
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
if int(_id) >= 1:
# sql
sql = """
update blogs set title='{}',`date`='{}',content='{}',category='{}',completed={} WHERE id={}
""".format(title, date, content, category, 1, _id)
cursor.execute(sql)
# 要判断该id是否存在
else:
sql = """
insert into blogs(title,`date`,content,category,completed) VALUES ('{}','{}','{}','{}',{})
""".format(title, date, content, category, 1)
cursor.execute(sql)
cursor.close()
conn.close()
except Exception as e:
print(e)
return {'status': 300, 'msg': '保存失败,数据库错误'}
'''数据处理及返回'''
return {'status': 200, 'msg': "保存成功"}
def insert_all():
res = query_all()
conn = db.database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
for item in res:
code = item['code']
word = item['name']
pron = item['pron']
note = item['note']
_class = item['word_languageclass.name']
type_name = item['word_languagetype.name']
sql = """
insert into word(code,word,pron,note,class,`type`)
VALUES ('%s','%s','%s','%s','%s','%s')
""" % (code, word, pron, note, _class, type_name)
cursor.execute(sql)
conn.commit()
print(item)
conn.close()
return None
def word2():
document = Document()
document.styles['Normal'].font.name = '宋体'
document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), '宋体')
conn = database_conn()
# 配置结果集为字典形式
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from clothes'
cursor.execute(sql)
res = cursor.fetchall()
# item为字典
clothes = ''
category = ''
for item in res:
if clothes != item['clothes']:
clothes = item['clothes']
#打印标题
document.add_heading(clothes, level=1)
category = item['category']
#打印条目
document.add_heading(category, level=4)
#打印书: 有内容才打印
item.pop('Id')
item.pop('clothes')
item.pop('category')
for i in item.keys():
# 如果有值
if item[i]:
sql = 'select name from type WHERE code="{}"'.format(i[3:])
cursor.execute(sql)
res = cursor.fetchone()
key = res['name']
value = item[i]
document.add_paragraph('{}: {}'.format(key, value),
style='List Bullet')
conn.close()
document.save('word.docx')
def getWordsList():
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select code,word,pron,note,class,type from word'
cursor.execute(sql)
res = cursor.fetchall()
conn.close()
except:
return {'status': 400, 'data': [], 'msg': '获取词目失败'}
# 先取数据,再查询
data = []
query_word = request.args.get('query')
select_item = request.args.get('select')
pagenum = request.args.get('pagenum')
pagesize = request.args.get('pagesize')
if query_word:
if select_item:
for item in res:
if query_word in item[select_item]:
data.append(item)
elif select_item == '':
for item in res:
is_code = query_word in item['code']
is_word = query_word in item['word']
is_pron = query_word in item['pron']
is_note = query_word in item['note']
is_class = query_word in item['class']
is_type = query_word in item['type']
if is_code or is_word or is_pron or is_note or is_class or is_type:
data.append(item)
else:
data = res
total = len(data)
start = (int(pagenum) - 1) * int(pagesize)
end = int(pagenum) * int(pagesize)
data = data[start:end]
return {'status': 200, 'data': data, 'total': total, 'msg': '获取译语成功'}
def getDegree():
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select type_1,type_2,degree,like_words from word_like'
cursor.execute(sql)
res = cursor.fetchall()
conn.close()
except:
return {'status': 400, 'data': [], 'msg': '获取译语失败'}
# 先取数据,再查询
data = []
query_word = request.args.get('query')
select_item = request.args.get('select')
pagenum = request.args.get('pagenum')
pagesize = request.args.get('pagesize')
if query_word:
if select_item:
for item in res:
if query_word in item[select_item]:
data.append(item)
elif select_item == '':
for item in res:
is_type_1 = query_word in item['type_1']
is_type_2 = query_word in item['type_2']
is_degree = query_word in item['degree']
is_like_words = query_word in item['like_words']
if is_type_1 or is_type_2 or is_degree or is_like_words:
data.append(item)
else:
data = res
total = len(data)
start = (int(pagenum) - 1) * int(pagesize)
end = int(pagenum) * int(pagesize)
data = data[start:end]
return {'status': 200, 'data': data, 'total': total, 'msg': '获取译语成功'}
def ccc():
conn = database_conn()
# 配置结果集为字典形式
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from class'
cursor.execute(sql)
res = cursor.fetchall()
for item in res:
print(item)
for item in res:
code = item['language_type_id']
sql = 'select * from `type` WHERE code="{}"'.format(code)
cursor.execute(sql)
rdd = cursor.fetchone()
type = rdd['name']
print(code, type)
sql = "update class set type='%s' WHERE language_type_id='%s'" % (type,
code)
cursor.execute(sql)
conn.close()
def getTimeline():
# 数据库操作
try:
#连接
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#sql
sql = """
select id,title,date from blogs
"""
cursor.execute(sql)
data = cursor.fetchall()
# 关闭
cursor.close()
conn.close()
except Exception as e:
print(e)
return {'status': 300, 'msg': '获取数据失败！'}
if not data:
data = []
for i in range(0, len(data)):
data[i]['date'] = data[i]['date'].strftime("%Y-%m-%d")
return {'status': 200, 'blog': data}
def getBooksList():
try:
conn = database_conn()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from clothes'
cursor.execute(sql)
res = cursor.fetchall()
sql = "select code,name from type "
cursor.execute(sql)
book_res = cursor.fetchall()
# code和name键值对
book_dict = {}
for item in book_res:
book_dict[item['code']] = item['name']
conn.close()
except:
return {'status': 400, 'data': [], 'msg': '获取译语失败'}
# 先取数据,再查询
data = []
query_word = request.args.get('query')
select_item = request.args.get('select')
pagenum = request.args.get('pagenum')
pagesize = request.args.get('pagesize')
if query_word:
if select_item:
for item in res:
if query_word in item[select_item]:
data.append(item)
elif select_item == '':
for item in res:
if query_word in item['category'] or query_word in item[
'clothes']:
data.append(item)
else:
data = res
# 取出book
# 数据格式: [{'clothes':'','category':'','books':[{'book':'','word':''}]},]
new_data = []
for item in data:
# 每条数据
dict = {}
dict['books'] = []
for key in item.keys():
if key == 'Id':
pass
elif key == 'clothes' or key == 'category':
dict[key] = item[key]
elif item[key]:
dict['books'].append({
'book': book_dict[key[3:]],
'word': item[key]
})
new_data.append(dict)
total = len(new_data)
start = (int(pagenum) - 1) * int(pagesize)
end = int(pagenum) * int(pagesize)
new_data = new_data[start:end]
return {'status': 200, 'data': new_data, 'total': total, 'msg': '获取译语成功'}
