I have following Group and Contact model in flask with Sql Alchemy ORM

group_contact = db.Table('group_contact',db.Column('group_id', db.Integer, db.ForeignKey('group.id')),db.Column('contact_id', db.Integer, db.ForeignKey('contact.id')),db.PrimaryKeyConstraint('group_id', 'contact_id')
)class Group(db.Model):id = db.Column(db.Integer, primary_key=True)name = db.Column(db.String(100))class Contact(db.Model):id = db.Column(db.Integer, primary_key=True)phone = db.Column(db.String(15), nullable=False, unique=True)groups = db.relationship("Group", secondary=group_contact, backref='contacts')

Now I need to query Contact with groups:

contacts = Contact.query.join(Group, Contact.groups).all()
for contact in contacts:print(contact.groups)

Here the problem is number of SQL query increases as number of contact increases when I execute above code.

Django ORM has prefetch_related() with queryset which does the following according to django docs.

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related, in addition to the foreign key and one-to-one relationships that are supported by select_related.

Now I am trying to do the same thing with Sql Alchemy by the following code:

contacts = Contact.query.all()     
contact_groups = group_contact.query.join(Group
).filter(group_contact.contact_id.in_([item.id for item in contacts]))

But this gives me this error:

AttributeError: 'Table' object has no attribute 'query'

How can I get prefetch_related like feature from django with SqlAlchemy?

You want to tell SQLAlchemy to eagerly load related objects by using a relationship loading technique. SQLAlchemy can be told to load the groups together with the contacts in a single query.

For just this one query, you can add joinedload() option (it is available via the Flask-SQLAlchemy db object):

contacts = Contact.query.options(db.joinedload(Contact.groups)).all()

This pre-loads the Contact.groups attribute on each matched contact:

for contact in contacts:# no new query issued to fetch groups, the data for the groups# is already availableprint(contact.groups)

The query executed looks like this:

SELECT contact.id AS contact_id,contact.phone AS contact_phone,group_1.id AS group_1_id,group_1.name AS group_1_name
FROM contact 
LEFT OUTER JOIN (group_contact AS group_contact_1JOIN "group" AS group_1 ON group_1.id = group_contact_1.group_id
) ON contact.id = group_contact_1.contact_id

You can also set a default loading strategy for the relationship on the model; to always eagerly load groups, use lazy='joined' on the relationship:

class Contact(db.Model):# ...groups = db.relationship("Group", secondary=group_contact, backref='contacts',lazy='joined')