如何实现Python持续运行拍卖网站爬虫的数据持久化与中断后重访问？

Hey there! Let's fix your progress loss issue once and for all. The core problem right now is that your links only live in memory—so if the script crashes, you lose everything. Your earlier text file attempt probably failed due to flawed read/write logic (like not properly loading existing links or tracking their status). Below are the best solutions, ordered from most robust to quickest to implement:

方案1：使用SQLite数据库（最推荐） #

SQLite is a lightweight, file-based database that requires no extra server setup—perfect for single-script crawlers. We'll create a table to store links along with their processing status (unprocessed/checked/sold), so you can pick up right where you left off after a crash.

步骤1：Initialize the Database #

Add this at the top of your script to set up the database:

import sqlite3

# Connect to SQLite database (creates the file if it doesn't exist)
conn = sqlite3.connect('auction_links.db')
cursor = conn.cursor()

# Create a table to track links and their status
cursor.execute('''
CREATE TABLE IF NOT EXISTS auction_links (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    link TEXT UNIQUE NOT NULL,
    status TEXT DEFAULT 'unprocessed', -- Options: unprocessed/checked/sold
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()

步骤2：Save New Links to the Database #

Replace your pickles_links_list.append(each_auction_link) line with this to store links in the database (ignoring duplicates):

# Add link to database (skip if it already exists)
try:
    cursor.execute('''
    INSERT OR IGNORE INTO auction_links (link) VALUES (?)
    ''', (each_auction_link,))
    conn.commit()
    print({'Added to DB': each_auction_link})
except Exception as e:
    print(f'Error saving link: {e}')

步骤3：Load Links to Process #

Replace unique_links_list = list(set(pickles_links_list)) with this to fetch only unprocessed or previously checked (but unsold) links:

# Fetch links that need processing
cursor.execute('''
SELECT link FROM auction_links WHERE status IN ('unprocessed', 'checked')
''')
unique_links_list = [row[0] for row in cursor.fetchall()]
print({'Total links to process': f'*** {len(unique_links_list)} ***'})

步骤4：Update Link Status After Processing #

Modify your link processing loop to update the database status once you check a link:

for each_link in unique_links_list:
    print({'Scraping': each_link})
    random_delay = randint(1, 7)
    print(f'*** Sleeping for [{random_delay}] seconds ***')
    sleep(random_delay)
    each_auction_request = requests.get(each_link, headers=headers)
    response = Selector(text=each_auction_request.text)
    current_status = response.xpath('//h6[@class="mt-2"]/text()[2]').get()

    if current_status == 'This item has been sold. ':
        # Your existing code to scrape sale data and write to CSV goes here...
        
        # Mark link as sold in database
        cursor.execute('''
        UPDATE auction_links SET status = 'sold', updated_at = CURRENT_TIMESTAMP WHERE link = ?
        ''', (each_link,))
        conn.commit()
        print('*** Sold item found and added to CSV ***')
    else:
        # Mark link as checked (but not sold)
        cursor.execute('''
        UPDATE auction_links SET status = 'checked', updated_at = CURRENT_TIMESTAMP WHERE link = ?
        ''', (each_link,))
        conn.commit()
        print('*** Item not sold yet ***')

步骤5：Clean Up Connection #

Add this at the end of your script (or use a context manager) to close the database connection properly:

conn.close()

方案2：Improved Text/JSON Storage (Quick Fix) #

If you want to avoid databases, fix your text file logic with JSON to track link statuses. This is simpler but less robust than SQLite for large datasets.

步骤1：Set Up JSON Storage #

Add these helper functions to handle JSON read/write:

import json
import os
from datetime import datetime

LINK_STORAGE = 'auction_links.json'

# Initialize storage file if it doesn't exist
def init_storage():
    if not os.path.exists(LINK_STORAGE):
        with open(LINK_STORAGE, 'w', encoding='utf-8') as f:
            json.dump([], f)

# Load existing links from storage
def load_links():
    with open(LINK_STORAGE, 'r', encoding='utf-8') as f:
        return json.load(f)

# Save updated links to storage
def save_links(links):
    with open(LINK_STORAGE, 'w', encoding='utf-8') as f:
        json.dump(links, f, indent=2)

# Initialize storage on script start
init_storage()

步骤2：Save New Links #

When scraping new links, add them to the JSON file if they don't already exist:

existing_links = load_links()
existing_urls = [link['url'] for link in existing_links]

# Your existing code to generate each_auction_link goes here...
if each_auction_link not in existing_urls:
    existing_links.append({
        'url': each_auction_link,
        'status': 'unprocessed',
        'created_at': datetime.now().isoformat()
    })
    save_links(existing_links)
    print({'Added to storage': each_auction_link})

步骤3：Process Links from Storage #

Fetch only unprocessed/checked links and update their status after processing:

links_to_process = [link for link in existing_links if link['status'] in ['unprocessed', 'checked']]
print({'Total links to process': f'*** {len(links_to_process)} ***'})

for link_item in links_to_process:
    each_link = link_item['url']
    # Your existing scraping logic goes here...
    
    if current_status == 'This item has been sold. ':
        # Your CSV write code goes here...
        link_item['status'] = 'sold'
        link_item['updated_at'] = datetime.now().isoformat()
        save_links(existing_links)
    else:
        link_item['status'] = 'checked'
        link_item['updated_at'] = datetime.now().isoformat()
        save_links(existing_links)

为什么SQLite是最优选择？ #

• Data Integrity: Supports transactions to avoid corrupted data if the script crashes mid-write.
• Efficiency: Lets you quickly filter links by status without loading everything into memory.
• Scalability: Easy to add more fields (like retry counts, last scraped time) later.
• Reliability: More stable than text/JSON files, which can break if formatted incorrectly.

Extra Tips #

• Add a retry mechanism for failed requests (mark links as failed and retry later).
• Periodically clean up sold links from the database if you don't need to keep them.
• Use context managers (with sqlite3.connect(...) as conn:) to auto-close database connections.

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