Python写脚本将excel数据导入到网站指定接口

今天药忘吃喽~ 2023-07-24 11:54 16阅读 0赞

#!/usr/bin/python3
    # code:utf-8
    import json
    import time
    import requests
    import xlrd
    xlrd.Book.encoding = "utf-8"
    class ImportTool():
        def __init__(self):
            self.session = requests.Session()
            pass
            self.header = { 
                "Pragma": "no-cache",
                "Cache-Control": "no-cache",
                "Accept": "application/json, text/javascript, */*; q=0.01",
                "X-Requested-With": "XMLHttpRequest",
                "User-Agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36",
                "Accept-Encoding": "gzip, deflate",
                "Accept-Language": "zh-CN,zh;q=0.9",
                "Cookie": "PHPSESSID=d80lu94qbmoaeoh46bu7pceef1xx2xxxxxxcaf3",
            }
        def request_add(self, city_name, parent_id):
    
            url = "http://kyctagger.xxxxxxxxx.com.cn/newstagger/tagger/ajax/"
            # 'parentid'是父ID， data参数缺一不可
            data = { 'method': 'saveConcept', 'type': 1, 'id': '', 'parentid': parent_id, 'name': city_name,
                    'sort': '', 'etype': '', 'etag': '', 'classid': '', 'rule': [], 'link_parent': 0, 'importance': 0}
            res = self.session.post(url, data=data, headers=self.header)
            res_data = res.json()
            print(res_data)
            if "ok" not in res_data:
                print("登录状态可能失效")
                input("请刷新页面登录后输入任意字符继续:")
                return self.request_add(city_name, parent_id)
            if res_data["ok"] == 1:
                print("添加成功")
                return True
            else:
                print(res_data['msg'])
                return False
        def import_city_excel(self, file_name, partent_id, import_row):
            file_path = r'C:\Users\viruser.v-desktop\Desktop\WorldData\%s' % file_name   # 要导入的excel文件位置
            xlrd.Book.encoding = "utf-8"     
            data = xlrd.open_workbook(file_path)   # 打开这个文件
            table = data.sheet_by_name('Sheet1')      # 用的是excel文件中的Sheet1这张表
            import_row = table.nrows
            i = 0
            for i in range(0, import_row):
                time.sleep(0.1)
                city_name = table.row_values(i)[0]
                print("正在导入：" + city_name)
                status = self.request_add(city_name, partent_id)
                if status:
                    print(status)
                print("这是第%d次导入" % i)
    
    if __name__ == '__main__':
        parent_id_dict = { 
            'city': "597ae9b5f6e6e61b3dff71a2xxxxxx",     # 父ID
            'area': "5e83fcffd4c89d252c463835xxxxx",
            'country': "597998caf6e6e6a2197e7a8dxxxxx",
        }
        file_name_dict = { 
            'city': "国外城市.xlsx",
            'area': "国外州省.xlsx",
            'country': "重要国家.xlsx",
        }
        import_file = input("请输入导入的区域：")
        if import_file not in file_name_dict:
            print("文件不存在")
            exit()
        file_name = file_name_dict[import_file]
        parent_id = parent_id_dict[import_file]
        print(parent_id)
        print("本次执行 导入 %s 文件数据,parent_id :%s" % (file_name, parent_id))
        tool = ImportTool()
        tool.import_city_excel(file_name, parent_id, 5)