I am using SQLAlchemy with sqlite, python 3.13.3. In case it matters, this is for a Discord bot, but I doubt that the code will change for that.

I have the following tables:

[CODE lang="python" title="DB tables"]import sqlalchemy as sqal

class Base(DeclarativeBase):
    pass


client_group_association = sqal.Table(
    "client_group_association",
    Base.metadata,
    sqal.Column("client_id", sqal.UUID, sqal.ForeignKey("client_info.id"), primary_key=True),
    sqal.Column("group_id", sqal.UUID, sqal.ForeignKey("group_info.id"), primary_key=True)

)


class ClientInfo(Base):
    """Stores information of clients."""

    __tablename__ = "client_info"

    id: Mapped[uuid6.UUID] = mapped_column(sqal.UUID(as_uuid=True), primary_key=True,

                                           insert_default=uuid6.uuid7())

    name: Mapped[str] = mapped_column(sqal.String, nullable=False)
    email: Mapped[str] = mapped_column(sqal.String, nullable=False)
    phone: Mapped[str] = mapped_column(sqal.String, nullable=False)
    age: Mapped[int] = mapped_column(sqal.Integer, nullable=False)

    # Other unrelated fields omitted

    groups: Mapped[List[uuid6.UUID]] = mapped_column(UUIDList, nullable=True)

    group_objs: Mapped[List["GroupInfo"]] = relationship(
        secondary=client_group_association,
        back_populates="client_objs"
    )

    __table_args__ = (sqal.UniqueConstraint('name', 'email', 'phone', name='_unique_client_details'),
                      sqal.ForeignKeyConstraint(['groups'], ['group_info.id'],
                                                onupdate='CASCADE', ondelete='CASCADE'))


class GroupInfo(Base):
    """Stores information of groups."""

    __tablename__ = "group_info"


    id: Mapped[uuid6.UUID] = mapped_column(sqal.UUID(as_uuid=True), primary_key=True,
                                           insert_default=uuid6.uuid7())


    location: Mapped[Locations] = mapped_column(sqal.Enum(Locations), nullable=False)

    members: Mapped[List[uuid6.UUID]] = mapped_column(UUIDList, nullable=True)

    client_objs: Mapped[List["ClientInfo"]] = relationship(
        secondary=client_group_association,
        back_populates="group_objs"
    )

    __table_args__ = (sqal.UniqueConstraint('members', name='_unique_members'),
                      sqal.ForeignKeyConstraint(['members'], ['client_info.id'],
                                                onupdate='CASCADE', ondelete='CASCADE'))[/CODE]


My aim was to set up a proper bidirectional many-to-many relationship such that I can access the [ICODE]GroupInfo[/ICODE] objects directly from a [ICODE]ClientInfo[/ICODE] object (which represents the groups the user is in), and the [ICODE]ClientInfo[/ICODE] objects from [ICODE]GroupInfo[/ICODE] (representing the clients in the group).


I am using an [ICODE]AsyncSession[/ICODE], which I obtain as follows:

[CODE=python]__LOCK: asyncio.Lock = asyncio.Lock()
__SQL_ENGINE: AsyncEngine = create_async_engine(f"sqlite+aiosqlite:///{DATABASE_NAME}")

@contextlib.asynccontextmanager
async def get_async_session(self, locked: bool = True) -> AsyncIterator[AsyncSession]:

    Session = sessionmaker(bind=self.__SQL_ENGINE, class_=AsyncSession)
    if locked:
        async with self.__LOCK:
            async with Session() as session:
                yield session
    else:
        async with Session() as session:
            yield session[/CODE]

Things seemed to work for a while; I could retrieve the nested objects using nested [ICODE]selectinload[/ICODE] statements:
[CODE=python]stmt = select(ClientInfo).where(...)\
                         .limit(10)\
                         .options(selectinload(ClientInfo.group_objs)\
                                    .selectinload(GroupInfo.client_objs))  # Force eager-loading[/CODE]

But now, every time I want to add a new group, I am facing this:

[CODE]sqlalchemy.exc.InvalidRequestError: Can't attach instance <ClientInfo at 0x7baada2f3cb0>; another instance with key (<class 'db_models.ClientInfo'>, (UUID('019634d4-1422-7a0e-9a21-96941acc10dd'),), None) is already present in this session.[/CODE]

Note that every time I wish to access the database, I create a new session. The lock is also supposed to allow only one session at a time I guess.

The error crops when I add the [ICODE]ClientInfo[/ICODE] objects into the [ICODE]GroupInfo[/ICODE] object. This is the most essential part of the transaction because I refer to the client objects through that relationship in the future. And this is done before adding the [ICODE]GroupInfo[/ICODE] object to the current session and committing.

[CODE=python]group_info: GroupInfo = GroupInfo(id=uuid6.uuid7(),
                                  location=self.location,
                                  members=list(self.clients.keys()))

for client_info in self.clients.values():
    group_info.client_objs.append(client_info)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^[/CODE]

Most probably this happens because I load some [ICODE]ClientInfo[/ICODE] objects from a search query to let the user pick a person. However, this is done in a separate instance of [ICODE]AsyncSession[/ICODE], and I store the data of the chosen individuals in a list as an instance variable, and make sure to expunge and expire all variables from that session after loading the selected person's info into the instance variable.


I have tried to delete those objects from the current session using

[CODE=python]session.expunge(client_info)
session.expire(client_info)[/CODE]

I have also created two instance variables in the main class which will hold the [ICODE]ClientInfo.group_objs[/ICODE] field and [ICODE]GroupInfo.client_objs[/ICODE] relationships, and try to use that if adding to the session returns an error:

[CODE=python]# In constructor
    self.group_objs_relationship = None
    self.client_objs_relationship = None

# Later in the separate class
    bot.client_object_relationship = group_info.client_objs  # Nullability checked
    bot.group_objs_relationship = group_info.client_objs[0].group_objs  # Nullability checked

# When adding the new group:
try:
    session.add(group_info)

except sqal.exc.InvalidRequestError:
    print('Error raised when adding GroupInfo, trying alternate path!')

    if bot.group_objs_relationship:
        bot.group_objs_relationship.append(group_info)

    else:
        print('Sorry, could not add that.')

finally:
    await session.commit()[/CODE]

This works for [ICODE]GroupInfo[/ICODE], but I cannot think of an alternative to this for adding the [ICODE]ClientInfo[/ICODE] objects to the [ICODE]GroupInfo[/ICODE] object before committing, because none of the expunge statements seem to work in that case.

Any assistance is appreciated.

[i][size=2]N.B.: For full transparency: I have posted this question on SO too, but the chances of getting a reply there is bleak, to be honest. Hence, posting here too because we have good programmers here.[/size][/i]