使用Python下载EMCSG网站Excel文件遇403及SSL证书问题求解

问题场景 #

尝试从EMCSG平台下载电力价格数据，浏览器访问API URL可正常获取文件，但用Python调用urllib或requests时遇到两类错误：

• urllib调用返回HTTP Error 403: Forbidden，被反爬拦截
• requests调用返回SSL证书验证失败错误

原测试代码：

import pandas as pd
import numpy as np
import datetime 
import pytz
import urllib.request

def download_file(file_name):
    url = f'https://www.nems.emcsg.com/api/sitecore/DataSync/DataDownload?value=10&fromDate={file_name}&toDate={file_name}&tpcValue=1'
    file_path = f'raw data/omie_spain/{file_name}'
    try:
        urllib.request.urlretrieve(url, file_path)
        print(f'Downloaded {file_name} successfully!')
        return file_path
    except urllib.error.URLError as e:
        print("Download failed:", e.reason)

if __name__ == '__main__':
    file_name_date = datetime.date(year=2023, month=7, day=20) 
    file_name_date = file_name_date.strftime("%Y-%m-%d")
    file_name_time = datetime.datetime.now().strftime("%H%M")
    print(file_name_time)
    print(file_name_date)

    raw_file_name, formatted_file_name = f'{file_name_date}', f'{file_name_date}{file_name_time}.csv'
    raw_file_path = download_file(raw_file_name)

错误详情 #

403 Forbidden错误（urllib调用） #

1653
2023-07-20
Download failed: Forbidden

完整栈追踪：

1651
2023-07-20
Traceback (most recent call last):
    urllib.request.urlopen(url,context=context)
  File "C:\Users\XX\AppData\Local\Programs\Python\Python310\lib\urllib\request.py", line 216, in urlopen
    return opener.open(url, data, timeout)
  File "C:\Users\XX\AppData\Local\Programs\Python\Python310\lib\urllib\request.py", line 525, in open
    response = meth(req, response)
  File "C:\Users\XX\AppData\Local\Programs\Python\Python310\lib\urllib\request.py", line 634, in http_response
    response = self.parent.error(
  File "C:\Users\XX\AppData\Local\Programs\Python\Python310\lib\urllib\request.py", line 563, in error
    return self._call_chain(*args)
  File "C:\Users\XX\AppData\Local\Programs\Python\Python310\lib\urllib\request.py", line 496, in _call_chain
    result = func(*args)
  File "C:\Users\XX\AppData\Local\Programs\Python\Python310\lib\urllib\request.py", line 643, in http_error_default
    raise HTTPError(req.full_url, code, msg, hdrs, fp)
urllib.error.HTTPError: HTTP Error 403: Forbidden

SSL证书验证错误（requests调用） #

requests.exceptions.SSLError: HTTPSConnectionPool(host='www.nems.emcsg.com', port=443): Max retries exceeded with url: /api/sitecore/DataSync/DataDownload?value=10&fromDate=2023-07-21&toDate=2023-07-21&tpcValue=1 (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:997)')))

解决方案 #

1. 绕过403反爬：添加浏览器请求头 #

网站通过检测请求头识别爬虫，添加User-Agent模拟浏览器请求，必要时补充Referer指定来源页面。

修改后的requests版本代码

import requests
import datetime

def download_file(file_name):
    url = f'https://www.nems.emcsg.com/api/sitecore/DataSync/DataDownload?value=10&fromDate={file_name}&toDate={file_name}&tpcValue=1'
    file_path = f'raw data/omie_spain/{file_name}'
    
    # 模拟Chrome浏览器请求头
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36',
        'Referer': 'https://www.nems.emcsg.com/nems-prices'
    }
    
    try:
        # 临时忽略SSL证书验证
        response = requests.get(url, headers=headers, verify=False)
        response.raise_for_status()  # 检查请求是否成功
        
        with open(file_path, 'wb') as f:
            f.write(response.content)
        
        print(f'Downloaded {file_name} successfully!')
        return file_path
    except requests.exceptions.RequestException as e:
        print("Download failed:", str(e))

if __name__ == '__main__':
    file_name_date = datetime.date(year=2023, month=7, day=20).strftime("%Y-%m-%d")
    file_name_time = datetime.datetime.now().strftime("%H%M")
    print(file_name_time)
    print(file_name_date)

    raw_file_name, formatted_file_name = f'{file_name_date}', f'{file_name_date}{file_name_time}.csv'
    raw_file_path = download_file(raw_file_name)

修改后的urllib版本代码

import urllib.request
import datetime

def download_file(file_name):
    url = f'https://www.nems.emcsg.com/api/sitecore/DataSync/DataDownload?value=10&fromDate={file_name}&toDate={file_name}&tpcValue=1'
    file_path = f'raw data/omie_spain/{file_name}'
    
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.0.0 Safari/537.36',
        'Referer': 'https://www.nems.emcsg.com/nems-prices'
    }
    
    req = urllib.request.Request(url, headers=headers)
    
    try:
        # 临时忽略SSL证书验证
        import ssl
        context = ssl._create_unverified_context()
        with urllib.request.urlopen(req, context=context) as response:
            with open(file_path, 'wb') as f:
                f.write(response.read())
        
        print(f'Downloaded {file_name} successfully!')
        return file_path
    except urllib.error.URLError as e:
        print("Download failed:", e.reason)

if __name__ == '__main__':
    file_name_date = datetime.date(year=2023, month=7, day=20).strftime("%Y-%m-%d")
    file_name_time = datetime.datetime.now().strftime("%H%M")
    print(file_name_time)
    print(file_name_date)

    raw_file_name, formatted_file_name = f'{file_name_date}', f'{file_name_date}{file_name_time}.csv'
    raw_file_path = download_file(raw_file_name)

2. 解决SSL证书问题（安全方案） #

忽略证书验证存在安全风险，建议手动导入网站的CA证书：

1. 浏览器访问目标API，导出站点的SSL证书
2. 在代码中指定证书路径：
   • requests：response = requests.get(url, headers=headers, verify="/path/to/certificate.crt")
   • urllib：创建带证书的SSL上下文

注意事项 #

• 控制请求频率，避免触发更严格的反爬机制
• 定期更新User-Agent，避免被识别为过时浏览器

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