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