sqlalchemy 学习-- 多表操作

一对多；一对一

# 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()