689 lines
31 KiB
Plaintext
689 lines
31 KiB
Plaintext
{
|
||
"cells": [
|
||
{
|
||
"cell_type": "markdown",
|
||
"id": "a738ca16021a9e3b",
|
||
"metadata": {},
|
||
"source": [
|
||
"# 车辆信息导出"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 1,
|
||
"id": "initial_id",
|
||
"metadata": {
|
||
"ExecuteTime": {
|
||
"end_time": "2026-04-23T08:20:44.296733100Z",
|
||
"start_time": "2026-04-23T08:16:24.431928500Z"
|
||
},
|
||
"collapsed": true
|
||
},
|
||
"outputs": [
|
||
{
|
||
"name": "stderr",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"100%|██████████| 986/986 [09:09<00:00, 1.79it/s]\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"import time\n",
|
||
"import pandas as pd\n",
|
||
"import requests\n",
|
||
"from tqdm import tqdm\n",
|
||
"\n",
|
||
"import requests\n",
|
||
"import requests\n",
|
||
"\n",
|
||
"cookies = {\n",
|
||
" 'Hm_lvt_684c22b31d0037eca5a691cde16370ad': '1773130169,1774314982',\n",
|
||
" 'e_token': '078d3c82d4cf4d68a86d147b7797cf98',\n",
|
||
" 'weixin_token': 'Y',\n",
|
||
" 'e_c': 'MTMxMDU1MjE0NzM1Mjc5ODg=',\n",
|
||
" 'acw_tc': '76b20fb517801201588657844e21ecfb30d9ab85051f45e42c388e7e4400de',\n",
|
||
"}\n",
|
||
"\n",
|
||
"headers = {\n",
|
||
" 'Accept': 'application/json, text/plain, */*',\n",
|
||
" 'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',\n",
|
||
" 'Connection': 'keep-alive',\n",
|
||
" 'Content-Type': 'application/json;charset=UTF-8',\n",
|
||
" 'Irisclient': 'PC-web#tedspc',\n",
|
||
" 'Origin': 'https://teds.tyreplus.com.cn',\n",
|
||
" 'Referer': 'https://teds.tyreplus.com.cn/tedspc/index.html',\n",
|
||
" 'Sec-Fetch-Dest': 'empty',\n",
|
||
" 'Sec-Fetch-Mode': 'cors',\n",
|
||
" 'Sec-Fetch-Site': 'same-origin',\n",
|
||
" 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0',\n",
|
||
" 'sec-ch-ua': '\"Chromium\";v=\"148\", \"Microsoft Edge\";v=\"148\", \"Not/A)Brand\";v=\"99\"',\n",
|
||
" 'sec-ch-ua-mobile': '?0',\n",
|
||
" 'sec-ch-ua-platform': '\"Windows\"',\n",
|
||
" # 'Cookie': 'Hm_lvt_684c22b31d0037eca5a691cde16370ad=1773130169,1774314982; e_token=078d3c82d4cf4d68a86d147b7797cf98; weixin_token=Y; e_c=MTMxMDU1MjE0NzM1Mjc5ODg=; acw_tc=76b20fb517801201588657844e21ecfb30d9ab85051f45e42c388e7e4400de',\n",
|
||
"}\n",
|
||
"\n",
|
||
"\n",
|
||
"\n",
|
||
"\n",
|
||
"all_data = []\n",
|
||
"for i in tqdm(range(1, 987)):\n",
|
||
" json_data = {\n",
|
||
" 'pageStart': i,\n",
|
||
" 'pageNums': 10,\n",
|
||
" 'pageNum': 1,\n",
|
||
" 'pageSize': 10,\n",
|
||
" }\n",
|
||
"\n",
|
||
" response = requests.post(\n",
|
||
" 'https://teds.tyreplus.com.cn/api/v1/custom/vehicle/queryVehicleInfosByStore',\n",
|
||
" cookies=cookies,\n",
|
||
" headers=headers,\n",
|
||
" json=json_data,\n",
|
||
" )\n",
|
||
" data_list = response.json()['obj']['list']\n",
|
||
" all_data += data_list\n",
|
||
"\n",
|
||
"df = pd.DataFrame(all_data)\n",
|
||
"df.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\18027080999驰加车辆信息1.xlsx\", index=False)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"id": "e166fa181fddc651",
|
||
"metadata": {},
|
||
"source": [
|
||
"# 历史维修记录列表（结算单）"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 4,
|
||
"id": "51f8f4b21505280a",
|
||
"metadata": {
|
||
"ExecuteTime": {
|
||
"end_time": "2026-04-23T08:36:56.349271400Z",
|
||
"start_time": "2026-04-23T08:20:44.300734900Z"
|
||
}
|
||
},
|
||
"outputs": [
|
||
{
|
||
"name": "stdout",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"正在获取第 1 页数据以计算总记录数...\n",
|
||
"✅ 获取成功。总记录数: 13861\n",
|
||
"📄 预计总页数: 1387 (每页 10 条)\n"
|
||
]
|
||
},
|
||
{
|
||
"name": "stderr",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"正在爬取结算单: 100%|██████████| 1387/1387 [21:56<00:00, 1.05it/s]\n"
|
||
]
|
||
},
|
||
{
|
||
"name": "stdout",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"\n",
|
||
"🎉 数据已成功保存至: D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\18027080999结算单.xlsx\n",
|
||
"📊 共保存 13861 条记录 (期望: 13861)\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"import time\n",
|
||
"import requests\n",
|
||
"from tqdm import tqdm\n",
|
||
"import pandas as pd\n",
|
||
"\n",
|
||
"# ================= 配置区域 =================\n",
|
||
"cookies = {\n",
|
||
" 'Hm_lvt_684c22b31d0037eca5a691cde16370ad': '1773130169,1774314982',\n",
|
||
" 'e_token': '078d3c82d4cf4d68a86d147b7797cf98',\n",
|
||
" 'weixin_token': 'Y',\n",
|
||
" 'e_c': 'MTMxMDU1MjE0NzM1Mjc5ODg=',\n",
|
||
" 'acw_tc': '76b20fb517801201588657844e21ecfb30d9ab85051f45e42c388e7e4400de',\n",
|
||
"}\n",
|
||
"\n",
|
||
"headers = {\n",
|
||
" 'Accept': 'application/json, text/plain, */*',\n",
|
||
" 'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',\n",
|
||
" 'Connection': 'keep-alive',\n",
|
||
" 'Content-Type': 'application/json;charset=UTF-8',\n",
|
||
" 'Irisclient': 'PC-web#tedspc',\n",
|
||
" 'Origin': 'https://teds.tyreplus.com.cn',\n",
|
||
" 'Referer': 'https://teds.tyreplus.com.cn/tedspc/index.html',\n",
|
||
" 'Sec-Fetch-Dest': 'empty',\n",
|
||
" 'Sec-Fetch-Mode': 'cors',\n",
|
||
" 'Sec-Fetch-Site': 'same-origin',\n",
|
||
" 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0',\n",
|
||
" 'sec-ch-ua': '\"Chromium\";v=\"148\", \"Microsoft Edge\";v=\"148\", \"Not/A)Brand\";v=\"99\"',\n",
|
||
" 'sec-ch-ua-mobile': '?0',\n",
|
||
" 'sec-ch-ua-platform': '\"Windows\"',\n",
|
||
" # 'Cookie': 'Hm_lvt_684c22b31d0037eca5a691cde16370ad=1773130169,1774314982; e_token=078d3c82d4cf4d68a86d147b7797cf98; weixin_token=Y; e_c=MTMxMDU1MjE0NzM1Mjc5ODg=; acw_tc=76b20fb517801201588657844e21ecfb30d9ab85051f45e42c388e7e4400de',\n",
|
||
"}\n",
|
||
"\n",
|
||
"\n",
|
||
"# 业务参数配置\n",
|
||
"TARGET_URL = 'https://teds.tyreplus.com.cn/api/aftersales/payment/queryPaymentSettlementListByCondition'\n",
|
||
"STORE_CODES = [\"125515\"] # 门店列表\n",
|
||
"PAGE_SIZE = 10\n",
|
||
"OUTPUT_PATH = r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\18027080999结算单.xlsx\"\n",
|
||
"MAX_RETRIES = 3\n",
|
||
"\n",
|
||
"# ================= 功能函数 =================\n",
|
||
"\n",
|
||
"def fetch_page_with_retry(page_num, store_list, max_retries=MAX_RETRIES):\n",
|
||
" \"\"\"\n",
|
||
" 带重试机制的单页数据获取函数\n",
|
||
" :param page_num: 当前页码 (从1开始)\n",
|
||
" :param store_list: 门店代码列表\n",
|
||
" :param max_retries: 最大重试次数\n",
|
||
" :return: 成功的响应JSON对象，若最终失败则返回None\n",
|
||
" \"\"\"\n",
|
||
" json_data = {\n",
|
||
" 'storeCodeList': store_list,\n",
|
||
" 'pageStart': page_num,\n",
|
||
" 'pageNums': 10,\n",
|
||
" 'pageNum': page_num,\n",
|
||
" 'pageSize': PAGE_SIZE,\n",
|
||
" }\n",
|
||
"\n",
|
||
" for attempt in range(max_retries):\n",
|
||
" try:\n",
|
||
" response = requests.post(\n",
|
||
" TARGET_URL,\n",
|
||
" cookies=cookies,\n",
|
||
" headers=headers,\n",
|
||
" json=json_data,\n",
|
||
" timeout=15 # 增加超时时间以防网络波动\n",
|
||
" )\n",
|
||
" response.raise_for_status() # 检查HTTP状态码 (如404, 500)\n",
|
||
" result = response.json()\n",
|
||
"\n",
|
||
" # 检查业务逻辑是否成功 (通常code=200或success=true表示成功)\n",
|
||
" # 根据实际接口返回调整判断条件，这里假设 obj 存在即视为结构正常\n",
|
||
" if 'obj' in result and 'list' in result['obj']:\n",
|
||
" return result\n",
|
||
" else:\n",
|
||
" # 如果返回了错误信息\n",
|
||
" error_msg = result.get('msg', result.get('message', 'Unknown business error'))\n",
|
||
" print(f\"第{page_num}页业务返回异常: {error_msg}\")\n",
|
||
" # 如果是明确的业务错误（如权限不足），通常重试无效，可直接跳出\n",
|
||
" if \"权限\" in str(error_msg) or \"token\" in str(error_msg).lower():\n",
|
||
" return None\n",
|
||
"\n",
|
||
" except Exception as e:\n",
|
||
" print(f\"第{page_num}页请求失败 (尝试 {attempt+1}/{max_retries}): {str(e)}\")\n",
|
||
" if attempt < max_retries - 1:\n",
|
||
" wait_time = 2 ** attempt # 指数退避: 1s, 2s, 4s...\n",
|
||
" time.sleep(wait_time)\n",
|
||
" else:\n",
|
||
" print(f\"第{page_num}页最终获取失败，将跳过该页。\")\n",
|
||
" return None\n",
|
||
"\n",
|
||
" return None\n",
|
||
"\n",
|
||
"# ================= 主执行逻辑 =================\n",
|
||
"\n",
|
||
"all_data = []\n",
|
||
"current_page = 1\n",
|
||
"total_records = 0\n",
|
||
"total_pages = 0\n",
|
||
"\n",
|
||
"print(\"正在获取第 1 页数据以计算总记录数...\")\n",
|
||
"\n",
|
||
"# 1. 获取第一页，确定总数\n",
|
||
"first_response = fetch_page_with_retry(current_page, STORE_CODES)\n",
|
||
"\n",
|
||
"if first_response:\n",
|
||
" data_list = first_response['obj']['list']\n",
|
||
" all_data += data_list\n",
|
||
"\n",
|
||
" # 获取总数\n",
|
||
" total_records = first_response['obj'].get('total', 0)\n",
|
||
" print(f\"✅ 获取成功。总记录数: {total_records}\")\n",
|
||
"\n",
|
||
" if total_records > 0:\n",
|
||
" # 计算总页数 (向上取整)\n",
|
||
" total_pages = (total_records + PAGE_SIZE - 1) // PAGE_SIZE\n",
|
||
" print(f\"📄 预计总页数: {total_pages} (每页 {PAGE_SIZE} 条)\")\n",
|
||
"\n",
|
||
" # 2. 循环获取剩余页面\n",
|
||
" if total_pages > 1:\n",
|
||
" for page_idx in tqdm(range(2, total_pages + 1), desc=\"正在爬取结算单\", initial=1, total=total_pages):\n",
|
||
" response = fetch_page_with_retry(page_idx, STORE_CODES)\n",
|
||
"\n",
|
||
" if response:\n",
|
||
" page_data = response['obj']['list']\n",
|
||
" all_data += page_data\n",
|
||
" else:\n",
|
||
" print(f\"⚠️ 警告：第 {page_idx} 页数据获取失败，已跳过。\")\n",
|
||
"\n",
|
||
" # 礼貌性延时，防止触发风控\n",
|
||
" time.sleep(0.5)\n",
|
||
" else:\n",
|
||
" print(\"ℹ️ 所有数据已在第一页获取完毕，无需继续爬取。\")\n",
|
||
" else:\n",
|
||
" print(\"ℹ️ 未找到任何符合条件的数据。\")\n",
|
||
"else:\n",
|
||
" print(\"❌ 无法获取第一页数据，程序终止。请检查Cookie是否过期或网络是否正常。\")\n",
|
||
"\n",
|
||
"# ================= 数据保存 =================\n",
|
||
"\n",
|
||
"if all_data:\n",
|
||
" df = pd.DataFrame(all_data)\n",
|
||
" try:\n",
|
||
" df.to_excel(OUTPUT_PATH, index=False)\n",
|
||
" print(f\"\\n🎉 数据已成功保存至: {OUTPUT_PATH}\")\n",
|
||
" print(f\"📊 共保存 {len(df)} 条记录 (期望: {total_records})\")\n",
|
||
"\n",
|
||
" if len(df) < total_records:\n",
|
||
" print(f\"⚠️ 注意：实际保存数量 ({len(df)}) 少于期望数量 ({total_records})，部分页面可能获取失败。\")\n",
|
||
"\n",
|
||
" except Exception as e:\n",
|
||
" print(f\"❌ 保存文件时出错: {e}\")\n",
|
||
" print(\"请检查文件路径是否正确，且文件未被其他程序占用。\")\n",
|
||
"else:\n",
|
||
" print(\"\\n💤 没有数据可保存。\")"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"id": "44cdf0d8f5f01c7d",
|
||
"metadata": {},
|
||
"source": [
|
||
"历史维修记录明细（结算单明细）"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 1,
|
||
"id": "b7e0d7a4da6c9b03",
|
||
"metadata": {
|
||
"ExecuteTime": {
|
||
"end_time": "2026-04-23T12:43:38.281667400Z",
|
||
"start_time": "2026-04-23T08:36:56.424636200Z"
|
||
}
|
||
},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"application/vnd.jupyter.widget-view+json": {
|
||
"model_id": "12fa0cd57dfa4ce6800c2d0f318d8a1b",
|
||
"version_major": 2,
|
||
"version_minor": 0
|
||
},
|
||
"text/plain": [
|
||
" 0%| | 0/13861 [00:00<?, ?it/s]"
|
||
]
|
||
},
|
||
"metadata": {},
|
||
"output_type": "display_data"
|
||
},
|
||
{
|
||
"name": "stdout",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"请求异常: HTTPSConnectionPool(host='teds.tyreplus.com.cn', port=443): Max retries exceeded with url: /api/v2/aftersales/payment/queryPaymentSettlementDetail (Caused by ConnectTimeoutError(<HTTPSConnection(host='teds.tyreplus.com.cn', port=443) at 0x16c7296a210>, 'Connection to teds.tyreplus.com.cn timed out. (connect timeout=10)')) (paymentNo: SI2604131850723783)\n",
|
||
"HTTP 请求失败，状态码: 504 (paymentNo: SI2505301159182058)\n",
|
||
"请求异常: HTTPSConnectionPool(host='teds.tyreplus.com.cn', port=443): Max retries exceeded with url: /api/v2/aftersales/payment/queryPaymentSettlementDetail (Caused by ConnectTimeoutError(<HTTPSConnection(host='teds.tyreplus.com.cn', port=443) at 0x16c7296a5d0>, 'Connection to teds.tyreplus.com.cn timed out. (connect timeout=10)')) (paymentNo: SI2504041651975383)\n",
|
||
"请求异常: HTTPSConnectionPool(host='teds.tyreplus.com.cn', port=443): Max retries exceeded with url: /api/v2/aftersales/payment/queryPaymentSettlementDetail (Caused by ConnectTimeoutError(<HTTPSConnection(host='teds.tyreplus.com.cn', port=443) at 0x16c7296a210>, 'Connection to teds.tyreplus.com.cn timed out. (connect timeout=10)')) (paymentNo: SI2502080511238782)\n",
|
||
"请求异常: HTTPSConnectionPool(host='teds.tyreplus.com.cn', port=443): Max retries exceeded with url: /api/v2/aftersales/payment/queryPaymentSettlementDetail (Caused by ConnectTimeoutError(<HTTPSConnection(host='teds.tyreplus.com.cn', port=443) at 0x16c7296a5d0>, 'Connection to teds.tyreplus.com.cn timed out. (connect timeout=10)')) (paymentNo: SI2502080509234932)\n",
|
||
"请求异常: HTTPSConnectionPool(host='teds.tyreplus.com.cn', port=443): Max retries exceeded with url: /api/v2/aftersales/payment/queryPaymentSettlementDetail (Caused by ConnectTimeoutError(<HTTPSConnection(host='teds.tyreplus.com.cn', port=443) at 0x16c7296b890>, 'Connection to teds.tyreplus.com.cn timed out. (connect timeout=10)')) (paymentNo: SI2502080508232252)\n",
|
||
"请求异常: HTTPSConnectionPool(host='teds.tyreplus.com.cn', port=443): Max retries exceeded with url: /api/v2/aftersales/payment/queryPaymentSettlementDetail (Caused by ConnectTimeoutError(<HTTPSConnection(host='teds.tyreplus.com.cn', port=443) at 0x16c7296b890>, 'Connection to teds.tyreplus.com.cn timed out. (connect timeout=10)')) (paymentNo: SI2502080502220502)\n",
|
||
"请求异常: HTTPSConnectionPool(host='teds.tyreplus.com.cn', port=443): Max retries exceeded with url: /api/v2/aftersales/payment/queryPaymentSettlementDetail (Caused by ConnectTimeoutError(<HTTPSConnection(host='teds.tyreplus.com.cn', port=443) at 0x16c7f86b390>, 'Connection to teds.tyreplus.com.cn timed out. (connect timeout=10)')) (paymentNo: SI2502080457209621)\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"import requests\n",
|
||
"from tqdm.notebook import tqdm\n",
|
||
"import pandas as pd\n",
|
||
"import time\n",
|
||
"\n",
|
||
"cookies = {\n",
|
||
" 'Hm_lvt_684c22b31d0037eca5a691cde16370ad': '1773130169,1774314982',\n",
|
||
" 'acw_tc': '76b20fe117802768775208769ed2b23577232cf69ace8a477e7b4fc813a8c1',\n",
|
||
" 'e_token': 'd998582737bd490eb3ae2372b0793f9b',\n",
|
||
" 'weixin_token': 'Y',\n",
|
||
" 'e_c': 'MTMxMDU1MjE0NzM1Mjc5ODg=',\n",
|
||
" 'e_i_o_c_n': 'JTIyJUU5JUE5JUIwJUU1JThBJUEwJUU2JUIxJUJEJUU4JUJEJUE2JUU2JTlDJThEJUU1JThBJUExJUU0JUI4JUFEJUU1JUJGJTgzJUU1JTg3JUE0JUU1JUIyJTk3JUU5JTk1JTg3JUU5JTg3JTkxJUU1JTg3JUE0JUU4JUI3JUFGJUU1JUJBJTk3JTVCKzIwMDMwMzQrJTVEJTIy',\n",
|
||
" 'e_i_p_c_n': 'JTIyJUU5JUE5JUIwJUU1JThBJUEwJUU2JUIxJUJEJUU4JUJEJUE2JUU2JTlDJThEJUU1JThBJUExJUU0JUI4JUFEJUU1JUJGJTgzJUU1JTg3JUE0JUU1JUIyJTk3JUU5JTk1JTg3JUU5JTg3JTkxJUU1JTg3JUE0JUU4JUI3JUFGJUU1JUJBJTk3JUU1JUJBJTk3JUU0JUI4JUJCJTVCKzIwMjQ0OTUrJTVEJTIy',\n",
|
||
"}\n",
|
||
"\n",
|
||
"headers = {\n",
|
||
" 'Accept': 'application/json, text/plain, */*',\n",
|
||
" 'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',\n",
|
||
" 'Connection': 'keep-alive',\n",
|
||
" 'Content-Type': 'application/json;charset=UTF-8',\n",
|
||
" 'Irisclient': 'PC-web#tedspc',\n",
|
||
" 'Origin': 'https://teds.tyreplus.com.cn',\n",
|
||
" 'Referer': 'https://teds.tyreplus.com.cn/tedspc/index.html',\n",
|
||
" 'Sec-Fetch-Dest': 'empty',\n",
|
||
" 'Sec-Fetch-Mode': 'cors',\n",
|
||
" 'Sec-Fetch-Site': 'same-origin',\n",
|
||
" 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0',\n",
|
||
" 'sec-ch-ua': '\"Chromium\";v=\"148\", \"Microsoft Edge\";v=\"148\", \"Not/A)Brand\";v=\"99\"',\n",
|
||
" 'sec-ch-ua-mobile': '?0',\n",
|
||
" 'sec-ch-ua-platform': '\"Windows\"',\n",
|
||
" # 'Cookie': 'Hm_lvt_684c22b31d0037eca5a691cde16370ad=1773130169,1774314982; acw_tc=76b20fe117802768775208769ed2b23577232cf69ace8a477e7b4fc813a8c1; e_token=d998582737bd490eb3ae2372b0793f9b; weixin_token=Y; e_c=MTMxMDU1MjE0NzM1Mjc5ODg=; e_i_o_c_n=JTIyJUU5JUE5JUIwJUU1JThBJUEwJUU2JUIxJUJEJUU4JUJEJUE2JUU2JTlDJThEJUU1JThBJUExJUU0JUI4JUFEJUU1JUJGJTgzJUU1JTg3JUE0JUU1JUIyJTk3JUU5JTk1JTg3JUU5JTg3JTkxJUU1JTg3JUE0JUU4JUI3JUFGJUU1JUJBJTk3JTVCKzIwMDMwMzQrJTVEJTIy; e_i_p_c_n=JTIyJUU5JUE5JUIwJUU1JThBJUEwJUU2JUIxJUJEJUU4JUJEJUE2JUU2JTlDJThEJUU1JThBJUExJUU0JUI4JUFEJUU1JUJGJTgzJUU1JTg3JUE0JUU1JUIyJTk3JUU5JTk1JTg3JUU5JTg3JTkxJUU1JTg3JUE0JUU4JUI3JUFGJUU1JUJBJTk3JUU1JUJBJTk3JUU0JUI4JUJCJTVCKzIwMjQ0OTUrJTVEJTIy',\n",
|
||
"}\n",
|
||
"\n",
|
||
"df = pd.read_excel(fr\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\18027080999结算单.xlsx\", sheet_name='Sheet1')\n",
|
||
"\n",
|
||
"all_data_list = []\n",
|
||
"all_service_data = []\n",
|
||
"all_order_data = []\n",
|
||
"all_payment_data = []\n",
|
||
"\n",
|
||
"for index, row in tqdm(df.iterrows(), total=len(df)):\n",
|
||
" id = row[\"paymentNo\"]\n",
|
||
" json_data = {\n",
|
||
" 'paymentNo': id,\n",
|
||
" }\n",
|
||
"\n",
|
||
" retry_count = 0\n",
|
||
" success = False\n",
|
||
"\n",
|
||
" while retry_count < 5 and not success:\n",
|
||
" try:\n",
|
||
" response = requests.post(\n",
|
||
" 'https://teds.tyreplus.com.cn/api/v2/aftersales/payment/queryPaymentSettlementDetail',\n",
|
||
" cookies=cookies,\n",
|
||
" headers=headers,\n",
|
||
" json=json_data,\n",
|
||
" timeout=10 # 建议加上超时防止卡死\n",
|
||
" )\n",
|
||
"\n",
|
||
" # 检查响应状态码和返回内容是否有效\n",
|
||
" if response.status_code == 200:\n",
|
||
" resp_json = response.json()\n",
|
||
" if resp_json.get('code') == 'success' and 'obj' in resp_json:\n",
|
||
" all_data = resp_json['obj']\n",
|
||
" service_list = all_data.get('orderServiceList', [])\n",
|
||
" order_list = all_data.get('orderSkuList', [])\n",
|
||
" paymentMethodList = all_data.get('paymentMethodList', [])\n",
|
||
"\n",
|
||
" # 添加 paymentNo 字段\n",
|
||
" all_data['paymentNo'] = id\n",
|
||
" all_data_list.append(all_data)\n",
|
||
"\n",
|
||
" for service in service_list:\n",
|
||
" service['paymentNo'] = id\n",
|
||
" all_service_data.append(service)\n",
|
||
"\n",
|
||
" for order in order_list:\n",
|
||
" order['paymentNo'] = id\n",
|
||
" all_order_data.append(order)\n",
|
||
"\n",
|
||
" for payment in paymentMethodList:\n",
|
||
" payment['paymentNo'] = id\n",
|
||
" all_payment_data.append(payment)\n",
|
||
"\n",
|
||
" success = True # 成功获取数据，跳出重试循环\n",
|
||
" else:\n",
|
||
" print(f\"请求返回非成功状态: {resp_json.get('message', '未知错误')} (paymentNo: {id})\")\n",
|
||
" else:\n",
|
||
" print(f\"HTTP 请求失败，状态码: {response.status_code} (paymentNo: {id})\")\n",
|
||
"\n",
|
||
" except Exception as e:\n",
|
||
" print(f\"请求异常: {e} (paymentNo: {id})\")\n",
|
||
"\n",
|
||
" if not success:\n",
|
||
" retry_count += 1\n",
|
||
" if retry_count < 5:\n",
|
||
" time.sleep(2) # 等待 2 秒后重试\n",
|
||
" else:\n",
|
||
" print(f\"已达到最大重试次数，跳过 paymentNo: {id}\")\n",
|
||
"\n",
|
||
" time.sleep(1) # 正常请求之间仍保留 1 秒间隔\n",
|
||
"\n",
|
||
"# 创建DataFrame\n",
|
||
"df = pd.DataFrame(all_data_list)\n",
|
||
"df1 = pd.DataFrame(all_service_data)\n",
|
||
"df2 = pd.DataFrame(all_order_data)\n",
|
||
"df3 = pd.DataFrame(all_payment_data)\n",
|
||
"\n",
|
||
"# 保存到Excel\n",
|
||
"df.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细1.xlsx\", index=False)\n",
|
||
"df1.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细-服务明细1.xlsx\",\n",
|
||
" index=False)\n",
|
||
"df2.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细-产品明细1.xlsx\",\n",
|
||
" index=False)\n",
|
||
"df3.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细-支付方式1.xlsx\",\n",
|
||
" index=False)\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"id": "bdef2047419fb838",
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"application/vnd.jupyter.widget-view+json": {
|
||
"model_id": "97447ec8af08478fa6c858910f2fb294",
|
||
"version_major": 2,
|
||
"version_minor": 0
|
||
},
|
||
"text/plain": [
|
||
"处理进度: 0%| | 0/13861 [00:00<?, ?条/s]"
|
||
]
|
||
},
|
||
"metadata": {},
|
||
"output_type": "display_data"
|
||
}
|
||
],
|
||
"source": [
|
||
"import requests\n",
|
||
"from tqdm.notebook import tqdm\n",
|
||
"import pandas as pd\n",
|
||
"import time\n",
|
||
"from concurrent.futures import ThreadPoolExecutor, as_completed\n",
|
||
"from threading import Lock\n",
|
||
"\n",
|
||
"# 全局锁，用于保护共享数据的写入\n",
|
||
"lock = Lock()\n",
|
||
"\n",
|
||
"cookies = {\n",
|
||
" 'Hm_lvt_684c22b31d0037eca5a691cde16370ad': '1773130169,1774314982',\n",
|
||
" 'e_token': '078d3c82d4cf4d68a86d147b7797cf98',\n",
|
||
" 'weixin_token': 'Y',\n",
|
||
" 'e_c': 'MTMxMDU1MjE0NzM1Mjc5ODg=',\n",
|
||
" 'acw_tc': '76b20fb517801201588657844e21ecfb30d9ab85051f45e42c388e7e4400de',\n",
|
||
"}\n",
|
||
"\n",
|
||
"headers = {\n",
|
||
" 'Accept': 'application/json, text/plain, */*',\n",
|
||
" 'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',\n",
|
||
" 'Connection': 'keep-alive',\n",
|
||
" 'Content-Type': 'application/json;charset=UTF-8',\n",
|
||
" 'Irisclient': 'PC-web#tedspc',\n",
|
||
" 'Origin': 'https://teds.tyreplus.com.cn',\n",
|
||
" 'Referer': 'https://teds.tyreplus.com.cn/tedspc/index.html',\n",
|
||
" 'Sec-Fetch-Dest': 'empty',\n",
|
||
" 'Sec-Fetch-Mode': 'cors',\n",
|
||
" 'Sec-Fetch-Site': 'same-origin',\n",
|
||
" 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0',\n",
|
||
" 'sec-ch-ua': '\"Chromium\";v=\"148\", \"Microsoft Edge\";v=\"148\", \"Not/A)Brand\";v=\"99\"',\n",
|
||
" 'sec-ch-ua-mobile': '?0',\n",
|
||
" 'sec-ch-ua-platform': '\"Windows\"',\n",
|
||
"}\n",
|
||
"\n",
|
||
"df = pd.read_excel(fr\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\18027080999结算单.xlsx\", sheet_name='Sheet1')\n",
|
||
"\n",
|
||
"all_data_list = []\n",
|
||
"all_service_data = []\n",
|
||
"all_order_data = []\n",
|
||
"all_payment_data = []\n",
|
||
"\n",
|
||
"def fetch_payment_data(row_data, pbar=None):\n",
|
||
" \"\"\"\n",
|
||
" 处理单个 paymentNo 的请求\n",
|
||
" \"\"\"\n",
|
||
" id = row_data[\"paymentNo\"]\n",
|
||
" json_data = {\n",
|
||
" 'paymentNo': id,\n",
|
||
" }\n",
|
||
" \n",
|
||
" retry_count = 0\n",
|
||
" success = False\n",
|
||
" result = {\n",
|
||
" 'all_data': None,\n",
|
||
" 'services': [],\n",
|
||
" 'orders': [],\n",
|
||
" 'payments': []\n",
|
||
" }\n",
|
||
" \n",
|
||
" while retry_count < 5 and not success:\n",
|
||
" try:\n",
|
||
" response = requests.post(\n",
|
||
" 'https://teds.tyreplus.com.cn/api/v2/aftersales/payment/queryPaymentSettlementDetail',\n",
|
||
" cookies=cookies,\n",
|
||
" headers=headers,\n",
|
||
" json=json_data,\n",
|
||
" timeout=10\n",
|
||
" )\n",
|
||
" \n",
|
||
" if response.status_code == 200:\n",
|
||
" resp_json = response.json()\n",
|
||
" if resp_json.get('code') == 'success' and 'obj' in resp_json:\n",
|
||
" all_data = resp_json['obj']\n",
|
||
" service_list = all_data.get('orderServiceList', [])\n",
|
||
" order_list = all_data.get('orderSkuList', [])\n",
|
||
" paymentMethodList = all_data.get('paymentMethodList', [])\n",
|
||
" \n",
|
||
" # 添加 paymentNo 字段\n",
|
||
" all_data['paymentNo'] = id\n",
|
||
" result['all_data'] = all_data\n",
|
||
" \n",
|
||
" for service in service_list:\n",
|
||
" service['paymentNo'] = id\n",
|
||
" result['services'].append(service)\n",
|
||
" \n",
|
||
" for order in order_list:\n",
|
||
" order['paymentNo'] = id\n",
|
||
" result['orders'].append(order)\n",
|
||
" \n",
|
||
" for payment in paymentMethodList:\n",
|
||
" payment['paymentNo'] = id\n",
|
||
" result['payments'].append(payment)\n",
|
||
" \n",
|
||
" success = True\n",
|
||
" else:\n",
|
||
" print(f\"\\n请求返回非成功状态: {resp_json.get('message', '未知错误')} (paymentNo: {id})\")\n",
|
||
" else:\n",
|
||
" print(f\"\\nHTTP 请求失败，状态码: {response.status_code} (paymentNo: {id})\")\n",
|
||
" \n",
|
||
" except Exception as e:\n",
|
||
" print(f\"\\n请求异常: {e} (paymentNo: {id})\")\n",
|
||
" \n",
|
||
" if not success:\n",
|
||
" retry_count += 1\n",
|
||
" if retry_count < 5:\n",
|
||
" time.sleep(2)\n",
|
||
" else:\n",
|
||
" print(f\"\\n已达到最大重试次数，跳过 paymentNo: {id}\")\n",
|
||
" \n",
|
||
" # 更新进度条\n",
|
||
" if pbar:\n",
|
||
" pbar.update(1)\n",
|
||
" \n",
|
||
" return result\n",
|
||
"\n",
|
||
"# 使用线程池，最大并发数为3\n",
|
||
"max_workers = 3\n",
|
||
"\n",
|
||
"# 创建进度条\n",
|
||
"with tqdm(total=len(df), desc=\"处理进度\", unit=\"条\", ncols=100) as pbar:\n",
|
||
" with ThreadPoolExecutor(max_workers=max_workers) as executor:\n",
|
||
" # 提交所有任务\n",
|
||
" future_to_row = {\n",
|
||
" executor.submit(fetch_payment_data, row, pbar): row \n",
|
||
" for _, row in df.iterrows()\n",
|
||
" }\n",
|
||
" \n",
|
||
" # 处理完成的任务\n",
|
||
" for future in as_completed(future_to_row):\n",
|
||
" row = future_to_row[future]\n",
|
||
" try:\n",
|
||
" result = future.result()\n",
|
||
" \n",
|
||
" # 使用锁保护共享数据的写入\n",
|
||
" with lock:\n",
|
||
" if result['all_data']:\n",
|
||
" all_data_list.append(result['all_data'])\n",
|
||
" \n",
|
||
" if result['services']:\n",
|
||
" all_service_data.extend(result['services'])\n",
|
||
" \n",
|
||
" if result['orders']:\n",
|
||
" all_order_data.extend(result['orders'])\n",
|
||
" \n",
|
||
" if result['payments']:\n",
|
||
" all_payment_data.extend(result['payments'])\n",
|
||
" \n",
|
||
" except Exception as e:\n",
|
||
" print(f\"\\n处理 paymentNo {row['paymentNo']} 时发生异常: {e}\")\n",
|
||
"\n",
|
||
"# 创建DataFrame\n",
|
||
"print(\"\\n正在保存数据...\")\n",
|
||
"df_result = pd.DataFrame(all_data_list)\n",
|
||
"df1 = pd.DataFrame(all_service_data)\n",
|
||
"df2 = pd.DataFrame(all_order_data)\n",
|
||
"df3 = pd.DataFrame(all_payment_data)\n",
|
||
"\n",
|
||
"# 保存到Excel\n",
|
||
"with tqdm(total=4, desc=\"保存文件\", unit=\"个\") as pbar:\n",
|
||
" df_result.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细1.xlsx\", index=False)\n",
|
||
" pbar.update(1)\n",
|
||
" pbar.set_postfix_str(\"主数据\")\n",
|
||
" \n",
|
||
" df1.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细-服务明细1.xlsx\", index=False)\n",
|
||
" pbar.update(1)\n",
|
||
" pbar.set_postfix_str(\"服务明细\")\n",
|
||
" \n",
|
||
" df2.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细-产品明细1.xlsx\", index=False)\n",
|
||
" pbar.update(1)\n",
|
||
" pbar.set_postfix_str(\"产品明细\")\n",
|
||
" \n",
|
||
" df3.to_excel(r\"D:\\Idea Project\\F6+宜搭+其它(1)\\张阳脚本\\文件输出\\1历史维修记录明细-支付方式1.xlsx\", index=False)\n",
|
||
" pbar.update(1)\n",
|
||
" pbar.set_postfix_str(\"支付方式\")\n",
|
||
"\n",
|
||
"print(f\"\\n✅ 处理完成！\")\n",
|
||
"print(f\"📊 主数据: {len(df_result)} 条\")\n",
|
||
"print(f\"📊 服务明细: {len(df1)} 条\")\n",
|
||
"print(f\"📊 产品明细: {len(df2)} 条\")\n",
|
||
"print(f\"📊 支付方式: {len(df3)} 条\")"
|
||
]
|
||
}
|
||
],
|
||
"metadata": {
|
||
"kernelspec": {
|
||
"display_name": "F6+宜搭+其它",
|
||
"language": "python",
|
||
"name": "python3"
|
||
},
|
||
"language_info": {
|
||
"codemirror_mode": {
|
||
"name": "ipython",
|
||
"version": 3
|
||
},
|
||
"file_extension": ".py",
|
||
"mimetype": "text/x-python",
|
||
"name": "python",
|
||
"nbconvert_exporter": "python",
|
||
"pygments_lexer": "ipython3",
|
||
"version": "3.13.11"
|
||
}
|
||
},
|
||
"nbformat": 4,
|
||
"nbformat_minor": 5
|
||
}
|