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='[email protected]', 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='[email protected]', 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.

Answer 1

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

<User(email='[email protected]', 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}>"