sqlalchemy_prefix_with.py

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import Session, relationship, subqueryload, joinedload
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()

e = create_engine("mysql+mysqlconnector://scott:tiger@localhost/test", echo=True)

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(64))
    user_id = Column(Integer, ForeignKey('users.id'))


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    addresses = relationship(Address, backref="user")


Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)


u = User(name='test')
s.add_all([u, Address(email_address='email1', user=u), Address(email_address='email2', user=u)])
s.commit()

# this works like expected
users = s.query(User).prefix_with('SQL_CALC_FOUND_ROWS').all()
row_count = s.execute('SELECT FOUND_ROWS()').scalar()

print(users, row_count)

# with eager loading (subqueryload or joinedload) it fails
users = s.query(User).prefix_with('SQL_CALC_FOUND_ROWS').options(subqueryload(User.addresses)).all()
row_count = s.execute('SELECT FOUND_ROWS()').scalar()

print(users, row_count)