一对多；一对一
# one -- many
class Students(Base):
__tablename__ = "students"
sid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
tid = Column(Integer, ForeignKey("teachers.tid")) # 声明字段值受到另一个表的字段值得约束，注意是<表名>
tec = relationship("Teachers", backref='std') # 注意只能类名，backref可以单独写，back_populates必须成对出现
def __str__(self):
return self.name
class Teachers(Base):
__tablename__ = "teachers"
tid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
def __str__(self):
return self.name
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# 添加数据/关系
# 方法一
# t_1 = Teachers(name='t_1')
# t_2 = Teachers(name='t_2')
# session.add_all([t_1, t_2])
# session.commit()
#
# s_1 = Students(name="s_1", tid=t_1.tid)
# s_2 = Students(name='s_2', tid=t_1.tid)
#
# session.add_all([s_1, s_2])
# session.commit()
# 方法二
# t_1 = Teachers(name='t_1')
# t_2 = Teachers(name='t_2')
# s_1 = Students(name="s_1")
# s_2 = Students(name='s_2')
# t_1.std=[s_1,s_2]
# t_2.std=[s_2]
# session.add_all([t_1, t_2]) # 1.会自动添加s_1,s_2; 2.会自动建立关系
# session.commit()
# 方法三
s_3 = Students(name='s_3')
t_1_q = session.query(Teachers).filter_by(name='t_1').first()
t_1_q.std.append(s_3)
session.commit()
### 删除
class Teachers(Base):
__tablename__ = "teachers"
tid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
# std = relationship("Students", back_populates='tec',cascade='all,delete-orphan') # 当使用back_populates时,级联删除标识只能写在这里；后期添加依然生效,但不建议；
def __str__(self):
return self.name
class Students(Base):
__tablename__ = "students"
sid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
tid = Column(Integer, ForeignKey("teachers.tid"))
# tec = relationship("Teachers", back_populates='std')
# tec = relationship("Teachers", backref='std',cascade='all,delete-orphan') # error
tec = relationship("Teachers", backref=backref('std',cascade='all,delete-orphan')) # 如果使用backref那么只能这种写法,同时级联删除标识写在此处,后期添加依旧有效,但不建议
def __str__(self):
return self.name
# one -- many
###非级联删除
# t_1 = Teachers(name='t1', age=18)
# session.add(t_1)
# session.commit() # 如果不提交，那么 t_1 是没有ID的，所以该对象不能使用，如果非要在不提交的情况下使用，那么可以使用query(t_1) 得到一个对象，赋予它id
# s_1 = Students(name='s1', tid=t_1.tid)
# s_2 = Students(name='s2', tid=t_1.tid)
# session.add_all([s_1, s_2])
# session.commit()
# t_1 = session.query(Teachers).filter_by(name='t1').first()
# session.delete(t_1) # 默认非级联删除.即：1.删除1的部分后,多的部分的相关字段自动置为Null;此例中：删除老师之后，学生的tid字段变为了NULL
# session.commit()
# s_1 = session.query(Students).filter_by(name='s1').first()
# s_2 = session.query(Students).filter_by(name='s2').delete()
# session.delete(s_1) # 删除多的部分之后, 1 的部分不受影响.此例中：删除学生之后,教师表不受任何影响.
# session.commit()
### 级联删除
class Students(Base):
__tablename__ = "students"
sid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
tid = Column(Integer, ForeignKey("teachers.tid")) # 声明字段值受到另一个表的字段值得约束，注意是<表名>
# tec = relationship("Teachers", backref = backref('std',cascade="delete,delete-orphan")) # 注意只能类名，backref可以单独写，back_populates必须成对出现
# tec = relationship("Teachers", backref = backref('std',cascade="all,delete-orphan")) # 注意只能类名，backref可以单独写，back_populates必须成对出现
tec = relationship("Teachers", backref = backref('std',cascade="all,delete,delete-orphan")) # 注意只能类名，backref可以单独写，back_populates必须成对出现
# tec = relationship("Teachers", backref = backref('std',cascade="delete-orphan")) # error
def __str__(self):
return self.name
class Teachers(Base):
__tablename__ = "teachers"
tid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
def __str__(self):
return self.name
# Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
#####one -- many
# t_1 = Teachers(name='t1', age=18)
# session.add(t_1)
# session.commit() # 如果不提交，那么 t_1 是没有ID的，所以该对象不能使用，如果非要在不提交的情况下使用，那么可以使用query(t_1) 得到一个对象，赋予它id
# s_1 = Students(name='s1', tid=t_1.tid)
# s_2 = Students(name='s2', tid=t_1.tid)
# session.add_all([s_1, s_2])
# session.commit()
t_1 = session.query(Teachers).filter_by(name='t1').first()
session.delete(t_1) # 当在relationship字段中设置了 cascade 属性之后，就会变为级联删除
session.commit()
### one -- one
## 在 one 一方(即没有FK字段的一方）的 relationship 增加参数 uselist=False
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String(30))
child = relationship("Child", uselist=False, back_populates="parent")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
name = Column(String(30))
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship("Parent", back_populates="child")
# Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(engine)
sess = Session()
# p_1 = Parent(name="p_1")
# sess.add(p_1)
# sess.commit()
# c_1 = Child(name="c_1", parent_id=p_1.id)
# sess.add_all([c_1])
# sess.commit()
# c_2 = Child(name='c_2',parent_id=p_1.id)
# sess.add(c_2)
# sess.commit()
# 虽然指定了 uselist为False,但是还是可以对同一个对象进行多次外键链接的。
# 虽然有多个外键链接,但是查找的时候,也只会显示第一个,并不会报错.
p_1_q = sess.query(Parent).filter_by(name='p_1').first()
print(p_1_q.name)
print(p_1_q.child)
### one -- one another
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String(3))
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", back_populates="parent")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
name = Column(String(3))
parent = relationship("Parent", back_populates="child", uselist=False)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
c_1 = Child(name="c_1")
sess.add(c_1)
sess.commit()
p_1 = Parent(name="p_1",child_id=c_1.id)
p_2 = Parent(name="p_2",child_id=c_1.id)
sess.add_all([p_1, p_2])
sess.commit()
c_1_q = sess.query(Child).filter_by(name='c_1').first()
print(c_1_q.name)
print(c_1_q.parent)
# 利用 unique 建立真正的 one -- one 关系
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String(30))
child = relationship("Child", uselist=False, back_populates="parent")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
name = Column(String(30))
parent_id = Column(Integer, ForeignKey('parent.id'),unique=True) # 通过限制外键的唯一性来真正建立一对一的关系
parent = relationship("Parent", back_populates="child")
# Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(engine)
sess = Session()
# p_1 = Parent(name="p_1")
# sess.add(p_1)
# sess.commit()
# c_1 = Child(name="c_1", parent_id=p_1.id)
# sess.add_all([c_1])
# sess.commit()
p_1_q = sess.query(Parent).filter_by(name='p_1').first()
print(p_1_q.child.name)
# c_2 = Child(name='c_2',parent_id=p_1_q.id)
# sess.add(c_2)
# sess.commit() # error 因为 unique的存在
# p_2 = Parent(name='p_2')
# sess.add(p_2)
# sess.commit()
# c_2 = Child(name='c_2',parent_id=p_2.id)
# sess.add(c_2)
# sess.commit()
# 修改关系
# t_1 = Teachers(name='t1', age=18)
# t_2 = Teachers(name='t2', age=20)
# session.add(t_1)
# session.add(t_2)
# session.commit() # 如果不提交，那么 t_1 是没有ID的，所以该对象不能使用，如果非要在不提交的情况下使用，那么可以使用query(t_1) 得到一个对象，赋予它id
# s_1 = Students(name='s1', tid=t_1.tid)
# s_2 = Students(name='s2', tid=t_1.tid)
# session.add_all([s_1, s_2])
# session.commit()
# 删除关系
# t_1_q = session.query(Teachers).filter_by(name='t1').first()
# for i in t_1_q.std: # t_1_q.std 是一个列表，我们可以以python的方式来删除其中的元素
# print(i.name)
# # s_1 = t_1_q.std.pop()
# # print(s_1.name)
# # session.commit() # 再次查看 学生表，会发现 学生 s2 的老师一栏变为了null 这说明，删除的只是 二者之间的关系
# s_2 = session.query(Students).filter_by(name='s1').first()
# t_1_q.std.remove(s_2)
# session.commit() # # 再次查看 学生表，会发现 学生 s2 的老师一栏变为了null 这说明，删除的只是 二者之间的关系
# print(len(t_1_q.std))
# 另外一种删除关系的方式 ：直接修改学生的关系属性
# s_1 = session.query(Students).filter_by(name='s1').first()
# s_1.tid = None
# session.commit()
# t_1_q = session.query(Teachers).filter_by(name='t1').first()
# for i in t_1_q.std:
# print(i.name)
# 添加关系
# 指定学生的外键来 建立学生 -教师关系
# t_2_q = session.query(Teachers).filter_by(name='t2').first()
# s_4= Students(name='s4',tid=t_2_q.tid)
# session.add(s_4)
# session.commit()
# for i in t_2_q.std:
# print(i.name)
# 另一种建立师-生关系的方式
# s_3 = Students(name='s3') # 我们并没有指定学生的id
# session.add(s_3)
# session.commit()
# t_2_q = session.query(Teachers).filter_by(name='t2').first()
# s_3_q = session.query(Students).filter_by(name='s3').first()
# print(t_2_q.name)
# print(s_3_q.name)
# for i in t_2_q.std:
# print(i.name)
# t_2_q.std.append(s_3_q) # 我们可以通过修改教师的std属性（利用列表的方式）来增加 实现学生-教师之间的关系，进而发现学生的tid属性也发生了更改
# print("___"*30)
# for i in t_2_q.std:
# print(i.name)
# session.commit()
多表查询
class Students(Base):
__tablename__ = "students"
sid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
math = Column(Integer)
tid = Column(Integer, ForeignKey("teachers.tid")) # 声明字段值受到另一个表的字段值得约束，注意是<表名>
tec = relationship("Teachers", backref='std') # 注意只能类名，backref可以单独写，back_populates必须成对出现
def __str__(self):
return self.name
class Teachers(Base):
__tablename__ = "teachers"
tid = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
def __str__(self):
return self.name
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# t_1 = Teachers(name='t1',age=28)
# t_2 = Teachers(name='t2',age=29)
# session.add_all([t_1, t_2])
# session.commit()
# s_1 = Students(name='s1',age=10,math=50,tid=t_1.tid)
# s_2 = Students(name='s2',age=20,math=70,tid=t_1.tid)
# s_3 = Students(name='s3',age=22,math=40,tid=t_2.tid)
# s_4 = Students(name='s4',age=20,math=80,tid=t_2.tid)
# session.add_all([s_1, s_2, s_3, s_4])
# session.commit()
# join 连表查询
# q_1 = session.query(Students).join(Teachers).filter(Students.math>60).all()
# q_2 = session.query(Students.name,Teachers.name).join(Teachers).filter(Students.math>60).all()
# for i in q_1:
# print(i.name)
# for i in q_2:
# print(i)
# contains
# s_1_q = session.query(Students).filter(Students.math==80).one()
# print(s_1_q)
# t_1_q = session.query(Teachers).filter(Teachers.std.contains(s_1_q)).one()
# print(t_1_q.name)
# any ==
# t_2_q = session.query(Teachers).filter(Teachers.std.any(Students.math == 80)).all()
# for i in t_2_q:
# print(i.name)
# any =
# t_3_q = session.query(Teachers).filter(Teachers.std.any(math=80)).one()
# print(t_3_q.name)
# 查找同类型的其他记录
# s_3_q = session.query(Students).filter(Students.tec.has(Teachers.tid==1)).all()
# for i in s_3_q:
# print(i.name)
# t_4_q = session.query(Teachers).filter_by(tid=1).one()
# print(t_4_q)
# s_4_q = session.query(Students).with_parent(t_4_q).all()
# for i in s_4_q:
# print(i.name)
多对多关系
第一种
tec_std = Table("tec_std_rel",
Base.metadata,
Column("tec_id", ForeignKey("teachers.tid"), primary_key=True),
Column("std_id", ForeignKey("students.sid"), primary_key=True)
)
class Teacher(Base):
__tablename__ = "teachers"
tid = Column(Integer, primary_key=True)
name = Column(String(30))
subject = Column(String(10))
students = relationship("Student",
secondary=tec_std, # 可以使用表的变量名
back_populates="teachers")
def __str__(self):
return self.name
class Student(Base):
__tablename__ = "students"
sid = Column(Integer, primary_key=True)
name = Column(String(30))
math = Column(Integer)
teachers = relationship("Teacher",
secondary="tec_std_rel", # 也可以使用表名
back_populates="students")
def __str__(self):
return self.name
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
Session = sessionmaker(engine)
sess = Session()
lilei = Teacher(name='lilei',subject='math')
hanmeimei = Teacher(name='hanmeimei', subject='chinese')
xiaohong = Student(name='xiaohong',math=80)
xiaoming = Student(name='xiaoming',math=90)
xiaogang = Student(name='xiaogang',math=70)
# 添加记录/关系
lilei.students = [xiaohong, xiaogang]
lilei.students.append(xiaoming)
hanmeimei.students.extend([xiaohong,xiaogang,xiaoming]) # 以上方式都会自动添加相关记录
sess.add(hanmeimei)
sess.add(lilei)
sess.commit()
# lilei_q = sess.query(Teacher).filter_by(name='lilei').one()
# xiaogang.teachers.append(lilei_q) # 注意：这里如果直接使用上面的变量lilei那么会在teachers表中增加新的一条记录。
# sess.add(xiaogang)
# sess.commit() # 这种方式也可以添加相关记录
# 删除关系
# lilei_q = sess.query(Teacher).filter(Teacher.subject == 'math').one()
# print(lilei_q)
# for i in lilei_q.students:
# print(i.name)
# xiaohong_q = sess.query(Student).filter(Student.name == 'xiaohong').one()
# lilei_q.students.remove(xiaohong_q)
#
# lilei_q.students.pop(1) # 以上两种方式删除的仅仅是关系
# sess.commit()
# 删除记录
# hanmeimei_q = sess.query(Teacher).filter(Teacher.name == 'hanmeimei').one()
# print(hanmeimei_q)
# sess.delete(hanmeimei_q) # 会把选中的记录及其相关关系都会删除，但是其关联的实体记录不受影响。即：只会删除教师表和关系表，学生表不受影响
# sess.commit()
# lilei_q = sess.query(Teacher).filter(Teacher.name == 'lilei').delete() # error 因为外键关系的存在，此种删除方式会报错
# sess.commit()
# 级联删除 Teacher.relationship (后期)增加cascade='all,delete-orphan', single_parent=True 参数
# hanmeimei_q = sess.query(Teacher).filter_by(name='hanmeimei').one()
# print(hanmeimei_q)
# sess.delete(hanmeimei_q) # 此种方式删除，会将Teachers中选中记录删除，同时会因为级联原因删除学生记录以及关系记录，又因为删除了学生记录，所以又会把关系表中的相关记录都删掉
# sess.commit()
# single_parent 意思是禁止多个记录与我建立关系
# order_by="TargeClass.col' 顺序排列
# order_by = '-Target.col' 逆序排列
# lilei_q = sess.query(Teacher).filter_by(name='lilei').one()
# for i in lilei_q.students:
# print(i.name+str(i.math))
第二种多对多
class TecStd(Base):
__tablename__ = "tec_std_rel"
tec_id = Column(Integer, ForeignKey("teachers.tid"), primary_key=True) # 需要把此表分别和Tec和Std两个结合着看,故需要两个primary_key
std_id = Column(Integer, ForeignKey("students.sid"), primary_key=True)
extra_data = Column(String(100))
teachers = relationship("Teacher", back_populates='students') # 此back_populates与 Teachers的bp相对应
students = relationship("Student", back_populates='teachers') # 此back_populates与Studentsde的bp相对应
class Teacher(Base):
__tablename__ = "teachers"
tid = Column(Integer, primary_key=True)
name = Column(String(30))
subject = Column(String(10))
students = relationship("TecStd", # 和第三张表相关联
back_populates="teachers",cascade='all,delete-orphan')
def __str__(self):
return self.name
class Student(Base):
__tablename__ = "students"
sid = Column(Integer, primary_key=True)
name = Column(String(30))
teachers = relationship("TecStd",
back_populates="students",cascade='all,delete-orphan')
def __str__(self):
return self.name
# Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
lilei = Teacher(name='lilei', subject='math')
hanmeimei = Teacher(name='hanmeimei', subject='chinese')
xiaohong = Student(name='xiaohong')
xiaoming = Student(name='xiaoming')
xiaogang = Student(name='xiaogang')
# lilei.students = [xiaohong, xiaoming, xiaogang] # error
# hanmeimei.students = [xiaogang, xiaoming, xiaogang] # error
# sess.add_all([lilei, hanmeimei])
# sess.commit()
# 方法一
t_1 = TecStd(extra_data='ts')
t_2 = TecStd(extra_data='ts')
t_3 = TecStd(extra_data='ts')
t_1_1 = TecStd(extra_data='ts')
t_2_1 = TecStd(extra_data='ts')
t_3_1 = TecStd(extra_data='ts')
t_1.students = xiaoming
t_2.students = xiaohong
t_3.students = xiaogang
t_1_1.students = xiaoming
t_2_1.students = xiaohong
t_3_1.students = xiaogang
print(lilei.students)
lilei.students.extend([t_1,t_2,t_3])
hanmeimei.students.extend([t_1_1, t_2_1,t_3_1])
# sess.add_all([lilei,hanmeimei])
# sess.commit()
#
# 方法二
# sess.add_all([lilei, hanmeimei, xiaohong, xiaoming, xiaogang])
# sess.commit()
# li_1 = sess.query(Teacher).filter_by(name='lilei').one()
# xiaogang_1 = sess.query(Student).filter_by(name='xiaogang').one()
# xiaohong_1 = sess.query(Student).filter_by(name='xiaohong').one()
# xiaoming_1 = sess.query(Student).filter_by(name='xiaoming').one()
# t_1 = TecStd(tec_id=li_1.tid, std_id=xiaogang_1.sid, extra_data='lg')
# t_2 = TecStd(tec_id=li_1.tid, std_id=xiaohong_1.sid, extra_data='lh')
# t_3 = TecStd(tec_id=li_1.tid, std_id=xiaoming_1.sid, extra_data='lm')
# sess.add_all([t_1, t_2, t_3])
# sess.commit()
# 删除记录
# relationship 添加了级联删除标识,就可以删除teachers了，,同时关系表中的相关记录也会被删除,但依旧不会删除students中的记录
# lilei_q = sess.query(Teacher).filter_by(name='lilei').one()
# sess.delete(lilei_q)
# sess.commit()
# xiaohong_q = sess.query(Student).filter_by(name='xiaohong').one()
# print(xiaohong_q.teachers)
# sess.delete(xiaohong_q)
# sess.commit()
# 删除关系
# 需要级联标删除识符
lilei_q = sess.query(Teacher).filter_by(name='lilei').one()
# print(lilei_q.students)
# for i in lilei_q.students:
# print(i)
# print(i.students.name)
# lilei_q.students.pop()
# sess.commit()
# 需要级联标删除识符
# xiaohong_1 = sess.query(Student).filter_by(name='xiaohong').one()
# r_1_q = sess.query(TecStd).filter(TecStd.tec_id == lilei_q.tid).filter(TecStd.std_id==xiaohong_1.sid).one()
# print(r_1_q)
# lilei_q.students.remove(r_1_q)
# sess.commit()
# 不需要级联标删除识符
# xiaohong_1 = sess.query(Student).filter_by(name='xiaohong').one()
# r_1_q = sess.query(TecStd).filter(TecStd.tec_id == lilei_q.tid).filter(TecStd.std_id==xiaohong_1.sid).one()
# print(r_1_q)
# sess.delete(r_1_q)
# sess.commit()
