def edit_student(request):
if request.method == 'GET':
class_list = sqlhelper.get_list('SELECT * FROM class;')
student_id = request.GET.get("nid")
student = sqlhelper.get_list(
'SELECT * FROM student WHERE id=%s LIMIT 1;', [
student_id,
])[0]
class_name = sqlhelper.get_list(
'SELECT * FROM class WHERE id=%s LIMIT 1;', [
student[2],
])[0][1]
return render(
request, "edit_student.html", {
'student': student,
'class_name': class_name,
'class_list': class_list,
'class_name': class_name,
})
if request.method == 'POST':
student_id = request.GET.get("nid")
student_name = request.POST.get("student_name")
class_id = request.POST.get("class_id")
if sqlhelper.modify(
'UPDATE student SET name=%s,class_id=%s WHERE id =%s;', [
student_name,
class_id,
student_id,
]):
return redirect('/student/')
else:
return redirect('/edit_student/?nid=' + student_id)
def add_student(request):
ret = {
'status': True,
'message': None,
}
name = request.POST.get("name")
class_name = request.POST.get("class")
if name == '':
ret['status'] = False
ret['message'] = 'None type'
return HttpResponse(json.dumps(ret))
stu_num = sqlhelper.get_list(
'SELECT id FROM student ORDER BY id DESC LIMIT 1;')
if stu_num == []:
stu_num = 1
else:
stu_num = stu_num[0][0] + 1
try:
class_id = sqlhelper.get_list(
'SELECT id FROM class WHERE title=%s LIMIT 1;', [class_name])[0][0]
sqlhelper.modify(
'INSERT INTO student (id,name,class_id) VALUES (%s,%s,%s);', [
stu_num,
name,
class_id,
])
except Exception as e:
ret['status'] = False
ret['message'] = str(e)
return HttpResponse(json.dumps(ret))
def student(request):
student_list = sqlhelper.get_list(
'SELECT s.id,s.name,c.title class,c.id FROM student s LEFT OUTER JOIN class c ON s.class_id =c.id;'
)
class_list = sqlhelper.get_list('SELECT * FROM class;')
return render(request, "student.html", {
'student_list': student_list,
'class_list': class_list,
})
def students(request):
stu_list = sqlhelper.get_list(
"select students.id,students.name,students.cid,class.id, class.title from students left join class on students.cid = class.id",
[])
class_list = sqlhelper.get_list("select * from class", [])
return render(request, 'students.html', {
'stu_list': stu_list,
'class_list': class_list
})
def students(request):
student_list = sqlhelper.get_list(
'select sid,sname,classid,cname from student left join class on student.classid=class.cid',
[])
class_list = sqlhelper.get_list('select cid,cname from class', [])
return render(request, 'students.html', {
'student_list': student_list,
'class_list': class_list
})
def editStudent(request):
if request.method == "GET":
nid = request.GET.get('nid')
result = sqlhelper.get_one(
'select id,stu_name,class_id from student where id=%s', [
nid,
])
class_list = sqlhelper.get_list("select id,title from class", [])
return render(request, 'editStudent.html', {
'result': result,
'class_list': class_list
})
else:
nid = request.GET.get('nid')
id = request.POST.get('id')
name = request.POST.get('stu_name')
class_id = request.POST.get('class_id')
sqlhelper.modify(
"update student set id=%s, stu_name=%s, class_id=%s where id = %s",
[
id,
name,
class_id,
nid,
])
return redirect('/students/')
def teachers(request):
# teas_list = sqlhelper.get_list('select tea_id,tea_name from tea_info',[])
teas_list = sqlhelper.get_list(
"""
SELECT tea_info.tea_id as tea_id,tea_info.tea_name as tea_name,class_info.class_id as class_id,class_info.class_name as class_name from tea_info
LEFT JOIN teatocla on tea_info.tea_id = teatocla.teatocla_tea_id
LEFT JOIN class_info ON class_info.class_id = teatocla.teatocla_class_id
""", [])
result = {}
for row in teas_list:
tea_id = row['tea_id']
if tea_id in result:
result[tea_id]['class_name'].append(row['class_name'])
result[tea_id]['class_id'].append(row['class_id'])
else:
result[tea_id] = {
'tea_id': row['tea_id'],
'tea_name': row['tea_name'],
'class_id': [
row['class_id'],
],
'class_name': [
row['class_name'],
]
}
# print(result)
return render(request, 'teachers.html', {'teas_list': result.values()})
def edit_student(request):
ret = {
'status': True,
'message': None,
}
if request.method == "POST":
stu_id = request.POST.get("stu_id")
new_name = request.POST.get("new_name")
new_class = request.POST.get("new_class")
if new_name == "":
ret['status'] = False
ret['message'] = 'new name is empty'
return HttpResponse(json.dumps(ret))
class_id = sqlhelper.get_list(
'SELECT id FROM class WHERE title=%s LIMIT 1;', [new_class])[0][0]
if sqlhelper.modify(
'UPDATE student SET name=%s ,class_id=%s WHERE id=%s;', [
new_name,
class_id,
stu_id,
]):
return HttpResponse(json.dumps(ret))
ret['status'] = False
ret['message'] = 'sql error'
return HttpResponse(json.dumps(ret))
def add_teacher(request):
if request.method == "GET":
class_list = sqlhelper.get_list('select cid,cname from class', [])
return render(request, 'add_teacher.html', {'class_list': class_list})
else:
tchrname = request.POST.get('tchr_name')
tchrid = sqlhelper.create('insert into teacher(tname) values(%s)', [
tchrname,
])
classids = request.POST.getlist('class_ids')
## multi-connects, multi-submits
#for clsid in classids:
# sqlhelper.modify('insert into teacher2class(teacherid,classid) values(%s,%s)', [tchrid,clsid,])
## one-connect, multi-submits
#sqlobj = sqlhelper.Sqlapi()
#for clsid in classids:
# sqlobj.modify('insert into teacher2class(teacherid,classid) values(%s,%s)', [tchrid,clsid,])
#sqlobj.close()
## one-connect, one-submit
data_list = []
for clsid in classids:
data_list.append((
tchrid,
clsid,
))
sqlobj = sqlhelper.Sqlapi()
sqlobj.multi_modify(
'insert into teacher2class(teacherid,classid) values(%s,%s)',
data_list)
sqlobj.close()
return redirect('/teachers/')
def add_class(request):
'''
页面不刷新,提示错误信息
若用form表单提交，这个功能无法完成
form表单提交，页面必刷新
若想完成此功能并且是对话框的方式就不能用form表单提交
若用ajex提交，提交后无法用renturn 进行跳转，只能通过写js进行跳转
在js中使用location.href完成跳转
'''
class_name = request.POST.get('title')
if len(class_name) > 0:
class_num = sqlhelper.get_list(
'SELECT id FROM class ORDER BY id DESC LIMIT 1;')
if class_num == []:
class_id = 1
else:
class_id = class_num[0][0] + 1
sqlhelper.modify('INSERT IGNORE INTO class (id,title) VALUES (%s,%s);',
[
class_id,
class_name,
])
return HttpResponse('ok')
else:
return HttpResponse('班级不能为空')
def classes(request):
'''
去请求的cookie中获取凭证
tk =request.COOKIES.get("ticket")
if not tk:
return redirect('/login/')
'''
class_list = sqlhelper.get_list("SELECT * FROM class;")
return render(request, 'class.html', {'class_list': class_list})
def students(request):
'''
student's list
:param request: 封装请求相关的所有信息
:return:
'''
# 获取学生列表
students_list = sqlhelper.get_list(
'select stu_info.stu_id,stu_info.stu_name,stu_info.stu_class_id,class_info.class_name FROM stu_info LEFT JOIN class_info ON stu_info.stu_class_id = class_info.class_id',
[])
# 获取班级列表
classes_list = sqlhelper.get_list(
'select class_id,class_name from class_info', [])
return render(request, 'students.html', {
'students_list': students_list,
'classes_list': classes_list
})
def add_student(request):
if request.method == "GET":
class_list = sqlhelper.get_list('SELECT * FROM class;')
return render(request, "add_student.html", {'class_list': class_list})
if request.method == "POST":
class_id = request.POST.get("class_id")
if len(class_id) > 0:
student_name = request.POST.get("student_name")
student_id = sqlhelper.get_list(
'SELECT id FROM student ORDER BY id DESC LIMIT 1;')
if student_id == []:
student_id = 1
else:
student_id = student_id[0][0] + 1
sqlhelper.modify(
'INSERT INTO student (id,name,class_id) VALUES (%s,%s,%s);',
[student_id, student_name, class_id])
return redirect('/student/')
else:
return render(request, "add_student.html")
def classes(request):
# conn = pymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='root1234', db='db_mysite')
#
# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# cursor.execute("select class_id,class_name from classes")
# classes_list = cursor.fetchall()
# cursor.close()
# conn.close()
classes_list = sqlhelper.get_list(
"select class_id,class_name from class_info", [])
return render(request, 'classes.html', {'classes_list': classes_list})
def add_student(request):
if request.method == "GET":
classes_list = sqlhelper.get_list(
'select class_id,class_name from class_info', [])
return render(request, 'add_student.html',
{'classes_list': classes_list})
else:
stu_name = request.POST.get('stu_name')
class_id = request.POST.get('classId')
sqlhelper.add(
"insert into stu_info(stu_name,stu_class_id) values(%s,%s)",
[stu_name, class_id])
return redirect('/students/')
def teacher(request):
teacher_list = sqlhelper.get_list(
'''
SELECT l.id,t.id teacher_id,t.name,c.id class_id,c.title FROM teacher t
LEFT JOIN link_t_C l ON t.id =l.teacher_id
LEFT JOIN class c ON l.class_id =c.id;''', [])
class_list = sqlhelper.get_list('SELECT * FROM class')
dict_ = {}
for key in teacher_list:
if dict_.get(key[1]) == None:
dict_[key[1]] = {
'name': key[2],
'titles': [
key[4],
],
'teacher_id': key[1]
}
else:
dict_[key[1]]['titles'].append(key[4])
return render(request, 'teacher.html', {
'teacher_list': dict_.values(),
'class_list': class_list
})
def edit_class(request):
if request.method == "GET":
class_id = request.GET.get("nid")
class_title = sqlhelper.get_list(
'SELECT title FROM class WHERE id =%s LIMIT 1;', [class_id])
return render(request, 'edit_class.html',
{'result': [class_id, class_title[0][0]]})
if request.method == "POST":
class_id = request.GET.get("nid")
class_title = request.POST.get("class_title")
if class_title == '':
return redirect('/del_class/?nid=' + class_id)
sqlhelper.modify("UPDATE class SET title=%s WHERE id=%s;",
[class_title, class_id])
return redirect('/class/')
def add_teacher(request):
if request.method == "GET":
class_list = sqlhelper.get_list("SELECT * FROM class")
return render(request, 'add_teacher.html', {"class_list": class_list})
if request.method == "POST":
teacher_name = request.POST.get("teacher_name")
class_list = request.POST.getlist("class_list")
if teacher_name == "":
class_list = sqlhelper.get_list("SELECT * FROM class")
return render(request, 'add_teacher.html', {
"class_list": class_list,
"error": "empty name"
})
teacher_id = sqlhelper.get_list(
'SELECT id FROM teacher ORDER BY id DESC LIMIT 1;')
if teacher_id == []:
teacher_id = 1
else:
teacher_id = teacher_id[0][0] + 1
sqlhelper.modify("INSERT INTO teacher (id,name) VALUES (%s,%s);",
[teacher_id, teacher_name])
for class_id in class_list:
num = sqlhelper.get_list(
"SELECT * FROM link_t_C ORDER BY id DESC LIMIT 1;")
if num == []:
num = 1
else:
num = num[0][0] + 1
sqlhelper.modify(
"INSERT INTO link_t_C (id,teacher_id,class_id) VALUES (%s,%s,%s)",
[
num,
teacher_id,
int(class_id),
])
return redirect('/teacher/')
def add_class(request):
if request.method == "GET":
return render(request, 'add_class.html')
else:
class_name = request.POST.get('title')
class_num = sqlhelper.get_list(
'SELECT id FROM class ORDER BY id DESC LIMIT 1;')
if class_num == []:
class_id = 1
else:
class_id = class_num[0][0] + 1
sqlhelper.modify('INSERT IGNORE INTO class (id,title) VALUES (%s,%s);',
[
class_id,
class_name,
])
return redirect('/class/')
def edit_students(request):
if request.method == 'GET':
pk = request.GET.get('pk')
class_list = sqlhelper.get_list("select id,title from class", [])
current_stuinfo = sqlhelper.get_one(
"select id,name,cid from students where id=%s", [
pk,
])
return render(request, 'edit_students.html', {
'class_list': class_list,
'current_stuinfo': current_stuinfo
})
else:
pk = request.GET.get('pk')
edit_name = request.POST.get('edit_name')
c_id = request.POST.get('c_id')
sqlhelper.modify("update students set name=%s,cid=%s where id=%s",
[edit_name, c_id, pk])
return redirect('stu:students')
def edit_class(request):
if request.method == "GET":
class_id = request.GET.get("nid")
class_name = sqlhelper.get_list(
'SELECT title FROM class WHERE id =%s LIMIT 1;', [
class_id,
])[0][0]
return HttpResponse(class_name)
if request.method == "POST":
print(request.POST)
class_id = request.POST.get('class_id')
new_classname = request.POST.get("new_c_name")
if new_classname == '':
return HttpResponse('ERROR')
sqlhelper.modify('UPDATE class SET title=%s WHERE id=%s;', [
new_classname,
class_id,
])
return HttpResponse('200')
def edit_student(request):
if request.method == "GET":
nid = request.GET.get('nid')
class_list = sqlhelper.get_list('select cid,cname from class', [])
curr_stu = sqlhelper.get_one(
'select sid,sname,classid from student where sid=%s', [
nid,
])
return render(request, 'edit_student.html', {
'class_list': class_list,
'curr_stu': curr_stu
})
else:
nid = request.GET.get('nid')
stuname = request.POST.get('sname')
class_id = request.POST.get('class_id')
sqlhelper.modify("update student set sname=%s,classid=%s where sid=%s",
[stuname, class_id, nid])
return redirect('/students/')
def edit_student(request):
nid = request.GET.get('nid')
if request.method == 'GET':
class_list = sqlhelper.get_list(
'select class_id,class_name from class_info', [])
stu_name = sqlhelper.get_one(
'select stu_id,stu_name,stu_class_id from stu_info where stu_id=%s',
[
nid,
])
return render(request, 'edit_student.html', {
'class_list': class_list,
'stu_name': stu_name
})
else:
stu_name = request.POST.get('stu_name')
class_id = request.POST.get('classId')
sqlhelper.modify(
'update stu_info set stu_name = %s,stu_class_id=%s where stu_id = %s',
[stu_name, class_id, nid])
return redirect('/students/')
def teachers(request):
teacher_list = sqlhelper.get_list(
"""
select teachers.id as tid, teachers.name, class.title from teachers
left join relations on teachers.id = relations.t_id
left join class on class.id = relations.c_id;
""", [])
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, 'teachers.html', {'teacher_list': result.values()})
def teachers(request):
teacher_list = sqlhelper.get_list(
"""
SELECT tid,tname,cname FROM teacher
LEFT JOIN teacher2class ON teacher.tid = teacher2class.teacherid
LEFT JOIN class ON teacher2class.classid = class.cid
""", [])
teacher_dict = {}
for row in teacher_list:
tid = row['tid']
if tid in teacher_dict:
teacher_dict[tid]['classes'].append(row['cname'])
else:
teacher_dict[tid] = {
'tid': row['tid'],
'tname': row['tname'],
'classes': [
row['cname'],
]
}
return render(request, 'teachers.html',
{'teacher_dict': teacher_dict.values()})
def add_teacher(request):
if request.method == "GET":
classes_list = sqlhelper.get_list(
'select class_id,class_name from class_info', [])
return render(request, 'add_teacher.html',
{"classes_list": classes_list})
else:
tea_name = request.POST.get('tea_name')
lastrowid = sqlhelper.create(
'insert into tea_info(tea_name) values(%s)', [
tea_name,
])
class_list = request.POST.getlist('class_ids')
# for row in class_list:
# sqlhelper.modify('insert into teatocla(teatocla_tea_id,teatocla_class_id) values(%s,%s)',
# [lastrowid, row, ])
# 连接一次,提交多次
# obj = sqlhelper.SqlHelper()
# for row in class_list:
# sqlhelper.modify('insert into teatocla(teatocla_tea_id,teatocla_class_id) values(%s,%s)',
# [lastrowid, row, ])
# obj.close()
# 连接一次，提交一次
data_list = []
for row in class_list:
temp = (lastrowid, row)
data_list.append(temp)
# print(data_list)
obj = sqlhelper.SqlHelper()
obj.multiple_modify(
'insert into teatocla(teatocla_tea_id,teatocla_class_id) values(%s,%s)',
data_list)
obj.close()
return redirect('/teachers/')
def add_teacher(request):
if request.method == 'GET':
class_list = sqlhelper.get_list("select id,title from class", [])
return render(request, 'add_teacher.html', {'class_list': class_list})
else:
name = request.POST.get('name')
teacher_id = sqlhelper.create("insert into teachers(name) value (%s)",
[
name,
])
class_ids = request.POST.getlist('class_ids')
data_list = []
for cls_id in class_ids:
temp = (
teacher_id,
cls_id,
)
data_list.append(temp)
obj = sqlhelper.SqlHelper()
obj.multiple_modify('insert into relations(t_id, c_id) values(%s, %s)',
data_list)
obj.close()
return redirect('stu:teachers')
def edit_student(request):
if request.method == 'GET':
nid = request.GET.get('nid')
class_list = sqlhelper.get_list('select id,title from class', [])
current_student_info = sqlhelper.get_one(
'select id,name,class_id from student where id=%s', [
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')
sqlhelper.modify('update student set name=%s,class_id=%s where id=%s',
[
name,
class_id,
nid,
])
return redirect('/students/')
def add_teacher(request):
"""
添加教师
:param request:
:return:
"""
if request.method == 'GET':
class_list = sqlhelper.get_list('select id,title from class', [])
return render(request, 'add_teacher.html', {'class_list': class_list})
else:
name = request.POST.get('name')
obj = sqlhelper.SqlHelper()
teacher_id = obj.create('insert into teacher(name) values (%s)', [
name,
])
class_ids = request.POST.getlist('class_ids') # ['1', '8', '9', '10']
# 多次连接，多次提交
"""
for class_id in class_ids:
sqlhelper.modify('insert into teacher2class(teacher_id,class_id) values (%s,%s)', [teacher_id, class_id])
"""
# 一次连接，多次提交
"""
for class_id in class_ids:
obj.modify('insert into teacher2class(teacher_id,class_id) values (%s,%s)', [teacher_id, class_id])
obj.close()
"""
# 一次连接，一次提交
data_list = [] # [(9, '8'), (9, '9'), (9, '10')]
for class_id in class_ids:
data_list.append((teacher_id, class_id))
obj.multiple_modify(
'insert into teacher2class(teacher_id,class_id) values (%s,%s)',
data_list)
obj.close()
return redirect('/user/teacher/')
from getpass import getuser
from utils import sqlhelper
target = teacher_list =sqlhelper.get_list('''
SELECT l.id,t.id teacher_id,t.name,c.id class_id,c.title FROM teacher t
LEFT JOIN link_t_C l ON t.id =l.teacher_id
LEFT JOIN class c ON l.class_id =c.id;''',[]
)
print(target)
dict_ ={}
for key in target:
if dict_.get(key[1]) ==None:
dict_[key[1]] ={
'name':key[2],
'titles':[key[4],],
'teacher_id':key[1]
}
else:
dict_[key[1]]['titles'].append(key[4])
print(dict_)
