from flask import Flask, render_template, request, jsonify
import pymysql
from flask_cors import CORS
app = Flask(__name__)
CORS(app)
# MySQL数据库连接配置
DATABASE_CONFIG = {
'host': '127.0.0.1',
'user': 'root',
'password': 'Your_Password',
'database': 'pythonweb',
'cursorclass': pymysql.cursors.DictCursor,
}
# 创建MySQL连接
def create_connection():
return pymysql.connect(**DATABASE_CONFIG)
# 创建users表（如果不存在）
def create_table():
conn = create_connection()
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL
)
''')
conn.commit()
conn.close()
# 创建表格（如果不存在）
create_table()
@app.route("/")
def index():
return render_template("index.html")
@app.route("/login", methods=['POST'])
def login():
data = request.json
username = data.get('Username')
password = data.get('Password')
# 在这里执行与数据库的交互逻辑，检查用户名和密码是否匹配
user = get_user(username, password)
# 在登录成功时返回一个 success 字段
if user:
return jsonify({'success': True, 'message': '登录成功'})
else:
return jsonify({'success': False, 'message': '无效的用户名或密码'}), 401
def get_user(username, password):
conn = create_connection()
cursor = conn.cursor()
cursor.execute('SELECT * FROM users WHERE username = %s AND password = %s', (username, password)) #参数化查询，避免sql注入
user = cursor.fetchone() #将查询结果这一行数据赋值给user变量，如果未查询到则为空
conn.close()
return user #返回查询得到的数据
# 创建grades表（如果不存在）
def create_grades_table():
conn = create_connection()
cursor = conn.cursor()
cursor.execute('''
create table if not exists grade(
name varchar(255) not null,
g1 int not null ,
g2 int not null ,
g3 int not null ,
g4 int not null ,
g5 int not null ,
g6 int not null
)
''')
conn.commit()
conn.close()
# 创建表格（如果不存在）
create_grades_table()
# 路由用于保存数据到数据库
@app.route("/saveDataToDatabase", methods=['POST'])
def save_data_to_database():
data = request.json
name = data.get('name')
g1 = data.get('g1')
g2 = data.get('g2')
g3 = data.get('g3')
g4 = data.get('g4')
g5 = data.get('g5')
g6 = data.get('g6')
# 在这里执行与数据库的交互逻辑，将数据保存到数据库
success = save_data_to_db(name, g1, g2, g3, g4, g5, g6)
# 响应
if success:
return jsonify({'success': True, 'message': '数据保存成功'})
else:
return jsonify({'success': False, 'message': '数据保存失败'}), 500
def save_data_to_db(name, g1, g2, g3, g4, g5, g6):
try:
conn = create_connection()
cursor = conn.cursor()
cursor.execute('INSERT INTO grade (name, g1, g2, g3, g4, g5, g6) VALUES (%s, %s, %s, %s, %s, %s, %s)',
(name, g1, g2, g3, g4, g5, g6))
conn.commit()
conn.close()
return True
except Exception as e:
print(f"Error saving data to database: {e}")
return False
# 跳转hall.html界面的路由函数
@app.route("/hall")
def hall():
return render_template("hall.html")
# 跳转gradeList.html界面的路由函数
@app.route("/gradeList")
def gL():
return render_template("gradeList.html")
@app.route("/get_student_data")
def get_student_data():
conn = create_connection()
cursor = conn.cursor()
cursor.execute('SELECT name, g1, g2, g3, g4, g5, g6 FROM grade')
student_data = cursor.fetchall()
conn.close()
return jsonify({"data": student_data})
# 删除路由
# 路由用于删除数据
@app.route("/deleteRecord", methods=['POST'])
def delete_record():
data = request.json
name = data.get('name')
g1 = data.get('g1')
g2 = data.get('g2')
# 添加其他成绩项
# 在这里执行与数据库的交互逻辑，将数据从数据库中删除
success = delete_record_from_db(name, g1, g2)
# 响应
if success:
return jsonify({'success': True, 'message': '数据删除成功'})
else:
return jsonify({'success': False, 'message': '数据删除失败'}), 500
def delete_record_from_db(name, g1, g2):
try:
conn = create_connection()
cursor = conn.cursor()
cursor.execute('DELETE FROM grade WHERE name = %s AND g1 = %s AND g2 = %s',
(name, g1, g2))
conn.commit()
conn.close()
return True
except Exception as e:
print(f"Error deleting data from database: {e}")
return False
# 路由用于更新数据
@app.route("/updateRecord", methods=['POST'])
def update_record():
data = request.json
name = data.get('name')
g1 = data.get('g1')
g2 = data.get('g2')
g3 = data.get('g3')
g4 = data.get('g4')
g5 = data.get('g5')
g6 = data.get('g6')
# 在这里执行与数据库的交互逻辑，将数据更新到数据库
success = update_record_in_db(name, g1, g2, g3, g4, g5, g6)
# 响应
if success:
return jsonify({'success': True, 'message': '数据更新成功'})
else:
return jsonify({'success': False, 'message': '数据更新失败'}), 500
def update_record_in_db(name, g1, g2, g3, g4, g5, g6):
try:
conn = create_connection()
cursor = conn.cursor()
cursor.execute('UPDATE grade SET g1=%s, g2=%s, g3=%s, g4=%s, g5=%s, g6=%s WHERE name=%s',
(g1, g2, g3, g4, g5, g6, name))
conn.commit()
conn.close()
return True
except Exception as e:
print(f"Error updating data in database: {e}")
return False
# 路由用于职业规划
@app.route("/careerPlan", methods=['POST'])
def career_plan():
data = request.json
g6 = data.get('g6')
# 根据成绩6进行职业规划
career = plan_career(int(g6))
# 响应
return jsonify({'success': True, 'message': career})
# 根据成绩6进行职业规划
def plan_career(g6):
if g6 > 100:
return '黑客'
elif 80 <= g6 <= 100:
return '算法设计师'
elif 60 <= g6 < 80:
return '运维工程师'
elif 40 <= g6 < 60:
return '电脑维修工'
else:
return '板砖'
@app.route("/sa")
def sa():
conn = create_connection()
cursor = conn.cursor()
cursor.execute('SELECT name, g1, g2, g3, g4, g5, g6 FROM grade')
students = cursor.fetchall()
# 计算最高分、最低分和平均分
max_grades = {'g1': max(student['g1'] for student in students),
'g2': max(student['g2'] for student in students),
'g3': max(student['g3'] for student in students),
'g4': max(student['g4'] for student in students),
'g5': max(student['g5'] for student in students),
'g6': max(student['g6'] for student in students)}
min_grades = {'g1': min(student['g1'] for student in students),
'g2': min(student['g2'] for student in students),
'g3': min(student['g3'] for student in students),
'g4': min(student['g4'] for student in students),
'g5': min(student['g5'] for student in students),
'g6': min(student['g6'] for student in students)}
avg_grades = {'g1': sum(student['g1'] for student in students) / len(students),
'g2': sum(student['g2'] for student in students) / len(students),
'g3': sum(student['g3'] for student in students) / len(students),
'g4': sum(student['g4'] for student in students) / len(students),
'g5': sum(student['g5'] for student in students) / len(students),
'g6': sum(student['g6'] for student in students) / len(students)}
conn.close()
return render_template("sa.html", students=students, max_grades=max_grades, min_grades=min_grades,
avg_grades=avg_grades)
if __name__ == '__main__':
app.run(debug=True)
