【Python毕业设计】基于Python+Flask+MySQL的学生信息管理系统（附完整源码）

from flask import Flask, render_template, request, redirect, session, jsonify, url_for
from flask_sqlalchemy import SQLAlchemy
from werkzeug.utils import secure_filename
import os,shutil
import random
import json
import pymysql
import logging
import math
from operator import or_

from exts.QueryPaginate import QueryPaginate

app = Flask(__name__,static_folder='')
app.config['DEBUG']=True
#配置session key
app.config['SECRET_KEY'] = '2cf0fa7e-48d9-11e6-93fb-c03fd53413ef'

#SQLAlchemy相关配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/student'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = False

# 创建组件对象
db = SQLAlchemy(app)

#**********配置初始系统变量**********
data = {}
data['SYSNAME']='毕业设计 - 学生信息管理系统'
data['DEVURL']='http://www.wwww.com/'
data['DEVNAME']='上海交大'

#**********数据库配置**********

#学生表
class TStudent(db.Model):
    __tablename__ = 't_student'
    id = db.Column(db.Integer, primary_key=True)
    student_no = db.Column(db.String(20))
    student_name = db.Column(db.String(100))
    department_no = db.Column(db.String(20))

#课程表
class TCourse(db.Model):
    __tablename__ = 't_course'
    id = db.Column(db.Integer, primary_key=True)
    course_no = db.Column(db.String(20))
    course_name = db.Column(db.String(100))

#成绩表
class TGrade(db.Model):
    __tablename__ = 't_grade'
    id = db.Column(db.Integer, primary_key=True)
    student_no = db.Column(db.String(20))
    course_no = db.Column(db.String(20))
    grade = db.Column(db.String(10))

#用户表
class TUser(db.Model):
    __tablename__ = 't_user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20))
    password = db.Column(db.String(20))
    realname = db.Column(db.String(20))

#学院表
class TDepartment(db.Model):
    __tablename__ = 't_department'
    id = db.Column(db.Integer, primary_key=True)
    department_no = db.Column(db.String(20))
    department_name = db.Column(db.String(20))

#**********路由部分**********

#首页
@app.route('/')
@app.route('/index')
def index():
    
    if(checkSignIn()==False):
        return redirect('/signin')

    studentCount = db.session.query(TStudent).count()
    courseCount = db.session.query(TCourse).count()
    userCount = db.session.query(TUser).count()
    gradeCount = db.session.query(TGrade).count()

    data['studentCount']= studentCount
    data['courseCount']= courseCount
    data['userCount']= studentCount
    data['gradeCount']= gradeCount

    studentData = db.session.query(TStudent,TDepartment).outerjoin(TDepartment, TStudent.department_no==TDepartment.department_no).order_by(TStudent.id.desc()).limit(10).all()
    print(studentData)
    data['studentData']=studentData

    userData = db.session.query(TUser).order_by(TUser.id.desc()).limit(10).all()
    data['userData']=userData
   

    return render_template('index.html', **data)
'''
#首页
@app.route('/index')
def index2():

    if(checkSignIn()==False):
        return redirect('/signin')

    return render_template('index.html', **data)
'''
#用户登录模块
@app.route('/signin',methods=['GET','POST'])
def signin():

    return render_template('sign-in.html',form=data)

#用户退出模块
@app.route('/signout',methods=['GET','POST'])
def signout():
    session.clear()
    #return redirect('/signin')
    return redirect(url_for('signin'))

#用户登录验证
@app.route('/dosignin',methods=['GET','POST'])
def dosignin():

    userName = request.values.get('userName')
    userPass = request.values.get('userPass')

    logging.info(request.remote_addr)#记录日志
    print(request.headers)#头文件
    print(request.remote_addr)#远程IP地址

    user = db.session.query(TUser).filter(TUser.username == userName,
    TUser.password == userPass
    ).first()

    if(user is not None):
        session['userName']=userName
        print(session)        
        return redirect('/')
    else:
        data['errorMessage']='用户名或密码不正确！'
        return render_template('signin.html',form=data)

