Python實現(xiàn)根據(jù)Excel生成Model和數(shù)據(jù)導入腳本
前言
最近遇到一個需求，有幾十個Excel，每個的字段都不一樣，然后都差不多是第一行是表頭，后面幾千上萬的數(shù)據(jù)，需要把這些Excel中的數(shù)據(jù)全都加入某個已經(jīng)上線的Django項目
這就需要每個Excel建個表，然后一個個導入了
這樣的效率太低，不能忍
所以我造了個自動生成 Model 和導入腳本的輪子
思路
首先拿出 pandas，它的 DataFrame 用來處理數(shù)據(jù)很方便
pandas 加載 Excel 之后，提取表頭，我們要通過表頭來生成數(shù)據(jù)表的字段。有些 Excel 的表頭是中文的，需要先做個轉(zhuǎn)換。
一開始我是想用翻譯API，全都翻譯成英文，不過發(fā)現(xiàn)免費的很慢有限額，微軟、DeepL都要申請，很麻煩。索性用個拼音轉(zhuǎn)換庫，全都轉(zhuǎn)換成拼音得了~
然后字段的長度也要確定，或者全部用不限制長度的 TextField
權衡一下，我還是做一下字段長度判定的邏輯，遍歷整個表，找出各個字段最長的數(shù)據(jù)，然后再加一個偏移量，作為最大長度。
接著生成 Model 類，這里我用 jinja2 模板語言，先把大概的模板寫好，然后根據(jù)提取出來的字段名啥的生成。
最后生成 admin 配置和導入腳本，同理，也是用 jinja2 模板。
實現(xiàn)
簡單介紹下思路，現(xiàn)在開始上代碼。
就幾行而已，Python很省代碼~
模型
首先定義倆模型
字段模型
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模型
為了符合Python關于變量的命名規(guī)范，snake_name 屬性是用正則表達式實現(xiàn)駝峰命名轉(zhuǎn)蛇形命名
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__()
代碼模板
使用 jinja2 實現(xiàn)。
本身 jinja2 是 Flask、Django 之類的框架用來渲染網(wǎng)頁的。
不過單獨使用的效果也不錯，我的 DjangoStarter 框架也是用這個 jinja2 來自動生成 CRUD 代碼~
Model模板
# -*- 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配置模板
@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
數(shù)據(jù)導入腳本
這里做了幾件事：
- 使用 pandas 處理空值，填充空字符串
- 已有數(shù)據(jù)進行批量更新
- 新數(shù)據(jù)批量插入
更新邏輯麻煩一點，因為數(shù)據(jù)庫一般都有每次最大更新數(shù)量的限制，所以我做了分批處理，通過 update_data_once_max_lines 控制每次最多同時更新多少條數(shù)據(jù)。
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('開始處理數(shù)據(jù)')
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} 條數(shù)據(jù)')
{{ model.name }}.objects.bulk_update(update_sub, list(update_fields))
logger.info('更新完成')
主體代碼
剩下的全是核心代碼了
引用依賴
先把用到的庫導入
import os import re from typing import List, Optional from pypinyin import pinyin, lazy_pinyin, Style from jinja2 import Environment, PackageLoader, FileSystemLoader
或者后面直接去我的完整代碼里面拿也行~
類
老規(guī)矩，我封裝了一個類。
構造方法需要指定 Excel 文件地址，還有表頭的行索引。
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()
這里面有個 self.load_file() 后面再貼。
字段名中文轉(zhuǎn)拼音
用了 pypinyin 這個庫，感覺還不錯。
轉(zhuǎn)換后用正則表達式，去除符號，只保留英文和數(shù)字。
代碼如下，也是放在 ExcelToModel 類里邊。
@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))
加載文件
拿出萬能的 pandas，按照前面說的思路，提取表頭轉(zhuǎn)換成字段，并且遍歷數(shù)據(jù)確定每個字段的最大長度，我這里偏移值是32，即在當前數(shù)據(jù)最大長度基礎上加上32個字符。
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)
如果覺得這樣生成表太慢，可以把確定最大長度的這塊代碼去掉，就下面這塊代碼
for index, row in df.iterrows():
item_len = len(str(row[col]))
if item_len > field.max_length:
field.max_length = item_len + 32
手動指定最大長度或者換成不限制長度的 TextField 就行。
生成文件
先構造個 context 然后直接用 jinja2 的 render 功能生成代碼。
為了在導入時判斷數(shù)據(jù)存不存在，生成代碼時要指定 id_field_verbose_name，即Excel文件中類似“證件號碼”、“編號”之類的列名，注意是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)
使用
看代碼。
tool = ExcelToModel('file.xlsx')
tool.generate_file('CitizenFertility', '房價與居民生育率', '證件號碼', 'output/citizen_fertility.py')
生成出來的代碼都在一個文件里，請根據(jù)實際情況放到項目的各個位置。
完整代碼
發(fā)布到Github了
地址: https://github.com/Deali-Axy/excel_to_model
以上就是Python實現(xiàn)根據(jù)Excel生成Model和數(shù)據(jù)導入腳本的詳細內(nèi)容，更多關于Python生成Model和數(shù)據(jù)導入腳本的資料請關注腳本之家其它相關文章！
相關文章
Python使用QQ郵箱發(fā)送郵件報錯smtplib.SMTPAuthenticationError
這篇文章主要介紹了Python使用QQ郵箱發(fā)送郵件報錯smtplib.SMTPAuthenticationError，簡單介紹了python 發(fā)送郵件的步驟,需要的朋友可以參考下2019-12-12
虛擬環(huán)境是一個獨立的Python環(huán)境,它與系統(tǒng)的全局Python環(huán)境隔離,這篇文章主要介紹了Python中venv虛擬環(huán)境的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-04-04
python機器學習算法與數(shù)據(jù)降維分析詳解
這篇文章主要為大家介紹了python機器學習算法與數(shù)據(jù)降維的分析詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步2021-11-11
python中time tzset()函數(shù)實例用法
在本篇文章里小編給大家整理的是一篇關于python中time tzset()函數(shù)實例用法內(nèi)容，有興趣的朋友們可以學習下。2021-02-02
這篇文章主要介紹了python多環(huán)境切換及pyenv使用過程詳解,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2019-09-09
這篇文章主要介紹了淺談flask源碼之請求過程,小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-07-07
這篇文章主要介紹了如何使用ddddocr庫來識別滑塊驗證碼,并提供了一個示例代碼和識別結果,同時提醒注意ddddocr庫的大小限制,可能會影響某些無服務器函數(shù)的部署,需要的朋友可以參考下2024-11-11
Python+opencv 實現(xiàn)圖片文字的分割的方法示例
這篇文章主要介紹了Python+opencv 實現(xiàn)圖片文字的分割的方法示例，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-07-07
python數(shù)據(jù)預處理之數(shù)據(jù)標準化的幾種處理方式
這篇文章主要介紹了python數(shù)據(jù)預處理之數(shù)據(jù)標準化的幾種處理方式，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-07-07
本文將詳細介紹 RSA 數(shù)字簽名的原理、實現(xiàn)步驟,以及如何通過 Python 的 rsa 庫完成公鑰私鑰生成、數(shù)字簽名和認證,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2025-03-03