系统学习数据库day17

from db_engine import engine
from school_models import Student, Course, StudentGrade, SexEnum
from sqlalchemy.orm import Session
from sqlalchemy import select, or_, and_, func
class MySqlOrmTest(object):
    def __init__(self):
        # 为了练习，共用一个session
        self.session = Session(bind=engine, future=True)
    def exercise_search_1(self):
        """ 查询年龄大于12岁的所有学生 """
        stmt = select(Student).where(Student.age > 12)
        querySet = self.session.execute(stmt).scalars()
        # SELECT * from school_student_info where age > 12;
        for item in querySet:
            print(item)


    def exercise_search_2(self):
        """ 查询年龄在9~12之间（含）的学生信息 """
        stmt = select(Student).filter(Student.age.between(9, 12))
        querySet = self.session.execute(stmt).scalars()
        for item in querySet:
            print(item)
        pass

    def exercise_search_3(self):
        """ 查询未设置/已设置年龄的学生信息 """
        # stmt = select(Student).where(Student.age.is_(None))
        stmt = select(Student).where(Student.age.is_not(None))
        querySet = self.session.execute(stmt).scalars()
        for item in querySet:
            print(item)
        pass
    def exercise_search_4(self):
        """ 查询学生年龄在9岁和12岁的学生信息 """
        # age_list = (9, 12)
        # stmt = select(Student).where(Student.age.in_(age_list))
        stmt = select(Student).where(or_(
            Student.age == 9,
            Student.age == 12,
            ))
        querySet = self.session.execute(stmt).scalars()
        for item in querySet:
            print(item)
    def exercise_search_5(self):
        """ 查找所有姓“李”的学生信息 """
        stmt = select(Student).where(Student.stu_name.like("李%"))
        querySet = self.session.execute(stmt).scalars().all()
        for item in querySet:
            print(item)
    def exercise_search_6(self):
        """ 查找所有姓“李”，名只有一个字的学生信息 """
        stmt = select(Student).where(Student.stu_name.like("李_"))
        querySet = self.session.execute(stmt).scalars().all()
        for item in querySet:
            print(item)

    def exercise_search_7(self):
        """ 查找姓名中包含“雪”字的学生信息 """
        stmt = select(Student).where(Student.stu_name.like("%雪%"))
        querySet = self.session.execute(stmt).scalars().all()
        for item in querySet:
            print(item)


    def exercise_search_8(self):
        """ 查询所有年龄在9岁到12岁之间的女生 """
        # stmt = select(Student).where(Student.age.between(9, 12)).where(Student.sex == SexEnum.女.value)
        # stmt = select(Student).where(and_(Student.age.between(9, 12), Student.sex == SexEnum.女.value))
        stmt = select(Student).where(and_(
            Student.age>=9,
            Student.age <=12,
            Student.sex == SexEnum.女.value))
        querySet = self.session.execute(stmt).scalars()
        for item in querySet:
            print(item)


    def exercise_search_9(self):
        """ 查询所有12岁以上的男生和9岁以下的女生 """
        stmt = select(Student).where(or_(and_(
            Student.age >= 12,
            Student.sex == SexEnum.男.value)
            ,and_(
            Student.age <= 9,
            Student.sex == SexEnum.女.value
        )
        ))
        querySet = self.session.execute(stmt).scalars()
        for item in querySet:
            print(item)
        pass

    def exercise_search_10(self):
        """ 查询所有 “李”姓的女生中年龄不为空的学生信息 """
        stmt = select(Student).where(and_(
            Student.stu_name.like("李%"),
            Student.sex == SexEnum.女.value,
            Student.age.is_not(None)
        ))
        querySet = self.session.execute(stmt).scalars()
        for item in querySet:
            print(item)
        pass

    def exercise_function_1(self):
        """ 统计本班的学生人数 """
        # 方式一:
        # stmt = select(func.count()).select_from(Student)
        # # rows = self.session.execute(stmt).scalar_one()
        # rows = self.session.execute(stmt).mappings().one()
        # # print(rows)
        # print(rows['count'])
        # 方式二:
        print(self.session.query(Student).count())
        #加限制条件
        print(self.session.query(Student).filter(Student.age > 12).count())
        print(self.session.query(Student).filter_by(age=10).count())

        pass
    def exercise_function_2(self):
        """ 统计本班语文成绩的最高分/最低分 """
        stmt = select(func.max(StudentGrade.score).label('max_score'),
                      func.min(StudentGrade.score).label('min_score')
                      ).where(StudentGrade.course_id==1)
        results = self.session.execute(stmt).mappings().one()
        print(results) # {'max_score': 99, 'min_score': 40}

    def exercise_function_3(self):
        """ 统计本班语文成绩的平均分 """
        stmt = select(func.avg(StudentGrade.score).label('avg_score')).where(StudentGrade.course_id == 1)
        results = self.session.execute(stmt).mappings().one()
        print(results) # {'avg_score': Decimal('70.7708')}
        pass
    def exercise_function_4(self):
        stmt = select(func.sum(StudentGrade.score).label('sum_score'))\
                    .where(StudentGrade.student_id == 5)
        result = self.session.execute(stmt).mappings().one()
        print(result)
    def exercise_table_join(self):
        """ 查询每个学生的学号、姓名、年龄、课程、老师、成绩 """
        # 使用mapping
        # stmt = select(Student,Course, StudentGrade)\
        #     .where(Student.id== StudentGrade.student_id)\
        #     .where(Course.id== StudentGrade.course_id)
        # querySet = self.session.execute(stmt).mappings().all()
        # for item in querySet:
        #     print(item.Student.stu_no, item.Student.stu_name, item.Student.age,
        #               item.Course.course_name, item.Course.teacher,
        #               item.StudentGrade.score)
        # 使用scalars
        # 第一个使用主表
        stmt = select(StudentGrade) \
            .where(Student.id == StudentGrade.student_id) \
            .where(Course.id == StudentGrade.course_id)
        querySet = self.session.execute(stmt).scalars().all()
        for item in querySet:
            print(item.student.stu_no, item.student.stu_name, item.student.age,
                  item.course.course_name, item.course.teacher,
                  item.score)

    def exercise_distinct(self):
        """ 查找所有地址信息（去重） """
        stmt = select(Student.address).distinct()
        querySet = self.session.execute(stmt).scalars().all()
        for item in querySet:
            print(item)
    def exercise_group_1(self):
        """ 统计每个班的学生人数 """
        stmt = select(Student.class_name, func.count().label("学生人数")).group_by(Student.class_name)
        querySet = self.session.execute(stmt).mappings().all()
        for item in querySet:
            print(item)

    def exercise_group_2(self):
        """ 统计每个班男生、女生的总人数 """
        stmt = select(Student.class_name,Student.sex,  func.count().label("学生人数"))\
            .group_by(Student.class_name, Student.sex)
        querySet = self.session.execute(stmt).mappings().all()
        for item in querySet:
            print(item)
    def exercise_group_3(self):
        """ 查找三门成绩都及格（>=60）的学生 """
        # 学生及格的科目有几个，=3
        stmt = select(StudentGrade.student_id,  func.count().label("三门成绩都及格"))\
            .where(StudentGrade.score>=60).group_by(StudentGrade.student_id)\
            .having(func.count()==3)
        querySet = self.session.execute(stmt).mappings().all()
        for item in querySet:
            print(item)
    def close(self):
        self.session.close()
if __name__ == '__main__':
    ormtest = MySqlOrmTest()
    # ormtest.exercise_search_1()
    # ormtest.exercise_search_2()
    # ormtest.exercise_search_3()
    # ormtest.exercise_search_4()
    # ormtest.exercise_search_5()
    # ormtest.exercise_search_6()
    # ormtest.exercise_search_7()
    # ormtest.exercise_search_8()
    # ormtest.exercise_search_9()
    # ormtest.exercise_search_10()
    # ormtest.exercise_function_1()
    # ormtest.exercise_function_2()
    # ormtest.exercise_function_3()
    # ormtest.exercise_function_4()
    # ormtest.exercise_table_join()
    # ormtest.exercise_distinct()
    # ormtest.exercise_group_1()
    # ormtest.exercise_group_2()
    ormtest.exercise_group_3()
    ormtest.close()