python">import mysql.connector
from mysql.connector import Error
from datetime import datetime# 数据库配置
db_config = {'host': 'xxx','user': 'xxx','password': 'xxx','database': 'xxx','port': 3306
}# 起始 ID，初始值为你要求的值
current_id = 745689123879789456# 生成当前时间
current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')# 连接数据库
def create_connection():connection = Nonetry:connection = mysql.connector.connect(**db_config)if connection.is_connected():print("连接 MySQL 数据库成功")except Error as e:print(f"Error: '{e}'")return connection# 递归查找完整的 ID 链路
def get_id_path(cursor, org_id):id_path = []name_path = []while org_id:# 查找当前组织的 id, name, parent_idquery = """SELECT id, name, parent_idFROM t_departWHERE id = %s"""cursor.execute(query, (org_id,))result = cursor.fetchone()if result:current_id, name, parent_id = resultid_path.insert(0, current_id)  # 从前插入，保持从根到当前的顺序name_path.insert(0, name)  # 同时记录 name_pathorg_id = parent_id  # 更新为上级组织的 parent_id，继续递归else:breakreturn id_path, name_path# 根据 code 查找对应的组织 id
def get_org_id_by_code(cursor, code):query = """SELECT idFROM t_departWHERE code = %s"""cursor.execute(query, (code,))result = cursor.fetchone()return result[0] if result else None# 生成插入 SQL 语句
def generate_insert_sql(identity_id, code_list, cursor):global current_id  # 引用全局变量global current_time # 引用全局变量scope_entries = []display = []display_label = []# 对于每个 code，查找其对应的组织 id，然后递归查找完整的 id 链路for code in code_list:org_id = get_org_id_by_code(cursor, code)if org_id:# 查找完整的 id 链路和 name 链路id_path, name_path = get_id_path(cursor, org_id)# 计算层级 levellevel = len(id_path)# 添加到 scope_value 中的项scope_entries.append(f'{{"scopeKey":"{org_id}","brandCode":"","uniqueCode":"","level":"{level}","type":""}}')# 添加到 display 和 display_label 中的项display.append(id_path)display_label.append(name_path)# 将 scope_entries, display, display_label 转换成字符串格式scope_value = f'[{",".join(scope_entries)}]'display_value = f'{display}'.replace("'", "\"")  # 转为双引号的 JSON 格式display_label_value = f'{display_label}'.replace("'", "\"")# 生成插入 SQL 语句，并使用当前的唯一递增 IDinsert_sql = f"""INSERT INTO `ram`.`t_data_scope` (`id`, `app_id`, `identity_id`, `scope_type`, `scope_value`, `scope_operating_type`, `status`, `remark`, `create_time`, `create_by`, `update_time`, `update_by`, `deleted`, `version`, `delete_time`, `display`, `display_label`) VALUES ('{current_id}', '10000', '{identity_id}', 'depart', '{scope_value}', '行政组织', 1, NULL, '{current_time}', 'script', '{current_time}', 'script', 0, NULL, NULL, '{display_value}', '{display_label_value}');"""# 每次生成 SQL 后递增 current_idcurrent_id += 1return insert_sql# 主函数，处理 t_depart_collect 中的数据
def process_collect_data(connection):cursor = connection.cursor()# 查询 t_depart_collect 表中的所有数据，测试时先只查前几条数据cursor.execute("SELECT identity_id, code FROM ram.t_depart_collect where exist = 1")rows = cursor.fetchall()with open("initDepartDataScope_SIT.sql", 'w', encoding='utf-8') as file:  # 打开文件以写入模式# 处理每一条记录for row in rows:identity_id, code_str = row# 分割 code 字符串，处理多个组织编码code_list = code_str.split('、')  # 使用 '、' 进行分割# 打印删除语句delete_sql = f"""DELETE FROM `ram`.`t_data_scope` WHERE scope_type = 'depart' AND identity_id = '{identity_id}';"""print(delete_sql)file.write(delete_sql + '\n')  # 将 SQL 语句写入文件，每条 SQL 语句换行insert_sql = generate_insert_sql(identity_id, code_list, cursor)print(insert_sql)  # 可以将其执行到数据库，或写入文件file.write(insert_sql + '\n')  # 将 SQL 语句写入文件，每条 SQL 语句换行cursor.close()# 运行程序
if __name__ == "__main__":connection = create_connection()if connection is not None and connection.is_connected():try:process_collect_data(connection)finally:connection.close()print("数据库连接已关闭")