SQLAlchemy warning for many to many relation with association table

Question

I'm getting this warning:

sqlalchemy/orm/relationships.py:3447: SAWarning: relationship 'Group.members' will copy column group.id to column user_group.group_id, which conflicts with relationship(s): 'BuiltinUser.groups' (copies group.id to user_group.group_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. The 'overlaps' parameter may be used to remove this warning.

that makes me feel that I have made a mistake in relationship definition. It is a many to many relation specified via secondary (association table). Below is working script/definition that will produce unwanted warning. Using SQLAlchemy version 1.4.0b1 and 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())

Is this correct way to define many to many via association table? How to improve this definition to get rid of the warning?

scagbackbone scagbackbone · Accepted Answer · 2021-02-09T20:53:30

In the end, solution is encoded in the warning itself. It is to add back_populates to relationship

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"),
    }
)

SQLAlchemy warning for many to many relation with association table

1 Answers