1 import sqlalchemy
2 from sqlalchemy import create_engine
3 from sqlalchemy.ext.declarative import declarative_base
4 from sqlalchemy import Column, Integer, String, Enum, ForeignKey
5 from sqlalchemy.orm import sessionmaker, relationship
6
7 engine = create_engine('mysql+pymysql://root:123456@localhost/student')
8
9 Base = declarative_base()
10
11 class Student(Base):
12 __tablename__ = 'student_info'
13
14 # 设置id, 类型为int, 不能为空, id是这张表的主键
15 id = Column(Integer, nullable=False, primary_key=True)
16 # 设置stu_id, 类型为int, 不能为空, id在这张表中的值唯一
17 stu_id = Column(Integer, nullable=False, unique=True)
18 name = Column(String(32), nullable=False, )
19 age = Column(Integer, nullable=False, )
20 gender = Column(Enum('F', 'M'), nullable=False)
21
22 # 查询结果的显示是此函数返回的格式
23 def __repr__(self):
24 return "<Student(stu_id='%s', name='%s', age='%s', gender='%s')>" % (
25 self.stu_id, self.name, self.age, self.gender)
26
27 class Study(Base):
28 __tablename__ = 'study_level'
29
30 id = Column(Integer, nullable=False, primary_key=True)
31 # 设置stu_id为study_level表的外键,与student_info表中的stu_id关联
32 stu_id = Column(Integer, ForeignKey('student_info.stu_id'))
33 mathematics = Column(Integer)
34 physics = Column(Integer)
35 chemistry = Column(Integer)
36
37 # 定义关系,可以在本类中使用属性student_info查询表student_info中的数据(以同样的条件)
38 # 也可以在Student类中使用属性study_level查询表study_level中的数据
39 student_info = relationship('Student', backref='study_level')
40
41 def __repr__(self):
42 return "<Study(name=%s, mathematics=%s, physics=%s, chemistry=%s)>" % (
43 self.student_info.name, self.mathematics, self.physics, self.chemistry)
44
45 # Base.metadata.create_all(engine)
46
47 Session = sessionmaker(engine)
48 session = Session()
49
50 # 插入4个学生信息
51 # session.add_all([Student(stu_id=10001, name='zhangsan', age=16, gender='F'),
52 # Student(stu_id=10002, name='lisi', age=17, gender='M'),
53 # Student(stu_id=10003, name='wangwu', age=16, gender='M'),
54 # Student(stu_id=10004, name='zhouliu', age=15, gender='F')])
55 #
56 # 插入考试成绩,成绩不到60分的科目需补考,再插入补考成绩
57 # session.add_all([Study(stu_id=10001, mathematics=78, physics=70, chemistry=83),
58 # Study(stu_id=10002, mathematics=87, physics=85, chemistry=92),
59 # Study(stu_id=10003, mathematics=60, physics=54, chemistry=76),
60 # Study(stu_id=10004, mathematics=52, physics=46, chemistry=44),
61 # Study(stu_id=10003, physics=68),
62 # Study(stu_id=10004, mathematics=63, physics=61, chemistry=65)])
63 # session.commit()
64
65 # 使用这种方法查询多张表,表之间可以没有任何关系
66 data = session.query(Student, Study).filter(Student.stu_id==Study.stu_id).all()
67 print(data)
68 print('\n')
69
70
71 # 使用下面的方法通过一张表查询其他表,表之间必须有外键关联
72 # 因为每个学生的信息唯一,所以使用first()
73 student = session.query(Student).filter(Student.stu_id==10003).first()
74 print(student)
75 # print(student.study_level)相当于Student.stu_id==10003时,下面的两行代码
76 # data = session.query(Study).filter(session.query(Study).filter(Student.stu_id==Study.stu_id).all()).all()
77 # print(data)
78 print(student.study_level)
79 print('\n')
80
81 # 因为一个学生可能会有多次考试记录,所以使用all()
82 score = session.query(Study).filter(Study.stu_id==10003).all()
83 print(score)
84 # print(score[0].student_info)相当于Study.stu_id==10003时
85 # 因为在student_info表中stu_id的值唯一,所以只有一条数据
86 # data = session.query(Student).filter(Study[0].stu_id==Student.stu_id).first()
87 # print(data)
88 print(score[0].student_info)