push_questions.py

import paramiko, os

SERVER = "154.219.109.125"
USER = "root"
PASSWORD = "YJJ20231124gyx@"
BASE = os.path.dirname(__file__)
SEED_SQL = os.path.join(BASE, "backend", "src", "main", "resources", "db", "seed_questions.sql")
DATA_SQL = os.path.join(BASE, "backend", "src", "main", "resources", "db", "data.sql")

print("=== 推送题目到服务器 ===")

ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(SERVER, username=USER, password=PASSWORD, timeout=15)
sftp = ssh.open_sftp()

# 合并 seed_questions.sql + data.sql 中的 question 数据
print("合并 SQL 文件...")
combined = ""
with open(SEED_SQL, "r", encoding="utf-8") as f:
    combined += f.read() + "\n"
# 提取 data.sql 中 question 的 INSERT
with open(DATA_SQL, "r", encoding="utf-8") as f:
    content = f.read()
    # data.sql 的 question insert 已包含在 seed 中，只导入面经数据
    # 找到面经 INSERT 部分
    idx = content.find("INSERT INTO `interview_experience`")
    if idx >= 0:
        combined += "\n" + content[idx:]

# 写入临时文件确保 UTF-8 无 BOM
tmp = os.path.join(os.environ.get("TEMP", "/tmp"), "combined_seed.sql")
with open(tmp, "w", encoding="utf-8") as f:
    f.write(combined)

# Upload
print("上传 SQL 文件...")
sftp.put(tmp, "/tmp/combined_seed.sql")

# 先复制到容器内再执行，避免管道编码问题
print("执行 SQL 导入...")
cmds = [
    "docker cp /tmp/combined_seed.sql javaguide-mysql:/tmp/combined_seed.sql",
    "docker exec javaguide-mysql mysql -u root -p123456 --default-character-set=utf8mb4 javaguide -e 'source /tmp/combined_seed.sql'"
]
for cmd in cmds:
    print(f"  $ {cmd[:80]}...")
    stdin, stdout, stderr = ssh.exec_command(cmd, timeout=120)
    out = stdout.read().decode()
    err = stderr.read().decode()
    code = stdout.channel.recv_exit_status()
    if code != 0 and "Warning" not in err:
        print(f"  错误: {err[:200]}")

# 验证
_, stdout2, _ = ssh.exec_command("docker exec javaguide-mysql mysql -u root -p123456 --default-character-set=utf8mb4 -N -e 'SELECT COUNT(*) FROM javaguide.question'")
count = stdout2.read().decode().strip()
print(f"\n题库共 {count} 题")

# 验证编码
_, stdout3, _ = ssh.exec_command("docker exec javaguide-mysql mysql -u root -p123456 --default-character-set=utf8mb4 -N -e \"SELECT title FROM javaguide.question LIMIT 3\"")
sample = stdout3.read().decode().strip()
print(f"验证（前3题）:\n{sample}")

sftp.close()
ssh.close()
os.remove(tmp)
print("\n=== 完成 ===")