学员管理系统(SQLAlchemy 实现)
一、业务逻辑
 
二、设计表结构
三、代码结构
start.py
import os, sys
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
if __name__ == '__main__':
from core import main
main.Management_center()
main.py 处理不同角色请求
 
1 from core.color import Colors
2 from core.center_admin import Center_admin
3 from core.center_student import Center_student
4 from core.center_teacher import Center_teacher
5 from core.create_table import User
6 from sqlalchemy import create_engine
7 from sqlalchemy.orm import sessionmaker
8 import hashlib
9
10 class Management_center(object):
11 def __init__(self):
12 engine = create_engine("mysql+pymysql://root:123456@10.0.0.15/studentdb?charset=utf8")#, echo=True)
13 Session_class = sessionmaker(bind=engine)
14 self.session = Session_class()
15 self.init_db()
16 self.interactive()
17
18 def init_db(self):
19 from core import create_table
20 #创建表结构
21 create_table.create_table()
22 print(Colors('init database done..', 'green'))
23 #初始化admin管理员用户
24 if not self.session.query(User).filter(User.username=='admin').first():
25 md5 = hashlib.md5()
26 md5.update('admin'.encode('utf-8'))
27 md5_password = md5.hexdigest()
28 user_obj_admin = User(username='admin', password=md5_password, permission=0)
29 self.session.add_all([user_obj_admin, ])
30 self.session.commit()
31 print(Colors('初始化admin管理员用户完成!', 'green'))
32
33 def interactive(self):
34 while True:
35 print("欢迎来到学员管理系统".center(45, '-'))
36 msg = """
37 1.管理员中心
38 2.讲师中心
39 3.学生中心
40 4.退出
41 """
42 print(Colors(msg))
43 print("".center(50, '-'))
44 choice = input(Colors('请输入要进入的中心:', 'cyan')).strip()
45 if choice == '1':
46 Center_admin(self.session)
47 elif choice == '2':
48 Center_teacher(self.session)
49 elif choice == '3':
50 Center_student(self.session)
51 elif choice == '4':
52 exit('Bye')
53 else:
54 print(Colors('请输入正确的选项!', 'red'))
main.pycenter_admin.py 管理员中心
1 # Author：ton
2 # -*- coding: utf-8 -*-
3 from core.color import Colors
4 from core import commons
5 from core import auth
6 from core.create_table import Course_m2m_class, User, School, Course, Teacher, Class, Student, Lesson, C2c_m2m_lesson,\
7 Study_record, school_m2m_course, school_m2m_teacher, school_m2m_student, student_m2m_class
8 import hashlib
9
10
11 class Center_admin(object):
12 """管理员中心"""
13 def __init__(self, session):
14 self.session = session
15 self.handle()
16
17 @auth.auth(permission=0)
18 def handle(self):
19 while True:
20 class_dic = {
21 '1':Manage_school,
22 '2':Manage_course,
23 '3':Manage_teacher,
24 '4':Manage_class,
25 '5':Manage_student,
26 }
27 print("管理员中心".center(45, '-'))
28 msg = """
29 1.校区管理
30 2.课程管理
31 3.讲师管理
32 4.班级管理
33 5.学员管理
34 6.退出并返回系统主界面
35 """
36 print(Colors(msg))
37 print('当前用户:%s' %self.user.username)
38 print("".center(50, '-'))
39 choice = input(Colors('请输入要执行的指令:', 'cyan')).strip()
40 if choice == '6':break
41 if class_dic.get(choice):
42 class_dic.get(choice)(self.session, self.user)
43 else:
44 print(Colors('无效的指令!', 'red'))
45
46 class Manage_school(object):
47 """校区管理"""
48 def __init__(self, session, user):
49 self.session = session
50 self.user = user
51 self.handle()
52
53 def handle(self):
54 while True:
55 print("校区管理界面".center(45, '-'))
56 msg = """
57 创建校区 create_school
58 查看校区 display_school
59 删除校区 delete_school
60 修改校区 update_school
61 添加/删除课程 add_course/del_course
62 查看各校区已有课程 display_sch_courses
63 添加/删除讲师 add_teacher/del_teacher
64 查看各校区已有讲师 display_sch_teachers
65 查看各校区已有班级 display_sch_classes
66 添加/删除学员 add_student/del_student
67 查看各校区已有学员 display_sch_students
68 返回管理员中心 b
69 """
70 print(Colors(msg))
71 print('当前用户:%s' %self.user.username)
72 print("".center(50, '-'))
73 choice = input(Colors('请输入要执行的指令(b 返回):', 'cyan')).strip()
74 if choice == 'b':break
75 if hasattr(self, choice):
76 getattr(self, choice)()
77 else:
78 print(Colors('无效的指令!', 'red'))
79
80 def create_school(self):
81 while True:
82 add_school_name = input(Colors('请输入要创建的校区名(b 返回)：', 'cyan')).strip()
83 if add_school_name == 'b':break
84 if not add_school_name:continue
85 #判断该校区名是否存在
86 sch_obj = self.session.query(School).filter(School.name==add_school_name).first()
87 if sch_obj:
88 print(Colors('该校区名已存在!', 'red'))
89 continue
90 while True:
91 add_school_address = input(Colors('请输入校区地址(b 返回)：', 'cyan')).strip()
92 if add_school_address == 'b':break
93 if not add_school_address:continue
94 #更新School表
95 sch_obj = School(name=add_school_name, address=add_school_address)
96 self.session.add_all([sch_obj, ])
97 self.session.commit()
98 print(Colors('创建校区成功!', 'green'))
99 break
100
101 def display_school(self):
102 return commons.display_school(self.session)
103
104 def delete_school(self):
105 while True:
106 #判断School表是否为空
107 if not self.display_school():break
108 del_school_name = input(Colors('请输入要删除的校区名(b 返回)：', 'cyan')).strip()
109 if not del_school_name:continue
110 if del_school_name == 'b':break
111 #判断该校区名是否存在
112 sch_obj = self.session.query(School).filter(School.name==del_school_name).first()
113 if not sch_obj:
114 print(Colors('该校区名不存在!', 'red'))
115 continue
116 else:
117 #删除School表记录
118 self.session.delete(sch_obj)
119 self.session.commit()
120 print(Colors('删除校区成功!', 'green'))
121
122 def update_school(self):
123 while True:
124 #判断School表是否为空
125 if not self.display_school():break
126 update_school_name = input(Colors('请输入要修改的校区名(b 返回)：', 'cyan')).strip()
127 if not update_school_name:continue
128 if update_school_name == 'b':break
129 #判断该校区名是否存在
130 sch_obj = self.session.query(School).filter(School.name==update_school_name).first()
131 if not sch_obj:
132 print(Colors('该校区名不存在!', 'red'))
133 continue
134 else:
135 #修改School表记录
136 column_school = [{'name':'校区名'}, {'address':'地址'}, ]
137 res = commons.update_table(self.session, column_school, sch_obj)
138 if not res:return
139
140 def add_course(self):
141 """为某校区增设课程"""
142 sch_obj = commons.choice_school(self.session)
143 if not sch_obj:return
144 while True:
145 #查看所有课程并判断Course表是否为空
146 if not commons.display_course(self.session):return
147 #展示该校区已有课程信息
148 commons.display_sch_courses(sch_obj)
149 add_course_name = input(Colors('请输入要增设的课程名(b 返回)：', 'cyan')).strip()
150 if not add_course_name:continue
151 if add_course_name == 'b':break
152 #判断该课程名是否存在
153 cou_obj = self.session.query(Course).filter(Course.name==add_course_name).first()
154 if not cou_obj:
155 print(Colors('该课程名不存在!', 'red'))
156 continue
157 #判断该校区是否已经有该课程
158 if cou_obj not in sch_obj.courses:
159 #更新school_m2m_course表
160 sch_obj.courses.append(cou_obj)
161 self.session.commit()
162 print(Colors('增设课程成功!', 'green'))
163 else:
164 print(Colors('该校区已经有该课程!', 'red'))
165
166 def display_sch_courses(self):
167 """查看各校区已有课程"""
168 sch_obj_li = self.session.query(School).all()
169 if sch_obj_li:
170 for sch_obj in sch_obj_li:
171 #展示某校区所有课程信息
172 commons.display_sch_courses(sch_obj)
173 else:
174 print(Colors('现在还没有校区，请先创建校区!', 'red'))
175
176 def del_course(self):
177 sch_obj = commons.choice_school(self.session)
178 if not sch_obj:return
179 while True:
180 #展示该校区已有课程信息
181 res = commons.display_sch_courses(sch_obj)
182 if not res:return
183 del_course_name = input(Colors('请输入要删除的课程名(b 返回)：', 'cyan')).strip()
184 if not del_course_name:continue
185 if del_course_name == 'b':break
186 #判断该校区是否存在该课程
187 cou_obj = self.session.query(Course).join(school_m2m_course).join(School).filter(School.name==sch_obj.name).filter(Course.name==del_course_name).first()
188 if not cou_obj:
189 print(Colors('该校区没有该课程!', 'red'))
190 continue
191 else:
192 #删除该校区的该课程
193 sch_obj.courses.remove(cou_obj)
194 self.session.commit()
195 print(Colors('删除课程[%s]成功!'%cou_obj.name, 'green'))
196
197 def add_teacher(self):
198 sch_obj = commons.choice_school(self.session)
199 if not sch_obj:return
200 while True:
201 #查看所有讲师并判断Teacher表是否为空
202 if not commons.display_teacher(self.session):return
203 #展示该校区已有讲师信息
204 commons.display_sch_teachers(sch_obj)
205 add_tea_card_id = input(Colors('请输入要创建的讲师身份证号码(b 返回)：', 'cyan')).strip()
206 if not add_tea_card_id:continue
207 if add_tea_card_id == 'b':break
208 #判断该讲师身份证号是否存在
209 tea_obj = self.session.query(Teacher).filter(Teacher.card_id==add_tea_card_id).first()
210 if not tea_obj:
211 print(Colors('该讲师身份证号不存在!', 'red'))
212 continue
213 #判断该校区是否已经有该讲师
214 if tea_obj not in sch_obj.teachers:
215 #更新school_m2m_teacher表
216 sch_obj.teachers.append(tea_obj)
217 self.session.commit()
218 print(Colors('添加讲师成功!', 'green'))
219 else:
220 print(Colors('该校区已经有该讲师!', 'red'))
221
222 def display_sch_teachers(self):
223 """查看各校区已有讲师"""
224 sch_obj_li = self.session.query(School).all()
225 if sch_obj_li:
226 for sch_obj in sch_obj_li:
227 #展示某校区所有讲师信息
228 commons.display_sch_teachers(sch_obj)
229 else:
230 print(Colors('现在还没有校区，请先创建校区!', 'red'))
231
232 def del_teacher(self):
233 sch_obj = commons.choice_school(self.session)
234 if not sch_obj:return
235 while True:
236 #展示该校区已有讲师信息
237 res = commons.display_sch_teachers(sch_obj)
238 if not res:return
239 del_tea_card_id = input(Colors('请输入要删除的讲师身份证号码(b 返回)：', 'cyan')).strip()
240 if not del_tea_card_id:continue
241 if del_tea_card_id == 'b':break
242 #判断该校区是否存在该讲师
243 tea_obj = self.session.query(Teacher).join(school_m2m_teacher).join(School).filter(School.name==sch_obj.name).filter(Teacher.card_id==del_tea_card_id).first()
244 if not tea_obj:
245 print(Colors('校区[%s]没有该讲师身份证号码[%s]!'%(sch_obj.name, del_tea_card_id), 'red'))
246 continue
247 else:
248 #删除该校区的该讲师
249 sch_obj.teachers.remove(tea_obj)
250 self.session.commit()
251 print(Colors('删除讲师[%s]成功!'%tea_obj.name, 'green'))
252
253 def display_sch_classes(self):
254 """查看各校区已有班级"""
255 sch_obj_li = self.session.query(School).all()
256 if sch_obj_li:
257 for sch_obj in sch_obj_li:
258 #展示某校区所有班级信息
259 commons.display_sch_classes(sch_obj)
260 else:
261 print(Colors('现在还没有校区，请先创建校区!', 'red'))
262
263 def add_student(self):
264 sch_obj = commons.choice_school(self.session)
265 if not sch_obj:return
266 while True:
267 #查看所有学员并判断Student表是否为空
268 if not commons.display_student(self.session):return
269 #展示该校区已有学员信息
270 commons.display_sch_students(sch_obj)
271 add_stu_qq = input(Colors('请输入要添加的学员QQ(b 返回)：', 'cyan')).strip()
272 if not add_stu_qq:continue
273 if add_stu_qq == 'b':break
274 #判断该学员QQ是否存在
275 stu_obj = self.session.query(Student).filter(Student.qq==add_stu_qq).first()
276 if not stu_obj:
277 print(Colors('该学员QQ[%s]不存在!'%add_stu_qq, 'red'))
278 continue
279 #判断该校区是否已经有该学员
280 if stu_obj not in sch_obj.students:
281 #更新school_m2m_student表
282 sch_obj.students.append(stu_obj)
283 self.session.commit()
284 print(Colors('添加学员[%s]成功!'%stu_obj.name, 'green'))
285 else:
286 print(Colors('校区[%s]已经有学员[%s]!'%(sch_obj.name, stu_obj.name), 'red'))
287
288 def display_sch_students(self):
289 """查看各校区已有学员"""
290 sch_obj_li = self.session.query(School).all()
291 if sch_obj_li:
292 for sch_obj in sch_obj_li:
293 #展示某校区所有学员信息
294 commons.display_sch_students(sch_obj)
295 else:
296 print(Colors('现在还没有校区，请先创建校区!', 'red'))
297
298 def del_student(self):
299 sch_obj = commons.choice_school(self.session)
300 if not sch_obj:return
301 while True:
302 #展示该校区已有学员信息
303 res = commons.display_sch_students(sch_obj)
304 if not res:return
305 del_stu_qq = input(Colors('请输入要删除的学员QQ(b 返回)：', 'cyan')).strip()
306 if not del_stu_qq:continue
307 if del_stu_qq == 'b':break
308 #判断该校区是否存在该学员
309 stu_obj = self.session.query(Student).join(school_m2m_student).join(School).filter(School.name==sch_obj.name).filter(Student.qq==del_stu_qq).first()
310 if not stu_obj:
311 print(Colors('校区[%s]不存在该学员QQ[%s]!'%(sch_obj.name, del_stu_qq), 'red'))
312 continue
313 else:
314 #删除该校区的该学员
315 sch_obj.students.remove(stu_obj)
316 self.session.commit()
317 print(Colors('删除学员[%s]成功!'%stu_obj.name, 'green'))
318
319 class Manage_course(object):
320 def __init__(self, session, user):
321 self.session = session
322 self.user = user
323 self.handle()
324
325 def handle(self):
326 while True:
327 print("课程管理界面".center(45, '-'))
328 msg = """
329 创建课程 create_course
330 查看课程 display_course
331 删除课程 delete_course
332 修改课程 update_course
333 返回管理员中心 b
334 """
335 print(Colors(msg))
336 print('当前用户:%s' %self.user.username)
337 print("".center(50, '-'))
338 choice = input(Colors('请输入要执行的指令(b 返回):', 'cyan')).strip()
339 if choice == 'b':break
340 if hasattr(self, choice):
341 getattr(self, choice)()
342 else:
343 print(Colors('无效的指令!', 'red'))
344
345 def create_course(self):
346 while True:
347 add_course_name = input(Colors('请输入要创建的课程名(b 返回)：', 'cyan')).strip()
348 if add_course_name == 'b':break
349 if not add_course_name:continue
350 cou_obj = self.session.query(Course).filter(Course.name==add_course_name).first()
351 #判断该课程名是否存在
352 if cou_obj:
353 print(Colors('该课程名已存在!', 'red'))
354 continue
355 while True:
356 add_course_price = input(Colors('请输入课程价格(b 返回)：', 'cyan')).strip()
357 if add_course_price == 'b':break
358 if not add_course_price:continue
359 if not add_course_price.isdigit():
360 print(Colors('无效的价格，请输入数字!', 'red'))
361 continue
362 #更新Course表
363 cou_obj = Course(name=add_course_name, price=add_course_price)
364 self.session.add_all([cou_obj, ])
365 self.session.commit()
366 print(Colors('创建课程成功!', 'green'))
367 break
368
369 def display_course(self):
370 return commons.display_course(self.session)
371
372 def delete_course(self):
373 while True:
374 #判断Course表是否为空
375 if not self.display_course():break
376 del_course_name = input(Colors('请输入要删除的课程名(b 返回)：', 'cyan')).strip()
377 if not del_course_name:continue
378 if del_course_name == 'b':break
379 #判断该课程名是否存在
380 cou_obj = self.session.query(Course).filter(Course.name==del_course_name).first()
381 if not cou_obj:
382 print(Colors('该课程名不存在!', 'red'))
383 continue
384 else:
385 #删除Course表记录
386 self.session.delete(cou_obj)
387 self.session.commit()
388 print(Colors('删除课程成功!', 'green'))
389
390 def update_course(self):
391 while True:
392 #判断Course表是否为空
393 if not self.display_course():break
394 update_course_name = input(Colors('请输入要修改的课程名(b 返回)：', 'cyan')).strip()
395 if not update_course_name:continue
396 if update_course_name == 'b':break
397 #判断该课程名是否存在
398 cou_obj = self.session.query(Course).filter(Course.name==update_course_name).first()
399 if not cou_obj:
400 print(Colors('该课程名不存在!', 'red'))
401 continue
402 else:
403 #修改Course表记录
404 column_course = [{'name':'课程名'}, {'price':'价格'}, ]
405 res = commons.update_table(self.session, column_course, cou_obj)
406 if not res:return
407
408 class Manage_teacher(object):
409 def __init__(self, session, user):
410 self.session = session
411 self.user = user
412 self.handle()
413
414 def handle(self):
415 while True:
416 print("讲师管理界面".center(45, '-'))
417 msg = """
418 创建讲师 create_teacher
419 查看讲师 display_teacher
420 删除讲师 delete_teacher
421 修改讲师 update_teacher
422 添加/删除任课的班级课程 add_c2c/del_c2c
423 查看各讲师任课的班级课程 display_tea_c2c
424 返回管理员中心 b
425 """
426 print(Colors(msg))
427 print('当前用户:%s' %self.user.username)
428 print("".center(50, '-'))
429 choice = input(Colors('请输入要执行的指令(b 返回):', 'cyan')).strip()
430 if choice == 'b':break
431 if hasattr(self, choice):
432 getattr(self, choice)()
433 else:
434 print(Colors('无效的指令!', 'red'))
435
436 def create_teacher(self):
437 while True:
438 add_teacher_card_id = input(Colors('请输入要创建的讲师身份证号码(b 返回)：', 'cyan')).strip()
439 if add_teacher_card_id == 'b':break
440 if not add_teacher_card_id:continue
441 if not add_teacher_card_id[:-1].isdigit() or not len(add_teacher_card_id) == 18:
442 print(Colors('无效的身份证号码，请输入正确格式身份证号码!', 'red'))
443 continue
444 tea_obj = self.session.query(Teacher).filter(Teacher.card_id==add_teacher_card_id).first()
445 if tea_obj:
446 print(Colors('该身份证号码已存在!', 'red'))
447 continue
448 while True:
449 add_teacher_name = input(Colors('请输入要创建的讲师名(b 返回)：', 'cyan')).strip()
450 if add_teacher_name == 'b':break
451 if not add_teacher_name:continue
452 user_obj_id = commons.create_user(self.session, 1)
453 if not user_obj_id:continue
454 #更新teacher表
455 tea_obj = Teacher(name=add_teacher_name, card_id=add_teacher_card_id, user_id=user_obj_id)
456 self.session.add_all([tea_obj, ])
457 self.session.commit()
458 print(Colors('创建讲师成功!', 'green'))
459 break
460
461 def display_teacher(self):
462 return commons.display_teacher(self.session)
463
464 def delete_teacher(self):
465 while True:
466 print("开始删除讲师".center(45, '-'))
467 #判断Teacher表是否为空
468 if not self.display_teacher():break
469 del_tea_card_id = input(Colors('请输入要删除的讲师的身份证号码(b 返回)：', 'cyan')).strip()
470 if not del_tea_card_id:continue
471 if del_tea_card_id == 'b':break
472 #判断该讲师身份证号码是否存在
473 tea_obj = self.session.query(Teacher).filter(Teacher.card_id==del_tea_card_id).first()
474 if not tea_obj:
475 print(Colors('该讲师身份证号码不存在!', 'red'))
476 continue
477 else:
478 #删除Teacher表记录
479 self.session.delete(tea_obj)
480 #删除User表
481 self.session.delete(tea_obj.user)
482 self.session.commit()
483 print(Colors('删除讲师成功!同时该讲师账号已被注销!', 'green'))
484
485 def update_teacher(self):
486 while True:
487 #判断Teacher表是否为空
488 if not self.display_teacher():break
489 update_tea_card_id = input(Colors('请输入要修改的讲师的身份证号码(b 返回)：', 'cyan')).strip()
490 if not update_tea_card_id:continue
491 if update_tea_card_id == 'b':break
492 #判断该讲师身份证号码是否存在
493 tea_obj = self.session.query(Teacher).filter(Teacher.card_id==update_tea_card_id).first()
494 if not tea_obj:
495 print(Colors('该讲师身份证号码不存在!', 'red'))
496 continue
497 else:
498 #修改Teacher表记录
499 column_teacher = [{'name':'讲师名'}, {'card_id':'身份证号码'},]
500 res = commons.update_table(self.session, column_teacher, tea_obj)
501 if not res:return
502
503 def add_c2c(self):
504 tea_obj = commons.choice_teacher(self.session)
505 if not tea_obj:return
506 while True:
507 #选择该讲师任教的校区
508 if not commons.display_tea_schools(tea_obj):return
509 sch_name = input(Colors('请选择任教的校区名(b 结束操作)：', 'cyan')).strip()
510 if sch_name == 'b':return
511 if not sch_name:continue
512 sch_obj = self.session.query(School).join(school_m2m_teacher).join(Teacher).\
513 filter(Teacher.name==tea_obj.name).filter(School.name==sch_name).first()
514 if not sch_obj:
515 print(Colors('讲师[%s]没有任教该校区[%s]!'%(tea_obj.name, sch_name), 'red'))
516 continue
517 while True:
518 #判断该校区是否有班级，有则继续选择讲师任课的班级
519 if not commons.display_sch_classes(sch_obj):break
520 commons.display_tea_sch_c2c(tea_obj, sch_obj)
521 cls_name = input(Colors('请输入讲师[%s]任课的班级名(b 返回)：'%tea_obj.name, 'cyan')).strip()
522 if cls_name == 'b':break
523 if not cls_name:continue
524 cls_obj = self.session.query(Class).join(School).filter(School.name==sch_name).filter(Class.name==cls_name).first()
525 if not cls_obj:
526 print(Colors('校区[%s]不存在班级[%s]!'%(sch_name, cls_name), 'red'))
527 continue
528 while True:
529 #判断该班级是否有关联课程，有则继续选择讲师任课的课程
530 if not commons.display_cls_courses(cls_obj):break
531 commons.display_tea_cls_courses(self.session, tea_obj, cls_obj)
532 cou_name = input(Colors('请输入讲师[%s]任课的课程名(b 返回)：'%tea_obj.name, 'cyan')).strip()
533 if cou_name == 'b':break
534 if not cou_name:continue
535 cou_obj = self.session.query(Course).join(Course_m2m_class).filter(Course_m2m_class.class_id==cls_obj.id).filter(Course.name==cou_name).first()
536 if not cou_obj:
537 print(Colors('班级[%s]没有添加课程[%s]!'%(sch_name, cls_name), 'red'))
538 continue
539 add_c2c_obj = self.session.query(Course_m2m_class).filter(Course_m2m_class.course_id==cou_obj.id).\
540 filter(Course_m2m_class.class_id==cls_obj.id).filter(Course_m2m_class.teacher_id.isnot(None)).first()
541 if add_c2c_obj:
542 used_tea_obj = self.session.query(Teacher).join(Course_m2m_class).filter(Course_m2m_class.course_id==cou_obj.id)\
543 .filter(Course_m2m_class.class_id==cls_obj.id).first()
544 print(Colors('该班级-课程[%s]已有任课讲师%s!请先删除该班级-课程的任课讲师'%(add_c2c_obj, used_tea_obj.name), 'red'))
545 continue
546 add_c2c_obj = self.session.query(Course_m2m_class).filter(Course_m2m_class.course_id==cou_obj.id).\
547 filter(Course_m2m_class.class_id==cls_obj.id).first()
548 #更新course_m2m_class表
549 print(add_c2c_obj)
550 tea_obj.course_m2m_classes.append(add_c2c_obj)
551 self.session.commit()
552 print(Colors('添加任课的班级-课程[%s]成功!'%add_c2c_obj, 'green'))
553
554 def del_c2c(self):
555 while True:
556 tea_obj = commons.choice_teacher(self.session)
557 if not tea_obj:return
558 if not tea_obj.course_m2m_classes:
559 print(Colors('讲师[%s]还没有添加任课的班级-课程'%tea_obj.name, 'red'))
560 continue
561 while True:
562 #选择该讲师任教的校区
563 if not commons.display_tea_schools(tea_obj):return
564 sch_name = input(Colors('请选择任教的校区名(b 结束操作)：', 'cyan')).strip()
565 if sch_name == 'b':return
566 if not sch_name:continue
567 sch_obj = self.session.query(School).join(school_m2m_teacher).join(Teacher).\
568 filter(Teacher.id==tea_obj.id).filter(School.name==sch_name).first()
569 if not sch_obj:
570 print(Colors('讲师[%s]没有任教该校区[%s]!'%(tea_obj.name, sch_name), 'red'))
571 continue
572 while True:
573 #判断该讲师是否有任课的班级，有则继续选择删除任课的班级
574 if not commons.display_tea_sch_c2c(tea_obj, sch_obj):break
575 cls_name = input(Colors('请输入要删除任课的班级名(b 返回)：', 'cyan')).strip()
576 if cls_name == 'b':break
577 if not cls_name:continue
578 while True:
579 cou_name = input(Colors('请输入要删除任课的课程名(b 返回)：', 'cyan')).strip()
580 if cou_name == 'b':break
581 if not cou_name:continue
582 c2c_obj = self.session.query(Course_m2m_class).join(Course).join(Class).\
583 filter(Class.school_id==sch_obj.id).filter(Class.name==cls_name).filter(Course.name==cou_name).first()
584 if c2c_obj not in tea_obj.course_m2m_classes:
585 print(Colors('讲师[%s]没有任课班级-课程[%s %s]'%(tea_obj.name, cls_name, cou_name), 'red'))
586 continue
587 #更新course_m2m_class表
588 tea_obj.course_m2m_classes.remove(c2c_obj)
589 self.session.commit()
590 print(Colors('删除任课的班级-课程[%s]成功!'%c2c_obj, 'green'))
591
592 def display_tea_c2c(self):
593 """查看各讲师任教的各校区任课的班级课程"""
594 tea_obj_li = self.session.query(Teacher).all()
595 if tea_obj_li:
596 for tea_obj in tea_obj_li:
597 for sch_obj in tea_obj.schools:
598 commons.display_tea_sch_c2c(tea_obj, sch_obj)
599 else:
600 print(Colors('现在还没有讲师，请先创建讲师!', 'red'))
601
602 class Manage_student(object):
603 def __init__(self, session, user):
604 self.session = session
605 self.user = user
606 self.handle()
607
608 def handle(self):
609 while True:
610 print("学员管理界面".center(45, '-'))
611 msg = """
612 创建学员 create_student
613 查看学员 display_student
614 删除学员 delete_student
615 修改学员 update_student
616 返回管理员中心 b
617 """
618 print(Colors(msg))
619 print('当前用户:%s' %self.user.username)
620 print("".center(50, '-'))
621 choice = input(Colors('请输入要执行的指令(b 返回):', 'cyan')).strip()
622 if choice == 'b':break
623 if hasattr(self, choice):
624 getattr(self, choice)()
625 else:
626 print(Colors('无效的指令!', 'red'))
627
628 def create_student(self):
629 while True:
630 add_stu_qq = input(Colors('请输入学生qq(b 返回)：', 'cyan')).strip()
631 if add_stu_qq == 'b':return
632 if not add_stu_qq:continue
633 if not add_stu_qq.isdigit() or not len(add_stu_qq) > 3:
634 print(Colors('无效的qq号，请输入正确格式qq号!', 'red'))
635 continue
636 stu_obj = self.session.query(Student).filter(Student.qq==add_stu_qq).first()
637 if stu_obj:
638 print(Colors('该qq号已存在!', 'red'))
639 continue
640 while True:
641 add_stu_name = input(Colors('请输入要创建的学生名(b 返回)：', 'cyan')).strip()
642 if add_stu_name == 'b':return
643 if not add_stu_name:continue
644 user_obj_id = commons.create_user(self.session, 2)
645 if not user_obj_id:continue
646 #更新student表
647 stu_obj = Student(name=add_stu_name, qq=add_stu_qq, user_id=user_obj_id)
648 self.session.add_all([stu_obj, ])
649 self.session.commit()
650 print(Colors('创建学生成功!', 'green'))
651 break
652
653 def display_student(self):
654 return commons.display_student(self.session)
655
656 def delete_student(self):
657 while True:
658 #判断Student表是否为空
659 if not self.display_student():break
660 del_stu_qq = input(Colors('请输入要删除的学生的QQ号码(b 返回)：', 'cyan')).strip()
661 if not del_stu_qq:continue
662 if del_stu_qq == 'b':break
663 #判断该学生qq号码是否存在
664 stu_obj = self.session.query(Student).filter(Student.qq==del_stu_qq).first()
665 if not stu_obj:
666 print(Colors('该学生qq号码不存在!', 'red'))
667 continue
668 else:
669 #删除Student表记录
670 self.session.delete(stu_obj)
671 #删除User表
672 self.session.delete(stu_obj.user)
673 self.session.commit()
674 print(Colors('删除学生成功!同时该学生账号已被注销!', 'green'))
675
676 def update_student(self):
677 while True:
678 #判断Student表是否为空
679 if not self.display_student():break
680 update_stu_qq = input(Colors('请输入要修改的学生的QQ号码(b 返回)：', 'cyan')).strip()
681 if not update_stu_qq:continue
682 if update_stu_qq == 'b':break
683 #判断该学生qq号码是否存在
684 stu_obj = self.session.query(Student).filter(Student.qq==update_stu_qq).first()
685 if not stu_obj:
686 print(Colors('该学生qq号码不存在!', 'red'))
687 continue
688 else:
689 #修改Student表记录
690 column_student = [{'name':'学生名'}, {'qq':'QQ号码'},]
691 res = commons.update_table(self.session, column_student, stu_obj)
692 if not res:return
693
694 class Manage_class(object):
695 def __init__(self, session, user):
696 self.session = session
697 self.user = user
698 self.handle()
699
700 def handle(self):
701 while True:
702 print("课程管理界面".center(45, '-'))
703 msg = """
704 创建班级 create_class
705 查看班级 display_class
706 删除班级 delete_class
707 修改班级 update_class
708 添加/删除课程 add_course/del_course
709 添加/删除学员 add_student/del_student
710 查看各班级的课程 display_cls_course
711 查看各班级的学员 display_cls_student
712 返回管理员中心 b
713 """
714 print(Colors(msg))
715 print('当前用户:%s' %self.user.username)
716 print("".center(50, '-'))
717 choice = input(Colors('请输入要执行的指令(b 返回):', 'cyan')).strip()
718 if choice == 'b':break
719 if hasattr(self, choice):
720 getattr(self, choice)()
721 else:
722 print(Colors('无效的指令!', 'red'))
723
724 def create_class(self):
725 sch_obj = commons.choice_school(self.session)
726 if not sch_obj:return
727 while True:
728 add_class_name = input(Colors('请输入要创建的班级名(b 返回)：', 'cyan')).strip()
729 if add_class_name == 'b':return
730 if not add_class_name:continue
731 #判断该校区是否已经存在该班级
732 cls_obj = self.session.query(Class).join(School).filter(School.name==sch_obj.name).filter(Class.name==add_class_name).first()
733 if cls_obj:
734 print(Colors('该校区已有该班级!', 'red'))
735 continue
736 #更新Class表
737 cls_obj = Class(name=add_class_name, school_id=sch_obj.id)
738 self.session.add_all([cls_obj, ])
739 self.session.commit()
740 print(Colors('创建班级成功!', 'green'))
741
742 def display_class(self):
743 return commons.display_class(self.session)
744
745 def delete_class(self):
746 while True:
747 #判断Class表是否为空
748 if not self.display_class():break
749 del_class_name = input(Colors('请输入要删除的班级名(b 返回)：', 'cyan')).strip()
750 if del_class_name == 'b':return
751 sch_name = input(Colors('请输入删除的班级所属校区名(b 返回)：', 'cyan')).strip()
752 if sch_name == 'b':return
753 #判断该校区是否存在该班级
754 cls_obj = self.session.query(Class).join(School).filter(Class.name==del_class_name).filter(School.name==sch_name).first()
755 if not cls_obj:
756 print(Colors('该校区[%s]下不存在班级[%s]!'%(sch_name, del_class_name), 'red'))
757 continue
758 #删除Class表记录
759 self.session.delete(cls_obj)
760 self.session.commit()
761 print(Colors('删除班级成功!', 'green'))
762
763 def update_class(self):
764 while True:
765 #判断Class表是否为空
766 if not self.display_class():break
767 update_class_name = input(Colors('请输入要修改的班级名(b 返回)：', 'cyan')).strip()
768 if update_class_name == 'b':return
769 sch_name = input(Colors('请输入要修改班级所属校区名(b 返回)：', 'cyan')).strip()
770 if sch_name == 'b':return
771 #判断该校区是否存在该班级
772 cls_obj = self.session.query(Class).join(School).filter(Class.name==update_class_name).filter(School.name==sch_name).first()
773 if not cls_obj:
774 print(Colors('该校区[%s]下不存在班级[%s]!'%(sch_name, update_class_name), 'red'))
775 continue
776 #修改Class表记录
777 column_class = [{'name':'班级名'},]
778 res = commons.update_table(self.session, column_class, cls_obj)
779 if not res:return
780
781 def add_course(self):
782 """为某班级添加课程"""
783 sch_obj, cls_obj = commons.choice_class(self.session)
784 if not cls_obj:return
785 while True:
786 #展示所选校区所有课程信息,并判断所选校区已有课程是否为空
787 if not commons.display_sch_courses(sch_obj):return
788 #展示该班级已有课程信息
789 commons.display_cls_courses(cls_obj)
790 add_cou_name = input(Colors('请输入要添加的课程名(b 返回)：', 'cyan')).strip()
791 if not add_cou_name:continue
792 if add_cou_name == 'b':break
793 #判断所选校区是否存在该课程名
794 cou_obj = self.session.query(Course).join(school_m2m_course).join(School).filter(School.id==sch_obj.id).filter(Course.name==add_cou_name).first()
795 if not cou_obj:
796 print(Colors('所选校区[%s]没有该课程[%s]!'%(sch_obj.name, add_cou_name), 'red'))
797 continue
798 #判断该班级是否已经有该课程
799 if cou_obj not in cls_obj.courses:
800 #更新course_m2m_class表
801 cls_obj.courses.append(cou_obj)
802 self.session.commit()
803 print(Colors('班级[%s]添加课程[%s]成功!'%(cls_obj.name, cou_obj.name), 'green'))
804 else:
805 print(Colors('班级[%s]已经有该课程[%s]!'%(cls_obj.name, cou_obj.name), 'red'))
806
807 def del_course(self):
808 sch_obj, cls_obj = commons.choice_class(self.session)
809 if not cls_obj:return
810 while True:
811 #展示该班级已有课程信息,没有则退出
812 if not commons.display_cls_courses(cls_obj):return
813 del_cou_name = input(Colors('请输入要删除的课程名(b 返回)：', 'cyan')).strip()
814 if not del_cou_name:continue
815 if del_cou_name == 'b':break
816 #判断该班级是否存在该课程
817 cou_obj = self.session.query(Course).join(Course_m2m_class).join(Class).filter(Class.name==cls_obj.name).filter(Course.name==del_cou_name).first()
818 if not cou_obj:
819 print(Colors('班级[%s]没有该课程[%s]!'%(cls_obj.name, del_cou_name), 'red'))
820 continue
821 else:
822 #删除该班级的该课程
823 cls_obj.courses.remove(cou_obj)
824 self.session.commit()
825 print(Colors('班级[%s]删除课程[%s]成功!'%(cls_obj.name, cou_obj.name), 'green'))
826
827 def display_cls_course(self):
828 """查看各校区下各班级已有课程"""
829 sch_obj_li = self.session.query(School).all()
830 if sch_obj_li:
831 for sch_obj in sch_obj_li:
832 print('校区[%s]:'%sch_obj.name)
833 if sch_obj.classes:
834 for cls_obj in sch_obj.classes:
835 #展示某班级所有课程信息
836 commons.display_cls_courses(cls_obj)
837 else:
838 print(Colors('现在还没有校区，请先创建校区!', 'red'))
839 else:
840 print(Colors('现在还没有校区，请先创建校区!', 'red'))
841
842 def add_student(self):
843 """为某班级添加学员"""
844 sch_obj, cls_obj = commons.choice_class(self.session)
845 if not cls_obj:return
846 while True:
847 #展示所选校区所有学员信息,并判断所选校区已有学员是否为空
848 if not commons.display_sch_students(sch_obj):return
849 #展示该班级已有学员信息
850 commons.display_cls_students(cls_obj)
851 add_stu_qq = input(Colors('请输入要添加的学员QQ(b 返回)：', 'cyan')).strip()
852 if not add_stu_qq:continue
853 if add_stu_qq == 'b':break
854 #判断所选校区是否存在该学员QQ
855 stu_obj = self.session.query(Student).join(school_m2m_student).join(School).filter(School.id==sch_obj.id).filter(Student.qq==add_stu_qq).first()
856 if not stu_obj:
857 print(Colors('所选校区[%s]没有该学员QQ[%s]!'%(sch_obj.name, add_stu_qq), 'red'))
858 continue
859 #判断该班级是否已经有该学员
860 if stu_obj not in cls_obj.students:
861 #更新student_m2m_class表
862 cls_obj.students.append(stu_obj)
863 self.session.commit()
864 print(Colors('班级[%s]添加学员[%s]成功!'%(cls_obj.name, stu_obj.name), 'green'))
865 else:
866 print(Colors('班级[%s]已经有该学员[%s]!'%(cls_obj.name, stu_obj.name), 'red'))
867
868 def del_student(self):
869 sch_obj, cls_obj = commons.choice_class(self.session)
870 if not cls_obj:return
871 while True:
872 #展示该班级已有学员信息,没有则退出
873 if not commons.display_cls_students(cls_obj):return
874 del_stu_qq = input(Colors('请输入要删除的学员QQ(b 返回)：', 'cyan')).strip()
875 if not del_stu_qq:continue
876 if del_stu_qq == 'b':break
877 #判断该班级是否存在该学员
878 stu_obj = self.session.query(Student).join(student_m2m_class).filter(student_m2m_class.class_id==cls_obj.id).filter(Student.qq==del_stu_qq).first()
879 if not stu_obj:
880 print(Colors('班级[%s]没有该学员QQ[%s]!'%(cls_obj.name, del_stu_qq), 'red'))
881 continue
882 else:
883 #删除该班级的该学员
884 cls_obj.students.remove(stu_obj)
885 self.session.commit()
886 print(Colors('班级[%s]删除学员[%s]成功!'%(cls_obj.name, stu_obj.name), 'green'))
887
888 def display_cls_student(self):
889 """查看各校区下各班级已有学员"""
890 sch_obj_li = self.session.query(School).all()
891 if sch_obj_li:
892 for sch_obj in sch_obj_li:
893 print('校区[%s]:'%sch_obj.name)
894 if sch_obj.classes:
895 for cls_obj in sch_obj.classes:
896 #展示某班级所有学员信息
897 commons.display_cls_students(cls_obj)
898 else:
899 print(Colors('现在还没有校区，请先创建校区!', 'red'))
900 else:
901 print(Colors('现在还没有校区，请先创建校区!', 'red'))
902
903 def display_course(self):
904 """展示该校区所有课程信息"""
905 if not len(self.school.courses) == 0:
906 print('该校区已有课程信息如下：')
907 column_course = [{'name':'课程名'}, {'price':'价格'}, ]
908 fmt_cou_table = commons.fmt_table(column_course, self.school.courses)
909 print(fmt_cou_table)
910 return True
911 else:
912 print(Colors('该校区还没有课程，请先为该校区增设课程!', 'red'))
913
914 def delete_course(self):
915 while True:
916 #判断该校区的课程是否为空
917 if not self.display_course():break
918 del_course_name = input(Colors('请输入要删除的课程名(b 返回)：', 'cyan')).strip()
919 if not del_course_name:continue
920 if del_course_name == 'b':break
921 #判断该校区是否存在该课程
922 cou_obj = self.session.query(Course).join(school_m2m_course).join(School).filter(School.name==self.school.name).filter(Course.name==del_course_name).first()
923 if not cou_obj:
924 print(Colors('该校区没有该课程!', 'red'))
925 continue
926 else:
927 #删除该校区的该课程
928 self.school.courses.remove(cou_obj)
929 self.session.commit()
930 print(Colors('该校区删除课程成功!', 'green'))
931
932 def display_teacher(self):
933 """展示该校区所有讲师信息"""
934 if self.school.teachers:
935 column_teacher = [{'name':'讲师名'}, {'card_id':'身份证号码'}, {'user':'账号信息'}]
936 fmt_tea_table = commons.fmt_table(column_teacher, self.school.teachers)
937 print(Colors('该校区已有讲师信息如下：'))
938 print(fmt_tea_table)
939 return True
940 else:
941 print(Colors('该校区还没有讲师，请先为该校区创建讲师!', 'red'))
942
943 def create_teacher(self):
944 add_teacher_name = None
945 add_teacher_card_id = None
946 add_username = None
947 add_password = None
948 sch_obj = None
949 cou_m2m_cls_li = []
950 while True:
951 add_teacher_name = input(Colors('请输入要创建的讲师名(b 返回)：', 'cyan')).strip()
952 if add_teacher_name == 'b':return
953 if not add_teacher_name:continue
954 break
955 while True:
956 add_teacher_card_id = input(Colors('请输入讲师身份证号码(b 返回)：', 'cyan')).strip()
957 if add_teacher_card_id == 'b':return
958 if not add_teacher_card_id:continue
959 if not add_teacher_card_id[:-1].isdigit() or not len(add_teacher_card_id) == 18:
960 print(Colors('无效的身份证号码，请输入正确格式身份证号码!', 'red'))
961 continue
962 tea_obj = self.session.query(Teacher).filter(Teacher.card_id==add_teacher_card_id).first()
963 if tea_obj:
964 print(Colors('该身份证号码已存在!', 'red'))
965 continue
966 break
967 while True:
968 add_username = input(Colors('请输入该讲师用于登陆的用户名(b 返回)：', 'cyan')).strip()
969 if add_username == 'b':return
970 if not add_username:continue
971 user_obj = self.session.query(User).filter(User.username==add_username).first()
972 if user_obj:
973 print(Colors('该用户名已存在!', 'red'))
974 continue
975 break
976 while True:
977 add_password = input(Colors('请输入该讲师用于登陆的密码(b 返回)：', 'cyan')).strip()
978 if add_password == 'b':return
979 if not add_password:continue
980 md5 = hashlib.md5()
981 md5.update(add_password.encode('utf-8'))
982 add_password = md5.hexdigest()
983 break
984 #选择该讲师归属的校区
985 while while_flag:
986 res = self.display_school()
987 if not res:
988 while_flag = False
989 break
990 sch_name = input(Colors('请输入该讲师归属的校区名(b 返回)：', 'cyan')).strip()
991 if sch_name == 'b':while_flag = False
992 if not sch_name:continue
993 sch_obj = self.session.query(School).filter(School.name==sch_name).first()
994 if not sch_obj:
995 print(Colors('该校区名不存在!', 'red'))
996 continue
997 break
998 #判断该校区是否有班级，有则继续选择讲师任课的班级
999 if while_flag:
1000 if sch_obj.classes:
1001 choice_cls_obj_li = []
1002 choice_cls_name_li = []
1003 while while_flag:
1004 self.display_class(sch_name=sch_name)
1005 print('已选择班级:%s'%choice_cls_name_li)
1006 cls_name = input(Colors('请输入该讲师任课的班级名(b 返回|end 结束选择班级)：', 'cyan')).strip()
1007 if cls_name == 'b':
1008 while_flag = False
1009 break
1010 if cls_name == 'end':break
1011 if not cls_name:continue
1012 cls_obj = self.session.query(Class).join(School).filter(School.name==sch_name).filter(Class.name==cls_name).first()
1013 if not cls_obj:
1014 print(Colors('校区[%s]不存在[%s]该班级!'%(sch_name, cls_name), 'red'))
1015 continue
1016 if cls_obj in choice_cls_obj_li:
1017 print(Colors('已经选择过该班级', 'red'))
1018 continue
1019 #判断该班级是否有关联课程，有则继续选择讲师任课的课程
1020 if cls_obj.courses:
1021 choice_cou_obj_li = []
1022 choice_cou_name_li = []
1023 while while_flag:
1024 print('班级[%s]的所有课程:'%cls_obj.name)
1025 column_course = [{'name':'课程名'},]
1026 fmt_cou_table = commons.fmt_table(column_course, cls_obj.courses)
1027 print(fmt_cou_table)
1028 print('已选择课程:%s'%choice_cou_name_li)
1029 cou_name = input(Colors('请输入该讲师任课的课程名(b 返回|end 结束选择课程)：', 'cyan')).strip()
1030 if cou_name == 'b':
1031 while_flag = False
1032 break
1033 if cou_name == 'end':
1034 if len(choice_cou_obj_li) != 0:
1035 for cou_obj in choice_cou_obj_li:
1036 cou_m2m_cls_li.append(self.session.query(Course_m2m_class).filter(Course_m2m_class.course_id==cou_obj.id).filter(Course_m2m_class.class_id==cls_obj.id).first())
1037 choice_cls_obj_li.append(cls_obj)
1038 choice_cls_name_li.append(cls_name)
1039 break
1040 if not cou_name:continue
1041 cou_obj = self.session.query(Course).join(Course_m2m_class).join(Class).filter(Class.name==cls_obj.name).filter(Course.name==cou_name).first()
1042 if not cou_obj:
1043 print(Colors('班级[%s]不存在[%s]该课程!'%(cls_obj.name, cou_name), 'red'))
1044 continue
1045 if cou_obj in choice_cou_obj_li:
1046 print(Colors('已经选择过该课程', 'red'))
1047 continue
1048 choice_cou_obj_li.append(cou_obj)
1049 choice_cou_name_li.append(cou_name)
1050 else:
1051 print(Colors('该班级还没有关联课程!', 'red'))
1052 else:
1053 print(Colors('该校区还没有设立班级，已经跳过为该讲师选择任课班级和课程的步骤!', 'red'))
1054 if while_flag:
1055 #至此，Teacher/course_m2m_class/User/school_m2m_teacher表所有外键关联确认完毕
1056 #更新User表
1057 user_obj = User(username=add_username, password=add_password, permission=1)
1058 self.session.add_all([user_obj, ])
1059 self.session.commit()
1060 #更新teacher表
1061 user_obj = self.session.query(User).filter(User.username==add_username).first()
1062 tea_obj = Teacher(name=add_teacher_name, card_id=add_teacher_card_id, user_id=user_obj.id)
1063 self.session.add_all([tea_obj, ])
1064 #更新school_m2m_teacher表
1065 tea_obj.schools = [sch_obj,]
1066 self.session.commit()
1067 #判断该校区是否有班级且选择的班级是否有课程，没有则不更新Course_m2m_class表
1068 if not len(cou_m2m_cls_li) == 0:
1069 #更新course_m2m_class表
1070 tea_obj.course_m2m_classes = cou_m2m_cls_li
1071 self.session.commit()
1072 print(Colors('创建讲师成功!', 'green'))
1073
1074 def create_student(self):
1075 while_flag = True
1076 add_stu_name = None
1077 add_stu_qq = None
1078 add_username = None
1079 add_password = None
1080 sch_obj = None
1081 cls_obj = None
1082 while while_flag:
1083 add_stu_name = input(Colors('请输入要创建的学生名(b 返回)：', 'cyan')).strip()
1084 if add_stu_name == 'b':while_flag = False
1085 if not add_stu_name:continue
1086 break
1087 while while_flag:
1088 add_stu_qq = input(Colors('请输入学生qq(b 返回)：', 'cyan')).strip()
1089 if add_stu_qq == 'b':while_flag = False
1090 if not add_stu_qq:continue
1091 if not add_stu_qq.isdigit() or not len(add_stu_qq) > 3:
1092 print(Colors('无效的qq号，请输入正确格式qq号!', 'red'))
1093 continue
1094 stu_obj = self.session.query(Student).filter(Student.qq==add_stu_qq).first()
1095 if stu_obj:
1096 print(Colors('该qq号已存在!', 'red'))
1097 continue
1098 else:
1099 break
1100 while while_flag:
1101 add_username = input(Colors('请输入该学生用于登陆的用户名(b 返回)：', 'cyan')).strip()
1102 if add_username == 'b':while_flag = False
1103 if not add_username:continue
1104 #判断该用户名是否存在
1105 user_obj = self.session.query(User).filter(User.username==add_username).first()
1106 if user_obj:
1107 print(Colors('该用户名已存在!', 'red'))
1108 continue
1109 break
1110 while while_flag:
1111 add_password = input(Colors('请输入该学生用于登陆的密码(b 返回)：', 'cyan')).strip()
1112 if add_password == 'b':while_flag = False
1113 if not add_password:continue
1114 md5 = hashlib.md5()
1115 md5.update(add_password.encode('utf-8'))
1116 add_password = md5.hexdigest()
1117 break
1118 #选择该学生注册的校区
1119 while while_flag:
1120 res = self.display_school()
1121 if not res:while_flag = False
1122 sch_name = input(Colors('请输入该学生注册的校区名(b 返回)：', 'cyan')).strip()
1123 if sch_name == 'b':while_flag = False
1124 if not sch_name:continue
1125 sch_obj = self.session.query(School).filter(School.name==sch_name).first()
1126 if not sch_obj:
1127 print(Colors('该校区名不存在!', 'red'))
1128 continue
1129 break
1130 #判断该校区是否有班级，有则继续选择学生报名的班级，没有则退出
1131 if sch_obj.classes:
1132 while while_flag:
1133 self.display_class(sch_name=sch_name)
1134 cls_name = input(Colors('请输入学生报名的班级名(b 返回)：', 'cyan')).strip()
1135 if cls_name == 'b':while_flag = False
1136 if not cls_name:continue
1137 cls_obj = self.session.query(Class).join(School).filter(School.name==sch_name).filter(Class.name==cls_name).first()
1138 if not cls_obj:
1139 print(Colors('校区[%s]不存在[%s]该班级!'%(sch_name, cls_name), 'red'))
1140 continue
1141 break
1142 else:
1143 print(Colors('该校区还没有设立班级，请先为该校区增设班级!', 'red'))
1144 return
1145 #至此，Student/User/school_m2m_student/student_m2m_class表所有外键关联确认完毕
1146 #更新User表
1147 user_obj = User(username=add_username, password=add_password, permission=2)
1148 self.session.add_all([user_obj, ])
1149 self.session.commit()
1150 #更新student表
1151 user_obj = self.session.query(User).filter(User.username==add_username).first()
1152 stu_obj = Student(name=add_stu_name, qq=add_stu_qq, user_id=user_obj.id)
1153 self.session.add_all([stu_obj, ])
1154 #更新school_m2m_student表
1155 stu_obj.schools = [sch_obj,]
1156 self.session.commit()
1157 #判断该校区是否有班级，没有则不更新student_m2m_class表
1158 if cls_obj:
1159 #更新course_m2m_class表
1160 stu_obj.classes = [cls_obj,]
1161 self.session.commit()
1162 print(Colors('创建学生成功!', 'green'))
1163
1164 def create_class(self):
1165 sch_obj = None #要创建班级的校区
1166 add_class_name = None #新增班级名
1167 tea_obj = None #该班级的班主任
1168 choice_cou_obj_li = None #该班级关联的课程列表
1169 while True:
1170 #判断School表是否为空
1171 res = Manage_school(self.session, self.user).display_school()
1172 if not res:break
1173 choice_school_name = input(Colors('请输入要创建班级的校区名(b 返回)：', 'cyan')).strip()
1174 if not choice_school_name:continue
1175 if choice_school_name == 'b':return
1176 #判断该校区名是否存在
1177 sch_obj = self.session.query(School).filter(School.name==choice_school_name).first()
1178 if not sch_obj:
1179 print(Colors('该校区名不存在!', 'red'))
1180 continue
1181 break
1182 while True:
1183 add_class_name = input(Colors('请输入要创建的班级名(b 返回)：', 'cyan')).strip()
1184 if add_class_name == 'b':return
1185 if not add_class_name:continue
1186 #判断该校区是否已经存在该班级
1187 cls_obj = self.session.query(Class).join(School).filter(School.name==sch_obj.name).filter(Class.name==add_class_name).first()
1188 if cls_obj:
1189 print(Colors('该校区已有该班级!', 'red'))
1190 continue
1191 break
1192 #判断该校区是否存在讲师
1193 if sch_obj.teachers:
1194 while True:
1195 Manage_teacher.display_teacher()
1196 add_class_tea_card_id = input(Colors('请输入该班级的班主任(讲师)身份证号(b 返回)：', 'cyan')).strip()
1197 if add_class_tea_card_id == 'b':
1198 while_flag = False
1199 break
1200 if not add_class_tea_card_id:continue
1201 tea_obj = self.session.query(Teacher).join(school_m2m_teacher).join(School).filter(School.name==self.school.name).filter(Teacher.card_id==add_class_tea_card_id).first()
1202 if not tea_obj:
1203 print(Colors('该校区不存在该讲师身份证号!', 'red'))
1204 continue
1205 break
1206 else:
1207 print(Colors('该校区还没有讲师，已经跳过为该班级指定班主任(讲师)的步骤!', 'red'))
1208 if while_flag:
1209 #判断该校区是否存在课程，并为该班级关联课程
1210 if self.school.courses:
1211 choice_cou_obj_li = []
1212 choice_cou_name_li = []
1213 while while_flag:
1214 self.display_course()
1215 print(Colors('该班级已关联课程名:%s'%choice_cou_name_li, 'cyan'))
1216 cou_name = input(Colors('请输入该班级关联的课程名(b 返回|end 结束关联课程)：', 'cyan')).strip()
1217 if cou_name == 'b':
1218 while_flag = False
1219 break
1220 if cou_name == 'end':break
1221 if not cou_name:continue
1222 cou_obj = self.session.query(Course).join(school_m2m_course).join(School).filter(School.name==self.school.name).filter(Course.name==cou_name).first()
1223 if not cou_obj:
1224 print(Colors('该校区不存在课程[%s]!'%cou_name, 'red'))
1225 continue
1226 if cou_obj in choice_cou_obj_li:
1227 print(Colors('该班级已关联该课程[%s]'%cou_name, 'red'))
1228 continue
1229 choice_cou_obj_li.append(cou_obj)
1230 choice_cou_name_li.append(cou_name)
1231 else:
1232 print(Colors('该校区还没有增设课程，已经跳过为该班级关联课程的步骤!', 'red'))
1233 if while_flag:
1234 #更新Class表
1235 if tea_obj:
1236 cls_obj = Class(name=add_class_name, school_id=self.school.id, class_teacher_id=tea_obj.id)
1237 else:
1238 cls_obj = Class(name=add_class_name, school_id=self.school.id)
1239 self.session.add_all([cls_obj, ])
1240 self.session.commit()
1241 #更新course_m2m_class表
1242 if choice_cou_obj_li:
1243 cls_obj = self.session.query(Class).join(School).filter(School.name==self.school.name).filter(Class.name==add_class_name).first()
1244 cls_obj.courses = choice_cou_obj_li
1245 self.session.commit()
1246 print(Colors('创建班级成功!', 'green'))
center_admin.py
center_teacher.py 讲师中心
1 # Author：ton
2 # -*- coding: utf-8 -*-
3 from core.color import Colors
4 from core import commons
5 from core import auth
6 from core.create_table import Course_m2m_class, User, School, Course, Teacher, Class, Student, Lesson, C2c_m2m_lesson,\
7 Study_record, school_m2m_course, school_m2m_teacher, school_m2m_student, student_m2m_class
8
9 class Center_teacher(object):
10 """讲师中心"""
11 def __init__(self, session):
12 self.session = session
13 self.handle()
14
15 @auth.auth(permission=1)
16 def handle(self):
17 self.tea_obj = self.user.teacher[0]
18 while True:
19 print("讲师中心".center(45, '-'))
20 msg = """
21 创建指定班级-课程、天数的上课纪录 create_c2c_m2m_lesson
22 查看上课记录 display_tea_c2c_m2m_lessons
23 批改成绩 correcting_results
24 退出并返回系统主界面 q
25 """
26 print(Colors(msg))
27 print('当前讲师:%s' %self.tea_obj.name)
28 print("".center(50, '-'))
29 choice = input(Colors('请输入要执行的指令:', 'cyan')).strip()
30 if choice == 'q':break
31 if hasattr(self, choice):
32 getattr(self, choice)()
33 else:
34 print(Colors('无效的指令!', 'red'))
35
36 def create_c2c_m2m_lesson(self):
37 while True:
38 if not commons.display_tea_classes(self.session, self.tea_obj):return
39 cls_name = input(Colors('请指定班级名(b返回)：', 'cyan')).strip()
40 if cls_name == 'b':break
41 if not cls_name:continue
42 #判断该讲师是否有任课该班级名
43 cls_obj = self.session.query(Class).join(Course_m2m_class).filter(Course_m2m_class.teacher_id==self.tea_obj.id).filter(Class.name==cls_name).first()
44 if not cls_obj:
45 print(Colors('讲师[%s]没有任课该班级[%s]!'%(self.tea_obj.name, cls_name), 'red'))
46 continue
47 while True:
48 if not commons.display_tea_cls_courses(self.session, self.tea_obj, cls_obj):return
49 cou_name = input(Colors('请指定课程名(b返回)：', 'cyan')).strip()
50 if cou_name == 'b':break
51 if not cou_name:continue
52 #判断该讲师是否有任课该班级-课程
53 c2c_obj = self.session.query(Course_m2m_class).join(Course).filter(Course_m2m_class.teacher_id==self.tea_obj.id)\
54 .filter(Course_m2m_class.class_id==cls_obj.id).filter(Course.name==cou_name).first()
55 if not c2c_obj:
56 print(Colors('讲师[%s]没有任课[班级%s课程%s]!'%(self.tea_obj.name, cls_name, cou_name), 'red'))
57 continue
58 while True:
59 print(Colors('选定的班级-课程[%s]'%c2c_obj, 'cyan'))
60 les_name = input(Colors('请指定天数(例如day01)(b返回)：', 'cyan')).strip()
61 if les_name == 'b':break
62 if not les_name:continue
63 #该天数不存在则更新Lesson表
64 les_obj = self.session.query(Lesson).filter(Lesson.name==les_name).first()
65 if not les_obj:
66 les_obj = Lesson(name=les_name)
67 self.session.add(les_obj)
68 self.session.commit()
69 #该上课记录不存在则更新C2c_m2m_lesson表
70 c2c_m2m_les_obj = self.session.query(C2c_m2m_lesson).filter(C2c_m2m_lesson.lesson_id==les_obj.id)\
71 .filter(C2c_m2m_lesson.course_m2m_class_id==c2c_obj.id).first()
72 if c2c_m2m_les_obj:
73 print(Colors('该上课记录已存在!', 'red'))
74 continue
75 c2c_m2m_les_obj = C2c_m2m_lesson(course_m2m_class_id=c2c_obj.id, lesson_id=les_obj.id)
76 self.session.add(c2c_m2m_les_obj)
77 self.session.commit()
78 #更新该班级所有学员的学习记录
79 for stu_obj in cls_obj.students:
80 stu_rec_obj = Study_record(c2c_m2m_lesson_id=c2c_m2m_les_obj.id, student_id=stu_obj.id)
81 self.session.add(stu_rec_obj)
82 self.session.flush()
83 self.session.commit()
84
85 print(Colors('创建上课纪录成功!', 'green'))
86
87 def display_tea_c2c_m2m_lessons(self):
88 for sch_obj in self.tea_obj.schools:
89 commons.display_tea_sch_c2c_m2m_lessons(self.session, self.tea_obj, sch_obj)
90
91 def correcting_results(self):
92 """批改作业"""
93 while True:
94 for sch_obj in self.session.query(School).join(school_m2m_teacher).join(Teacher).filter(Teacher.id==self.tea_obj.id).all():
95 commons.display_tea_sch_studyRecords(self.session, self.tea_obj, sch_obj)
96 stu_rec_id = input(Colors('请输入批改的学习记录ID(b返回)：', 'cyan')).strip()
97 if stu_rec_id == 'b':break
98 if not stu_rec_id:continue
99 stu_rec_obj = self.session.query(Study_record).join(C2c_m2m_lesson).join(Course_m2m_class).\
100 filter(Course_m2m_class.teacher_id==self.tea_obj.id).filter(Study_record.id==stu_rec_id).first()
101 if not stu_rec_obj:
102 print(Colors('无权限或者没有该学习记录!', 'red'))
103 continue
104 score = input(Colors('请输入批改后的分数(b返回)：', 'cyan')).strip()
105 if score == 'b':break
106 if not score:continue
107 #更新Study_record表
108 stu_rec_obj.score = score
109 self.session.commit()
110 print(Colors('批改作业成功!', 'green'))
center_teacher.py
center_student.py 学员中心
1 from core.color import Colors
2 from core import commons
3 from core import auth
4 from core.create_table import Course_m2m_class, User, School, Course, Teacher, Class, Student, Lesson, C2c_m2m_lesson,\
5 Study_record, school_m2m_course, school_m2m_teacher, school_m2m_student, student_m2m_class
6
7 class Center_student(object):
8 """学员中心"""
9 def __init__(self, session):
10 self.session = session
11 self.handle()
12
13 @auth.auth(permission=2)
14 def handle(self):
15 self.stu_obj = self.user.student[0]
16 while True:
17 print("学员中心".center(45, '-'))
18 msg = """
19 签到(提交作业) sign_in
20 查看作业成绩 display_score
21 成绩排名 pai
22 退出并返回系统主界面 q
23 """
24 print(Colors(msg))
25 print('当前学员:%s' %self.stu_obj.name)
26 print("".center(50, '-'))
27 choice = input(Colors('请输入要执行的指令:', 'cyan')).strip()
28 if choice == 'q':break
29 if hasattr(self, choice):
30 getattr(self, choice)()
31 else:
32 print(Colors('无效的指令!', 'red'))
33
34 def sign_in(self):
35 while True:
36 for sch_obj in self.session.query(School).join(school_m2m_student).join(Student).filter(Student.id==self.stu_obj.id).all():
37 commons.display_stu_sch_studyRecords(self.session, self.stu_obj, sch_obj)
38 stu_rec_id = input(Colors('请输入要签到的学习记录ID(b返回)：', 'cyan')).strip()
39 if stu_rec_id == 'b':break
40 if not stu_rec_id:continue
41 stu_rec_obj = self.session.query(Study_record).filter(Study_record.student_id==self.stu_obj.id).filter(Study_record.id==stu_rec_id).first()
42 if not stu_rec_obj:
43 print(Colors('无权限或者没有该学习记录!', 'red'))
44 continue
45 #更新Study_record表
46 stu_rec_obj.status = 'yes'
47 self.session.commit()
48 print(Colors('签到成功!', 'green'))
49
50 def display_score(self):
51 for sch_obj in self.session.query(School).join(school_m2m_student).join(Student).filter(Student.id==self.stu_obj.id).all():
52 commons.display_stu_sch_studyRecords(self.session, self.stu_obj, sch_obj)
53
54
55 def pai(self):
56 for sch_obj in self.session.query(School).join(school_m2m_student).join(Student).filter(Student.id==self.stu_obj.id).all():
57 commons.display_stu_sch_cls_score(self.session, self.stu_obj, sch_obj)
center_student.py
commons.py
1 from tabulate import tabulate
2 from core.color import Colors
3 from sqlalchemy import Column, Integer, String, Table, MetaData, ForeignKey, func,DATE, Enum
4 from core.create_table import User, School, Course, Teacher, Student, Class, Course_m2m_class, C2c_m2m_lesson, Study_record, student_m2m_class
5 import hashlib
6
7
8 def fmt_table(column_table, table_obj_li):
9 headers = []
10 table = []
11 for column_dic in column_table:
12 for column_name in column_dic:
13 headers.append(column_dic[column_name])
14 for obj in table_obj_li:
15 row = []
16 for column_dic in column_table:
17 for column_name in column_dic:
18 row.append(getattr(obj, column_name))
19 table.append(row)
20 else:
21 return tabulate(table, headers, tablefmt='psql')
22
23 def update_table(session, column_table, table_obj):
24 for column_dic in column_table:
25 for column_name in column_dic:
26 while True:
27 new_value = input(Colors('请输入修改后的%s(b 退出修改操作):'%column_dic[column_name], 'cyan')).strip()
28 if not new_value:continue
29 if new_value == 'b':return
30 setattr(table_obj, column_name, new_value)
31 break
32 session.commit()
33 print(Colors('更新%s表成功!'%table_obj.__tablename__, 'green'))
34 return True
35
36 def create_user(session, permission):
37 while True:
38 add_username = input(Colors('请输入用于登陆的用户名(b 返回)：', 'cyan')).strip()
39 if add_username == 'b':return
40 if not add_username:continue
41 user_obj = session.query(User).filter(User.username==add_username).first()
42 if user_obj:
43 print(Colors('该用户名已存在!', 'red'))
44 continue
45 while True:
46 add_password = input(Colors('请输入用于登陆的密码(b 返回)：', 'cyan')).strip()
47 if add_password == 'b':break
48 if not add_password:continue
49 md5 = hashlib.md5()
50 md5.update(add_password.encode('utf-8'))
51 add_password = md5.hexdigest()
52 #更新User表
53 user_obj = User(username=add_username, password=add_password, permission=permission)
54 session.add_all([user_obj, ])
55 session.commit()
56 user_obj = session.query(User).filter(User.username==add_username).first()
57 return user_obj.id
58
59 def choice_school(session):
60 while True:
61 #判断School表是否为空,为空则退出
62 if not display_school(session):return
63 choice_school_name = input(Colors('请输入校区名(b 返回)：', 'cyan')).strip()
64 if choice_school_name == 'b':return
65 if not choice_school_name:continue
66 #判断该校区名是否存在
67 sch_obj = session.query(School).filter(School.name==choice_school_name).first()
68 if not sch_obj:
69 print(Colors('该校区名不存在!', 'red'))
70 continue
71 return sch_obj
72
73 def choice_teacher(session):
74 while True:
75 #判断Teacher表是否为空,为空则退出
76 if not display_teacher(session):return
77 choice_tea_card_id = input(Colors('请输入讲师身份证号码(b 返回)：', 'cyan')).strip()
78 if choice_tea_card_id == 'b':return
79 if not choice_tea_card_id:continue
80 #判断该讲师身份证号码是否存在
81 tea_obj = session.query(Teacher).filter(Teacher.card_id==choice_tea_card_id).first()
82 if not tea_obj:
83 print(Colors('该讲师身份证号码不存在!', 'red'))
84 continue
85 return tea_obj
86
87 def choice_class(session):
88 while True:
89 sch_obj = choice_school(session)
90 if not sch_obj:return
91 while True:
92 #判断该校区的班级是否为空,为空则break
93 if not display_sch_classes(sch_obj):break
94 choice_cls_name = input(Colors('请输入班级名(b 返回)：', 'cyan')).strip()
95 if choice_cls_name == 'b':break
96 if not choice_cls_name:continue
97 #判断该班级名是否存在
98 cls_obj = session.query(Class).filter(Class.name==choice_cls_name).first()
99 if not cls_obj:
100 print(Colors('校区[%s]没有该班级[%s]!'%(sch_obj.name, choice_cls_name), 'red'))
101 continue
102 return sch_obj, cls_obj
103
104 def display_school(session):
105 sch_obj_li = session.query(School).all()
106 if sch_obj_li:
107 print('所有校区信息如下：')
108 column_school = [{'name':'校区名'}, {'address':'地址'}, ]
109 fmt_sch_table = fmt_table(column_school, sch_obj_li)
110 print(fmt_sch_table)
111 return True
112 else:
113 print(Colors('现在还没有校区，请先创建校区!', 'red'))
114
115 def display_course(session):
116 cou_obj_li = session.query(Course).all()
117 if not len(cou_obj_li) == 0:
118 print('所有课程信息如下：')
119 column_course = [{'name':'课程名'}, {'price':'价格'}, ]
120 fmt_cou_table = fmt_table(column_course, cou_obj_li)
121 print(fmt_cou_table)
122 return True
123 else:
124 print(Colors('现在还没有课程，请先创建课程!', 'red'))
125
126 def display_teacher(session):
127 tea_obj_li = session.query(Teacher).all()
128 if tea_obj_li:
129 column_teacher = [{'name':'讲师名'}, {'card_id':'身份证号码'}, {'user':'账号信息'}, {'schools':'所属校区信息'}, {'course_m2m_classes':'任课班级和课程信息'}]
130 fmt_tea_table = fmt_table(column_teacher, tea_obj_li)
131 print(Colors('所有讲师信息如下：'))
132 print(fmt_tea_table)
133 return True
134 else:
135 print(Colors('现在还没有讲师，请先创建讲师!', 'red'))
136
137 def display_student(session):
138 stu_obj_li = session.query(Student).all()
139 if stu_obj_li:
140 column_student = [{'name':'学员名'}, {'qq':'QQ号'}, {'user':'账号信息'}, {'schools':'所属校区信息'}, {'classes':'报名班级信息'}]
141 fmt_stu_table = fmt_table(column_student, stu_obj_li)
142 print(Colors('所有学员信息如下：'))
143 print(fmt_stu_table)
144 return True
145 else:
146 print(Colors('现在还没有学员，请先创建学员!', 'red'))
147
148 def display_class(session):
149 cls_obj_li = session.query(Class).all()
150 if cls_obj_li:
151 column_class = [{'name':'班级名'}, {'school':'所属校区信息'}, {'courses':'关联课程'}, {'students':'该班学员信息'}]
152 fmt_cls_table = fmt_table(column_class, cls_obj_li)
153 print(Colors('所有班级信息如下：'))
154 print(fmt_cls_table)
155 return True
156 else:
157 print(Colors('现在还没有班级，请先创建班级!', 'red'))
158
159 def display_sch_courses(sch_obj):
160 """展示某校区所有课程信息"""
161 if sch_obj.courses:
162 print('校区[%s]已有课程信息如下：'%sch_obj.name)
163 column_course = [{'name':'课程名'}, {'price':'价格'}, ]
164 fmt_cou_table = fmt_table(column_course, sch_obj.courses)
165 print(fmt_cou_table)
166 return True
167 else:
168 print(Colors('校区[%s]还没有课程'%sch_obj.name, 'red'))
169
170 def display_sch_teachers(sch_obj):
171 """展示某校区所有讲师信息"""
172 if sch_obj.teachers:
173 print('校区[%s]已有讲师信息如下：'%sch_obj.name)
174 column_teacher = [{'name':'讲师名'}, {'card_id':'身份证号码'}, {'user':'账号信息'}, {'schools':'所属校区信息'}, {'course_m2m_classes':'任课班级和课程信息'}]
175 fmt_tea_table = fmt_table(column_teacher, sch_obj.teachers)
176 print(fmt_tea_table)
177 return True
178 else:
179 print(Colors('校区[%s]还没有讲师'%sch_obj.name, 'red'))
180
181 def display_sch_students(sch_obj):
182 """展示某校区所有学员信息"""
183 if sch_obj.students:
184 print('校区[%s]已有学员信息如下：'%sch_obj.name)
185 column_student = [{'name':'学员名'}, {'qq':'QQ号'}, {'user':'账号信息'}, {'schools':'所属校区信息'}, {'classes':'报名班级信息'}]
186 fmt_stu_table = fmt_table(column_student, sch_obj.students)
187 print(fmt_stu_table)
188 return True
189 else:
190 print(Colors('校区[%s]还没有学员'%sch_obj.name, 'red'))
191
192 def display_sch_classes(sch_obj):
193 """展示某校区所有班级信息"""
194 if sch_obj.classes:
195 print('校区[%s]已有班级信息如下：'%sch_obj.name)
196 column_class = [{'name':'班级名'}, {'school':'所属校区信息'}, {'courses':'关联课程'}, {'students':'该班学员信息'}]
197 fmt_cls_table = fmt_table(column_class, sch_obj.classes)
198 print(fmt_cls_table)
199 return True
200 else:
201 print(Colors('校区[%s]还没有班级'%sch_obj.name, 'red'))
202
203 def display_cls_courses(cls_obj):
204 """展示某班级已有课程信息"""
205 if not cls_obj.courses:
206 print(Colors('班级[%s]还没有添加课程'%cls_obj.name, 'red'))
207 return
208 print('班级[%s]关联的所有课程信息如下：'%cls_obj.name)
209 column_course = [{'name':'课程名'},]
210 fmt_cou_table = fmt_table(column_course, cls_obj.courses)
211 print(fmt_cou_table)
212 return True
213
214 def display_cls_students(cls_obj):
215 """展示某班级已有学员信息"""
216 if not cls_obj.students:
217 print(Colors('班级[%s]还没有添加学员'%cls_obj.name, 'red'))
218 return
219 print('班级[%s]的所有学员信息如下：'%cls_obj.name)
220 column_student = [{'name':'学员名'}, {'qq':'QQ号'}, {'user':'账号信息'}, {'schools':'所属校区信息'}, {'classes':'报名班级信息'}]
221 fmt_stu_table = fmt_table(column_student, cls_obj.students)
222 print(fmt_stu_table)
223 return True
224
225 def display_tea_schools(tea_obj):
226 if not tea_obj.schools:
227 print(Colors('讲师[%s]还没有任教的校区,请在校区管理界面添加讲师!'%tea_obj.name, 'red'))
228 return
229 print('讲师[%s]任教的所有校区信息如下：'%tea_obj.name)
230 column_school = [{'name':'校区名'}, {'address':'地址'}, ]
231 fmt_sch_table = fmt_table(column_school, tea_obj.schools)
232 print(fmt_sch_table)
233 return True
234
235 def display_tea_classes(session, tea_obj):
236 """展示某讲师任课的所有班级"""
237 cls_obj_li = session.query(Class).join(Course_m2m_class).filter(Course_m2m_class.teacher_id==tea_obj.id).all()
238 if not cls_obj_li:
239 print(Colors('讲师[%s]还没有添加任课的班级,请在讲师管理界面添加班级-课程!'%tea_obj.name, 'red'))
240 return
241 print('讲师[%s]任课的所有班级信息如下：'%tea_obj.name)
242 column_class = [{'name':'班级名'}, {'school':'所属校区信息'}, {'courses':'关联课程'}, {'students':'该班学员信息'}]
243 fmt_cls_table = fmt_table(column_class, cls_obj_li)
244 print(fmt_cls_table)
245 return True
246
247 def display_tea_cls_courses(session, tea_obj, cls_obj):
248 """展示某讲师任课的某班级的所有任课课程"""
249 cou_obj_li = session.query(Course).join(Course_m2m_class).filter(Course_m2m_class.teacher_id==tea_obj.id).filter(Course_m2m_class.class_id==cls_obj.id).all()
250 if not cou_obj_li:
251 print(Colors('讲师[%s]没有任课班级[%s]!'%(tea_obj.name, cls_obj.name), 'red'))
252 return
253 print('讲师[%s]任课的校区%s班级[%s]的所有任课课程信息如下：'%(tea_obj.name, cls_obj.school.name, cls_obj.name))
254 column_course = [{'name':'课程名'},]
255 fmt_cls_table = fmt_table(column_course, cou_obj_li)
256 print(fmt_cls_table)
257 return True
258
259 def display_tea_sch_c2c(tea_obj, sch_obj):
260 """展示讲师任教的某一校区所任课的班级-课程"""
261 #找出讲师任教的某一校区所任课的班级-课程
262 c2c_obj_li = select_tea_sch_c2c(tea_obj, sch_obj)
263 if len(c2c_obj_li)==0:
264 print(Colors('讲师%s任教的校区%s还没有任课的班级-课程'%(tea_obj.name, sch_obj.name), 'red'))
265 return
266 c2c_name_dic = {}
267 for c2c_obj in c2c_obj_li:
268 if not c2c_name_dic.get(c2c_obj.glass.name):
269 c2c_name_dic[c2c_obj.glass.name] = [c2c_obj.course.name,]
270 else:
271 c2c_name_dic[c2c_obj.glass.name].append(c2c_obj.course.name)
272 print('讲师%s任教的校区%s任课的班级-课程信息如下：'%(tea_obj.name, sch_obj.name))
273 for cls_name in c2c_name_dic:
274 print('\t班级[%s]:'%cls_name)
275 for cou_name in c2c_name_dic[cls_name]:
276 print('\t\t课程[%s]'%cou_name)
277 return True
278
279 def display_tea_sch_c2c_m2m_lessons(session, tea_obj, sch_obj):
280 """展示某讲师任教的某校区的上课记录"""
281 #找出讲师任教的某校区所任课的班级-课程
282 c2c_obj_li = select_tea_sch_c2c(session, tea_obj, sch_obj)
283 if len(c2c_obj_li)==0:
284 print(Colors('讲师%s任教的校区%s还没有任课的班级-课程'%(tea_obj.name, sch_obj.name), 'red'))
285 return
286 #循环找出c2c的上课记录
287 c2c_les_obj_li = []
288 c2c_les_obj_dic = {}
289 for c2c_obj in c2c_obj_li:
290 # c2c_les_obj_li.extend(c2c_obj.c2c_m2m_lessons)
291 c2c_les_obj_li.extend(session.query(C2c_m2m_lesson).filter(C2c_m2m_lesson.course_m2m_class_id==c2c_obj.id).all())
292 if len(c2c_les_obj_li)==0:
293 print(Colors('讲师%s任教的校区%s还没有上课记录'%(tea_obj.name, sch_obj.name), 'red'))
294 return
295 for c2c_les_obj in c2c_les_obj_li:
296 if not c2c_les_obj_dic.get(c2c_les_obj.c2c.glass.name):
297 c2c_les_obj_dic[c2c_les_obj.c2c.glass.name] = [c2c_les_obj,]
298 else:
299 c2c_les_obj_dic[c2c_les_obj.c2c.glass.name].append(c2c_les_obj)
300 print('讲师%s任教的校区%s的上课记录信息如下：'%(tea_obj.name, sch_obj.name))
301 for cls_name in c2c_les_obj_dic:
302 print('\t班级[%s]:'%cls_name)
303 for c2c_les_obj in c2c_les_obj_dic[cls_name]:
304 print('\t\t%s'%c2c_les_obj)
305 return True
306
307 def display_tea_sch_studyRecords(session, tea_obj, sch_obj):
308 """展示某讲师任教的某校区任课的班级-课程的所有学习记录"""
309 #找出讲师任教的某校区任课的班级-课程的所有学习记录
310 stu_rec_obj_li = select_tea_sch_stuRec(session, tea_obj, sch_obj)
311 stu_rec_obj_dic = {}
312 if len(stu_rec_obj_li)==0:
313 print(Colors('讲师%s任教的校区%s还没有上课记录'%(tea_obj.name, sch_obj.name), 'red'))
314 return
315 for stu_rec_obj in stu_rec_obj_li:
316 if not stu_rec_obj_dic.get(stu_rec_obj.c2c_m2m_lesson.c2c.glass.name):
317 stu_rec_obj_dic[stu_rec_obj.c2c_m2m_lesson.c2c.glass.name] = [stu_rec_obj,]
318 else:
319 stu_rec_obj_dic[stu_rec_obj.c2c_m2m_lesson.c2c.glass.name].append(stu_rec_obj)
320 print('讲师%s任教的校区%s的学员学习记录如下：'%(tea_obj.name, sch_obj.name))
321 for cls_name in stu_rec_obj_dic:
322 print('\t班级%s:'%cls_name)
323 for stu_rec_obj in stu_rec_obj_dic[cls_name]:
324 print('\t\t%s'%stu_rec_obj)
325 return True
326
327 def display_stu_sch_studyRecords(session, stu_obj, sch_obj):
328 """展示某学员注册的某校区的学习记录"""
329 #找出学员注册的某校区报名班级的所有学习记录
330 stu_rec_obj_li = select_stu_sch_stuRec(session, stu_obj, sch_obj)
331 stu_rec_obj_dic = {}
332 if len(stu_rec_obj_li)==0:
333 print(Colors('学员%s注册的校区%s还没有学习记录'%(stu_obj.name, sch_obj.name), 'red'))
334 return
335 for stu_rec_obj in stu_rec_obj_li:
336 if not stu_rec_obj_dic.get(stu_rec_obj.c2c_m2m_lesson.c2c.glass.name):
337 stu_rec_obj_dic[stu_rec_obj.c2c_m2m_lesson.c2c.glass.name] = [stu_rec_obj,]
338 else:
339 stu_rec_obj_dic[stu_rec_obj.c2c_m2m_lesson.c2c.glass.name].append(stu_rec_obj)
340 print('学员%s注册的校区%s的学习记录如下：'%(stu_obj.name, sch_obj.name))
341 for cls_name in stu_rec_obj_dic:
342 print('\t班级%s:'%cls_name)
343 for stu_rec_obj in stu_rec_obj_dic[cls_name]:
344 print('\t\t%s'%stu_rec_obj)
345 return True
346
347 def display_stu_sch_cls_score(session, stu_obj, sch_obj):
348 """展示某学员注册的某学校下报名的班级的成绩排名"""
349 #找出学员注册的某校区报名班级的所有学习记录
350 stu_rec_obj_li = select_stu_sch_stuRec(session, stu_obj, sch_obj)
351 stu_rec_obj_dic = {}
352 if len(stu_rec_obj_li)==0:
353 print(Colors('学员%s注册的校区%s还没有学习记录'%(stu_obj.name, sch_obj.name), 'red'))
354 return
355
356 for stu_rec_obj in stu_rec_obj_li:
357 if not stu_rec_obj_dic.get(stu_rec_obj.c2c_m2m_lesson.c2c.glass.name):
358 stu_rec_obj_dic[stu_rec_obj.c2c_m2m_lesson.c2c.glass.name] = [stu_rec_obj,]
359 else:
360 stu_rec_obj_dic[stu_rec_obj.c2c_m2m_lesson.c2c.glass.name].append(stu_rec_obj)
361 print('学员%s注册的校区%s的学习记录如下：'%(stu_obj.name, sch_obj.name))
362 for cls_name in stu_rec_obj_dic:
363 print('\t班级%s:'%cls_name)
364 obj_li = session.query(Student.qq, func.sum(Study_record.score)).join(Study_record).join(C2c_m2m_lesson).join(Course_m2m_class).join(Class).\
365 filter(Class.name==cls_name).filter(Class.school_id==sch_obj.id).group_by(Study_record.student_id).order_by(func.sum(Study_record.score)).all()
366 print(obj_li)
367 sort_stu_qq_li = []
368 for obj in obj_li:
369 sort_stu_qq_li.append(obj[0])
370 print(sort_stu_qq_li)
371 print('当前班级总成绩排名%s'%(len(sort_stu_qq_li)-sort_stu_qq_li.index(stu_obj.qq)))
372 # for stu_rec_obj in stu_rec_obj_dic[cls_name]:
373 # print('\t\t%s'%stu_rec_obj)
374 return True
375
376 def select_tea_sch_c2c(session, tea_obj, sch_obj):
377 """找出讲师任教的某一校区所任课的班级-课程"""
378 tmp_c2c_obj_li = []
379 c2c_obj_li = []
380 cls_obj_li = session.query(Class).filter(Class.school_id==sch_obj.id).all()
381 for cls_obj in cls_obj_li:
382 tmp_c2c_obj_li.extend(session.query(Course_m2m_class).filter(Course_m2m_class.class_id==cls_obj.id).all())
383 for c2c_obj in tmp_c2c_obj_li:
384 if c2c_obj.teacher_id == tea_obj.id:
385 c2c_obj_li.append(c2c_obj)
386 return c2c_obj_li
387
388 def select_tea_sch_stuRec(session, tea_obj, sch_obj):
389 """找出讲师任教的某校区任课的班级-课程的所有学习记录"""
390 #找出讲师任教的某校区所任课的班级-课程
391 c2c_obj_li = select_tea_sch_c2c(session, tea_obj, sch_obj)
392 if len(c2c_obj_li)==0:
393 print(Colors('讲师%s任教的校区%s还没有任课的班级-课程'%(tea_obj.name, sch_obj.name), 'red'))
394 return
395 #循环找出c2c的上课记录
396 c2c_les_obj_li = []
397 stu_rec_obj_li = []
398 for c2c_obj in c2c_obj_li:
399 c2c_les_obj_li.extend(session.query(C2c_m2m_lesson).filter(C2c_m2m_lesson.course_m2m_class_id==c2c_obj.id).all())
400 for c2c_les_obj in c2c_les_obj_li:
401 stu_rec_obj_li.extend(session.query(Study_record).filter(Study_record.c2c_m2m_lesson_id==c2c_les_obj.id).all())
402 return stu_rec_obj_li
403
404 def select_stu_sch_stuRec(session, stu_obj, sch_obj):
405 """找出学员注册的某校区报名的班级的所有学习记录"""
406 #找出学员注册的某校区报名的班级
407 cls_obj_li = session.query(Class).join(student_m2m_class).join(Student).filter(Student.id==stu_obj.id).filter(Class.school_id==sch_obj.id).all()
408 if len(cls_obj_li)==0:
409 print(Colors('学员%s注册的校区%s还没有报名班级'%(stu_obj.name, sch_obj.name), 'red'))
410 return
411 #循环找出该班级下该学员的学习记录
412 stu_rec_obj_li = []
413 for cls_obj in cls_obj_li:
414 stu_rec_obj_li.extend(session.query(Study_record).join(C2c_m2m_lesson).join(Course_m2m_class).\
415 filter(Course_m2m_class.class_id==cls_obj.id).filter(Study_record.student_id==stu_obj.id).all())
416 return stu_rec_obj_li
View Code
create_table.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Table, ForeignKey, Enum, PrimaryKeyConstraint, UniqueConstraint
from sqlalchemy.orm import relationship
# 指定字符集、最大连接池数
engine = create_engine("mysql+pymysql://root:123456@10.0.0.15/studentdb?charset=utf8", max_overflow=5)
Base = declarative_base()
school_m2m_course = Table('school_m2m_course', Base.metadata,
Column('school_id', Integer, ForeignKey('school.id'), nullable=False),
Column('course_id', Integer, ForeignKey('course.id'), nullable=False),
UniqueConstraint('school_id', 'course_id', name='sch_id_cou_id_uk'),
)
school_m2m_teacher = Table('school_m2m_teacher', Base.metadata,
Column('school_id', Integer, ForeignKey('school.id'), nullable=False),
Column('teacher_id', Integer, ForeignKey('teacher.id'), nullable=False),
UniqueConstraint('school_id', 'teacher_id', name='sch_id_tea_id_uk'),
)
school_m2m_student = Table('school_m2m_student', Base.metadata,
Column('school_id', Integer, ForeignKey('school.id'), nullable=False),
Column('student_id', Integer, ForeignKey('student.id'), nullable=False),
UniqueConstraint('school_id', 'student_id', name='sch_id_stu_id_uk'),
)
# course_m2m_class = Table('course_m2m_class', Base.metadata,
# Column('id', Integer, primary_key=True, autoincrement=True),
# Column('course_id', Integer, ForeignKey('course.id')),
# Column('class_id', Integer, ForeignKey('class.id')),
# Column('teacher_id', Integer, ForeignKey('teacher.id')),
# UniqueConstraint('course_id', 'class_id', name='cls_id_cou_id_uk'),
# )
student_m2m_class = Table('student_m2m_class', Base.metadata,
Column('student_id', Integer, ForeignKey('student.id'), nullable=False),
Column('class_id', Integer, ForeignKey('class.id'), nullable=False),
UniqueConstraint('student_id', 'class_id', name='stu_id_cls_id_uk'),
)
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(32), nullable=False, unique=True)
password = Column(String(64), nullable=False)
permission = Column(Integer, nullable=False)
def __repr__(self):
return "用户名:%s 权限:%s"%(self.username, self.permission)
class School(Base):
__tablename__ = 'school'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False, unique=True)
address = Column(String(32), nullable=False)
courses = relationship('Course', secondary=school_m2m_course, backref='schools')
classes = relationship('Class', backref='school')
teachers = relationship('Teacher', secondary=school_m2m_teacher, backref='schools')
students = relationship('Student', secondary=school_m2m_student, backref='schools')
def __repr__(self):
return "校区名:%s"%self.name
class Course_m2m_class(Base):
__tablename__ = 'course_m2m_class'
id = Column(Integer, primary_key=True, autoincrement=True)
course_id = Column(Integer, ForeignKey('course.id'), nullable=False)
class_id = Column(Integer, ForeignKey('class.id'), nullable=False)
teacher_id = Column(Integer, ForeignKey('teacher.id'), )
__table_args__ = (
UniqueConstraint('course_id', 'class_id', name='cls_id_cou_id_uk'), # 唯一索引
)
teacher = relationship('Teacher', backref='course_m2m_classes')
glass = relationship('Class', backref='course_m2m_classes')
course = relationship('Course', backref='course_m2m_classes')
def __repr__(self):
return "班级名:%s 课程名:%s"%(self.glass.name, self.course.name)
class Course(Base):
__tablename__ = 'course'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False, unique=True)
price = Column(Integer, nullable=False)
classes = relationship('Class', secondary=Course_m2m_class.__table__, backref='courses')
def __repr__(self):
return "课程名:%s"%self.name
class Class(Base):
__tablename__ = 'class'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False)
school_id = Column(Integer, ForeignKey('school.id'), nullable=False)
__table_args__ = (
UniqueConstraint('name', 'school_id', name='name_school_id_uk'), # 唯一索引
)
def __repr__(self):
return "班级名:%s"%self.name
class Teacher(Base):
__tablename__ = 'teacher'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False)
card_id = Column(String(32), nullable=False, unique=True)
user_id = Column(Integer, ForeignKey('user.id'), unique=True, nullable=False)
user = relationship('User', backref='teacher')
def __repr__(self):
return "讲师名:%s 身份证号:%s 用户名:%s"%(self.name, self.card_id, self.user.username)
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False)
qq = Column(String(32), nullable=False, unique=True)
user_id = Column(Integer, ForeignKey('user.id'), unique=True, nullable=False)
classes = relationship('Class', secondary=student_m2m_class, backref='students')
user = relationship('User', backref='student')
def __repr__(self):
return "学员名:%s QQ号:%s 用户名:%s"%(self.name, self.qq, self.user.username)
class Lesson(Base):
__tablename__ = 'lesson'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), nullable=False, unique=True)
class C2c_m2m_lesson(Base):
__tablename__ = 'c2c_m2m_lesson'
id = Column(Integer, primary_key=True, autoincrement=True)
course_m2m_class_id = Column(Integer, ForeignKey('course_m2m_class.id'), nullable=False)
lesson_id = Column(Integer, ForeignKey('lesson.id'), nullable=False)
__table_args__ = (
UniqueConstraint('course_m2m_class_id', 'lesson_id', name='c2c_id_les_id_uk'), # 唯一索引
)
c2c = relationship('Course_m2m_class', backref='c2c_m2m_lessons')
lesson = relationship('Lesson', backref='c2c_m2m_lessons')
def __repr__(self):
return "%s %s"%(self.c2c, self.lesson.name)
class Study_record(Base):
__tablename__ = 'study_record'
id = Column(Integer, primary_key=True, autoincrement=True)
status = Column(Enum('no','yes'), nullable=False, default='no')
score = Column(String(32), nullable=False, default='未评分')
c2c_m2m_lesson_id = Column(Integer, ForeignKey('c2c_m2m_lesson.id'), nullable=False)
student_id = Column(Integer, ForeignKey('student.id'), nullable=False)
__table_args__ = (
UniqueConstraint('c2c_m2m_lesson_id', 'student_id', name='c2c_m2m_les_id_stu_id_uk'), # 唯一索引
)
c2c_m2m_lesson = relationship('C2c_m2m_lesson', backref='study_records')
student = relationship('Student', backref='study_records')
def __repr__(self):
return "学习记录ID:%s 签到状态:%s 成绩:%s 上课记录:%s 学员:%s"%(self.id, self.status, self.score, self.c2c_m2m_lesson, self.student.name)
def create_table():
Base.metadata.create_all(engine)
if __name__ == '__main__':
create_table()
|
浙公网安备 33010602011771号