140 lines
4.9 KiB
Python
Executable File
140 lines
4.9 KiB
Python
Executable File
#!/usr/bin/env python3
|
||
"""UNIS HRMS 数据搬运工具(SQLite ⇄ JSON ⇄ MySQL)。
|
||
|
||
结构(表/索引)交给 Alembic,本脚本只负责数据:
|
||
|
||
# 1) 从 SQLite 导出(DATABASE_URL 指向 sqlite)
|
||
./.venv/bin/python scripts/export_sqlite.py --out data/sqlite_dump.json
|
||
|
||
# 2) 导入到 MySQL(DATABASE_URL 指向 mysql,且已 alembic upgrade head)
|
||
./.venv/bin/python scripts/export_sqlite.py --in data/sqlite_dump.json
|
||
|
||
导出按外键依赖拓扑排序,导入按同样顺序插入,因此一次性完成,无需关心父子表。
|
||
"""
|
||
from __future__ import annotations
|
||
|
||
import argparse
|
||
import json
|
||
import os
|
||
import sys
|
||
from datetime import date, datetime
|
||
from decimal import Decimal
|
||
from pathlib import Path
|
||
|
||
ROOT = Path(__file__).resolve().parents[1]
|
||
sys.path.insert(0, str(ROOT))
|
||
# 让相对路径(./data/…)与 settings 的解析基准一致,都落在项目根目录
|
||
os.chdir(ROOT)
|
||
|
||
from sqlalchemy import Date, DateTime, Numeric, Time, TypeDecorator, insert, select # noqa: E402
|
||
from sqlalchemy import types as sa_types # noqa: E402
|
||
|
||
from app.core.config import settings # noqa: E402
|
||
from app.db.base import Base # noqa: E402
|
||
from app import models # noqa: E402 注册全部映射
|
||
from app.db.session import SessionFactory, engine # noqa: E402
|
||
|
||
|
||
def _default(obj):
|
||
if isinstance(obj, Decimal):
|
||
return str(obj)
|
||
if isinstance(obj, (datetime, date)):
|
||
return obj.isoformat()
|
||
if isinstance(obj, (bytes, bytearray)):
|
||
return obj.decode("utf-8", "replace")
|
||
raise TypeError(type(obj))
|
||
|
||
|
||
def _impl(col) -> object:
|
||
"""剥掉 TypeDecorator 包装,拿到真正决定存储格式的方言类型。"""
|
||
t = col.type
|
||
for _ in range(5):
|
||
if not isinstance(t, TypeDecorator):
|
||
break
|
||
inner = getattr(t, "impl_instance", None)
|
||
if isinstance(inner, type):
|
||
inner = inner()
|
||
t = inner if inner is not None else getattr(t, "impl", None)
|
||
return t
|
||
|
||
|
||
def _coerce(table, row: dict) -> dict:
|
||
"""JSON 里的 ISO 字符串/十进制字符串还原成驱动需要的 Python 对象。"""
|
||
out = {}
|
||
for key, value in row.items():
|
||
if key not in table.c or value is None:
|
||
out[key] = value
|
||
continue
|
||
t = _impl(table.c[key])
|
||
if isinstance(value, str):
|
||
if isinstance(t, DateTime):
|
||
value = datetime.fromisoformat(value)
|
||
elif isinstance(t, Date):
|
||
value = date.fromisoformat(value)
|
||
elif isinstance(t, Time):
|
||
value = datetime.fromisoformat(f"1970-01-01T{value}").time()
|
||
elif isinstance(t, Numeric):
|
||
value = Decimal(value)
|
||
elif isinstance(t, sa_types.LargeBinary):
|
||
value = value.encode("utf-8")
|
||
elif isinstance(value, (int, float)) and isinstance(t, Numeric):
|
||
value = Decimal(str(value))
|
||
out[key] = value
|
||
return out
|
||
|
||
|
||
def _tables() -> list:
|
||
return list(reversed(Base.metadata.sorted_tables)) # 子表在前
|
||
|
||
|
||
async def dump(out: Path) -> None:
|
||
payload: dict[str, list[dict]] = {}
|
||
async with SessionFactory() as session:
|
||
for table in _tables():
|
||
rows = (await session.execute(select(table))).mappings().all()
|
||
payload[table.name] = [dict(r) for r in rows]
|
||
print(f" {table.name:32s} {len(rows):6d}")
|
||
out.parent.mkdir(parents=True, exist_ok=True)
|
||
out.write_text(json.dumps(payload, ensure_ascii=False, default=_default), encoding="utf-8")
|
||
print(f"已导出 {sum(len(v) for v in payload.values())} 行 → {out}")
|
||
|
||
|
||
async def load(src: Path, truncate: bool) -> None:
|
||
payload = json.loads(src.read_text(encoding="utf-8"))
|
||
async with engine.begin() as conn: # 关闭外键检查(MySQL: SET FOREIGN_KEY_CHECKS)
|
||
await conn.exec_driver_sql(
|
||
"PRAGMA foreign_keys=OFF" if settings.is_sqlite else "SET FOREIGN_KEY_CHECKS=0"
|
||
)
|
||
total = 0
|
||
for table in reversed(_tables()): # 父表在前
|
||
rows = payload.get(table.name) or []
|
||
if not rows:
|
||
continue
|
||
if truncate:
|
||
await conn.execute(table.delete())
|
||
await conn.execute(insert(table), [_coerce(table, r) for r in rows])
|
||
total += len(rows)
|
||
print(f" {table.name:32s} {len(rows):6d}")
|
||
await engine.dispose()
|
||
print(f"已导入 {total} 行 ← {src}")
|
||
|
||
|
||
def main() -> None:
|
||
ap = argparse.ArgumentParser(description=__doc__)
|
||
ap.add_argument("--out", help="导出到 JSON 文件")
|
||
ap.add_argument("--in", dest="inp", help="从 JSON 文件导入当前 DATABASE_URL")
|
||
ap.add_argument("--no-truncate", action="store_true", help="导入时不清空目标表")
|
||
args = ap.parse_args()
|
||
import asyncio
|
||
|
||
if args.out:
|
||
asyncio.run(dump(Path(args.out)))
|
||
elif args.inp:
|
||
asyncio.run(load(Path(args.inp), truncate=not args.no_truncate))
|
||
else:
|
||
ap.print_help()
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|