Import data from excel into database using Django Rest Framework

Import data from Excel into the database using Django Rest Framework with Example



In many applications, it is necessary to import data into a database from external sources like Excel files. Since it offers a solid foundation for creating RESTful APIs in Django, the Django Rest Foundation (DRF) is the perfect choice for managing data imports. We will examine how to use DRF to import data from an Excel file into a database with a step-by-step example.


Import data from excel into database using Django Rest Framework


Prerequisites 

Please confirm that you meet the following prerequisites before we start:

  • The Django Rest Framework and Django are key ideas.
  • On your computer, Python and Django are already installed.
  • It's necessary to have a practical grasp of databases, especially MySQL.
  • knowledge of the Pandas library and Excel file formats, such as.xlsx.

Implementation

Step-1 Create a Django Project

    1. Create a new Django project:
            
                django-admin startproject excel_import_data_project


    2. Create a Django app in a side project:
            
                cd excel_import_project                 python manage.py startapp excel_import_app

     3. Update the settings in the settings.py file:
            
                In settings.py, add 'excel_import_app' to the INSTALLED_APPS list and also add database              settings in the Database section.

Step-2 Create a Django Model in the side model.py file

Following that, we must develop a Django model that represents the table into which we wish to import the data. Consider a basic Person model with fields name, age, and email for this example.

Define the Person model in excel_import_app/models.py as follows:

                        from django.db import models

                        class Person(models.Model):
                            name = models.CharField(max_length=255)
                            age = models.IntegerField()
                            email = models.EmailField()


After specifying the model, use the following commands to create the corresponding database table:

                    python manage.py makemigrations
                    python manage.py migrate


Step-3 Create a Django Serializer in the side serializers.py file

A serializer that can check and process the incoming data is required to manage data import. Create a PersonSerializer serializer class in excel_import_app/serializers.py:


                      from rest_framework import serializer
                     from excel_import.models import Person

                    class PersonSerializer(serializers.ModelSerializer):
                        class Meta:
                           model = Person
                           fields = '__all__'

The Person model is mapped using the serializer class, which contains all fields.

Step-4 Create a Django View in the side views.py file

Let's now design a view to handle the Excel data import. Define a class-based view named ExcelImportView in excel_import_app/views.py:


                     from rest_framework.views import APIView
                    from rest_framework.response import Response
                    from excel_import.serializers import PersonSerializer
                   import pandas as pd

                  class ExcelImportView(APIView):
                      def post(self, request):
                         file = request.FILES['file']
                        df = pd.read_excel(file)

                        serializer = PersonSerializer(data=df.to_dict('records'), many=True)
                        if serializer.is_valid():
                            serializer.save()
                            return Response({"message": "Data imported successfully"})
                       else:
                           return Response(serializer.errors, status=400)

This view manages the POST request for data import and extends DRF's APIView. The provided Excel file is read and converted into a dictionary of records using the Pandas library. The data is then checked and saved by using the PersonSerializer.


Step-5 Create a Django URL pattern in the side urls.py file

The URL pattern must be defined in order to access the import view. Insert the following code into excel_import/urls.py:


                     from django.urls import path
                    from excel_import.views import ExcelImportView

                    urlpatterns = [
                    path('import-excel/', ExcelImportView.as_view(), name='import-excel'),
                    ]

Step-6 Test the data import

With everything set up, let's test the data import functionality.

        1. Start the Django development server:
            
                python manage.py runserver
    
        2. Open a web browser and go to the following address:
            
               http://localhost:8000/import-excel/

        3. Upload an Excel file with the data to import.
            
        4. Submit the form and check to see if the data was imported correctly. Validation errors will          be displayed if they exist.  


Above is the example of data import using the Serializers, make sure the Excel columns name should be match with database column names, if you import data without a serializer then do the below changes in views.py:

                   def import_excel_data(file_path):
                        # Read the Excel file into a Pandas DataFrame
                        df = pd.read_excel(file_path)

                        # Iterate over each row in the DataFrame and create a Person object
                        for _, row in df.iterrows():
                        person = Person(
                        name=row['name'],
                        age=row['age'],
                        email=row['email']
                        )
                       person.save()

Conclusion

In this tutorial, we looked at how to import data from an Excel file into a database using the Django Rest Framework. To handle data import, we launched a Django project, created a serializer and view, and built a model to represent the database table. By simply following these instructions, you may extend this capability to import data from Excel files into your Django-based projects.

Many industries, such as data migration, huge data uploads, and data synchronisation, frequently need the import of data. By using Django Rest Framework and technologies like Pandas, you may expedite the process of importing data from Excel files into your Django project while ensuring data integrity.       

Post a Comment

0 Comments