映射在第五步，我们还是一步一步来哈
一. 关系介绍
举一个比较经典的关系，部门与员工（以下是我的需求情况，算是把该有的关系都涉及到了）
1.每个部门会有很多成员（这里只算成员的主属部门） ---> 一对多
2.每个部门都有一个负责人 ---> 多对一
3.每个部门可能有一个上级部门 ---> 自关联多对一
4.每个员工可能有很多附属部门 ---> 多对多
5.每个员工可能有很多上级员工 ---> 自关联多对多
二. 设计部门与成员表模型
直接附上模型表代码，但是不包含关系映射（单独写一步）
from sqlalchemy.ext.declarative import declarative_base
BaseModel = declarative_base() # 创建模型对象的基类
# 部门
class Department(BaseModel):
__tablename__ = 'dep'
id = Column(Integer(), primary_key=True, autoincrement=True)
name = Column(String(30),nullable=False,unique=True) # 部门名称
staff_id = Column(Integer(),ForeignKey('staff.id')) # 负责人
up_dep_id = Column(Integer(),ForeignKey('dep.id')) # 上级部门----自关联
def __repr__(self): # 方便打印查看
return '<Department %s>' % self.name
# 员工
class Staff(BaseModel):
__tablename__ = 'staff'
id = Column(Integer(), primary_key=True, autoincrement=True)
name = Column(String(30),nullable=False,unique=True) # 员工名称
main_dep_id = Column(Integer(),ForeignKey('dep.id')) # 主要部门
def __repr__(self):
return '<Staff %s>' % self.name
三. 设计第三方表模型--附属部门与上级员工
建立多对多关系映射时secondary参数需要指定一个第三方表模型对象，但不是自己写的Class哦，而是一个Table对象
from sqlalchemy import Table # 使用Table专门生成第三方表模型
# 第三方表--附属部门
aux_dep_table = Table('staff_aux_dep',BaseModel.metadata,
Column('id',Integer(),primary_key=True, autoincrement=True),
Column('dep_id', Integer(), ForeignKey('dep.id',ondelete='CASCADE')),
Column('staff_id', Integer(), ForeignKey('staff.id',ondelete='CASCADE'))
)
# 第三方表--上级员工
up_obj_table = Table('staff_up_obj',BaseModel.metadata,
Column('id',Integer(),primary_key=True, autoincrement=True),
Column('up_staff_id', Integer(), ForeignKey('staff.id',ondelete='CASCADE')),
Column('down_staff_id', Integer(), ForeignKey('staff.id',ondelete='CASCADE'))
)
四. 生成表
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test?charset=utf8') # 关联数据库
DBSession = sessionmaker(engine) # 创建DBSession类
session = DBSession() # 创建session对象
BaseModel.metadata.create_all(engine) # 数据库生成表
五. relationship--关系映射
简单来说, relationship函数是sqlalchemy对关系之间提供的一种便利的调用方式, relationship的返回值赋给的变量名为正向调用的属性名，绑定给当前表模型类中，而backref参数则是指定反向调用的属性名，绑定给关联的表模型类中，如下部门表中Department.staffs就为正向，Staff.main_dep就为反向。
先导入relationship
from sqlalchemy.orm import relationship
先映射部门表，需要注意的是：
1. 是由于部门与员工之间有多重外键约束，在多对一或一对多关系相互映射时需要用foreign_keys指定映射的具体字段
2. 自关联多对一或一对多时候，需要用remote_side参数指定‘一’的一方，值为一个Colmun对象（必须唯一）
# 部门
class Department(BaseModel):
__tablename__ = 'dep'
id = Column(Integer(), primary_key=True, autoincrement=True)
name = Column(String(30), nullable=False, unique=True) # 部门名称
staff_id = Column(Integer(), ForeignKey('staff.id')) # 负责人
up_dep_id = Column(Integer(), ForeignKey('dep.id')) # 上级部门----自关联
# 主属部门为此部门的所有员工，反向Staff实例.main_dep获取员工的主属部门
main_staffs = relationship('Staff', backref='main_dep', foreign_keys='Staff.main_dep_id')
# 部门的直属上级部门，反向Department实例.down_deps,获取部门的所有直属下级部门（自关联多对一需用remote_side=id指定‘一’的一方)
up_dep = relationship('Department', backref='down_deps', remote_side=id)
重新生成数据（当调用属性映射的为‘多’的一方，则代表的是一个InstrumentedList 类型的结果集，是List的子类，对这个集合的修改，就是修改外键关系）
staff_names = ['我','你','他','她','它']
dep_names = ['1部','2部','21部','22部']
staff_list = [Staff(name=name) for name in staff_names]
dep_list = [Department(name=name) for name in dep_names]
# 为所有员工初始分配一个主属部门（按列表顺序对应）
[dep.main_staffs.append(staff) for staff,dep in zip(staff_list,dep_list)]
# 关联上下级部门（2部的上级为1部，2部的下级为21、22部）
dep_list[1].up_dep = dep_list[0]
dep_list[1].down_deps.extend(dep_list[2:])
session.add_all(staff_list + dep_list)
session.commit() # 数据持久化到数据库
部门表关系映射 查询测试
dep_2 = session.query(Department).filter_by(name='2部').one() # 获取 2部 部门
staff_me = session.query(Staff).filter(Staff.name=='我').one() # 获取 ‘我’ 员工
print(dep_2.main_staffs) # 主属部门为2部的所有员工
print(dep_2.up_dep) # 2部的上级部门
print(dep_2.down_deps) # 2部的所有直属下级部门
print(staff_me.main_dep) # 员工‘我’ 的主属部门
# [<Staff 你>]
# <Department 1部>
# [<Department 21部>, <Department 22部>]
# <Department 1部>
映射员工表，需要注意的是：
1. 多对多关系中需要用secondary参数指定第三方表模型对象
2. 自关联多对多需要用primaryjoin和secondaryjoin指定主副连接关系，查询逻辑是根据主连接关系对应的第三方表的字段查询（例，查询id为10的员工的所有上级对象，就会在第三方表里查询down_staff_id=10对应的所有数据，而把每条数据的up_staff_id值对应员工表的id查询出来的对象集合，就为id为10的员工的所有上级对象了）
# 员工
class Staff(BaseModel):
__tablename__ = 'staff'
id = Column(Integer(), primary_key=True, autoincrement=True)
name = Column(String(30), nullable=False, unique=True) # 员工名称
main_dep_id = Column(Integer(), ForeignKey('dep.id')) # 主要部门
# 员工的所有附属部门，反向为所有附属部门为此部门的员工
aux_deps = relationship(Department, secondary=aux_dep_table,backref = 'aux_staffs')
# 员工的所有直属上级员工，反向为员工的所有直属下级员工（自关联多对多需要指定第三方表主连接关系与副连接关系，查询逻辑是根据主连接关系对应的第三方表的字段查询）
up_staffs = relationship('Staff', secondary=up_obj_table,
primaryjoin = id == up_obj_table.c.down_staff_id, # 主连接关系 为 本表id字段对应第三方表的down_staff_id
secondaryjoin = id == up_obj_table.c.up_staff_id, # 副连接关系 为 本表id对应第三表的up_staff_id
backref = 'down_staffs')
建立数据映射关系
staff_all_list = session.query(Staff).all() # 获取所有员工对象列表
dep_1 = session.query(Department).filter_by(name='1部').one() # 获取 部门 1部
dep_2 = session.query(Department).filter_by(name='2部').one() # 获取 部门 2部
staff_me = session.query(Staff).filter_by(name='我').one() # 获取员工 ‘我’
dep_1.aux_staffs.extend(staff_all_list) # 分配所有员工的附属部门都为1部
staff_me.aux_deps.append(dep_2) # 给员工‘我’额外分配附属部门2部
staff_all_list.remove(staff_me)
staff_me.down_staffs.extend(staff_all_list) # 将所有除员工‘我’以外的员工 都成为 员工‘我’的下级
session.commit()
员工表关系映射 查询测试
staff_you = session.query(Staff).filter_by(name='你').one() # 获取员工 你
print(dep_1.aux_staffs.all()) # 所有附属部门为1部的员工
print(staff_me.aux_deps.all()) # 员工‘我’ 的所有附属部门
print(staff_me.down_staffs.all()) # 员工‘我’ 的所有直属下级员工
print(staff_you.up_staffs.all()) # 员工‘你’ 的所有直属上级员工
# [<Staff 我>, <Staff 你>, <Staff 他>, <Staff 她>, <Staff 它>]
# [<Department 1部>]
# [<Staff 你>, <Staff 他>, <Staff 她>, <Staff 它>]
# [<Staff 我>]
如果我们想根据调用属性获得的集合，再进行筛选，可以吗？
模拟个场景，我已经知道了员工‘我’有一个附属部门叫‘1部’了，我想知道除了‘1部’的其他附属部门，如下：
from sqlalchemy import not_
staff_me.aux_deps.filter(not_(Department.name=='1部'))
# AttributeError: 'InstrumentedList' object has no attribute 'filter'
报错了，也是，之前都说了，调用属性映射的为‘多’的一方代表的是一个InstrumentedList 对象，只有为Query对象才能有filter、all、one等的方法。
那么该怎么转换成Query对象呢，通过查看relationship的参数，发现了lazy，通过改变它的值可以在查询时可以得到不同的结果，他有很多值可以选择：
1. 默认值为select, 他直接会导出所有的结果对象合成一个列表
aux_deps = relationship(Department, secondary=aux_dep_table, backref='aux_staffs', lazy='select')
print(type(staff_me.aux_deps))
print(staff_me.aux_deps)
# <class 'sqlalchemy.orm.collections.InstrumentedList'>
# [<Department 1部>, <Department 2部>]
2. dynamic，他会生成一个继承与Query的AppenderQuery对象，可以用于继续做过滤操作，这就是我们模拟的场景想要的，需要注意的是，如果调用属性映射的不是‘多’而是‘一’的一方，那么就会报错（虽然为默认值select没有影响，但本来就一个结果，就不要加lazy参数了）
aux_deps = relationship(Department, secondary=aux_dep_table, backref='aux_staffs', lazy='dynamic')
print(type(staff_me.aux_deps))
print(staff_me.aux_deps.all()) # 获取员工‘我’的所有附属部门
print(staff_me.aux_deps.filter(not_(Department.name == '1部')).all()) # 获取员工‘我’除1部以外的所有附属部门
# <class 'sqlalchemy.orm.dynamic.AppenderQuery'>
# [<Department 1部>, <Department 2部>]
# [ <Department 2部>]
3. 其他的还有很多参数，例如joined，连接查询，使用不当会有性能问题，以下为谷歌翻译的，大家有兴趣可以去看看原文https://docs.sqlalchemy.org/en/latest/orm/relationship_api.html，ctrl+f搜lazy
我们现在知道当需要对映射的结果集继续筛选的时候，可以在relationship指定lazy参数为'dynamic'，但是在这里加好像只是正向调用的时候有效，反向还是为InstrumentedList 对象
dep_1 = session.query(Department).filter_by(name='1部').one() # 获取 部门 1部
print(dep_1.aux_staffs.all()) # 获取所有附属部门为1部的员工
# AttributeError: 'InstrumentedList' object has no attribute 'all'
如果反向的时候我们该加在哪里呢？其实backref参数也可以接受一个元祖，里面只能是两个参数，一个跟之前一样是个字符串，为反向调用的属性名，另一个就是一个加关于反向调用时的参数（dict对象）
aux_deps = relationship(Department, secondary=aux_dep_table, backref=('aux_staffs',{'lazy':'dynamic'}), lazy='dynamic')
print(type(dep_1.aux_staffs.all()))
print(dep_1.aux_staffs.all()) # 获取所有附属部门为1部的员工
# <class 'sqlalchemy.orm.dynamic.AppenderQuery'>
# [<Staff 我>, <Staff 你>, <Staff 他>, <Staff 她>, <Staff 它>]
掌握了如上，之后使用SQLALchemy在工作中遇到的奇葩关系应该都能处理了吧，我语言组织能力比较弱，有什么错误还请指出----1738268742@qq.com
