80 lines
3.0 KiB
Python
80 lines
3.0 KiB
Python
"""导入 5月31日 库存数据"""
|
|
import csv, os, pymysql
|
|
from config import DB_CONFIG
|
|
|
|
CSV_DIR = "/home/gxd/.openclaw/workspace/dy-dashboard/tmp_csv"
|
|
DATA_DATE = "2026-05-31"
|
|
SOURCE = "wdt"
|
|
|
|
def parse_warehouse(filename):
|
|
"""从文件名提取仓库号: 20260531-wdt-kcmx-01 → 01"""
|
|
return filename.replace(".csv", "").split("-")[-1]
|
|
|
|
def import_file(fpath, wh_code):
|
|
"""导入单个仓库库存文件"""
|
|
rows = []
|
|
with open(fpath, encoding="utf-8-sig") as f:
|
|
reader = csv.DictReader(f)
|
|
for row in reader:
|
|
product_code = (row.get("货品编号") or "").strip()
|
|
if not product_code:
|
|
continue
|
|
rows.append({
|
|
"data_date": DATA_DATE,
|
|
"source": SOURCE,
|
|
"warehouse_code": wh_code,
|
|
"goods_code": (row.get("商家编码") or "").strip(),
|
|
"product_code": product_code,
|
|
"product_name": (row.get("货品名称") or "").strip(),
|
|
"brand": (row.get("品牌") or "").strip(),
|
|
"cost_price": float(str(row.get("成本价", 0) or 0).replace("NA","0")),
|
|
"available_qty": int(str(row.get("可用库存", 0) or 0).replace("NA","0")),
|
|
"production_in_transit": int(str(row.get("生产在途量", 0) or 0).replace("NA","0")),
|
|
"purchase_in_transit": int(str(row.get("采购在途", 0) or 0).replace("NA","0")),
|
|
"category1": (row.get("一级类目") or "").strip(),
|
|
"category2": (row.get("二级类目") or "").strip(),
|
|
"category3": (row.get("三级类目") or "").strip(),
|
|
})
|
|
return rows
|
|
|
|
# 连接DB
|
|
conn = pymysql.connect(**DB_CONFIG)
|
|
cur = conn.cursor()
|
|
|
|
total = 0
|
|
for fname in sorted(os.listdir(CSV_DIR)):
|
|
if not fname.startswith("20260531") or not fname.endswith(".csv"):
|
|
continue
|
|
fpath = os.path.join(CSV_DIR, fname)
|
|
wh_code = parse_warehouse(fname)
|
|
rows = import_file(fpath, wh_code)
|
|
|
|
if not rows:
|
|
print(f" ⚠️ {fname}: 空文件，跳过")
|
|
continue
|
|
|
|
# 删除旧数据
|
|
cur.execute("DELETE FROM inventory_detail WHERE data_date=%s AND warehouse_code=%s",
|
|
(DATA_DATE, wh_code))
|
|
|
|
# 批量插入
|
|
cols = ["data_date","source","warehouse_code","goods_code","product_code",
|
|
"product_name","brand","cost_price","available_qty",
|
|
"production_in_transit","purchase_in_transit","category1","category2","category3"]
|
|
placeholders = ",".join(["%s"] * len(cols))
|
|
sql = f"INSERT INTO inventory_detail ({','.join(cols)}) VALUES ({placeholders})"
|
|
|
|
batch_size = 1000
|
|
for i in range(0, len(rows), batch_size):
|
|
batch = rows[i:i+batch_size]
|
|
values = [[r[c] for c in cols] for r in batch]
|
|
cur.executemany(sql, values)
|
|
|
|
conn.commit()
|
|
print(f" ✅ {fname}: {len(rows)} 条 (仓库{wh_code})")
|
|
total += len(rows)
|
|
|
|
cur.close()
|
|
conn.close()
|
|
print(f"\n✅ 导入完成! 总计 {total} 条库存记录")
|