Generating Models and Import Scripts from Excel with Python

Preface

I recently ran into a requirement: there were dozens of Excel files, each with different fields. Most of them have the first row as the header, followed by thousands or tens of thousands of rows of data, and all the data in these Excel files needed to be added into an already-live Django project

That would mean creating a table for each Excel file, then importing them one by one

That's way too inefficient—I couldn't stand it

So I built a wheel that automatically generates Models and import scripts

Approach

First, bring out pandas—its DataFrame is very convenient for processing data

After pandas loads the Excel file, extract the headers; we'll use the headers to generate the fields of the data table. Some Excel files have Chinese headers, so they need to be converted first.

At first I wanted to use a translation API to translate everything into English, but I found that the free ones are slow and have quotas, and Microsoft and DeepL both require you to apply, which is a hassle. Might as well just use a pinyin conversion library and convert everything to pinyin~

Then the field lengths also need to be determined, or we could use unlimited-length TextField for everything

After weighing it, I still went ahead and implemented field-length determination logic: traverse the entire table, find the longest value for each field, then add an offset as the max length.

Next, generate the Model class. Here I used the jinja2 templating language: first write a rough template, then generate based on the extracted field names and such.

Finally, generate the admin configuration and the import script; same idea, also using jinja2 templates.

Implementation

That's a brief intro to the approach; now let's get to the code.

Just a few lines—Python is very economical with code~

Models

First, define a couple of models

Field Model

class Field(object):
    def __init__(self, name: str, verbose_name: str, max_length: int = 128):
        self.name = name
        self.verbose_name = verbose_name
        self.max_length = max_length

    def __str__(self):
        return f'<Field>{self.name}:{self.verbose_name}'

    def __repr__(self):
        return self.__str__()

Model Model

To comply with Python's variable naming conventions, the snake_name property uses a regular expression to convert camelCase to snake_case

class Model(object):
    def __init__(self, name: str, verbose_name: str, id_field: Field, fields: List[Field]):
        self.name = name
        self.verbose_name = verbose_name
        self.id_field = id_field
        self.fields: List[Field] = fields

    @property
    def snake_name(self):
        import re
        pattern = re.compile(r'(?<!^)(?=[A-Z])')
        name = pattern.sub('_', self.name).lower()
        return name

    def __str__(self):
        return f'<Model>{self.name}:{self.verbose_name}'

    def __repr__(self):
        return self.__str__()

Code Templates

Implemented with jinja2.

jinja2 itself is what frameworks like Flask and Django use to render web pages.

But it works pretty well used on its own too. My DjangoStarter framework also uses this jinja2 to auto-generate CRUD code~

Model Template

# -*- coding:utf-8 -*-
from django.db import models

class {{ model.name }}(models.Model):
    """{{ model.verbose_name }}"""
    {% for field in model.fields -%}
    {{ field.name }} = models.CharField('{{ field.verbose_name }}', default='', null=True, blank=True, max_length={{ field.max_length }})
    {% endfor %}
    class Meta:
        db_table = '{{ model.snake_name }}'
        verbose_name = '{{ model.verbose_name }}'
        verbose_name_plural = verbose_name

Admin Configuration Template

@admin.register({{ model.name }})
class {{ model.name }}Admin(admin.ModelAdmin):
    list_display = [{% for field in model.fields %}'{{ field.name }}', {% endfor %}]
    list_display_links = None

    def has_add_permission(self, request):
        return False

    def has_delete_permission(self, request, obj=None):
        return False

    def has_view_permission(self, request, obj=None):
        return False

Data Import Script

Here a few things are done:

• Use pandas to handle null values, filling them with empty strings
• Batch-update existing data
• Batch-insert new data

The update logic is a bit more troublesome, because databases generally have a limit on the maximum number of rows updated at a time, so I implemented batch processing, using update_data_once_max_lines to control how many records can be updated at once.

def import_{{ model.snake_name }}():
    file_path = path_proc(r'{{ excel_filepath }}')

    logger.info(f'读取文件: {file_path}')
    xlsx = pd.ExcelFile(file_path)
    df = pd.read_excel(xlsx, 0, header={{ excel_header }})
    df.fillna('', inplace=True)

    logger.info('开始处理数据')

    id_field_list = {{ model.name }}.objects.values_list('{{ model.id_field.name }}', flat=True)
    item_list = list({{ model.name }}.objects.all())

    def get_item(id_value):
        for i in item_list:
            if i.shen_qing_ren_zheng_jian_hao_ma == id_value:
                return i
        return None

    insert_data = []
    update_data_once_max_lines = 100
    update_data_sub_set_index = 0
    update_data = [[]]
    update_fields = set()

    for index, row in df.iterrows():
        if '{{ model.id_field.verbose_name }}' not in row:
            logger.error('id_field {} is not existed'.format('{{ model.id_field.verbose_name }}'))
            continue

        if row['{{ model.id_field.verbose_name }}'] in id_field_list:
            item = get_item(row['{{ model.id_field.verbose_name }}'])
            {% for field in model.fields -%}
            if '{{ field.verbose_name }}' in row:
                if item.{{ field.name }} != row['{{ field.verbose_name }}']:
                    item.{{ field.name }} = row['{{ field.verbose_name }}']
                    update_fields.add('{{ field.name }}')
            {% endfor %}
            if len(update_data[update_data_sub_set_index]) >= update_data_once_max_lines:
                update_data_sub_set_index += 1
                update_data.append([])
            update_data[update_data_sub_set_index].append(item)
        else:
            # {% for field in model.fields -%}{{ field.verbose_name }},{%- endfor %}
            model_obj = {{ model.name }}()
            {% for field in model.fields -%}
            if '{{ field.verbose_name }}' in row:
                model_obj.{{ field.name }} = row['{{ field.verbose_name }}']
            {% endfor %}
            insert_data.append(model_obj)

    logger.info('开始批量导入')
    {{ model.name }}.objects.bulk_create(insert_data)
    logger.info('导入完成')

    if len(update_data[update_data_sub_set_index]) > 0:
        logger.info('开始批量更新')
        for index, update_sub in enumerate(update_data):
            logger.info(f'正在更新 {index * update_data_once_max_lines}-{(index + 1) * update_data_once_max_lines} 条数据')
            {{ model.name }}.objects.bulk_update(update_sub, list(update_fields))
        logger.info('更新完成')

