A python script can be useful in cases where you import data from Excel files on a regular basis. Usually, to load data from Excel Files to Django Databases, django fixture files used.
A fixture file is usually a JSON or YAML file containing a list of records or objects, where each record corresponds to a row in a database table. These fixture files are then used to populate Databases.
But what happens if you need to add new data to your database regularly (Say monthly)? You can manually do this everytime but this can soon become cumbersome.
In this case, you can write a Python script to automate the conversion process and then use a cron job or other scheduling tool to run the script on a regular basis.
In this article, we’ll look at how to convert data from an Excel file into a Django fixture file using Python.
(Related Articles: Automate Tasks using Cron Jobs)
Prerequisites:
Before we get started, you’ll need the following:
- A Django project set up with a database
- Python installed on your machine
- An Excel file with data that you want to convert to a fixture file
- Familiarity with Django fixtures
To learn more about fixtures and how to manually load data into django databases you can check out these articles:
Fixture Files in Django Web Framework
How to load data from an Excel file into a Django database
Now let’s make our script:
We’ll use pandas to read the data and convert it into a dictionary, and then use the Django built-in serializers to convert that dictionary into a fixture file.
Step 1 : Install pandas
To install pandas, open a terminal and run:
pip install pandasStep 2 : Read the Excel file:
We’ll use the pandas to read the data from the Excel file and convert it into a pandas dataframe:
import pandas as pd
dataframe = pd.read_excel('path/to/excel/file.xlsx')
Where path/to/excel/file.xlsx is the path to your Excel file. So if your excel file is in a folder, mycontacts in C drive and is called contacts.xlsx then the code would be :
import pandas as pd
dataframe = pd.read_excel('C:/mycontacts/contacts.xlsx')
Convert the dataframe to a dictionary:
Now that we have the data in a pandas dataframe, we can easily convert it into a dictionary using the to_dict() method. We’ll also specify the orient parameter as ‘records’ to ensure that the data is in the correct format for a Django fixture file.
data_dict = dataframe.to_dict(orient='records')
Serialize the data to a fixture file:
Finally, we can use the Django built-in serializers to convert the dictionary to a fixture file. We’ll use the dumpdata management command to do this.
from django.core.management import call_command
with open('path/to/fixture/file.json', 'w') as file:
call_command('dumpdata', 'app_name.ModelName', indent=4, stdout=file)
Where path/to/fixture/file.json is the path to where you want to save your fixture file. Also, replace app_name.ModelName with the name of your app and model. You can also specify multiple models by separating them with a comma.
Conclusion:
In this blog post, we’ve seen how to use Python to convert data from an Excel file into a Django fixture file. We used pandas to read the Excel file, converted the data to a dictionary, and then used Django’s built-in serializers to convert the dictionary to a fixture file. This can be a very useful tool when working with large data sets that need to be pre-populated into a Django database.