multiple load_only() in Query.options(...) ignore all but a single of the load_only()'s.

[John Prior]

I'm trying to load specific columns from two joined tables. The load_only() specification is respected for one table, but not the other:

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

Base = declarative_base()

class Table1(Base):
    __tablename__ = 'table1'
    global_id = Column(Integer, primary_key=True)
    table1_val1 = Column(String)
    table1_val2 = Column(String)
    r1 = relationship('Table2', backref = 'r2')

class Table2(Base):
    __tablename__ = 'table2'
    global_id = Column(Integer, ForeignKey('table1.global_id'), primary_key=True)
    table2_val1 = Column(String)
    table2_val2 = Column(String)

from sqlalchemy.orm import sessionmaker
some_engine = create_engine('sqlite://')
Base.metadata.create_all(some_engine)
Session = sessionmaker(bind=some_engine)
session = Session()

session.add(Table1(table1_val1='1val1',table1_val2='1val2',r1=[Table2(table2_val1='2val1', table2_val2='2val2')]))
query = session.query(Table1).options(joinedload('r1',innerjoin=True),Load(Table1).load_only('table1_val1'),Load(Table2).load_only('table2_val1'))

foo = query.all()
print foo[0].table1_val2

Output:

'1val2'

You can also see the not-desired field in the SELECT statement output by logging:

INFO:sqlalchemy.engine.base.Engine:SELECT table1.global_id AS table1_global_id, table1.table1_val1 AS table1_table1_val1, table2_1.global_id AS table2_1_global_id, table2_1.table2_val1 AS table2_1_table2_val1, table2_1.table2_val2 AS table2_1_table2_val2 FROM table1 JOIN table2 AS table2_1 ON table1.global_id = table2_1.global_id

[Mike Bayer]

the Table2 here has to be specified on its complete path. Also, the Session here has the attribute anyway because it isn't being expired. Also, calling "some_object.someattr" loads the attribute unconditionally. The test with all these issues corrected looks like:

session.add(
    Table1(
        table1_val1='1val1', table1_val2='1val2',
        r1=[Table2(table2_val1='2val1', table2_val2='2val2')]))
session.commit()

query = session.query(Table1).options(
    joinedload('r1', innerjoin=True).load_only('table2_val1'),
    Load(Table1).load_only('table1_val1'))

foo = query.all()

assert 'table1_val2' not in foo[0].__dict__
assert 'table2_val2' not in foo[0].r1[0].__dict__

turn on echo and you'll see the attribute is not there.

[John Prior]

Now I understand the nuances you explained. Thank you.

[Mike Bayer]

thanks for the complete test it makes these quick to answer!