python读取excel自动请求接口并记录日期到mongodb

#!/usr/bin/python
# encoding: utf-8

import pymongo
import json
from bson import json_util

import numpy as np
import pandas as pd
import xlrd ,xlwt
import xlsxwriter

import httplib
import requests
import json
import bson

import time

conn = pymongo.MongoClient('localhost')
# 库名
db = conn.bh
# 表名
users = db.manualpayment

bhData = list()


convert1 = {
  "outTradeNo": str,
  "paymentAccountNo": str,
  "receivedTransactionNo": str,
  "paymentTransactionNo": str,
  # "accountName": str,
  "paymentType": str,
  "paymentAmount": str,
  "actionType": str,
  "mmanualDetails": str,
  "contractNo": str,
  "payDate": str,
  "seqId": str,
  "amount": str,
  "payCorp": str,
  "payInte": str,
  "inteReduction": str,
  "payFine": str,
  "fineReduction": str,
  "payOperationFee": str,
  "operationFeeReduction": str,
  "payServiceFee": str,
  "serviceFeeReduction": str,
  "prepaymentPenalty": str,
  "penaltyReduction": str,
  # "isPayOff": str,
  # "settledEarly": str,
  "occurDate": str
}

# 写入mongodb
def writedb(bh):
    print bh
    x = users.insert_many(bh)
    # print dir(x)
    # print x.inserted_id
    pass

# 读取excel数据
data = None
def readXls():
    io = r'‪D:\doc\python\bh20200422.xlsx'
    converters = {'paymentAccountNo':str}
    data = pd.read_excel(io.replace('\xe2\x80\xaa',''), sheet_name='Sheet4',skiprows=1,converters = convert1,keep_default_na=False)
    return data

# 发送数据到渤海
def sendbh(payload):
    # r = requests.get('http://172.16.200.120:30606/sysBank/findAll')
    # print r  json.dumps(users.find()[0])
    # payload = dict(key1='value1', key2='value2')
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.99 Safari/537.36",
        "app":"ALMS",
        "Content-Type":"application/json"
    }
    # 测试地址
    # r = requests.post('http://172.16.200.120:30606/sysBank/findAll', data=payload,headers=headers)
    # 渤海地址
    r = requests.post('[你的接口地址]', data=json.dumps(payload), headers=headers)
    return r.text

class JSONEncoder(json.JSONEncoder):
    def default(self, o):
        if isinstance(o,bson.objectid.ObjectId):
            return str(o)
        return json.JSONEncoder.default(self,o)

def getFmtTime(dat,fmt):
    # "%Y-%m-%d %H:%M:%S"
    time_struct = time.strptime(dat, "%Y-%m-%d %H:%M:%S")
    return time.strftime(fmt, time_struct)

def intiBh(bhFile):
    bh = {
        "outTradeNo": None,
        "busiDate": None,
        "receivedAccountNo": None,
        "receivedTransactionNo": None,
        "paymentAccountNo": None,
        "paymentTransactionNo": None,
        "accountName": None,
        "paymentType": None,
        "paymentAmount": None,
        "actionType": None,
        "mmanualDetails": [
            {
                "contractNo": None,
                "payDate": None,
                "seqId": None,
                "amount": None,
                "payCorp": None,
                "payInte": None,
                "inteReduction": None,
                "payFine": None,
                "fineReduction": None,
                "payOperationFee": None,
                "operationFeeReduction": None,
                "payServiceFee": None,
                "serviceFeeReduction": None,
                "prepaymentPenalty": None,
                "penaltyReduction": None,
                "isPayOff": None,
                "settledEarly": None,
                "occurDate": None
            }
        ],
        "ret": None
    }
    bh['outTradeNo'] = str(bhFile['outTradeNo'])
    bh['outTradeNo'] = str(bhFile['outTradeNo'])
    bh['busiDate'] = getFmtTime(str(bhFile['busiDate']),'%Y/%m/%d')
    bh['receivedAccountNo'] = str(bhFile['receivedAccountNo'])
    bh['receivedTransactionNo'] = str(bhFile['receivedTransactionNo'])
    bh['paymentAccountNo'] = str(bhFile['paymentAccountNo'])
    bh['paymentTransactionNo'] = str(bhFile['paymentTransactionNo'])
    bh['accountName'] = bhFile['accountName']
    bh['paymentType'] = str(bhFile['paymentType'])
    bh['paymentAmount'] = str(bhFile['paymentAmount'])
    bh['actionType'] = str(bhFile['actionType'])
    bh['mmanualDetails'][0]['contractNo'] = str(bhFile['contractNo'])
    bh['mmanualDetails'][0]['payDate'] = getFmtTime(str(bhFile['payDate']),'%Y-%m-%d')
    bh['mmanualDetails'][0]['seqId'] = str(bhFile['seqId'])
    bh['mmanualDetails'][0]['amount'] = str(bhFile['amount'])
    bh['mmanualDetails'][0]['payCorp'] = str(bhFile['payCorp'])
    bh['mmanualDetails'][0]['payInte'] = str(bhFile['payInte'])
    bh['mmanualDetails'][0]['inteReduction'] = str(bhFile['inteReduction'])
    bh['mmanualDetails'][0]['payFine'] = str(bhFile['payFine'])
    bh['mmanualDetails'][0]['fineReduction'] = str(bhFile['fineReduction'])
    bh['mmanualDetails'][0]['payOperationFee'] = str(bhFile['payOperationFee'])
    bh['mmanualDetails'][0]['operationFeeReduction'] = str(bhFile['operationFeeReduction'])
    bh['mmanualDetails'][0]['payServiceFee'] = str(bhFile['payServiceFee'])
    bh['mmanualDetails'][0]['serviceFeeReduction'] = str(bhFile['serviceFeeReduction'])
    bh['mmanualDetails'][0]['prepaymentPenalty'] = str(bhFile['prepaymentPenalty'])
    bh['mmanualDetails'][0]['penaltyReduction'] = str(bhFile['penaltyReduction'])
    bh['mmanualDetails'][0]['isPayOff'] = str(bhFile['isPayOff'])
    bh['mmanualDetails'][0]['settledEarly'] = str(bhFile['settledEarly'])
    bh['mmanualDetails'][0]['occurDate'] = getFmtTime(str(bhFile['occurDate']),'%Y-%m-%d')
    bh['ret'] = sendbh(bh)
    bhData.append(bh)



if __name__ == '__main__':
    # step1 读取excel
    data = readXls()
    # data = data.fillna(value='')
    # step2 处理excel数据到mongodb res.encode("utf-8")
    for i in data.index:
        intiBh(data.loc[i].to_dict())
        # writedb(data.loc[i].to_dict())
    # step3 读取mongodb数据发送数据到请求接口，记录执行结果
    writedb(bhData)
    # data2.to_dict()
    # json.dumps(data2.to_dict(), encoding="UTF-8", ensure_ascii=False)
    # print res