#用户注册模块
@app.route('/signup',methods=['GET','POST'])
def signup():
    
    return render_template('sign-up.html',form=data)


#学生信息部分开始************************************

#查看列表带分页
@app.route('/students/',methods=['GET','POST'])
def students():

    studentNo = request.form.get('studentNo') if request.form.get('studentNo') is not None else ''
    studentName = request.form.get('studentName') if request.form.get('studentName') is not None else ''
    data['studentNo']=studentNo
    data['studentName']=studentName
    
    page = int(request.form.get('search_page')) if request.form.get('search_page') is not None and request.form.get('search_page') != '0' else 1

    query = db.session.query(TStudent).filter(or_(TStudent.student_no == studentNo, studentNo == ''),
    or_(TStudent.student_name.like('%{0}%'.format(studentName)),studentName == '')
    )
    queryPaginate = QueryPaginate(query,10,page)

    data['currdata']=queryPaginate.items
    data['pagelist']=queryPaginate.iter_pages()
    data['pages']=queryPaginate.pages
    data['page']=queryPaginate.page
    data['per_page']=queryPaginate.per_page
    data['prev_num']=queryPaginate.prev_num
    data['next_num']=queryPaginate.next_num
    
    return render_template('students.html',**data)

#查看单个信息
@app.route('/student/<int:studentid>',methods=['GET','POST'])
def student(studentid):
    data['studentid']=studentid

    currdata = db.session.query(TStudent).filter(TStudent.id==studentid).first()
    data['currdata'] = currdata
    return render_template('student.html',**data)

#编辑单个信息
@app.route('/student/edit/<int:studentid>',methods=['GET'])
def student_edit(studentid):

    currdata = db.session.query(TStudent).filter(TStudent.id==studentid).first()
    print(currdata)
    data['currdata'] = currdata
    return render_template('student_edit.html',**data)

#编辑保存单个信息
@app.route('/student/doedit/',methods=['POST'])
def student_doedit():
    studentid = request.form.get('studentid') 
    studentNo = request.form.get('studentNo')
    studentName = request.form.get('studentName')

    update_dict = {}
    if(studentNo is not None):
        update_dict['student_no']=studentNo
    if(studentName is not None):
        update_dict['student_name']=studentName    

    db.session.query(TStudent).filter_by(id=studentid).update(update_dict)
    db.session.commit()
    print(data)
    return redirect(url_for('students'))

#删除单个信息
@app.route('/student/del/<int:studentid>',methods=['GET'])
def student_del(studentid):
    db.session.query(TStudent).filter_by(id=studentid).delete()
    db.session.commit()    

    return redirect(url_for('students'))

#删除单个信息
@app.route('/student/dodel/',methods=['POST'])
def student_dodel(studentid):
    
    db.session.query(TStudent).filter_by(id=studentid).delete()
    db.session.commit()
    return redirect(url_for('students'))

#保存单个信息
@app.route('/student/add',methods=['GET'])
def student_add():

    return render_template('student_add.html',form=data)

#保存单个信息
@app.route('/student/doAdd',methods=['POST'])
def student_doadd():
    studentNo = request.form.get('studentNo')
    studentName = request.form.get('studentName')
    print(request.form)
    student = TStudent(student_no=studentNo,student_name=studentName)
    db.session.add(student)
    db.session.commit()

    return redirect(url_for('students'))

#学生信息部分结束************************************



#课程信息部分开始************************************

