pymysql--练习3

需求：

在sql创建学生表、班级表、考试成绩表

题目1：使用pymysql查询考试成绩都大于80分以上的学员信息，查询结果存储在列表中，列表中存储学生对象，遍历列表输出。

题目2：使用pymysql查询所有学生信息，以及他们的班级信息。查询结果存储在一个列表中，列表中存储学生对象，学生对象包含一个班级对象属性，存储班级信息。遍历列表输出。

题目3：使用pymysql查询所有学生信息，以及他们的考试成绩。查询结果存在一个字典中，key是学生姓名，value是一个字典对象，这个value字典的key是科目名称，value是成绩。

输出外层字典，格式如下：{“姓名”:{“科目1”:”成绩”,”科目2”:”成绩”} , ..}

在sql创建学生表、班级表、考试成绩表

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

-- 创建班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(255) NOT NULL
);

-- 创建考试成绩表
CREATE TABLE exam_scores (
    score_id INT PRIMARY KEY,
    student_id INT,
    exam_subject VARCHAR(255) NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

-- 插入班级数据
INSERT INTO classes (class_id, class_name) VALUES
(1, 'Class A'),
(2, 'Class B'),
(3, 'Class C');

-- 插入学生数据
INSERT INTO students (student_id, student_name, class_id) VALUES
(101, 'Alice', 1),
(102, 'Bob', 1),
(103, 'Charlie', 2),
(104, 'David', 2),
(105, 'Eva', 3);

-- 插入考试成绩数据
INSERT INTO exam_scores (score_id, student_id, exam_subject, score) VALUES
(1, 101, 'Math', 90),
(2, 101, 'English', 85),
(3, 102, 'Math', 78),
(4, 102, 'English', 92),
(5, 103, 'Math', 88),
(6, 103, 'English', 75),
(7, 104, 'Math', 95),
(8, 104, 'English', 80),
(9, 105, 'Math', 87),
(10, 105, 'English', 91);

 

题目1：使用pymysql查询考试成绩都大于80分以上的学员信息，查询结果存储在列表中，列表中存储学生对象，遍历列表输出。

#题目1：使用pymysql查询考试成绩都大于80分以上的学员信息，查询结果存储在列表中，列表中存储学生对象，遍历列表输出。
import pymysql

# 定义学生类
class Student:
    def __init__(self, id, name,Classes=None):
        self.id = id
        self.name = name
        self.Classes = Classes

    def __repr__(self):
        return f"id = {self.id}, name = {self.name}, Classes = {self.Classes}"

class Classes:

    def __init__(self, class_id, class_name):
        self.class_id = class_id
        self.class_name = class_name

    def __repr__(self):
        return f"class_id = {self.class_id}, class_name = {self.class_name}"

db_connection = pymysql.connect(
    host="hadoop11",
    port=3306,
    user="root",
    password="123456",
    database="test1"
)
db_cursor = db_connection.cursor()
db_cursor.execute("""
    select t1.student_id,t1.student_name
    from students t1 inner join (
        select student_id from exam_scores 
        group by student_id 
        having min(score) >= 80
    )t2 on t1.student_id = t2.student_id
""")
list1 = db_cursor.fetchall()  --使用fetchall()方法获取查询结果的所有行，并存储在list1中。每一行都是一个包含学生ID和姓名的元组。
list2 = []
for a1 in list1:
    list2.append(Student(a1[0],a1[1]))   --遍历list1中的每一行，将每行的ID和姓名作为参数创建Student对象，并添加到list2中。
print(list2)

 

题目2：使用pymysql查询所有学生信息，以及他们的班级信息。查询结果存储在一个列表中，列表中存储学生对象，学生对象包含一个班级对象属性，存储班级信息。遍历列表输出。

 

import pymysql

# 定义学生类
class Student:
    def __init__(self, id, name,Classes=None):
        self.id = id
        self.name = name
        self.Classes = Classes

    def __repr__(self):
        return f"id = {self.id}, name = {self.name}, Classes = {self.Classes}"

class Classes:

    def __init__(self, class_id, class_name):
        self.class_id = class_id
        self.class_name = class_name

    def __repr__(self):
        return f"class_id = {self.class_id}, class_name = {self.class_name}"

db_connection = pymysql.connect(
    host="hadoop11",
    port=3306,
    user="root",
    password="123456",
    database="test1"
)

db_cursor = db_connection.cursor()
db_cursor.execute("""
    select t1.student_id , t1.student_name,t2.class_id,t2.class_name
    from students t1 left join classes t2 
    on t1.class_id = t2.class_id
""")
list3 = db_cursor.fetchall()
list4 = []
for a1 in list3:
    list4.append(Student(a1[0],a1[1],Classes(a1[2],a1[3])))
print(list4)

题目3：使用pymysql查询所有学生信息，以及他们的考试成绩。查询结果存在一个字典中，key是学生姓名，value是一个字典对象，这个value字典的key是科目名称，value是成绩。

输出外层字典，格式如下：{“姓名”:{“科目1”:”成绩”,”科目2”:”成绩”} , ..}

 

import pymysql

# 定义学生类
class Student:
    def __init__(self, id, name,Classes=None):
        self.id = id
        self.name = name
        self.Classes = Classes

    def __repr__(self):
        return f"id = {self.id}, name = {self.name}, Classes = {self.Classes}"

class Classes:

    def __init__(self, class_id, class_name):
        self.class_id = class_id
        self.class_name = class_name

    def __repr__(self):
        return f"class_id = {self.class_id}, class_name = {self.class_name}"

db_connection = pymysql.connect(
    host="hadoop11",
    port=3306,
    user="root",
    password="123456",
    database="test1"
)
db_cursor = db_connection.cursor()
db_cursor.execute("""
    select t1.student_name,t2.exam_subject,t2.score
    from students t1 inner join exam_scores t2
    on t1.student_id = t2.student_id
""")
list5 = db_cursor.fetchall()
dict1 = {}
--遍历list5列表中的每个元素（每个元素都是一个元组），分别提取学生姓名、考试科目和成绩。
for a1 in list5:
    student_name = a1[0]
    exam_subject = a1[1]
    score = a1[2]
--检查当前学生姓名是否已经作为键存在于dict1字典中。
如果存在，则从dict1中获取与该学生姓名对应的字典（dict2），并在该字典中添加或更新考试科目和成绩。
如果不存在，则在dict1中为该学生姓名创建一个新的字典，并设置考试科目和成绩。
    if student_name in dict1:
        dict2 = dict1[student_name]
        dict2[exam_subject] = score
    else:
        dict1[student_name] = {exam_subject:score}

print(dict1)