python - sqlalchemy实现MySQL增删改查

比眉伴天荒 2024-03-22 08:29 89阅读 0赞

## 导入MySQL ##

/*
     Navicat Premium Data Transfer
    
     Source Server         : localhost
     Source Server Type    : MySQL
     Source Server Version : 80031
     Source Host           : localhost:3306
     Source Schema         : py_test
    
     Target Server Type    : MySQL
     Target Server Version : 80031
     File Encoding         : 65001
    
     Date: 24/04/2023 15:58:23
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for students
    -- ----------------------------
    DROP TABLE IF EXISTS `students`;
    CREATE TABLE `students`  (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `age` int NULL DEFAULT NULL,
      `gender` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `major` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `gpa` float NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of students
    -- ----------------------------
    INSERT INTO `students` VALUES (1, '艾丽斯', 22, '女', '计算机科学', 3.5);
    INSERT INTO `students` VALUES (2, '鲍勃', 20, '男', '生物学', 3.2);
    INSERT INTO `students` VALUES (3, '查理', 19, '男', '数学', 3.9);
    INSERT INTO `students` VALUES (4, '黛安娜', 21, '女', '化学', 3.8);
    INSERT INTO `students` VALUES (5, '艾玛', 22, '女', '物理学', 3.6);
    INSERT INTO `students` VALUES (6, '弗兰克', 20, '男', '英语', 3.1);
    INSERT INTO `students` VALUES (7, '格蕾丝', 21, '女', '艺术', 3.7);
    INSERT INTO `students` VALUES (8, '亨利', 22, '男', '历史学', 3.4);
    INSERT INTO `students` VALUES (9, '伊莎贝拉', 19, '女', '社会学', 3.3);
    INSERT INTO `students` VALUES (10, '杰克', 20, '男', '政治学', 3);
    INSERT INTO `students` VALUES (11, '凯蒂', 21, '女', '心理学', 3.6);
    INSERT INTO `students` VALUES (12, '里奥', 20, '男', '物理学', 3.9);
    INSERT INTO `students` VALUES (13, '玛吉', 19, '女', '生物学', 3.2);
    INSERT INTO `students` VALUES (14, '内森', 21, '男', '计算机科学', 3.7);
    INSERT INTO `students` VALUES (15, '奥利维亚', 22, '女', '工程学', 3.8);
    INSERT INTO `students` VALUES (16, '皮特', 20, '男', '化学', 3.5);
    INSERT INTO `students` VALUES (17, '秦淮', 21, '男', '地理信息科学', 3.2);
    INSERT INTO `students` VALUES (18, '任洋', 20, '女', '计算机应用技术', 3.1);
    INSERT INTO `students` VALUES (19, '孙悟空', 22, '男', '文学', 3.4);
    INSERT INTO `students` VALUES (20, '唐三藏', 19, '男', '历史', 3.5);
    INSERT INTO `students` VALUES (21, '猪八戒', 21, '男', '哲学', 3.3);
    INSERT INTO `students` VALUES (22, '白骨精', 20, '女', '经济学', 3.8);
    INSERT INTO `students` VALUES (23, '红孩儿', 19, '男', '数学', 3.6);
    INSERT INTO `students` VALUES (24, '铁扇公主', 22, '女', '外语', 3.1);
    INSERT INTO `students` VALUES (25, '牛魔王', 21, '男', '计算机科学', 3.9);
    
    SET FOREIGN_KEY_CHECKS = 1;

## 实现增删改查 ##

from sqlalchemy import create_engine, Column, Integer, String, Float
    from sqlalchemy.orm import sessionmaker, declarative_base
    
    Base = declarative_base()
    engine = create_engine("mysql+pymysql://root:123321@localhost/py_test?charset=utf8mb4")
    Session = sessionmaker(bind=engine)
    
    
    class Student(Base):
        __tablename__ = "students"
    
        id = Column(Integer, primary_key=True)
        name = Column(String(50))
        age = Column(Integer)
        gender = Column(String(10))
        major = Column(String(50))
        gpa = Column(Float)
    
        def __str__(self):
            return "Student (ID: {}, Name: {}, Age: {}, Gender: {}, Major: {}, GPA: {})".format(
                self.id, self.name, self.age, self.gender, self.major, self.gpa)
    
    
    # 查询所有学生
    print("查询所有学生")
    with Session() as session:
        result = session.query(Student).all()
        for student in result:
            print(str(student))
    
    # 分页查询学生
    print("分页查询学生")
    with Session() as session:
        page_size = 10  # 每页数据条数
        page_num = 1  # 当前页数
        offset = (page_num - 1) * page_size  # 数据偏移量
        result = session.query(Student).offset(offset).limit(page_size).all()
        for student in result:
            print(str(student))
    
    # 按条件查询学生
    print("按条件查询学生")
    with Session() as session:
        result = session.query(Student).filter(Student.major == "计算机科学", Student.gpa > 3.5).all()
        for student in result:
            print(str(student))
    
    print("新增一个蔡徐坤")
    with Session() as session:
        new_student = Student(name="蔡徐坤", age=250, gender="未知", major="唱跳", gpa=4.0)
        session.add(new_student)
        session.commit()
        print("蔡徐坤 已被加入练习生:", str(new_student))
    
    print("按条件查询蔡徐坤")
    with Session() as session:
        result = session.query(Student).filter(Student.name == "蔡徐坤").all()
        for student in result:
            print(str(student))
    
    print("修改蔡徐坤的信息")
    with Session() as session:
        student_to_update = session.query(Student).filter_by(name="蔡徐坤").first()
        if student_to_update:
            student_to_update.major = "rap篮球"
            session.commit()
            print("蔡徐坤 修改了爱好:", str(student_to_update))
        else:
            print("蔡徐坤找不到了")
    
    print("按条件查询蔡徐坤")
    with Session() as session:
        result = session.query(Student).filter(Student.name == "蔡徐坤").all()
        for student in result:
            print(str(student))
    
    print("删除一个蔡徐坤")
    with Session() as session:
        student_to_delete = session.query(Student).filter_by(name="蔡徐坤").first()
        if student_to_delete:
            session.delete(student_to_delete)
            session.commit()
            print("蔡徐坤 练习了两年半毕业了:", str(student_to_delete), "你鸡哥厉不厉害！")
        else:
            print("蔡徐坤找不到了")
    
    print("按条件查询蔡徐坤")
    with Session() as session:
        result = session.query(Student).filter(Student.name == "蔡徐坤").all()
        for student in result:
            print(str(student))
        if result:
            pass
        else:
            print("找不到蔡徐坤")

## 部分实现效果 ##

![在这里插入图片描述][4fd7115a828c4a0788c1f42201f1a453.png]

[4fd7115a828c4a0788c1f42201f1a453.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/101cfbc03eb34859aec1a264b8f3b084.png