from flask import Flask, request, render_template, redirect, url_for from flask_sqlalchemy import SQLAlchemy # 实例化对象 app = Flask(__name__) app.secret_key = '123456' # 秘钥 # 配置类 class Config: # 1、链接数据库 SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://root:123456@localhost:3306/student3' # 2、控制台会打印出执行的SQL语句的详细信息 SQLALCHEMY_ECHO = True # 将配置类注册到app中 app.config.from_object(Config) # 将flask对象注册到SQLAlchemy中 db = SQLAlchemy(app) # 创建数据库模型表 # 管理员表 class AdminInfo(db.Model): __tablename__ = 'adminInfo' # column表示列 id = db.Column(db.Integer, primary_key=True) # user 字符串20 password 字符串255 user = db.Column(db.String(20)) password = db.Column(db.String(255)) # 学生表(从表) class Student(db.Model): __tablename__ = 'student' # column表示列 id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(255)) python = db.Column(db.Integer) java = db.Column(db.Integer) english = db.Column(db.Integer) # 外键 classid = db.Column(db.Integer, db.ForeignKey('class.id'), nullable=False) # 班级表(主表) class Class(db.Model): __tabalename__ = 'class' id = db.Column(db.Integer, primary_key=True, autoincrement=True) className = db.Column(db.String(255)) # 定义关系属性,建表是student不会成为数据表中的字段 student = db.relationship('Student', backref='class', lazy='dynamic', uselist=True) # 查询建表有没有成功 @app.route('/') def create_table(): db.create_all() return '建表成功' # 登录系统 @app.route('/login', methods=['POST', 'GET']) def login(): if request.method == 'GET': return render_template('login.html') elif request.method == 'POST': # 验证登录 user = request.form['user'] password = request.form['pwd'] # 从数据库中查询 result = AdminInfo.query.filter(AdminInfo.user == user, AdminInfo.password == password).first() if result: return render_template('query.html') else: return '登录失败' # 插入功能 @app.route('/add', methods=['POST', 'GET']) def add(): if request.method == 'GET': result = Class.query.all() print('result结果', result) return render_template('add.html', result=result) elif request.method == 'POST': name = request.form['name'] classid = request.form['clazz'] python = request.form['python'] java = request.form['java'] english = request.form['english'] # 实例化对象 student = Student(name=name, classid=classid, python=python, java=java, english=english) # 将student类的对象添加到数据库会话中 db.session.add(student) # 使用commit()方法从会话提交至数据库 db.session.commit() return redirect(url_for('query')) # 查询功能 @app.route('/query', methods=['POST', 'GET']) def query(): # 从数据库中查询相关信息 if request.method == 'GET': result = db.session.query(Student, Class).join(Class).all() return render_template('query.html', result=result) elif request.method == 'POST': name = request.form['name'] if name: result = db.session.query(Student, Class).join(Class).filter(Student.name == name).all() return render_template('query.html', result=result) else: result = db.session.query(Student, Class).join(Class).all() return render_template('query.html', result=result) if __name__ == '__main__': app.run()
query代码展示如下:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <h1>学生信息管理系统</h1> <form action="/query" method="post"> 按姓名查询:<input type="text" name="name" id="" value="" /> <input type="submit" value="查询"> </form> <a href="{{ url_for('add') }}">添加</a> <table border="1"> <tr> <th>学生id</th> <th>学生姓名</th> <th>班级id</th> <th>python成绩</th> <th>java成绩</th> <th>英语成绩</th> <th>班级名称</th> {% for i in result%} <tr> <th>{{ i[0].id }}</th> <th>{{ i[0].name }}</th> <th>{{ i[0].classid }}</th> <th>{{ i[0].python }}</th> <th>{{ i[0].java }}</th> <th>{{ i[0].english }}</th> <th>{{ i[1].className }}</th> {% endfor %} </table> </body> </html>
login代码登录功能代码展示如下:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <h1>学生信息管理系统</h1> <form action="/query" method="post"> 按姓名查询:<input type="text" name="name" id="" value="" /> <input type="submit" value="查询"> </form> <a href="{{ url_for('add') }}">添加</a> <table border="1"> <tr> <th>学生id</th> <th>学生姓名</th> <th>班级id</th> <th>python成绩</th> <th>java成绩</th> <th>英语成绩</th> <th>班级名称</th> {% for i in result%} <tr> <th>{{ i[0].id }}</th> <th>{{ i[0].name }}</th> <th>{{ i[0].classid }}</th> <th>{{ i[0].python }}</th> <th>{{ i[0].java }}</th> <th>{{ i[0].english }}</th> <th>{{ i[1].className }}</th> {% endfor %} </table> </body> </html>
add添加功能代码展示如下:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> </head> <body> <form action="/add" method="post"> <table border="2"> <tr> <th colspan="2">Header</th> </tr> <tr> <td>姓名</td><td><input type="text" name="name"/></td> </tr> <tr> <td>班级</td> <td> <select name="clazz"> {% for i in result %} <option value="{{ i.id }}">{{ i.className }}</option> {% endfor %} </select> </td> </tr> <tr> <td>python成绩</td><td><input type="text" name="python"/></td> </tr> <tr> <td>Java成绩</td><td><input type="text" name="java"/></td> </tr> <tr> <td>英语成绩</td><td><input type="text" name="english"/></td> </tr> <tr> <td colspan="2" align="center"><input type="submit"value="添加" /></td> </tr> </table> </form> </body> </html>