如何用BeautifulSoup爬取汽车Trim与MSRP并导出至Google Sheets（支持多URL循环）

看起来你已经搭好了基础框架，接下来咱们一步步解决你的问题：从提取正确的Trim和MSRP，到实现批量爬取，再到正确导出数据。

1. 修正单个页面的数据提取逻辑 #

你的现有代码里overview = soup.find()没有指定任何定位条件，导致后续的trim提取是基于空对象。咱们先定位到正确的表格，再逐行提取Trim和对应的MSRP：

# 替换原爬虫部分的代码
URL = 'https://carbuzz.com/cars/tesla/model-3'
response = requests.get(URL)
response.raise_for_status()
soup = BeautifulSoup(response.text, 'lxml')

# 定位到trim和MSRP所在的表格
trim_table = soup.find('table', class_='car-sub-model-trim-levels-table')
if not trim_table:
    print(f"未找到目标表格: {URL}")
    exit()

# 遍历表格行，提取数据
data_rows = []
for row in trim_table.find_all('tr')[1:]:  # 跳过表头行
    # 提取Trim文本
    trim_cell = row.find('td', class_='car-sub-model-trim-levels-table__name')
    trim_name = trim_cell.find('a').get_text(strip=True) if trim_cell and trim_cell.find('a') else 'N/A'
    
    # 提取MSRP文本
    msrp_cell = row.find('td', class_='car-sub-model-trim-levels-table__msrp')
    msrp_value = msrp_cell.get_text(strip=True) if msrp_cell else 'N/A'
    
    data_rows.append([trim_name, msrp_value])

print(data_rows)  # 验证提取结果

这段代码会输出类似：

[['Model 3', '$46,990'], ['Long Range', '$54,990'], ['Performance', '$58,990']]

2. 实现多URL批量爬取逻辑 #

把需要爬取的URL放到一个列表里，用for循环遍历处理，同时添加time.sleep()避免请求过于频繁被网站拦截：

# 定义要爬取的URL列表
TARGET_URLS = [
    'https://carbuzz.com/cars/tesla/model-3',
    'https://carbuzz.com/cars/tesla/model-y',
    # 可以继续添加更多车型URL
]

all_data = []
for url in TARGET_URLS:
    print(f"正在爬取: {url}")
    try:
        response = requests.get(url)
        response.raise_for_status()
        soup = BeautifulSoup(response.text, 'lxml')
        
        trim_table = soup.find('table', class_='car-sub-model-trim-levels-table')
        if not trim_table:
            print(f"跳过: {url} 未找到目标表格")
            continue
            
        for row in trim_table.find_all('tr')[1:]:
            trim_cell = row.find('td', class_='car-sub-model-trim-levels-table__name')
            trim_name = trim_cell.find('a').get_text(strip=True) if trim_cell and trim_cell.find('a') else 'N/A'
            
            msrp_cell = row.find('td', class_='car-sub-model-trim-levels-table__msrp')
            msrp_value = msrp_cell.get_text(strip=True) if msrp_cell else 'N/A'
            
            all_data.append([trim_name, msrp_value])
            
        time.sleep(2)  # 每爬取一个页面暂停2秒
    except Exception as e:
        print(f"爬取 {url} 时出错: {str(e)}")
        continue

3. 正确导出数据至Google Sheets #

现在把收集到的all_data批量写入Google Sheets，注意使用append方法时确保数据格式正确：

# 写入表头（如果之前没写过的话）
sheet_header = [['Trim', 'MSRP']]
sheet.values().update(
    spreadsheetId=SAMPLE_SPREADSHEET_ID,
    range="caranddriver!A1",
    valueInputOption="USER_ENTERED",
    body={"values": sheet_header}
).execute()

# 批量写入数据
if all_data:
    sheet.values().append(
        spreadsheetId=SAMPLE_SPREADSHEET_ID,
        range="caranddriver!A2",
        valueInputOption="USER_ENTERED",
        body={"values": all_data},
        insertDataOption="INSERT_ROWS"  # 自动插入新行，避免覆盖已有数据
    ).execute()
    print(f"成功导出 {len(all_data)} 条数据至Google Sheets")
else:
    print("没有可导出的数据")

完整整合代码 #

把所有部分整合起来，就是最终的完整代码：

from googleapiclient.discovery import build
from google.oauth2 import service_account
from bs4 import BeautifulSoup
import requests
import time

# Google Sheets 认证配置
SERVICE_ACCOUNT_FILE = 'keys.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive']
creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
SAMPLE_SPREADSHEET_ID = 'PLACEHOLDER'  # 替换成你的表格ID
service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()

# 定义要爬取的目标URL列表
TARGET_URLS = [
    'https://carbuzz.com/cars/tesla/model-3',
    'https://carbuzz.com/cars/tesla/model-y',
    # 可添加更多URL
]

all_data = []

# 批量爬取数据
for url in TARGET_URLS:
    print(f"处理URL: {url}")
    try:
        response = requests.get(url)
        response.raise_for_status()
        soup = BeautifulSoup(response.text, 'lxml')
        
        # 定位Trim表格
        trim_table = soup.find('table', class_='car-sub-model-trim-levels-table')
        if not trim_table:
            print(f"警告: {url} 未找到目标表格")
            continue
        
        # 遍历表格行提取数据
        for row in trim_table.find_all('tr')[1:]:
            # 提取Trim名称
            trim_elem = row.find('td', class_='car-sub-model-trim-levels-table__name')
            trim_name = trim_elem.find('a').get_text(strip=True) if (trim_elem and trim_elem.find('a')) else 'N/A'
            
            # 提取MSRP
            msrp_elem = row.find('td', class_='car-sub-model-trim-levels-table__msrp')
            msrp = msrp_elem.get_text(strip=True) if msrp_elem else 'N/A'
            
            all_data.append([trim_name, msrp])
        
        time.sleep(2)  # 防反爬延迟
    except Exception as e:
        print(f"处理 {url} 出错: {str(e)}")
        continue

# 写入Google Sheets
if all_data:
    # 写入表头
    sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID,
        range="caranddriver!A1",
        valueInputOption="USER_ENTERED",
        body={"values": [['Trim', 'MSRP']]}
    ).execute()
    
    # 写入数据
    sheet.values().append(
        spreadsheetId=SAMPLE_SPREADSHEET_ID,
        range="caranddriver!A2",
        valueInputOption="USER_ENTERED",
        body={"values": all_data},
        insertDataOption="INSERT_ROWS"
    ).execute()
    print(f"完成！共导出 {len(all_data)} 条数据")
else:
    print("未收集到任何数据")

注意事项 #

• 确保keys.json文件路径正确，并且Google Sheets API已启用，表格共享给了服务账号的邮箱
• 如果网站结构有变化，需要调整CSS选择器（比如car-sub-model-trim-levels-table__msrp这类类名）
• 可以根据需要增加更多异常处理逻辑，比如处理网络超时等情况

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