作业需求:
用户角色，讲师＼学员， 用户登陆后根据角色不同，能做的事情不同，分别如下
讲师视图
管理班级，可创建班级，根据学员qq号把学员加入班级
可创建指定班级的上课纪录，注意一节上课纪录对应多条学员的上课纪录， 即每节课都有整班学员上， 为了纪录每位学员的学习成绩，需在创建每节上课纪录是，同时 为这个班的每位学员创建一条上课纪录
为学员批改成绩， 一条一条的手动修改成绩
学员视图
提交作业
查看作业成绩
一个学员可以同时属于多个班级，就像报了Linux的同时也可以报名Python一样， 所以提交作业时需先选择班级，再选择具体上课的节数
附加：学员可以查看自己的班级成绩排名
数据库关联示意图：
思路解析：
按照数据库关联，操作班级教室分数和上课记录
讲师视图
1.管理班级，可创建班级
操作class表，输入classname后显示现在有几个课程几个课程表，然后让讲师创建TeacheCourse关联后才能创建班级
2. 根据学员qq号把学员加入班级
操作StudentTeachClass表，把qq号加入班级ID，然后显示，课程，课程表，班级，学员，
3. 可创建指定班级的上课纪录，注意一节上课纪录对应多条学员的上课纪录，即每节课都有整班学员上，为了纪录每位学员的学习成绩，需在创建每节上课纪录，同时为这个班的每位学员创建一条上课纪录
进入后显示班级，根据进入时候的班级讲师ID，操作teacherclass，操作teacher_course，找到对应的学生查看对应的班级的童鞋，操作RecordStudent
4. 为学员批改成绩， 一条一条的手动修改成绩
1. 进入后显示班级，根据进入时候的班级讲师ID，操作teacherclass，操作teacher_course，
2. 查看StudentHomework，查看学生的作业
3. 为学员一条一条修改成绩和创建上课记录，操作RecordScore
学员视图
1. 提交作业
显示课程，班级，上课节数，提交作业，做成绑定
2. 查看作业成绩
查看RecordScore
3. 一个学员可以同时属于多个班级，就像报了Linux的同时也可以报名Python一样， 所以提交作业时需先选择班级，再选择具体上课的节数
4. 附加：学员可以查看自己的班级成绩排名
操作recordscore
排名，读取出所有的成绩和姓名，然后进行排序
Readme:
作者:yaobin
版本: 学员管理系统 示例版本 v0.1
开发环境: python3.6
程序介绍:
讲师视图
管理班级，可创建班级，根据学员qq号把学员加入班级
可创建指定班级的上课纪录，注意一节上课纪录对应多条学员的上课纪录， 即每节课都有整班学员上，
为了纪录每位学员的学习成绩，需在创建每节上课纪录是，同时为这个班的每位学员创建一条上课纪录
为学员批改成绩， 一条一条的手动修改成绩
学员视图
提交作业
查看作业成绩
一个学员可以同时属于多个班级，就像报了Linux的同时也可以报名Python一样， 所以提交作业时需先选择班级，再选择具体上课的节数
附加：学员可以查看自己的班级成绩排名
文件目录结构
├── bin
│ ├── __init__.py
│ └── tiny.py # 主程序
├── conf
│ ├── action_registers.py # 程序命令交互
│ ├── __init__.py
│ └── settings.py # 配置文件
├── log
│ └── __init__.py
├── modules
│ ├── actions.py # 欢迎页和程序命令交互
│ ├── db_conn.py # mysql连接交互
│ ├── utils.py # yaml配置交互
│ └── views.py # 创建表,表数据创建,查看数据库数据交互
│ └── db_insert.py # 插入数据范例
├── Server.zip
└── share
└── examples
├── new_bindhosts.yml # 主机绑定关系配置文件
├── new_groups.yml # 组创建，组关系绑定配置文件
├── new_hosts.yml # 主机配置文件
├── new_remoteusers.yml # 主机用户名密码配置文件
└── new_user.yml # 堡垒机用户配置文件
使用方法：
python3 bin/manage.py syncdb
python3 modules/db_insert.py
python3 bin/manage.py teacher
python3 bin/manage.py student
核心代码：
view.py
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Time:2017/12/15 21:34
__Author__ = 'Sean Yao'
import sqlalchemy.exc
from modules import models
from conf import settings
from modules.utils import print_err, yaml_parser
from modules.db_conn import engine, session
import codecs
def syncdb(argvs):
'''
创建表结构方法
:param argvs:
:return:
'''
print("Syncing DB....")
engine = models.create_engine(settings.ConnParams, echo=True)
models.Base.metadata.create_all(engine) # 创建所有表结构
def auth_teacher():
'''
用户验证
do the user login authentication
:return:
'''
count = 0
while count < 3:
username = input(" 33[32;1mUsername: 33[0m").strip()
if len(username) == 0:
continue
password = input(" 33[32;1mPassword: 33[0m").strip()
if len(password) == 0:
continue
user_obj = session.query(models.Teacher).filter(models.Teacher.username == username,
models.Teacher.password == password).first()
if user_obj:
return user_obj
else:
print("wrong username or password, you have %s more chances." % (3-count-1))
count += 1
else:
print_err("too many attempts.")
def auth_student():
'''
用户验证
do the user login authentication
:return:
'''
count = 0
while count < 3:
username = input(" 33[32;1mUsername: 33[0m").strip()
if len(username) == 0:
continue
password = input(" 33[32;1mPassword: 33[0m").strip()
if len(password) == 0:
continue
user_obj = session.query(models.Student).filter(models.Student.username == username,
models.Student.password == password).first()
if user_obj:
return user_obj
else:
print("wrong username or password, you have %s more chances." % (3-count-1))
count += 1
else:
print_err("too many attempts.")
def list1(dict: dict):
''' 将字典转化为列表 '''
keys = dict.keys()
vals = dict.values()
list = [(key, val) for key, val in zip(keys, vals)]
return list
def welcome_msg(user):
'''
:param user:
:return:
'''
WELCOME_MSG = ''' 33[32;1m
------------- Welcome [%s] login -------------
33[0m''' % user.username
print(WELCOME_MSG)
def show_class(user):
'''
show教室
:param user:
:return:
'''
print('%s 请参照现有课程教室管理' % user.username)
data = session.query(models.ClassRoom).filter_by().all()
print('所有班级')
for index, x in enumerate(data):
print(index, x.classname)
def show_course(user):
'''
展示课程,课程安排,教室
:param user:
:return:
'''
print('你可以创建班级关系或给学生分配班级')
teacher_class_obj = session.query(models.TeacherClass).filter_by(teacher_id=user.id).all()
for i in teacher_class_obj:
course_teacher_obj = session.query(models.TeacheCourse).filter_by(bind_teacher_class_id=i.id).all()
for x in course_teacher_obj:
time_course_obj = session.query(models.CourseClassRecord).filter_by(id=x.bind_course_time_id).first()
print('课堂ID: %s 课程:%s 课程安排:%s 教室:%s' % (x.id, time_course_obj.course.coursename,
time_course_obj.course_time.course_time_name,
i.class_room.classname))
def show_student_class(user):
print('%s 你所在的班级' % user.username)
student_class_obj = session.query(models.StudentTeachClass).filter_by(student_qq=user.qq_number).all()
for i in student_class_obj:
class_teacher_obj = session.query(models.TeacherClass).filter_by(
id=i.teacher_course.bind_teacher_class_id).first()
course_obj = session.query(models.CourseClassRecord).filter_by(
id=i.teacher_course.bind_course_time_id).first()
studentname_obj = session.query(models.Student).filter_by(
qq_number=i.student.qq_number).first()
print('绑定id:%s 课程:%s 课程时间:%s 讲师:%s 教室:%s 学生名:%s 学生qq %s' % (
i.id, course_obj.course.coursename, course_obj.course_time.course_time_name,
class_teacher_obj.teacher.username, class_teacher_obj.class_room.classname,
studentname_obj.username, studentname_obj.qq_number))
def view_student():
'''
查看学生
'''
student_obj = session.query(models.Student).filter_by().all()
for i in student_obj:
# 学生姓名QQ
print('现有学生:%s 学生QQ:%s' % (i.username, i.qq_number))
print("----------- END -----------")
def view_student_class(user):
'''
查看该教室的学生,通过学生和老师id反查
:param user:
:return:
'''
teacher_class_obj = session.query(models.TeacherClass).filter_by(teacher_id=user.id).all()
for i in teacher_class_obj:
teachecourse_obj = session.query(
models.TeacheCourse).filter_by(bind_teacher_class_id=i.id).all()
for y in teachecourse_obj:
student_teachclass_obj = session.query(models.StudentTeachClass).filter_by(teacher_course_id=y.id).all()
for x in student_teachclass_obj:
class_teacher_obj = session.query(models.TeacherClass).filter_by(
id=x.teacher_course.bind_teacher_class_id).first()
course_obj = session.query(models.CourseClassRecord).filter_by(
id=x.teacher_course.bind_course_time_id).first()
studentname_obj = session.query(models.Student).filter_by(
qq_number=x.student.qq_number).first()
print('绑定id:%s 课程:%s 课程时间:%s 讲师:%s 教室:%s 学生名:%s 学生qq %s' % (
x.id, course_obj.course.coursename, course_obj.course_time.course_time_name,
class_teacher_obj.teacher.username, class_teacher_obj.class_room.classname,
studentname_obj.username, studentname_obj.qq_number))
def view_record(user):
'''
查看上课记录
:param user:
:return:
'''
record_student = session.query(models.RecordStudent).filter_by().all()
for i in record_student:
student_teachclass_obj = session.query(models.StudentTeachClass).
filter_by(id=i.student_teach_class_id).first()
class_teacher_obj = session.query(models.TeacherClass).filter_by(
id=student_teachclass_obj.teacher_course.bind_teacher_class_id).first()
course_obj = session.query(models.CourseClassRecord).filter_by(
id=student_teachclass_obj.teacher_course.bind_course_time_id).first()
studentname_obj = session.query(models.Student).filter_by(
qq_number=student_teachclass_obj.student.qq_number).first()
if class_teacher_obj.teacher.username == user.username:
print('绑定id:%s 课程:%s 课程时间:%s 讲师:%s 教室:%s 学生名:%s 学生qq %s 上课记录:%s' % (
student_teachclass_obj.id, course_obj.course.coursename, course_obj.course_time.course_time_name,
class_teacher_obj.teacher.username, class_teacher_obj.class_room.classname,
studentname_obj.username, studentname_obj.qq_number, i.record.record))
def view_homework(user):
'''
查看作业
:param user:
:return:
'''
record_student = session.query(models.RecordStudent).filter_by().all()
for i in record_student:
student_teachclass_obj = session.query(models.StudentTeachClass).
filter_by(id=i.student_teach_class_id).first()
class_teacher_obj = session.query(models.TeacherClass).filter_by(
id=student_teachclass_obj.teacher_course.bind_teacher_class_id).first()
course_obj = session.query(models.CourseClassRecord).filter_by(
id=student_teachclass_obj.teacher_course.bind_course_time_id).first()
studentname_obj = session.query(models.Student).filter_by(
qq_number=student_teachclass_obj.student.qq_number).first()
studenthomework_obj = session.query(models.StudentHomework).filter_by(
student_teach_class_id=i.student_teach_class_id).first()
if studenthomework_obj:
if class_teacher_obj.teacher.username == user.username:
print('绑定ID %s 课程:%s 课程时间:%s 讲师:%s 教室:%s 学生名:%s 学生qq %s 上课记录:%s 作业:%s' % (
student_teachclass_obj.id, course_obj.course.coursename, course_obj.course_time.course_time_name,
class_teacher_obj.teacher.username, class_teacher_obj.class_room.classname,
studentname_obj.username, studentname_obj.qq_number, i.record.record,
studenthomework_obj.homework.home_work))
def view_score(user):
'''
查看分数
:param user:
:return:
'''
record_student = session.query(models.RecordStudent).filter_by().all()
for i in record_student:
student_teachclass_obj = session.query(models.StudentTeachClass).
filter_by(id=i.student_teach_class_id).first()
class_teacher_obj = session.query(models.TeacherClass).filter_by(
id=student_teachclass_obj.teacher_course.bind_teacher_class_id).first()
course_obj = session.query(models.CourseClassRecord).filter_by(
id=student_teachclass_obj.teacher_course.bind_course_time_id).first()
studentname_obj = session.query(models.Student).filter_by(
qq_number=student_teachclass_obj.student.qq_number).first()
studenthomework_obj = session.query(models.StudentHomework).filter_by(
student_teach_class_id=i.student_teach_class_id).first()
score_obj = session.query(models.RecordScore).filter_by(student_teach_class_id=i.student_teach_class_id).first()
if studenthomework_obj:
if score_obj:
if class_teacher_obj.teacher.username == user.username:
if score_obj.student_teacher_class.student.qq_number == studentname_obj.qq_number:
print('绑定ID %s 课程:%s 课程时间:%s 讲师:%s 教室:%s 学生名:%s 学生qq %s 上课记录:%s 作业:%s 分数:%s'
% (student_teachclass_obj.id, course_obj.course.coursename,
course_obj.course_time.course_time_name, class_teacher_obj.teacher.username,
class_teacher_obj.class_room.classname, studentname_obj.username,
studentname_obj.qq_number, i.record.record, studenthomework_obj.homework.home_work,
score_obj.score.score))
def teacher(argvs):
'''
讲师视图
:param argvs:
:return:
'''
user = auth_teacher()
if user:
welcome_msg(user)
exit_flag = False
while not exit_flag:
show_class(user)
show_course(user)
msg = '''
1) 创建班级
2）添加学员到班级
3）上课记录
4）批改成绩
5）添加学员
'''
print(msg)
while not exit_flag:
user_option = input("[ (q)quit, select num to manage]:").strip()
if len(user_option) == 0:
continue
if user_option == 'q':
exit_flag = True
if user_option == '1':
course_name = input('请输入班级上的课程>>>>: ')
if len(course_name) == 0:
print('sorry...班级不能为空')
break
else:
course_time = input('请输入课程时间安排>>>>:')
if len(course_time) == 0:
print('sorry...课程时间不能为空')
break
else:
class_name = input('请输入班级名称>>>:')
if len(class_name) == 0:
print('sorry...班级名称不能为空')
break
# 课程名
course_name_obj = session.query(models.Course).filter_by(coursename=course_name).first()
# 课程安排
course_time_obj = session.query(models.CourseTime).filter_by(course_time_name=course_time).first()
# 教室名
class_name_obj = session.query(models.ClassRoom).filter_by(classname=class_name).first()
# 添加课程
if course_name_obj:
pass
else:
course_name_db_obj = models.Course(coursename=course_name)
session.add(course_name_db_obj)
session.commit()
# 添加课程安排
if course_time_obj:
pass
else:
course_time_db_obj = models.CourseTime(course_time_name=course_time)
session.add(course_time_db_obj)
session.commit()
if class_name_obj:
pass
else:
class_name_db_obj = models.ClassRoom(classname=class_name)
session.add(class_name_db_obj)
session.commit()
course_name = session.query(models.Course).filter_by(coursename=course_name).first()
course_time = session.query(models.CourseTime).filter_by(course_time_name=course_time).first()
class_name = session.query(models.ClassRoom).filter_by(classname=class_name).first()
# 创建课程课程时间关联
course_class_record_id_obj = session.query(models.CourseClassRecord).filter_by(
course_id=course_name.id).filter_by(course_time_id=course_time.id).all()
if course_class_record_id_obj:
pass
else:
course_class_record_id_obj = models.CourseClassRecord(course_id=course_name.id,
course_time_id=course_time.id)
session.add(course_class_record_id_obj)
session.commit()
# 添加讲师教室关联
teacher_class_id_obj = session.query(models.TeacherClass).filter_by(
teacher_id=user.id).filter_by(class_room_id=class_name.id).all()
if teacher_class_id_obj:
pass
else:
teacher_class_id_obj = models.TeacherClass(teacher_id=user.id, class_room_id=class_name.id)
session.add(teacher_class_id_obj)
session.commit()
# 查老师的对象
db_teacher_obj = session.query(models.Teacher).filter_by(id=user.id).first()
# 查课程对象
db_course_name_obj = session.query(models.Course).filter_by(
coursename=course_name.coursename).first()
# 查课程安排对象
db_course_time_obj = session.query(models.CourseTime).
filter_by(course_time_name=course_time.course_time_name).first()
# 查教室对象
db_class_obj = session.query(models.ClassRoom).filter_by(classname=class_name.classname).first()
# 教室讲师关联对象
db_teacher_class_obj = session.query(models.TeacherClass).filter_by(
class_room_id=db_class_obj.id).filter_by(teacher_id=user.id).first()
# 课程课程安排关联对象
db_course_class_obj = session.query(models.CourseClassRecord).filter_by(
course_id=db_course_name_obj.id).filter_by(course_time_id=db_course_time_obj.id).first()
if db_teacher_class_obj and db_course_class_obj:
teacher_class = session.query(models.TeacheCourse).filter_by(
bind_teacher_class_id=db_teacher_class_obj.id).filter_by(
bind_course_time_id=db_course_class_obj.id).all()
if teacher_class:
print('班级已经关联了')
else:
teachecourse = models.TeacheCourse(bind_teacher_class_id=db_teacher_class_obj.id,
bind_course_time_id=db_course_class_obj.id)
session.add(teachecourse)
session.commit()
print('班级创建完毕')
show_course(user)
elif user_option == '2':
view_student()
view_student_class(user)
show_course(user)
add_choice = input('[ (y)是,(n)否, select num to manage]:').strip()
if add_choice == 'n':
break
elif add_choice == 'y':
teacher_input_qq = input('请输入学员QQ号>>>: ')
teacher_input_courseid = input('请输入课堂ID>>>:')
try:
qq = int(teacher_input_qq)
courseid = int(teacher_input_courseid)
except ValueError:
print('qq或课堂ID必须是数字')
break
student_teachclass_check_obj = session.query(
models.StudentTeachClass).filter_by(
teacher_course_id=teacher_input_courseid).filter_by(student_qq=teacher_input_qq).all()
if student_teachclass_check_obj:
print('学生已经在班级里了....')
else:
student_obj = session.query(models.Student).filter_by(qq_number=qq).all()
if student_obj:
for i in student_obj:
if teacher_input_qq == str(i.qq_number):
student_class_obj = models.StudentTeachClass(
teacher_course_id=courseid, student_qq=qq)
session.add(student_class_obj)
session.commit()
view_student_class(user)
else:
print('没有这个学生')
else:
print('no this option')
elif user_option == '3':
print('
%s 管理的班级学员
' % user.username)
view_student_class(user)
print('
%s 管理的班级的上课记录
' % user.username)
view_record(user)
record_choice = input('[ (y)是,(n)否, select num to manage]:').strip()
if record_choice == 'n':
break
elif record_choice == 'y':
record_id_input = input('请输入绑定id添加学员上课记录:')
# 插入绑定关系
record_input = input('[ 请输入学员上课记录(y)yes,(no)否]')
try:
courseid = int(record_id_input)
except ValueError:
print('绑定ID必须是数字')
break
if record_input == 'yes' or 'no':
record_in_obj = session.query(
models.RecordStudent).filter_by(student_teach_class_id=record_id_input).all()
if record_in_obj:
print('记录已经添加...')
else:
record_in_obj = session.query(models.Record).filter_by(record=record_input).first()
student_teachclass_id_obj = models.RecordStudent(
student_teach_class_id=record_id_input, record_id=record_in_obj.id)
session.add(student_teachclass_id_obj)
session.commit()
view_record(user)
else:
print('no this option')
elif user_option == '4':
print('
%s 管理的班级的上课记录
' % user.username)
view_record(user)
print('
已交作业的童鞋
')
view_homework(user)
print('
已批改的成绩
')
view_score(user)
choice_score_input = input('[ 批改成绩是否继续(y)是,(n)否, select num to manage]:').strip()
if choice_score_input == 'n':
break
elif choice_score_input == 'y':
while not exit_flag:
record_id_input = input('请输入显示的绑定id添加学员成绩:')
score_input = input('请输入分数..')
# 插入绑定关系
home_work_check = session.query(
models.StudentHomework).filter_by(student_teach_class_id=int(record_id_input)).all()
if home_work_check:
score = int(score_input)
if score > 100 or score < 0:
print('请输入100以内的整数')
else:
score_obj = session.query(models.Score).filter_by(score=score).all()
if score_obj:
for i in score_obj:
score_db_id_obj = session.query(
models.Score).filter_by(score=i.score).first()
record_score_obj = session.query(models.RecordScore).filter_by(
student_teach_class_id=record_id_input).first()
if record_score_obj:
print('该学员已经有成绩了...')
break
else:
add_score_obj = models.RecordScore(
student_teach_class_id=record_id_input, score_id=score_db_id_obj.id)
session.add(add_score_obj)
session.commit()
print('添加成绩完成')
view_score(user)
else:
score_db_obj = models.Score(score=score)
session.add(score_db_obj)
session.commit()
score_db_id_obj = session.query(models.Score).filter_by(score=score).first()
record_score_obj = session.query(models.RecordScore).filter_by(
student_teach_class_id=record_id_input).first()
if record_score_obj:
print('该学员已经有成绩了...')
break
else:
add_score_obj = models.RecordScore(
student_teach_class_id=record_id_input, score_id=score_db_id_obj.id)
session.add(add_score_obj)
session.commit()
print('添加成绩完成')
view_score(user)
else:
print('学生还没有交作业,请先联系学生交作业')
break
else:
print('no this option')
pass
elif user_option == '5':
print('添加学员后请注意给学生分配教室并添加上课记录，课后请提示学生交作业')
student_add_input = input('[ 批改成绩是否继续(y)是,(n)否, select num to manage]:').strip()
student_name_input = input('请输入学生账号:')
student_password_input = input('请输入学生密码:')
student_qq_input = input('请输入学生qq号码:')
try:
qq = int(student_qq_input)
except ValueError:
print('qq必须是数字')
break
# 联合查询
student_check = session.query(
models.Student).filter_by(qq_number=qq).filter_by(username=student_name_input).all()
if student_check:
print('学生已经存在')
else:
# 联合查询不能避免qq号或用户名重复...数据库中做了qq号和用户名唯一
try:
student_obj = models.Student(
qq_number=qq, username=student_name_input, password=student_password_input)
session.add(student_obj)
session.commit()
print('添加学生完成,请给学生分配教室并添加上课记录，课后请提示学生交作业')
except sqlalchemy.exc.IntegrityError:
print('学生已经存在')
elif user_option == 'q':
exit_flag = True
else:
print("no this option..")
def student(argvs):
'''
学生视图
:param argvs:
:return:
'''
user = auth_student()
if user:
print('student')
welcome_msg(user)
exit_flag = False
show_student_class(user)
msg = '''
1）选择课程班级
2) 提交作业
3）查看作业成绩
4）查看所属班级成绩排名
'''
print(msg)
while not exit_flag:
user_option = input("[ (q)quit, select num to manage]:").strip()
if len(user_option) == 0:
continue
if user_option == 'q':
exit_flag = True
if user_option == '1':
print('请联系您的讲师帮您安排课程和教室')
elif user_option == '2':
home_work_add_choice = input('[ (y)是,(n)否, select num to manage]:').strip()
if home_work_add_choice == 'n':
break
elif home_work_add_choice == 'y':
home_work_course_time_input = input('请输入上课节数/课程时间>>>:')
home_work_classroom_input = input('请输入所在班级/教室>>>: ')
home_work_classid_input = input('请输入对应教室的绑定ID>>>:')
student_class_obj = session.query(models.StudentTeachClass).filter_by(
id=int(home_work_classid_input)).all()
if student_class_obj:
for i in student_class_obj:
course_obj = session.query(models.CourseClassRecord).filter_by(
id=i.teacher_course.bind_course_time_id).first()
# 检查作业
student_home_work_id_check = session.query(
models.HomeWork).filter_by(home_work=user.username+'_'+course_obj.course_time.
course_time_name+'_'+'home_work').all()
if student_home_work_id_check:
print('已经交作业了，不需要重复提交')
else:
home_add = input('[ (y)是,(n)否, to add home_work]:').strip()
if home_add == 'n':
break
if home_add == 'y':
homework =
models.HomeWork(
home_work=user.username+'_' + course_obj.course_time.course_time_name + '_' +
'home_work')
session.add(homework)
session.commit()
home_work_id = session.query(models.HomeWork).filter_by(
home_work=user.username+'_' + course_obj.course_time.course_time_name+'_' +
'home_work').first()
record_home_work = models.StudentHomework(
student_teach_class_id=i.id, homework_id=home_work_id.id)
session.add(record_home_work)
session.commit()
print('作业添加完成,请提醒老师添加上课记录和批改成绩...')
break
else:
print('没有这个班级...')
elif user_option == '3':
print('如果没有成绩的请先交作业然后找讲师批改成绩')
student_record_id = input('请输入您的绑定ID，查看作业信息;')
record_student = session.query(models.RecordStudent).filter_by().all()
for i in record_student:
student_teachclass_obj = session.query(models.StudentTeachClass).
filter_by(id=student_record_id).first()
class_teacher_obj = session.query(models.TeacherClass).filter_by(
id=student_teachclass_obj.teacher_course.bind_teacher_class_id).first()
course_obj = session.query(models.CourseClassRecord).filter_by(
id=student_teachclass_obj.teacher_course.bind_course_time_id).first()
studentname_obj = session.query(models.Student).filter_by(
qq_number=user.qq_number).first()
studenthomework_obj = session.query(models.StudentHomework).filter_by(
student_teach_class_id=student_record_id).first()
score_obj = session.query(models.RecordScore).filter_by(
student_teach_class_id=i.student_teach_class_id).first()
if studenthomework_obj:
if user.username == studentname_obj.username:
if score_obj:
if score_obj.student_teacher_class.student.qq_number == studentname_obj.qq_number:
print('绑定ID %s 课程:%s 课程时间:%s 讲师:%s 教室:%s 学生名:%s 学生qq %s 上课记录:%s 作业:%s 分数:%s'
% (student_teachclass_obj.id, course_obj.course.coursename,
course_obj.course_time.course_time_name,
class_teacher_obj.teacher.username,
class_teacher_obj.class_room.classname, studentname_obj.username,
studentname_obj.qq_number, i.record.record,
studenthomework_obj.homework.home_work,
score_obj.score.score))
elif user_option == '4':
print('查看班级排名.请按照所在班级的绑定ID查询排名')
home_work_course_time_input = input('请输入上课节数/课程时间>>>:')
home_work_classroom_input = input('请输入所在班级/教室>>>: ')
record_student = session.query(models.RecordStudent).filter_by().all()
tmp_dict = {}
for i in record_student:
student_teachclass_obj = session.query(models.StudentTeachClass).
filter_by(id=i.student_teach_class_id).first()
class_teacher_obj = session.query(models.TeacherClass).filter_by(
id=student_teachclass_obj.teacher_course.bind_teacher_class_id).first()
course_obj = session.query(models.CourseClassRecord).filter_by(
id=student_teachclass_obj.teacher_course.bind_course_time_id).first()
studentname_obj = session.query(models.Student).filter_by(
qq_number=student_teachclass_obj.student.qq_number).first()
studenthomework_obj = session.query(models.StudentHomework).filter_by(
student_teach_class_id=i.student_teach_class_id).first()
score_obj = session.query(models.RecordScore).filter_by(
student_teach_class_id=i.student_teach_class_id).first()
if home_work_course_time_input == course_obj.course_time.course_time_name
and home_work_classroom_input == class_teacher_obj.class_room.classname:
if studenthomework_obj:
if score_obj:
if score_obj.student_teacher_class.student.qq_number == studentname_obj.qq_number:
if score_obj.score.score in tmp_dict.keys():
tmp_dict[score_obj.score.score].append(studentname_obj.username)
else:
tmp_dict[score_obj.score.score] = [studentname_obj.username]
# 分数排序,按道理应该是用group_by 这里偷个懒
tmp_list = []
for key in tmp_dict.keys():
tmp_list.append(key)
tmp_list.sort(reverse=True)
for key in tmp_list:
for name in tmp_dict[key]:
print(name, key)
elif user_option == 'q':
exit_flag = True
else:
print('no this option')
models.py
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Time:2017/12/19 20:36
__Author__ = 'Sean Yao'
import datetime
from sqlalchemy import Table, Column, Integer, String, DATE, ForeignKey, Enum, UniqueConstraint, DateTime, Text
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import ChoiceType, PasswordType
from sqlalchemy import create_engine
Base = declarative_base()
class TeacheCourse(Base):
'''
课程/课程表/讲师/教室/关联
'''
__tablename__ = 'teacher_course'
__table_args__ = (UniqueConstraint('bind_teacher_class_id', 'bind_course_time_id', name='_class_uc'),)
id = Column(Integer, primary_key=True)
bind_teacher_class_id = Column('bind_teacher_class_id', Integer, ForeignKey('bind_teacher_class.id'))
bind_course_time_id = Column('bind_course_time_id', Integer, ForeignKey('bind_course_time.id'))
teacher_class = relationship('TeacherClass', backref='teacher_course')
course_class_record = relationship('CourseClassRecord', backref='teacher_course')
def __repr__(self):
return self.id, self.teacher_class.id, self.bind_course_time_id
class RecordScore(Base):
'''
打分/关联 老师视图操作
1 66
2 88
'''
__tablename__ = 'record_score'
__table_args__ = (UniqueConstraint('student_teach_class_id', 'score_id', name='_record_score_uc'),)
id = Column(Integer, primary_key=True)
student_teach_class_id = Column(
'student_teach_class_id', Integer, ForeignKey('student_teach_class.id'), unique=True)
score_id = Column('score_id', Integer, ForeignKey('score.id'))
score = relationship('Score', backref='record_score')
student_teacher_class = relationship('StudentTeachClass', backref='record_score')
def __repr__(self):
return self.id, self.student_teacher_class.teacher_course.id, self.student_teacher_class.student.qq_number,
self.score.score
class RecordStudent(Base):
'''
上课记录/关联 老师视图操作
1 yes
2 no
'''
__tablename__ = 'record_student'
__table_args__ = (UniqueConstraint('student_teach_class_id', 'record_id', name='_record_class_uc'),)
id = Column(Integer, primary_key=True)
student_teach_class_id = Column(
'student_teach_class_id', Integer, ForeignKey('student_teach_class.id'), unique=True)
record_id = Column('record_id', Integer, ForeignKey('record.id'))
record = relationship('Record', backref='record_student')
student_teacher_class = relationship('StudentTeachClass', backref='record_student')
def __repr__(self):
return self.id, self.student_teacher_class.teacher_course.id, self.student_teacher_class.student.qq_number,
self.record.record
class StudentHomework(Base):
'''
课程/课程表/讲师/教室/学生/作业/关联 学生视图操作
449010391 1
'''
__tablename__ = 'student_homework'
__table_args__ = (UniqueConstraint('student_teach_class_id', 'homework_id', name='_record_homework_uc'),)
id = Column(Integer, primary_key=True)
student_teach_class_id = Column(
'student_teach_class_id', Integer, ForeignKey('student_teach_class.id'), unique=True)
homework_id = Column('homework_id', Integer, ForeignKey('homework.id'))
homework = relationship('HomeWork', backref='student_homework')
student_teacher_class = relationship('StudentTeachClass', backref='student_homework')
def __repr__(self):
return self.id, self.student_teacher_class.teacher_course.id, self.student_teacher_class.student.qq_number,
self.homework.home_work
class StudentTeachClass(Base):
'''
课程/课程表/讲师/教室/学生/关联
python/python1/alex/python_1 449010391
1 449010391
'''
__tablename__ = 'student_teach_class'
__table_args__ = (UniqueConstraint('student_qq', 'teacher_course_id', name='_record_uc'),)
id = Column(Integer, primary_key=True)
teacher_course_id = Column('teacher_course_id', Integer, ForeignKey('teacher_course.id'))
student_qq = Column('student_qq', Integer, ForeignKey('student.qq_number'))
teacher_course = relationship("TeacheCourse", backref='student_teach_class')
student = relationship('Student', backref='student_teach_class')
def __repr__(self):
# qq号,讲师/教室/课程/课程表
return self.id, self.student.qq_number, self.teacher_course.bind_teacher_class_id,
self.teacher_course.bind_course_time_id
class Course(Base):
'''
课程表
课程唯一
'''
__tablename__ = 'course'
id = Column(Integer, primary_key=True)
coursename = Column(String(64), unique=True, nullable=False)
def __repr__(self):
return self.id, self.coursename
class CourseTime(Base):
'''
课程时间表
课程时间唯一
'''
__tablename__ = 'course_time'
id = Column(Integer, primary_key=True)
course_time_name = Column(String(64), unique=True, nullable=False)
def __repr__(self):
return self.id, self.course_time_name
class Teacher(Base):
'''
teacher 表
老师名字唯一
'''
__tablename__ = 'teacher'
id = Column(Integer, primary_key=True)
username = Column(String(64), unique=True, nullable=False)
password = Column(String(128), nullable=False)
def __repr__(self):
return self.id, self.username, self.password,
class ClassRoom(Base):
'''
班级表
班级唯一
'''
__tablename__ = 'class_room'
id = Column(Integer, primary_key=True)
classname = Column(String(64), unique=True, nullable=False)
def __repr__(self):
return self.id, self.classname
class Student(Base):
'''
student表
用户名/qq号/唯一
'''
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
qq_number = Column(Integer, unique=True, nullable=False)
username = Column(String(64), unique=True, nullable=False)
password = Column(String(128), nullable=False)
def __repr__(self):
return self.id, self.qq_number, self.username, self.password
class Score(Base):
'''
成绩表
成绩不唯一可能不同的童鞋得到的分数一样
'''
__tablename__ = 'score'
id = Column(Integer, primary_key=True)
score = Column(Integer, unique=True, nullable=False)
def __repr__(self):
return self.id, self.score
class HomeWork(Base):
'''
作业表
作业唯一对应不同的童鞋和课程
'''
__tablename__ = 'homework'
id = Column(Integer, unique=True, primary_key=True)
home_work = Column(String(128))
def __repr__(self):
return self.id, self.home_work
class Record(Base):
'''
上课记录
唯一只有yes/no
'''
__tablename__ = 'record'
id = Column(Integer, primary_key=True)
action_choices = [
(u'yes', u'Yes'),
(u'no', u'No'),
]
record = Column(ChoiceType(action_choices), unique=True)
def __repr__(self):
return self.id, self.record
class CourseClassRecord(Base):
'''
课程/课程时间/关联
课程:pyrhon 课程时间:python_day1
课程:linux 课程时间:linux_day1
课程:linux 课程时间:linux_day2
课程和课程时间联合唯一
'''
__tablename__ = "bind_course_time"
# 联合唯一
__table_args__ = (UniqueConstraint('course_id', 'course_time_id', name='_course_uc'),)
id = Column(Integer, primary_key=True)
course_id = Column(Integer, ForeignKey('course.id'))
course_time_id = Column(Integer, ForeignKey('course_time.id'))
course = relationship('Course', backref='bind_course_time')
course_time = relationship('CourseTime', backref='bind_course_time')
def __repr__(self):
return self.id, self.course.coursename, self.course_time.course_time_name
class TeacherClass(Base):
'''
老师/班级/关联
老师和班级联合唯一
老师:alex 班级:python_S14
老师:alex 班级:python_S15
'''
__tablename__ = "bind_teacher_class"
__table_args__ = (UniqueConstraint('teacher_id', 'class_room_id', name='_class_uc'),)
id = Column(Integer, primary_key=True)
teacher_id = Column(Integer, ForeignKey('teacher.id'))
class_room_id = Column(Integer, ForeignKey('class_room.id'))
teacher = relationship('Teacher', backref='bind_teacher_class')
class_room = relationship('ClassRoom', backref='bind_teacher_class')
def __repr__(self):
return self.id, self.teacher.username, self.class_room.classname
程序测试样图:
添加多个学生后，给学生绑定了课堂，并登陆学生交了作业，讲师批改作业后学生可以查看分数排名
