一,创建表及关系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
View Code

二, 增删改查

(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
View Code

(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
View Code

(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
View Code

(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
View Code