python解析网页上的json数据落地到EXCEL

ops/2024/11/19 8:17:55/

安装必要的库

python">import requests
import pandas as pd
import os
import sys
import io
import urllib3
import json

测试数据

  • 网页上的数据结构如下
{"success": true,"code": "CIFM_0000","encode": null,"message": "ok","url": null,"total": 3,"items": [{"summaryDate": "20240611","summaryType": "naturalDay","workday": true,"newCustNum": 1,"haveCustNum": 1691627,"newAccountNum": 2,"haveAccountNum": 1692934,"totalShare": 4947657341.69,"netCash": -3523387.25,"yield": 0.01386},{"summaryDate": "20240612","summaryType": "naturalDay","workday": true,"newCustNum": 5,"haveCustNum": 1672766,"newAccountNum": 5,"haveAccountNum": 1674071,"totalShare": 4927109080.29,"netCash": -20735233.55,"yield": 0.01387},{"summaryDate": "20240613","summaryType": "naturalDay","workday": true,"newCustNum": 4,"haveCustNum": 1662839,"newAccountNum": 5,"haveAccountNum": 1664146,"totalShare": 4927405885.59,"netCash": 110659.8,"yield": 0.01389}],"data": null,"info": null
}

详细逻辑代码

python">import requests
import pandas as pd
import os
import sys
import io
import urllib3
import jsonurllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')url = "https://ip/ma/web/trade/dailySummary?startDate={pi_startdate}&endDate={pi_enddate}"
headers = {"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7","Accept-Language": "zh-CN,zh;q=0.9","User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36 Edg/119.0.0.0",
}def save_data(data, columns, excel_path, sheet_name):df = pd.DataFrame(data, columns=columns)if not os.path.exists(excel_path):df.to_excel(excel_path, sheet_name=sheet_name, index=False)else:with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:df.to_excel(writer, sheet_name=sheet_name, index=False)def json2list(response_text):# 把json数据转化为python用的类型json_dict = json.loads(response_text)src_total = json_dict["total"]print("src_total: {}".format(src_total))items = json_dict["items"]excel_columns = ['summaryDate','summaryType','workday','newCustNum','haveCustNum','newAccountNum','haveAccountNum','totalShare','netCash','yield']excel_data = []# 使用XPath定位元素并打印内容for item in items:excel_row_data = []for column_index in range(len(excel_columns)):data = str(item[excel_columns[column_index]])if excel_columns[column_index] == 'workday':data = str(0 if data == "False" else 1)excel_row_data.append(data)excel_data.append(excel_row_data)trg_total = len(excel_data)# 稽核print("trg_total: {}".format(trg_total))vn_biasval = trg_total - src_totalif vn_biasval != 0:print("This audit-rule is not passed,diff: {}".format(vn_biasval))exit(-1)else:print("This audit-rule is passed,diff: {}".format(vn_biasval))return excel_columns, excel_dataif __name__ == '__main__':try:excel_path = "C:/xxx/temp/ylb_dailySummary_{pi_startdate}_{pi_enddate}.xlsx"sheet_name = 'result_data'pi_startdate = 20240611pi_enddate = 20240613excel_path = excel_path.format(pi_startdate=pi_startdate, pi_enddate=pi_enddate)url = url.format(pi_startdate=pi_startdate, pi_enddate=pi_enddate)print("url:{}".format(url))print("excel_path:{}".format(excel_path))response_text = requests.get(url, headers=headers, timeout=(21, 300), verify=False).content.decode("utf8")excel_columns, excel_data = json2list(response_text)print("=================excel_columns=======================")print(excel_columns)print("=================excel_data==========================")for x in excel_data:print(x)print("=====================================================")# 文件存在,则删除if os.path.exists(excel_path):os.remove(excel_path)# 保存文件save_data(excel_data, excel_columns, excel_path, sheet_name)print("save_data is end.")except Exception as e:print("[ERROR]:" + str(e))exit(-1)

代码解析

  • 请求头
    构造请求头
python">urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')url = "https://ip/ma/web/trade/dailySummary?startDate={pi_startdate}&endDate={pi_enddate}"
headers = {"Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7","Accept-Language": "zh-CN,zh;q=0.9","User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36 Edg/119.0.0.0",
}
  • 数据保存到excel
    如果excel已经存在,那么则会将数据追加到excel
