工作小记--文件转存

需要把excel表格转为平台上可用的表，下发到工厂侧存入MongoDB表中

import json
import pandas as pd

# 定义列名映射表
column_map = {
    "工厂ID": "factoryID",
    "工厂名称": "customerName",
    "工厂简称": "simplifiedName",
    "区域": "region",
    "验收状态": "acceptanceStatus",
    "表名": "tableName",
    "检测项": "point",
    "数据精度": "dataPrecision",
    "groupId": "groupId",
    "组别": "groupCategory",
    "groupName": "groupName",
    "数据使用说明": "pointName",
    "有效值约束": "valueRange",
    "更新频次(h)": "updateFrequencyHours",
    "关联品种": "associatedProducts",
    "单位": "unit",
    "关联设备": "equipment",
    "延迟天数": "delayDays",
    "项次类别": "itemCategory",
    "字段类型": "fieldType",
    "影响级别": "impactLevel",
    "是否启用": "isEnabled",
    "数据格式": "dataFormat",
    "国标相关": "nationalStandard",
    "影响模型": "impactModel",
    "生产阶段": "productionStage",
    "公式": "formula",
    "权限": "permissions",
    "影响描述": "impactDescription",
    "窑设备综合样时段": "kilnEquipmentSamplingPeriod",
    "磨设备综合杨时段": "millEquipmentSamplingPeriod",
    "数据质量规则": "dataQualityRules",
    "利益相关方": "stakeholders",
    "标签": "tags",
    "创建时间": "creationTime",
    "示例": "example"
}


def write_data_to_json(excel_path, output_json_path):
    df = pd.read_excel(excel_path)
    df.rename(columns=column_map, inplace=True)

    # 转换为字典列表并替换 NaN
    data = []
    for record in df.to_dict(orient='records'):
        processed_record = {}
        for key, value in record.items():
            if pd.isna(value):
                if key == "factoryID":
                    processed_record[key] = "factoryId"  
                elif key == "customerName":
                    processed_record[key] = "factoryName"  
                else:
                    processed_record[key] = ''
            else:
                processed_record[key] = value
        data.append(processed_record)


    json_str = json.dumps(data, ensure_ascii=False, indent=4)

    # 替换带引号的 "factoryId" 和 "factoryName" 为不带引号的变量名
    json_str = json_str.replace('"factoryId"', 'factoryId').replace('"factoryName"', 'factoryName')
    with open(output_json_path, 'w', encoding='utf-8') as file:
        file.write(json_str)

    print(f"数据已成功写入 {output_json_path}")


excel_path = "原始文件/质检数据元数据_v1.0.20250704-1.xlsx"
output_json_path = 'json文件/元数据_v1.0.20250704.json'
write_data_to_json(excel_path, output_json_path)

再进行下一步

import json
import socket
import pandas as pd
import pytz
from pandas.core.interchange.dataframe_protocol import DataFrame
from pymongo import MongoClient
from typing import Dict, List, Union


def initConn(test):
    global producer, pro_mongo_client, pre_mongo_client, cement_db

    hostname = socket.gethostname()
    pre_mongo_ip = 'prenode1'
    pro_mongo_ip = 'pronode1'
    if hostname.find('stage') != -1:
        pro_mongo_ip = 'prdnode1'
        pre_mongo_ip = 'stagenode1'

    if test:
        pro_mongo_client = MongoClient(host='',
                                       port=,
                                       username='',
                                       password='',
                                       authSource='admin',
                                       authMechanism='SCRAM-SHA-256',
                                       tz_aware=True,  # 设置为True
                                       tzinfo=pytz.timezone('Asia/Shanghai'),
                                       connectTimeoutMS=50000,  # 连接建立超时时间，单位毫秒，例如5秒
                                       socketTimeoutMS=50000
                                       )
        pre_mongo_client = MongoClient(host='',
                                       port=,
                                       username='',
                                       password='',
                                       authSource='admin',
                                       authMechanism='SCRAM-SHA-256',
                                       tz_aware=True,  # 设置为True
                                       tzinfo=pytz.timezone('Asia/Shanghai'),
                                       connectTimeoutMS=50000,  # 连接建立超时时间，单位毫秒，例如5秒
                                       socketTimeoutMS=50000
                                       )
    else:
        pro_mongo_client = MongoClient(host=pro_mongo_ip,
                                       port=,
                                       username='',
                                       password='',
                                       authSource='admin',
                                       authMechanism='SCRAM-SHA-256',
                                       tz_aware=True,  # 设置为True
                                       tzinfo=pytz.timezone('Asia/Shanghai'),
                                       connectTimeoutMS=300000,  # 连接建立超时时间，单位毫秒，例如5秒
                                       socketTimeoutMS=300000
                                       )
        pre_mongo_client = MongoClient(host=pre_mongo_ip,
                                       port=,
                                       username='',
                                       password='',
                                       authSource='admin',
                                       authMechanism='SCRAM-SHA-256',
                                       tz_aware=True,  # 设置为True
                                       tzinfo=pytz.timezone('Asia/Shanghai'),
                                       connectTimeoutMS=300000,  # 连接建立超时时间，单位毫秒，例如5秒
                                       socketTimeoutMS=300000
                                       )
    return pro_mongo_client