#查看列表带分页
@app.route('/courses/',methods=['GET','POST'])
def courses():

    courseNo = request.form.get('courseNo') if request.form.get('courseNo') is not None else ''
    courseName = request.form.get('courseName') if request.form.get('courseName') is not None else ''
    data['courseNo']=courseNo
    data['courseName']=courseName
    
    page = int(request.form.get('search_page')) if request.form.get('search_page') is not None and request.form.get('search_page') != '0' else 1

    query = db.session.query(TCourse).filter(or_(TCourse.course_no == courseNo, courseNo == ''),
    or_(TCourse.course_name.like('%{0}%'.format(courseName)),courseName == '')
    )
    queryPaginate = QueryPaginate(query,10,page)

    data['currdata']=queryPaginate.items
    data['pagelist']=queryPaginate.iter_pages()
    data['pages']=queryPaginate.pages
    data['page']=queryPaginate.page
    data['per_page']=queryPaginate.per_page
    data['prev_num']=queryPaginate.prev_num
    data['next_num']=queryPaginate.next_num
    
    return render_template('courses.html',**data)

#查看单个信息
@app.route('/course/<int:courseid>',methods=['GET','POST'])
def course(courseid):
    data['courseid']=courseid

    currdata = db.session.query(TCourse).filter(TCourse.id==courseid).first()
    data['currdata'] = currdata
    return render_template('course.html',**data)

#编辑单个信息
@app.route('/course/edit/<int:courseid>',methods=['GET'])
def course_edit(courseid):

    currdata = db.session.query(TCourse).filter(TCourse.id==courseid).first()
    print(currdata)
    data['currdata'] = currdata
    return render_template('course_edit.html',**data)

#编辑保存单个信息
@app.route('/course/doedit/',methods=['POST'])
def course_doedit():
    courseid = request.form.get('courseid') 
    courseNo = request.form.get('courseNo')
    courseName = request.form.get('courseName')

    update_dict = {}
    if(courseNo is not None):
        update_dict['course_no']=courseNo
    if(courseName is not None):
        update_dict['course_name']=courseName    

    db.session.query(TCourse).filter_by(id=courseid).update(update_dict)
    db.session.commit()
    print(data)
    return redirect(url_for('courses'))

#删除单个信息
@app.route('/course/del/<int:courseid>',methods=['GET'])
def course_del(courseid):
    db.session.query(TCourse).filter_by(id=courseid).delete()
    db.session.commit()    

    return redirect(url_for('courses'))

#删除单个信息
@app.route('/course/dodel/',methods=['POST'])
def course_dodel(courseid):
    
    db.session.query(TCourse).filter_by(id=courseid).delete()
    db.session.commit()
    return redirect(url_for('courses'))

#保存单个信息
@app.route('/course/add',methods=['GET'])
def course_add():

    return render_template('course_add.html',form=data)

#保存单个信息
@app.route('/course/doAdd',methods=['POST'])
def course_doadd():
    courseNo = request.form.get('courseNo')
    courseName = request.form.get('courseName')
    print(request.form)
    course = TCourse(course_no=courseNo,course_name=courseName)
    db.session.add(course)
    db.session.commit()

    return redirect(url_for('courses'))

#课程信息部分结束************************************



#成绩信息部分开始************************************
@app.route('/grades/',methods=['GET','POST'])
def grades():
    studentNo = request.form.get('studentNo') if request.form.get('studentNo') is not None else ''
    studentName = request.form.get('studentName') if request.form.get('studentName') is not None else ''
    courseNo = request.form.get('courseNo') if request.form.get('courseNo') is not None else ''
    courseName = request.form.get('courseName') if request.form.get('courseName') is not None else ''

    data['studentNo']=studentNo
    data['studentName']=studentName
    data['courseNo']=courseNo
    data['courseName']=courseName    
    
    page = int(request.form.get('search_page')) if request.form.get('search_page') is not None and request.form.get('search_page') != '0' else 1

    query = db.session.query(TGrade,TStudent,TCourse).outerjoin(TStudent, TStudent.student_no==TGrade.student_no).outerjoin(TCourse,TCourse.course_no==TGrade.course_no).filter(or_(TCourse.course_no == courseNo, courseNo == ''),
    or_(TCourse.course_name.like('%{0}%'.format(courseName)),courseName == ''),
    or_(TStudent.student_no == studentNo, studentNo == ''),
    or_(TStudent.student_name.like('%{0}%'.format(studentName)),studentName == '')                                                                                                                                                               
    )
    #print(query)
    queryPaginate = QueryPaginate(query,10,page)
    #print(queryPaginate.items)
    data['currdata']=queryPaginate.items
    data['pagelist']=queryPaginate.iter_pages()
    data['pages']=queryPaginate.pages
    data['page']=queryPaginate.page
    data['per_page']=queryPaginate.per_page
    data['prev_num']=queryPaginate.prev_num
    data['next_num']=queryPaginate.next_num
    
    return render_template('grades.html',**data)

