Python爬取pickels.com.au时CSV按条件删除更新行问题求解

现有代码核心错误 #

• 价格对比逻辑错误：拿数字类型的sale_price和每行第0位的main_title做等值判断，完全匹配不到正确结果
• 内存操作不映射磁盘文件：调用auction_reader.clear()只是清空了内存中读取到的列表，没有对磁盘上的CSV文件做任何修改
• 文件IO逻辑混乱：同时用a+和r+两个句柄操作同一个文件，指针位置混乱会导致写入重复、乱码等问题
• 性能极低：每爬取一条数据就读取一次全量CSV文件，IO开销极大

最优实现方案 #

核心逻辑是用内存字典做去重/更新缓存，全量爬取完成后一次性写回磁盘，既保证逻辑清晰，性能也最优：

1. 启动时先读取现有CSV所有数据，以链接为唯一键存入字典，O(1)时间复杂度就能完成查重
2. 爬取所有新的拍卖数据，遍历更新字典条目
3. 爬取完成后一次性把字典中所有值重新写入CSV文件覆盖原文件

修正后代码 #

import requests
from scrapy.selector import Selector
import csv
import re
from os import path

# 初始化数据缓存字典，key为商品链接，value为完整行数据
data_dict = {}
csv_path = 'pickels_dataset.csv'
# 先读取已有CSV的现有数据
if path.exists(csv_path):
    with open(csv_path, 'r', newline='', encoding='utf-8') as f:
        reader = csv.reader(f)
        for row in reader:
            if len(row) >= 10: # 跳过格式错误行
                link = row[9]
                data_dict[link] = row

# 爬取新数据
live_auctions_api = 'https://www.pickles.com.au/PWR-Web/services/api/sales/future'
api_request = requests.get(url=live_auctions_api)
for auctions in api_request.json():
    auction_link = auctions.get('viewSaleListingLink')
    if 'cars/item/search/-/listing/listSaleItems/' in auction_link:
        auction_request = requests.get(url=auction_link)
        response = Selector(text=auction_request.text)

        sales_id_re = response.xpath('//script[contains(text(), "Product_Type_Sequence")]/text() | //script[contains(text(), "lot_number_suffix_sequence")]/text()').get()
        sales_id = re.findall(r'"Product_Type_Sequence";var n="(.*?)"', sales_id_re) or re.findall(r'"lot_number_suffix_sequence";var n="(.*?)"', sales_id_re)
        if sales_id == []:
            continue
        auction_sale_link = f'https://www.pickles.com.au/v4/caradvert/saleid-{sales_id[0]}-public?count=true&inav=Car%7Cbc%7Cha%7Cu&q=(And.ProductType.Vehicles._.Year.range(2010..2021).)&sr=%7Clot_number_suffix_sequence%7C0%7C30'
        auction_sale_link_requests = requests.get(url=auction_sale_link)

        auctions_data = auction_sale_link_requests.json().get('SearchResults')
        if auctions_data == []:
            print("NO RESULTS")
        for auction_data in auctions_data:
            if int(auction_data.get('MinimumBid')) > 0:
                ids = auction_data.get('TargetId')
                main_title = auction_data.get('Title')
                short_title = str(auction_data.get('Year')) + ' ' + str(auction_data.get('Make')) + ' ' + str(auction_data.get('Model'))
                make = auction_data.get('Make')
                model = auction_data.get('Model')
                variant = auction_data.get('Series')
                transmission = auction_data.get('Transmission')
                odometer = auction_data.get('Odometer')
                state = auction_data.get('Location').get('State')
                sale_price = auction_data.get('MinimumBid')
                formatted_price = "${:,.2f}".format(sale_price).strip()
                link_path = main_title.replace(' ', '-').replace('/', '-').replace(',', '-') + '/' + str(ids)
                link = f'https://www.pickles.com.au/cars/item/-/details/{link_path}'
                sale_date = auction_data.get('SaleEndString')
                
                auction_values = [
                    main_title, short_title, make, 
                    model, variant, transmission, odometer, 
                    state, formatted_price, 
                    link, sale_date
                ]

                # 更新字典逻辑处理
                if link in data_dict:
                    # 对比价格，每行第8位是格式化后的价格
                    old_price = data_dict[link][8]
                    if old_price != formatted_price:
                        data_dict[link] = auction_values
                        print(f'商品{link}价格更新完成')
                    else:
                        print(f'商品{link}数据无变化，跳过')
                else:
                    data_dict[link] = auction_values
                    print(f'新增商品{link}')

# 所有数据处理完成后一次性写回文件
with open(csv_path, 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerows(data_dict.values())
print('全量数据更新完成')

注意事项 #

• 如果CSV文件有表头，需要单独读取表头，写回的时候先写表头再写数据
• 数据量超过10万行的话，可以考虑用sqlite代替内存字典，避免内存占用过高

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