Introduction

In this blog we are going to use an amazing library django-import-export which can convert database to various formats like csv, xls, json, html, yaml, tsv, ods. It can do it the other way round too which means that it can convert a file to database and it does it very smarty. Read the blogtill the end and you will know why i said smartly!

Prerequisites

Django installed

What are we building?

In this blog we will go from very simple example covering char field,Foreign Key,Many to Many field and learn how to convert import and export data into csv.

Setup

1. Open cmd.

2. Create a django project and cd into it.

django-admin startproject DemoProject
cd DemoProject

3. Create a django app.

django-admin startapp app

This is how you project structure should look like until now.

4. pip install django-import-export package

pip install django-import-export

5. Go to settings.py and add “app” (app name) and “import_export” (django-import-export) to list of “INSTALLED_APPS” as shown below.

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'import_export', # Added by us
    'app', # Add the app name you created
]

Creating Models

Lets create a few models and perform all required actions over it to easily understand how django-import-export library works.

Models:

1. Book
2. Category — category to which a book belongs
3. Author — Author of a book

Book model’s fields and relations:

1. name — char field representing name of book.
2. author — Foreign key to Author model representing author of book.
3. categories — ManyToMany field to Category model representing to which single/multiple categories book belongs.

• Open models.py inside app folder and add following code.

# app/models.py

from django.db import models

class Category(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name

class Author(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name

class Book(models.Model):
    name = models.CharField(max_length=100)
    author = models.ForeignKey(Author,on_delete = models.CASCADE ,blank=True, null=True)
    categories = models.ManyToManyField(Category, blank=True)

    def __str__(self):
        return self.name

• Now run the following command. We do this to create a file in migrations folder which keeps tracks of how database schema is altered. In our case we just added new models. 

python manage.py makemigrations

• Until now we have just noted the alterations on database schema but now we will run a command to apply the changes mentioned in migrations file to actual database (default sqlite). Also you will see User model’s migrations too which is present by default.

python manage.py migrate

• That’s it for database for now. Now lets create a superuser so that we can use django admin. Enter username,mail,password of your choice. We will require this credentials later to access admin page.

python manage.py createsuperuser

• Before moving to admin page one last thing we need to do is tell django that these are our models and we want to access it on django admin page. To do this we need to register models in “admin.py” file present in apps folder.

# app/admin.py
from django.contrib import admin
from .models import *

@admin.register(Book)
class BookAdmin(admin.ModelAdmin):
    pass

@admin.register(Author)
class AuthorAdmin(admin.ModelAdmin):
    pass

@admin.register(Category)
class CategoryAdmin(admin.ModelAdmin):
    pass

• All basic set up id done. Lets run the server now.

python manage.py runserver

• Go to http://127.0.0.1:8000/admin/ . You should be able to see all the models we registered in admin.py

• Lets create dummy data. You can do it manually but i have got a easier solution.Create a file “myscript.py” in the root directory (along side manage.py) as shown below.

Paste the following code into “myscript.py” and run it. 
Basically this code will do the following-
- Create 100 Category instances with random names.
- Create 5 Author instances with random names.
- Create 30 Book instances with random names.

import os

PROJECT_NAME = 'DemoProject'

def main():
    from app.models import Category,Book,Author
    import uuid
    from random import randrange
    
    categor_ids = []
    for i in range(100):
        c = Category.objects.create(name = str(i+1) +" - "+uuid.uuid4().hex[:6]+"-categoryname")
        categor_ids.append(c.id)
    
    author_ids = []
    for i in range(5):
        a = Author.objects.create(name = str(i+1) +" - "+uuid.uuid4().hex[:6]+"-authorname")
        author_ids.append(a.id)
    
    for i in range(30):
        #  Generate random number between 0 to 4
        author_id = author_ids[randrange(5)]

        book = Book.objects.create(
            name = str(i+1) +" - "+uuid.uuid4().hex[:6]+"-bookname",
            author = Author.objects.get(id = author_id),
        )

        #  Generate random number between 0 to 5 - this will be equal to how many category items should we pick
        num_of_category = randrange(6)

        for i in range(num_of_category):
            book.categories.add(Category.objects.get(id = categor_ids[randrange(100)]))
        
        book.save()
    

if __name__ == '__main__':
    os.environ.setdefault('DJANGO_SETTINGS_MODULE', '%s.settings' % PROJECT_NAME)
    import django
    django.setup()
    main()

python myscript.py

After executing and completion of the above command visit http://127.0.0.1:8000/admin/ and you will see that data is populated already.

Well now what? how can we convert this data to csv, json etc??

• We will need to tell django that hey provide me a button to import export data from admin page. To do this we need to modify “admin.py” in apps folder which we earlier created. 

from django.contrib import admin
from .models import *
from import_export.admin import ImportExportModelAdmin

@admin.register(Book)
class BookAdmin(ImportExportModelAdmin):
    pass

@admin.register(Author)
class AuthorAdmin(ImportExportModelAdmin):
    pass

@admin.register(Category)
class CategoryAdmin(ImportExportModelAdmin):
    pass

Now visit the admin page http://127.0.0.1:8000/admin/ and click on Book or Category or Author model. I opened Book model. You will see that import export button at the top right!

• Click on export button. And select any format and click submit. I have selected xls format. A file will get downloaded which will have all your database enteries. 

When it comes to foreign key (author field) we get primary key of author model’s instance and similarly for many to many field (categories) we get comma separated ids of category instance.

For eg. Row 1 implies book with primary key 30 has author with primary key 4 and has categories whose ids are 2,12,17,32,33.

Yayy! Look at that! With just a few button clicks now we can convert out entire model data to xls/csv/json etc.

Importing data

Now comes interesting part which is importing the data. This package handles it so smartly. Lets see how.

Lets say in the file i downloaded i do the following changes and save it:

• add one more row (i.e i want to add this row to database)

• add a category id for any one row.

Before: 

After:

Don’t forget to save the changes.

Now got to http://127.0.0.1:8000/admin/app/book/ and click on import and upload the updated file and select proper file format and click submit.

And you will see that it will automatically detect the changes you made and highlight it with operation name (eg. update,New etc) and on submitting it will apply those changes to database.

Also cross verify that it is actually added to book model and you will see that it has been reflected in database.

That’s all we had to do to convert database to file and vice versa!

To know more about the django-import-export package visit https://django-import-export.readthedocs.io/en/latest/index.html.

Conclusion:

We successfully learnt to convert django model’s data to csv,json,xls,yaml etc and the reverse.

For code you can visit https://github.com/RugvedB/django-database-to-files-and-vice-versa