SQLAlchemy `.fetchmany()` vs `.limit()`

Question

Assuming query is some already defined query. As far as I can tell, connection.execute(query).fetchmany(n) and connection.execute(query).limit(n).fetchall() apparently return the same result set. I'm wondering if one of them is more idiomatic or — more importantly — more performant?

Example usage would be:

query = select([census.columns.state, (census.columns.pop2008 - census.columns.pop2000).label("pop_change")]).group_by(census.columns.state).order_by(desc("pop_change"))
results_1 = query.limit(5).fetchall()
results_2 = connection.execute(query).fetchmany(n)    #`results_2` = `results_1`

Answer 1

I have found fetchmany to be very useful when you need to get a very large dataset from the database but you do not want to load all of those results into memory. It allows you to process the results in smaller batches.

result = conn.execution_options(stream_results=True).execute(
  SomeLargeTable.__table__.select()
)
while chunk:= result.fetchmany(10000) ## only get 10K rows at a time
  for row in chunk:
    ## process each row before moving onto the next chunk

Answer 2

limit will be a part of the sql query sent to the database server.

With fetchmany the query is executed without any limit, but the client (python code) requests only certain number of rows.

Therefore using limit should be faster in most cases.