5zu-books/生成封面更新SQL.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
自动生成书籍封面更新SQL脚本
使用方法：
1. 先执行查询SQL获取所有书籍信息
2. 将书籍信息填入下面的books列表
3. 运行此脚本生成SQL文件
"""

# 书籍信息列表
# 格式：{"id": 书籍ID, "title": "书名", "cover_url": "封面URL"}
books = [
    # 示例：
    # {"id": 1, "title": "活着", "cover_url": "https://img3.doubanio.com/view/subject/l/public/s1070959.jpg"},
    # {"id": 2, "title": "三体", "cover_url": "https://img3.doubanio.com/view/subject/l/public/s2768378.jpg"},
    
    # TODO: 请根据实际查询结果，添加你的书籍信息
    # 可以从数据库查询结果中复制，格式如下：
    # {"id": 1, "title": "书名1", "cover_url": "封面URL1"},
    # {"id": 2, "title": "书名2", "cover_url": "封面URL2"},
]

def generate_sql():
    """生成SQL更新语句"""
    sql_statements = []
    sql_statements.append("-- 批量更新书籍封面SQL脚本")
    sql_statements.append("-- 生成时间: " + __import__('datetime').datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
    sql_statements.append("")
    sql_statements.append("-- 方法1: 逐个更新（推荐，可以单独执行）")
    sql_statements.append("")
    
    for book in books:
        sql = f"UPDATE `content` SET `cover_url` = '{book['cover_url']}' WHERE `content_id` = {book['id']};"
        sql_statements.append(sql)
        sql_statements.append(f"-- {book['title']}")
        sql_statements.append("")
    
    sql_statements.append("")
    sql_statements.append("-- 方法2: 使用CASE语句批量更新（适用于少量书籍）")
    sql_statements.append("UPDATE `content`")
    sql_statements.append("SET `cover_url` = CASE")
    
    for book in books:
        sql_statements.append(f"    WHEN `content_id` = {book['id']} THEN '{book['cover_url']}'")
    
    sql_statements.append("    ELSE `cover_url`")
    sql_statements.append("END")
    
    if books:
        ids = [str(book['id']) for book in books]
        sql_statements.append(f"WHERE `content_id` IN ({', '.join(ids)});")
    
    sql_statements.append("")
    sql_statements.append("-- 验证更新结果")
    sql_statements.append("SELECT `content_id`, `title`, `cover_url` FROM `content` ORDER BY `content_id`;")
    
    return "\n".join(sql_statements)

if __name__ == "__main__":
    if not books:
        print("=" * 60)
        print("提示：请先在脚本中填入书籍信息！")
        print("=" * 60)
        print("\n使用步骤：")
        print("1. 执行查询SQL获取所有书籍：")
        print("   SELECT content_id, title, author, cover_url FROM content ORDER BY content_id;")
        print("\n2. 为每本书找到封面URL（可以从豆瓣、网络搜索获取）")
        print("\n3. 将书籍信息添加到books列表，格式：")
        print('   {"id": 1, "title": "书名", "cover_url": "封面URL"}')
        print("\n4. 重新运行此脚本生成SQL文件")
    else:
        sql_content = generate_sql()
        
        # 保存到文件
        output_file = "批量更新封面_生成.sql"
        with open(output_file, 'w', encoding='utf-8') as f:
            f.write(sql_content)
        
        print("=" * 60)
        print(f"SQL脚本已生成：{output_file}")
        print("=" * 60)
        print("\n生成的SQL内容：")
        print("-" * 60)
        print(sql_content)
        print("-" * 60)
        print("\n请检查SQL语句后执行！")