1、外键的查询

第一种方式：

  1. from sqlalchemy import create_engine, ForeignKey
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker
  4. from sqlalchemy import Column, String, Integer, Enum
  6. USERNAME = "root"
  7. PASSWORD = "147258"
  8. PORT = 3306
  9. HOSTNAME = "127.0.0.1"
  10. DATABASE = "primary"
  12. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  14. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  19. class Class(Base):
  21.     __tablename__ = "class"
  23.     id = Column(Integer, primary_key=True, autoincrement=True)
  24.     Class = Column("class", Enum("一班", "二班", "三班", "四班"))
  26.     def __str__(self):
  27.         return self.Class
  30. class Student(Base):
  32.     __tablename__ = "student"
  34.     id = Column(Integer, primary_key=True, autoincrement=True)
  35.     name = Column(String(10))
  36.     # 添加外键
  37.     Class = Column("class", Integer, ForeignKey("class.id", ondelete="CASCADE"))
  40. Base.metadata.create_all()
  41. session = sessionmaker(bind=engine)()
  43. # 首先通过过滤得到目标值
  44. dates = session.query(Student).filter(Student.name == "龚祝俊").first()
  45. # 获取外键的值
  46. Class = dates.Class
  48. # 过滤出外键对应的另一张表的值
  49. result = session.query(Class).filter(Class.id == Class)
  50. print(result)

第二种方法：这里因为数据的关系选择了查询所有结果

  1. dates = session.query(Class).filter(Class.id == Student.Class)
  2. for date in dates:
  3.     print(date)

2、一对多表关系的实现

注意点：
1、需要导入relationship方法
2、两张表之间必需存在外键关系，否则无法查询
一个小例子：实现查找一个班级中的所有学生

方法一：普通思路-通过外键进行查找

  1. from sqlalchemy import create_engine, ForeignKey
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker
  4. from sqlalchemy import Column, String, Integer, Enum
  6. USERNAME = "root"
  7. PASSWORD = "147258"
  8. PORT = 3306
  9. HOSTNAME = "127.0.0.1"
  10. DATABASE = "primary"
  12. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  14. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  19. class Class(Base):
  21.     __tablename__ = "class"
  23.     id = Column(Integer, primary_key=True, autoincrement=True)
  24.     Class = Column("class", Enum("一班", "二班", "三班", "四班"))
  26.     def __str__(self):
  27.         return self.Class
  30. class Student(Base):
  32.     __tablename__ = "student"
  34.     id = Column(Integer, primary_key=True, autoincrement=True)
  35.     name = Column(String(10))
  36.     # 添加外键
  37.     Class = Column("class", Integer, ForeignKey("class.id", ondelete="CASCADE"))
  39.     def __str__(self):
  40.         return self.name
  43. Base.metadata.create_all()
  44. session = sessionmaker(bind=engine)()
  46. date = session.query(Class).filter(Class.Class == "一班").first()
  47. results = session.query(Student).filter(Student.Class == date.id)
  48. for result in results:
  49.     print(result.name)

方法二：relationship方法

  1. from sqlalchemy import create_engine, ForeignKey
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker, relationship
  4. from sqlalchemy import Column, String, Integer, Enum
  6. USERNAME = "root"
  7. PASSWORD = "147258"
  8. PORT = 3306
  9. HOSTNAME = "127.0.0.1"
  10. DATABASE = "primary"
  12. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  14. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  19. class Class(Base):
  21.     __tablename__ = "class"
  23.     id = Column(Integer, primary_key=True, autoincrement=True)
  24.     Class = Column("class", Enum("一班", "二班", "三班", "四班"))
  25.     # 定义relationship属性，关联Student表
  26.     seck_all = relationship("Student")
  28.     def __str__(self):
  29.         return self.Class
  32. class Student(Base):
  34.     __tablename__ = "student"
  36.     id = Column(Integer, primary_key=True, autoincrement=True)
  37.     name = Column(String(10))
  38.     # 添加外键
  39.     Class = Column("class", Integer, ForeignKey("class.id", ondelete="CASCADE"))
  40.     # 定义relationship属性，关联Class表
  41.     make_all = relationship("Class")
  43.     def __str__(self):
  44.         return self.name
  47. Base.metadata.create_all()
  48. session = sessionmaker(bind=engine)()
  50. dates = session.query(Class).filter(Class.Class == "一班").first()
  51. for date in dates.seck_all:
  52.     print(date)

