Spaces:
Sleeping
Sleeping
Download backend/v2/migrate_sqlite_to_postgres.py from shaun07/grid-backtest: direct link, hf CLI and curl.
- Browser
- Download file 9.94 kB
-
https://huggingface.co/spaces/shaun07/grid-backtest/resolve/main/backend/v2/migrate_sqlite_to_postgres.py
- Command line
-
hf download hf://spaces/shaun07/grid-backtest/backend/v2/migrate_sqlite_to_postgres.py
-
curl -L -o migrate_sqlite_to_postgres.py https://huggingface.co/spaces/shaun07/grid-backtest/resolve/main/backend/v2/migrate_sqlite_to_postgres.py
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()) | |