shop-java/tools/backup_tables.py

# -*- coding: utf-8 -*-
"""备份/查看与本批写接口相关的表（store_role / store_user / address / page / 两张关联表）。"""
import json
import os
import re

import pymysql
from _root import ROOT

YML = str(ROOT / "yoshop-java/src/main/resources/application.yml")
text = open(YML, encoding="utf-8").read()
pwd = re.findall(r"password:\s*(.+)", text)[0].strip().strip('"')

conn = pymysql.connect(host="192.168.1.177", port=3306, user="root", password=pwd,
                       database="shop", charset="utf8mb4")
cur = conn.cursor()

os.makedirs("backup", exist_ok=True)
TABLES = ["yoshop_store_role", "yoshop_store_user", "yoshop_store_address",
          "yoshop_page", "yoshop_store_role_menu", "yoshop_store_user_role"]
for t in TABLES:
    cur.execute("select * from " + t)
    cols = [d[0] for d in cur.description]
    rows = [dict(zip(cols, r)) for r in cur.fetchall()]
    json.dump(rows, open("backup/%s.json" % t, "w", encoding="utf-8"), ensure_ascii=False, default=str)
    print(t, len(rows))

cur.execute("select store_user_id,user_name,real_name,is_super from yoshop_store_user")
print("用户:", cur.fetchall())
cur.execute("select role_id,role_name,parent_id from yoshop_store_role")
print("角色:", cur.fetchall())
cur.execute("select address_id,type,name from yoshop_store_address")
print("地址:", cur.fetchall())
cur.execute("select page_id,page_type,page_name,is_delete from yoshop_page")
print("页面:", cur.fetchall())
conn.close()