#保存单个信息
@app.route('/grade/add',methods=['GET'])
def grade_add():

    studentdata = db.session.query(TStudent).all()
    data['studentdata']=studentdata
    
    coursedata = db.session.query(TCourse).all()
    data['coursedata']=coursedata

    return render_template('grade_add.html',**data)

#保存单个信息
@app.route('/grade/doAdd',methods=['POST'])
def grade_doadd():
    courseNo = request.form.get('courseNo')
    studentNo = request.form.get('studentNo')
    grade = request.form.get('grade')

    grade = TGrade(course_no=courseNo,student_no=studentNo,grade=grade)
    db.session.add(grade)
    db.session.commit()

    return redirect(url_for('grades'))


#删除单个信息
@app.route('/grade/del/<int:gradeid>',methods=['GET'])
def grade_del(gradeid):
    db.session.query(TGrade).filter_by(id=gradeid).delete()
    db.session.commit()    

    return redirect(url_for('grades'))

#成绩信息部分结束************************************





#用户信息部分开始************************************

#查看列表
@app.route('/users',methods=['GET','POST'])
def users():

    userName = request.form.get('userName') if request.form.get('userName') is not None else ''
    realName = request.form.get('realName') if request.form.get('realName') is not None else ''

    data['userName']=userName
    data['realName']=realName    
    
    page = int(request.form.get('search_page')) if request.form.get('search_page') is not None and request.form.get('search_page') != '0' else 1

    query = db.session.query(TUser).filter(or_(TUser.username == userName, userName == ''),
    or_(TUser.realname.like('%{0}%'.format(realName)),realName == '')                                                                                                                                                           
    )
    print(query)
    queryPaginate = QueryPaginate(query,10,page)
    #print(queryPaginate.items)
    data['currdata']=queryPaginate.items
    data['pagelist']=queryPaginate.iter_pages()
    data['pages']=queryPaginate.pages
    data['page']=queryPaginate.page
    data['per_page']=queryPaginate.per_page
    data['prev_num']=queryPaginate.prev_num
    data['next_num']=queryPaginate.next_num
    
    return render_template('users.html',**data)

#查看单个信息
@app.route('/user/<int:userid>',methods=['GET','POST'])
def user(userid):
    data['userid']=userid
    print(data)
    return render_template('user.html',form=data)

#保存单个信息
@app.route('/user/add',methods=['GET'])
def user_add():

    return render_template('user_add.html',**data)

#保存单个信息
@app.route('/user/doAdd',methods=['POST'])
def user_doadd():
    username = request.form.get('username')
    password = request.form.get('password')
    realname = request.form.get('realname')

    user = TUser(username=username,password=password,realname=realname)
    db.session.add(user)
    db.session.commit()

    return redirect(url_for('users'))

#编辑单个信息
@app.route('/user/edit/<int:userid>',methods=['GET'])
def user_edit(userid):

    currdata = db.session.query(TUser).filter(TUser.id==userid).first()
    print(currdata)
    data['currdata'] = currdata
    return render_template('user_edit.html',**data)

