Sqlalchemy – Difference between query and query.all in for loops

by

Nope, there is no difference in DB traffic. The difference is just that for row in session.Query(Model1) does the ORM work on each row when it is about to give it to you, while for row in session.Query(Model1).all() does the ORM work on all rows, before starting to give them to you.

Note that q.all() is just sugar for list(q), i.e. collecting everything yielded by the generator into a list. Here is the source code for it, in the Query class (find def all in the linked source):

def all(self):
    """Return the results represented by this ``Query`` as a list.

    This results in an execution of the underlying query.

    """
    return list(self)

… where self, the query object, is an iterable, i.e. has an __iter__ method.

So logically the two ways are exactly the same in terms of DB traffic; both end up calling query.__iter__() to get a row iterator, and next()ing their way through it.

The practical difference is that the former can start giving you rows as soon as their data has arrived, “streaming” the DB result set to you, with less memory use and latency. I can’t state for sure that all the current engine implementations do that (I hope they do!). In any case the latter version prevents that efficiency, for no good reason.

More Related Contents:

  1. What is the difference between SQLAlchemy Core and ORM?
  2. How to convert SQLAlchemy row object to a Python dict?
  3. jsonify a SQLAlchemy result set in Flask [duplicate]
  4. Using OR in SQLAlchemy
  5. SQLAlchemy support of Postgres Schemas
  6. SQLAlchemy ORM conversion to pandas DataFrame
  7. SQLAlchemy: unexpected results when using `and` and `or`
  8. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  9. sqlalchemy: how to join several tables by one query?
  10. How to create an SQL View with SQLAlchemy?
  11. Serve image stored in SQLAlchemy LargeBinary column
  12. SQLAlchemy: cascade delete
  13. When do I need to use sqlalchemy back_populates?
  14. SQLAlchemy ManyToMany secondary table with additional fields
  15. Can SQLAlchemy be used with Google Cloud SQL?
  16. Updates to JSON field don’t persist to DB
  17. SQLAlchemy, Psycopg2 and Postgresql COPY
  18. ImportError: No module named MySQLdb
  19. How do I select literal values in an sqlalchemy query?
  20. Get inserted key before commit session
  21. Querying with function on Flask-SQLAlchemy model gives BaseQuery object is not callable error
  22. SQLAlchemy multiple foreign keys in one mapped class to the same primary key
  23. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  24. SQLAlchemy default DateTime
  25. SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see
  26. pandas to_sql all columns as nvarchar
  27. sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table
  28. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  29. Can’t change system default SQLite binary
  30. add column to SQLAlchemy Table

Leave a Comment