31 lines
1.4 KiB
Python
31 lines
1.4 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
"""删除历史[微信]记录中与本次导入重复的（避免双计）"""
|
|
import pymysql, json
|
|
cfg = json.load(open('db_config.json'))
|
|
conn = pymysql.connect(host=cfg['host'], port=cfg['port'], user=cfg['user'],
|
|
password=cfg.get('password'), database=cfg['database'], charset='utf8mb4')
|
|
cur = conn.cursor()
|
|
cur.execute("""SELECT id, amount, recorded_date, note FROM variable_expenses
|
|
WHERE member_id=3 AND note LIKE '%[微信]%' AND recorded_date BETWEEN '2026-01-01' AND '2026-06-30'""")
|
|
rows = cur.fetchall()
|
|
old_ids = [] # 历史记录 id
|
|
new_keys = set() # 本次导入 (date, amount)
|
|
for vid, a, d, n in rows:
|
|
key = (d.strftime('%Y-%m-%d'), round(float(a), 2))
|
|
if n.startswith('[微信] 支出'):
|
|
new_keys.add(key)
|
|
else:
|
|
old_ids.append((vid, key))
|
|
# 删除历史中与本次导入重复的
|
|
to_delete = [vid for vid, key in old_ids if key in new_keys]
|
|
print(f"待删除重复历史记录: {len(to_delete)}笔")
|
|
for vid in to_delete:
|
|
cur.execute("DELETE FROM variable_expenses WHERE id=%s", (vid,))
|
|
conn.commit()
|
|
# 删除后剩余历史记录
|
|
cur.execute("""SELECT COUNT(*), SUM(amount) FROM variable_expenses
|
|
WHERE member_id=3 AND note LIKE '%[微信]%' AND recorded_date BETWEEN '2026-01-01' AND '2026-06-30' AND note NOT LIKE '[微信] 支出%'""")
|
|
print("剩余历史微信记录:", cur.fetchone())
|
|
conn.close()
|