CODEWITHSUNDEEP
mysqldjango
Ajay Kumar
Ajay Kumar

Reputation: 1655

Django retrieve data from database to template

I have a problem on retrieving the data from database, I'm using Mysql database and one table which contains 15 fields which I'm going to display it on template in a table.

It has more than 4 million records in the table, 

def index(request):
    args = {}
    data = Mymodel.objects.all()
    args['data'] = data
    return render(request, 'index.html', args)

I tried this way but the data loads very very slowly,

and next approach I tried for database connections

def index(request):
    args = {}
    sql = "select * from mymodel"
    cursor = connection.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    args['result'] = result
    return render(request, 'index.html', args)

This also does the same, I need to load the data much faster. Please suggest to me any approach that would load the data faster.

Upvotes: 2

Views: 10204

Answers (3)

Muhammad Hassan
Muhammad Hassan

Reputation: 14391

You can use pagination if you want your system to work for thousands of records. Django genaric ListView will help you in this case. They are easy to use. They works like this

class YourView(ListView):
    template_name = 'books/acme_list.html'
    context_object_name = 'object_list'
    queryset = TableName.objects.all()
    paginate_by = 50

Your template will be like this

<table id="exam">
  {% for object in object_list %}
  <tr>
    <td>{{ object }}</td>
  </tr>
  {% endfor %}
</table>

And your paginate section will be like this

{% if is_paginated %}
  <ul class="pagination">
    {% if page_obj.has_previous %}
      <li>
         <span><a href="?page={{ page_obj.previous_page_number }}">Previous</a></span>
     </li>
    {% endif %}
      <li class="">
        <span>Page {{ page_obj.number }} of {{ page_obj.paginator.num_pages }}.</span>
      </li>
    {% if page_obj.has_next %}
      <li>
        <span><a href="?page={{ page_obj.next_page_number }}">Next</a></span>
      </li>
    {% endif %}
      </ul>
{% endif %}

You can find further detail in this link and Django documentation.

Upvotes: 1

fabio.sussetto
fabio.sussetto

Reputation: 7055

Since you're already executing a raw query, I don't think you'll be realistically able to do much better than what you have.

Is there any reason why you can't paginate the results? Normally you never return all the data available.

You can try and use a QuerySet.iterator to avoid having to load all your instances in memory at once. Also, QuerySet.values would return dictionaries instead of full-blown model instances, reducing memory usage.

If you absolutely need to and you get into request timeouts, one option is to run the actual data retrieval in the background. For example, you could use Celery and run the query as part of a task. Your view would trigger the Celery task and return a task identifier in the response. The consumer could then poll a "job status" endpoint passing such task id and retrieve the results when they are ready. You'd still need to store the results somewhere else (faster) than your db, i.e. Redis (which you can easily use as a Celery backend).

This approach is obviously more convoluted and would add a few moving parts to your system and you're likely to get into other issues anyway (e.g. the response size). Using pagination if possible would be a lot easier.

Upvotes: 1

Shang Wang
Shang Wang

Reputation: 25539

One concept correction before the solution is that using raw sql doesn't do any better than django ORM and you also lost the ability to use ORM api to display the data easily.

The common solution is to use django built in pagination to show only certain amount of records per page. Django doc has very detailed explanation about the usage of pagination.

Upvotes: 1

Related Questions