python用那些数据模型 python建立数据模型

PythonORM操作数据库01

数据表模型

crm_conn_exam.py

from sqlalchemy import create_engine
from urllib.parse import quote_plus


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


ip_port = 'xxx.xx.xxx.xxx:pppp'
username = 'uuuu'
passwd = 'uuuuu@232e2e2'
base_name = 'dbname_mysql'
# 第一步：准备链接
DB_URI = f"mysql+pymysql://{username}:{quote_plus(passwd)}@{ip_port}/{base_name}?charset=utf8mb4"

engine = create_engine(DB_URI,echo=True) # echo=True 会打印执行日志

# 第二步：声明ORM模型基类

Base = declarative_base()

# 第三步实现ORM模型


# class SexEnum(IntEnum):
#     MAN = 1     # 男
#     WOMEN = 2   # 女

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

# class Gender(Enum):
#     Male = "男"
#     Female = "女"
#     Unknown = "保密"

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), default=None, 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)

# 第四步同步数据库表
# 创建数据库DBA
# 创建表
# 这里可以单独写个py文件
#from crm_conn_exam import Base,engine
#Base.metadata.create_all(engine)
def create_table():
    """同步数据库表"""
    Base.metadata.create_all(engine)


#删除表
#Base.metadata.drop_all(engine)


def drop_table():
    """ 删除表 """
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    drop_table()
    create_table()

表数据

-- ----------------------------
-- Records of school_course_info
-- ----------------------------
INSERT INTO `school_course_info` (`id`, `course_name`, `teacher`, `desc`) VALUES (1, '语文', '王老师', '人教版三年级语文');
INSERT INTO `school_course_info` (`id`, `course_name`, `teacher`, `desc`) VALUES (2, '数学', '李老师', '人教版三年级数学');
INSERT INTO `school_course_info` (`id`, `course_name`, `teacher`, `desc`) VALUES (3, '英语', '吴老师', '人教版三年级英语');

-- ----------------------------
-- Records of school_student_info
-- ----------------------------

INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) 
VALUES (1, 1001, '李明', '男', 12, '三年级一班', '希望小区', '13300000000');
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (2, 1002, '王浩', '男', 12, '三年级一班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (3, 1003, '刘宇', '男', 11, '三年级一班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (4, 1004, '李思', '女', 12, '三年级一班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (5, 1005, '张三', '男', 11, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (6, 1006, '张虹', '女', 12, '三年级三班', '红星小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (7, 1007, '张武', '男', 10, '三年级一班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (8, 1008, '张乐', '女', 12, '三年级二班', '新世界花园万象苑', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (9, 1009, '李红', '女', 12, '三年级三班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (10, 1010, '王俊杰', '男', 11, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (11, 1011, '李四', '男', 12, '三年级一班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (12, 1012, '王五', '男', 12, '三年级二班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (13, 1013, '王武', '男', 11, '三年级一班', '红星小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (14, 1014, '李珊', '女', NULL, '三年级三班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (15, 1015, '刘文秀', '女', 12, '三年级一班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (16, 1016, '陈雪松', '男', NULL, '三年级三班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (17, 1017, '陈雪梅', '女', 8, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (18, 1018, '李雨辰', '女', 15, '三年级一班', '红星小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (19, 1019, '韩宏昌', '女', 14, '三年级三班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (20, 1020, '赵宏', '男', 8, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (21, 1021, '陶然', '女', NULL, '三年级二班', '红星小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (22, 1022, '唐可人', '女', 13, '三年级三班', '新世界花园万象苑', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (23, 1023, '李思涵', '女', NULL, '三年级一班', '红星小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (24, 1024, '周鑫', '男', 10, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (25, 1025, '陆文杰', '男', 14, '三年级三班', '新世界花园万象苑', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (26, 1026, '田心', '男', NULL, '三年级一班', '红星小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (27, 1027, '吴月', '女', 9, '三年级三班', '新世界花园万象苑', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (28, 1028, '李雪', '女', 11, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (29, 1029, '陈阳', '男', 11, '三年级一班', '红星小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (30, 1030, '林富贵', '男', 10, '三年级二班', '新世界花园万象苑', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (31, 1031, '蔡雅丽', '女', NULL, '三年级一班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (32, 1032, '黄芳', '女', 9, '三年级三班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (33, 1033, '黄建军', '男', NULL, '三年级一班', '新世界花园万象苑', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (34, 1034, '李兆丰', '男', 10, '三年级三班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (35, 1035, '庞大和', '男', 13, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (36, 1036, '高星星', '女', 9, '三年级一班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (37, 1037, '刘艳芳', '女', NULL, '三年级三班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (38, 1038, '胡慧', '男', NULL, '三年级二班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (39, 1039, '孙长城', '男', 9, '三年级三班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (40, 1040, '高先大', '男', 11, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (41, 1041, '吕辉', '男', 10, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (42, 1042, '龙城', '男', NULL, '三年级三班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (43, 1043, '崔浩', '男', 9, '三年级三班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (44, 1044, '刘雯', '女', NULL, '三年级二班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (45, 1045, '周成伟', '男', 10, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (46, 1046, '蒋欣花', '女', 9, '三年级三班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (47, 1047, '孙健', '男', 11, '三年级三班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (48, 1048, '郑晓月', '女', 10, '三年级二班', '希望小区', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (49, 1049, '王鹏', '男', 12, '三年级一班', '深航御花园', NULL);
INSERT INTO `school_student_info` (`id`, `stu_no`, `stu_name`, `sex`, `age`, `class_name`, `address`, `phone_no`) VALUES (50, 1050, '沈小姐', '女', 9, '三年级三班', '深航御花园', NULL);


-- ----------------------------
-- Records of school_student_grade
-- ----------------------------

INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (1, 1, 1, 98, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (2, 1, 2, 87, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (3, 1, 3, 96, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (4, 1, 4, 79, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (5, 1, 5, 97, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (6, 1, 6, 42, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (7, 1, 7, 64, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (8, 1, 8, 48, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (9, 1, 9, 69, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (10, 1, 10, 97, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (11, 1, 11, 88, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (12, 1, 12, 89, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (13, 1, 13, 92, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (14, 1, 14, 87, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (15, 1, 15, 94, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (16, 1, 16, 69, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (17, 1, 17, 55, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (18, 1, 18, 76, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (19, 1, 19, 43, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (20, 1, 20, 74, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (21, 1, 21, 56, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (22, 1, 22, 56, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (23, 1, 23, 88, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (24, 1, 24, 70, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (25, 1, 25, 63, '2000-07-10');
INSERT INTO `school_student_grade` (`id`, `course_id`, `student_id`, `score`, `created_at`) VALUES (26, 1, 26, 62, '2000-07-10');