python - 与关联表的多对多关系的 SQLAlchemy 警告

Question

我收到此警告:
sqlalchemy/orm/relationships.py:3447: SAWarning: 关系 'Group.members' 会将列 group.id 复制到列 user_group.group_id，这与关系冲突:'BuiltinUser.groups'(将 group.id 复制到 user_group .group_id)。如果这不是本意，请考虑这些关系是否应该与 back_populates 相关联，或者如果 viewonly=True 应该应用于一个或多个，如果它们是只读的。对于外键约束部分重叠的不太常见的情况，orm.foreign() 注释可用于隔离应写入的列。 'overlaps' 参数可用于删除此警告。
这让我觉得我在关系定义上犯了一个错误。它是通过辅助(关联表)指定的多对多关系。以下是会产生不需要的警告的工作脚本/定义。使用 SQLAlchemy 版本 1.4.0b1 和 python 3.6.8

import attr
import uuid
import asyncio
from typing import List

from sqlalchemy.future import select
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import registry, relationship
from sqlalchemy import (
    Table, MetaData, Column, Integer, String,
    ForeignKey, Boolean,
)


meta = MetaData()
mapper_registry = registry()

user_table = Table(
    "user",
    meta,
    Column("id", String, primary_key=True),
    Column("username", String(20), unique=True, nullable=False),
)

group_table = Table(
    "group",
    meta,
    Column("id", String, primary_key=True),
    Column("name", String(20), unique=True, nullable=False),
)

user_group_table = Table(
    "user_group",
    meta,
    Column("user_id", Integer, ForeignKey("user.id")),
    Column("group_id", Integer, ForeignKey("group.id")),
)


@attr.s(kw_only=True)
class Group:
    id: str = attr.ib(factory=uuid.uuid4, converter=str)
    name: str = attr.ib(default="")
    members: List["BuiltinUser"] = attr.ib(factory=list, repr=False)


@attr.s(kw_only=True)
class BuiltinUser:
    id: str = attr.ib(factory=uuid.uuid4, converter=str)
    username: str = attr.ib(default="")
    groups: List[Group] = attr.ib(factory=list, repr=False)


mapper_registry.map_imperatively(
    BuiltinUser,
    user_table,
    properties={
        "groups": relationship(Group, secondary=user_group_table)
    }
)
mapper_registry.map_imperatively(
    Group,
    group_table,
    properties={
        "members": relationship(BuiltinUser, secondary=user_group_table),
    }
)


async def async_main():
    engine = create_async_engine(
        "sqlite:///foo.db",
        echo=True,
    )
    async with engine.begin() as conn:
        await conn.run_sync(meta.drop_all)
        await conn.run_sync(meta.create_all)

    user0 = BuiltinUser(username="more")

    async with AsyncSession(engine) as session:
        session.add(user0)
        await session.commit()

        result = await session.execute(select(BuiltinUser))
        for user in result.scalars():
            print(user.username)


loop = asyncio.get_event_loop()
loop.run_until_complete(async_main())

这是通过关联表定义多对多的正确方法吗？如何改进此定义以消除警告？

Answer 1

最后，解决方案被编码在警告本身中。就是给关系添加back_populates

mapper_registry.map_imperatively(
    BuiltinUser,
    user_table,
    properties={
        "groups": relationship(Group, secondary=user_group_table, back_populates="members")
    }
)
mapper_registry.map_imperatively(
    Group,
    group_table,
    properties={
        "members": relationship(BuiltinUser, secondary=user_group_table, back_populates="groups"),
    }
)