108 lines
3.3 KiB
Python
Executable File
108 lines
3.3 KiB
Python
Executable File
import pandas as pd
|
||
import psycopg2
|
||
import json
|
||
import os
|
||
|
||
# ================= 配置区 =================
|
||
DB_CONFIG = {
|
||
'dbname': 'inventory_system',
|
||
'user': 'test',
|
||
'password': '1234',
|
||
'host': 'localhost',
|
||
'port': '5435'
|
||
}
|
||
|
||
EXCEL_FILE = "Odoo_Archive/Odoo产品_终极大满贯版.xlsx"
|
||
|
||
|
||
# ================= 辅助函数 =================
|
||
def process_paths_only(json_str):
|
||
"""
|
||
将爬虫的绝对路径，转换为现有后端接口完美支持的纯文件名格式！
|
||
"""
|
||
if not json_str or str(json_str).strip() in ['[]', 'nan', 'None']:
|
||
return '[]'
|
||
|
||
try:
|
||
paths = json.loads(json_str)
|
||
new_paths = []
|
||
|
||
for path in paths:
|
||
if path.startswith('http://') or path.startswith('https://'):
|
||
new_paths.append(path)
|
||
else:
|
||
filename = os.path.basename(path)
|
||
|
||
# 【终极修复】去掉中间的子文件夹，直接请求文件名！
|
||
web_path = f"/api/v1/common/files/{filename}"
|
||
new_paths.append(web_path)
|
||
|
||
return json.dumps(new_paths, ensure_ascii=False)
|
||
|
||
except Exception as e:
|
||
return '[]'
|
||
|
||
|
||
# ================= 主程序 =================
|
||
def process_excel_to_db():
|
||
if not os.path.exists(EXCEL_FILE):
|
||
print(f"❌ 找不到 Excel 文件: {EXCEL_FILE}")
|
||
return
|
||
|
||
try:
|
||
df = pd.read_excel(EXCEL_FILE, dtype=str)
|
||
df = df.where(pd.notnull(df), None)
|
||
print(f"✅ 成功读取 Excel，共 {len(df)} 行数据。")
|
||
|
||
conn = psycopg2.connect(**DB_CONFIG)
|
||
cur = conn.cursor()
|
||
success_count = 0
|
||
|
||
for index, row in df.iterrows():
|
||
internal_ref = row.get('内部参考')
|
||
barcode = row.get('条码')
|
||
|
||
spec_model = ""
|
||
if barcode and internal_ref:
|
||
spec_model = f"{barcode}/{internal_ref}"
|
||
elif barcode:
|
||
spec_model = f"{barcode}"
|
||
elif internal_ref:
|
||
spec_model = f"{internal_ref}"
|
||
else:
|
||
continue
|
||
|
||
raw_image_json = row.get('generalImage')
|
||
raw_manual_json = row.get('generalManual')
|
||
|
||
if (not raw_image_json or raw_image_json == '[]') and (not raw_manual_json or raw_manual_json == '[]'):
|
||
continue
|
||
|
||
product_image = process_paths_only(raw_image_json)
|
||
manual_link = process_paths_only(raw_manual_json)
|
||
|
||
update_query = """
|
||
UPDATE material_base
|
||
SET product_image = %s, \
|
||
manual_link = %s
|
||
WHERE spec_model = %s
|
||
"""
|
||
cur.execute(update_query, (product_image, manual_link, spec_model))
|
||
|
||
if cur.rowcount > 0:
|
||
success_count += 1
|
||
|
||
conn.commit()
|
||
print(f"\n🎉 导入完成！成功更新了 {success_count} 条数据的正确路径。")
|
||
print("💡 赶快去刷新前端看看吧！这次图片一定能刷出来！")
|
||
|
||
except Exception as e:
|
||
print(f"❌ 发生致命错误: {e}")
|
||
if 'conn' in locals() and conn: conn.rollback()
|
||
finally:
|
||
if 'cur' in locals() and cur: cur.close()
|
||
if 'conn' in locals() and conn: conn.close()
|
||
|
||
|
||
if __name__ == "__main__":
|
||
process_excel_to_db() |