def post(self): data = request.get_json() or request.form courseId = int(data['courseId']) title = data['title'] db = Mysql() topicid = get_id() db.insertOne( """ insert into Topic (`topic_id`, `title`, `course_id`, `section_count`) values(%s, %s, %s, %s); """,(topicid, title, courseId, 0) ) db.modify( """ update Course set topic_count=topic_count+1 where course_id=%s; """, (courseId,) ) db.close() return { "state": "success", "topic_id": str(topicid) }
def get(self): topicId = int(request.args.get("topicId")) db = Mysql() title = db.getOne( """ select title from Topic where topic_id=%s; """, (topicId, )) if title: db.close() return title
def get(self): courseId = int(request.args.get("courseId")) db = Mysql() title = db.getOne( """ select title from Course where course_id=%s; """, (courseId, )) if title: db.close() return title
def post(self): """ POST /api/createCourse """ # Deal with request.data data = request.get_json() or request.form userId = int(data['userId']) print(userId) courseTitle = data['courseTitle'] courseDescription = data['courseDescription'] # topicCount = data['topics']['count'] # topics = data['topics']['details'] # Generate course_id courseId = get_id() respData = {} db = Mysql() # SQL Statements db.insertOne( """ insert into Course (`course_id`, `title`, `description`, `teacher_id`, `is_valid`) values (%s, %s, %s, %s, %s); """, (courseId, courseTitle, courseDescription, userId, 0)) res = db.getOne( """ select * from Course where course_id=%s; """, (courseId, )) res = db.getOne( """ select course_count from User where user_id=%s; """, (userId, )) if res: courseCount = res['course_count'] + 1 db.modify( """ update User set course_count=%s where user_id=%s; """, (courseCount, userId)) respData = {"status": True, "course_id": str(courseId), "data": res} db.close() return respData
def get(self): # data = request.args.get("userId") userId = request.args.get("userId") # character = request.args.get("character") db = Mysql() # Query Statements res = db.getOne( """ select course_count from User where user_id=%s; """, (userId,) )['course_count'] if res: if res <= 5: page = 0 courseRes = db.getAll( """ select * from Course where teacher_id=%s; """, (userId,) ) courseCount = res else: page = res/5+1 if res%5 else res/5 courseRes = db.getSome( """ select * from Course where teacher_id=%s; """, 5, (userId,) ) courseCount = 5 courseDetail = [] for i in range(0, courseCount): courseDetail.append({ "id": str(courseRes[i]["course_id"]), "title": courseRes[i]['title'], "count": [courseRes[i]['topic_count'], courseRes[i]['section_count'], courseRes[i]['practice_count'], courseRes[i]['student_count']] }) respData = { "state": "success", "count": courseCount, "page": page, "courses": courseDetail } db.close() return respData
def post(self): data = request.get_json() or request.form username = data['username'] password = data['password'] db = Mysql() res = db.getOne( """ select password from User where school_id=%s; """, (username, )) if (res == False): db.close() return {"state": False, "reason": "User Not Found."} elif password == res['password']: d = db.getOne( """ select * from User where school_id=%s; """, (username, )) # print(d["user_id"]) db.close() return { "state": True, "reason": "Success!", "data": { "username": username, "character": d['character'], "uid": str(d["user_id"]) # "origindata": d } } else: db.close() return {"state": False, "reason": "Wrong User/Password."}
#!/bin/python ''' return the args type information as xml format: for example: <vms> <vm> <id></id> <name></name> </vm> <vm>...</vm> . . . </vms> ''' from dbop import Mysql vms_xml = '<?xml version="1.0" encoding="utf-8"?>\n' vms_xml += '<vms>\n' sql_conn = Mysql() sql_conn.connect() data = sql_conn.query("select * from vmtb") for x in data: vms_xml+= '<vm>\n' vms_xml+= '<id>'+str(x[0])+'</id>\n' vms_xml+= '<name>'+str(x[1])+'</name>\n' vms_xml+= '</vm>\n' vms_xml+='</vms>' sql_conn.close() print vms_xml
#!/bin/python ''' return the args type information as xml format: for example: <argtypes> <types> <id></id> <name></name> </types> <types>...</types> . . . </argtypes> ''' from dbop import Mysql types_xml = '<?xml version="1.0" encoding="utf-8"?>\n' types_xml += '<argtypes>\n' sql_conn = Mysql() sql_conn.connect() data = sql_conn.query("select * from argtypestb") for x in data: types_xml+= '<types>\n' types_xml+= '<id>'+str(x[0])+'</id>\n' types_xml+= '<name>'+str(x[1])+'</name>\n' types_xml+= '</types>\n' types_xml+='</argtypes>' sql_conn.close() print types_xml
def delete(self): topicId = int(request.args.get("topicId")) courseId = int(request.args.get("courseId")) db = Mysql() # Delete Topic itself db.delete( """ delete from Topic where topic_id=%s; """,(topicId,) ) db.modify( """ update Practice set relation=3 where topic_id=%s and relation=1; """, (topicId,) ) # Get Section list and delete sectionIds = db.getAll( """ select section_id as id from Section where topic_id=%s; """,(topicId,) ) if sectionIds: cnt = len(sectionIds) # Delete all sections related with the topic db.delete( """ delete from Section where topic_id=%s; """,(topicId,) ) # Delete the entities related with sections for section in sectionIds: id = section["id"] db.delete( """ delete from Entity where section_id=%s; """,(id,) ) # Untie the relationship of practice for section in sectionIds: id = section["id"] db.modify( """ update Practice set relation=3 where section_id=%s and relation=2; """, (id,) ) # Delete the learning process related with deleted sections pd = db.getAll( """ select `student_id`,`process_detail` where course_id=%s; """, (courseId,) ) if pd: for each in pd: process = eval(each["process_detail"]) for section in sectionIds: process.pop(str(section["id"])) db.modify( """ update StudentCourse set process=detail=%s where student_id=%s and course_id=%s; """, (str(process),each["student_id"],courseId) ) else : cnt = 0 db.modify( """ update Course set topic_count=topic_count-1, section_count=section_count-%s where course_id=%s; """, (cnt, courseId) ) db.close() return { "state": "success" }
def get(self): print ("Get the Request!") userId = request.args.get("userId") # character = request.args.get("character") db = Mysql() # Query Statements res = db.getOne( """ select course_count from User where user_id=%s; """, (userId,) )['course_count'] if res: if res <= 5: page = 0 # courseRes = db.getAll( # """ # select * from Course where course_id in ( # select course_id from StudentCourse where student_id=%s # ); # """, # (userId,) # ) # User JOIN instead of Sub-query --> faster courseRes = db.getAll( """ select a.*, b.status, b.last_section_id from Course a inner join StudentCourse b on a.course_id=b.course_id where b.student_id=%s; """, (userId,) ) courseCount = res else: page = res/5+1 if res%5 else res/5 courseRes = db.getSome( """ select a.*, b.status, b.last_section_id from Course a inner join StudentCourse b on a.course_id=b.course_id where b.student_id=%s; """, 5, (userId,) ) courseCount = 5 courseDetail = [] for i in range(0, courseCount): print (courseRes[i]) courseId = courseRes[i]['course_id'] teacherId = courseRes[i]['teacher_id'] teacherRes = db.getOne( """ select name, icon from User where user_id=%s; """, (teacherId,) ) if courseRes[i]['last_section_id'] is not None: sectionTitle = db.getOne( """ select title from Section where section_id=%s; """, (courseRes[i]['last_section_id'],) )["title"] else: sectionTitle = '' courseDetail.append({ "title": courseRes[i]['title'], "desc": courseRes[i]['description'], "id": str(courseRes[i]['course_id']), "teacher": teacherRes["name"], "lastProgress": { "title": sectionTitle, "id": str(courseRes[i]['last_section_id']) } }) respData = { "state": "success", "count": courseCount, "page": page, "courses": courseDetail } db.close() return respData
def get(self): courseId = int(request.args.get("courseId")) studentId = int(request.args.get("studentId")) db = Mysql() res = db.getOne( """ select * from Course where course_id=%s; """, (courseId,) ) d = db.getOne( """ select process_detail from StudentCourse where course_id=%s and student_id=%s; """, (courseId, studentId) ) t = db.getOne( """ select name from User where user_id=%s; """, (res["teacher_id"]) ) processDetail = eval(d["process_detail"]) resp = {} if res: resp = { "title": res["title"], "desc": res["description"], "teacher": t["name"] } if res["topic_count"] != 0: topics = db.getAll( """ select * from Topic where course_id=%s; """,(courseId,) ) if topics: for topic in topics: sections = [] s = db.getAll( """ select * from Section where topic_id=%s; """,(topic["topic_id"],) ) print (s) if s: for section in s: section["section_id"] = str(section["section_id"]) section["topic_id"] = str(section["topic_id"]) section["process"] = processDetail[section["section_id"]] sections = s else: sections = [] topic["sections"] = sections topic["course_id"] = str(topic["course_id"]) topic["topic_id"] = str(topic["topic_id"]) else: topics = [] resp["topics"] = topics resp["id"] = str(courseId) db.close() return resp