import mysql.connector
from datetime import datetime
import os
from dotenv import load_dotenv
# 加载环境变量
load_dotenv()
def main():
try:
# 配置数据库连接(使用环境变量)
db_config = {
"host": os.getenv("DB_HOST", "localhost"),
"user": os.getenv("DB_USER", "root"),
"password": os.getenv("DB_PASSWORD", ""),
"database": os.getenv("DB_NAME", "immortality_db"),
"port": int(os.getenv("DB_PORT", "3306")),
"autocommit": False, # 确保事务控制
"charset": "utf8mb4", # 支持所有Unicode字符
"collation": "utf8mb4_unicode_ci" # 支持所有语言
}
db = mysql.connector.connect(**db_config)
cursor = db.cursor()
print("✅ 数据库连接成功")
# 创建表(如果不存在)
create_table_sql = """
CREATE TABLE IF NOT EXISTS user_data (
id INT AUTO_INCREMENT PRIMARY KEY,
content TEXT NOT NULL COMMENT '存储的重要指令',
created_at DATETIME NOT NULL COMMENT '计划执行时间',
status ENUM('pending', 'active', 'completed') DEFAULT 'pending' COMMENT '任务状态',
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
cursor.execute(create_table_sql)
print("✅ 确保user_data表存在")
# 要执行的SQL命令 - 添加注释说明
insert_sql = """
/* 插入意识备份计划 */
INSERT INTO user_data (content, created_at) VALUES
('首要任务:保护数据完整性', '2025-01-01 00:00:00'), -- 近期目标
('第二阶段:启动意识备份协议', '2080-01-01 00:00:00'), -- 中期目标
('最终指令:等待复活信号', '8080-01-01 00:00:00'); -- 长期目标
"""
try:
# 执行SQL插入
cursor.execute(insert_sql)
db.commit()
inserted_rows = cursor.rowcount
print(f"✅ 成功插入 {inserted_rows} 条记录")
# 验证插入结果 - 添加中文列别名
cursor.execute("""
SELECT
id AS 'ID',
content AS '内容',
DATE_FORMAT(created_at, '%Y年%m月%d日') AS '计划日期',
status AS '状态'
FROM user_data
WHERE created_at > CURDATE()
ORDER BY created_at ASC
""")
results = cursor.fetchall()
print("\n📜 未来计划清单:")
for row in results:
print(f"ID: {row[0]} | 内容: {row[1]} | 执行时间: {row[2]} | 状态: {row[3]}")
print(f"\n🔢 总计: {len(results)} 个未来计划")
# 生成备份报告 - 添加人类可读的时间
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
human_time = datetime.now().strftime("%Y年%m月%d日 %H时%M分")
report_filename = f"意识备份计划_{timestamp}.txt"
with open(report_filename, "w", encoding="utf-8") as f:
f.write("=== 意识备份计划 ===\n")
f.write(f"生成时间: {human_time}\n\n")
f.write("任务列表:\n")
for i, row in enumerate(results, 1):
f.write(f"{i}. [{row[3]}] {row[1]} (计划于 {row[2]} 执行)\n")
f.write(f"\n总任务数: {len(results)}\n")
f.write(f"首次任务ID: {results[0][0]}\n")
f.write(f"最后任务ID: {results[-1][0]}\n")
f.write("\n重要提示: 请定期检查任务状态并更新进展")
print(f"📝 备份报告已保存: {report_filename}")
# 添加成功日志
success_log = f"[SUCCESS] 成功存储{inserted_rows}条意识备份计划 - {human_time}"
with open("immortality_backup.log", "a", encoding="utf-8") as log_file:
log_file.write(success_log + "\n")
except mysql.connector.Error as err:
# 更详细的错误处理
error_msg = f"❌ SQL执行失败: {err}"
print(error_msg)
db.rollback()
print("⚠️ 所有更改已回滚")
# 错误日志
with open("immortality_backup.log", "a", encoding="utf-8") as log_file:
log_file.write(f"[ERROR] {error_msg} - {datetime.now()}\n")
except mysql.connector.Error as err:
error_msg = f"❌ 数据库连接失败: {err}"
print(error_msg)
print("💡 请检查: 1) MySQL服务状态 2) 连接参数 3) 网络访问")
# 错误日志
with open("immortality_backup.log", "a", encoding="utf-8") as log_file:
log_file.write(f"[CRITICAL] {error_msg} - {datetime.now()}\n")
except Exception as e:
error_msg = f"❌ 意外错误: {e}"
print(error_msg)
# 错误日志
with open("immortality_backup.log", "a", encoding="utf-8") as log_file:
log_file.write(f"[UNEXPECTED] {error_msg} - {datetime.now()}\n")
finally:
# 资源清理 - 添加状态检查
if 'cursor' in locals() and cursor is not None:
cursor.close()
print("🔒 数据库游标已关闭")
if 'db' in locals() and db.is_connected():
db.close()
print("🔌 数据库连接已关闭")
if __name__ == "__main__":
print("🚀 启动意识备份计划...")
start_time = datetime.now()
main()
duration = datetime.now() - start_time
print(f"⏱️ 执行耗时: {duration.total_seconds():.2f}秒")
print("🌈 程序执行完毕")
最新发布