Main Code

The rest is all core code

Import Dependencies

First, import the libraries we'll use

import os
import re
from typing import List, Optional

from pypinyin import pinyin, lazy_pinyin, Style
from jinja2 import Environment, PackageLoader, FileSystemLoader

Or you can just grab them from my complete code later~

Class

As usual, I wrapped it in a class.

The constructor needs the Excel file path, as well as the header row index.

class ExcelToModel(object):
    def __init__(self, filepath, header_index=0):
        self.filepath = filepath
        self.header_index = header_index
        self.columns = []
        self.fields: List[Field] = []

        self.base_dir = os.path.dirname(os.path.abspath(__file__))
        self.template_path = os.path.join(self.base_dir, 'templates')
        self.jinja2_env = Environment(loader=FileSystemLoader(self.template_path))

        self.load_file()

There's a self.load_file() in here; I'll paste it later.

Converting Chinese Field Names to Pinyin

I used the pypinyin library; it feels pretty good.

After conversion, use a regular expression to strip symbols, keeping only English letters and numbers.

The code is as follows, also placed inside the ExcelToModel class.

@staticmethod
def to_pinyin(text: str) -> str:
    pattern = r'~`!#$%^&*()_+-=|\';"＂:/.,?><~·！@#￥%……&*（）——+-=“：’；、。，？》{《}】【\n\]\[ '
    text = re.sub(r"[%s]+" % pattern, "", text)
    return '_'.join(lazy_pinyin(text, style=Style.NORMAL))

Loading the File

Bring out the almighty pandas. Following the approach described earlier, extract the headers and convert them into fields, and traverse the data to determine the max length of each field. The offset I used here is 32, meaning 32 characters are added on top of the current maximum data length.

def load_file(self):
    import pandas as pd
    xlsx = pd.ExcelFile(self.filepath)
    df = pd.read_excel(xlsx, 0, header=self.header_index)
    df.fillna('', inplace=True)
    self.columns = list(df.columns)
    for col in self.columns:
        field = Field(self.to_pinyin(col), col)
        self.fields.append(field)
        for index, row in df.iterrows():
            item_len = len(str(row[col]))
            if item_len > field.max_length:
                field.max_length = item_len + 32

        print(field.verbose_name, field.name, field.max_length)

If you find generating tables this way too slow, you can drop the part that determines the maximum length, namely the following snippet

for index, row in df.iterrows():
    item_len = len(str(row[col]))
    if item_len > field.max_length:
        field.max_length = item_len + 32

Just specify the maximum length manually, or switch to an unrestricted TextField.

Generating files

First build a context, then use jinja2's render to generate the code.

To check whether data already exists during import, you need to specify id_field_verbose_name when generating the code, i.e. a column name in the Excel file such as “ID number” or “serial number”. Note that this is the header column name in Excel.

def find_field_by_verbose_name(self, verbose_name) -> Optional[Field]:
    for field in self.fields:
        if field.verbose_name == verbose_name:
            return field
    return None

def generate_file(self, model_name: str, verbose_name: str, id_field_verbose_name: str, output_filepath: str):
    template = self.jinja2_env.get_template('output.jinja2')
    context = {
        'model': Model(
            model_name, verbose_name,
            self.find_field_by_verbose_name(id_field_verbose_name),
            self.fields
        ),
        'excel_filepath': self.filepath,
        'excel_header': self.header_index,
    }
    with open(output_filepath, 'w+', encoding='utf-8') as f:
        render_result = template.render(context)
        f.write(render_result)

Usage

See the code.

tool = ExcelToModel('file.xlsx')
tool.generate_file('CitizenFertility', '房价与居民生育率', '证件号码', 'output/citizen_fertility.py')

The generated code all lives in a single file; please place it in the appropriate locations in your project as needed.

Full code

Published on GitHub

URL: https://github.com/Deali-Axy/excel_to_model

Wrap-up

So far it fits the requirements perfectly and saves a huge amount of work~

When actually running it, I have to complain about Python’s weak performance and high memory usage… It’ll do for now. Maybe I’ll optimize it later if I have time~