Django Export Model Data to CSV

In this article, you will learn how to export model data to a CSV file using the Django.

Django is a free, open-source, Python-based framework. It enables fast development of any type of web application. It is secure, maintainable, portable, and scalable. The main advantages of using Django are that it has fully loaded common web development tasks, like administration, authentication, site maps, etc.

CSV (Comma-separated-values) file is widely used in business, data-based applications for data exchange. Data import and export from database to CSV is most in demand in web applications. The data in CSV is stored as sequences of records. With the help of Django, we can easily store the database fetched results in a CSV file.

Suppose we have a 'Students' table-

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `roll` varchar(20) NOT NULL,
  `sclass` varchar(50) NOT NULL,
  `fname` char(100) NOT NULL,
  `lname` char(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `students` (`id`, `roll`, `sclass`, `fname`, `lname`) VALUES
(1, '12', '1A', 'Priska', 'Kashyap'),
(2, '11', '2A', 'Aditi', 'Vyas'),
(3, '31', '2B', 'Smith', 'Goal'),
(4, '39', '1C', 'Jorz', 'Gaga');

Change settings in 'settings.py'

The default database setting in Django is for 'sqlite3', so we need to change this to 'MySQL'. These are the changes that we have made for our project.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'school',
        'USER': 'root',
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '3306'
    }
}

Model

We have placed the following code in 'models.py' to map with the 'students' table.

from django.db import connections
from django.db import models

# Create your models here.

class Student(models.Model):   
    roll = models.CharField(max_length=100)
    sclass = models.CharField(max_length=100)
    fname = models.CharField(max_length=100)
    lname = models.CharField(max_length=100)
    class Meta:
        db_table = "students"

View

In the 'views.py', we have added 'import csv' to import the CSV properties. In this article, we have used 'csv.writer()' method to write on a CSV file and 'writer.writerow()' method to write on each row in your CSV file. We have stored the query set in a variable and further used the 'values_list()' method to return the tuples when we iterated over it.

from django.shortcuts import render,redirect
from demoapp.models import Student
from django.http import HttpResponse

import csv

# Create your views here.

def exportcsv(request):
    students = Student.objects.all()
    response = HttpResponse('text/csv')
    response['Content-Disposition'] = 'attachment; filename=students.csv'
    writer = csv.writer(response)
    writer.writerow(['ID', 'RollNo', 'Class', 'First Name', 'Last Name'])
    studs = students.values_list('id','roll', 'sclass', 'fname', 'lname')
    for std in studs:
        writer.writerow(std)
    return response

urls.py

At last, we have added a path to route the page to a particular link.

from django.contrib import admin
from django.urls import path
from demoapp import views

urlpatterns = [
    path('admin/', admin.site.urls),
    path('exportcsv', views.exportcsv),
]

When you open the 'http://127.0.0.1:8000/exportcsv' on the browser, the CSV file will be automatically downloaded.

Related Articles