shop-java/tools/check_isdelete.py

# -*- coding: utf-8 -*-
"""统计各表 is_delete 取值分布"""
import re
import pymysql
from _root import ROOT

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

conn = pymysql.connect(host="192.168.1.177", port=3306, user="root", password=pwd, database="shop")
cur = conn.cursor()
cur.execute("""SELECT table_name FROM information_schema.columns
               WHERE table_schema='shop' AND column_name='is_delete' ORDER BY table_name""")
for (t,) in cur.fetchall():
    cur.execute(f"SELECT is_delete, COUNT(*) FROM {t} GROUP BY is_delete")
    print(t, cur.fetchall())
conn.close()