import os import json import pymysql def get_icon_format(folder_path, folder): icon_file_prefix = f"icon_{folder}" for file in os.listdir(folder_path): if file.startswith(icon_file_prefix): return file.split('.')[-1] return None def read_order_list(file_path): with open(file_path, 'r') as file: return [line.strip() for line in file] def generate_assets_json(assets_type): directory = "/var/www/html/eli/new_assets/" + assets_type assets = [] for folder in os.listdir(directory): folder_path = os.path.join(directory, folder) if os.path.isdir(folder_path): target_file_prefix = folder + '.' icon_format = get_icon_format(folder_path, folder) for file in os.listdir(folder_path): if file.startswith(target_file_prefix): format = file.split('.')[-1] asset = {"id": folder, "format": format} if format in ["jpg", "png", "mp4"]: file_path = os.path.join(folder_path, file) screen = get_screen_orientation(file_path) asset["screen"] = screen if icon_format: asset["iconFormat"] = icon_format assets.append(asset) break if assets_type == 'vdstreamers': order_list = read_order_list('/var/www/html/eli/new_assets/vdstreamers/order_list.txt') assets.sort(key=lambda asset: order_list.index(asset["id"]) if asset["id"] in order_list else len(order_list)) if assets_type == 'backgrounds': order_list = read_order_list('/var/www/html/eli/new_assets/backgrounds/order_list.txt') assets.sort(key=lambda asset: order_list.index(asset["id"]) if asset["id"] in order_list else len(order_list)) return {f"{assets_type}": assets} # 数据库连接配置 DB_CONFIG = { "host": "8.129.147.237", # 数据库主机 "user": "root", # 数据库用户名 "password": "AMAIGC080523@", # 数据库密码 "database": "elai", # 数据库名 "charset": "utf8mb4" } # JSON 文件路径 def insert_json_to_database(json_file_path, db_config): # 连接到 MySQL 数据库 connection = pymysql.connect(**db_config) cursor = connection.cursor() # 插入数据的 SQL 语句 sql = """ INSERT INTO `elai_assets` (`id`, `format`, `icon_format`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `format` = VALUES(`format`), `icon_format` = VALUES(`icon_format`); """ try: # 读取 JSON 文件 with open(json_file_path, 'r', encoding='utf-8') as file: data = json.load(file) # 获取 JSON 中的 "vdstreamers" 数据 assets = data.get("vdstreamers", []) # 插入每一条数据 for asset in assets: asset_id = asset.get("id") asset_format = asset.get("format") icon_format = asset.get("iconFormat") cursor.execute(sql, (asset_id, asset_format, icon_format)) # 提交事务 connection.commit() print("数据已成功写入数据库！") except Exception as e: # 出现异常时回滚 connection.rollback() print(f"发生错误：{e}") finally: # 关闭数据库连接 cursor.close() connection.close() def get_account_assets(): # 数据库连接配置 account = "test" db_config = { "host": "8.129.147.237", "user": "root", "password": "AMAIGC080523@", "database": "elai", "charset": "utf8mb4" } try: # 连接数据库 conn = pymysql.connect(**db_config) cursor = conn.cursor() # 从 account_assets 表获取 asset_primkey cursor.execute("SELECT asset_primkey FROM account_assets WHERE account = %s", (account,)) result = cursor.fetchone() if not result: return {"error": "Account not found"} # 将 asset_primkey 转为列表 asset_primkey_list = json.loads(result[0]) # 假设 asset_primkey 存储为 JSON 字符串 # 根据 asset_primkey 查询 elai_assets 表 cursor.execute( "SELECT id, format, icon_format FROM elai_assets WHERE primkey IN %s", (tuple(asset_primkey_list),) ) assets = cursor.fetchall() # 整理为指定格式 formatted_assets = [ {"id": asset[0], "format": asset[1], "iconFormat": asset[2]} for asset in assets ] return {"vdstreamers": formatted_assets} except pymysql.MySQLError as e: return {"error": f"Database error: {e}"} finally: # 关闭数据库连接 if cursor: cursor.close() if conn: conn.close() import json import pymysql def check_account_ali_voice(): db_connection = pymysql.connect( host="8.129.147.237", # 数据库主机地址 user="root", # 数据库用户名 password="AMAIGC080523@", # 数据库密码 database="elai", # 数据库名称 charset="utf8mb4" # 字符集 ) # 创建游标对象，确保返回结果为字典格式 cursor = db_connection.cursor(pymysql.cursors.DictCursor) # 指定特定的 account account_to_query = 'ellhj' # 替换为你要查询的 account 名称 # 查询指定 account 的 voice_primkey cursor.execute("SELECT voice_primkey FROM account_ali_voice WHERE account = %s", (account_to_query,)) # 获取查询结果，确保返回的是字典格式 result = cursor.fetchone() if result: # 确保 voice_primkey 为有效的 JSON 格式 try: # 将 JSON 字符串转为 Python 列表 voice_primkey = json.loads(result["voice_primkey"]) # print("voice_primkey:",voice_primkey) except json.JSONDecodeError: print("voice_primkey 字段的值不是有效的 JSON 格式") cursor.close() db_connection.close() exit() # 查询 elai_ali_voice 表中与 voice_primkey 相对应的记录 # cursor.execute("SELECT Name, Remark, LogicName, Sex FROM elai_ali_voice WHERE primkey IN (%s)", # (",".join(map(str, voice_primkey)),)) # 将列表转化为逗号分隔的字符串 cursor.execute("SELECT Name, Remark, LogicName, Sex, Language FROM elai_ali_voice WHERE primkey IN (%s)" % ','.join(['%s'] * len(voice_primkey)),tuple(voice_primkey)) # 获取查询结果 voices = cursor.fetchall() # 生成 JSON 数据结构 json_result = { "PrefabSoundDatas": [] } # 将查询到的数据添加到 PrefabSoundDatas 中 for voice in voices: json_result["PrefabSoundDatas"].append({ "Name": voice["Name"], "Remark": voice["Remark"], "LogicName": voice["LogicName"], "Sex": voice["Sex"], "Language":voice["Language"] }) # 打印最终的 JSON 数据 # print(json.dumps(json_result, ensure_ascii=False, indent=4)) return json_result else: print("没有找到对应的 account 或 voice_primkey。") if __name__ == '__main__': # assets_json = generate_assets_json("vdstreamers") # with open("asset_data.json",'w',encoding = 'utf-8') as json_file: # json.dump(assets_json,json_file,ensure_ascii=False,indent=4) # insert_json_to_database("asset_data.json", DB_CONFIG) # asset_json = get_account_assets("EL2024062601") # print(asset_json) # insert_data_to_db(json_data) result = check_account_ali_voice() with open("result.json",'w',encoding = 'utf-8') as json_file: json.dump(result,json_file,indent = '\t', ensure_ascii = False)