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': '获取译语成功'}