44 lines
1.6 KiB
Python
44 lines
1.6 KiB
Python
"""检查测试库数据范围和 settle_type 分布"""
|
||
import os
|
||
from dotenv import load_dotenv
|
||
load_dotenv()
|
||
|
||
dsn = os.environ.get("TEST_DB_DSN")
|
||
if not dsn:
|
||
raise RuntimeError("TEST_DB_DSN 未配置")
|
||
|
||
import psycopg2
|
||
conn = psycopg2.connect(dsn)
|
||
cur = conn.cursor()
|
||
|
||
queries = [
|
||
("pay_time 范围（全表）",
|
||
"SELECT MIN(pay_time)::date, MAX(pay_time)::date, COUNT(*) FROM dwd.dwd_settlement_head"),
|
||
("pay_time 范围（有会员手机号）",
|
||
"SELECT MIN(pay_time)::date, MAX(pay_time)::date, COUNT(*) FROM dwd.dwd_settlement_head WHERE member_phone IS NOT NULL AND member_phone != ''"),
|
||
("settle_type 分布",
|
||
"SELECT settle_type, COUNT(*) FROM dwd.dwd_settlement_head GROUP BY settle_type ORDER BY settle_type"),
|
||
("2025-12-09 之后的记录数（不限 settle_type）",
|
||
"SELECT COUNT(*) FROM dwd.dwd_settlement_head WHERE pay_time >= '2025-12-09'"),
|
||
("2025-12-09 之后 settle_type 分布",
|
||
"SELECT settle_type, COUNT(*) FROM dwd.dwd_settlement_head WHERE pay_time >= '2025-12-09' GROUP BY settle_type ORDER BY settle_type"),
|
||
("2025-12-09 之后有会员手机号的记录",
|
||
"SELECT settle_type, COUNT(*) FROM dwd.dwd_settlement_head WHERE pay_time >= '2025-12-09' AND member_phone IS NOT NULL AND member_phone != '' GROUP BY settle_type ORDER BY settle_type"),
|
||
]
|
||
|
||
for title, sql in queries:
|
||
print(f"\n{'='*60}")
|
||
print(title)
|
||
print('-'*60)
|
||
cur.execute(sql)
|
||
rows = cur.fetchall()
|
||
cols = [d[0] for d in cur.description]
|
||
print(f" {' '.join(cols)}")
|
||
for r in rows:
|
||
print(f" {' '.join(str(v) for v in r)}")
|
||
if not rows:
|
||
print(" (无数据)")
|
||
|
||
cur.close()
|
||
conn.close()
|