如何解决SQLAlchemy中关系重叠的SAWarning问题？

警告信息 #

SAWarning: relationship 'Grupo.users' will copy column ominicontacto_app_user.id to column ominicontacto_app_agenteprofile.user_id, which conflicts with relationship(s): 'ActividadAgenteLog.user' (copies ominicontacto_app_user.id to ominicontacto_app_agenteprofile.user_id). 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="user"' to the 'Grupo.users' relationship.

模型代码 #

from datetime import datetime
from sqlalchemy import BigInteger, ForeignKey
from sqlalchemy.orm import mapped_column, DeclarativeBase, Mapped, relationship
from typing import List, Optional

class Base(DeclarativeBase):
    type_annotation_map = {
        int: BigInteger
    }

class ActividadAgenteLog(Base):
    __tablename__ = 'reportes_app_actividadagentelog'

    agente_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_agenteprofile.id'))
    event: Mapped[str]
    id: Mapped[int] = mapped_column(primary_key=True)
    pausa_id: Mapped[Optional[str]]
    time: Mapped[datetime]

    user: Mapped['User'] = relationship('User', secondary='ominicontacto_app_agenteprofile', back_populates='actividades', uselist=False)

    def __repr__(self):
        return f'<ActividadAgente(id={self.id})>'

class AgenteProfile(Base):
    __tablename__ = 'ominicontacto_app_agenteprofile'

    borrado: Mapped[bool]
    estado: Mapped[int]
    grupo_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_grupo.id'))
    id: Mapped[int] = mapped_column(primary_key=True)
    is_inactive: Mapped[bool]
    reported_by_id: Mapped[int]
    sip_extension: Mapped[int]
    sip_password: Mapped[Optional[str]]
    user_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_user.id'))

    def __repr__(self):
        return f'<AgenteProfile(id={self.id})>'

class Grupo(Base):
    __tablename__ = 'ominicontacto_app_grupo'

    acceso_agendas_agente: Mapped[bool]
    acceso_calificaciones_agente: Mapped[bool]
    acceso_campanas_preview_agente: Mapped[bool]
    acceso_contactos_agente: Mapped[bool]
    acceso_dashboard_agente: Mapped[bool]
    acceso_grabaciones_agente: Mapped[bool]
    auto_attend_dialer: Mapped[bool]
    auto_attend_inbound: Mapped[bool]
    auto_unpause: Mapped[int]
    call_off_camp: Mapped[bool]
    cantidad_agendas_personales: Mapped[Optional[int]]
    conjunto_de_pausa_id: Mapped[Optional[int]]
    id: Mapped[int] = mapped_column(primary_key=True)
    limitar_agendas_personales: Mapped[bool]
    limitar_agendas_personales_en_dias: Mapped[bool]
    nombre: Mapped[str]
    obligar_calificacion: Mapped[bool]
    obligar_despausa: Mapped[bool]
    on_hold: Mapped[bool]
    show_console_timers: Mapped[bool]
    tiempo_maximo_para_agendar: Mapped[Optional[int]]
    whatsapp_habilitado: Mapped[bool]

    users: Mapped[List['User']] = relationship('User', secondary='ominicontacto_app_agenteprofile', back_populates='grupos')

    def __repr__(self):
        return f'<Grupo(id={self.id})>'

class LlamadaLog(Base):
    __tablename__ = 'reportes_app_llamadalog'

    agente_extra_id: Mapped[Optional[int]]
    agente_id: Mapped[int] = mapped_column(ForeignKey('ominicontacto_app_agenteprofile.id'))
    archivo_grabacion: Mapped[Optional[str]]
    bridge_wait_time: Mapped[Optional[int]]
    callid: Mapped[str]
    campana_extra_id: Mapped[Optional[int]]
    campana_id: Mapped[int]
    contacto_id: Mapped[Optional[int]]
    duracion_llamada: Mapped[int]
    event: Mapped[str]
    id: Mapped[int] = mapped_column(primary_key=True)
    numero_extra: Mapped[Optional[str]]
    numero_marcado: Mapped[Optional[str]]
    time: Mapped[datetime]
    tipo_campana: Mapped[int]
    tipo_llamada: Mapped[int]

    def __repr__(self):
        return f'<LlamadaLog(id={self.id})>'
    
class User(Base):
    __tablename__ = 'ominicontacto_app_user'

    borrado: Mapped[bool]
    date_joined: Mapped[datetime]
    email: Mapped[str]
    first_name: Mapped[str]
    id: Mapped[int] = mapped_column(primary_key=True)
    is_active: Mapped[bool]
    is_agente: Mapped[bool]
    is_cliente_webphone: Mapped[bool]
    is_staff: Mapped[bool]
    is_superuser: Mapped[bool]
    is_supervisor: Mapped[bool]
    last_login: Mapped[datetime]
    last_name: Mapped[str]
    last_session_key: Mapped[str]
    password: Mapped[str]
    username: Mapped[str]

    actividades: Mapped['ActividadAgenteLog'] = relationship('ActividadAgenteLog', secondary='ominicontacto_app_agenteprofile', back_populates='user')
    grupos: Mapped[List['Grupo']] = relationship('Grupo', secondary='ominicontacto_app_agenteprofile', back_populates='users')

    def __repr__(self):
        return f'<User(id={self.id}, username={self.username})>'

问题 #

我尝试为关系添加back_populates参数，但问题仍存在，警告依旧出现，请问该如何解决这个警告？

解决方案 #

警告原因 #

Grupo.users和ActividadAgenteLog.user这两个关系都依赖中间表ominicontacto_app_agenteprofile的user_id列，SQLAlchemy无法自动判断哪个关系负责该列的写入操作，因此抛出重叠警告。back_populates仅用于关联双向关系，无法解决不同关系组共享同一中间表列的冲突问题。

方法一：添加overlaps参数 #

直接按照警告提示，在其中一个冲突关系上声明overlaps参数，告知SQLAlchemy这些关系共享同一列，无需担心写入冲突。

修改Grupo类的users关系：

users: Mapped[List['User']] = relationship(
    'User', 
    secondary='ominicontacto_app_agenteprofile', 
    back_populates='grupos',
    overlaps="user"  # 添加此行
)

或修改ActividadAgenteLog类的user关系：

user: Mapped['User'] = relationship(
    'User', 
    secondary='ominicontacto_app_agenteprofile', 
    back_populates='actividades',
    uselist=False,
    overlaps="users"  # 添加此行
)

方法二：设置viewonly=True #

如果其中一个关系仅用于查询（不需要通过该关系修改user_id列），可以将其标记为只读，避免SQLAlchemy尝试通过该关系写入数据。

例如，若ActividadAgenteLog.user仅用于查询，修改如下：

user: Mapped['User'] = relationship(
    'User', 
    secondary='ominicontacto_app_agenteprofile', 
    back_populates='actividades',
    uselist=False,
    viewonly=True  # 添加此行
)

内容的提问来源于stack exchange，提问作者RiXaTorAgu