Azure Blob读取Excel至Pandas时间歇性SSL证书验证失败求助

我尝试将Azure Blob存储中的Excel文件读取到Pandas DataFrame，代码如下：

blob_service_client = BlobServiceClient(account_url="xxxxxxx", credential='yyyyyyy')
blob_client = blob_service_client.get_blob_client('aaaa', blob = 'abc.xlsx')
blob = blob_client.download_blob().readall()
df = pd.read_excel(blob, engine = "openpyxl")

这段代码间歇性失败，报错信息如下：

ServiceRequestError                       Traceback (most recent call last)
Cell In[69], line 4
      2blob_service_client = BlobServiceClient(account_url="xxxxxxx", credential='yyyyyyy')
      3 blob_client = blob_service_client.get_blob_client('aaaa', blob = 'abc.xlsx')
----> 4 blob = blob_client.download_blob().readall()
      5 df = pd.read_excel(blob, engine = "openpyxl")
      6 cc = {df.iloc[i, 0] : df.iloc[i, 1] for i in range(df.shape[0])}

File ~\AppData\Roaming\Python\Python311\site-packages\azure\core\tracing\decorator.py:78, in distributed_trace..decorator..wrapper_use_tracer(*args, **kwargs)
     76 span_impl_type = settings.tracing_implementation()
     77 if span_impl_type is None:
---> 78     return func(*args, **kwargs)
     80 # Merge span is parameter is set, but only if no explicit parent are passed
     81 if merge_span and not passed_in_parent:

File ~\AppData\Roaming\Python\Python311\site-packages\azure\storage\blob\_blob_client.py:942, in BlobClient.download_blob(self, offset, length, encoding, **kwargs)
    848 """Downloads a blob to the StorageStreamDownloader. The readall() method must
    849 be used to read all the content or readinto() must be used to download the blob into
    850 a stream. Using chunks() returns an iterator which allows the user to iterate over the content in chunks.
   (...)
    935         :caption: Download a blob.
    936 """
    937 options = self._download_blob_options(
    938     offset=offset,
...
--> 386     raise error
    387 if _is_rest(request):
    388     from azure.core.rest._requests_basic import RestRequestsTransportResponse

ServiceRequestError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:992)

已检查VPN连接，问题仍存在。

1. 处理SSL证书验证 #

报错提示自签名证书在证书链中，可通过以下方式处理：

• 指定信任的CA证书：将自签名证书保存为本地文件（比如ca_cert.pem），创建客户端时指定证书路径：

from azure.storage.blob import BlobServiceClient

# 指定CA证书路径
blob_service_client = BlobServiceClient(
    account_url="xxxxxxx",
    credential='yyyyyyy',
    connection_verify="path/to/ca_cert.pem"
)

• 临时禁用验证（仅测试环境）：不推荐生产环境使用，但可快速排查问题：

blob_service_client = BlobServiceClient(
    account_url="xxxxxxx",
    credential='yyyyyyy',
    connection_verify=False
)

2. 添加重试机制 #

间歇性错误可能由网络波动导致，配置Azure SDK的重试策略：

from azure.storage.blob import BlobServiceClient
from azure.core.pipeline.policies import RetryPolicy

# 配置重试策略：最多重试5次，每次间隔递增
retry_policy = RetryPolicy(max_retries=5, retry_backoff_factor=1)

blob_service_client = BlobServiceClient(
    account_url="xxxxxxx",
    credential='yyyyyyy',
    client_options={"retry_policy": retry_policy}
)

3. 系统层面信任证书 #

将自签名证书导入操作系统的信任证书库：

• Windows：通过「证书管理器」导入到「受信任的根证书颁发机构」
• macOS：使用「钥匙串访问」将证书设为信任
• Linux：将证书复制到/usr/local/share/ca-certificates/，然后执行update-ca-certificates

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