17
Automatic Update of Django Models from a Google Spreadsheet
Often Data Model definition and Data Model Implementation are performed by different people and a little change in Data Model definition should be transformed into an implemented model as soon as possible.
The speed with which changes in the model can be translated into implementation depends on how definition and implementation are related to each other.
In this tutorial I propose a mechanism to directly translate changes in the model into changes in the implementation. I exploit the following tools:
- Google Spreadsheet for the Data Model definition
- Python to translate changes in the spreadsheet into changes for Django
- Django to render the model in a Web interface.
Three steps are needed to automatically update a Data Model in Django through a Google Spreadsheet:
- Data Model Definition in Google Spreadsheet
- Model Translation from a Google Spreadsheet to a Django Model
- Model Update in Django
Firstly, I define a basic Data Model. For example, I can consider three tables:
Person(id,name,surname,birthDate,sex)Organization(id,name,address,website,locality)Staff(person_id,organization_id)
Then, I create a new Google Spreadsheet and I devote one sheet for each table, as shown in this example. For each table, I define the following columns:
- attributes — the list of attributes for a table
- description — what the attribute represents
- data type — the attribute data type
- max length — the max length of char type variable. It is valid only for char attributes
- key — specifies if an attribute is a key (primary or foreign)
- foreign key table — if the attribute is a foreign key, specify the associated table
The Data Model can be dynamically updated by the Model Designer, without taking care of any implementation. They can even add other tables to the spreadsheet or modify existing ones, by adding or removing attributes.
It is sufficient that the Model Designer respects the structure of the table.
The next step involves Model Translation from the Google Spreadsheet to Django. This can be achieved by dynamically building the Django Model with the content of the Google Spreadsheet. In practice, I can write a Python code, which generates the Django model.py script on the fly, depending on the Google Spreadsheet.
Model Translation can be achieved in three steps:
- Read the Google Spreadsheet
- Create a Mapping between tables and Django model classes
- Create the content of the model.py script
In my previous article, I described how to load the content of a Google Document in Python. Regarding Google Spreadsheets, the procedure for authentication is quite similar, with the difference that Google Spreadsheets APIs should be enabled in the Google Console, instead of Google Documents API.
Continue Reading on Towards Data Science
17