def enroll_create():
# 학번으로 학생 테이블 pk 가져오기
student_no = request.form.get("student_no")
student = Base.classes.student
student_id = db_session.query(student).filter_by(
student_no=student_no).one().id
# 과목 코드와 학기 정보로 최신 개설강좌 pk 가져오기
subject_code = request.form.get("subject_code")
subject = Base.classes.subject
subject_id = db_session.query(subject).filter_by(
code=subject_code).one().id
semester = request.form.get("semester")
lecture = Base.classes.lecture
lecture_id = db_session.query(lecture).filter_by(semester=semester)\
.filter_by(subject_id=subject_id).first().id
grade = request.form.get("grade", None)
# enroll 생성 및 저장
enroll = Base.classes.enroll(student_id=student_id,
lecture_id=lecture_id,
grade=grade)
db_session.add(enroll)
db_session.commit()
return redirect(url_for("enroll_list"))
def show_students():
model = Base.classes.student
# joined load 는 left outer join 이 기본. (타겟 테이블 조회 시 쿼리 단일화 용도에 적절)
# inner join 이 필요한 경우 joined load 의 inner join 옵션 지정
students = db_session.query(model).options(
joinedload(model.school), joinedload(model.department)).all()
return render_template('students.html', students=students, urls=urls)
def show_enrolls():
model = Base.classes.enroll
lecture = Base.classes.lecture
enrolls = db_session.query(model).order_by(model.id).options(
joinedload(model.student),
joinedload(model.lecture).options(joinedload(
lecture.subject))).all()
return render_template('enrolls.html', enrolls=enrolls, urls=urls)
def show_lectures():
model = Base.classes.lecture
subject = Base.classes.subject
room = Base.classes.room
# 2차 relation 참조 필요합 경우 joined load 체이닝으로 ORM 쿼리 통합
lectures = db_session.query(model).options(
joinedload(model.subject).options(joinedload(subject.department)),
joinedload(model.room).options(joinedload(room.building)),
joinedload(model.professor)).all()
return render_template('lectures.html', lectures=lectures, urls=urls)
def enroll_form():
model = Base.classes.enroll
db_session.query(model)
return render_template('enroll_form.html', urls=urls)
def enroll_delete(pk):
model = Base.classes.enroll
db_session.query(model).filter_by(id=pk).delete()
db_session.commit()
return redirect(url_for("enroll_list"))
def enroll_update(pk):
grade = request.form.get("grade")
model = Base.classes.enroll
db_session.query(model).filter(model.id == pk).update({"grade": grade})
db_session.commit()
return redirect(url_for("enroll_list"))