系统学习数据库day16

使用orm新增数据

from db_engine import engine
from user_models import User, UserProfile, UserAddress
from sqlalchemy.orm import Session
class MySqlOrmTest(object):
    def __init__(self):
        # 为了练习，共用一个session
        self.session = Session(bind=engine, future=True)
    def add_user(self):
        """新增用户"""
        # user1 = User(
        #     username="小小明",
        #     password="123456",
        #     real_name="张三"
        # )
        # self.session.add(user1)
        user_list = []
        for i in range(10):
            user_list.append(
                User(
                    username="user_{}".format(i),
                    password="123456",
                    real_name="用户{}".format(i)
                )
            )
        self.session.add_all(user_list)
        self.session.commit()

    def add_user_and_address(self):
        user_obj = User(
            username="大明星",
            password="123456",
            real_name="用户哈哈"
        )
        profile = UserProfile(user = user_obj, hobby="game")
        # userAddress = UserAddress(user=user_obj, area='地址1', phone_no ='12345678901')
        # backref的值
        user_obj.addresses.append(
            UserAddress(user=user_obj, area='铁岭', phone_no ='12345678901')
        )
        user_obj.addresses.append(
            UserAddress(user=user_obj, area='郑州', phone_no='12345678901')
        )
        self.session.add(user_obj)
        self.session.add(profile)
        # self.session.add(userAddress)
        self.session.commit()
if __name__ == '__main__':
    ormtest = MySqlOrmTest()
    # ormtest.add_user()
    ormtest.add_user_and_address()

使用orm查询数据

from db_engine import engine
from user_models import User, UserProfile, UserAddress
from sqlalchemy.orm import Session
from sqlalchemy import select
class MySqlOrmTest(object):
    def __init__(self):
        # 为了练习，共用一个session
        self.session = Session(bind=engine, future=True)
    def get_user_by_id(self, pk):
        user = self.session.get(User, {"id": pk})
        return user
    def get_one(self):
        stmt = select(User)
        stmt = stmt.where(User.id==12)
        row = self.session.execute(stmt).scalar_one_or_none()
        return row
    def get_more(self):
        stmt = select(User)
        rows = self.session.execute(stmt).scalars()
        return rows
    def close(self):
        self.session.close()
if __name__ == '__main__':
    ormtest = MySqlOrmTest()
    # user = ormtest.get_user_by_id(1)
    # user = ormtest.get_one()
    # print(user.username)
    # print(user.password)
    querySet = ormtest.get_more()
    for item in querySet:
        print(item.username)


    ormtest.close()

条件查询

#!/usr/bin/python
# coding = utf-8
from enum import IntEnum

from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, Integer, SmallInteger, String, \
    Enum, Date, ForeignKey
from sqlalchemy.types import CHAR
from sqlalchemy.dialects.mysql import TINYINT

Base = declarative_base()


class SexEnum(IntEnum):
    男 = 1
    女 = 2


class Student(Base):
    """ 学生信息表 """
    __tablename__ = 'school_student_info'
    id = Column(Integer, name='id', primary_key=True)
    stu_no = Column(Integer, nullable=False, unique=True, comment='学号')
    stu_name = Column(String(16), nullable=False, comment='姓名')
    sex = Column(Enum(SexEnum), comment='性别')
    age = Column(TINYINT(unsigned=True), default=0, comment='年龄',
                 doc="年龄只能是正整数")
    class_name = Column(String(10), comment='班级')
    address = Column(String(255), comment='家庭住址')
    phone_no = Column(CHAR(11), comment='电话号码')

    def __repr__(self):
        return '{}：{}'.format(self.stu_no, self.stu_name)


class Course(Base):
    """ 课程信息表 """
    __tablename__ = 'school_course_info'
    id = Column(Integer, primary_key=True)
    course_name = Column(String(64), nullable=False)
    teacher = Column(String(16))
    desc = Column(String(512))

    def __repr__(self):
        return '课程：{}'.format(self.course_name)


class StudentGrade(Base):
    """ 学生成绩表 """
    __tablename__ = 'school_student_grade'
    id = Column(Integer, primary_key=True)
    course_id = Column(Integer,  ForeignKey(Course.id), nullable=False, comment='课程ID')
    student_id = Column(Integer, ForeignKey(Student.id), nullable=False, comment='学生ID')
    score = Column(SmallInteger, nullable=False, comment='成绩')
    created_at = Column(Date, comment='考试时间')

    student = relationship(Student, backref="grade_list")
    course = relationship(Course, backref="grade_list")

    def __repr__(self):
        return '{}-{}: {}'.format(
            self.student.stu_name,
            self.course.course_name,
            self.score)


def create_table():
    """ 创建表 """
    from db_engine import engine
    Base.metadata.create_all(engine)

create_table()
def drop_table():
    """ 删除表 """
    from db_engine import engine
    Base.metadata.drop_all(engine)

测试

from db_engine import engine
from school_models import Student, Course, StudentGrade, SexEnum
from sqlalchemy.orm import Session
from sqlalchemy import select
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岁的学生信息 """
        pass
    def exercise_search_5(self):
        """ 查找所有姓“李”的学生信息 """
        pass

    def exercise_search_6(self):
        """ 查找所有姓“李”，名只有一个字的学生信息 """
        pass

    def exercise_search_7(self):
        """ 查找姓名中包含“雪”字的学生信息 """
        pass
    def exercise_search_8(self):
        """ 查询所有年龄在9岁到12岁之间的女生 """
        pass


    def exercise_search_9(self):
        """ 查询所有12岁以上的男生和9岁以下的女生 """
        pass

    def exercise_search_10(self):
        """ 查询所有 “李”姓的女生中年龄不为空的学生信息 """
        pass
    def close(self):
        self.session.close()
if __name__ == '__main__':
    ormtest = MySqlOrmTest()
    # ormtest.exercise_search_1()
    # ormtest.exercise_search_2()
    ormtest.exercise_search_3()

    ormtest.close()