memory-efficient built-in SqlAlchemy iterator/generator?

by

Most DBAPI implementations fully buffer rows as they are fetched – so usually, before the SQLAlchemy ORM even gets a hold of one result, the whole result set is in memory.

But then, the way Query works is that it fully loads the given result set by default before returning to you your objects. The rationale here regards queries that are more than simple SELECT statements. For example, in joins to other tables that may return the same object identity multiple times in one result set (common with eager loading), the full set of rows needs to be in memory so that the correct results can be returned otherwise collections and such might be only partially populated.

So Query offers an option to change this behavior through yield_per(). This call will cause the Query to yield rows in batches, where you give it the batch size. As the docs state, this is only appropriate if you aren’t doing any kind of eager loading of collections so it’s basically if you really know what you’re doing. Also, if the underlying DBAPI pre-buffers rows, there will still be that memory overhead so the approach only scales slightly better than not using it.

I hardly ever use yield_per(); instead, I use a better version of the LIMIT approach you suggest above using window functions. LIMIT and OFFSET have a huge problem that very large OFFSET values cause the query to get slower and slower, as an OFFSET of N causes it to page through N rows – it’s like doing the same query fifty times instead of one, each time reading a larger and larger number of rows. With a window-function approach, I pre-fetch a set of “window” values that refer to chunks of the table I want to select. I then emit individual SELECT statements that each pull from one of those windows at a time.

The window function approach is on the wiki and I use it with great success.

Also note: not all databases support window functions; you need Postgresql, Oracle, or SQL Server. IMHO using at least Postgresql is definitely worth it – if you’re using a relational database, you might as well use the best.

More Related Contents:

  1. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  2. How to disable SQLAlchemy caching?
  3. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  4. How to build a flask application around an already existing database?
  5. SQLAlchemy ON DUPLICATE KEY UPDATE
  6. Bulk insert with SQLAlchemy ORM
  7. ImportError: No module named MySQLdb
  8. flake8 complains on boolean comparison “==” in filter clause
  9. Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?
  10. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  11. Retrieve query results as dict in SQLAlchemy
  12. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  13. How can I connect to MySQL in Python 3 on Windows?
  14. Python & MySql: Unicode and Encoding
  15. Authentication plugin ‘caching_sha2_password’ is not supported
  16. How can I bind a list to a parameter in a custom query in SQLAlchemy?
  17. Setting Django up to use MySQL
  18. When to close cursors using MySQLdb
  19. Bulk Insert A Pandas DataFrame Using SQLAlchemy
  20. Python MySql Insert not working
  21. How can I use psycopg2.extras in sqlalchemy?
  22. trapping a MySql warning
  23. Dynamic Class Creation in SQLAlchemy
  24. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  25. Simple Subquery with OuterRef
  26. How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?
  27. SQLAlchemy Joinedload filter column
  28. SQLAlchemy func.count on boolean column
  29. Connecting to SQL Server 2012 using sqlalchemy and pyodbc
  30. using sqlalchemy to load csv file into a database

Leave a Comment