Handling large ResultSets

1. Handling large ResultSets

So what do we do when we have really complex queries with large result sets?

2. Dealing with large ResultSets

Dealing with large result sets can be problematic, as we might run out of memory or disk space to store the results. Thankfully, SQLAlchemy has a fetchmany method that allows us to retrieve results so many at a time. It works by passing the number of records we want at once to the fetchmany method and using the method in a loop. When there are no more records, fetchmany will return an empty list. Because the result proxy does not know when we are done calling fetchmany, we must call the close method on the result proxy when we are done. Let's look at an example.

3. Fetching many rows

I want to count how many results we have for each state; however, we have a HUGE table so I need to work in smaller groups of records with fetchmany. We're going to do this in a while loop. Recall that while loops will check to see if a variable or expression is true and if so, it will continue running a loop. When the condition is false, the loop stops executing. In this example, we already have set more_results to be True, we also started a state_count dictionary to hold the count for each state, and we have already executed the query and stored the results proxy as results_proxy. We start the while loop by checking to see if more_results is True. Then inside the loop, we fetch 50 records from the results proxy and store that as partial_results. We immediately follow that up by checking to see if partial_results is an empty list. Remember that is how we know there are no more records to fetch. If it is an empty list, we update more_results to be False so we will exit the loop. Next we loop over the partial_results and increment the state_count for that records state by one. So that will keep running until we get an empty list back from fetchmany and exit the while loop. Once we exit the while loop, we close the results_proxy so the database and SQLAlchemy know we are done with the large result set.

4. Let's practice!

Now it's you're turn to write queries while handling a large ResultSet.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.