方法三、升级版的relationship用法（backref参数,反向访问属性）

在上面的用法中分别在两张表模型中写了relationship属性，现在通过relationship属性只要写一条即可

  1. from sqlalchemy import create_engine, ForeignKey
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker, relationship
  4. from sqlalchemy import Column, String, Integer, Enum
  6. USERNAME = "root"
  7. PASSWORD = "147258"
  8. PORT = 3306
  9. HOSTNAME = "127.0.0.1"
  10. DATABASE = "primary"
  12. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  14. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  19. class Class(Base):
  21.     __tablename__ = "class"
  23.     id = Column(Integer, primary_key=True, autoincrement=True)
  24.     Class = Column("class", Enum("一班", "二班", "三班", "四班"))
  25.     # 在该表中去除seck_all属性
  26.     # seck_all = relationship("Student")
  27.     # 放在这也是一样的，只不过改下内容,其中的Student是模型名
  28.     # seck_all = relationship("Student", backref="make_all")
  30.     def __str__(self):
  31.         return self.Class
  34. class Student(Base):
  36.     __tablename__ = "student"
  38.     id = Column(Integer, primary_key=True, autoincrement=True)
  39.     name = Column(String(10))
  40.     # 添加外键
  41.     Class = Column("class", Integer, ForeignKey("class.id", ondelete="CASCADE"))
  42.     # 定义relationship属性，关联Class表，在该表中添加backref参数
  43.     make_all = relationship("Class", backref="seck_all")
  45.     def __str__(self):
  46.         return self.name
  49. Base.metadata.create_all()
  50. session = sessionmaker(bind=engine)()
  52. dates = session.query(Class).filter(Class.Class == "一班").first()
  53. for date in dates.seck_all:
  54.     print(date)

