如何解决EAN码匹配结果存在错误数据的问题？

我编写了一段代码，功能为从Excel文件读取EAN码列表，通过请求网页检查是否存在data-offer-ean属性与请求EAN码一致的产品，匹配则加入匹配列表，不匹配则加入不匹配列表。但目前结果中存在大量实际未在页面上的错误代码，附上代码寻求解决建议：

import pandas as pd
import requests
from bs4 import BeautifulSoup
from tqdm import tqdm
import openpyxl

df = pd.read_excel('eanBR.xlsx', dtype="int64")
mylist = df['ean'].tolist()
dopasowane = []
niedopasowane = []

def get_ean(symbol):
    response = requests.get(f"https://www.bricomarche.pl/search?query%5Bquerystring%5D={symbol}")
    soup = BeautifulSoup(response.content, "html.parser")
    div = soup.find('div', attrs={'data-offer-ean': True})
    if div:
        ean = div['data-offer-ean']
        if str(ean) == str(symbol):
            dopasowane.append(ean)
        else:
            niedopasowane.append(ean)
    else:
        niedopasowane.append(symbol)
    return ean

bar = tqdm(total=len(mylist), desc='Pobieranie danych', bar_format='{l_bar}{bar}| {n_fmt}/{total_fmt} [{percent:.1f}% - {remaining}]')

for item in mylist:
    try:
        ean_result = get_ean(item)
        bar.update(1)
    except Exception as e:
        bar.update(1)

bar.close()

wb = openpyxl.Workbook()
ws_dopasowane = wb.active
ws_dopasowane.title = 'Dopasowane'
ws_niedopasowane = wb.create_sheet('Niedopasowane')

for idx, ean in enumerate(dopasowane, start=1):
    ws_dopasowane.cell(row=idx, column=1, value=ean)

for idx, ean in enumerate(niedopasowane, start=1):
    ws_niedopasowane.cell(row=idx, column=1, value=ean)

wb.save('wynik.xlsx')

问题分析与修复建议 #

1. 仅匹配第一个产品，忽略其他结果

原代码用soup.find()只获取页面中第一个带data-offer-ean的div，但搜索页面可能返回多个产品，第一个不匹配不代表没有匹配项。需要遍历所有匹配元素：

# 替换原div查找逻辑
product_divs = soup.find_all('div', attrs={'data-offer-ean': True})
matched = False
target_ean = str(symbol)
for div in product_divs:
    ean = div['data-offer-ean']
    if ean == target_ean:
        dopasowane.append(target_ean)
        matched = True
        break
if not matched:
    niedopasowane.append(target_ean)

2. 异常处理不完善，存在未定义变量风险

原代码中当div不存在时，return ean会抛出NameError，且异常捕获后未做记录，无法排查请求失败原因。修改后：

def get_ean(symbol):
    target_ean = str(symbol)
    try:
        headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36'
        }
        # 添加请求头避免被反爬拦截，设置超时
        response = requests.get(f"https://www.bricomarche.pl/search?query%5Bquerystring%5D={target_ean}", headers=headers, timeout=10)
        response.raise_for_status()  # 触发HTTP错误异常
        soup = BeautifulSoup(response.content, "html.parser")
        product_divs = soup.find_all('div', attrs={'data-offer-ean': True})
        matched = False
        for div in product_divs:
            ean = div['data-offer-ean']
            if ean == target_ean:
                dopasowane.append(target_ean)
                matched = True
                break
        if not matched:
            niedopasowane.append(target_ean)
    except Exception as e:
        print(f"处理EAN {target_ean}时出错: {str(e)}")
        niedopasowane.append(target_ean)

3. 未处理反爬机制

频繁无标识请求易被网站拦截，返回空页面或错误内容导致匹配错误。需添加请求头并控制请求频率：

import time

# 在循环中添加延迟
for item in mylist:
    try:
        get_ean(item)
        bar.update(1)
        time.sleep(1)  # 每秒请求一次，降低被拦截概率
    except Exception as e:
        bar.update(1)
        time.sleep(1)

4. EAN格式统一处理

Excel读取整数会丢失前导零，导致和网页返回的字符串格式不匹配，需直接读取为字符串：

# 读取时保留字符串格式
df = pd.read_excel('eanBR.xlsx', dtype=str)
mylist = df['ean'].tolist()

修复后的完整代码 #

import pandas as pd
import requests
from bs4 import BeautifulSoup
from tqdm import tqdm
import openpyxl
import time

# 读取EAN时保留字符串格式，避免前导零丢失
df = pd.read_excel('eanBR.xlsx', dtype=str)
mylist = df['ean'].tolist()
dopasowane = []
niedopasowane = []

def get_ean(symbol):
    target_ean = symbol.strip()  # 去除可能的空格
    try:
        headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36'
        }
        response = requests.get(
            f"https://www.bricomarche.pl/search?query%5Bquerystring%5D={target_ean}",
            headers=headers,
            timeout=10
        )
        response.raise_for_status()
        soup = BeautifulSoup(response.content, "html.parser")
        # 遍历所有带data-offer-ean的产品
        product_divs = soup.find_all('div', attrs={'data-offer-ean': True})
        matched = False
        for div in product_divs:
            page_ean = div['data-offer-ean'].strip()
            if page_ean == target_ean:
                dopasowane.append(target_ean)
                matched = True
                break
        if not matched:
            niedopasowane.append(target_ean)
    except Exception as e:
        print(f"EAN {target_ean} 处理失败: {str(e)}")
        niedopasowane.append(target_ean)

bar = tqdm(total=len(mylist), desc='Pobieranie danych', bar_format='{l_bar}{bar}| {n_fmt}/{total_fmt} [{percent:.1f}% - {remaining}]')

for item in mylist:
    get_ean(item)
    bar.update(1)
    time.sleep(1)  # 控制请求频率

bar.close()

# 写入结果
wb = openpyxl.Workbook()
ws_dopasowane = wb.active
ws_dopasowane.title = 'Dopasowane'
ws_niedopasowane = wb.create_sheet('Niedopasowane')

for idx, ean in enumerate(dopasowane, start=1):
    ws_dopasowane.cell(row=idx, column=1, value=ean)

for idx, ean in enumerate(niedopasowane, start=1):
    ws_niedopasowane.cell(row=idx, column=1, value=ean)

wb.save('wynik.xlsx')

内容的提问来源于stack exchange，提问作者Łukasz Tarnowski