DEV Community: Rugved

Admits and Rejects | MS in US | Fall 2022

Rugved — Tue, 26 Jul 2022 04:09:22 +0000

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

Rugved — Fri, 14 May 2021 07:36:42 +0000

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

Create a django app.

django-admin startapp app

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

pip install django-import-export package

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:

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

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

Django standalone script to take database backup

Rugved — Thu, 06 May 2021 06:00:26 +0000

In this blog we will learn to write standalone script which will be a generalized script to convert entire database to csv/xls files etc.

Standalone script is basically a script where you don’t need to run server. It is “stand alone” i.e it is capable of performing tasks without any other requirement.

This blog was initially published on https://www.gyaanibuddy.com/blog/django-standalone-script-to-take-database-backup/

Prerequisites

A django project with any database along with some models whose data you wish to take backup.

Basic template

This code is going to be the bare minimum skeleton of our script.

Create a script.py file in the same directory as manage.py file.
Paste the below code snippet.
Rename PROJECT_NAME to your project’s name

import os
PROJECT_NAME = 'ENTER YOUR PROJECT NAME HERE'

def main():
    # import statemts
    # from app.models import Author,Category,Book

    # code logic - anything you want


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

Run the code by entering python script.py

If it ran with no errors then you are good to go. If you have any error, then depending on error you may have path error, installed app error etc.

Backup script

If you are only interested in the script, scroll to the bottom and just paste it in script.py and run it.

Lets look at some of the basic functions which will help us get the script ready.

Firstly, we need list of all the models.

from django.apps import apps 
model_list = apps.get_models() 
model_name_list = [x.__name__ for x in model_list]

Now that we have the model, we will iterate over every single table and get its schema i.e the columns (or fields) in the table. We need the field/column names so that we can write them as column names to csv/xls file.

for model in model_list:
    all_fields = model._meta.get_fields()
    columns = [x.name for x in all_fields]

Lets iterate over entire database and write the data to csv/xls.

with open(f'csvs/{model.__name__}.csv', mode='w') as csv_file:
  writer = csv.writer(csv_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
  # Writing column names
  writer.writerow(columns)
  objects = model.objects.all()

  for obj in objects:
    row = [str(getattr(obj, field_name,"NA")) for field_name in columns]
    writer.writerow(row)

You will observe that a csv folder is created and inside it will contain one csv file for every table. So if you have Author, Book as tables, you will have Author.csv and Book.csv saved in the csv folder in the project root directory.

'''
django standalone script to take backup of database in the form of csvs for each model.
- Place it in the same directory as manage.py file
- Rename PROJECT_NAME to your project's name
'''

import os

PROJECT_NAME = '--- ENTER YOUR PROJECT NAME HERE ---'

def main():
    from django.apps import apps
    import csv

    model_list = apps.get_models()
    model_name_list = [x.__name__ for x in model_list]

    for model in model_list:
        all_fields = model._meta.get_fields()
        columns = [x.name for x in all_fields]

        if not os.path.exists('csvs'):
            os.makedirs('csvs')

        with open(f'csvs/{model.__name__}.csv', mode='w') as csv_file:
            writer = csv.writer(csv_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)

            # Writing column names
            writer.writerow(columns)

            objects = model.objects.all()

            for obj in objects:
                row = [str(getattr(obj, field_name,"NA")) for field_name in columns]
                writer.writerow(row)

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

Based on your preference you can store the database in any form (Eg. sqlite,xls,csv,json etc). In the above script i have created a “csv” folder and inside it contains individual csv file for each model. Each csv will contain data of one model and will have column name as field names and each row will be single entry of that specific model.

Yayy! That’s all you need to do to write standalone script in django to take backup of database.