个人成绩管理系统
第一次使用这个框架写后台，现在用这个博客来总结以下我遇到的问题，在使用过程中
目录结构
结构比较简单
- app 是用来存放views，用来实现具体代码的；
- libary是用来存放设置，路由的，这是在我用这个框架的时候使用的东西
- static存放样式以及图片，表格的；
- templates用来存放html文件
配置问题
路由urls.py
settings
功能设计
1.具有管理功能，登陆后管理自己的成绩
2.用户数据、课程数据、成绩数据存放数据库
3.实现增删改查的功能
4.成绩统计，数据可视化
5.成绩表单的上传与下载
6.图片上传的功能，页面展示
7.具有人像识别功能
登录功能
前端代码部分
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>login</title>
<link rel="stylesheet" href="/static/login.css">
</head>
<body>
<div id="login">
<h1>个人成绩管理系统</h1>
<form method="post" action="/login/？p={{ studentID }}">
<img src="{{ img }}" alt="">
<input type="text" required="required" placeholder="学号" name="studentID"></input>
<input type="password" required="required" placeholder="密码" name="psw"></input>
<button class="but" type="submit" >登录</button>
<p >{{ msg }}</p>
</form>
</div>
</body>
</html>
后端代码
def login(request):
if request.method == "GET":
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "select * from users where uid='%s'" % ('string')
cursor = db.cursor()
cursor.execute(sql)
results = cursor.fetchall()
return render(request, 'login.html',)
else:
# 用户POST提交的数据
student=request.POST.get('studentID')
psw=request.POST.get('psw')
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "select * from users where uid='%s'" % student
cursor = db.cursor()
cursor.execute(sql)
results =cursor.fetchall()
# print(results)
# print(results_uid ,results_pwd)
if cursor.rowcount <= 0:
return redirect('/registered/')
else:
results_uid = results[0][2]
results_pwd = results[0][5]
# print(student, psw)
# student=int(student)
if results_uid == student and results_pwd == psw:
return redirect('/index/')
else:
return render(request, 'login.html', {'msg': '学号或密码错误'})
# print(request.POST)
在这个过程中我遇到一些问题：现在的代码是可以运行的,在此过程中render(request, ‘login.html’, {‘msg’: ‘学号或密码错误’})加粗部分只能给html 页面传一个字典值，如果传第二个就会显示源码
注册功能
前端代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>注册页面</title>
<style>
*{
margin: 0px;/*所有的外边距为0*/
padding: 0px;/*所有的内边距为0*/
box-sizing: border-box;/*规定两个并排的带边框的框*/
}
body{
background-color: #4A374A;;
padding-top: 25px;
}
.rg_layout{
width: 900px;
height: 500px;
border: 8px solid #EEEEEE;/*solid 定义实线*/
background-color: white;
margin: auto;
}
.rg_left{
float: left;
margin: 15px;
}
.rg_left>p:first-child{
color: #FFD026;
font-size: 20px;
}
.rg_left>p:last-child{
color: #A6A6A6;
font-size: 20px;
}
.rg_center{
float: left;
}
.rg_right{
float: right;
margin: 15px;
padding-left: 50px;
}
.rg_right p{
font-size: 15px;
}
.rg_right p a{
color: coral;
}
.td_left{
width: 100px;
text-align: right;
height: 45px;
}
.td_right{
padding-left: 50px;
}
#username,#password,#email,#name,#tel,#birthday,#checkcode{
width: 251px;
height: 32px;
border: 1px solid #A6A6A6;
/*设置边框圆角*/
border-radius: 5px;
padding-left: 10px;
}
#checkcode{
width: 110px;
}
#img_check{
height: 32px;
vertical-align: middle;/*设置图片的位置垂直居中*/
}
#btn_sub{
width: 100px;
height: 40px;
background-color: #FFD026;
border: 1px solid #FFD026;
padding-left: 10px;
}
</style>
</head>
<body>
<div class="rg_layout">
<div class="rg_left">
<p>新用户注册</p>
<p>USER REGISTER</p>
</div>
<div class="rg_center">
<div class="rg_form">
<form method="post" action="/registered/" enctype="multipart/form-data" >
<table>
<tr><!--label 标签的作用是当点击文字也会跳到文本输出框-->
<!--for属性与ID属性对应规定 label 绑定到哪个表单元素。-->
<td class="td_left"><label for="school">学校</label> </td>
<td class="td_right"><input type="text" name="school" id="school"> </td>
</tr>
<tr>
<td class="td_left"><label for="college">学院</label> </td>
<td class="td_right"><input type="text" name="college" id="college"> </td>
</tr>
<tr><!--label 标签的作用是当点击文字也会跳到文本输出框-->
<td class="td_left"><label for="uid">学号</label> </td>
<td class="td_right"><input type="text" name="uid" id="uid"> </td>
</tr>
<tr>
<td class="td_left"><label for="sname">姓名</label> </td>
<td class="td_right"><input type="text" name="sname" id="sname"> </td>
</tr>
<tr>
<td class="td_left"><label for="email">Email</label> </td>
<td class="td_right"><input type="email" name="email" id="email"> </td>
</tr>
<tr>
<td class="td_left"><label for="password">密码</label> </td>
<td class="td_right"><input type="password" name="password" id="password"> </td>
</tr>
<tr>
<td class="td_left"><label for="picture">头像</label> </td>
<td class="td_right"><input type="file" name="picture" id="picture"> </td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="注册" id="btn_sub">
</td>
</tr>
</table>
</form>
</div>
</div>
</div>
</body>
</html>
后端代码
def registered(request):
if request.method == "GET":
return render(request, 'registered.html')
else:
# 用户POST提交的数据
school=request.POST.get('school')
college=request.POST.get('college')
uid=request.POST.get('uid')
sname = request.POST.get('sname')
email=request.POST.get('email')
pwd=request.POST.get('password')
picture=request.POST.get('picture')
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "select * from users where uid='%s'" % uid
# print(school,college,uid,sname,email,pwd,picture)
cursor = db.cursor()
cursor.execute(sql)
if cursor.rowcount > 0:
return render(request, 'login.html',{'msg':'学号已注册，请确认是否正确'})
else:
sal = """INSERT INTO users(school,college,uid,sname,email,password,picture)VALUES(%s,%s,%s,%s,%s,%s,%s)"""
cursor.execute(sal, (school,college,uid,sname,email,pwd,picture))
db.commit()
db.close()
return redirect('/login/')
现在这些代码存在一些问题没办法,图片在存在了数据库中，没有存在文件夹中
def registered(request):
if request.method == "GET":
return render(request, 'registered.html')
else:
# 用户POST提交的数据
school=request.POST.get('school')
college=request.POST.get('college')
uid=request.POST.get('uid')
sname = request.POST.get('sname')
email=request.POST.get('email')
pwd=request.POST.get('password')
picture=request.FILES.get('picture')
if not picture:
return HttpResponse("no files for upload!")
destination = open(os.path.join("F:\\Users\\asus\\PycharmProjects\\python\\libary\\static\\pic", picture.name), 'wb+')
for chunk in picture.chunks(): # 分块写入文件
destination.write(chunk)
destination.close()
# return HttpResponse("upload over!")
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "select * from users where uid='%s'" % uid
print(school,college,uid,sname,email,pwd,picture)
print(type(picture))
cursor = db.cursor()
cursor.execute(sql)
if cursor.rowcount > 0:
return render(request, 'login.html',{'msg':'学号已注册，请确认是否正确'})
else:
# 4.在数据库中保存上传记录
sal = """INSERT INTO users(school,college,uid,sname,email,password,picture)VALUES(%s,%s,%s,%s,%s,%s,%s)"""
cursor.execute(sal, (school,college,uid,sname,email,pwd,str(picture)))
db.commit()
db.close()
return redirect('/login/')
上面的问题解决了
主页
前端代码
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title></title>
<link rel="stylesheet" href="/static/index.css">
</head>
<body>
<div class="width">
<h2>成绩展示</h2>
<ul>
<li><a href="/add_score/">添加</a></li>
<li><a href="/importfile/">导入</a></li>
<li><a href="/exportfile/">导出</a></li>
<li><a href="/del_score/">删除</a></li>
<li><a href="/updata_score/">更新</a></li>
<li><a href="/statistical/">统计</a></li>
<li><a href="/visualization/">学期成绩汇总</a></li>
<li><a href="/center/">个人中心</a></li>
</ul>
<table border="1" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th>cid</th>
<th>semester</th>
<th>cname</th>
<th>attribute</th>
<th>learning_cycle</th>
<th>credits</th>
<th>score</th>
</tr>
</thead>
<tbody>
{% for row in curscore_list %}
<tr>
<td>{{ row.0 }}</td>
<td>{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td>{{ row.4 }}</td>
<td>{{ row.5 }}</td>
<td>{{ row.6 }}</td>
<td>{{ row.7 }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</body>
</html>
后端代码
def index(request):
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "select * from curscore"
cursor = db.cursor()
cursor.execute(sql)
curscore_list = cursor.fetchall()
# print(curscore_list)
cursor.close()
db.close()
return render(request, 'index.html', {'curscore_list': curscore_list})
增删改查
前端
add
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>addscore</title>
</head>
<body>
<h1>添加课程</h1>
<form method="post" action="/add_score/">
<p>学年学期 <input type="text" name="semester" ></p>
<p>课程<input type="text" name="course"></p>
<p>属性 <input type="text" name="attribute" ></p>
<p>学时 <input type="text" name="learning_cycle" ></p>
<p>学分 <input type="text" name="credits" ></p>
<p>成绩<input type="text" name="score" ></p>
<p></p>
<input type="submit" name="submit" value="提交">
</form>
</body>
</html>
del
del
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>delscore</title>
</head>
<body>
<h1>删除课程</h1>
<form method="post" action="/del_score/">
<p>要删除课程的课程号 <input type="text" name="del_cid" id=""></p>
<input type="submit" name="" id="" value="提交">
</form>
</body>
</html>
updata
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>修改课程，或者成绩</h1>
<form method="post" action="/updata_score/">
<p>需要修改的课程的课程号 <input type="text" name="updata_cid" id=""></p>
<p>成绩 <input type="text" name="score" id=""></p>
<input type="submit" name="" id="" value="提交">
</form>
</body>
</html>
后端代码
def add_score(request):
if request.method=='GET':
return render(request,'add_score.html')
else:
cname = request.POST.get('course')
score = request.POST.get('score')
cid = request.POST.get('cid')
semester = request.POST.get('semester')
attribute = request.POST.get('attribute')
learning_cycle = request.POST.get('learning_cycle')
credits = request.POST.get('credits')
# print(cname,score,cid,semester,attribute,learning_cycle,credits)
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
cursor = db.cursor()
sal = """INSERT INTO curscore(uid,semester,cname,attribute,learning_cycle,credits,score)VALUES(%s,%s,%s,%s,%s,%s,%s)"""
cursor.execute(sal, ('string',semester,cname,attribute,learning_cycle,credits,score))
db.commit()
db.close()
return redirect('/index/')
def del_score(request):
if request.method =='GET':
return render(request,'del_score.html')
else:
cid = request.POST.get('del_cid')
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
cursor = db.cursor()
sql = "DELETE FROM curscore WHERE cid=%s" % cid
cursor.execute(sql)
db.commit()
db.close()
return redirect('/index/')
def updata_score(request):
if request.method == 'GET':
return render(request,'updata_score.html')
else:
score = request.POST.get('score')
cid = request.POST.get('updata_cid')
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
cursor = db.cursor()
sql = "update curscore set score=%s WHERE cid=%s" % (score,cid)
cursor.execute(sql)
db.commit()
db.close()
return redirect('/index/')
导入导出
前端代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>导入</title>
</head>
<body>
<form class="" action="/importfile/" method="post" enctype="multipart/form-data" >
{% csrf_token %}
<input type="file" name="excel_data">
<p></p>
<input type="submit" class="btn btn-default" value="上传">
</form>
</body>
</html>
后端代码
def importfile(request):
if request.method == 'POST': # 获取对象
excel_raw_data = pandas.read_excel(request.FILES.get('excel_data'))
# print(type(excel_raw_data.index))
db = create_engine('mysql+pymysql://root:123456@localhost:3306/achievement?charset=utf8')
# db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
# cursor = db.cursor()
df1 = pandas.DataFrame(excel_raw_data)
# print(df1)
excel_raw_data.to_sql(name = 'curscore',con=db, if_exists= 'append',index=False)
# db.commit()
# db.close()
return redirect('/index/')
return render(request, 'importfile.html')
def exportfile(request):
if request.method =='GET':
outfile = BytesIO()
engine = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = """SELECT * FROM curscore where uid='string'"""
df = pandas.read_sql(sql, engine)
response = HttpResponse(content_type='application/vnd.ms-excel')
execl_name = 'curscore'
response['Content-Disposition'] = 'attachment;filename={0}.xlsx'.format(execl_name)
df.to_excel(outfile, index=False)
response.write(outfile.getvalue())
return response
else:
return redirect('/index/')
可视化
前端代码
<html>
<head>
<meta charset="UTF-8" />
<title>可视化成绩</title>
<script src="http://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
</head>
<body>
<div id="container" style="width: 550px; height: 400px; margin: 0 auto"></div>
<script language="JavaScript">
$(document).ready(function() {
var title = {
text: '学年成绩汇总'
};
var subtitle = {
text: 'Source: imnu.edu.cn'
};
var xAxis = {
categories: ['2018-2019-1', '2018-2019-2', '2019-2020-1', '2019-2020-2', '2020-2021-1', '2020-2021-2'
,'2021-2022-1', '2021-2022-2']
};
var yAxis = {
title: {
text: '总分数'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
};
var tooltip = {
valueSuffix: ''
}
var legend = {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle',
borderWidth: 0
};
var series = [
{
name: 'string',
data:{{ total_score | safe }}
{#data: [{{ total_score1 | safe}},{{ total_score2 | safe }},{{ total_score3 | safe }},{{ total_score4 | safe}},{{ total_score5 | safe }},{{ total_score6 | safe }},{{ total_score7 | safe }},{{ total_score8 | safe}}]#}
},
];
var json = {};
json.title = title;
json.subtitle = subtitle;
json.xAxis = xAxis;
json.yAxis = yAxis;
json.tooltip = tooltip;
json.legend = legend;
json.series = series;
$('#container').highcharts(json);
});
</script>
</body>
</html>
后端代码
def visualization(request):
if request.method=="GET":
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "select score from curscore where semester = '2018-2019-1'"
cursor = db.cursor()
cursor.execute(sql)
results = cursor.fetchall()
total=0
for item in results:
for jtem in item:
total=total+jtem
print(total)
sql1 = "select score from curscore where semester = '2018-2019-2'"
cursor = db.cursor()
cursor.execute(sql1)
results1 = cursor.fetchall()
total1=0
for item in results1:
for jtem in item:
total1 = total1 + jtem
print(total1)
sql2 = "select score from curscore where semester = '2019-2020-1'"
cursor = db.cursor()
cursor.execute(sql2)
results2 = cursor.fetchall()
total2 = 0
for item in results2:
for jtem in item:
total2 = total2 + jtem
print(total2)
sql3 = "select score from curscore where semester = '2019-2020-2'"
cursor = db.cursor()
cursor.execute(sql3)
results3 = cursor.fetchall()
total3 = 0
for item in results3:
for jtem in item:
total3 = total3 + jtem
print(total3)
sql4 = "select score from curscore where semester = '2020-2021-1'"
cursor = db.cursor()
cursor.execute(sql4)
results4 = cursor.fetchall()
total4 = 0
for item in results4:
for jtem in item:
total4 = total4 + jtem
print(total4)
sql5 = "select score from curscore where semester = '2020-2021-2'"
cursor = db.cursor()
cursor.execute(sql5)
results5 = cursor.fetchall()
total5 = 0
for item in results5:
for jtem in item:
total5 = total5 + jtem
print(total5)
sql6 = "select score from curscore where semester = '2021-2022-1'"
cursor = db.cursor()
cursor.execute(sql6)
results6 = cursor.fetchall()
total6 = 0
for item in results6:
for jtem in item:
total6 = total6 + jtem
sql7 = "select score from curscore where semester = '2021-2022-2'"
cursor = db.cursor()
cursor.execute(sql7)
results7 = cursor.fetchall()
total7 = 0
for item in results7:
for jtem in item:
total7 = total7 + jtem
db.close()
total_score=[total,total1,total2,total3,total4,total5,total6,total7]
print(total_score)
return render(
request,
'visualization.html',
{'total_score':total_score}
)
在这个过程中遇到了一个问题cursor.fetchall()的返回值是元组套元组，在循环往外传值时得双循环
统计
前端代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>统计</title>
</head>
<body>
<h1>统计重修个数</h1>
<p>以下课程需要重修</p>
<table border="1" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th>cid</th>
<th>semester</th>
<th>cname</th>
<th>attribute</th>
<th>learning_cycle</th>
<th>credits</th>
<th>score</th>
</tr>
</thead>
<tbody>
{% for row in results %}
<tr>
<td>{{ row.0 }}</td>
<td>{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td>{{ row.4 }}</td>
<td>{{ row.5 }}</td>
<td>{{ row.6 }}</td>
<td>{{ row.7 }}</td>
</tr>
{% endfor %}
</tbody>
</table>
<a href="/index/"> <strong>回到主页</strong> </a>
</body>
</html>
后端代码
def statistical(request):
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
cursor = db.cursor()
sql = "select * from curscore where score<75"
cursor.execute(sql)
results = cursor.fetchall()
# print(results)
return render(
request,
'statistical.html',
# {'reset' : count},
{'results':results}
)
更换头像
前端代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>更新头像</title>
</head>
<body>
<form method="POST" action="/updata_pic/" enctype="multipart/form-data">
<h3>uid <input type="text" name="uid" id=""></h3>
<h3>上传要修改的头像</h3>
<p><input type="file" name="updata_pic" ></p>
<input type="submit" name="submit" value="提交">
</form>
</body>
</html>
后端代码
def updata_pic(request):
if request.method=='GET':
return render(request, 'updata_pic.html')
else:
picture=request.FILES.get('updata_pic')
print(picture)
uid = request.POST.get('uid')
if not picture:
return HttpResponse("no files for upload!")
destination = open(os.path.join("F:\\Users\\asus\\PycharmProjects\\python\\libary\\static\\pic", picture.name),'wb+')
for chunk in picture.chunks(): # 分块写入文件
destination.write(chunk)
destination.close()
picture=str(picture)
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "update users set picture='%s' WHERE uid='%s'" % (picture,uid)
cursor = db.cursor()
cursor.execute(sql)
db.commit()
db.close()
return redirect('/center/')
在这个过程中sql语句出现了问题，字符串的替换得加‘%s‘，才可以成功
个人中心
前端代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Personal center</title>
<style>
.wid {
width: 1200px;
margin: 0 auto;
padding: 100px 0;
}
.pic {
width: 70px;
height: 100px;
background-color: red;
float: left;
margin-right: 20px;
}
.pic img {
width: 70px;
height: 100px;
}
.uppic {
width: 70px;
height: 20px;
background-color: #3762bc;
}
.uppic p a {
text-decoration: none;
color: #fff;
}
</style>
</head>
<body>
<div class="wid">
{% for row in result %}
<div class="pic">
<img src="../static/pic/{{ row.2 }}" alt="头像">
</div>
<h3>姓名：{{ row.1 }}</h3>
<h3>学号：{{ row.0 }}</h3>
{% endfor %}
<div class="uppic">
<p><a href="/updata_pic/">更新头像</a></p>
</div>
</div>
</body>
</html>
后端代码
def center(request):
if request.method=='GET':
db = pymysql.connect(host="localhost", user="root", passwd="123456", db="achievement", port=3306)
sql = "select uid,sname,picture from users where uid = 'string'"
cursor = db.cursor()
cursor.execute(sql)
results = cursor.fetchall()
print(results)
return render(
request,
'center.html',
{'result':results}
# {'picture':picture}
)
else:
return redirect('/index/')
最后总结
在这个项目中感觉,遇到的问题很多，多数还是对于sql语句的不熟悉，还有对于框架的不熟悉导致的，以后多练习多熟练吧
190
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
