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

1 Data Model Definition

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.

2 Model Translation to a Django Model

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

2.1 Read the Google Spreadsheet

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