python">def save_data(data, columns, excel_path, sheet_name):df = pd.DataFrame(data, columns=columns)if not os.path.exists(excel_path):df.to_excel(excel_path, sheet_name=sheet_name, index=False)else:with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:df.to_excel(writer, sheet_name=sheet_name, index=False)
  • 解析json数据获取字段名称以及对应的数据list列表
python">def json2list(response_text):# 把json数据转化为python用的类型json_dict = json.loads(response_text)src_total = json_dict["total"]print("src_total: {}".format(src_total))items = json_dict["items"]excel_columns = ['summaryDate','summaryType','workday','newCustNum','haveCustNum','newAccountNum','haveAccountNum','totalShare','netCash','yield']excel_data = []# 使用XPath定位元素并打印内容for item in items:excel_row_data = []for column_index in range(len(excel_columns)):data = str(item[excel_columns[column_index]])if excel_columns[column_index] == 'workday':data = str(0 if data == "False" else 1)excel_row_data.append(data)excel_data.append(excel_row_data)trg_total = len(excel_data)# 稽核print("trg_total: {}".format(trg_total))vn_biasval = trg_total - src_totalif vn_biasval != 0:print("This audit-rule is not passed,diff: {}".format(vn_biasval))exit(-1)else:print("This audit-rule is passed,diff: {}".format(vn_biasval))return excel_columns, excel_data
  • 测试方法入口
python">if __name__ == '__main__':

测试结果

会生成ylb_dailySummary_20240611_20240613.xlsx文件
result_data


http://www.ppmy.cn/ops/134904.html

相关文章

人工智能:塑造未来的工作与生活

目录 人工智能技术的应用前景与影响 人工智能的历史与现状 人工智能的应用领域 人工智能的前景与挑战 个人视角:人工智能的应用前景与未来 人工智能在生活中的潜力 面对人工智能带来的挑战 我的观点与建议 结语 人工智能技术的应用前景与影响 随着人工智能…

如何对AWS进行节省

AWS 云服务器的费用确实可能会让人感到高昂,尤其是在资源使用不当或配置过多的情况下。不过,通过一些策略的合理应用和优化,完全可以降低云服务的使用成本,实现高效节省。以下是九河云总结的几种主要的优化方法,帮助你…

《CSDN:我的 365 天创作之旅》

不知不觉在CSDN创作已经365天了,回想起上一次256天创作纪念日,仿佛还在109天之前…… 目录 一、创作缘起 二、收获满满 (一)知识沉淀与成长 (二)粉丝关注与认可 三、创作日常 四、创作成就 &#xf…

如何通过电脑监控软件远程监控一台电脑的所有屏幕画面记录

7-1 本教程介绍一个简单的工具,可以安装在电脑中,按设置的时间间隔,自动对屏幕截图保存,并且可以在有网络的其它电脑上远程提取截图文件。 该软件用于自动记录电脑的屏幕画面内容和变化,如果你有这方面的使用场景&am…

FFmpeg 4.3 音视频-多路H265监控录放C++开发十三.2:avpacket中包含多个 NALU如何解析头部分析

前提: 注意的是:我们这里是从avframe转换成avpacket 后,从avpacket中查看NALU。 在实际开发中,我们有可能是从摄像头中拿到 RGB 或者 PCM,然后将pcm打包成avframe,然后将avframe转换成avpacket&#xff0…

Python+Requests+Pytest+Excel+Allure 接口自动化测试项目实战【框架之间的对比】

--------UnitTest框架和PyTest框架的简单认识对比与项目实战-------- 定义: Unittest是Python标准库中自带的单元测试框架,Unittest有时候也被称为PyUnit,就像JUnit是Java语言的标准单元测试框架一样,Unittest则是Python语言的标…

DevOps工程技术价值流:打造卓越项目协作的优化宝典

一、引言 解锁项目协作的无限潜力,覆盖全链路实现流畅高效。 在当今瞬息万变的商业环境中,项目协作的效率和效果直接关系到企业的竞争力和市场响应速度。DevOps工程技术价值流中的项目协作优化,不仅是技术层面的革新,更是团队协…

常用Adb 命令

# 连接设备 adb connect 192.168.10.125# 断开连接 adb disconnect 192.168.10.125# 查看已连接的设备 adb devices# 安装webview adb install -r "D:\webview\com.google.android.webview_103.0.5060.129-506012903_minAPI23(arm64-v8a,armeabi-v7a)(nodpi)_apkmirror.co…