89 lines
3.4 KiB
Python
89 lines
3.4 KiB
Python
"""
|
||
检查关联关系查询逻辑
|
||
"""
|
||
import pymysql
|
||
import os
|
||
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 = 615873064429507639
|
||
|
||
def check_relations():
|
||
"""检查关联关系查询"""
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
||
|
||
try:
|
||
# 检查一个具体模板的关联关系
|
||
template_id = 1765273962716807 # 走读式谈话流程
|
||
|
||
print(f"检查模板 ID: {template_id}")
|
||
|
||
# 方法1: 当前 API 使用的查询
|
||
print("\n方法1: 当前 API 使用的查询（带 INNER JOIN 和 state=1）:")
|
||
cursor.execute("""
|
||
SELECT fff.file_id, fff.filed_id, fff.state as relation_state, fc.state as template_state
|
||
FROM f_polic_file_field fff
|
||
INNER JOIN f_polic_file_config fc ON fff.file_id = fc.id AND fff.tenant_id = fc.tenant_id
|
||
WHERE fff.tenant_id = %s AND fff.state = 1 AND fff.file_id = %s
|
||
""", (TENANT_ID, template_id))
|
||
results1 = cursor.fetchall()
|
||
print(f" 结果数: {len(results1)}")
|
||
for r in results1[:5]:
|
||
print(f" file_id: {r['file_id']}, filed_id: {r['filed_id']}, relation_state: {r['relation_state']}, template_state: {r['template_state']}")
|
||
|
||
# 方法2: 只查询关联表，不检查模板状态
|
||
print("\n方法2: 只查询关联表（不检查模板状态）:")
|
||
cursor.execute("""
|
||
SELECT fff.file_id, fff.filed_id, fff.state as relation_state
|
||
FROM f_polic_file_field fff
|
||
WHERE fff.tenant_id = %s AND fff.state = 1 AND fff.file_id = %s
|
||
""", (TENANT_ID, template_id))
|
||
results2 = cursor.fetchall()
|
||
print(f" 结果数: {len(results2)}")
|
||
for r in results2[:5]:
|
||
print(f" file_id: {r['file_id']}, filed_id: {r['filed_id']}, relation_state: {r['relation_state']}")
|
||
|
||
# 方法3: 检查模板是否存在且启用
|
||
print("\n方法3: 检查模板状态:")
|
||
cursor.execute("""
|
||
SELECT id, name, state
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s AND id = %s
|
||
""", (TENANT_ID, template_id))
|
||
template = cursor.fetchone()
|
||
if template:
|
||
print(f" 模板存在: {template['name']}, state: {template['state']}")
|
||
else:
|
||
print(f" 模板不存在")
|
||
|
||
# 检查所有关联关系（包括 state=0 的）
|
||
print("\n方法4: 检查所有关联关系（包括未启用的）:")
|
||
cursor.execute("""
|
||
SELECT fff.file_id, fff.filed_id, fff.state as relation_state
|
||
FROM f_polic_file_field fff
|
||
WHERE fff.tenant_id = %s AND fff.file_id = %s
|
||
""", (TENANT_ID, template_id))
|
||
results4 = cursor.fetchall()
|
||
print(f" 结果数: {len(results4)}")
|
||
enabled = [r for r in results4 if r['relation_state'] == 1]
|
||
disabled = [r for r in results4 if r['relation_state'] == 0]
|
||
print(f" 启用: {len(enabled)}, 未启用: {len(disabled)}")
|
||
|
||
finally:
|
||
cursor.close()
|
||
conn.close()
|
||
|
||
if __name__ == '__main__':
|
||
check_relations()
|