一,创建表及关系relationship

1 from sqlalchemy.ext.declarative import declarative_base 2 3 Base = declarative_base() 4 5 # 这次我们要多导入一个 ForeignKey 字段了,外键关联对了 6 from sqlalchemy import Column,Integer,String,ForeignKey 7 # 还要从orm 中导入一个 relationship 关系映射 8 from sqlalchemy.orm import relationship 9 10 class ClassTable(Base): 11 __tablename__="classtable" 12 id = Column(Integer,primary_key=True) 13 name = Column(String(32),index=True) 14 15 class Student(Base): 16 __tablename__="student" 17 id=Column(Integer,primary_key=True) 18 name = Column(String(32),index=True) 19 20 # 关联字段,让class_id 与 class 的 id 进行关联,主外键关系(这里的ForeignKey一定要是表名.id不是对象名) 21 class_id = Column(Integer,ForeignKey("classtable.id")) 22 23 # 将student 与 classtable 创建关系 这个不是字段,只是关系,backref是反向关联的关键字 24 to_class = relationship("ClassTable",backref = "stu2class") 25 26 from sqlalchemy import create_engine 27 engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/many_table?charset=utf8") 28 29 Base.metadata.create_all(engine) 30 31 my_ForeignKey.py
二, 增删改查
(1), 增加数据

1 from my_ForeignKey import Student, ClassTable,engine 2 # 创建连接 3 from sqlalchemy.orm import sessionmaker 4 # 创建数据表操作对象 sessionmaker 5 DB_session = sessionmaker(engine) 6 db_session = DB_session() 7 8 # 增加数据 9 # 1.简单增加数据 10 # 添加两个班级: 11 # db_session.add_all([ 12 # ClassTable(name="01"), 13 # ClassTable(name="02") 14 # ]) 15 # db_session.commit() 16 # 添加一个学生 DragonFire 班级是 01 17 # 查询要添加到的班级 18 # class_obj = db_session.query(ClassTable).filter(ClassTable.name == "01").first() 19 # 创建学生 20 # stu = Student(name="liang",class_id = class_obj.id) 21 # db_session.add(stu) 22 # db_session.commit() 23 24 # 2. relationship版 添加数据 25 # 通过关系列 to_class 可以做到两件事 26 # 第一件事 在ClassTable表中添加一条数据 27 # 第二件事 在Student表中添加一条数据并将刚刚添加的ClassTable的数据id填写在Student的class_id中 28 # stu_cla = Student(name="liang",to_class=ClassTable(name="01")) 29 # print(stu_cla.name,stu_cla.class_id) 30 # db_session.add(stu_cla) 31 # db_session.commit() 32 33 # 3.relationship版 反向添加数据 34 # 首先建立ClassTable数据 35 class_obj = ClassTable(name="02") 36 # 通过class_obj中的反向关联字段backref - stu2class 37 # 向 Student 数据表中添加 2条数据 并将 2条数据的class_id 写成 class_obj的id 38 # class_obj.stu2class = [Student(name="BMW"),Student(name="Audi")] 39 # db_session.add(class_obj) 40 # db_session.commit() 41 42 # 关闭连接 43 db_session.close() 44 45 orm_ForeignKey_insert.py
(2),查询数据

1 from my_ForeignKey import Student, ClassTable,engine 2 3 from sqlalchemy.orm import sessionmaker 4 DB_session = sessionmaker(engine) 5 db_session = DB_session() 6 7 # 1.查询所有数据,并显示班级名称,连表查询 8 student_list = db_session.query(Student).all() 9 for row in student_list: 10 # row.to_class.name 通过Student对象中的关系字段relationship to_class 获取关联 ClassTable中的name 11 print(row.name,row.to_class.name,row.class_id) 12 13 # 2.反向查询 14 class_list = db_session.query(ClassTable).all() 15 for row in class_list: 16 for row2 in row.stu2class: 17 print(row.name,row2.name) 18 # row.stu2class 通过 backref 中的 stu2class 反向关联到 Student 表中根据ID获取name 19 20 21 db_session.close() 22 23 orm_ForeignKey_select.py
(3),更新数据

1 from my_ForeignKey import Student, ClassTable,engine 2 3 from sqlalchemy.orm import sessionmaker 4 DB_session = sessionmaker(engine) 5 db_session = DB_session() 6 7 # 更新 8 class_info = db_session.query(ClassTable).filter(ClassTable.name=="01").first() 9 db_session.query(Student).filter(Student.class_id == class_info.id).update({"name":"NBDragon"}) 10 db_session.commit() 11 12 db_session.close() 13 14 orm_ForeignKey_update
(4),删除数据

1 from my_ForeignKey import Student, ClassTable,engine 2 3 from sqlalchemy.orm import sessionmaker 4 DB_session = sessionmaker(engine) 5 db_session = DB_session() 6 7 # 删除 8 class_info = db_session.query(ClassTable).filter(ClassTable.name=="01").first() 9 db_session.query(Student).filter(Student.class_id == class_info.id).delete() 10 db_session.commit() 11 12 db_session.close() 13 14 orm_ForeignKey_delete.py