[Python] Getting a list from many-to-many column

I have the following data structure with the many-to-many relation:

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 the data 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 managed to obtain the 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.

Continue reading...