203 lines
7.5 KiB
Python
203 lines
7.5 KiB
Python
"""
|
||
检查数据库中的模板记录情况
|
||
"""
|
||
import os
|
||
import pymysql
|
||
from pathlib import Path
|
||
from dotenv import load_dotenv
|
||
|
||
# 加载环境变量
|
||
load_dotenv()
|
||
|
||
# 数据库配置
|
||
DB_CONFIG = {
|
||
'host': os.getenv('DB_HOST', '152.136.177.240'),
|
||
'port': int(os.getenv('DB_PORT', 5012)),
|
||
'user': os.getenv('DB_USER', 'finyx'),
|
||
'password': os.getenv('DB_PASSWORD', '6QsGK6MpePZDE57Z'),
|
||
'database': os.getenv('DB_NAME', 'finyx'),
|
||
'charset': 'utf8mb4'
|
||
}
|
||
|
||
# 先检查数据库中的实际 tenant_id
|
||
TENANT_ID = 615873064429507639 # 默认值，会在检查时自动发现实际的 tenant_id
|
||
|
||
|
||
def print_section(title):
|
||
"""打印章节标题"""
|
||
print("\n" + "="*70)
|
||
print(f" {title}")
|
||
print("="*70)
|
||
|
||
|
||
def check_database():
|
||
"""检查数据库记录"""
|
||
print_section("数据库模板记录检查")
|
||
|
||
try:
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
||
|
||
# 0. 先检查所有 tenant_id，确定实际使用的 tenant_id
|
||
print_section("0. 检查所有不同的 tenant_id")
|
||
cursor.execute("SELECT DISTINCT tenant_id, COUNT(*) as count FROM f_polic_file_config GROUP BY tenant_id")
|
||
tenant_ids = cursor.fetchall()
|
||
actual_tenant_id = None
|
||
for row in tenant_ids:
|
||
print(f" tenant_id={row['tenant_id']}: {row['count']} 条记录")
|
||
if actual_tenant_id is None:
|
||
actual_tenant_id = row['tenant_id']
|
||
|
||
# 使用实际的 tenant_id
|
||
if actual_tenant_id:
|
||
print(f"\n [使用] tenant_id={actual_tenant_id} 进行后续检查")
|
||
tenant_id = actual_tenant_id
|
||
else:
|
||
tenant_id = TENANT_ID
|
||
print(f"\n [使用] 默认 tenant_id={tenant_id}")
|
||
|
||
# 1. 检查 f_polic_file_config 表的所有记录（不限制条件）
|
||
print_section("1. 检查 f_polic_file_config 表（所有记录）")
|
||
cursor.execute("SELECT COUNT(*) as count FROM f_polic_file_config")
|
||
total_count = cursor.fetchone()['count']
|
||
print(f" 总记录数: {total_count}")
|
||
|
||
# 2. 检查按 tenant_id 过滤
|
||
print_section("2. 检查 f_polic_file_config 表（按 tenant_id 过滤）")
|
||
cursor.execute("SELECT COUNT(*) as count FROM f_polic_file_config WHERE tenant_id = %s", (tenant_id,))
|
||
tenant_count = cursor.fetchone()['count']
|
||
print(f" tenant_id={tenant_id} 的记录数: {tenant_count}")
|
||
|
||
# 3. 检查有 file_path 的记录
|
||
print_section("3. 检查 f_polic_file_config 表（有 file_path 的记录）")
|
||
cursor.execute("""
|
||
SELECT COUNT(*) as count
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s
|
||
AND file_path IS NOT NULL
|
||
AND file_path != ''
|
||
""", (tenant_id,))
|
||
path_count = cursor.fetchone()['count']
|
||
print(f" 有 file_path 的记录数: {path_count}")
|
||
|
||
# 4. 检查不同状态的记录
|
||
print_section("4. 检查 f_polic_file_config 表（按 state 分组）")
|
||
cursor.execute("""
|
||
SELECT state, COUNT(*) as count
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s
|
||
GROUP BY state
|
||
""", (tenant_id,))
|
||
state_counts = cursor.fetchall()
|
||
for row in state_counts:
|
||
state_name = "已启用" if row['state'] == 1 else "已禁用"
|
||
print(f" state={row['state']} ({state_name}): {row['count']} 条")
|
||
|
||
# 5. 查看前10条记录示例
|
||
print_section("5. f_polic_file_config 表记录示例（前10条）")
|
||
cursor.execute("""
|
||
SELECT id, name, file_path, state, tenant_id, parent_id
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s
|
||
LIMIT 10
|
||
""", (tenant_id,))
|
||
samples = cursor.fetchall()
|
||
if samples:
|
||
for i, row in enumerate(samples, 1):
|
||
print(f"\n 记录 {i}:")
|
||
print(f" ID: {row['id']}")
|
||
print(f" 名称: {row['name']}")
|
||
print(f" 路径: {row['file_path']}")
|
||
print(f" 状态: {row['state']} ({'已启用' if row['state'] == 1 else '已禁用'})")
|
||
print(f" 租户ID: {row['tenant_id']}")
|
||
print(f" 父级ID: {row['parent_id']}")
|
||
else:
|
||
print(" 没有找到记录")
|
||
|
||
# 7. 检查 file_path 的类型分布
|
||
print_section("7. 检查 file_path 路径类型分布")
|
||
cursor.execute("""
|
||
SELECT
|
||
CASE
|
||
WHEN file_path LIKE 'template_finish/%%' THEN '本地路径'
|
||
WHEN file_path LIKE '/%%TEMPLATE/%%' THEN 'MinIO路径'
|
||
WHEN file_path IS NULL OR file_path = '' THEN '空路径'
|
||
ELSE '其他路径'
|
||
END as path_type,
|
||
COUNT(*) as count
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s
|
||
GROUP BY path_type
|
||
""", (tenant_id,))
|
||
path_types = cursor.fetchall()
|
||
for row in path_types:
|
||
print(f" {row['path_type']}: {row['count']} 条")
|
||
|
||
# 8. 检查 f_polic_file_field 关联表
|
||
print_section("8. 检查 f_polic_file_field 关联表")
|
||
cursor.execute("""
|
||
SELECT COUNT(*) as count
|
||
FROM f_polic_file_field
|
||
WHERE tenant_id = %s
|
||
""", (tenant_id,))
|
||
relation_count = cursor.fetchone()['count']
|
||
print(f" 关联记录数: {relation_count}")
|
||
|
||
# 9. 检查 f_polic_field 字段表
|
||
print_section("9. 检查 f_polic_field 字段表")
|
||
cursor.execute("""
|
||
SELECT
|
||
field_type,
|
||
CASE
|
||
WHEN field_type = 1 THEN '输入字段'
|
||
WHEN field_type = 2 THEN '输出字段'
|
||
ELSE '未知'
|
||
END as type_name,
|
||
COUNT(*) as count
|
||
FROM f_polic_field
|
||
WHERE tenant_id = %s
|
||
GROUP BY field_type
|
||
""", (tenant_id,))
|
||
field_types = cursor.fetchall()
|
||
for row in field_types:
|
||
print(f" {row['type_name']} (field_type={row['field_type']}): {row['count']} 条")
|
||
|
||
# 10. 检查完整的关联关系
|
||
print_section("10. 检查模板与字段的关联关系（示例）")
|
||
cursor.execute("""
|
||
SELECT
|
||
fc.id as file_id,
|
||
fc.name as file_name,
|
||
fc.file_path,
|
||
COUNT(ff.filed_id) as field_count
|
||
FROM f_polic_file_config fc
|
||
LEFT JOIN f_polic_file_field ff ON fc.id = ff.file_id AND ff.tenant_id = %s
|
||
WHERE fc.tenant_id = %s
|
||
GROUP BY fc.id, fc.name, fc.file_path
|
||
LIMIT 10
|
||
""", (tenant_id, tenant_id))
|
||
relations = cursor.fetchall()
|
||
if relations:
|
||
for i, row in enumerate(relations, 1):
|
||
print(f"\n 模板 {i}:")
|
||
print(f" ID: {row['file_id']}")
|
||
print(f" 名称: {row['file_name']}")
|
||
print(f" 路径: {row['file_path']}")
|
||
print(f" 关联字段数: {row['field_count']}")
|
||
else:
|
||
print(" 没有找到关联记录")
|
||
|
||
cursor.close()
|
||
conn.close()
|
||
|
||
print_section("检查完成")
|
||
|
||
except Exception as e:
|
||
print(f"检查失败: {str(e)}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
|
||
|
||
if __name__ == "__main__":
|
||
check_database()
|