33 KiB
33 KiB
In [7]:
# 根据订单号，查询实际包裹尺寸，实际包裹实重，实际包裹体积重， # bill账单给头程单价，实际尾端计费重，预估尾端成本，尾端附加费 import os import re import pandas as pd from utils.Package import Package,Package_group from utils.gtools import MySQLconnect from utils.countryOperator import OperateCountry from utils.logisticsBill import BillFactory, Billing import requests import json ods = MySQLconnect("ods") engine = ods.engine() def get_SKU_info(order_id): """ 根据订单号获取SKU信息,以及入库采购价 """ SKULIST = """ SELECT order_id, CASE WHEN COUNT(DISTINCT opl.SKU) = 1 THEN "单SKU" ELSE "多SKU" end as "SKU类型", CONCAT(group_concat(opl.SKU),",") as "SKU明细", sum(s1.`成本价`*product_num) AS "入库采购价" FROM dws.order_product_list opl LEFT JOIN ods.stg_bayshop_litfad_sku s1 on s1.sku = opl.sku LEFT JOIN dwd.dim_erp_sku_package_vol_info des on des.erp_sku = opl.sku WHERE order_id = %s AND order_product_id regexp "[0-9]{15}_[0-9]*$" GROUP BY order_id """ engine = ods.engine() sku_df = pd.read_sql(SKULIST, engine,params=(order_id,)) # print("SKU类型:",sku_df['SKU类型'][0],"SKU明细:",sku_df['SKU明细'][0],"入库采购价:",sku_df['入库采购价'][0]) return sku_df['SKU类型'][0],sku_df['SKU明细'][0],sku_df['入库采购价'][0] def get_package_real_vol_by_api(packages_id): """ 根据订单号的包裹ID,返回包裹类，包裹明细，包裹实重，包裹体积重6000 """ packages = Package_group() packages_str = "" packages_weight=0 packages_vol = 0 for package_id in packages_id: url = f'https://cp.maso.hk/index.php?main=biphp&act=package_fund&key=W6BOYJ7BH27YCGRFCA0LWBVKMU1KRU5Q&package={package_id}' resp = requests.get(url).json() if resp['code'] == "0": weight = int(float(resp['data'][0]['weight'])*1000) package_length = resp['data'][0]['l'].replace(",","") if len(resp['data'][0]['l'])>0 else "0" package_width = resp['data'][0]['w'].replace(",","") if len(resp['data'][0]['w'])>0 else "0" package_hight = resp['data'][0]['h'].replace(",","") if len(resp['data'][0]['h'])>0 else "0" package = Package(str(package_id),float(package_length),float(package_width),float(package_hight),weight) packages.add_package(package) # 实际包裹尺寸 package_str = f"{weight}|{package_length}*{package_width}*{package_hight}" packages_str += package_str + "," return packages,packages_str def get_shiji_bill(opCountry,order_id,packages_id,postcode,convey,amount): # print(order_id) beizhu = amount conveys = 1 if convey == "海运" else 0 try: packages,packages_str = get_package_real_vol_by_api(packages_id=packages_id) bill = Billing(str(order_id),opCountry,packages,postcode,company_name=None,head_type=conveys,beizhu=beizhu) print(bill) tail_amount = bill.bill_dict()["预测尾端"] total_weight = sum([p.weight/1000 for p in packages.packages]) _type = bill.bill_dict()["尾端渠道"] head_amount = bill.bill_dict()["预测头程CNY"] total_amount = bill.bill_dict()["总金额USD"] volume_weight = bill.bill_dict()["体积重"] per_head = bill.bill_dict()["头程单价"] other_detail = bill.get_other_fee() return packages_str,total_weight,volume_weight,per_head,head_amount,tail_amount,other_detail,_type,total_amount except ZeroDivisionError as e: print(e) return "",0,0,0,0,0,"","",0 def get_package_info_by_sql(order_df): packages = Package_group() packages_str = "" for index, row in order_df.iterrows(): if row['包裹数据'] is None: return None,"" row['包裹数据'] = json.loads(row['包裹数据']) item_list = [ {key: float(re.search(r"\d+\.?\d*", str(value)).group()) for key, value in package.items()} for package in row['包裹数据'].values() ] for item in item_list: if item['长'] == 0 or item['宽'] == 0 or item['高'] == 0 or item['重量'] == 0: return None,"" package = Package(row['SKU'], item['长'], item['宽'], item['高'], item['重量']) packages.add_package(package) # 实际包裹尺寸 package_str = f"{ item['重量']}|{item['长']}*{ item['宽']}*{item['高']}" packages_str += package_str + "," return packages,packages_str def get_biaozhun_bill(country,order_id,postcode,convey,amount): print(order_id) # 取标准包裹数据 sql = """SELECT order_id, opl.SKU, 包裹数据 FROM dws.order_product_list opl LEFT JOIN ads.new_erp_sku_size spi ON opl.SKU =spi.SKU WHERE order_id = %s AND opl.order_product_id REGEXP "[0-9]{15}_[0-9]*$" """ order_package_date = pd.read_sql(sql, engine, params=(order_id,)) print(order_package_date['SKU']) if order_package_date['包裹数据'] is None: return "",0,0,0,0,0,"","" # 取账单数据 beizhu = amount conveys = 1 if convey == "海运" else 0 opCountry = OperateCountry(country) try: packages,packages_str = get_package_info_by_sql(order_package_date) if packages is None: return "",0,0,0,0,0,"","" bill = Billing(str(order_id),opCountry,packages,postcode,company_name=None,head_type=conveys,beizhu=beizhu) print(bill) tail_amount = bill.bill_dict()["预测尾端"] total_weight = sum([p.weight/1000 for p in packages.packages]) _type = bill.bill_dict()["尾端渠道"] head_amount = bill.bill_dict()["预测头程CNY"] total_amount = bill.bill_dict()["总金额USD"] volume_weight = bill.bill_dict()["体积重"] per_head = bill.bill_dict()["头程单价"] other_detail = bill.get_other_fee() return packages_str,total_weight,volume_weight,per_head,head_amount,tail_amount,other_detail,_type except ZeroDivisionError as e: print(e) return "",0,0,0,0,0,"",""
In [8]:
sql = """SELECT ol.order_id , CONCAT("[",GROUP_CONCAT(pr.包裹号),"]") AS package_group , ol.postcode, ol.delivery_country, ol.convey, ols.`货币`, ols.`订单总额`, ols.支付方式, ol.order_price_dollar FROM parcel pr LEFT JOIN dwd.order_list ol ON ol.order_id = pr.订单号 left JOIN ods.order_list_supplement ols ON ol.order_id = ols.订单号 WHERE NOT EXISTS ( SELECT 1 FROM `order_express` oe WHERE oe.包裹号 = pr.包裹号 AND oe.包裹状态 = "已作废") AND order_id IN (250610233027170 , 250608163427468 , 250521222214528 , 250610230415406 , 250607044202071 , 250607144614471 , 250607211414736 , 250401032025780 , 250531061202721 , 250611112802162 , 250614114015175 , 250610185002525 , 250610022439644 , 250404163802384 , 250531191002625 , 250610035602297 , 250609232002941 ) GROUP BY ol.order_id """
In [9]:
order_id_df = pd.read_sql(sql,engine) # SKU类型和SKU明细，以及入库采购价 order_id_df[['订单SKU类型','订单SKU明细','入库采购价']] = order_id_df.apply(lambda x: get_SKU_info(x['order_id']), axis=1, result_type='expand') # 获取标准账单 order_id_df[["标准包裹尺寸详情","标准实重","标准体积重","标准头程单价","标准头程费用(CNY)","标准尾端费用(当地货币)","标准尾端附加费","标准尾端渠道"]] = order_id_df.apply(lambda x: get_biaozhun_bill( x['delivery_country'], x['order_id'], x['postcode'], x['convey'], x['order_price_dollar']),axis=1,result_type='expand') # 获取实际采购价 # order_id_df['实际采购价'] = order_id_df['order_id'].apply(lambda x: get_purchase_price(x)) # 获取实际账单 order_id_df[["实际包裹尺寸详情","实际实重","实际体积重","实际头程单价","实际头程费用(CNY)","实际尾端费用(当地货币)","实际尾端附加费","实际尾端渠道","总金额(USD)"]] = order_id_df.apply(lambda x: get_shiji_bill(OperateCountry(x['delivery_country']), x['order_id'], eval(x['package_group']), x['postcode'], x['convey'], x['order_price_dollar']),axis=1,result_type='expand') order_id_df.to_clipboard()
250401032025780 0 2205003721 Name: SKU, dtype: int64 账单名称: 250401032025780 账单项:head_amount: 22.307399999999998 CNY base: 7.17 USD residential_delivery: 2.08 USD fuel: 1.66 USD tail_amount: 10.91 USD 总金额: 14.033036 USD 250404163802384 0 2205811679 Name: SKU, dtype: int64 账单名称: 250404163802384 账单项:head_amount: 79.78666666666666 CNY base: 11.9 EUR bigpackage: 44.5 EUR fuel: 7.33 EUR tail_amount: 63.73 EUR 总金额: 82.54773333333334 USD 250521222214528 0 2202859120 1 2202859129 Name: SKU, dtype: int64 账单名称: 250521222214528 账单项:head_amount: 2470.0 CNY base: 7.4 GBP tail_amount: 7.4 GBP 总金额: 355.42 USD 250531061202721 0 227752959 Name: SKU, dtype: int64 账单名称: 250531061202721 账单项:head_amount: 109.83735000000001 CNY base: 21.9 USD oversize: 8.17 USD remote: 2.17 USD residential_delivery: 2.08 USD fuel: 6.18 USD tail_amount: 40.5 USD 总金额: 55.877229 USD 250531191002625 0 2205993938 Name: SKU, dtype: int64 账单名称: 250531191002625 账单项:head_amount: 72.83966666666667 CNY base: 11.9 EUR fuel: 1.55 EUR tail_amount: 13.45 EUR 总金额: 25.261553333333335 USD 250607044202071 0 228294469 Name: SKU, dtype: int64 账单名称: 250607044202071 账单项:head_amount: 168.73212180000002 CNY base: 20 USD transfer: 9.37 USD tail_amount: 29.37 USD 总金额: 52.992497052000004 USD 250607144614471 0 227555929 Name: SKU, dtype: int64 账单名称: 250607144614471 账单项:head_amount: 37.5 CNY base: 8.18 USD residential_delivery: 2.08 USD fuel: 1.85 USD tail_amount: 12.11 USD 总金额: 17.36 USD 250607211414736 0 229586263 Name: SKU, dtype: int64 账单名称: 250607211414736 账单项:head_amount: 47.25 CNY base: 7.77 USD residential_delivery: 2.08 USD fuel: 1.77 USD tail_amount: 11.62 USD 总金额: 18.235 USD 250608163427468 0 227658471 Name: SKU, dtype: int64 账单名称: 250608163427468 账单项:head_amount: 554.4 CNY base: 3.7 GBP tail_amount: 3.7 GBP 总金额: 82.426 USD 250609232002941 0 2206109629 Name: SKU, dtype: int64 账单名称: 250609232002941 账单项:head_amount: 94.5 CNY base: 10.2 EUR fuel: 1.33 EUR tail_amount: 11.53 EUR 总金额: 26.1436 USD 250610022439644 0 2206929691 Name: SKU, dtype: int64 账单名称: 250610022439644 账单项:head_amount: 280.0 CNY base: 11.9 EUR overweight: 2.89 EUR fuel: 1.92 EUR tail_amount: 16.71 EUR 总金额: 57.915200000000006 USD 250610035602297 0 2204180587 Name: SKU, dtype: int64 账单名称: 250610035602297 账单项:head_amount: 177.072 CNY base: 20.4 EUR overweight: 2.89 EUR bigpackage: 44.5 EUR fuel: 8.81 EUR tail_amount: 76.6 EUR 总金额: 110.58208 USD 250610185002525 0 2203543601 Name: SKU, dtype: int64 账单名称: 250610185002525 账单项:head_amount: 95.33333333333333 CNY base: 11.9 EUR overweight: 2.89 EUR bigpackage: 44.5 EUR fuel: 7.71 EUR tail_amount: 67.0 EUR 总金额: 88.38666666666667 USD 250610230415406 0 2205149996 Name: SKU, dtype: int64 账单名称: 250610230415406 账单项:head_amount: 735.0 CNY base: 32.25 USD oversize: 11.83 USD remote: 2.17 USD residential_delivery: 2.08 USD fuel: 8.7 USD tail_amount: 57.02 USD 总金额: 159.92000000000002 USD 250610233027170 0 229445257 Name: SKU, dtype: int64 账单名称: 250610233027170 账单项:head_amount: 1645.6533333333334 CNY base: 3.7 GBP tail_amount: 3.7 GBP 总金额: 235.2014666666667 USD 250611112802162 0 2206393292 Name: SKU, dtype: int64 账单名称: 250611112802162 账单项:head_amount: 49.6 CNY base: 6.02 AUD fuel: 0.31 AUD tail_amount: 6.33 AUD 总金额: 11.375 USD 250614114015175 0 2202252863 Name: SKU, dtype: int64 账单名称: 250614114015175 账单项:head_amount: 270.9 CNY base: 17.08 AUD fuel: 0.87 AUD tail_amount: 17.95 AUD 总金额: 50.491 USD 账单名称: 250401032025780 账单项:head_amount: 121.93650000000001 CNY base: 20 USD transfer: 6.77 USD tail_amount: 26.77 USD 总金额: 43.84111 USD 账单名称: 250404163802384 账单项:head_amount: 176.11733333333333 CNY base: 111.67 EUR notify: 5 EUR responsibility: 1.85 EUR management: 2 EUR toll: 0.72 EUR tail_amount: 121.24 EUR 总金额: 160.44522666666668 USD 账单名称: 250521222214528 账单项:head_amount: 14967.906666666668 CNY base: 55.0 GBP tail_amount: 55.0 GBP 总金额: 2167.0069333333336 USD 账单名称: 250531061202721 账单项:head_amount: 694.17 CNY base: 210 USD tail_amount: 210.0 USD 总金额: 307.1838 USD 账单名称: 250531191002625 账单项:head_amount: 381.6 CNY base: 11.9 EUR overweight: 2.89 EUR bigpackage: 44.5 EUR fuel: 7.71 EUR tail_amount: 67.0 EUR 总金额: 128.464 USD 账单名称: 250607044202071 账单项:head_amount: 1059.24 CNY base: 75 USD transfer: 58.85 USD tail_amount: 133.85 USD 总金额: 282.1436 USD 账单名称: 250607144614471 账单项:head_amount: 533.5740000000001 CNY base: 53.85 USD oversize: 8.17 USD residential_delivery: 4.16 USD fuel: 11.91 USD tail_amount: 78.09 USD 总金额: 152.79036000000002 USD 账单名称: 250607211414736 账单项:head_amount: 655.1999999999999 CNY base: 59.17 USD oversize: 8.17 USD residential_delivery: 4.16 USD fuel: 12.87 USD tail_amount: 84.38 USD 总金额: 176.108 USD 账单名称: 250608163427468 账单项:head_amount: 2968.0 CNY base: 48.02 GBP fuel: 4.8 GBP tail_amount: 52.82 GBP 总金额: 484.18600000000004 USD 账单名称: 250609232002941 账单项:head_amount: 351.488 CNY base: 111.41 EUR notify: 5 EUR responsibility: 1.85 EUR management: 2 EUR toll: 1.2 EUR tail_amount: 121.46 EUR 总金额: 185.24352000000002 USD 账单名称: 250610022439644 账单项:head_amount: 3410.88 CNY base: 272.46 EUR notify: 5 EUR responsibility: 1.85 EUR management: 2 EUR toll: 2.16 EUR tail_amount: 283.47 EUR 总金额: 795.0096000000001 USD 账单名称: 250610035602297 账单项:head_amount: 1189.824 CNY base: 310 EUR fuel: 24.8 EUR tail_amount: 334.8 EUR 总金额: 541.55136 USD 账单名称: 250610185002525 账单项:head_amount: 119.25333333333333 CNY base: 136.7 EUR notify: 5 EUR responsibility: 1.85 EUR management: 2 EUR toll: 0.96 EUR tail_amount: 146.51 EUR 总金额: 180.7866666666667 USD 账单名称: 250610230415406 账单项:head_amount: 1500.9866666666667 CNY base: 39.39 USD remote: 6.2 USD big_package: 47.73 USD residential_delivery: 5.95 USD fuel: 17.87 USD tail_amount: 117.14 USD 总金额: 327.27813333333336 USD 账单名称: 250610233027170 账单项:head_amount: 4055.04 CNY base: 76.65 GBP fuel: 7.66 GBP tail_amount: 84.31 GBP 总金额: 677.3086000000001 USD 账单名称: 250611112802162 账单项:head_amount: 793.8000000000001 CNY base: 26.82 AUD fuel: 1.94 AUD tail_amount: 28.76 AUD 总金额: 131.264 USD 账单名称: 250614114015175 账单项:head_amount: 1276.5200000000002 CNY base: 71.19 AUD oversize: 15.5 AUD fuel: 6.29 AUD tail_amount: 92.98 AUD 总金额: 243.79880000000003 USD
In [ ]:
# 取订单号和包裹号 sql = """ SELECT pr.包裹号,pr.订单号 FROM parcel pr WHERE 订单号 IN (250516031202445 ) """ package_df = pd.read_sql(sql,engine) package_df.to_clipboard(index=False)
In [ ]:
from utils.gtools import MySQLconnect import pandas as pd import os # date = "2025/3/5" # 连接数据库 ods = MySQLconnect("ods") engine = ods.engine() # 读取 Excel 数据 excel_path = r"D:\test\logistics\拦截数据\拦截订单登记明细.xlsx" order_df = pd.read_excel(excel_path, sheet_name="拦截明细", skiprows=1) # 读取 Excel 数据时，指定 '订单号' 列为字符串类型 order_df = pd.read_excel(excel_path, sheet_name="拦截明细", skiprows=1, dtype={'订单号': str}) # order_df = order_df[order_df['拦截计算日期'] == date] order_df = order_df[['订单号', '提交拦截申请时间']] # 提取订单号并转为字符串 order_id = [str(x) for x in order_df["订单号"].dropna()] if not order_id: print("没有符合条件的订单号，查询终止。") else: try: # 生成占位符并执行查询 placeholders = ', '.join(['%s'] * len(order_id)) sql = f""" SELECT 单号, 包裹号, 包裹状态, 快递跟踪号, `关联卡板号`, 关联提单号, 打包时间, `快递公司` AS 打单渠道, `包裹测量时间`, 运单生成时间 AS 打单时间, 绑定卡板时间, 绑定提单时间, `提单发货时间` FROM ods.`order_express` WHERE 单号 IN ({placeholders}) """ df = pd.read_sql(sql, engine, params=tuple(order_id)) # 合并数据 order_df["订单号"] = order_df["订单号"].astype(str) df["单号"] = df["单号"].astype(str) merged_df = df.merge(order_df, left_on="单号", right_on="订单号", how="left") merged_df = merged_df.drop(columns=["订单号"]) # 删除重复的 '订单号' 列 # 重新排列列顺序 merged_df = merged_df[[ "单号", "包裹号", "提交拦截申请时间", "包裹状态", "快递跟踪号", "关联卡板号", "关联提单号", "打包时间", "包裹测量时间", "打单时间", "打单渠道", "绑定卡板时间", "绑定提单时间", "提单发货时间" ]] # 检查文件是否存在，并读取目标工作表 if os.path.exists(excel_path): with pd.ExcelFile(excel_path) as xls: sheets = xls.sheet_names if "包裹时间记录" in sheets: existing_df = pd.read_excel(excel_path, sheet_name="包裹时间记录") else: existing_df = pd.DataFrame(columns=merged_df.columns) # 如果表不存在，创建一个空 DataFrame else: existing_df = pd.DataFrame(columns=merged_df.columns) # 如果文件不存在，创建一个空 DataFrame # 过滤掉已存在的包裹号 existing_package_ids = existing_df["包裹号"].astype(str).tolist() new_data = merged_df[~merged_df["包裹号"].astype(str).isin(existing_package_ids)] # 如果没有新数据，直接退出 if new_data.empty: print("没有新数据需要追加。") else: # 追加新数据 combined_df = pd.concat([existing_df, new_data], ignore_index=True) # 写入 Excel（保留其他工作表） with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a' if os.path.exists(excel_path) else 'w', if_sheet_exists='replace') as writer: combined_df.to_excel(writer, sheet_name="包裹时间记录", index=False) print("数据已成功追加到工作表！") except Exception as e: print(f"操作失败，错误详情：{e}")
In [ ]:
# 手动输入包裹的包裹信息 from logisticsClass.logisticsTail_EUR import KPASLLogistics_FR from utils.Package import Package,Package_group from utils.gtools import MySQLconnect from utils.countryOperator import OperateCountry from utils.logisticsBill import BillFactory, Billing packages = Package_group() packages.add_package(Package("包裹1", 78,35,15, 15000)) # packages.add_package(Package("包裹1", 55,45,40, 9000)) # packages.add_package(Package("包裹1", 210,55,20, 32000)) # packages.add_package(Package("包裹1", 103,42,12, 8000)) # packages.add_package(Package("包裹1", 103,42,12, 8000)) country = 'United Kingdom' postcode ='DY13 0RX' conveys= 0 # 1海运0空运 opCountry = OperateCountry(country) bill = Billing(str(1),opCountry,packages,postcode,company_name=None,head_type=conveys,beizhu="1") print(bill) tail_amount = bill.bill_dict()["预测尾端"] total_weight = sum([p.weight/1000 for p in packages.packages]) _type = bill.bill_dict()["尾端渠道"] head_amount = bill.bill_dict()["预测头程CNY"] total_amount = bill.bill_dict()["总金额USD"] volume_weight = bill.bill_dict()["体积重"] per_head = bill.bill_dict()["头程单价"] other_detail = bill.get_other_fee() packages_str = "" for package in packages.packages: package_str = f"{package.weight}|{package.length}*{package.width}*{package.height}" packages_str += package_str + "," print(f"packages_str:{packages_str}") print(f"实重:{total_weight}") print(f"体积重:{volume_weight}") print(f"头程单价:{per_head}") print(f"预测头程CNY:{head_amount}") print(f"预测尾端(当地货币):{tail_amount}") print(f"其他费用:{other_detail}") print(f"尾端渠道:{_type}") print(f"总金额USD:{total_amount}") print(KPASLLogistics_FR.active)
查找供应商退货退款金额
In [ ]:
import pandas as pd import numpy as np from utils.gtools import MySQLconnect ods = MySQLconnect('ods') engine = ods.engine() # 读取 Excel 数据 excel_path = r"D:\test\logistics\拦截数据\拦截订单登记明细.xlsx" order_df = pd.read_excel(excel_path, sheet_name="拦截明细", skiprows=1) # 读取 Excel 数据时，指定 '订单号' 列为字符串类型 order_df = pd.read_excel(excel_path, sheet_name="拦截明细", skiprows=1, dtype={'订单号': str}) # 提取订单号并转为字符串 order_id = [str(x) for x in order_df["订单号"].dropna()] placeholders = ','.join(['%s'] * len(order_id)) sql = f""" WITH t1 AS ( SELECT -- 提取交易号 TRIM(SUBSTRING( `外部关联信息`, LOCATE('交易号：', `外部关联信息`) + CHAR_LENGTH('交易号：'), CASE WHEN LOCATE('单品号：', `外部关联信息`) > 0 THEN LOCATE('单品号：', `外部关联信息`) - LOCATE('交易号：', `外部关联信息`) - CHAR_LENGTH('交易号：') ELSE LOCATE('[', `外部关联信息`) - LOCATE('交易号：', `外部关联信息`) - CHAR_LENGTH('交易号：') END )) AS 交易号, -- 提取单品号（如果存在） CASE WHEN LOCATE('单品号：', `外部关联信息`) > 0 THEN TRIM(SUBSTRING( `外部关联信息`, LOCATE('单品号：', `外部关联信息`) + CHAR_LENGTH('单品号：'), LOCATE('[', `外部关联信息`) - LOCATE('单品号：', `外部关联信息`) - CHAR_LENGTH('单品号：') )) ELSE NULL END AS 单品号, `支付账号`, `处理金额`, `添加时间`, `资金状态`, `完成时间`, 备注 FROM cpmaso_procurement_fund_manage WHERE `添加时间` >= DATE_SUB(NOW(), INTERVAL 8 MONTH) ) SELECT LEFT(order_product_id,15) AS 订单号, order_product_id, buy_audit AS 状态, trans_pay_date AS 采购时间, log_receiving_date AS 到货时间, t1.* FROM `warehouse_purchasing` wp LEFT JOIN t1 ON wp.trans_id = t1.交易号 WHERE 交易号>0 AND LEFT(wp.order_product_id,15) IN ({placeholders}) ORDER BY 添加时间,订单号 """ df = pd.read_sql(sql, engine,params=tuple(order_id)) df.to_clipboard(index = False)
入库
In [ ]:
import pandas as pd import numpy as np from sqlalchemy import text from utils.gtools import MySQLconnect ods = MySQLconnect('ods') engine = ods.engine() # 读取 Excel 数据 excel_path = r"D:\test\logistics\拦截数据\拦截订单登记明细.xlsx" # 读取 Excel 数据时，指定 '订单号' 列为字符串类型 order_df = pd.read_excel(excel_path, sheet_name="拦截明细", skiprows=1, dtype={'订单号': str}) # 提取订单号并转为字符串 order_id = df['订单号'].astype(str).tolist() bind_names = [f":p{i}" for i in range(len(order_id))] placeholders = ', '.join(bind_names) sql = text(f""" SELECT REGEXP_SUBSTR(note, '[0-9]{{15}}') AS 订单号, stock_in_id AS `入库单号`, instock_date AS `入库时间` FROM stockinid_list WHERE note REGEXP '[0-9]{{15}}_' AND REGEXP_SUBSTR(note, '[0-9]{{15}}') IN ({placeholders}) """) params = {f"p{i}": v for i, v in enumerate(order_id)} df = pd.read_sql(sql, engine, params=params) df.to_clipboard(index = False)