由于relationship参数对应的是单条数据，所以找寻all(）时：

  1. dates = session.query(Class).filter(XXXXX).all()
  2. for date in dates:
  3.     print(date.seck_all)

代码分析：
方法导入：

  1. from sqlalchemy.orm import relationship

写入模型字段（xxx = relationship(“对应关联表模型名”)，backref=”定义名称进行反向查询”）

  1. class Class(Base):
  3.     __tablename__ = "class"
  5.     id = Column(Integer, primary_key=True, autoincrement=True)
  6.     Class = Column("class", Enum("一班", "二班", "三班", "四班"))
  7.     # 定义seck_all属性，用以连接关联表表
  8.     seck_all = relationship("Student")
  10.     def __str__(self):
  11.         return self.Class

进行查询：
找到需要查询的条件

  1. dates = session.query(Class).filter(Class.Class == "一班").first()

调用relationship属性

  1. for date in dates.seck_all:
  2.     print(date)

当然通过学生来查询班级的操作也是没有问题的，在代码中Student模型已经写入了
make_all = relationship(“Class”)

3、关系表实现新增数据

外键约束的关系使在子表中增加主表中并不存在的主键时会报错：

方法一：分别提交，先在主表中增加添加新增数据，然后在子表中添加新增数据

由于之前选择的是Enum类型，无法添加新的数据，因此对数据进行重构

  1. from sqlalchemy import create_engine, ForeignKey
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker, relationship
  4. from sqlalchemy import Column, String, Integer, Enum
  6. USERNAME = "root"
  7. PASSWORD = "147258"
  8. PORT = 3306
  9. HOSTNAME = "127.0.0.1"
  10. DATABASE = "primary"
  12. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  14. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  19. class Class(Base):
  21.     __tablename__ = "class"
  23.     id = Column(Integer, primary_key=True, autoincrement=True)
  24.     Class = Column("class", string(10))
  26.     def __str__(self):
  27.         return self.Class
  30. class Student(Base):
  32.     __tablename__ = "student"
  34.     id = Column(Integer, primary_key=True, autoincrement=True)
  35.     name = Column(String(10))
  36.     # 添加外键
  37.     Class = Column("class", Integer, ForeignKey("class.id", ondelete="CASCADE"))
  39.     def __str__(self):
  40.         return self.name
  42. Base.metadata.create_all()
  43. session = sessionmaker(bind=engine)()
  45. Class1 = Class(Class="一班")
  46. student1 = Student(name="刘晨", Class=1)
  48. # 先提交主表数据
  49. session.add(Class1)
  50. session.commit()
  52. # 在提交子表数据
  53. session.add(student1)
  54. session.commit()

方法二，通过relationship的关联进行提交（写入backref参数的同理）

  1. from sqlalchemy import create_engine, ForeignKey
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import sessionmaker, relationship
  4. from sqlalchemy import Column, String, Integer, Enum
  6. USERNAME = "root"
  7. PASSWORD = "147258"
  8. PORT = 3306
  9. HOSTNAME = "127.0.0.1"
  10. DATABASE = "primary"
  12. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  14. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  19. class Class(Base):
  21.     __tablename__ = "class"
  23.     id = Column(Integer, primary_key=True, autoincrement=True)
  24.     Class = Column("class", Enum("一班", "二班", "三班", "四班"))
  25.     seck_all = relationship("Student")
  27.     def __str__(self):
  28.         return self.Class
  31. class Student(Base):
  33.     __tablename__ = "student"
  35.     id = Column(Integer, primary_key=True, autoincrement=True)
  36.     name = Column(String(10))
  37.     # 添加外键
  38.     Class = Column("class", Integer, ForeignKey("class.id", ondelete="CASCADE"))
  39.     make_all = relationship("Class")
  41.     def __str__(self):
  42.         return self.name
  45. Base.metadata.drop_all()
  46. Base.metadata.create_all()
  47. session = sessionmaker(bind=engine)()
  49. # 实例化Class
  50. class1 = Class(Class="一班")
  51. # 实例化Student
  52. student1 = Student(name="刘晨", Class=1)
  53. # 使Student1的make_all属性等于class1
  54. student1.make_all = class1
  55. # 添加提交student1
  56. session.add(student1)
  57. session.commit()

一般范式：（只能通过子表属性进行提交，不能用主表的属性进行提交）

  1. 子表实例化对象名.relationship属性 = 主表实例化对象名

多条数据的提交同理：

  1. # 实例化Class
  2. class1 = Class(Class="一班")
  3. # 实例化Student
  4. student1 = Student(name="刘晨", Class=1)
  5. student2 = Student(name="孜然", Class=1)
  6. student3 = Student(name="刘欣欣", Class=1)
  7. # 使Student1的make_all属性等于class1
  8. student1.make_all = class1
  9. student12make_all = class1
  10. student3.make_all = class1
  11. # 添加提交student1
  12. session.add_all([student1, student2, student3])
  13. session.commit()

4、一对一表关系的实现

一对一其实就是一对多的特殊情况（uselist=False）模型.属性.append(实例化对象)
关系如下：
image.png
代码部分：有点问题
导入from sqlalchemy.orm import backref

  1. from sqlalchemy import create_engine, ForeignKey
  2. from sqlalchemy import Column, String, Integer, Enum
  3. from sqlalchemy.ext.declarative import declarative_base
  4. from sqlalchemy.orm import relationship, sessionmaker, backref
  5. # from sqlalchemy.dialects.mysql import LONGTEXT
  8. # 定义引擎
  9. USERNAME = "root"
  10. PASSWORD = "147258"
  11. PORT = 3306
  12. HOSTNAME = "127.0.0.1"
  13. DATABASE = "primary"
  15. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  17. engine = create_engine(SLDG)
  19. Base = declarative_base(engine)
  22. class Information(Base):
  23.     __tablename__ = "student"
  25.     id = Column(Integer, primary_key=True, autoincrement=True)
  26.     name = Column(String(10), nullable=False)
  28.     make = relationship("InformationExtent", backref=backref("extent", uselist=False))
  30.     def __str__(self):
  31.         return self.name
  34. class InformationExtent(Base):
  36.     __tablename__ = "studentextent"
  38.     id = Column(Integer, primary_key=True, autoincrement=True)
  39.     age = Column(Integer)
  40.     gender = Column(Enum("男", "女"), default="男")
  41.     uid = Column(Integer, ForeignKey("student.id", ondelete="CASCADE"))
  44. Base.metadata.drop_all()
  45. Base.metadata.create_all()
  46. session = sessionmaker(bind=engine)()
  48. information = Information(name="流浪者")
  49. informationextent1 = InformationExtent(age=18, gender="男", uid=1)
  50. informationextent2 = InformationExtent(age=18, gender="男", uid=1)
  51. information.make.append(informationextent1)
  52. information.make.append(informationextent2)
  53. session.add(information)
  54. session.commit()

5、多对多表关系的实现

需要中间表来作为桥梁
image.png
一个小例子：

  1. from sqlalchemy import create_engine, ForeignKey, Table
  2. from sqlalchemy import Column, String, Integer, Enum
  3. from sqlalchemy.orm import relationship, backref, sessionmaker
  4. from sqlalchemy.ext.declarative import declarative_base
  6. # 定义引擎
  7. USERNAME = "root"
  8. PASSWORD = "147258"
  9. PORT = 3306
  10. HOSTNAME = "127.0.0.1"
  11. DATABASE = "primary"
  13. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  15. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  18. # mytable = Table("mytable", metadata,
  19. #                 Column('mytable_id', Integer, primary_key=True),
  20. #                 Column('value', String(50))
  21. #                 )
  22. MY_TABLE = Table("many-to-many", Base.metadata,
  23.                     Column("Teachers_id", Integer, ForeignKey("teachers.id")),
  24.                     Column("Classroom", Integer, ForeignKey("classroom.id"))
  25.                     )
  28. class Teachers(Base):
  29.     __tablename__ = "teachers"
  31.     id = Column(Integer, primary_key=True, autoincrement=True)
  32.     name = Column(String(10))
  33.     classroom = relationship("Classroom", backref="teacher", secondary=MY_TABLE)
  35.     def __str__(self):
  36.         return self.name
  39. class Classroom(Base):
  40.     __tablename__ = "classroom"
  42.     id = Column(Integer, primary_key=True, autoincrement=True)
  43.     name = Column(String(10))
  44.     floor = Column(Enum("1", "2", "3"))
  46.     def __str__(self):
  47.         return f"班级名：{self.name}, 楼层：{self.floor}"
  50. # Base.metadata.drop_all()
  51. Base.metadata.create_all()
  52. session = sessionmaker(bind=engine)()
  55. def add():
  56.     # 定义老师字段
  57.     teacher1 = Teachers(name="龚祝俊")
  58.     teacher2 = Teachers(name="吴艳新")
  59.     teacher3 = Teachers(name="阿吉非替尼")
  60.     # 定义教室字段
  61.     classroom1 = Classroom(name="物理教室", floor="3")
  62.     classroom2 = Classroom(name="地理教室", floor="2")
  63.     classroom3 = Classroom(name="化学教室", floor="1")
  64.     # 建立关系,通过teacher2.classroom
  65.     teacher1.classroom.append(classroom1)
  66.     teacher1.classroom.append(classroom2)
  68.     teacher2.classroom.append(classroom2)
  69.     teacher2.classroom.append(classroom3)
  70.     session.add_all([teacher1, teacher2])
  71.     # 建立关系,通过classroom3.teacher
  72.     classroom3.teacher.append(teacher3)
  73.     classroom3.teacher.append(teacher2)
  74.     session.add(classroom3)
  75.     # print(classroom3.teacher)   --> []
  76.     # print(teacher2.classroom)   --> []
  77.     session.commit()
  80. def query():
  81.     dates = session.query(Teachers).get(1)
  82.     for date in dates.classroom:
  83.         print(date)
  84.     print("-"*100)
  85.     results = session.query(Classroom).filter(Classroom.name == "化学教室").first()
  86.     for result in results.teacher:
  87.         print(result)
  90. if __name__ == '__main__':
  91.     add()
  92.     query()

代码解读：
导入Table:

  1. from sqlalchemy import Table

写入Table
一般样式

  1. mytable = Table("mytable", Base.metadata,
  2.                 Column('mytable_id', Integer, primary_key=True),
  3.                 Column('value', String(50))
  4.                 )

mytable：自行命名的Table名，在secondary参数调用时使用
“mytable”表明，在数据库中该表的名字
metadata：元数据，从定义的Base中获取，写成Base.metadata
Column:定义表中的外键与其他两张表建立关系Column(“Teachers_id”, Integer, ForeignKey(“teachers.id”)需要使用ForeignKey属性（表名.字段名）
secondary参数，在relationship方法中定义

  1.  classroom = relationship("Classroom", backref="teacher", secondary=MY_TABLE)

append方法：
多对多不像是一对多，在定义relationship属性后，只能通过子表的relationship属性才能添加新的数据，多对多能够双向的增加数据(因为此时两张表没有主次之分)

  1.     # 定义老师字段
  2.     teacher1 = Teachers(name="龚祝俊")
  3.     teacher2 = Teachers(name="吴艳新")
  4.     teacher3 = Teachers(name="阿吉非替尼")
  5.     # 定义教室字段
  6.     classroom1 = Classroom(name="物理教室", floor="3")
  7.     classroom2 = Classroom(name="地理教室", floor="2")
  8.     classroom3 = Classroom(name="化学教室", floor="1")
  9.     # 建立关系,通过teacher2.classroom
  10.     teacher1.classroom.append(classroom1)
  11.     teacher1.classroom.append(classroom2)
  13.     teacher2.classroom.append(classroom2)
  14.     teacher2.classroom.append(classroom3)
  15.     session.add_all([teacher1, teacher2])
  16.     # 建立关系,通过classroom3.teacher
  17.     classroom3.teacher.append(teacher3)
  18.     classroom3.teacher.append(teacher2)
  19.     session.add(classroom3)
  20.     # print(classroom3.teacher)   --> []
  21.     # print(teacher2.classroom)   --> []
  22.     session.commit()

小拓展：代码改写，可以但没有必要，哈哈（修改以及注意点写在代码中）

  1. from sqlalchemy import create_engine, ForeignKey, Table
  2. from sqlalchemy import Column, String, Integer, Enum
  3. from sqlalchemy.orm import relationship, backref, sessionmaker
  4. from sqlalchemy.ext.declarative import declarative_base
  6. # 定义引擎
  7. USERNAME = "root"
  8. PASSWORD = "147258"
  9. PORT = 3306
  10. HOSTNAME = "127.0.0.1"
  11. DATABASE = "primary"
  13. SLDG = f"mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8"
  15. engine = create_engine(SLDG)
  16. Base = declarative_base(engine)
  18. # 由于加入的是主键，所以需要对主键进行改写
  19. MY_TABLE = Table("many-to-many", Base.metadata,
  20.                     Column("Teachers_id", String(10), ForeignKey("teachers.name")),
  21.                     Column("Classroom_id", String(10), ForeignKey("classroom.name"))
  22.                     )
  25. class Teachers(Base):
  26.     __tablename__ = "teachers"
  28.     id = Column(Integer, autoincrement=True)
  29.     # 重新定义了主键，主键只能有一个
  30.     name = Column(String(10), primary_key=True)
  31.     classroom = relationship("Classroom", backref="teacher", secondary=MY_TABLE)
  33.     def __str__(self):
  34.         return self.name
  37. class Classroom(Base):
  38.     __tablename__ = "classroom"
  40.     id = Column(Integer)
  41.     # 重新定义了主键
  42.     name = Column(String(10), primary_key=True)
  43.     floor = Column(Enum("1", "2", "3"))
  45.     def __str__(self):
  46.         return f"班级名：{self.name}, 楼层：{self.floor}"
  49. Base.metadata.drop_all()
  50. Base.metadata.create_all()
  51. session = sessionmaker(bind=engine)()
  54. def add():
  55.     # 定义老师字段
  56.     teacher1 = Teachers(id=1, name="龚祝俊")
  57.     teacher2 = Teachers(id=2, name="吴艳新")
  58.     teacher3 = Teachers(id=3, name="阿吉非替尼")
  59.     # 定义教室字段
  60.     classroom1 = Classroom(id=1, name="物理教室", floor="3")
  61.     classroom2 = Classroom(id=2, name="地理教室", floor="2")
  62.     classroom3 = Classroom(id=2, name="化学教室", floor="1")
  63.     # 建立关系,通过teacher2.classroom
  64.     teacher1.classroom.append(classroom1)
  65.     teacher1.classroom.append(classroom2)
  67.     teacher2.classroom.append(classroom2)
  68.     teacher2.classroom.append(classroom3)
  69.     session.add_all([teacher1, teacher2])
  70.     # 建立关系,通过classroom3.teacher
  71.     classroom3.teacher.append(teacher3)
  72.     classroom3.teacher.append(teacher2)
  73.     session.add(classroom3)
  74.     # print(classroom3.teacher)   --> []
  75.     # print(teacher2.classroom)   --> []
  76.     session.commit()
  78. # 由于改写了主键，因此get()方法失效
  79. def query():
  80.     dates = session.query(Teachers).filter(Teachers.name == "吴艳新").first()
  81.     for date in dates.classroom:
  82.         print(date)
  83.     print("-"*100)
  84.     results = session.query(Classroom).filter(Classroom.name == "化学教室").first()
  85.     for result in results.teacher:
  86.         print(result)
  89. if __name__ == '__main__':
  90.     add()
  91.     query()

数据库显示：
教师表：
image.png
教室表：
image.png
中介表：
image.png