Download link for database in Django

Question

I want to put a link in my Django app to download app's data from MySQL server as CSV format. Is there any easy way to do that?

Correction: When I'm saying database, I meant related tables as one file, which means I need to join them before downloading. Since I'm doing this for the client and they do not need to see all tables including log and user tables. And they can use only one file for basic reporting instead of the entire relational database. So, first I need to denormalize the relational database and make it ready to be downloaded.

I'd suggest looking into django import/export – Foon Sep 03 '16 at 00:54 — Foon, Sep 03 '16 at 00:54

score 1 · Answer 1 · edited May 23 '17 at 12:32

Somewhat a duplciate of this: django download csv file using a link

You could do something like this:

from io import StringIO

from django.core import management


def create_fixture(app_name, filename):
    buf = StringIO()
    management.call_command('dumpdata', app_name, stdout=buf)
    buf.seek(0)
    with open(filename, 'w') as f:
        f.write(buf.read())


class YourPage(...):
    ....

    def dispatch(self, *args, **kwargs):
        create_fixture('<yourapp>', '<yourapp>/static/reports/test.csv')
        return super(YourPage, self).dispatch(*args, **kwargs)

Then in your view, (assuming you configured static file paths correctly)

{% load staticfiles %}
<a href="{% static '<yourapp>/test.csv' %}">Download CSV</a>

Obviously don't actually do the data dump inside your view logic, as this would slow stuff down, you might want to look into doing it async elsewhere in your code base.

Thanks Jack, but I found an easier solution. To be frank, I couldn't implement yours to my app. — Dogan Askan, Sep 06 '16 at 01:35

score 0 · Answer 2 · answered Sep 06 '16 at 01:46

I solve this issue by creating an external function to join and download the table then I provide a link in my static file pointing that downloaded file.

Here is my helper function,

def database_downloader():
    import pymysql # run pip install pymysql if this fails
    import sys
    import time
    import csv

    start = time.time()
    connect = 0
    attempt = 1
    while connect==0: #if connection is not secured, will try again in 3 seconds.
        try:
            print "connecting, attempt "+str(attempt)
            conn = pymysql.connect(host='url', port=3306, user='username', passwd='pass', db='db', autocommit=True) #setup our credentials
            cur = conn.cursor()
            connect = 1
        except:
            print "try again in 3 seconds"
            time.sleep(3)
            attempt+=1
            continue

    print "connected to server in " +str(time.time()-start)+ " seconds."

    out_file = open("main_app/static/main_app/reports/output.csv", "wb")
    writer = csv.writer(out_file)

    sql = "SELECT * FROM main_app_basic_info b LEFT JOIN main_app_add_info a ON a.Student_ID = b.Student_ID;"
    cur.execute(sql)

    column_names = []
    for i in cur.description:
       column_names.append(i[0])
    writer.writerow(column_names)

    for i in cur.fetchall():
        writer.writerow(i)

    print "Downloading completed in " + str((time.time()-start)) + " seconds."
    out_file.close() # you need to close to save before sending

Then I run this function every time the page loads to keep it updated and ready to download. Like this,

def index(request):
    student_list = basic_info.objects.order_by('-id')[:5]
    student_list_full = basic_info.objects.order_by('-id')
    context = {'student_list': student_list, 'student_list_full': student_list_full}
    database_downloader() # this downloads the database in every refresh to main_app/reports/output.csv
    return render(request, 'main_app/index.html', context)

Lastly, I add this into my template,

{% load static %}
<div><a href="{% static "main_app/reports/output.csv" %}" download>Click Here to Download to Database</a></div>

I'm still not perfectly sure that this is the best solution. But this is the one I'm capable of doing now, and it works well.

Download link for database in Django

2 Answers2