Sqlalchemy 中间表的建立

第一种：使用Table（）函数构造形式。

注意：如果table构造出的表仅仅能作为其他两个表的连接查询中间表使用，如果要对这个中间表的数据进行操作请使用Mapper对其映射成类。

from sqlalchemy import Column,INTEGER,String,BOOLEAN,TIMESTAMP,text,ForeignKey,Table
from sqlalchemy.orm import relationship,mapper
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata

 t_sys_role_menu = Table(
     'sys_role_menu', metadata,
     Column('rid', ForeignKey('sys_role.id'), primary_key=True, nullable=False),
     Column('mid', ForeignKey('sys_menu.id'), primary_key=True, nullable=False, index=True)
 )
 class SysRoleMenu(object):
     def __init__(self,rid,mid):
         if rid:
             self.rid = rid
         if mid:
             self.mid = mid

 mapper(SysRoleMenu, t_sys_role_menu)
# 数据库映射角色表
class SysRole(Base):
    __tablename__ = 'sys_role'
    __table_args__ = {'comment': '角色表'}

    id = Column(INTEGER, primary_key=True, comment='序号')
    role_name = Column(String(128), nullable=False, comment='角色名称')
    status = Column(BOOLEAN(1), comment='状态')
    role_key = Column(String(128), comment='角色代码')
    role_sort = Column(INTEGER, comment='排序')
    flag = Column(String(128), comment='标志')
    remark = Column(String(255), comment='备注')
    admin = Column(BOOLEAN(1), comment='是否是管理员角色')
    data_scope = Column(String(128), comment='权限范围')
    create_by = Column(INTEGER, comment='创建者')
    update_by = Column(INTEGER, comment='更新者')
    create_at = Column(TIMESTAMP, comment='创建时间')
    update_at = Column(TIMESTAMP, comment='更新时间')
    deleted_at = Column(TIMESTAMP, comment='删除时间')
    menus = relationship('SysMenu',secondary='sys_role_menu',backref='roles')
#系统菜单
class SysMenu(Base):
    __tablename__ = 'sys_menu'
    __table_args__ = {'comment': '系统菜单表'}

    id = Column(INTEGER, primary_key=True, comment='ID')
    pid = Column(INTEGER, nullable=False, server_default=text("'0'"), comment='父ID')
    title = Column(String(100), nullable=False, index=True, server_default=text("''"), comment='名称')
    icon = Column(String(100), nullable=False, server_default=text("''"), comment='菜单图标')
    href = Column(String(100), nullable=False, index=True, server_default=text("''"), comment='链接')
    target = Column(String(20), nullable=False, server_default=text("'_self'"), comment='链接打开方式')
    sort = Column(INTEGER, server_default=text("'0'"), comment='菜单排序')
    status = Column(BOOLEAN(1), nullable=False, server_default=text("'1'"), comment='状态(0:禁用,1:启用)')
    remark = Column(String(255), comment='备注信息')
    create_at = Column(TIMESTAMP, comment='创建时间')
    update_at = Column(TIMESTAMP, comment='更新时间')
    delete_at = Column(TIMESTAMP, comment='删除时间')

    roles = relationship('SysRole', secondary='sys_role_menu',backref='menus')

第二种：使用类命名一个中间表。

应注意在其他两个表的relationship中要声明primaryjoin的关系。不然报错提示：

sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class SysRole->sys_role'. Original exception was: Could not determine join condition between parent/child tables on relationship SysRole.sys_menus - there are no foreign keys linking these tables via secondary table 'sys_role_menu'. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.

from sqlalchemy import Column,INTEGER,String,BOOLEAN,TIMESTAMP,text,ForeignKey,Table
from sqlalchemy.orm import relationship,mapper
#角色菜单表
class SysRoleMenu(Base):
    __tablename__ = 'sys_role_menu'
    __table_args__ = {'comment':'角色权限表'}
    rid = Column(INTEGER, ForeignKey('sys_role.id'), primary_key=True, nullable=False)
    mid = Column(INTEGER, ForeignKey('sys_menu.id'), primary_key=True, nullable=False, index=True)


