shop-java/tools/dump_home_schema.py

# -*- coding: utf-8 -*-
"""确认首页统计涉及的表字段"""
import re
import pymysql
from pathlib import Path
from _root import ROOT

yml = Path(str(ROOT / "yoshop-java/src/main/resources/application.yml"))
pwd = re.findall(r"password:\s*(.+)", yml.read_text(encoding="utf-8"))[0].strip().strip('"\'')
conn = pymysql.connect(host="192.168.1.177", port=3306, user="root", password=pwd, database="shop")
cur = conn.cursor()

for t in ("yoshop_order", "yoshop_user", "yoshop_order_refund"):
    cur.execute(
        "SELECT column_name, column_type FROM information_schema.columns "
        "WHERE table_schema='shop' AND table_name=%s ORDER BY ordinal_position", (t,))
    cols = cur.fetchall()
    print("=" * 15, t, f"({len(cols)} 字段)")
    print("  ", ", ".join(c for c, _ in cols))

    # 行数与状态分布，便于校验统计口径
    if t == "yoshop_order":
        cur.execute("SELECT COUNT(*), SUM(is_delete=0) FROM yoshop_order")
        print("   行数/未删除:", cur.fetchone())
        cur.execute("SELECT pay_status, order_status, COUNT(*) FROM yoshop_order WHERE is_delete=0 GROUP BY pay_status, order_status")
        print("   pay_status×order_status:", cur.fetchall())
    if t == "yoshop_user":
        cur.execute("SELECT COUNT(*), SUM(is_delete=0), SUM(pay_money>0) FROM yoshop_user")
        print("   行数/未删除/已消费:", cur.fetchone())
    if t == "yoshop_order_refund":
        cur.execute("SELECT status, COUNT(*) FROM yoshop_order_refund GROUP BY status")
        print("   status 分布:", cur.fetchall())

conn.close()