SQLAlchemy Performance Tip
Updated 2026-08-29: engine.execute() was removed in SQLAlchemy 2.0, and the oursql recommendation no longer installs on Python 3.
Mobify: SQLAlchemy memory magic
“If you can process the results of database queries iteratively (and very often you can), stream the results”
“Since the data needs to travel over the network from the database whether it’s streamed or not, this doesn’t add a huge overhead, but we’ll see that it reduces memory requirements.”
Example (using Flask-SQLAlchemy):
with db.engine.connect().execution_options(stream_results=True) as conn:
for row in conn.execute(query):
...
However, that’s not the full story when it comes to MySQLdb (MySQL-python). http://stackoverflow.com/a/3699677
If you’re doing this with MySQL, the driver also has to use a server-side cursor or it buffers the whole result set regardless. mysqlclient, the maintained fork of MySQL-python, and PyMySQL both provide SSCursor for that. I originally recommended oursql here, but its last release was in 2016 and it does not install on Python 3.