Python SQLite3 动态创建子表指南

# 假设 ids = [(1,), (2,)]
# 错误的尝试：
# for id_tuple in ids:
#     # id_tuple 第一次迭代是 (1,)
#     # ids[0] 始终是 (1,)，而不是 1
#     cursor.execute('''CREATE TABLE IF NOT EXISTS ChildTable_%s (...)''' % (ids[0]))

import sqlite3
import requests
from bs4 import BeautifulSoup
import time # 推荐使用time模块，而不是直接导入sleep

# 模拟请求头，实际应用中应更完整
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}

# 连接到 SQLite 数据库
connection = sqlite3.connect('parser_results.db')
cursor = connection.cursor()

# 创建主表 Comics (如果不存在)
cursor.execute('''
CREATE TABLE IF NOT EXISTS Comics (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  cover TEXT NOT NULL,
  status TEXT NOT NULL,
  comic_url TEXT UNIQUE NOT NULL
  )
''')
connection.commit()

# 示例：爬取并插入 Comics 数据 (简化版，仅用于演示)
# 实际应用中，这部分代码应更健壮，包含错误处理和分页逻辑
def get_comic_urls_and_details():
    base_url = "https://asuratoon.com/manga/?page={}&order=update"
    for count in range(1, 2): # 仅爬取第一页作为示例
        url = base_url.format(count)
        try:
            response = requests.get(url, headers=headers, timeout=10)
            response.raise_for_status() # 检查HTTP请求是否成功
            soup = BeautifulSoup(response.text, "html.parser")
            data_url = soup.find_all('div', class_='bsx')

            for comic_div in data_url:
                comic_link = comic_div.find('a')
                if comic_link:
                    comic_url = comic_link.get('href')
                    # 进一步访问每个漫画详情页获取名称、封面和状态
                    detail_response = requests.get(comic_url, headers=headers, timeout=10)
                    detail_response.raise_for_status()
                    detail_soup = BeautifulSoup(detail_response.text, "html.parser")

                    data_general = detail_soup.find('div', class_='bixbox animefull')
                    if data_general:
                        name_tag = data_general.find('h1', class_='entry-title')
                        cover_tag = data_general.find('img')
                        status_div = data_general.find('div', class_='imptdt')

                        name = name_tag.text.strip() if name_tag else "Unknown Name"
                        cover = cover_tag.get('src') if cover_tag else "No Cover"
                        status = status_div.text.replace('Status ', '').strip() if status_div else "Unknown Status"

                        # 插入主表数据
                        try:
                            cursor.execute('INSERT INTO Comics (name, cover, status, comic_url) VALUES (?, ?, ?, ?)',
                                           (name, cover, status, comic_url))
                            connection.commit()
                            print(f"Inserted comic: {name}")
                        except sqlite3.IntegrityError:
                            print(f"Comic '{name}' (URL: {comic_url}) already exists, skipping.")
                        except Exception as e:
                            print(f"Error inserting comic {name}: {e}")
                    time.sleep(1) # 礼貌性延时
        except requests.exceptions.RequestException as e:
            print(f"Error fetching URL {url}: {e}")
        except Exception as e:
            print(f"An unexpected error occurred: {e}")

# 执行数据抓取和插入，确保Comics表有数据
get_comic_urls_and_details()

# 获取所有漫画的 ID 和 URL，以便后续创建子表和插入章节数据
cursor.execute('SELECT id, comic_url FROM Comics')
comic_data_for_children = cursor.fetchall() # 结果如 [(1, 'url1'), (2, 'url2')]

print(f"Found {len(comic_data_for_children)} comics to create child tables for.")

# 遍历每个漫画，创建对应的子表并插入章节数据
for comic_id, comic_detail_url in comic_data_for_children:
    # 构建子表名称，确保ID是整数
    child_table_name = f"ChildTable_{comic_id}"

    # 使用 f-string 安全地构建 CREATE TABLE 语句
    # 推荐使用方括号 [] 包裹表名，以防表名包含特殊字符（虽然这里是纯数字ID）
    create_table_sql = f"""
    CREATE TABLE IF NOT EXISTS [{child_table_name}] (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        chapter_name TEXT,
        release_date TEXT,
        chapter_url TEXT,
        comic_id INTEGER NOT NULL,
        FOREIGN KEY (comic_id) REFERENCES Comics(id)
    );
    """
    try:
        cursor.execute(create_table_sql)
        print(f"Created table: {child_table_name}")
        connection.commit()

        # 爬取当前漫画的章节信息并插入到对应的子表
        # 假设章节信息可以在 comic_detail_url 页面找到
        print(f"Fetching chapters for comic ID {comic_id} from {comic_detail_url}")
        detail_response = requests.get(comic_detail_url, headers=headers, timeout=10)
        detail_response.raise_for_status()
        detail_soup = BeautifulSoup(detail_response.text, "html.parser")

        chapters_data = detail_soup.find_all('div', class_='eph-num')
        if not chapters_data:
            print(f"No chapters found for comic ID {comic_id}.")

        for chapter_item in chapters_data:
            chapter_name_tag = chapter_item.find('span', class_='chapternum')
            release_date_tag = chapter_item.find('span', class_='chapterdate')
            chapter_url_tag = chapter_item.find('a')

            chapter_name = chapter_name_tag.text.strip() if chapter_name_tag else "Unknown Chapter"
            release_date = release_date_tag.text.strip() if release_date_tag else "Unknown Date"
            chapter_url = chapter_url_tag.get('href') if chapter_url_tag else "No URL"

            # 插入章节数据到对应的子表
            insert_chapter_sql = f"""
            INSERT INTO [{child_table_name}] (chapter_name, release_date, chapter_url, comic_id)
            VALUES (?, ?, ?, ?);
            """
            try:
                cursor.execute(insert_chapter_sql, (chapter_name, release_date, chapter_url, comic_id))
                connection.commit()
                # print(f"  Inserted chapter '{chapter_name}' into {child_table_name}")
            except Exception as e:
                print(f"  Error inserting chapter '{chapter_name}' into {child_table_name}: {e}")
        time.sleep(1) # 礼貌性延时
    except requests.exceptions.RequestException as e:
        print(f"Error fetching chapter details for {comic_detail_url}: {e}")
    except Exception as e:
        print(f"Error creating table or inserting data for comic ID {comic_id}: {e}")

# 关闭数据库连接
connection.close()
print("Database operations completed and connection closed.")