# 数据库映射角色表
class SysRole(Base):
    __tablename__ = 'sys_role'
    __table_args__ = {'comment': '角色表'}

    id = Column(INTEGER, primary_key=True, comment='序号')
    role_name = Column(String(128), nullable=False, comment='角色名称')
    status = Column(BOOLEAN(1), comment='状态')
    role_key = Column(String(128), comment='角色代码')
    role_sort = Column(INTEGER, comment='排序')
    flag = Column(String(128), comment='标志')
    remark = Column(String(255), comment='备注')
    admin = Column(BOOLEAN(1), comment='是否是管理员角色')
    data_scope = Column(String(128), comment='权限范围')
    create_by = Column(INTEGER, comment='创建者')
    update_by = Column(INTEGER, comment='更新者')
    create_at = Column(TIMESTAMP, comment='创建时间')
    update_at = Column(TIMESTAMP, comment='更新时间')
    deleted_at = Column(TIMESTAMP, comment='删除时间')

    menus = relationship('SysMenu',secondary='sys_role_menu',back_populates='roles',primaryjoin='SysRoleMenu.rid==SysRole.id',lazy='dynamic')



#系统菜单
class SysMenu(Base):
    __tablename__ = 'sys_menu'
    __table_args__ = {'comment': '系统菜单表'}

    id = Column(INTEGER, primary_key=True, comment='ID')
    pid = Column(INTEGER, nullable=False, server_default=text("'0'"), comment='父ID')
    title = Column(String(100), nullable=False, index=True, server_default=text("''"), comment='名称')
    icon = Column(String(100), nullable=False, server_default=text("''"), comment='菜单图标')
    href = Column(String(100), nullable=False, index=True, server_default=text("''"), comment='链接')
    target = Column(String(20), nullable=False, server_default=text("'_self'"), comment='链接打开方式')
    sort = Column(INTEGER, server_default=text("'0'"), comment='菜单排序')
    status = Column(BOOLEAN(1), nullable=False, server_default=text("'1'"), comment='状态(0:禁用,1:启用)')
    remark = Column(String(255), comment='备注信息')
    create_at = Column(TIMESTAMP, comment='创建时间')
    update_at = Column(TIMESTAMP, comment='更新时间')
    delete_at = Column(TIMESTAMP, comment='删除时间')

    roles = relationship('SysRole', secondary='sys_role_menu',back_populates='menus',primaryjoin='SysRoleMenu.mid==SysMenu.id',lazy='dynamic')

Tips：

这个错误

SAWarning: relationship 'SysMenu.sys_roles' will copy column sys_role.id to column sys_role_menu.rid, which conflicts with relationship(s): 'SysMenu.roles' (copies sys_role.id to sys_role_menu.rid), 'SysRole.sys_menus' (copies sys_role.id to sys_role_menu.rid). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards. To silence this warning, add the parameter 'overlaps="roles,sys_menus"' to the 'SysMenu.sys_roles' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)

错误的代码

#系统菜单
class SysMenu(Base):
    __tablename__ = 'sys_menu'
    __table_args__ = {'comment': '系统菜单表'}

    id = Column(INTEGER, primary_key=True, comment='ID')
    pid = Column(INTEGER, nullable=False, server_default=text("'0'"), comment='父ID')
    title = Column(String(100), nullable=False, index=True, server_default=text("''"), comment='名称')
    icon = Column(String(100), nullable=False, server_default=text("''"), comment='菜单图标')
    href = Column(String(100), nullable=False, index=True, server_default=text("''"), comment='链接')
    target = Column(String(20), nullable=False, server_default=text("'_self'"), comment='链接打开方式')
    sort = Column(INTEGER, server_default=text("'0'"), comment='菜单排序')
    status = Column(BOOLEAN(1), nullable=False, server_default=text("'1'"), comment='状态(0:禁用,1:启用)')
    remark = Column(String(255), comment='备注信息')
    create_at = Column(TIMESTAMP, comment='创建时间')
    update_at = Column(TIMESTAMP, comment='更新时间')
    delete_at = Column(TIMESTAMP, comment='删除时间')

    sys_roles = relationship('SysRole', secondary='sys_role_menu',back_populates='menus',primaryjoin='SysRoleMenu.mid==SysMenu.id',lazy='dynamic')

错误在于我使用了一个sys_roles作为字段名，重命名roles就可以了,或者是根据提示增加一个overlaps只读属性，因为在映射时去SysRole中的字段产生了冲突。

   roles = relationship('SysRole', secondary='sys_role_menu',back_populates='menus',primaryjoin='SysRoleMenu.mid==SysMenu.id',lazy='dynamic')