261 lines
8.3 KiB
Python
261 lines
8.3 KiB
Python
"""
|
||
修复缺少字段关联的模板
|
||
为有 template_code 但没有字段关联的文件节点补充字段关联
|
||
"""
|
||
import os
|
||
import json
|
||
import pymysql
|
||
from typing import Dict, List
|
||
|
||
# 数据库连接配置
|
||
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
|
||
CREATED_BY = 655162080928945152
|
||
UPDATED_BY = 655162080928945152
|
||
|
||
|
||
def generate_id():
|
||
"""生成ID"""
|
||
import time
|
||
import random
|
||
timestamp = int(time.time() * 1000)
|
||
random_part = random.randint(100000, 999999)
|
||
return timestamp * 1000 + random_part
|
||
|
||
|
||
def get_templates_without_relations(conn):
|
||
"""获取没有字段关联的文件节点"""
|
||
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
||
|
||
sql = """
|
||
SELECT
|
||
fc.id,
|
||
fc.name,
|
||
fc.template_code,
|
||
fc.input_data,
|
||
COUNT(ff.id) as relation_count
|
||
FROM f_polic_file_config fc
|
||
LEFT JOIN f_polic_file_field ff ON fc.id = ff.file_id AND ff.tenant_id = fc.tenant_id
|
||
WHERE fc.tenant_id = %s
|
||
AND fc.template_code IS NOT NULL
|
||
AND fc.template_code != ''
|
||
GROUP BY fc.id, fc.name, fc.template_code, fc.input_data
|
||
HAVING relation_count = 0
|
||
ORDER BY fc.name
|
||
"""
|
||
cursor.execute(sql, (TENANT_ID,))
|
||
templates = cursor.fetchall()
|
||
|
||
cursor.close()
|
||
return templates
|
||
|
||
|
||
def get_fields_by_code(conn):
|
||
"""获取所有字段，按字段编码索引"""
|
||
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
||
|
||
sql = """
|
||
SELECT id, name, filed_code, field_type
|
||
FROM f_polic_field
|
||
WHERE tenant_id = %s
|
||
"""
|
||
cursor.execute(sql, (TENANT_ID,))
|
||
fields = cursor.fetchall()
|
||
|
||
result = {
|
||
'by_code': {},
|
||
'by_name': {}
|
||
}
|
||
|
||
for field in fields:
|
||
field_code = field['filed_code']
|
||
field_name = field['name']
|
||
result['by_code'][field_code] = field
|
||
result['by_name'][field_name] = field
|
||
|
||
cursor.close()
|
||
return result
|
||
|
||
|
||
def extract_fields_from_input_data(input_data: str) -> List[str]:
|
||
"""从 input_data 中提取字段编码列表"""
|
||
try:
|
||
data = json.loads(input_data) if isinstance(input_data, str) else input_data
|
||
if isinstance(data, dict):
|
||
return data.get('input_fields', [])
|
||
except:
|
||
pass
|
||
return []
|
||
|
||
|
||
def create_field_relations(conn, file_id: int, field_codes: List[str], field_type: int,
|
||
db_fields: Dict, dry_run: bool = True):
|
||
"""创建字段关联关系"""
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
created_count = 0
|
||
|
||
for field_code in field_codes:
|
||
field = db_fields['by_code'].get(field_code)
|
||
|
||
if not field:
|
||
print(f" ⚠ 字段不存在: {field_code}")
|
||
continue
|
||
|
||
if field['field_type'] != field_type:
|
||
print(f" ⚠ 字段类型不匹配: {field_code} (期望 {field_type}, 实际 {field['field_type']})")
|
||
continue
|
||
|
||
if not dry_run:
|
||
# 检查是否已存在
|
||
check_sql = """
|
||
SELECT id FROM f_polic_file_field
|
||
WHERE tenant_id = %s AND file_id = %s AND filed_id = %s
|
||
"""
|
||
cursor.execute(check_sql, (TENANT_ID, file_id, field['id']))
|
||
existing = cursor.fetchone()
|
||
|
||
if not existing:
|
||
relation_id = generate_id()
|
||
insert_sql = """
|
||
INSERT INTO f_polic_file_field
|
||
(id, tenant_id, file_id, filed_id, created_time, created_by, updated_time, updated_by, state)
|
||
VALUES (%s, %s, %s, %s, NOW(), %s, NOW(), %s, %s)
|
||
"""
|
||
cursor.execute(insert_sql, (
|
||
relation_id, TENANT_ID, file_id, field['id'],
|
||
CREATED_BY, UPDATED_BY, 1
|
||
))
|
||
created_count += 1
|
||
print(f" ✓ 创建关联: {field['name']} ({field_code})")
|
||
else:
|
||
created_count += 1
|
||
print(f" [模拟] 将创建关联: {field_code}")
|
||
|
||
if not dry_run:
|
||
conn.commit()
|
||
|
||
return created_count
|
||
|
||
finally:
|
||
cursor.close()
|
||
|
||
|
||
def main():
|
||
"""主函数"""
|
||
print("="*80)
|
||
print("修复缺少字段关联的模板")
|
||
print("="*80)
|
||
|
||
try:
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
print("✓ 数据库连接成功\n")
|
||
except Exception as e:
|
||
print(f"✗ 数据库连接失败: {e}")
|
||
return
|
||
|
||
try:
|
||
# 获取没有字段关联的模板
|
||
print("查找缺少字段关联的模板...")
|
||
templates = get_templates_without_relations(conn)
|
||
print(f" 找到 {len(templates)} 个缺少字段关联的文件节点\n")
|
||
|
||
if not templates:
|
||
print("✓ 所有文件节点都有字段关联，无需修复")
|
||
return
|
||
|
||
# 获取所有字段
|
||
print("获取字段定义...")
|
||
db_fields = get_fields_by_code(conn)
|
||
print(f" 找到 {len(db_fields['by_code'])} 个字段\n")
|
||
|
||
# 显示需要修复的模板
|
||
print("需要修复的模板：")
|
||
for template in templates:
|
||
print(f" - {template['name']} (code: {template['template_code']})")
|
||
|
||
# 尝试从 input_data 中提取字段
|
||
print("\n" + "="*80)
|
||
print("分析并修复")
|
||
print("="*80)
|
||
|
||
fixable_count = 0
|
||
unfixable_count = 0
|
||
|
||
for template in templates:
|
||
print(f"\n处理: {template['name']}")
|
||
print(f" template_code: {template['template_code']}")
|
||
|
||
input_data = template.get('input_data')
|
||
if not input_data:
|
||
print(" ⚠ 没有 input_data，无法自动修复")
|
||
unfixable_count += 1
|
||
continue
|
||
|
||
# 从 input_data 中提取输入字段
|
||
input_fields = extract_fields_from_input_data(input_data)
|
||
|
||
if not input_fields:
|
||
print(" ⚠ input_data 中没有 input_fields，无法自动修复")
|
||
unfixable_count += 1
|
||
continue
|
||
|
||
print(f" 找到 {len(input_fields)} 个输入字段")
|
||
fixable_count += 1
|
||
|
||
# 创建输入字段关联
|
||
print(" 创建输入字段关联...")
|
||
created = create_field_relations(conn, template['id'], input_fields, 1, db_fields, dry_run=True)
|
||
print(f" 将创建 {created} 个输入字段关联")
|
||
|
||
print("\n" + "="*80)
|
||
print("统计")
|
||
print("="*80)
|
||
print(f" 可修复: {fixable_count} 个")
|
||
print(f" 无法自动修复: {unfixable_count} 个")
|
||
|
||
# 询问是否执行
|
||
if fixable_count > 0:
|
||
print("\n" + "="*80)
|
||
response = input("\n是否执行修复？(yes/no，默认no): ").strip().lower()
|
||
|
||
if response == 'yes':
|
||
print("\n执行修复...")
|
||
for template in templates:
|
||
input_data = template.get('input_data')
|
||
if not input_data:
|
||
continue
|
||
|
||
input_fields = extract_fields_from_input_data(input_data)
|
||
if not input_fields:
|
||
continue
|
||
|
||
print(f"\n修复: {template['name']}")
|
||
create_field_relations(conn, template['id'], input_fields, 1, db_fields, dry_run=False)
|
||
|
||
print("\n" + "="*80)
|
||
print("✓ 修复完成！")
|
||
print("="*80)
|
||
else:
|
||
print("\n已取消修复")
|
||
else:
|
||
print("\n没有可以自动修复的模板")
|
||
|
||
finally:
|
||
conn.close()
|
||
print("\n数据库连接已关闭")
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|
||
|