def searchBook(): print(request.data) data = json.loads(request.data) print(data) searchKey = data["searchKey"] sql = "select * from ebook where concat(title, author,createTime) like '%%%s%%' order by createTime asc" % searchKey print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) all_data = cursor.fetchall() print("all", all_data) key_list = ["ebookId", "title", "author", "createTime", "contentHtml"] resp_data = tuple_to_dict(key_list, all_data) print("resp", resp_data) resp = do_response("success", resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def addClassNotification(): print(request.data) data = json.loads(request.data) print(data) className = data['className'] title = data['title'] content = data["content"] noticeTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") sql = 'insert into class_notification(className, title, content, noticeTime) \ values("%s","%s","%s","%s") ' % (className, title, content, noticeTime) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor =db2.cursor() cursor.execute(sql) print(sql) db2.commit() resp = do_response("success","true", 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def do_paper_one(): print(request.data) data = json.loads(request.data) print(data) askContent = data["askContent"] stuPaperId = data["stuPaperId"] selectContent = data["selectContent"] sql = "update student_paper set askContent = '%s',selectContent = '%s', status = 1 where stuPaperId = %d" % ( askContent, selectContent, int(stuPaperId)) print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) print(sql) db2.commit() resp = do_response("success", "true", 200) except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) cursor.close() db2.close() return make_response(resp, 200)
def get_bbs_comment_by_topic(): print(request.data) data = json.loads(request.data) print(data) bbsId = data['bbsId'] sql = 'select * from bbs_comment where bbsId = %d order by createTime asc' \ % (int(bbsId)) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor =db2.cursor() cursor.execute(sql) all_data = cursor.fetchall() print("all",all_data) key_list = ["commentId","bbsId","author","avatar","content","createTime"] resp_data = tuple_to_dict(key_list, all_data) print("resp",resp_data) resp = do_response("success",resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def create_bbs_comment(): print(request.data) data = json.loads(request.data) print(data) author = data['author'] bbsId = data['bbsId'] avatar = data['avatar'] content = data['content'] createTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") sql = 'insert into bbs_comment(bbsId,author,avatar, \ content,createTime ) values("%s","%s", "%s","%s", "%s")' \ % (bbsId,author,avatar,content,createTime ) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") db2.cursor().execute(sql) db2.commit() resp = do_response("success", "true", 200) db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def register(): print(request.data) data = eval(request.data) username = data['username'] password = data['password'] types = data['types'] password = MD5(password) sql = 'insert into user(username,password,types) values("%s","%s", %d)' % ( username, password, int(types)) print(sql) data = '' status = 200 try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") db2.cursor().execute(sql) db2.commit() resp = do_response("success", "true", 200) except Exception as e: print(e) data = str(e) print(e) resp = do_response("error", data, 400) db2.close() return make_response(resp, 200)
def addClassNotificatio(): print(request.data) data = json.loads(request.data) print(data) className = data['className'] sql = 'select * from class_notification where className = "%s"' %(className) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor =db2.cursor() cursor.execute(sql) print(sql) all_data = cursor.fetchall() print("all",all_data) key_list = ["notificationId","className","title","content","noticeTime"] resp_data = tuple_to_dict(key_list, all_data) print("resp",resp_data) resp = do_response("success",resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def student_paper(): print(request.data) data = json.loads(request.data) print(data) studentName = data["studentName"] sql = "select stuPaperId,studentName,a.paperId,a.askContent, a.selectContent,a.createTime,score,remark,status, b.paperName,b.paperDescription from student_paper a left join teacher_paper b on a.paperId = b.paperId where a.studentName = '%s' \ order by createTime desc" % (studentName) print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) all_data = cursor.fetchall() print("all", all_data) key_list = ["stuPaperId", "studentName", "paperId", "askContent", "selectContent", "createTime", "score", "remark", "status", "paperName", "paperDescription"] resp_data = tuple_to_dict(key_list, all_data) print("resp", resp_data) resp = do_response("success", resp_data, 200) except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) cursor.close() db2.close() return make_response(resp, 200)
def createPaper(): print(request.data) data = json.loads(request.data) print(data) askContent = data["askContent"] paperDescription = data["paperDescription"] paperName = data["paperName"] print(type(data["selectContent"])) print(data["selectContent"]) selectContent = data["selectContent"] print("ss", selectContent) print(type(selectContent)) # json.loads(selectContent) creator = data["creator"] createTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") sql = "insert into teacher_paper(paperName, paperDescription,selectContent, askContent, createTime, creator) values('%s', '%s', '%s', '%s', '%s', '%s') " % ( paperName, paperDescription, selectContent, askContent, createTime, creator) print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) print(sql) db2.commit() resp = do_response("success", "true", 200) except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) cursor.close() db2.close() return make_response(resp, 200)
def getClass(): print("data",request.data) print("sss") data = json.loads(request.data) print(data) teacherName = data['teacherName'] sql = 'select * from class_teacher where teacherName = "%s"' % (teacherName) if teacherName == "": sql = 'select * from class_teacher' print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor =db2.cursor() cursor.execute(sql) print("ssss") all_data = cursor.fetchall() print("all",all_data) key_list = ["className", "teacherName", "description"] resp_data = tuple_to_dict(key_list, all_data) print("resp",resp_data) resp = do_response("success",resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def submitPaper(): print(request.data) data = json.loads(request.data) print(data) askContent = data["askContent"] paperId = data["paperId"] selectContent = data["selectContent"] studentName = data["studentName"] createTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") sql = "insert into student_paper(studentName, paperId, askContent, selectContent, createTime) values('%s','%d','%s','%s','%s') " % ( studentName, int(paperId), askContent, selectContent, createTime) print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) print(sql) db2.commit() resp = do_response("success", "true", 200) except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) cursor.close() db2.close() return make_response(resp, 200)
def get_by_id(): print(request.data) data = json.loads(request.data) print(data) paperId = data["paperId"] sql = "select * from teacher_paper where paperId = '%s'" % (paperId) print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) all_data = cursor.fetchall() print("all", all_data) key_list = ["paperId", "paperName", "paperDescription", "selectContent", "askContent", "createTime", "creator"] resp_data = tuple_to_dict(key_list, all_data) print("resp", resp_data) resp = do_response("success", resp_data, 200) except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) cursor.close() db2.close() return make_response(resp, 200)
def get_class_one(): print(request.data) data = json.loads(request.data) print(data) className = data['className'] sql = ''' select studentName from class_student where className = "%s" \ and status = 1''' % (className) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor =db2.cursor() cursor.execute(sql) print(sql) all_data = cursor.fetchall() print("all",all_data) key_list = ["studentName"] resp_data = tuple_to_dict(key_list, all_data) print("resp",resp_data) resp = do_response("success",resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def get_join_list(): print(request.data) data = json.loads(request.data) print(data) studentName = data['studentName'] sql = 'select a.className,a.teacherName, a.description, b.status \ from class_teacher as a left join class_student as b \ on a.className = b.className \ and b.studentName = "%s"' % (studentName) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor =db2.cursor() cursor.execute(sql) print(sql) all_data = cursor.fetchall() print("all",all_data) key_list = ["className", "teacherName", "description", "status"] resp_data = tuple_to_dict(key_list, all_data) print("resp",resp_data) resp = do_response("success",resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def save_book(): print(request.data) data = json.loads(request.data) print(data) ebookId = data["ebookId"] username = data["username"] contentHtml = data["contentHtml"] createTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") updateTime = createTime sql_one = "select * from ebook_note where ebookId = %d \ and username = '******'" % (ebookId, username) sql_two = "update ebook_note set contentHtml = '%s' , updateTime = '%s' where \ ebookId = %d and username = '******' " % \ (contentHtml, updateTime, ebookId, username) sql_three = ''' insert into ebook_note(ebookId, username, contentHtml, createTime, updateTime) values(%d,"%s",'%s',"%s","%s")''' % ( ebookId, username, contentHtml, createTime, updateTime) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() print(sql_one) cursor.execute(sql_one) all_data = cursor.fetchall() print("all", all_data) if len(all_data) < 1: print(sql_three) cursor.execute(sql_three) else: print(sql_two) cursor.execute(sql_two) db2.commit() resp = do_response("success", "true", 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def delete(): print(request.data) data = json.loads(request.data) print(data) className = data['className'] sql = 'delete from class_teacher where className = "%s"' % (className) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") db2.cursor().execute(sql) db2.commit() resp = do_response("success", "true", 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def get_topic_all(): sql = 'select * from bbs_topic order by createTime desc' print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor =db2.cursor() cursor.execute(sql) all_data = cursor.fetchall() print("all",all_data) key_list = ["idbbs","author","topicName","createTime"] resp_data = tuple_to_dict(key_list, all_data) print("resp",resp_data) resp = do_response("success",resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def create(): print(request.data) data = json.loads(request.data) print(data) teacherName = data['teacherName'] className = data['className'] description = data['description'] sql = 'insert into class_teacher(teacherName,className,description) values("%s","%s", "%s")' % (teacherName,className, description) print(sql) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") db2.cursor().execute(sql) db2.commit() resp = do_response("success", "true", 200) db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def publish_class(): print(request.data) data = json.loads(request.data) print(data) className = data["className"] askContent = data["askContent"] paperId = data["paperId"] selectContent = data["selectContent"] sql_one = "select studentName from class_student where className = '%s' and status = 1" % className try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql_one) all_data = cursor.fetchall() print("all", all_data) createTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") cursor = db2.cursor() for item in all_data: studentName = item[0] sql = "insert into student_paper(studentName, paperId, \ askContent, selectContent, createTime, status) \ values('%s','%d','%s','%s','%s','0') " % \ (studentName, int(paperId), askContent, selectContent, createTime) print(sql) cursor.execute(sql) db2.commit() resp = do_response("success", "true", 200) except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) cursor.close() db2.close() return make_response(resp, 200)
def join(): print(request.data) data = json.loads(request.data) print(data) className = data['className'] studentName = data['studentName'] status = data['status'] sql_one = 'select * from class_student where studentName = "%s" \ and className = "%s"' % (studentName, className) sql_two = 'insert into class_student(studentName,className,status) \ values("%s","%s", %d)' % (studentName,className, status) sql_three = 'update class_student set status = %d \ where studentName = "%s" \ and className = "%s"' % (status, studentName, className) try: db2 = pymysql.connect(host="localhost",user="******",password="******",db="ebook",port=3306,use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql_one) print(sql_one) all_data = cursor.fetchall() print("all",all_data) if len(all_data) < 1: cursor.execute(sql_two) print(sql_two) else: cursor.execute(sql_three) print(sql_three) db2.commit() resp = do_response("success", "true", 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def login(): data = eval(request.data) username = data['username'] password = MD5(data['password']) types = data['types'] print(request.data) sql = 'select * from user where username = "******" and password = "******" and types = "%d"' % ( username, password, types) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) print(sql) all_data = cursor.fetchall() print(all_data) if len(all_data) < 1: msg = 'error' data = 'username or password error' resp = do_response(msg, data, 400) else: msg = 'success' data = generate_auth_token(username) session['token'] = data resp = do_response(msg, data, 200) except Exception as e: print(e) resp = do_response("error", str(e), 400) cursor.close() db2.close() return make_response(resp, 200)
def get_book_list(): # print(request.data) # data = json.loads(request.data) # print(data) # askContent = data["askContent"] # createTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") # sql = "insert into teacher_paper(paperName, paperDescription, \ # selectContent, askContent, createTime, creator) \ # values('%s','%s','%s','%s','%s','%s') " % \ # (paperName, paperDescription,selectContent, \ # askContent, createTime, creator) sql = "select * from ebook order by createTime asc" print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) all_data = cursor.fetchall() print("all", all_data) key_list = ["ebookId", "title", "author", "createTime", "contentHtml"] resp_data = tuple_to_dict(key_list, all_data) print("resp", resp_data) resp = do_response("success", resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)
def load_book(): print(request.data) data = json.loads(request.data) print(data) ebookId = data["ebookId"] username = data["username"] sql = "select * from ebook_note where ebookId = %d and username \ = '%s'" % (ebookId, username) print(sql) try: db2 = pymysql.connect(host="localhost", user="******", password="******", db="ebook", port=3306, use_unicode=True, charset="utf8mb4") cursor = db2.cursor() cursor.execute(sql) all_data = cursor.fetchall() print("all", all_data) key_list = [ "noteId", "ebookId", "username", "contentHtml", "createTime", "updateTime" ] resp_data = tuple_to_dict(key_list, all_data) print("resp", resp_data) resp = do_response("success", resp_data, 200) cursor.close() db2.close() except Exception as e: print(e) data = str(e) resp = do_response("error", data, 400) return make_response(resp, 200)