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")