如何基于模板文本与JSON数据批量生成Python函数文件

需求概述 #

基于指定文本模板，为数据源中的每张表生成对应的Python文件，文件内包含适配该表的函数，函数中的SQL查询会自动筛选出表中类型为NVARCHAR2的字段。

模板文件（mytemplate.txt） #

#text as template to python file
def D_{var_1}_O(pref, in_params):
    logger.info(f"{pref} Transform params: {in_params}")
    table_name = '{var_1}'
    load = table_info['TIPO_CARGA']
    query = 'select {list just NVARCHAR2 column with coma separator} from {var_1}'
    logger.info(f"Query: {query}")
    return query

数据源（schema.json） #

{
    "schema": {
        "CUENTA_C": {
            "TIPO_CARGA": "daily",
            "FIELDS": {
                "ID": "NVARCHAR2",
                "DATE": "DATE",
                "ISOPEN": "NUMBER",
                "NAME": "NVARCHAR2",
                "DEA": "NVARCHAR2"
            }
        },
        "BUSINESS__C": {
            "TIPO_CARGA": "daily",
            "FIELDS": {
                "ID": "NVARCHAR2",
                "ISOPEN": "NUMBER",
                "NAME": "NVARCHAR2",
                "CURRENCY": "NVARCHAR2"
            }
        },
        "LLAMADAS__C": {
            "TIPO_CARGA": "daily",
            "FIELDS": {
                "ID": "NVARCHAR2",
                "OWNERID": "NVARCHAR2",
                "ISOPEN": "NUMBER",
                "ZIP": "NVARCHAR2",
                "COUNTRY": "NVARCHAR2"
            }
        }
    }
}

期望生成的文件示例（cuenta_c.py） #

def D_CUENTA_C_O(pref, in_params):
    logger.info(f"{pref} Transform params: {in_params}")
    table_name = 'CUENTA_C'
    load = table_info['TIPO_CARGA']
    query = 'select ID, NAME, DEA from CUENTA_C'
    logger.info(f"Query: {query}")
    return query

实现脚本 #

以下Python脚本可完成批量生成任务：

import json

# 读取模板文件
with open('mytemplate.txt', 'r', encoding='utf-8') as f:
    template = f.read()

# 读取数据源JSON
with open('schema.json', 'r', encoding='utf-8') as f:
    data = json.load(f)

# 遍历每个表生成对应文件
for table_name, table_details in data['schema'].items():
    # 筛选出NVARCHAR2类型的字段并拼接成字符串
    nvarchar_fields = [field for field, dtype in table_details['FIELDS'].items() if dtype == 'NVARCHAR2']
    field_list = ', '.join(nvarchar_fields)
    
    # 替换模板中的变量
    content = template.replace('{var_1}', table_name)
    content = content.replace('{list just NVARCHAR2 column with coma separator}', field_list)
    
    # 生成小写文件名并写入内容
    filename = f"{table_name.lower()}.py"
    with open(filename, 'w', encoding='utf-8') as f:
        f.write(content)

print("所有文件生成完成")

使用说明 #

1. 将模板保存为mytemplate.txt，数据源保存为schema.json，与脚本放在同一目录下
2. 运行脚本，会自动生成对应表名的Python文件（文件名统一为小写）

内容的提问来源于stack exchange，提问作者Julio