上次发的一篇文章，有很多朋友私信我要后面的部分，那咱们就今天来一起学习一下吧，因为我的数据库这门课选中的课题是学生管理系统，所以今天就以这个课题为例子，从0到1去实现一个管理系统。数据库设计部分我会专门出一个博客的，敬请期待吧~~~

介如很多朋友问源码，我已经将它上传到github上。（内有sql文件，可直接导入数据库使用）

看到这了点个赞再走吧

wuyongch/-Student-management-system： 该学生管理系统使用python+flask框架+mysql数据库 实现学生录入、学生信息修改、学生课程录入和查询、毕业学生去向查询、教师开设课程查看、管理超级用户 (github.com)

效果展现

一、实现登录功能

这里我就不细讲了，感兴趣的可以看下面这个博客👇

(5条消息) python Web开发 flask轻量级Web框架实战项目--实现功能--账号密码登录界面(连接数据库Mysql)_flask web开发实战_吴永畅的博客-CSDN博客

这次有点不同的是 需要把登录功能封装成一个login函数，然后我为了省事呢就把数据库连接放在外部。

代码实现（前端登录界面在上方博客里）

# 初始化app = flask.Flask(__name__)# 使用pymysql.connect方法连接本地mysql数据库db = pymysql.connect(host='localhost', port=3306, user='root', password='root', database='student', charset='utf8')# 操作数据库，获取db下的cursor对象cursor = db.cursor()# 存储登陆用户的名字用户其它网页的显示users = []@app.route("/", methods=["GET", "POST"])def login(): # 增加会话保护机制(未登陆前login的session值为空) flask.session['login'] = '' if flask.request.method == 'POST': user = flask.request.values.get("user", "") pwd = flask.request.values.get("pwd", "") # 防止sql注入,如:select * from admins where admin_name = '' or 1=1 -- and password=''; # 利用正则表达式进行输入判断 result_user = re.search(r"^[a-zA-Z]+$", user) # 限制用户名为全字母 result_pwd = re.search(r"^[a-zA-Z\d]+$", pwd) # 限制密码为 字母和数字的组合 if result_user != None and result_pwd != None: # 验证通过 msg = '用户名或密码错误' # 正则验证通过后与数据库中数据进行比较 # sql = "select * from sys_user where username='" + \ # user + "' and password='" + pwd + "';" sql1 = "select * from admins where admin_name='" + \ user + " ' and admin_password='" + pwd + "';" # cursor.execute(sql) cursor.execute(sql1) result = cursor.fetchone() # 匹配得到结果即管理员数据库中存在此管理员 if result: # 登陆成功 flask.session['login'] = 'OK' users.append(user) # 存储登陆成功的用户名用于显示 return flask.redirect(flask.url_for('student')) # return flask.redirect('/file') else: # 输入验证不通过 msg = '非法输入' else: msg = '' user = '' return flask.render_template('login.html', msg=msg, user=user)

二、学生信息录入功能

 这里我们可以录入的信息是学生学号、学生姓名、班级、性别等。

首先用户登录成功之后，跳转到学生信息录入界面，系统需要显示出学生表信息。

if flask.request.method == 'GET': sql_list = "select * from students_infos" cursor.execute(sql_list) results = cursor.fetchall() if flask.request.method == 'POST': # 获取输入的学生信息 student_id = flask.request.values.get("student_id", "") student_class = flask.request.values.get("student_class", "") student_name = flask.request.values.get("student_name", "") student_sex = flask.request.values.get("student_sex") print(student_id, student_class, student_name, student_sex)

插入数据只需要写入sql语句，并且执行该语句就可以，异常处理是个人习惯，在插入失败是系统给予提示，这里的sql语句都是写活的，真正的数据是来源于页面输入，其实就是调用了sql语句插入成功后，学生表就会自动同步显示在前端页面上。

完整代码如下

