I have the following data structure with the many-to-many relationship:
things_tags = Table("things_to_tags", Base.metadata,
Column('id', Integer, primary_key=True),
Column('thing_id', Integer, ForeignKey('things_table.id')),
Column('tag_id', Integer, ForeignKey('tags_table.id')),
)
class Thing(Base):
__tablename__ = 'things_table'
id = Column(Integer, primary_key=True)
title = Column(String)
tags = relationship("Tag", secondary = things_tags, back_populates='things')
class Tag(Base):
__tablename__ = 'tags_table'
id = Column(Integer, primary_key=True)
name = Column(String)
things = relationship("Thing", secondary = things_tags)
And I would like to retrieve all "things" from the database with their associated "tags" in the format:
[
(thing1, [tag2, tag5, tag6]),
(thing2, [tag1, tag3]),
...
]
I tried to query the database with
session.execute(select(Thing.title,Tag.name).join(Thing.tags)).all()
But this gives me the result in the format:
[(thing1, tag2),
(thing1, tag5),
(thing1, tag6),
(thing2, tag1),
(thing2, tag3),
...
]
I almost managed to obtain the desired result with
for x in session.query( Thing ).all():
print(x.title, x.tags)
But, if I understand correctly, this is not the proper way of doing it. First, query() is being deprecated. Second, every time I invoke x.title or x.tags, the database is being connected again. So, for a large database, this would result in many many connections to the database.
I am trying to find the method to achieve what I want for hours and hours now, but cannot seam to find any good pointers to how to do that.
I think u can try this code once
from sqlalchemy import select
from sqlalchemy.orm import selectinload
query = select(Thing).options(selectinload(Thing.tags))
result = session.execute(query).scalars().all()
formatted_result = [(thing, thing.tags) for thing in result]
This will load all the things and their tags and also return the data in a way u desire.[Avoid N+1 query prob]