async def update_courseplan(request): courseplan_sn = request.match_info.get("courseplan_sn") course = await request.json() #赋予的值为课程名,将课程名转为课程号 with db_block() as db: db.execute( """ select sn from course where name = %(cou_name)s """, dict(cou_name=course['name'])) record = db.fetch_first() course['name'] = record.sn with db_block() as db: db.execute( """ UPDATE courseplan SET cou_sn=%(name)s, term=%(term)s, place=%(place)s, time=%(time)s WHERE sn=%(courseplan_sn)s; """, course) return web.Response(text=json_dumps(course), content_type="application/json")
async def get_student_list(request): with db_block() as db: db.execute(""" SELECT sn AS stu_sn, no AS stu_no, name AS stu_name, gender, enrolled FROM student """) data = list(asdict(r) for r in db) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_course_list(request): with db_block() as db: db.execute(""" SELECT sn AS cou_sn, no AS cou_no, name AS cou_name, place, time FROM course """) data = list(asdict(r) for r in db) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_student_list(request): with db_block() as db: db.execute(""" SELECT sn AS cur_sn, no AS cur_no, name AS cur_name, starttime AS cur_sta, place AS cur_pla,term AS cur_term FROM course """) data = list(asdict(r) for r in db) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_courseplan_list(request): with db_block() as db: db.execute(""" SELECT courseplan.sn as courseplan_sn, name,no,place,time,term FROM course,courseplan where course.sn = courseplan.cou_sn order by name,courseplan_sn; """) data = list(asdict(r) for r in db) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_courseplan_list(request): courseplan_sn = request.match_info.get("courseplan_sn") with db_block() as db: db.execute(""" select stu_sn from course_grade where courseplan_sn = %(courseplan_sn)s; """,dict(courseplan_sn = courseplan_sn)) data = list(asdict(r) for r in db) return web.Response(text=json_dumps(data), content_type="application/json")
async def new_courseplan(request): choose_course = await request.json() choose_course['stu_sn'] = int(choose_course['stu_sn']) choose_course['courseplan_sn'] = int(choose_course['courseplan_sn']) #将传入数据中的课程名,改成课程序号 with db_block() as db: db.execute(""" INSERT INTO course_grade (stu_sn, courseplan_sn, grade) VALUES(%(stu_sn)s, %(courseplan_sn)s,%(grade)s) """, choose_course) return web.Response(text=json_dumps(choose_course), content_type="application/json")
async def get_plan_list(request): with db_block() as db: db.execute(""" SELECT p.sn AS plan_sn,p.plan_xueqi,p.plan_name,p.plan_time,p.plan_jieci,p.plan_didian,c.name as cuo_name,c.sn as cuo_sn FROM plan AS p INNER JOIN course as c ON p.plan_name = c.sn ORDER BY plan_sn; """) data = list(asdict(r) for r in db) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_addgrade_list(request): courseplan_sn = request.match_info.get("courseplan_sn") with db_block() as db: db.execute( """ SELECT name,grade,stu_sn FROM course_grade,student where student.sn = course_grade.stu_sn and courseplan_sn = %(courseplan_sn)s """, dict(courseplan_sn=courseplan_sn)) data = list(asdict(r) for r in db) for item in data: item['grade'] = float(item['grade']) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_courseplan_profile(request): courseplan_sn = request.match_info.get("courseplan_sn") with db_block() as db: db.execute( """ SELECT stu_sn FROM course_grade WHERE courseplan_sn=%(courseplan_sn)s; """, dict(courseplan_sn=courseplan_sn)) data = list(asdict(r) for r in db) return web.Response(text=json_dumps(data), content_type="application/json")
async def update_course(request): course_sn = request.match_info.get("course_sn") course = await request.json() with db_block() as db: db.execute(""" UPDATE course SET no=%(course_no)s, name=%(course_name)s, teacher=%(teacher)s WHERE sn=%(course_sn)s; """, course) return web.Response(text=json_dumps(course), content_type="application/json")
async def get_course(request): cou_sn = request.match_info.get("cou_sn") with db_block() as db: db.execute( """ SELECT course.name as cou_name,student.name as stu_name, grade FROM course_grade,course,student WHERE course.sn=course_grade.cou_sn and student.sn=course_grade.stu_sn and cou_sn=%(cou_sn)s; """, dict(cou_sn=cou_sn)) data = list(asdict(r) for r in db) for a in data: a['grade'] = float(a['grade']) print(data) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_grade_list(request): with db_block() as db: db.execute(""" select student.name as stu_name,stu_sn,cou_sn,course.name as cou_name,grade from course,student,course_grade,courseplan where student.sn = course_grade.stu_sn and course_grade.courseplan_sn = courseplan.sn and courseplan.cou_sn = course.sn order by cou_name; """) data = list(asdict(r) for r in db) for item in data: item["grade"] = int(item["grade"]) return web.Response(text=json_dumps(data), content_type="application/json")
async def new_student(request): course = await request.json() with db_block() as db: db.execute(""" INSERT INTO course (no, name, starttime, place) VALUES(%(cur_no)s, %(cur_name)s, %(cur_sta)s, %(cur_pla)s) RETURNING sn; """, course) record = db.fetch_first() course["cur_sn"] = record.sn return web.Response(text=json_dumps(course), content_type="application/json")
async def new_course(request): course = await request.json() with db_block() as db: db.execute(""" INSERT INTO course (no, name, teacher) VALUES(%(course_no)s, %(course_name)s, %(teacher)s) RETURNING sn; """, course) record = db.fetch_first() course["course_sn"] = record.sn print(course) return web.Response(text=json_dumps(course), content_type="application/json")
async def get_course_profile(request): course_sn = request.match_info.get("course_sn") with db_block() as db: db.execute(""" SELECT sn AS course_sn, no AS course_no, name AS course_name, teacher FROM course WHERE sn=%(course_sn)s """, dict(course_sn=course_sn)) record = db.fetch_first() if record is None: return web.HTTPNotFound(text=f"no such course: course_sn={course_sn}") data = asdict(record) return web.Response(text=json_dumps(data), content_type="application/json")
async def new_plan(request): plan = await request.json() with db_block() as db: db.execute(""" INSERT INTO plan (plan_xueqi,plan_name,plan_time,plan_jieci,plan_didian) VALUES( %(plan_xueqi)s, %(plan_name)s, %(plan_time)s, %(plan_jieci)s,%(plan_didian)s) RETURNING sn; """, plan) record = db.fetch_first() plan["plan_sn"] = record.sn print(plan) return web.Response(text=json_dumps(plan), content_type="application/json")
async def get_course_profile(request): cou_sn = request.match_info.get("cou_sn") #用字典类型传递参数(优点:易读,且不易搞错位置) with db_block() as db:#SQL语句中,参数采用%(key)s语法格式 db.execute(""" SELECT sn AS cou_sn, no AS cou_no, name AS cou_name, teacher, term, room, week, day, jie FROM course WHERE sn=%(cou_sn)s """, dict(cou_sn=cou_sn)) record = db.fetch_first() if record is None: return web.HTTPNotFound(text=f"no such course: cou_sn={cou_sn}") data = asdict(record) return web.Response(text=json_dumps(data), content_type="application/json")
async def get_student_profile(request): stu_sn = request.match_info.get("stu_sn") with db_block() as db: db.execute( """ SELECT sn AS stu_sn, no AS stu_no, name AS stu_name, gender, enrolled FROM student WHERE sn=%(stu_sn)s """, dict(stu_sn=stu_sn)) record = db.fetch_first() if record is None: return web.HTTPNotFound(text=f"no such student: stu_sn={stu_sn}") data = asdict(record) return web.Response(text=json_dumps(data), content_type="application/json")
async def update_student(request): cur_sn = request.match_info.get("cur_sn") course = await request.json() course["cur_sn"] = cur_sn with db_block() as db: db.execute(""" UPDATE course SET no=%(cur_no)s, name=%(cur_name)s, starttime=%(cur_sta)s, place=%(cur_pla)s WHERE sn=%(cur_sn)s; """, course) return web.Response(text=json_dumps(course), content_type="application/json")
async def update_plan(request): plan_sn = request.match_info.get("plan_sn") plan = await request.json() plan["plan_sn"] = plan_sn with db_block() as db: db.execute(""" UPDATE plan SET plan_xueqi=%(plan_xueqi)s, plan_name=%(plan_name)s, plan_time=%(plan_time)s, plan_jieci=%(plan_jieci)s,plan_didian=%(plan_didian)s WHERE sn=%(plan_sn)s; """, plan) return web.Response(text=json_dumps(plan), content_type="application/json")
async def update_course(request): cou_sn = request.match_info.get("cou_sn") course = await request.json() #if not course.get('enrolled'): # course['enrolled'] = datetime.date(Mon ,1, 1) course["cou_sn"] = cou_sn with db_block() as db: db.execute(""" UPDATE course SET no=%(cou_no)s, name=%(cou_name)s, teacher = %(teacher)s, term = %(term)s, room = %(room)s, week = %(week)s, day = %(day)s, jie = %(jie)s WHERE sn=%(cou_sn)s; """, course) return web.Response(text=json_dumps(course), content_type="application/json")
async def new_course(request): course = await request.json() # if not course.get('enrolled'): # course['enrolled'] = datetime.date( Mon,1, 1) with db_block() as db: db.execute(""" INSERT INTO course (no, name,teacher, term, room, week, day, jie) VALUES(%(cou_no)s, %(cou_name)s, %(teacher)s, %(term)s, %(room)s, %(week)s, %(day)s, %(jie)s) RETURNING sn; """,course) record = db.fetch_first() course["cou_sn"] = record.sn print(course) return web.Response(text=json_dumps(course), content_type="application/json")
async def update_course(request): cou_sn = request.match_info.get("cou_sn") course = await request.json() course["cou_sn"] = cou_sn with db_block() as db: db.execute( """ UPDATE course SET term=%(cou_term)s, no=%(cou_no)s, name=%(cou_name)s,place=%(place)s, time=%(time)s WHERE sn=%(cou_sn)s; """, course) return web.Response(text=json_dumps(course), content_type="application/json")
async def update_grade(request): stu_sn = int(request.match_info.get("stu_sn")) courseplan_sn = int(request.match_info.get("courseplan_sn")) grade = await request.json() with db_block() as db: db.execute( """ UPDATE course_grade SET grade=%(grade)s WHERE stu_sn = %(stu_sn)s and courseplan_sn=%(courseplan_sn)s; """, dict(grade=grade['grade'], stu_sn=stu_sn, courseplan_sn=courseplan_sn)) return web.Response(text=json_dumps(grade), content_type="application/json")
async def new_student(request): student = await request.json() #为入学时间提供默认值 if not student.get('enrolled'): student['enrolled'] = datetime.date(1900, 1, 1) with db_block() as db: db.execute( """ INSERT INTO student (no, name, gender, enrolled) VALUES(%(stu_no)s, %(stu_name)s, %(gender)s, %(enrolled)s) RETURNING sn; """, student) record = db.fetch_first() student["stu_sn"] = record.sn return web.Response(text=json_dumps(student), content_type="application/json")
async def update_student(request): stu_sn = request.match_info.get("stu_sn") student = await request.json() if not student.get('enrolled'): student['enrolled'] = datetime.date(1900, 1, 1) student["stu_sn"] = stu_sn with db_block() as db: db.execute( """ UPDATE student SET no=%(stu_no)s, name=%(stu_name)s, gender=%(gender)s, enrolled=%(enrolled)s WHERE sn=%(stu_sn)s; """, student) return web.Response(text=json_dumps(student), content_type="application/json")
async def update_course(request): cou_sn = request.match_info.get("cou_sn") course = await request.json() #if not course.get('class'): #course['class'] = datetime.date(1900, 1, 1) course["cou_sn"] = cou_sn with db_block() as db: db.execute( """ UPDATE course SET name=%(cou_name)s, place=%(place)s, week=%(week)s, class=%(class)s WHERE sn=%(cou_sn)s; """, course) return web.Response(text=json_dumps(course), content_type="application/json")
async def new_course(request): course = await request.json() if not course.get('place'): course['place'] = '一公教' with db_block() as db: db.execute( """ INSERT INTO course (no, name, place, time) VALUES(%(cou_no)s, %(cou_name)s, %(place)s, %(time)s) RETURNING sn; """, course) record = db.fetch_first() course["cou_sn"] = record.sn print(course) return web.Response(text=json_dumps(course), content_type="application/json")
async def new_course(request): course = await request.json() #if not course.get('class'): #course['class'] = datetime.date(1900, 1, 1) with db_block() as db: db.execute( """ INSERT INTO course (name, place, week, class) VALUES(%(cou_name)s, %(place)s, %(week)s, %(class)s) RETURNING sn; """, course) record = db.fetch_first() course["cou_sn"] = record.sn print(course) return web.Response(text=json_dumps(course), content_type="application/json")