@app.route('/student', methods=['GET', "POST"])def student(): # login session值 if flask.session.get("login", "") == '': # 用户没有登陆 print('用户还没有登陆!即将重定向!') return flask.redirect('/') insert_result = '' # 当用户登陆有存储信息时显示用户名,否则为空 if users: for user in users: user_info = user else: user_info = '' # 获取显示数据信息 if flask.request.method == 'GET': sql_list = "select * from students_infos" cursor.execute(sql_list) results = cursor.fetchall() if flask.request.method == 'POST': # 获取输入的学生信息 student_id = flask.request.values.get("student_id", "") student_class = flask.request.values.get("student_class", "") student_name = flask.request.values.get("student_name", "") student_sex = flask.request.values.get("student_sex") print(student_id, student_class, student_name, student_sex) try: # 信息存入数据库 sql = "create table if not exists students_infos(student_id varchar(10) primary key,student_class varchar(100),student_name varchar(32),student_sex VARCHAR(4));" cursor.execute(sql) sql_1 = "insert into students_infos(student_id, student_class, student_name, student_sex )values(%s,%s,%s,%s)" cursor.execute(sql_1, (student_id, student_class, student_name, student_sex)) insert_result = "成功存入一条学生信息" print(insert_result) except Exception as err: print(err) insert_result = "学生信息插入失败" print(insert_result) pass db.commit() # POST方法时显示数据 sql_list = "select * from students_infos" cursor.execute(sql_list) results = cursor.fetchall() return flask.render_template('student.html', insert_result=insert_result, user_info=user_info, results=results)

student前端页面（需要自取，点个赞哦）

