grid-backtest / backend /v2 /migrate_sqlite_to_postgres.py
shaun07's picture
Upload folder using huggingface_hub
61ca2b1 verified
Raw History Blame Contribute Delete
9.94 kB
from __future__ import annotations
import argparse
import json
import os
import sqlite3
from datetime import datetime
from typing import Any
from .db import init_db, session_scope
from .models import BacktestRecord, Task
def _parse_dt(value: Any) -> datetime | None:
if value is None:
return None
if isinstance(value, datetime):
return value
if isinstance(value, (int, float)):
try:
return datetime.utcfromtimestamp(float(value))
except Exception:
return None
s = str(value).strip()
if not s:
return None
s = s.replace('Z', '+00:00')
try:
return datetime.fromisoformat(s)
except Exception:
return None
def _parse_json(value: Any) -> Any:
if value is None:
return None
if isinstance(value, (dict, list)):
return value
if isinstance(value, (bytes, bytearray)):
try:
value = value.decode('utf-8', errors='replace')
except Exception:
return None
if isinstance(value, str):
s = value.strip()
if not s:
return None
try:
return json.loads(s)
except Exception:
return None
return None
def _sqlite_table_exists(conn: sqlite3.Connection, name: str) -> bool:
cur = conn.execute(
"SELECT 1 FROM sqlite_master WHERE type='table' AND name=? LIMIT 1",
(name,),
)
return cur.fetchone() is not None
def _sqlite_fetch_all(conn: sqlite3.Connection, table: str) -> list[dict[str, Any]]:
conn.row_factory = sqlite3.Row
cur = conn.execute(f'SELECT * FROM {table}')
return [dict(r) for r in cur.fetchall()]
def migrate_v2_sqlite(sqlite_path: str, *, overwrite: bool) -> dict[str, int]:
stats = {
'tasks_total': 0,
'tasks_migrated': 0,
'tasks_skipped': 0,
'records_total': 0,
'records_migrated': 0,
'records_skipped': 0,
}
if not os.path.exists(sqlite_path):
return stats
conn = sqlite3.connect(sqlite_path)
try:
if _sqlite_table_exists(conn, 'tasks'):
rows = _sqlite_fetch_all(conn, 'tasks')
stats['tasks_total'] = len(rows)
for r in rows:
task_id = str(r.get('id') or '').strip()
if not task_id:
continue
payload = {
'id': task_id,
'user_id': (str(r.get('user_id')) if r.get('user_id') is not None else None),
'type': str(r.get('type') or ''),
'status': str(r.get('status') or ''),
'progress': int(r.get('progress') or 0),
'stage': (str(r.get('stage')) if r.get('stage') is not None else None),
'message': (str(r.get('message')) if r.get('message') is not None else None),
'params_json': _parse_json(r.get('params_json')),
'result_json': _parse_json(r.get('result_json')),
'file_path': (str(r.get('file_path')) if r.get('file_path') is not None else None),
'error': (str(r.get('error')) if r.get('error') is not None else None),
'created_at': _parse_dt(r.get('created_at')) or datetime.utcnow(),
'started_at': _parse_dt(r.get('started_at')),
'finished_at': _parse_dt(r.get('finished_at')),
}
with session_scope() as s:
existing = s.get(Task, task_id)
if existing is not None and not overwrite:
stats['tasks_skipped'] += 1
continue
if existing is None:
s.add(Task(**payload))
else:
for k, v in payload.items():
if k == 'id':
continue
setattr(existing, k, v)
stats['tasks_migrated'] += 1
if _sqlite_table_exists(conn, 'backtest_records_v2'):
rows = _sqlite_fetch_all(conn, 'backtest_records_v2')
stats['records_total'] = len(rows)
for r in rows:
rid = str(r.get('id') or '').strip()
if not rid:
continue
parameters = _parse_json(r.get('parameters')) or {}
results = _parse_json(r.get('results'))
payload = {
'id': rid,
'user_id': (str(r.get('user_id')) if r.get('user_id') is not None else None),
'strategy_type': str(r.get('strategy_type') or ''),
'symbol': str(r.get('symbol') or ''),
'start_date': (str(r.get('start_date')) if r.get('start_date') is not None else None),
'end_date': (str(r.get('end_date')) if r.get('end_date') is not None else None),
'parameters': parameters,
'results': results,
'created_at': _parse_dt(r.get('created_at')) or datetime.utcnow(),
}
with session_scope() as s:
existing = s.get(BacktestRecord, rid)
if existing is not None and not overwrite:
stats['records_skipped'] += 1
continue
if existing is None:
s.add(BacktestRecord(**payload))
else:
for k, v in payload.items():
if k == 'id':
continue
setattr(existing, k, v)
stats['records_migrated'] += 1
return stats
finally:
conn.close()
def migrate_legacy_sqlite(sqlite_path: str, *, overwrite: bool) -> dict[str, int]:
stats = {
'legacy_total': 0,
'legacy_migrated': 0,
'legacy_skipped': 0,
}
if not os.path.exists(sqlite_path):
return stats
conn = sqlite3.connect(sqlite_path)
try:
if not _sqlite_table_exists(conn, 'backtest_records'):
return stats
rows = _sqlite_fetch_all(conn, 'backtest_records')
stats['legacy_total'] = len(rows)
for r in rows:
rid = str(r.get('id') or '').strip()
if not rid:
continue
strategy_type = str(r.get('strategy_type') or '').strip()
symbol = str(r.get('symbol') or '').strip()
parameters = _parse_json(r.get('parameters')) or {}
results = _parse_json(r.get('results'))
initial_capital = r.get('initial_capital')
if initial_capital is not None and 'initial_capital' not in parameters:
try:
parameters['initial_capital'] = float(initial_capital)
except Exception:
pass
payload = {
'id': rid,
'user_id': None,
'strategy_type': strategy_type,
'symbol': symbol,
'start_date': (str(r.get('start_date')) if r.get('start_date') is not None else None),
'end_date': (str(r.get('end_date')) if r.get('end_date') is not None else None),
'parameters': parameters,
'results': results,
'created_at': _parse_dt(r.get('created_at')) or datetime.utcnow(),
}
with session_scope() as s:
existing = s.get(BacktestRecord, rid)
if existing is not None and not overwrite:
stats['legacy_skipped'] += 1
continue
if existing is None:
s.add(BacktestRecord(**payload))
else:
for k, v in payload.items():
if k == 'id':
continue
setattr(existing, k, v)
stats['legacy_migrated'] += 1
return stats
finally:
conn.close()
def _default_backend_data_file(name: str) -> str:
backend_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
return os.path.join(backend_dir, 'data', name)
def main() -> int:
parser = argparse.ArgumentParser(description='Migrate WGJY SQLite databases to PostgreSQL (Postgres-only).')
parser.add_argument('--v2-sqlite', default=_default_backend_data_file('wgjy_v2.db'))
parser.add_argument('--legacy-sqlite', default=_default_backend_data_file('backtest.db'))
parser.add_argument('--overwrite', action='store_true', help='Overwrite existing rows in PostgreSQL')
args = parser.parse_args()
init_db()
v2_stats = migrate_v2_sqlite(args.v2_sqlite, overwrite=args.overwrite)
legacy_stats = migrate_legacy_sqlite(args.legacy_sqlite, overwrite=args.overwrite)
print('=== Migration summary ===')
print(f"v2 sqlite: {args.v2_sqlite}")
print(f" tasks: {v2_stats['tasks_migrated']}/{v2_stats['tasks_total']} migrated, {v2_stats['tasks_skipped']} skipped")
print(
f" backtest_records_v2: {v2_stats['records_migrated']}/{v2_stats['records_total']} migrated, {v2_stats['records_skipped']} skipped"
)
print(f"legacy sqlite: {args.legacy_sqlite}")
print(
f" backtest_records: {legacy_stats['legacy_migrated']}/{legacy_stats['legacy_total']} migrated, {legacy_stats['legacy_skipped']} skipped"
)
print('=========================')
return 0
if __name__ == '__main__':
raise SystemExit(main())