Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
447 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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}>"

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...