#编辑保存单个信息
@app.route('/user/doedit/',methods=['POST'])
def user_doedit():
    userid = request.form.get('userid') 
    username = request.form.get('username')
    password = request.form.get('password')
    realname = request.form.get('realname')

    update_dict = {}
    if(username is not None):
        update_dict['username']=username
    if(password is not None):
        update_dict['password']=password
    if(realname is not None):
        update_dict['realname']=realname            

    db.session.query(TUser).filter_by(id=userid).update(update_dict)
    db.session.commit()
    print(data)
    return redirect(url_for('users'))

#删除单个信息
@app.route('/user/del/<int:userid>',methods=['GET'])
def user_del(userid):
    db.session.query(TUser).filter_by(id=userid).delete()
    db.session.commit()    

    return redirect(url_for('users'))

#用户信息部分结束************************************

#用户设置部分结束************************************

#编辑单个信息
@app.route('/user/set/<username>',methods=['GET'])
def user_set(username):

    currdata = db.session.query(TUser).filter(TUser.username==username).first()
    print(currdata)
    data['currdata'] = currdata
    return render_template('user_set.html',**data)

#编辑保存单个信息
@app.route('/user/doset/',methods=['POST'])
def user_doset():
    userid = request.form.get('userid') 
    username = request.form.get('username')
    password = request.form.get('password')
    realname = request.form.get('realname')

    update_dict = {}
    if(username is not None):
        update_dict['username']=username
    if(password is not None):
        update_dict['password']=password
    if(realname is not None):
        update_dict['realname']=realname            

    db.session.query(TUser).filter_by(id=userid).update(update_dict)
    db.session.commit()
    print(data)
    return redirect(url_for('users'))
#用户设置部分结束************************************






@app.route('/faq',methods=['GET','POST'])
def faq():
    
    return render_template('faq.html',**data)

@app.route('/help',methods=['GET','POST'])
def help():
    
    return render_template('help.html',**data)

@app.route('/calendar',methods=['GET','POST'])
def calendar():
    
    return render_template('calendar.html',form=data)

@app.route('/media',methods=['GET','POST'])
def media():
    
    return render_template('media.html',**data)

@app.route('/theory',methods=['GET','POST'])
def theory():
    
    return render_template('theory.html',**data)

@app.route('/privacy-policy',methods=['GET','POST'])
def privacypolicy():
    
    return render_template('privacy-policy.html',**data)

@app.route('/terms-and-conditions',methods=['GET','POST'])
def termsandconditions():
    
    return render_template('terms-and-conditions.html',**data)

#系统级报警路由
@app.errorhandler(403)
def error403():
    
    return render_template('403.html',form=data)

@app.errorhandler(404)
def error404(error):
    
    return render_template('404.html',form=data)

@app.errorhandler(500)
def error500():
    
    return render_template('500.html',form=data)

@app.errorhandler(503)
def error503():
    
    return render_template('503.html',form=data)


#**********测试用路由**********

@app.route('/jsontest')
def jsonTest():
    
    jsonStr = '{"name":"张三","age":"33"}'
    jsonData = json.loads(jsonStr)
    print(jsonData['name'])
    
    data={"name":"张三","age":"33"}
    print (json.dumps(data,ensure_ascii=False))
    #return jsonify(data)
    return jsonify(name="张三",age=34)


@app.route('/mysqltest')
def mysqlTest():
    cursor = db.cursor()
    try:
        mySql = "select * from kelly limit 10"
        cursor.execute(mySql)
        data = cursor.fetchall()
    except Exception as e:
        print("Error: ",e)
    return jsonify(data)



#**********方法部分**********
#检测登录状态
def checkSignIn():
    #if(session.get('userName') is None or session.get('userName')==''):
    if('userName' not in session):
        return False
    else:
        return True


#**********通用方法部分**********


if __name__ == "__main__":
    app.run(host='0.0.0.0',debug=False,port=8088)
    #app.run(host='127.0.0.1',debug=False,port=8082)