DEV Community

loading...

How to convert database model to csv,xls,json etc and vice versa using django-import-export?

Rugved
・6 min read

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

  • Open cmd.
  • Create a django project and cd into it.
django-admin startproject DemoProject 
cd DemoProject
Enter fullscreen mode Exit fullscreen mode
  • Create a django app.
django-admin startapp app
Enter fullscreen mode Exit fullscreen mode

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

*

pip install django-import-export package
Enter fullscreen mode Exit fullscreen mode
  • 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
]
Enter fullscreen mode Exit fullscreen mode

Creating Models

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

Models:

  • Book
  • Category — category to which a book belongs
  • Author — Author of a book

Book model’s fields and relations:

  • name — char field representing name of book.
  • author — Foreign key to Author model representing author of book.
  • 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

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

python manage.py runserver
Enter fullscreen mode Exit fullscreen mode

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

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.
image

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()
Enter fullscreen mode Exit fullscreen mode
python myscript.py
Enter fullscreen mode Exit fullscreen mode

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.
image

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
Enter fullscreen mode Exit fullscreen mode

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!

image

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.

image

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) image
  • add a category id for any one row.

Before:
image

After:
image

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.

image

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

image

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

Discussion (0)