网站检测代码终端运行正常但无法写入Excel文件求助

代码在终端可正常输出网站状态，但无法将结果写入Result.xlsx，相关代码如下：

statuses = {
    200: "200, Website Available",
    301: "301, Permanent Redirect",
    302: "302, Temporary Redirect",
    403: "403, server understands the request but refuses to authorize it",
    404: "404, Not Found",
    500: "500, Internal Server Error",
    503: "503, Service Unavailable",
    509: "509, server has reached its bandwidth allowance",
    521: "521, Web server is down",
    522: "522, Connection timed out"
}
 
for url in website_url:
    
    web_response = requests.get(url)
    
    try:
        print(url, statuses[web_response.status_code])
        data = {
            'webphish' : urlgrab,
            'status' : statuses[web_response.status_code]
        }

        dfexcel = pd.DataFrame(data)

        with pd.ExcelWriter('Result.xlsx') as excel_writer:
            dfexcel.to_excel(excel_writer, sheet_name='Result', index=False)
 
    except:
        print(url, statuses[web_response.status_code])

问题分析 #

• 文件被重复覆盖：每次循环都创建新的ExcelWriter，会覆盖之前的文件，最终只会保留最后一条检测结果
• 未定义变量：urlgrab未赋值，应该使用当前循环的url变量
• 异常处理不当：宽泛的except捕获所有异常但不输出错误信息，无法定位写入失败的原因；且requests.get()可能抛出的异常（如连接超时、DNS解析失败）未被捕获
• DataFrame构造错误：单条数据构造DataFrame时，字典的每个值应该是列表，否则会导致数据结构异常

修正后的代码 #

import requests
import pandas as pd

statuses = {
    200: "200, Website Available",
    301: "301, Permanent Redirect",
    302: "302, Temporary Redirect",
    403: "403, server understands the request but refuses to authorize it",
    404: "404, Not Found",
    500: "500, Internal Server Error",
    503: "503, Service Unavailable",
    509: "509, server has reached its bandwidth allowance",
    521: "521, Web server is down",
    522: "522, Connection timed out"
}

# 初始化列表存储所有检测结果
results = []

for url in website_url:
    try:
        web_response = requests.get(url, timeout=10)  # 添加超时防止卡住
        status_text = statuses.get(web_response.status_code, f"Unknown status: {web_response.status_code}")
        print(url, status_text)
        # 将当前结果添加到列表
        results.append({
            'webphish': url,
            'status': status_text
        })
    except requests.exceptions.RequestException as e:
        # 捕获请求相关异常并输出
        error_msg = f"{url} - Request failed: {str(e)}"
        print(error_msg)
        results.append({
            'webphish': url,
            'status': error_msg
        })
    except Exception as e:
        # 捕获其他未知异常
        error_msg = f"{url} - Unexpected error: {str(e)}"
        print(error_msg)
        results.append({
            'webphish': url,
            'status': error_msg
        })

# 所有检测完成后，一次性写入Excel
if results:
    dfexcel = pd.DataFrame(results)
    with pd.ExcelWriter('Result.xlsx') as excel_writer:
        dfexcel.to_excel(excel_writer, sheet_name='Result', index=False)
    print("检测结果已成功写入Result.xlsx")
else:
    print("没有检测结果可写入")

关键修正点 #

• 用列表收集所有结果，最后一次性写入Excel，避免文件被覆盖
• 修正urlgrab为url，解决未定义变量问题
• 为requests.get()添加超时，并捕获请求相关异常，同时记录错误信息到Excel
• 使用statuses.get()处理未定义的状态码，避免KeyError
• 仅在有结果时才执行写入操作，避免创建空文件

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