### 使用 Python 和 MySQL 实现教师和学生端功能 #### 连接数据库并创建表结构 为了实现教师和学生端的功能，首先需要建立与MySQL数据库的连接，并设计相应的表结构。可以使用`mysql-connector-python`库来完成这些操作[^3]。 ```python import mysql.connector # 创建连接对象 conn = mysql.connector.connect( host="localhost", user="root", # 用户名 password="password", # 密码 database="school_db" ) cursor = conn.cursor() # 创建教师表 create_teacher_table_query = """ CREATE TABLE IF NOT EXISTS teachers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), subject VARCHAR(100) ); """ cursor.execute(create_teacher_table_query) # 创建学生表 create_student_table_query = """ CREATE TABLE IF NOT EXISTS students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), grade INT, teacher_id INT, FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE ON UPDATE CASCADE ); """ cursor.execute(create_student_table_query) ``` #### 插入数据到表格中 当建立了基本的数据模型之后，就可以向其中添加一些初始记录了。这里展示如何插入新的教师以及分配给他们的学生信息[^2]。 ```python insert_teacher_data = "INSERT INTO teachers (name, subject) VALUES (%s,%s)" teachers_info = [ ('张三', '语文'), ('李四', '数学') ] for teacher in teachers_info: cursor.execute(insert_teacher_data, teacher) student_insertion_query = """INSERT INTO students(name, grade, teacher_id) VALUES(%s,%s,%s);""" students_info = [ ("王五", 85, 1), # 属于第一个老师的学生 ("赵六", 90, 2), # 属于第二个老师的学生 ] for student in students_info: cursor.execute(student_insertion_query, student) conn.commit() print("Data inserted successfully.") ``` #### 查询特定条件下的数据 对于教师来说，可能想要查看自己所教班级的成绩情况；而对于管理员，则可能是统计全校各科目的平均分等。下面的例子展示了怎样获取某位具体老师的全部学生成绩列表[^1]。 ```sql SELECT s.name AS Student_Name, t.subject as Subject, s.grade FROM students s JOIN teachers t ON s.teacher_id=t.id WHERE t.name='张三'; ``` 此 SQL 可以转换成如下 Python 代码： ```python query_specific_teacher_students_grades = """ SELECT s.name AS Student_Name, t.subject as Subject, s.grade FROM students s JOIN teachers t ON s.teacher_id=t.id WHERE t.name=%s; """ teacher_name = '张三' cursor.execute(query_specific_teacher_students_grades, (teacher_name,)) results = cursor.fetchall() if results: for row in results: print(f'Student Name: {row[0]}, Subject: {row[1]}, Grade: {row[2]}') else: print('No records found.') ``` 以上就是利用Python配合MySQL构建简单师生管理系统的核心部分。当然实际应用中还需要考虑更多细节比如异常处理、安全性等问题，在此基础上进一步完善系统功能。