如何通过解析URL列表创建SQL数据库并实现数据筛选查询？

You're on the right track! Let's break down the full implementation from URL generation to storing extracted text in an SQL database, with practical code examples and flexible alternatives.

1. Refine Your URL List Generation #

Your current code works, but here's a polished version with better practices to avoid duplicates and ensure consistency:

# Generate URLs and save to file.txt
output_file = "file.txt"

# Use 'w' mode to overwrite (prevents duplicate URLs on repeated runs)
with open(output_file, "w") as f_out:
    for i in range(100, 120):
        url = f"https://www.website.com/{i}"  # Add HTTPS for reliable fetching
        print(url)
        f_out.write(f"{url}\n")

Pro Tip: Using https:// ensures compatibility with most fetch tools and avoids redirect issues.

2. Fetch Website Content #

wget -i file.txt is a quick way to download pages, but using Python's requests library gives you more control over errors, headers, and avoiding unnecessary assets. First, install it:

pip install requests

Then, a robust fetch function:

import requests
from requests.exceptions import RequestException

def fetch_page(url):
    try:
        # Add a timeout and custom user-agent to avoid being blocked
        headers = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"}
        response = requests.get(url, headers=headers, timeout=10)
        response.raise_for_status()  # Trigger error for HTTP 4xx/5xx codes
        return response.text
    except RequestException as e:
        print(f"Failed to fetch {url}: {str(e)}")
        return None

3. Extract Clean Text from HTML #

Raw HTML isn't useful for queries—you need to strip out scripts, styles, and non-content elements. Use BeautifulSoup for this:

pip install beautifulsoup4 lxml

Text extraction function:

from bs4 import BeautifulSoup

def extract_clean_text(html):
    if not html:
        return ""
    soup = BeautifulSoup(html, "lxml")
    # Remove non-content elements
    for element in soup(["script", "style", "nav", "footer", "aside", "ad"]):
        element.decompose()
    # Get properly formatted text with no extra whitespace
    clean_text = soup.get_text(strip=True, separator=" ")
    return clean_text

4. Set Up Your SQL Database #

Let's start with SQLite (lightweight, no server required, ideal for small-to-medium datasets). For larger scales, we'll also cover MySQL/PostgreSQL.

SQLite Setup (Built-in to Python) #

Create a database and table:

import sqlite3
from datetime import datetime

def init_sqlite_db():
    conn = sqlite3.connect("website_texts.db")
    cursor = conn.cursor()
    # Create table with essential fields
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS website_content (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            url TEXT UNIQUE NOT NULL,
            extracted_text TEXT NOT NULL,
            fetch_timestamp DATETIME NOT NULL,
            status TEXT NOT NULL DEFAULT 'success'
        )
    """)
    conn.commit()
    conn.close()

MySQL/PostgreSQL Alternative #

For client-server databases, here's a PostgreSQL example using psycopg2:

pip install psycopg2-binary

import psycopg2

def init_postgres_db():
    conn = psycopg2.connect(
        dbname="your_db_name",
        user="your_username",
        password="your_password",
        host="localhost"
    )
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS website_content (
            id SERIAL PRIMARY KEY,
            url VARCHAR(255) UNIQUE NOT NULL,
            extracted_text TEXT NOT NULL,
            fetch_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            status VARCHAR(20) NOT NULL DEFAULT 'success'
        )
    """)
    conn.commit()
    conn.close()

5. Combine All Components into a Pipeline #

Tie everything together to fetch, extract, and store text:

import time

def main():
    init_sqlite_db()  # Switch to init_postgres_db() if using PostgreSQL
    url_file = "file.txt"
    
    with open(url_file, "r") as f_in:
        urls = [line.strip() for line in f_in if line.strip()]
    
    conn = sqlite3.connect("website_texts.db")
    cursor = conn.cursor()
    
    for url in urls:
        html = fetch_page(url)
        if not html:
            # Log failed fetch
            cursor.execute("""
                INSERT OR IGNORE INTO website_content (url, extracted_text, fetch_timestamp, status)
                VALUES (?, ?, ?, ?)
            """, (url, "", datetime.now(), "failed"))
            time.sleep(1)  # Add delay to avoid rate limits
            continue
        
        clean_text = extract_clean_text(html)
        # Insert or update existing URL (prevents duplicates)
        cursor.execute("""
            INSERT OR REPLACE INTO website_content (url, extracted_text, fetch_timestamp, status)
            VALUES (?, ?, ?, ?)
        """, (url, clean_text, datetime.now(), "success"))
        time.sleep(1)
    
    conn.commit()
    conn.close()
    print("Pipeline completed successfully!")

if __name__ == "__main__":
    main()

6. Query Your Database #

Once data is stored, run queries to filter and retrieve text. Here's an example for SQLite:

def search_for_keyword(keyword):
    conn = sqlite3.connect("website_texts.db")
    cursor = conn.cursor()
    cursor.execute("""
        SELECT url, extracted_text FROM website_content
        WHERE extracted_text LIKE ? AND status = 'success'
    """, (f"%{keyword}%",))
    results = cursor.fetchall()
    conn.close()
    return results

# Example: Find all pages containing "sustainable"
matches = search_for_keyword("sustainable")
for url, text in matches:
    print(f"URL: {url}\nText Snippet: {text[:200]}...\n")

Key Enhancements to Consider #

• Rate Limiting: Adjust time.sleep() to match the target website's robots.txt rules.
• Multiprocessing: Use concurrent.futures to fetch multiple pages at once for large URL lists.
• Logging: Add a logging system to track errors and progress more effectively.

内容的提问来源于stack exchange，提问作者HowRuBro