166 lines
5.1 KiB
Python
166 lines
5.1 KiB
Python
"""
|
||
更新f_polic_file_config表，确保使用template_code字段
|
||
从input_data字段中提取template_code并更新到template_code字段
|
||
"""
|
||
import pymysql
|
||
import json
|
||
from datetime import datetime
|
||
|
||
# 数据库连接配置
|
||
DB_CONFIG = {
|
||
'host': '152.136.177.240',
|
||
'port': 5012,
|
||
'user': 'finyx',
|
||
'password': '6QsGK6MpePZDE57Z',
|
||
'database': 'finyx',
|
||
'charset': 'utf8mb4'
|
||
}
|
||
|
||
# 固定值
|
||
TENANT_ID = 615873064429507639
|
||
UPDATED_BY = 655162080928945152
|
||
CURRENT_TIME = datetime.now()
|
||
|
||
# 模板名称到template_code的映射
|
||
TEMPLATE_NAME_TO_CODE = {
|
||
'初步核实审批表': 'PRELIMINARY_VERIFICATION_APPROVAL',
|
||
'请示报告卡': 'REPORT_CARD',
|
||
# 可以根据实际情况添加其他映射
|
||
}
|
||
|
||
|
||
def update_template_code_from_input_data():
|
||
"""
|
||
从input_data字段中提取template_code并更新到template_code字段
|
||
如果template_code字段为空，则从input_data中提取
|
||
"""
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
||
|
||
print("="*60)
|
||
print("开始更新template_code字段...")
|
||
print("="*60)
|
||
|
||
try:
|
||
# 查询所有文件配置
|
||
sql = """
|
||
SELECT id, name, input_data, template_code
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s
|
||
"""
|
||
cursor.execute(sql, (TENANT_ID,))
|
||
configs = cursor.fetchall()
|
||
|
||
updated_count = 0
|
||
|
||
for config in configs:
|
||
config_id = config['id']
|
||
config_name = config['name']
|
||
input_data = config.get('input_data')
|
||
existing_template_code = config.get('template_code')
|
||
|
||
new_template_code = None
|
||
|
||
# 如果已有template_code，跳过
|
||
if existing_template_code:
|
||
print(f" ✓ {config_name} 已有template_code: {existing_template_code}")
|
||
continue
|
||
|
||
# 方法1: 从input_data中提取
|
||
if input_data:
|
||
try:
|
||
input_data_dict = json.loads(input_data) if isinstance(input_data, str) else input_data
|
||
if isinstance(input_data_dict, dict):
|
||
new_template_code = input_data_dict.get('template_code')
|
||
if new_template_code:
|
||
print(f" ✓ 从input_data中提取到template_code: {new_template_code}")
|
||
except:
|
||
pass
|
||
|
||
# 方法2: 从映射表中获取
|
||
if not new_template_code and config_name in TEMPLATE_NAME_TO_CODE:
|
||
new_template_code = TEMPLATE_NAME_TO_CODE[config_name]
|
||
print(f" ✓ 从映射表获取template_code: {new_template_code}")
|
||
|
||
# 如果找到了template_code，更新数据库
|
||
if new_template_code:
|
||
update_sql = """
|
||
UPDATE f_polic_file_config
|
||
SET template_code = %s, updated_time = %s, updated_by = %s
|
||
WHERE id = %s
|
||
"""
|
||
cursor.execute(update_sql, (new_template_code, CURRENT_TIME, UPDATED_BY, config_id))
|
||
updated_count += 1
|
||
print(f" ✓ 更新 {config_name} 的template_code: {new_template_code}")
|
||
else:
|
||
print(f" ⚠ 无法确定 {config_name} 的template_code，请手动设置")
|
||
|
||
conn.commit()
|
||
|
||
print("\n" + "="*60)
|
||
print(f"更新完成！共更新 {updated_count} 条记录")
|
||
print("="*60)
|
||
|
||
finally:
|
||
cursor.close()
|
||
conn.close()
|
||
|
||
|
||
def verify_template_code():
|
||
"""验证template_code字段"""
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
||
|
||
print("\n" + "="*60)
|
||
print("验证template_code字段...")
|
||
print("="*60)
|
||
|
||
try:
|
||
sql = """
|
||
SELECT id, name, template_code, state
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s
|
||
ORDER BY name
|
||
"""
|
||
cursor.execute(sql, (TENANT_ID,))
|
||
configs = cursor.fetchall()
|
||
|
||
print(f"\n共找到 {len(configs)} 个文件配置：\n")
|
||
|
||
for config in configs:
|
||
template_code = config.get('template_code')
|
||
state_str = "启用" if config.get('state') == 1 else "未启用"
|
||
|
||
if template_code:
|
||
print(f" ✓ {config['name']}")
|
||
print(f" template_code: {template_code}")
|
||
print(f" 状态: {state_str}")
|
||
else:
|
||
print(f" ⚠ {config['name']} (缺少template_code)")
|
||
print(f" 状态: {state_str}")
|
||
print()
|
||
|
||
finally:
|
||
cursor.close()
|
||
conn.close()
|
||
|
||
|
||
def main():
|
||
"""主函数"""
|
||
try:
|
||
# 更新template_code字段
|
||
update_template_code_from_input_data()
|
||
|
||
# 验证结果
|
||
verify_template_code()
|
||
|
||
except Exception as e:
|
||
print(f"\n错误: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|
||
|