buff3.0/backend-client/fix_sessions_his.py

"""
修复 sessions_his 表缺失字段的迁移脚本
"""
import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

conn = pymysql.connect(
    host=os.getenv('DB_HOST', 'localhost'),
    port=int(os.getenv('DB_PORT', 3306)),
    user=os.getenv('DB_USER', 'root'),
    password=os.getenv('DB_PASSWORD', ''),
    database=os.getenv('DB_NAME', 'buff'),
    charset='utf8mb4'
)

cursor = conn.cursor()

# 添加 his_created_at 字段
sql1 = (
    "ALTER TABLE `sessions_his` "
    "ADD COLUMN `his_created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间', "
    "ADD INDEX `idx_his_created_at` (`his_created_at`)"
)
try:
    cursor.execute(sql1)
    print('Added his_created_at column')
except Exception as e:
    if 'Duplicate' in str(e) or 'already exists' in str(e):
        print('his_created_at column already exists')
    else:
        print(f'his_created_at error: {e}')

# 添加 his_operation 字段
sql2 = (
    "ALTER TABLE `sessions_his` "
    "ADD COLUMN `his_operation` varchar(10) NOT NULL COMMENT '操作类型: INSERT/UPDATE/DELETE/RENEW'"
)
try:
    cursor.execute(sql2)
    print('Added his_operation column')
except Exception as e:
    if 'Duplicate' in str(e) or 'already exists' in str(e):
        print('his_operation column already exists')
    else:
        print(f'his_operation error: {e}')

conn.commit()
cursor.close()
conn.close()
print('Migration completed!')