Retrieving Query Data

Question

I know that regular queries or iterator query methods set and return the entire dataset in one snapshot.

eg:

my_objects = MyObject.objects.all() for rows in my_objects: # Way 1 for rows in my_objects.iterator(): # Way 2 

Question

In both methods, all rows are selected in one. Is there any way in djago so that query strings can be retrieved one by one from the database.

Why is this a strange claim

Currently, my queries are retrieved, says n lines, but sometimes I get Python and Django OperationalError (2006, "MySQL server is gone") .

in order to have a workaround for this, I am currently using weird while logic. So it was interesting whether there was any native or built-in method or my question is even logical in the first place! :)

Answer 1

I think what you're looking for is limiting your set of queries .

Quote from the link above:

Use a subset of the Pythons array syntax to limit the number of QuerySet queries to a specific number of results. This is the equivalent of the LIMIT SQL and OFFSET clauses.

In other words, if you start with an account, you can then iterate and take fragments as needed.

 cnt = MyObject.objects.count() start_point = 0 inc = 5 while start_point + inc < cnt: filtered = MyObject.objects.all()[start_point:inc] start_point += inc 

Of course, you may need to handle the error again.

Answer 2

Getting line by line can be worse. You might want to receive packages for 1000, etc. I have successfully used this Django fragment (and not my work) with very large requests. It does not feed on memory and does not cause disconnect problems.

Here is a snippet from this link:

 import gc def queryset_iterator(queryset, chunksize=1000): ''''' Iterate over a Django Queryset ordered by the primary key This method loads a maximum of chunksize (default: 1000) rows in it's memory at the same time while django normally would load all rows in it's memory. Using the iterator() method only causes it to not preload all the classes. Note that the implementation of the iterator does not support ordered query sets. ''' pk = 0 last_pk = queryset.order_by('-pk')[0].pk queryset = queryset.order_by('pk') while pk < last_pk: for row in queryset.filter(pk__gt=pk)[:chunksize]: pk = row.pk yield row gc.collect() 

Answer 3

To solve the problem (2006, "MySQL server is gone"), your approach is not so logical. If you get to the database for each record, it will increase the number of queries that themselves will create a problem in the future as your application grows. I think you should close the mysql connection after iterating over all the elements of the result, and then if you try to make another request, django will create a new connection.

 from django.db import connection: connection.close() 

Refer to this for more details.