Repository navigation
Expand file tree
/
Copy pathusage_db.py
More file actions
334 lines (278 loc) · 14.3 KB
/
Copy pathusage_db.py
File metadata and controls
334 lines (278 loc) · 14.3 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
# -*- coding: utf-8 -*-
"""
本地用量库(SQLite)· 唯一数据存储:明细入库 + 按分钟聚合供前端读取。
- 所有取数结果只写本库;不再维护第二份 Excel(历史版本双写会让两份数据各自漂移、难以对账)。
- 需要表格时用 export_xlsx() 从库里现生成一份下载文件,它是"报表"而不是"存储"。
- 老安装如果只有 Excel、库还是空的,import_excel_if_needed() 会做一次性迁移把它灌进库;
迁移之后 Excel 就不再参与运行(不做 mtime 比对、不重复导入)。
- 多账号:每条记录带 account_key(身份哈希)+ account_label(身份原文),主键是
(account_key, id)——记录 ID 只在单个账号内唯一,跨账号必须带账号才能不互相覆盖。
"""
import sqlite3
from datetime import datetime
from pathlib import Path
import accounts # 身份 → account_key 的换算只在 accounts 一处定义,库与爬虫口径一致
DB_PATH = Path(__file__).parent / "opencode_usage.db"
SCHEMA = """
CREATE TABLE IF NOT EXISTS usage_records (
id TEXT NOT NULL,
account_key TEXT NOT NULL DEFAULT '',
started_at TEXT NOT NULL,
model TEXT,
provider TEXT,
input_tokens INTEGER DEFAULT 0,
cache_read_tokens INTEGER DEFAULT 0,
cache_write_tokens INTEGER DEFAULT 0,
output_tokens INTEGER DEFAULT 0,
reasoning_tokens INTEGER DEFAULT 0,
cost REAL DEFAULT 0,
session_id TEXT,
api_key_id TEXT,
product TEXT,
outcome TEXT,
duration_ms INTEGER DEFAULT 0,
account_label TEXT NOT NULL DEFAULT '',
PRIMARY KEY (account_key, id)
);
CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT);
"""
# 索引一律在 migrate 之后建:旧表还没有 account_key 列,建在 SCHEMA 里会直接报错
INDEXES = [
"CREATE INDEX IF NOT EXISTS idx_usage_started ON usage_records(started_at)",
"CREATE INDEX IF NOT EXISTS idx_usage_model ON usage_records(model)",
"CREATE INDEX IF NOT EXISTS idx_usage_account ON usage_records(account_key, started_at)",
]
# 前端展示用的列名,与导出报表的表头一致(前端与 export_xlsx 都按这些名字取列)
AGG_SQL = """
SELECT strftime('%Y-%m-%d %H:%M', started_at) AS "日期时间",
COALESCE(account_label, '') AS "账号",
COALESCE(model, '') AS "模型",
COUNT(*) AS "请求数 Requests",
SUM(input_tokens) AS "输入 Input",
SUM(cache_read_tokens) AS "缓存读取 Cache Read",
SUM(output_tokens) AS "输出 Output",
SUM(reasoning_tokens) AS "推理 Reasoning",
SUM(cost) AS "成本($)"
FROM usage_records
GROUP BY "日期时间", "账号", "模型"
ORDER BY "日期时间", "账号", "模型"
"""
COLUMNS = ("id", "account_key", "started_at", "model", "provider", "input_tokens",
"cache_read_tokens", "cache_write_tokens", "output_tokens", "reasoning_tokens",
"cost", "session_id", "api_key_id", "product", "outcome", "duration_ms",
"account_label")
def connect() -> sqlite3.Connection:
conn = sqlite3.connect(DB_PATH, timeout=30)
conn.row_factory = sqlite3.Row
return conn
def init(conn: sqlite3.Connection, legacy_identity: str = "") -> None:
"""建表 + 老库迁移。legacy_identity 是旧版单账号的身份(注册表里的邮箱),用于给无账号的历史数据回填归属。"""
# WAL:爬虫写库的同时前端仍可读,不会互相阻塞
conn.execute("PRAGMA journal_mode=WAL")
conn.executescript(SCHEMA)
migrate(conn, legacy_identity)
for ddl in INDEXES:
conn.execute(ddl)
conn.commit()
def _has_column(conn: sqlite3.Connection, column: str) -> bool:
return any(r["name"] == column for r in conn.execute("PRAGMA table_info(usage_records)"))
def migrate(conn: sqlite3.Connection, legacy_identity: str = "") -> bool:
"""旧表(单账号、主键只有 id)→ 新表(账号维度 + 复合主键)。
SQLite 不能改主键,只能重建表;重建时把旧行的归属写成 legacy 身份,
这样历史 6 万多条记录和升级后第一个账号的新数据落在同一个桶里,不会重复计数。
"""
if _has_column(conn, "account_key"):
return False
existing = {r["name"] for r in conn.execute("PRAGMA table_info(usage_records)")}
cols = ", ".join(COLUMNS)
key, label = accounts.identity_key(legacy_identity), (legacy_identity or "").strip()
conn.execute(f"""
CREATE TABLE usage_records_new (
id TEXT NOT NULL,
account_key TEXT NOT NULL DEFAULT '',
started_at TEXT NOT NULL,
model TEXT,
provider TEXT,
input_tokens INTEGER DEFAULT 0,
cache_read_tokens INTEGER DEFAULT 0,
cache_write_tokens INTEGER DEFAULT 0,
output_tokens INTEGER DEFAULT 0,
reasoning_tokens INTEGER DEFAULT 0,
cost REAL DEFAULT 0,
session_id TEXT,
api_key_id TEXT,
product TEXT,
outcome TEXT,
duration_ms INTEGER DEFAULT 0,
account_label TEXT NOT NULL DEFAULT '',
PRIMARY KEY (account_key, id)
)
""")
# 旧表没有的列用默认值补齐;带 账号 列的旧 Excel 再导入时自带身份
select_parts = []
for c in COLUMNS:
if c == "account_key":
select_parts.append(f"'{key}'")
elif c == "account_label":
select_parts.append("'" + label.replace("'", "''") + "'")
elif c in existing:
select_parts.append(c)
else:
select_parts.append("''")
conn.execute(f"INSERT OR IGNORE INTO usage_records_new ({cols}) SELECT {', '.join(select_parts)} FROM usage_records")
conn.execute("DROP TABLE usage_records")
conn.execute("ALTER TABLE usage_records_new RENAME TO usage_records")
conn.commit()
return True
def tag_orphan_rows(conn: sqlite3.Connection, identity: str) -> int:
"""把无归属(account_key 为空)的历史行归给某个账号——注册表只有一个账号时归属是唯一确定的。"""
key = accounts.identity_key(identity)
if not key:
return 0
cur = conn.execute("UPDATE usage_records SET account_key=?, account_label=? "
"WHERE account_key='' OR account_key IS NULL", (key, identity.strip()))
conn.commit()
return cur.rowcount
def _num(v) -> float:
try:
return float(v)
except (TypeError, ValueError):
return 0.0
def _int(v) -> int:
try:
return int(float(v))
except (TypeError, ValueError):
return 0
def row_to_record(row: dict, default_identity: str = "") -> tuple:
"""统一行口径(报表表头命名的字典)→ 数据库行。缺列按 0/空处理,兼容新旧两种粒度。
历史迁移文件没有"账号"列,用 default_identity(注册表里的当前身份)兜底,
避免整表重导时把已有账号的数据刷成未知账号。
"""
started = str(row.get("日期时间") or "").strip()
rid = str(row.get("记录ID") or "").strip()
identity = str(row.get("账号") or default_identity or "").strip()
if not rid:
rid = "|".join([started, identity, str(row.get("模型") or ""),
str(row.get("提供方") or ""), str(row.get("成本($)") or "")])
return (
rid, accounts.identity_key(identity), started,
str(row.get("模型") or ""), str(row.get("提供方") or ""),
_int(row.get("输入 Input")), _int(row.get("缓存读取 Cache Read")),
_int(row.get("缓存写入 Cache Write")), _int(row.get("输出 Output")),
_int(row.get("推理 Reasoning")), _num(row.get("成本($)")),
str(row.get("Session") or ""), str(row.get("Key ID") or ""),
str(row.get("计划") or ""), str(row.get("状态") or ""),
_int(row.get("耗时(ms)")), identity,
)
UPSERT_SQL = """
INSERT OR REPLACE INTO usage_records
(id, account_key, started_at, model, provider, input_tokens, cache_read_tokens, cache_write_tokens,
output_tokens, reasoning_tokens, cost, session_id, api_key_id, product, outcome, duration_ms, account_label)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
def existing_ids(conn: sqlite3.Connection, account_key: str, ids: list) -> set:
"""返回其中已经在库里出现的记录 ID。
用途是把"重复覆盖写"和"真新增"分开报:started_at 只存到秒,增量起点因此会带上
边界那一秒里已存过的记录,每次都重新拉到、重新覆盖——数据无害,但日志不能谎报新增。
"""
ids = [str(i) for i in ids if i]
if not ids:
return set()
have = set()
for i in range(0, len(ids), 500):
chunk = ids[i:i + 500]
q = ("SELECT id FROM usage_records WHERE account_key=? AND id IN ("
+ ",".join("?" * len(chunk)) + ")")
have.update(r["id"] for r in conn.execute(q, [account_key] + chunk))
return have
def upsert_rows(conn: sqlite3.Connection, rows: list, default_identity: str = "") -> int:
"""按统一行口径批量写入(按 账号+记录ID 覆盖,只增不删)。返回写入条数。"""
batch = [row_to_record(r, default_identity) for r in rows if r]
batch = [b for b in batch if b[2]]
if not batch:
return 0
for i in range(0, len(batch), 2000):
conn.executemany(UPSERT_SQL, batch[i:i + 2000])
conn.commit()
return len(batch)
def read_excel_rows(path: Path) -> list:
"""只读遍历 Excel,返回行字典列表(表头→值)。"""
from openpyxl import load_workbook
wb = load_workbook(path, read_only=True)
try:
ws = wb.active
it = ws.iter_rows(values_only=True)
header = [str(h) for h in next(it, ())]
if not header:
return []
rows = []
for r in it:
if not r or not r[0]:
continue
rows.append({header[i]: r[i] for i in range(min(len(header), len(r)))})
return rows
finally:
wb.close()
def get_meta(conn: sqlite3.Connection, key: str) -> str:
cur = conn.execute("SELECT value FROM meta WHERE key=?", (key,))
row = cur.fetchone()
return row["value"] if row else ""
def set_meta(conn: sqlite3.Connection, key: str, value: str) -> None:
conn.execute("INSERT OR REPLACE INTO meta (key, value) VALUES (?,?)", (key, str(value)))
conn.commit()
def count_records(conn: sqlite3.Connection) -> int:
return conn.execute("SELECT COUNT(*) AS n FROM usage_records").fetchone()["n"]
def models(conn: sqlite3.Connection) -> list:
return [r["model"] for r in conn.execute(
"SELECT DISTINCT model FROM usage_records WHERE model IS NOT NULL AND model <> '' ORDER BY model")]
def account_labels(conn: sqlite3.Connection) -> list:
return [r["account_label"] for r in conn.execute(
"SELECT DISTINCT account_label FROM usage_records "
"WHERE account_label IS NOT NULL AND account_label <> '' ORDER BY account_label")]
def agg_rows(conn: sqlite3.Connection) -> list:
"""按分钟×账号×模型聚合,列名即报表表头,前端按这些名字取列。"""
return [dict(r) for r in conn.execute(AGG_SQL)]
def latest_started(conn: sqlite3.Connection, account_key: str) -> str:
"""该账号在库里最新一条记录的时间——增量抓取的起点就取这里,不再回读任何表格文件。"""
row = conn.execute("SELECT MAX(started_at) AS t FROM usage_records WHERE account_key=?",
(account_key,)).fetchone()
return (row["t"] or "") if row and row["t"] else ""
# 导出报表的表头与旧版 Excel 完全一致,用户拿到的文件形状不变
EXPORT_HEADERS = ["日期时间", "月份", "账号", "模型", "提供方", "输入 Input", "缓存读取 Cache Read", "输入合计",
"输出 Output", "推理 Reasoning", "缓存写入 Cache Write", "输出合计", "状态", "耗时(ms)",
"成本($)", "成本显示", "Session", "Key ID", "计划", "记录ID"]
EXPORT_SQL = """SELECT started_at, account_label, model, provider, input_tokens, cache_read_tokens,
cache_write_tokens, output_tokens, reasoning_tokens, cost, outcome, duration_ms,
session_id, api_key_id, product, id
FROM usage_records ORDER BY started_at DESC, account_label, model"""
def export_xlsx(conn: sqlite3.Connection, path: Path) -> int:
"""从库里现生成一份 xlsx。它是按需产出的报表,不是第二份存储。"""
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet("Usage History")
ws.append(EXPORT_HEADERS)
n = 0
for r in conn.execute(EXPORT_SQL):
dt = r["started_at"] or ""
inp, cache = r["input_tokens"] or 0, r["cache_read_tokens"] or 0
out, reason = r["output_tokens"] or 0, r["reasoning_tokens"] or 0
cost = float(r["cost"] or 0)
ws.append([dt, dt[:7], r["account_label"] or "", r["model"] or "", r["provider"] or "",
inp, cache, inp + cache, out, reason, r["cache_write_tokens"] or 0, out + reason,
r["outcome"] or "", r["duration_ms"] or 0, cost, f"USD (${cost:.4f})",
r["session_id"] or "", r["api_key_id"] or "", r["product"] or "", r["id"]])
n += 1
wb.save(str(path))
return n
def import_excel_if_needed(conn: sqlite3.Connection, xlsx: Path, default_identity: str = "") -> str:
"""一次性迁移:老安装只有 Excel、库还是空的 → 把它灌进库。
库里有数据后就不再读 Excel:它已经不是存储,重复导入只会和新入库的记录打架。
"""
if not xlsx.exists():
return "无历史 Excel,跳过迁移"
if count_records(conn) > 0:
return "库已有数据,无需迁移"
rows = read_excel_rows(xlsx)
n = upsert_rows(conn, rows, default_identity)
set_meta(conn, "excel_migrated_at", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
return f"已从历史 Excel 迁移 {n} 行"