SQLAlchemy 数据表之一对多的操作(ForeignKey)

一、创建数据表及关系relationship

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# 这次我们要多导入一个 ForeignKey 字段了,外键关联对了
from sqlalchemy import Column,Integer,String,ForeignKey
# 还要从orm 中导入一个 relationship 关系映射
from sqlalchemy.orm import relationship

class ClassTable(Base):
    __tablename__="classtable"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),index=True)

class Student(Base):
    __tablename__="student"
    id=Column(Integer,primary_key=True)
    name = Column(String(32),index=True)

    # 关联字段,让class_id 与 class 的 id 进行关联,主外键关系(这里的ForeignKey一定要是表名.id不是对象名)
    class_id = Column(Integer,ForeignKey("classtable.id"))

    # 将student 与 classtable 创建关系 这个不是字段,只是关系,backref是反向关联的关键字
    to_class = relationship("ClassTable",backref = "stu2class")

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:1234@127.0.0.1:3306/db4?charset=utf8")

# 创建所有表
Base.metadata.create_all(engine)

# 删除所有的表
# Model.metadata.drop_all(engine)

二、基于relationship增加数据

from my_ForeignKey import Student, ClassTable,engine
# 创建连接
from sqlalchemy.orm import sessionmaker
# 创建数据表操作对象 sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()

# 增加数据
# 1.简单增加数据
# 添加两个班级:
db_session.add_all([
    ClassTable(name="CBD1"),
    ClassTable(name="CBD2")
])
db_session.commit()

# 添加一个学生 annie 班级是 CBD1
# 查询要添加到的班级
class_obj = db_session.query(ClassTable).filter(ClassTable.name == "CBD1").first()
# 创建学生
stu = Student(name="DragonFire", class_id=class_obj.id)
db_session.add(stu)
db_session.commit()


# 2. relationship版 添加数据
# 通过关系列 to_class 可以做到两件事
# 第一件事 在ClassTable表中添加一条数据
# 第二件事 在Student表中添加一条数据并将刚刚添加的ClassTable的数据id填写在Student的class_id中
stu_cla = Student(name="annie", to_class=ClassTable(name="CBD1"))
print(stu_cla.name, stu_cla.class_id)
db_session.add(stu_cla)
db_session.commit()


# 3.relationship版 反向添加数据
# 首先建立ClassTable数据
class_obj = ClassTable(name="OldBoyS2")
# 通过class_obj中的反向关联字段backref - stu2class
# 向 Student 数据表中添加 2条数据 并将 2条数据的class_id 写成 class_obj的id
class_obj.stu2class = [Student(name="BMW"), Student(name="Audi")]
db_session.add(class_obj)
db_session.commit()


# 关闭连接
db_session.close()

三、基于relationship查询数据

from my_ForeignKey import Student, ClassTable,engine

from sqlalchemy.orm import sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()

# 1.查询所有数据,并显示班级名称,连表查询 正向查询
student_list = db_session.query(Student).all()
for row in student_list:
    # row.to_class.name 通过Student对象中的关系字段relationship to_class 获取关联 ClassTable中的name
    print(row.name,row.to_class.name,row.class_id)

# 2.反向查询
class_list = db_session.query(ClassTable).all()
for row in class_list:
    for row2 in row.stu2class:
        print(row.name,row2.name)
# row.stu2class 通过 backref 中的 stu2class 反向关联到 Student 表中根据ID获取name


db_session.close()

四、更新数据

from my_ForeignKey import Student, ClassTable,engine

from sqlalchemy.orm import sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()

# 更新
class_info = db_session.query(ClassTable).filter(ClassTable.name=="CBD1").first()
db_session.query(Student).filter(Student.class_id == class_info.id).update({"name":"ANNIE"})
db_session.commit()

db_session.close()

五、删除数据

from my_ForeignKey import Student, ClassTable,engine

from sqlalchemy.orm import sessionmaker
DB_session = sessionmaker(engine)
db_session = DB_session()

# 删除
class_info = db_session.query(ClassTable).filter(ClassTable.name=="CBD1").first()
db_session.query(Student).filter(Student.class_id == class_info.id).delete()
db_session.commit()

db_session.close()