def save_to_mongodb(json_data: Union[List[Dict], str],
                    collection_name: str = 'QUALITY_METADATA_1',
                    db_name: str = 'cement_ingredient',
                    test: bool = True,
                    clear_collection: bool = True) -> None:  # 新增clear_collection参数
    client = None
    try:
        client = initConn(test)
        db = client[db_name]
        collection = db[collection_name]

        if isinstance(json_data, str):
            json_data = json.loads(json_data)

        # 先清空集合
        if clear_collection:
            result_delete = collection.delete_many({})  # 删除所有文档
            print(f"已清空集合，删除文档数：{result_delete.deleted_count}")

        result = collection.insert_many(json_data)
        print(f"成功写入：{len(result.inserted_ids)}")

    except Exception as e:
        print(f"存入数据库出错： {str(e)}")
        raise
    finally:
        if client:
            client.close()


# 插入 MSG_FILTER_CONFIG_1 集合，若不需要这个集合可注释掉
filter_config_data = [{
    "line": '',
    "product": '',
    "groupId": '',
    "groupName": '',
    "impactModel": '',
    "productionStage": '',
    "impactLevel": '',
    "remark": '需要过滤不参与告警消息构建的数据'
}]


# if __name__ == '__main__':
def execute(dataframes):
    global factoryName, factoryId
    factoryId = None
    factoryName = None
    for index, row in dataframes['新增列'].iterrows():
        factoryId = row['factoryId']
        factoryName = row['factoryName']

    # "factoryID": '' -> 替换成 -> "factoryID": factoryId,
    # "customerName": '' -> 替换成 -> "customerName": factoryName
    # excel转成的JSON，若文件有变化可直接替换
    example_json = [
    {
        "factoryID": factoryId,
        "customerName": factoryName,
        "simplifiedName": "",
        "region": "",
        "acceptanceStatus": "",
        "tableName": "ANALYSIS_1",
        "point": "groupId",
        "tags": "",
        "delayDays": 3.0,
        "dataPrecision": "",
        "groupId": "CI_10001105",
        "groupCategory": "分析组",
        "groupName": "熟料",
        "pointName": "groupId",
        "valueRange": "",
        "updateFrequencyHours": 24,
        "associatedProducts": "",
        "nationalStandard": "",
        "impactModel": "",
        "impactLevel": "L0",
        "unit": "",
        "equipment": "回转窑",
        "itemCategory": "系统字段",
        "fieldType": "字符串",
        "isEnabled": "正常检测",
        "dataFormat": "^CI_\\d+$",
        "productionStage": "熟料烧成",
        "formula": "",
        "permissions": "",
        "impactDescription": "",
        "kilnEquipmentSamplingPeriod": "昨天:08:00~今天:08:00",
        "millEquipmentSamplingPeriod": "昨天:08:00~今天:08:00",
        "dataQualityRules": "",
        "stakeholders": "算法,项目经理",
        "creationTime": 20250409104755.0,
        "example": "CI_10001105"
    },
    {
        "factoryID": factoryId,
        "customerName": factoryName,
        "simplifiedName": "",
        "region": "",
        "acceptanceStatus": "",
        "tableName": "ANALYSIS_1",
        "point": "analysisDate",
        "tags": "",
        "delayDays": 3.0,
        "dataPrecision": "",
        "groupId": "CI_10001105",
        "groupCategory": "分析组",
        "groupName": "熟料",
        "pointName": "分析日期",
        "valueRange": "",
        "updateFrequencyHours": 24,
        "associatedProducts": "",
        "nationalStandard": "",
        "impactModel": "",
        "impactLevel": "L0",
        "unit": "",
        "equipment": "回转窑",
        "itemCategory": "系统字段",
        "fieldType": "时间",
        "isEnabled": "正常检测",
        "dataFormat": "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$",
        "productionStage": "熟料烧成",
        "formula": "",
        "permissions": "",
        "impactDescription": "",
        "kilnEquipmentSamplingPeriod": "昨天:08:00~今天:08:00",
        "millEquipmentSamplingPeriod": "昨天:08:00~今天:08:00",
        "dataQualityRules": "",
        "stakeholders": "算法,项目经理",
        "creationTime": 20250409104755.0,
        "example": "2023-12-29 14:11:00"
    }
]

    # QUALITY_METADATA_1
    save_to_mongodb(
        json_data=example_json,
        collection_name="QUALITY_METADATA_1",
        db_name='cement_ingredient',
        test=False,  # 生产环境，True预发环境
        clear_collection=True
    )

    # MSG_FILTER_CONFIG_1，若不需要这个集合可注释掉
    save_to_mongodb(
        json_data=filter_config_data,
        collection_name="MSG_FILTER_CONFIG_1",
        db_name='cement_ingredient',
        test=False,  # 生产环境，True预发环境
        clear_collection=True
    )

    df = pd.DataFrame([{"a": "a"}])
    return df

# execute("aaaa")