What is the difference between with_entities and load_only in SQLAlchemy?

Question

When querying my database, I only want to load specified columns. Creating a query with with_entities requires a reference to the model column attribute, while creating a query with load_only requires a string corresponding to the column name. I would prefer to use load_only because it is easier to create a dynamic query using strings. What is the difference between the two?

load_only documentation

with_entities documentation

Answer 1

There are a few differences. The most important one when discarding unwanted columns (as in the question) is that using load_only will still result in creation of an object (a Model instance), while using with_entities will just get you tuples with values of chosen columns.

>>> query = User.query
>>> query.options(load_only('email', 'id')).all()
[<User 1 using e-mail: n@d.com>, <User 2 using e-mail: n@d.org>]
>>> query.with_entities(User.email, User.id).all()
[('n@d.org', 1), ('n@d.com', 2)]  

load_only

load_only() defers loading of particular columns from your models. It removes columns from query. You can still access all the other columns later, but an additional query (in the background) will be performed just when you try to access them.

"Load only" is useful when you store things like pictures of users in your database but you do not want to waste time transferring the images when not needed. For example, when displaying a list of users this might suffice:

User.query.options(load_only('name', 'fullname'))

with_entities

with_entities() can either add or remove (simply: replace) models or columns; you can even use it to modify the query, to replace selected entities with your own function like func.count():

query = User.query
count_query = query.with_entities(func.count(User.id)))
count = count_query.scalar()

Note that the resulting query is not the same as of query.count(), which would probably be slower - at least in MySQL (as it generates a subquery).

Another example of the extra capabilities of with_entities would be:

query = (
    Page.query
    .filter(<a lot of page filters>)
    .join(Author).filter(<some author filters>)
)
pages = query.all()

# ok, I got the pages. Wait, what? I want the authors too!
# how to do it without generating the query again?

pages_and_authors = query.with_entities(Page, Author).all()