python 实现excel导入mongodb学习及踩坑

python导入excel到mongodb

直接上代码 后便有踩坑

import xlrd
import json
from pymongo import MongoClient


def excel2mongodb(name):
    # 连接数据库
    url = "mongodb://shop:aaaaa@00.111.22.333:27022/database_name?main?authSource=admin&authMechanism=SCRAM-SHA-1"
    db = MongoClient(url)['shop_selection']
    collection = 'tendency_dim_shop'

    # 读取Excel文件
    data = xlrd.open_workbook(name)
    table = data.sheets()[0]

    # 读取excel第一行数据作为存入mongodb的字段名
    row_stag = table.row_values(0)
    n_rows = table.nrows
    return_data = {}

    save_size = 0
    save_collection = []
    for i in range(1, n_rows):
        # 将字段名和excel数据存储为字典形式，并转换为json格式
        return_data[i] = json.dumps(dict(zip(row_stag, table.row_values(i))))
        # 通过编解码还原数据
        return_data[i] = json.loads(return_data[i])
        # 转换表头为所需字段名
        del return_data[i]['序号']
        return_data[i]['taskName'] = return_data[i].pop('任务名称')
        return_data[i]['taskDetail'] = return_data[i].pop('任务描述')
        return_data[i]['taskArea'] = return_data[i].pop('任务区域')
        return_data[i]['month'] = return_data[i].pop('月份')
        return_data[i]['flowMonth'] = return_data[i].pop('月均天级客流')

        save_collection.insert(save_size, return_data[i])
        if save_size >= 1000:
            db[collection].insert_many(save_collection)
            save_size = 0
            save_collection.clear()
        else:
            save_size += 1

    db[collection].insert_many(save_collection)



if __name__ == '__main__':
    excel2mongodb('/Users/hh/PycharmProjects/excel2mongodb/20220217_202101_202112.xls')

踩坑
excel中有month列，格式202201，但是导入后会发现他是数字，会变成202201.0令人头疼不已，所以导入需要进行下修改。或者最后整体更新。