[Python] How to fix `InvalidRequestError: Can't attach instance <ClassName at 0x7baada2f3cb0>`...

I have already gone through quite a few answers on SO (eg. this one and this one, but none of them seem to work for me.

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:

import sqlalchemy as sqal

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

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'))


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

I am using an AsyncSession, which I obtain as follows:

__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


Things seemed to work for a while; I could retrieve the nested objects using nested selectinload statements:

stmt = select(ClientInfo).where(...)\
.limit(10)\
.options(selectinload(ClientInfo.group_objs)\
.selectinload(GroupInfo.client_objs)) # Force eager-loading


Every time I want to create a new group, now I am facing

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.


error.

Note that every time I want to access the database, I create a new session.

The issue appears when I add the ClientInfo objects into the GroupInfo object before adding it to a session and committing. This is the most essential part of the transaction because I refer to the client objects through that relationship in the future.

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)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^


Most probably this happens because I load some ClientInfo objects to let the user pick the person, which makes sure that all the ClientInfo objects that I want to append to the relationship are already in the session. But what surprises me is that, this choosing part is done in a separate session in a separate class.

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

session.expunge(client_info)
session.expire(client_info)


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

# 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()


This works for GroupInfo, but I have no idea about how to add those ClientInfo objects to the GroupInfo object before committing because deleting doesn't work in that case.

Any help is appreciated.

Continue reading...