50 lines
1.9 KiB
Python
50 lines
1.9 KiB
Python
import json, sqlite3
|
|
from datetime import datetime
|
|
|
|
d = json.load(open('/home/hmo/web-dashboard/data/price_events.json'))
|
|
events = d.get('events', d if isinstance(d, list) else [])
|
|
print(f'JSON events: {len(events)}')
|
|
|
|
conn = sqlite3.connect('/home/hmo/MoFin/data/mofin.db', timeout=30)
|
|
conn.execute('PRAGMA busy_timeout=30000')
|
|
|
|
# 已有的 DB 记录（避免重复）：用 code+created_at 做粗粒度去重
|
|
existing = set()
|
|
for r in conn.execute("SELECT code, created_at FROM price_events"):
|
|
existing.add((r[0], r[1]))
|
|
|
|
inserted = 0
|
|
skipped = 0
|
|
for e in events:
|
|
code = str(e.get('code', ''))
|
|
name = e.get('name', code)
|
|
ts = e.get('timestamp') or e.get('created_at') or ''
|
|
if not code or not ts:
|
|
skipped += 1
|
|
continue
|
|
# 统一时间格式
|
|
try:
|
|
dt = datetime.fromisoformat(str(ts).replace('Z', ''))
|
|
created = dt.strftime('%Y-%m-%d %H:%M:%S')
|
|
date = dt.strftime('%Y-%m-%d')
|
|
except Exception:
|
|
skipped += 1
|
|
continue
|
|
if (code, created) in existing:
|
|
skipped += 1
|
|
continue
|
|
exch, typ = ('HK', 'H') if len(code) == 5 else (('SH', 'A') if code.startswith(('6', '9')) else ('SZ', 'A'))
|
|
conn.execute("INSERT OR IGNORE INTO stocks (code, name, exchange, type, updated_at) VALUES (?,?,?,?,?)",
|
|
(code, name, exch, typ, created))
|
|
conn.execute(
|
|
"INSERT INTO price_events (code, name, event_type, price, trigger_value, event_label, created_at, date) "
|
|
"VALUES (?,?,?,?,?,?,?,?)",
|
|
(code, name, e.get('event_type', ''), e.get('price', 0),
|
|
str(e.get('trigger_value', '')), e.get('event_label', ''), created, date))
|
|
inserted += 1
|
|
|
|
conn.commit()
|
|
total = conn.execute("SELECT COUNT(*) FROM price_events").fetchone()[0]
|
|
last = conn.execute("SELECT MAX(created_at) FROM price_events").fetchone()[0]
|
|
print(f'inserted={inserted} skipped={skipped} total={total} last={last}')
|
|
conn.close() |