def add_student(request): if request.method == 'GET': sql = 'select id,title from class' class_list = sqlheper.get_list(sql, []) return render(request, 'add_student.html', { 'class_list': class_list, }) else: name = request.POST.get('name') if len(name) == 0: sql = 'select id,title from class' class_list = sqlheper.get_list(sql, []) return render(request, 'add_student.html', { 'class_list': class_list, 'msg': 'Your submit is None!' }) else: class_id = request.POST.get('class_id') sql2 = 'insert into student(name, class_id) values(%s,%s)' sqlheper.modify(sql2, [ name, class_id, ]) return redirect('/students/')
def edit_student(request): """ 编辑学生信息 """ if request.method == "GET": nid = request.GET.get('nid') sql = "select * from student where id=%s" result = sqlheper.get_list(sql, [nid, ]) class_list = sqlheper.get_list("select id,title from class", []) major = sqlheper.get_list('select major_name from major', []) return render(request, 'edit_student.html', {'result': result[0], 'class_list': class_list, 'major': major}) else: ret = {'status': True, 'message': None} try: s = StudentList() s.edit_check(request.POST) stu_dict = list(request.POST.values()) sql = "update student set name=%s,sex=%s,nation=%s,native_place=%s,birth_date=%s,telephone=%s,major_name=%s,class_id=%s where id=%s" operationlogs.operationlogs(request.COOKIES.get('name'), 'edit', 'student', stu_dict[-1], json.dumps(request.POST)) sqlheper.modify(sql, stu_dict) except Exception as e: ret['status'] = False ret['message'] = str(e) return HttpResponse(json.dumps(ret))
def score_dis(request): """获取教师所教学生的成绩分布""" nid = 't1' # 获取教师所授课程编号 course_number = sqlheper.get_list('select course_number from course where teacher_number=%s', [nid, ]) cn = [] for i in course_number: for key, value in i.items(): cn.append(value) # 在学生选课表中查找选择该课程的学生的成绩 student_mark = sqlheper.get_list('select mark from stu_c where course_number=%s', [cn, ]) mark_a, mark_b, mark_c, mark_d, mark_f = [], [], [], [], [] for i in student_mark: for key, values in i.items(): values = int(values) if values >= 90: mark_a.append(values) elif 80 <= values < 90: mark_b.append(values) elif 70 <= values < 80: mark_c.append(values) elif 60 <= values < 70: mark_d.append(values) else: mark_f.append(values) mark = [len(mark_a), len(mark_b), len(mark_c), len(mark_d), len(mark_d)] bar = Bar(init_opts=opts.InitOpts(width='800px', height='500px', theme=ThemeType.LIGHT)) bar.add_xaxis(['优秀', '良好', '中等', '及格', '不及格']) bar.add_yaxis('学生成绩', mark) return render(request, 'student_mark_ana.html', {'bar': bar.render_embed()})
def students(request): sql = 'select student.id,student.name, student.class_id, class.title from student left join class on student.class_id = class.id' student_list = sqlheper.get_list(sql, []) sql2 = 'select id,title from class' class_list = sqlheper.get_list(sql2, []) return render(request, 'students.html', { 'student_list': student_list, 'class_list': class_list, })
def top(request): stu_num = len(sqlheper.get_list('select * from student', [])) cou_num = len(sqlheper.get_list('select * from course', [])) tea_num = len(sqlheper.get_list('select * from teacher', [])) log = sqlheper.get_list('select * from operationlogs', []) user_name = request.COOKIES.get('name') return render( request, 'home_page.html', { 'user_name': user_name, 'log': log, 'stu_num': stu_num, 'cou_num': cou_num, 'tea_num': tea_num, })
def students(request): """ 学生表 :param request: :return: """ conn = pymysql.connect(host="127.0.0.1", port=3306, user="******", passwd="123456", db="oldboy", charset="utf8") cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = "select student.id,student.sname,student.class_id,class.name from student left join class on student.class_id = class.id;" cursor.execute(sql) student_list = cursor.fetchall() cursor.close() conn.close() class_list = sqlheper.get_list("select id ,name from class;", []) return render(request, "students.html", { "student_list": student_list, "class_list": class_list })
def edit_course(request): """ 编辑课程信息 """ nid = request.GET.get('nid') if request.method == "GET": c = CourseList() sql = "select * from course where id=%s" result = sqlheper.get_list(sql, [nid, ]) return render(request, 'edit_course.html', {'result': result[0], 'teacher_list': c.teacher_list}) else: ret = {'status': True, 'message': None} try: c = CourseList() c.edit_check(request.POST) edit_list = list(request.POST.values()) sql = "update course set course_name=%s,credit=%s,teacher_number=%s where id=%s" operationlogs.operationlogs(request.COOKIES.get('name'), 'edit', 'course', edit_list[-1], json.dumps(request.POST)) sqlheper.modify(sql, edit_list) except Exception as e: ret['status'] = False ret['message'] = str(e) print(e.__traceback__.tb_lineno) return HttpResponse(json.dumps(ret))
def __init__(self, student_list=None, start=0, end=10000): if student_list is None: student_list = [] self.student_list = student_list self.student_list_page = self.student_list[start: end] self.class_list = sqlheper.get_list("select id,title from class", []) self.all_count = len(student_list)
def operationlogs(operator, operation, target_table, target_id=None, modify_value=None): """ 操作日志 :param operator: 操作员 :param operation: 操作 :param target_table: 对象表 :param target_id: 对象id :param modify_value: 修改值 """ time1 = datetime.datetime.now() time = datetime.datetime.strftime(time1, '%Y-%m-%d %H:%M:%S') if operation == 'edit' and modify_value not in ['开启选课', '关闭选课']: # 获取实际更新的数据 # 获得修改前的数据 old = sqlheper.get_list(f'select * from {target_table} where id={target_id}', [])[0] if target_table == 'student': old.pop('id') old.pop('student_id') elif target_table == 'course': old.pop('id') old.pop('course_number') old.pop('status') new = json.loads(modify_value) new.pop('nid') modify_value = {} for key, value in old.items(): if new[key] != str(value): modify_value[key] = new[key] sqlheper.modify( 'insert into operationlogs(operator, operation, target_table, target_id, modify_value,time) values(%s,%s,%s,%s,%s,%s)', [operator, operation, target_table, target_id, str(modify_value), time] )
def add_student(request): """ 添加学生 """ ret = {'status': True, 'message': None} try: name = request.POST.get('name') s = StudentList() s.add_check(request.POST) # 校验 print(request.POST) add_list = list(request.POST.values()) # 待添加的数据 sqlheper.modify("insert into student(name,sex,major_name,class_id) values(%s,%s,%s,%s)", add_list) # 自动生成学号 sid = sqlheper.get_list("select id from student where name=%s order by id DESC", [name, ]) sid = sid[0].get('id') year = ['2017', '2018', '2019', '2020'] # 年份 job = ['320', '321', '322'] # 工号 student_id = str(sid).zfill(3) # 序号,三位数 student_id = year[-1] + job[2] + student_id sqlheper.modify("update student set student_id=%s where id=%s", [student_id, sid]) operationlogs.operationlogs(request.COOKIES.get('name'), 'add', 'student') except Exception as e: # 添加失败返回错误信息 ret['status'] = False ret['message'] = str(e) return HttpResponse(json.dumps(ret))
def edit_student(request): if request.method == 'GET': nid = request.GET.get('nid') sql1 = 'select id, title from class' class_list = sqlheper.get_list(sql1, []) sql2 = 'select id, name, class_id from student where id=%s' current_student_info = sqlheper.get_one(sql2, [ nid, ]) return render( request, 'edit_student.html', { 'class_list': class_list, 'current_student_info': current_student_info, }) else: nid = request.GET.get('nid') name = request.POST.get('name') class_id = request.POST.get('class_id') print(name, class_id, nid) sql3 = 'update student set name=%s, class_id=%s where id=%s' sqlheper.modify(sql3, [ name, class_id, nid, ]) return redirect('/students/')
def student(request): """ 学生管理首页 """ # 检验用户是否登录 tk = request.COOKIES.get('ticket') if not tk: return redirect('/sign/') # 获得url url = '/student/?' if request.GET.get('per_page'): url = '/student/?per_page=' + request.GET.get('per_page') + '&' # 展示学生信息 if request.GET.get('sort') == "desc": # 倒排 url = "/student/?sort=desc&" slist = sqlheper.get_list( "select student.id,name,student_id,sex,telephone,major_name,class.title from student left join class on class.id=class_id order by student.id DESC", []) else: slist = sqlheper.get_list( "select student.id,name,student_id,sex,telephone,major_name,class.title from student left join class on class.id=class_id order by student.id", []) student_number = len(sqlheper.get_list("select * from student", [])) all_count = student_number # 学生表数据条数 current_page = 1 if request.GET.get('page'): current_page = int(request.GET.get('page')) # 当前页码 per_page = 10 # 默认每页数据条数 if request.GET.get('per_page'): per_page = int(request.GET.get('per_page')) # 新建一个分页器 page_info = pager.PageInfo(current_page, all_count, per_page, url) # 将学生表切片,只展示当前页所需学生信息 start, end = page_info.start(), page_info.end() student_list_obj = StudentList(slist, start, end) major = sqlheper.get_list('select major_name from major', []) return render(request, 'student.html', {'student_list_obj': student_list_obj, 'per_page': per_page, 'page_info': page_info, 'class_list': json.dumps(student_list_obj.class_list), 'major': major, 'url': request.get_full_path() })
def course(request): """ 课程管理首页 """ # 检验用户是否登录 tk = request.COOKIES.get('ticket') if not tk: return redirect('/sign/') # 获得url url = '/course/?' if request.GET.get('per_page'): url = '/course/?per_page=' + request.GET.get('per_page') + '&' # 展示课程信息 if request.GET.get('sort') == "desc": # 倒排 url = "/course/?sort=desc&" clist = sqlheper.get_list( "select course.id,course_name,teacher.name,credit,status from course left join teacher on course.teacher_number=teacher.teacher_number order by course.id DESC", []) else: # 正序 clist = sqlheper.get_list( "select course.id,course_name,teacher.name,credit,status from course left join teacher on course.teacher_number=teacher.teacher_number order by course.id", []) course_number = len(sqlheper.get_list("select * from course", [])) all_count = course_number # 课程表数据条数 current_page = 1 # 默认当前页 if request.GET.get('page'): current_page = int(request.GET.get('page')) # 当前页码 per_page = 10 # 默认每页数据条数 if request.GET.get('per_page'): per_page = int(request.GET.get('per_page')) # 新建一个分页器 page_info = pager.PageInfo(current_page, all_count, per_page, url) # 将学生表切片,只展示当前页所需学生信息 start, end = page_info.start(), page_info.end() course_list_obj = CourseList(clist, start, end) return render(request, 'course.html', {'course_list_obj': course_list_obj, 'per_page': per_page, 'page_info': page_info, 'teacher_list': json.dumps(course_list_obj.teacher_list), 'url': request.get_full_path() })
def search_course(request): """ 查询课程信息 """ if request.method == 'POST': search = request.POST.get('search') search_list = sqlheper.get_list( "select course.id,course_name,teacher.name,credit,status from course left join teacher on course.teacher_number=teacher.teacher_number where course_name like '%%%%%s%%%%'" % ( search), []) course_list_obj = CourseList(search_list) return render(request, 'course.html', {'course_list_obj': course_list_obj})
def search_student(request): """ 查询学生信息 """ if request.method == 'POST': search = request.POST.get('search') # 校验输入信息为学号或是姓名 if re.match(r'^\d+', search): # 学号 search_list = sqlheper.get_list( "select student.id,name,student_id,sex,telephone,class.title from student left join class on class.id=class_id where student_id like '%%%%%s%%%%'" % ( search), []) elif re.match('[\u4E00-\u9FA5]+', search): # 姓名 search_list = sqlheper.get_list( "select student.id,name,student_id,sex,telephone,class.title from student left join class on class.id=class_id where name like '%%%%%s%%%%'" % ( search), []) student_list_obj = StudentList(search_list) return render(request, 'student.html', {'student_list_obj': student_list_obj})
def edit_student(request): if request.method == "GET": nid = request.GET.get('nid') class_list = sqlheper.get_list("select id,class_name from t_class", []) print(class_list) current_sutdent_info = sqlheper.get_one("select id,student_name,class_id from t_student where id = %s", [nid, ]) print(current_sutdent_info) return render(request, 'edit_studemt.html', {'class_list': class_list, 'current_sutdent_info': current_sutdent_info}) else: nid = request.GET.get('nid') student_name = request.POST.get('student_name') sqlheper.modify("update t_student set student_name=%s where id = %s", [student_name, nid, ]) return redirect('/students/')
def add_student(request): if request.method == 'GET': class_list = sqlheper.get_list('select id,class_name from t_class', []) return render(request, 'add_student.html', {'class_list': class_list}) else: print(request.POST) c_id = request.POST.get('class_id') value = request.POST.get('student_name') if len(value) > 0: sqlheper.modify("insert into t_student(student_name,class_id) values(%s,%s)", [value, c_id,]) return redirect('/students/') else: return render(request, 'add_student.html', {'msg': '学生名称不能为空'})
def teachers(request): teacher_list = sqlheper.get_list("""SELECT t_teacher.id as tid,t_teacher.teacher_name as tname, t_class.class_name as cname FROM t_teacher LEFT JOIN t_t_c ON t_teacher.id = t_t_c.t_id LEFT JOIN t_class ON t_class.id = t_t_c.c_id""", []) print(teacher_list) result = {} for row in teacher_list: tid = row['tid'] if tid in result: result[tid]['cnames'].append(row['cname']) else: result[tid] = {'tid': row['tid'], 'tname': row['tname'], 'cnames': [row['cname'], ]} print(result.values()) return render(request, 'teacher.html', {'teacher_list': result.values()})
def classes(request): # 去请求的cookie中找凭证 # tk = request.COOKIES.get('ticket') tk = request.get_signed_cookie( 'ticket', salt='ttt', ) print(tk) if tk != 'qwertyuiop': return redirect('/login/') sql1 = 'select id,title from class' class_list = sqlheper.get_list(sql1, []) return render(request, 'classes.html', {'class_list': class_list})
def teachers(request): teacher_list = sqlheper.get_list( """SELECT teacher.id as tid, teacher.name, class.title from teacher LEFT JOIN teacher2class ON teacher.id=teacher2class.teacher_id LEFT JOIN class ON class.id=teacher2class.class_id""", []) print(teacher_list) result = {} for row in teacher_list: tid = row['tid'] if tid in result: result[tid]['titles'].append(row['title']) else: result[tid] = { 'tid': row['tid'], 'name': row['name'], 'titles': [row['title']] } print(result) class_list = sqlheper.get_list("select id,title from class", []) # for row in result.values(): # row['titles']=','.join(row['titles']) return render(request, 'teachers.html', { 'teachers_list': result.values(), 'class_list': class_list })
def students(request): conn = pymysql.connect(host='127.0.0.1', user='******', password='******', database='Studentdb', charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute( "select student.id,student.name,class.title,student.class_id from student LEFT JOIN class ON class.id=student.class_id" ) students_list = cursor.fetchall() cursor.close() conn.close() class_list = sqlheper.get_list("select id,title from class", []) print(class_list) return render(request, 'students.html', { 'students_list': students_list, 'class_list': class_list })
def add_teacher(request): if request.method == 'GET': sql = 'select id, title from class' class_list = sqlheper.get_list(sql, []) return render( request, 'add_teacher.html', {'class_list': class_list}, ) else: name = request.POST.get('name') class_ids = request.POST.getlist('class_ids') sql2 = 'insert into teacher(name) values(%s)' teacher_id = sqlheper.create(sql2, [ name, ]) sql3 = 'insert into teacher2class(teacher_id, class_id) values(%s, %s)' # 多次连接多次提交 # for item in class_ids: # sqlheper.modify(sql3, [teacher_id, item, ]) # 一次连接多次提交 # obj = sqlheper.SqlHelper() # for cls_id in class_ids: # obj.modify(sql3, [teacher_id, cls_id, ]) # obj.close() # 一次连接一次提交 data_list = [] for cls_id in class_ids: temp = ( teacher_id, cls_id, ) data_list.append(temp) obj = sqlhelper.SqlHelper() obj.multiple_modify(sql3, data_list) obj.close() return redirect('/teachers/')
def teachers(request): teacher_list = sqlheper.get_list( "select teacher.id as tid,teacher.name,class.name as cname from teacher left join teacher2class on teacher.id=teacher2class.teacher_id left join class on class.id = teacher2class.class_id;", []) result = {} # print(teacher_list) for row in teacher_list: tid = row["tid"] if tid in result: result[tid]["cnames"].append(row["cname"]) else: result[tid] = { "tid": row["tid"], "name": row["name"], "cnames": [ row["cname"], ] } return render(request, "teachers.html", {"teacher_list": result.values()})
def add_course(request): """ 添加课程 """ ret = {'status': True, 'message': None} try: cname = request.POST.get('course_name') c = CourseList() c.add_check(request.POST) # 校验 add_list = list(request.POST.values()) # 待添加的数据 sqlheper.modify("insert into course(course_name,teacher_number,credit) values(%s,%s,%s)", add_list) cid = sqlheper.get_list("select id from course where course_name=%s order by id", [cname, ]) cid = cid[0].get('id') course_number = 'c' + str(cid) sqlheper.modify("update course set course_number=%s where id=%s", [course_number, cid]) operationlogs.operationlogs(request.COOKIES.get('name'), 'add', 'course') except Exception as e: # 添加失败返回错误信息 ret['status'] = False ret['message'] = str(e) return HttpResponse(json.dumps(ret))
def edit_students(request): if request.method == "GET": nid = request.GET.get('nid') class_list = sqlheper.get_list("select id,title from login_classtable", []) current_student_info = sqlheper.get_one( 'select id, name, class_id_id from login_student where id=%s', [ nid, ]) return render(request, "edit_students.html", { 'class_list': class_list, 'current_student_info': current_student_info }) else: nid = request.GET.get('nid') name = request.POST.get('name') class_id = request.POST.get('class_id') sqlheper.modify( 'update login_student set name=%s, class_id_id=%s where id=%s', [name, class_id, nid]) return redirect('/students/')
def edit_student(request): from utils import sqlheper if request.method == "GET": nid = request.GET.get('nid') class_list = sqlheper.get_list("select id,title from class", []) student_list = sqlheper.get_one( "select id,name,class_id from student where id=%s", [ nid, ]) return render(request, 'edit_student.html', { 'class_list': class_list, 'student_list': student_list }) else: name = request.POST.get("name") nid = request.GET.get('nid') cid = request.POST.get('class_id') sqlheper.modify("update student set name=%s,class_id=%s where id=%s", [name, cid, nid]) return redirect('/students')
def sign(request): """ 登录函数 """ if request.method == "GET": return render(request, 'sign.html') else: name = request.POST.get('user_name') password = request.POST.get('password') exist_a = sqlheper.get_list( "select password from user_admin where name=%s and password=%s", [name, password]) if exist_a: obj = redirect('/top/') obj.set_cookie('ticket', '123') obj.set_cookie('name', name) obj.set_cookie('password', password) else: toast(request) return render(request, 'sign.html') return obj
def add_teacher(request): if request.method == "GET": class_list = sqlheper.get_list('select id,name from class', []) return render(request, 'add_teacher.html', {'class_list': class_list}) else: name = request.POST.get('name') teacher_id = sqlheper.create('insert into teacher(name) values(%s)', [ name, ]) class_ids = request.POST.getlist('class_ids') print(class_ids) data_list = [] for cls_id in class_ids: temp = (teacher_id, cls_id) data_list.append(temp) obj = sqlheper.SqlHelper() obj.multiple_modify( "insert into teacher2class (teacher_id,class_id) values (%s,%s)", data_list) obj.close() return redirect("/teachers/")
def students(request): conn = pymysql.connect(host='127.0.0.1', port=3306, user='******', password='******', db="data1", charset="utf8") # 连接database cursor = conn.cursor( cursor=pymysql.cursors.DictCursor) # 得到一个可以执行SQL语句的光标对象 #从数据库查询班级和学生的信息,注意如何关联 cursor.execute( "select myapp_student.id,myapp_student.stu_name,myapp_student.class_id_id,myapp_class.title from myapp_student left JOIN myapp_class on myapp_student.class_id_id = myapp_class.id" ) # 执行SQL语句,更新数据 student_list = cursor.fetchall() cursor.close() # 关闭光标对象 conn.close() class_list = sqlheper.get_list('select id,title from myapp_class', []) return render(request, 'myApp/students.html', { "student_list": student_list, 'class_list': class_list })
def teachers(request): # sql = 'select id, name from teacher' sql = """ SELECT teacher.id as tid, teacher.name, class.title FROM teacher left join teacher2class on teacher.id = teacher2class.teacher_id left join class on class.id = teacher2class.class_id; """ teacher_list = sqlheper.get_list(sql, []) result = {} for row in teacher_list: tid = row['tid'] if tid in result: result[tid]['titles'].append(row['title']) else: result[tid] = { 'tid': row['tid'], 'name': row['name'], 'titles': [ row['title'], ] } return render(request, 'teacher.html', {'teacher_list': result.values})