In SQLAlchemy, why is my load_only not filtering any columns that I have specified?

Question

Basically I have created a database, in normal full query, this is the code I've used and the response generated.

db.session.query(User).all()

The queries generated are as below:

<User(email='howard@howard.com', fullname='Howard', company='howard', address='None', password='howard')>,  <User(email='emailhoward', fullname='None', company='None', address='None', password='passwordhoward')>

This is logical as I'm extracting everything from the table. However, when I try to use load_only to specifically select one column, in this case, the email column. The code I've used is:

db.session.query(User).options(load_only(User.address)).all()
db.session.query(User).options(load_only('email')).all()

Both commands give me the same results:

<User(email='howard@howard.com', fullname='Howard', company='howard', address='None', password='howard')>,<User(email='emailhoward', fullname='None', company='None', address='None', password='passwordhoward')>

Which is extremely weird because I should be getting just one column in the query. However, when I use this:

db.session.query(User.email).select_from(User).filter_by(email=email).first()[0]

it magically returns just one column for me. I needed to use load_only as I have dynamic tables that I want to reuse the same function, rather than maintaining many sets of functions. Can anyone advise what is the issue with the load_only command, or if I'm doing something wrong?

Thank you.

Have a look at your logs. Load only is doing exactly what you expect, but your `__str__` or `__repr__` is accessing the other columns, so you should be seeing a bunch of extra SELECT statements that fetch values for the deferred attributes as they're needed. Deferred attrs don't contain None (or some other "no value" value). They're deferred and loaded when accessed. — Ilja Everilä, May 13 '18 at 16:00
Related [SQLAlchemy load\_only and defer](https://stackoverflow.com/questions/21145702/sqlalchemy-load-only-and-defer) — Ilja Everilä, May 14 '18 at 12:01

Ilja Everilä · Accepted Answer · 2018-05-15T07:26:54.340

There's no issue, just a bit of a misunderstanding;

<User(email='howard@howard.com', fullname='Howard', company='howard', address='None', password='howard')>

is the string representation of your User model object and it is the User.__repr__() method that pulls in the deferred columns as it accesses them.

Using load_only() you define a set of columns to load initially for an entity, while deferring all others. But deferring a column does not mean it's somehow unusable or contains some "no value" marker (actually it does, under the hood). When each deferred attribute is referenced for the first time SQLAlchemy will issue a SELECT in order to fetch its value. This should be apparent from logs:

In [7]: u = session.query(User).options(load_only(User.email)).first()
2018-05-14 16:04:49,218 INFO sqlalchemy.engine.base.Engine SELECT user.id AS user_id, user.email AS user_email 
FROM user
 LIMIT ? OFFSET ?
2018-05-14 16:04:49,218 INFO sqlalchemy.engine.base.Engine (1, 0)

In [8]: u.fullname
2018-05-14 16:04:53,773 INFO sqlalchemy.engine.base.Engine SELECT user.fullname AS user_fullname 
FROM user 
WHERE user.id = ?
2018-05-14 16:04:53,773 INFO sqlalchemy.engine.base.Engine (2,)
Out[8]: 'Bar'

You can check if a column has been deferred using the inspection API. InstanceState.unloaded holds the set of keys that have no loaded value. Using that you could modify your User.__repr__ to something like:

class User(Base):
    ...

    def __repr__(self):
        state = inspect(self)
        def ga(attr):
            return (repr(getattr(self, attr))
                    if attr not in state.unloaded
                    else "<deferred>")

        attrs = " ".join([f"{attr.key}={ga(attr.key)}"
                          for attr in state.attrs])
        return f"<User {attrs}>"

Alternatively you could iterate over InstanceState.attrs displaying AttributeState.loaded_value, which evaluates to the symbol NO_VALUE if a value has not been loaded:

class User(Base):
    ...

    def __repr__(self):
        state = inspect(self)    
        attrs = " ".join([f"{attr.key}={attr.loaded_value!r}"
                          for attr in state.attrs])
        return f"<User {attrs}>"

Thank you so much! I didn't think of checking from this aspects. I switched from MySQLdb to sqlalchemy and was shocked by the learning curve. — howardlhw, May 14 '18 at 19:30
This was so informative, jumping from one answer to another. Thanks a lot for the explanation, it wasn't very clear from the documentation. It felt that load_only performs a predicate pushdown, which apparently it does, but performs automated select queries as to whenever other columns are accessed. — Biplob Biswas, Oct 21 '19 at 12:26

In SQLAlchemy, why is my load_only not filtering any columns that I have specified?

1 Answers1

Linked