<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>学生成绩管理系统</title> <link rel="icon" href="http://v3.bootcss.com/favicon.ico"> <link rel="stylesheet" type="text/css" href="../static/css/bootstrap.min.css"/> <style> .container { position: absolute; width: 100%; height: 100%; } * { margin: 0; padding: 0; } ul { list-style-type: none; margin: 0; padding: 0; width: 200px; background-color: #f1f1f1; } li a { display: block; color: #000; padding: 8px 16px; text-decoration: none; } li a.active { background-color: #4CAF50; color: white; } li a:hover:not(.active) { background-color: #555; color: white; } li { list-style: none; } a { text-decoration: none; color: white; } .header { position: relative; width: 100%; height: 55px; background-color: black; } .left { position: absolute; left: 20px; font-size: 20px; line-height: 55px; color: white; text-align: center; } .right { position: absolute; right: 160px; line-height: 55px; color: white; text-align: center; } .right_right { position: absolute; right: 24px; line-height: 55px; color: white; text-align: center; } .leftside { float: left; background-color: rgb(245, 245, 245); /* height: 663px; */ width: 230px; } .leftside ul { height: 663px; } .leftside .first { background-color: rgb(66, 139, 202); margin-top: 25px; } .leftside .first a { color: white; } .leftside { float: left; width: 200px; height: 100%; } .leftside ul li { border-bottom: 0.2px solid white; font-size: 20px; height: 60px; line-height: 60px; width: 100%; text-align: center; } .container-fluid { float: none; width: 100%; height: 100%; } /* .sub-header { margin-top: 5px; } */ .table-responsive { margin-top: 10px; } .table-striped { width: 1250px; } thead tr th { background-color: white; } </style></head><body><div class="container"> <div class="header"> <span class="left">学 生 信 息 录 入</span> <span class="right">你 好，{ {user_info}}管 理 员!</span> <span class="right_right"><a href="/">退出登陆</a></span> </div> <div class="leftside"> <ul> <li class="first"><a href="/student">学生信息录入</a></li> <li><a href ="/updata_student">学生信息修改</a></li> <li><a href="/teacher_class">老师开设课程查看</a></li> <li><a href="/teacher">选课信息录入</a></li> <li><a href="/grade">成绩信息录入</a></li> <li><a href="/grade_infos">学生成绩查询</a></li> <li><a href="/graduation">毕业去向</a></li> <li><a href="/adminstator">系统管理员变动</a></li> </ul> </div> <div class="container-fluid"> <h1 class="sub-header">学 生 信 息 录 入 系 统</h1>&nbsp;&nbsp; <hr> <div class="table-responsive"> <table class="table table-striped"> <thead> <tr> <th>学号</th> <th>班级</th> <th>姓名</th> <th>性别</th> </tr> </thead> <tbody> <tr> <form action="" method="post"> <td><input class="long" name="student_id" type="text" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> <td><input class="long" name="student_class" type="text" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> <td><input class="long" name="student_name" type="text" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> <td><input class="long" name="student_sex" type="text" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> <td><input class="last" type="submit" value="提交" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> <td><span>提交结果:{ {insert_result}}</span></td> </form> </tr> <tr> <td>学生学号</td> <td>所属班级</td> <td>学生姓名</td> <td>学生性别</td> </tr> {% for result in results %} <tr> <td>{ {result[0]}}</td> <td>{ {result[1]}}</td> <td>{ {result[2]}}</td> <td>{ {result[3]}}</td> </tr> {% endfor %} </tbody> </table> </div> </div></div></body></html>

三、学生信息变动功能（修改班级和姓名or删除学生）

如图所示 吴永畅现在的班级是软件工程212 现在要修改为软件工程213 那么就在下拉框里选择修改学生班级，然后把学生学号和姓名填入，在学生班级那里填入修改后的班级。

 修改后：

原理都是一样的，执行对应的sql语句即可，这里就不赘述了。 

 完整代码

@app.route('/updata_student', methods=['GET', "POST"])def updata_student(): # login session值 if flask.session.get("login", "") == '': # 用户没有登陆 print('用户还没有登陆!即将重定向!') return flask.redirect('/') insert_result = '' # 获取显示学生数据信息(GET方法的时候显示数据) if flask.request.method == 'GET': sql_list = "select * from students_infos" cursor.execute(sql_list) results = cursor.fetchall() # 当用户登陆有存储信息时显示用户名,否则为空 if users: for user in users: user_info = user else: user_info = '' if flask.request.method == 'POST': # 获取输入的学生信息 student_id = flask.request.values.get("student_id", "") student_class = flask.request.values.get("student_class", "") student_name = flask.request.values.get("student_name", "") # student_sex = flask.request.values.get("student_sex", "") student_id_result = re.search(r"^\d{8,}$", student_id) # 限制用户名为全字母 # 验证通过 if student_id_result != None: # 验证通过 # 获取下拉框的数据 select = flask.request.form.get('selected_one') if select == '修改学生班级': try: sql = "update students_infos set student_class=%s where student_id=%s;" cursor.execute(sql, (student_class, student_id)) insert_result = "学生" + student_id + "的班级修改成功!" except Exception as err: print(err) insert_result = "修改学生班级失败!" pass db.commit() if select == '修改学生姓名': try: sql = "update students_infos set student_name=%s where student_id=%s;" cursor.execute(sql, (student_name, student_id)) insert_result = "学生" + student_name + "的姓名修改成功!" except Exception as err: print(err) insert_result = "修改学生姓名失败!" pass db.commit() if select == '删除学生': try: sql_delete = "delete from students_infos where student_id='" + student_id + "';" cursor.execute(sql_delete) insert_result = "成功删除学生" + student_id except Exception as err: print(err) insert_result = "删除失败" pass db.commit() else: # 输入验证不通过 insert_result = "输入的格式不符合要求!" # POST方法时显示数据 sql_list = "select * from students_infos" cursor.execute(sql_list) results = cursor.fetchall() return flask.render_template('updata_student.html', user_info=user_info, insert_result=insert_result, results=results)

前端页面 updata_student.html

<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>学生成绩管理系统</title> <link rel="icon" href="http://v3.bootcss.com/favicon.ico"> <link rel="stylesheet" type="text/css" href="../static/css/bootstrap.min.css"/> <style> .container { position: absolute; width: 100%; height: 100%; } * { margin: 0; padding: 0; } ul { list-style-type: none; margin: 0; padding: 0; width: 200px; background-color: #f1f1f1; } li a { display: block; color: #000; padding: 8px 16px; text-decoration: none; } li a.active { background-color: #4CAF50; color: white; } li a:hover:not(.active) { background-color: #555; color: white; } li { list-style: none; } a { text-decoration: none; color: white; } .header { position: relative; width: 100%; height: 55px; background-color: black; } .left { position: absolute; left: 20px; font-size: 20px; line-height: 55px; color: white; text-align: center; } .right { position: absolute; right: 160px; line-height: 55px; color: white; text-align: center; } .right_right { position: absolute; right: 24px; line-height: 55px; color: white; text-align: center; } .leftside { float: left; background-color: rgb(245, 245, 245); /* height: 663px; */ width: 230px; } .leftside ul { height: 663px; } .leftside .first { background-color: rgb(66, 139, 202); margin-top: 25px; } .leftside .first a { color: white; } .leftside { float: left; width: 200px; height: 100%; } .leftside ul li { border-bottom: 0.2px solid white; font-size: 20px; height: 60px; line-height: 60px; width: 100%; text-align: center; } .container-fluid { float: none; width: 100%; height: 100%; } .table-responsive { margin-top: 10px; } .table-striped { width: 1250px; } thead tr th { background-color: white; } </style></head><body><div class="container"> <div class="header"> <span class="left">学 生 信 息 修 改</span> <span class="right">你 好，{ {user_info}}管 理 员!</span> <span class="right_right"><a href="/">退出登陆</a></span> </div> <div class="leftside"> <ul> <li><a href="/student">学生信息录入</a></li> <li class="first"><a href="#">学生信息变动</a></li> <li><a href="/teacher_class">老师开设课程查看</a></li> <li><a href="/teacher">选课信息录入</a></li> <li><a href="/grade">成绩信息录入</a></li> <li><a href="/grade_infos">学生成绩查询</a></li> <li><a href="/graduation">毕业去向</a></li> <li><a href="/adminstator">系统管理员变动</a></li> </ul> </div> <div class="container-fluid"> <h1 class="sub-header">学 生 信 息 修 改 系 统</h1>&nbsp;&nbsp; <hr> <div class="table-responsive"> <table class="table table-striped"> <thead> <tr> <th>学生学号<span>(限全数字的学号)</span></th> <th>学生班级<span></span></th> <th>学生姓名<span></span></th> <th>学生性别<span></span></th>--> <th>选择(修改/删除)学生</th> </tr> </thead> <tbody> <tr> <form action="" method="post"> <td><input class="long" name="student_id" type="text" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> <td><input class="long" name="student_class" type="text" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> <td><input class="long" name="student_name" type="text" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </td> <td><select name="selected_one"> <option value="修改学生班级">修改学生班级</option> <option value="修改学生姓名">修改学生姓名</option> <option value="删除学生 ">删除学生</option> </select></td> <td><input class="last" type="submit" value="操作" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> <td class="doit"><span>操作结果:{ {insert_result}}</span></td> </form> </tr> <tr> <td>学号</td> <td>班级</td> <td>姓名</td> </tr> {% for result in results %} <tr> <td>{ {result[0]}}</td> <td>{ {result[1]}}</td> <td>{ {result[2]}}</td> </tr> {% endfor %} </tbody> </table> </div> </div></div></body></html>

四、学生成绩查询功能

如下图查询学号为20212248的学生成绩

 这里我们设计几种常见的查询方式，大家也可以自己添加。

完整代码 

@app.route('/grade_infos', methods=['GET', 'POST'])def grade_infos(): # login session值 if flask.session.get("login", "") == '': # 用户没有登陆 print('用户还没有登陆!即将重定向!') return flask.redirect('/') query_result = '' results = '' # 当用户登陆有存储信息时显示用户名,否则为空 if users: for user in users: user_info = user else: user_info = '' # 获取下拉框的数据 if flask.request.method == 'POST': select = flask.request.form.get('selected_one') query = flask.request.values.get('query') print(select, query) # 判断不同输入对数据表进行不同的处理 if select == '学号': try: sql = "select * from grade_infos where student_id = %s; " cursor.execute(sql, query) results = cursor.fetchall() if results: query_result = '查询成功!' else: query_result = '查询失败!' except Exception as err: print(err) pass if select == '姓名': try: sql = "select * from grade_infos where student_id in(select student_id from students_infos where student_name=%s);" cursor.execute(sql, query) results = cursor.fetchall() if results: query_result = '查询成功!' else: query_result = '查询失败!' except Exception as err: print(err) pass if select == '课程名称': try: sql = "select * from grade_infos where student_class_id in(select student_class_id from students_infos where student_class_id=%s);" cursor.execute(sql, query) results = cursor.fetchall() if results: query_result = '查询成功!' else: query_result = '查询失败!' except Exception as err: print(err) pass if select == "所在班级": try: sql = "select * from grade_infos where student_class_id in(select student_class_id from students_infos where student_class=%s);" cursor.execute(sql, query) results = cursor.fetchall() if results: query_result = '查询成功!' else: query_result = '查询失败!' except Exception as err: print(err) pass return flask.render_template('grade_infos.html', query_result=query_result, user_info=user_info, results=results)

前端页面 grade_infos.html

<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>学生成绩管理系统</title> <link rel="icon" href="http://v3.bootcss.com/favicon.ico"> <link rel="stylesheet" type="text/css" href="../static/css/bootstrap.min.css"/> <style> .container { position: absolute; width: 100%; height: 100%; } * { margin: 0; padding: 0; } ul { list-style-type: none; margin: 0; padding: 0; width: 200px; background-color: #f1f1f1; } li a { display: block; color: #000; padding: 8px 16px; text-decoration: none; } li a.active { background-color: #4CAF50; color: white; } li a:hover:not(.active) { background-color: #555; color: white; } li { list-style: none; } a { text-decoration: none; color: white; } .header { position: relative; width: 100%; height: 55px; background-color: black; } .left { position: absolute; left: 20px; font-size: 20px; line-height: 55px; color: white; text-align: center; } .right { position: absolute; right: 160px; line-height: 55px; color: white; text-align: center; } .right_right { position: absolute; right: 24px; line-height: 55px; color: white; text-align: center; } .leftside { float: left; background-color: rgb(245, 245, 245); /* height: 663px; */ width: 230px; } .leftside ul { height: 663px; } .leftside .first { background-color: rgb(66, 139, 202); margin-top: 25px; } .leftside .first a { color: white; } .leftside { float: left; width: 200px; height: 100%; } .leftside ul li { border-bottom: 0.2px solid white; font-size: 20px; height: 60px; line-height: 60px; width: 100%; text-align: center; } .container-fluid { float: none; width: 100%; height: 100%; } .table-responsive { margin-top: 10px; } .table-striped { width: 1250px; } thead tr th { background-color: white; } </style></head><body><div class="container"> <div class="header"> <span class="left">学 生 成 绩 查 询</span> <span class="right">你 好，{ {user_info}}老 师!</span> <span class="right_right"><a href="/">退出登陆</a></span> </div> <div class="leftside"> <ul> <li><a href="/student">学生信息录入</a></li> <li><a href ="/updata_student">学生信息修改</a></li> <li><a href="/teacher_class">老师开设课程查看</a></li> <li><a href="/teacher">选课信息录入</a></li> <li><a href="/grade">成绩信息录入</a></li> <li class="first"><a href="#">学生成绩查询</a></li> <li><a href="/graduation">毕业去向</a></li> <li><a href="/adminstator">系统管理员变动</a></li> </ul> </div> <div class="container-fluid"> <h1 class="sub-header">学 生 成 绩 查 询 系 统</h1>&nbsp;&nbsp; <hr> <div class="table-responsive"> <table class="table table-striped" cellspaing="10"> <tbody> <tr> <form action="" method="post"> <td><label for="#">请选择查询的方式:(学号/姓名/课程名称/所在班级)</label>&nbsp;&nbsp;&nbsp;</td> <td><select name="selected_one"> <option value="学号" selected="selected">学号</option> <option value="姓名">姓名</option> <option value="课程号">课程号</option> <option value="所在班级">所在班级</option> </select></td>&nbsp; <td><input class="long" type="text" name="query"></td> <td><input class="last" type="submit" value="查询" /></td> <td><span>查询结果:{ {query_result}}</span></td> </form> </tr> <tr> <td>学生学号</td> <td>课程号</td> <td>成绩</td> </tr> {% for result in results %} <tr> <td>{ {result[0]}}</td> <td>{ {result[1]}}</td> <td>{ {result[2]}}</td> </tr> {% endfor %} </tbody> </table> </div> </div></div></body></html>

 五、总结

因为实现原理都是相似的，所以我就不一个个去写了，就把增删查改功能各写一遍，剩下感兴趣的可以自己去尝试写下，要相信自己！总体来说还是一个值得初学者去练习的项目。