SQLAlchemy load_only option doesn't work, but returns all columns, not just specified columns

Question

I am using SQLAlchemy and the load_only option. After I read load_only docs, I expect that if I use load_only, I can get some specified columns. But the result is not. There were all columns, not some columns. I don't know why. Can you give me some advice?

ps. I need row-entity, so I don't use query like this:

session.query(table.columnA, table.columnB)

enter image description here

I am using SQLAlchemy 1.3.4

class Language(Base):
    __tablename__ = 'language'
    IDX = Column(INTEGER(11), primary_key=True)
    DH_INDEX = Column(ForeignKey('dryhop.DH_INDEX', ondelete='CASCADE', onupdate='CASCADE'), index=True)
    EP_INDEX = Column(ForeignKey('expendables.EP_INDEX', ondelete='CASCADE', onupdate='CASCADE'), index=True)
    LANG_CLASS = Column(String(20), nullable=False)
    LANG_NATION = Column(String(15), nullable=False)
    LANG_COMMENT = Column(Text)
    UPDATE_TIME = Column(DateTime, server_default=text("CURRENT_TIMESTAMP"))

query= session.query(db_model.Language).options(load_only("LANG_COMMENT", "LANG_NATION"))
dir(query.first()) # 'DH_INDEX', 'EP_INDEX', 'IDX','LANG_CLASS', 'LANG_COMMENT', 'LANG_NATION', 'UPDATE_TIME'... ETC

When dir(query.first()), I expected only two columns

'LANG_COMMENT', 'LANG_NATION')

but result is

'DH_INDEX', 'EP_INDEX', 'IDX','LANG_CLASS', 'LANG_COMMENT', 'LANG_NATION', 'UPDATE_TIME'

SQLA models do not implement `__dir__`, so the names are gathered in the usual fashion. There's nothing out of the ordinary in the result. — Ilja Everilä, May 28 '19 at 14:16
@Ilja Everilä thanks answer!. So you means that load_only query returns all columns not specified columns, right? But i understand that load_only returns only some columns. In that query, i can get values, query.DH_INDEX.. Is this right? i expected only two column's value — KimJangHun, May 29 '19 at 01:28
@Ilja Everilä i checked that select query is different(using sqlalchmy echo =True). Also i checked your answer(https://stackoverflow.com/questions/50317889/in-sqlalchemy-why-is-my-load-only-not-filtering-any-columns-that-i-have-specifi). If so what is difference between normal query and load_only query?, I read docs but i can't get any benefits using load_only options. And Thank you very much Ilja Everilä. You helped me last time. I appreciated. — KimJangHun, May 29 '19 at 01:51

Ilja Everilä · Accepted Answer · 2019-06-05T10:25:03.130

Given an object, the builtin dir attempts to return a list of valid attributes for that object. If the object in question does not override the default functionality by implementing __dir__(), the list is produced for "ordinary" objects as such:

Otherwise, the list contains the object’s attributes’ names, the names of its class’s attributes, and recursively of the attributes of its class’s base classes.

In other words the list you get is both a list of the loaded attributes (among others) from the instance, and the attributes from the class Language, its parent, and so on.

If you wish to verify that only the attributes you wanted are loaded, you can for example peek at the instance's __dict__ attribute, using vars():

In [11]: vars(query.first())
Out[11]: 
{'IDX': 1,
 'LANG_COMMENT': 'Comment',
 'LANG_NATION': 'bar',
 '_sa_instance_state': <sqlalchemy.orm.state.InstanceState at 0x7fa6414916d8>}

Or even better, use SQLAlchemy's runtime inspection:

In [37]: from sqlalchemy import inspect

In [38]: obj = query.first()

In [39]: inspect(obj).unloaded
Out[39]: {'DH_INDEX', 'EP_INDEX', 'LANG_CLASS', 'UPDATE_TIME'}

Finally, note that no matter what you put in load_only(), the primary key is always loaded, because it would not make sense not to, even if you really wanted.

Thank you very much Ilja Everilä. Every question, i owed you. Thanks !!! — KimJangHun, Jun 05 '19 at 09:05

SQLAlchemy load_only option doesn't work, but returns all columns, not just specified columns

1 Answers1