Python连接Snowflake遇UnpicklingError错误求助

问题情况 #

之前正常运行的Snowflake Python连接代码，在系统崩溃强制重启后出现错误。已尝试重装Anaconda、硬编码连接参数（排除.env文件影响），但问题依旧。

出错代码 #

import snowflake.connector #pip install snowflake-connector-python
#从本地.env文件获取环境变量
cnx = snowflake.connector.connect(
    user=os.getenv('USER'),
    password=os.getenv('PASSWORD'),
    account=os.getenv('ACCOUNT'),
    warehouse=os.getenv('WAREHOUSE'), 
    database=db,
    schema=schema 
)

完整错误信息 #

---------------------------------------------------------------------------
UnpicklingError                           Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_18464\4059644960.py in <module>
     23 db='DB_SANDBOX'
     24 schema='PUBLIC'
---> 25 cnx = snowflake.connector.connect(user=os.getenv('USER'),password=os.getenv('PASSWORD'),account=os.getenv('ACCOUNT'),warehouse=os.getenv('WAREHOUSE'), database=db,schema=schema )
     26 
     27 query ='''

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\__init__.py in Connect(**kwargs)
     49 
     50 def Connect(**kwargs) -> SnowflakeConnection:
---> 51     return SnowflakeConnection(**kwargs)
     52 
     53 

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\connection.py in __init__(self, **kwargs)
    295         self.converter = None
    296         self.__set_error_attributes()
---> 297         self.connect(**kwargs)
    298         self._telemetry = TelemetryClient(self._rest)
    299 

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\connection.py in connect(self, **kwargs)
    548                 connection_diag.generate_report()
    549         else:
---> 550             self.__open_connection()
    551 
    552     def close(self, retry=True):

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\connection.py in __open_connection(self)
    787         auth = Auth(self.rest)
    788         auth.read_temporary_credentials(self.host, self.user, self._session_parameters)
---> 789         self._authenticate(auth_instance)
    790 
    791         self._password = None  # ensure password won't persist

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\connection.py in _authenticate(self, auth_instance)
   1050         # make some changes if needed before real __authenticate
   1051         try:
-> 1052             self.__authenticate(self.__preprocess_auth_instance(auth_instance))
   1053         except ReauthenticationRequest as ex:
   1054             # cached id_token expiration error, we have cleaned id_token and try to authenticate again

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\connection.py in __authenticate(self, auth_instance)
   1070         auth = Auth(self.rest)
   1071         try:
-> 1072             auth.authenticate(
   1073                 auth_instance=auth_instance,
   1074                 account=self.account,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\auth.py in authenticate(self, auth_instance, account, user, database, schema, warehouse, role, passcode, passcode_in_password, mfa_callback, password_callback, session_parameters, timeout)
    255 
    256         try:
-> 257             ret = self._rest._post_request(
    258                 url,
    259                 headers,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\network.py in _post_request(self, url, headers, body, token, timeout, _no_results, no_retry, socket_timeout, _include_retry_params)
    702             pprint(ret)
    703 
-> 704         ret = self.fetch(
    705             "post",
    706             full_url,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\network.py in fetch(self, method, full_url, headers, data, timeout, **kwargs)
    792             retry_ctx = RetryCtx(timeout, include_retry_params)
    793             while True:
-> 794                 ret = self._request_exec_wrapper(
    795                     session, method, full_url, headers, data, retry_ctx, **kwargs
    796                 )

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\network.py in _request_exec_wrapper(self, session, method, full_url, headers, data, retry_ctx, no_retry, token, **kwargs)
    915         except Exception as e:
    916             if not no_retry:
-> 917                 raise e
    918             logger.debug("Ignored error", exc_info=True)
    919             return {}

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\network.py in _request_exec_wrapper(self, session, method, full_url, headers, data, retry_ctx, no_retry, token, **kwargs)
    835         full_url = SnowflakeRestful.add_request_guid(full_url)
    836         try:
-> 837             return_object = self._request_exec(
    838                 session=session,
    839                 method=method,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\network.py in _request_exec(self, session, method, full_url, headers, data, token, catch_okta_unauthorized_error, is_raw_text, is_raw_binary, binary_data_handler, socket_timeout)
   1114                 stack_trace=traceback.format_exc(),
   1115             )
-> 1116             raise err
   1117 
   1118     def make_requests_session(self):

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\network.py in _request_exec(self, session, method, full_url, headers, data, token, catch_okta_unauthorized_error, is_raw_text, is_raw_binary, binary_data_handler, socket_timeout)
   1016             # the response within the time. If not, ConnectReadTimeout or
   1017             # ReadTimeout is raised.
-> 1018             raw_ret = session.request(
   1019                 method=method,
   1020                 url=full_url,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\vendored\requests\sessions.py in request(self, method, url, params, data, headers, cookies, files, auth, timeout, allow_redirects, proxies, hooks, stream, verify, cert, json)
    585         }
    586         send_kwargs.update(settings)
-> 587         resp = self.send(prep, **send_kwargs)
    588 
    589         return resp

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\vendored\requests\sessions.py in send(self, request, **kwargs)
    699 
    700         # Send the request
-> 701         r = adapter.send(request, **kwargs)
    702 
    703         # Total elapsed time of the request (approximately)

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\vendored\requests\adapters.py in send(self, request, stream, timeout, verify, cert, proxies)
    487         try:
    488             if not chunked:
-> 489                 resp = conn.urlopen(
    490                     method=request.method,
    491                     url=url,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\vendored\urllib3\connectionpool.py in urlopen(self, method, url, body, headers, retries, redirect, assert_same_host, timeout, pool_timeout, release_conn, chunked, body_pos, **response_kw)
    701 
    702             # Make the request on the httplib connection object.
-> 703             httplib_response = self._make_request(
    704                 conn,
    705                 method,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\vendored\urllib3\connectionpool.py in _make_request(self, conn, method, url, timeout, chunked, **httplib_request_kw)
    384         # Trigger any extra validation we need to do.
    385         try:
-> 386             self._validate_conn(conn)
    387         except (SocketTimeout, BaseSSLError) as e:
    388             # Py2 raises this as a BaseSSLError, Py3 raises it as socket timeout.

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\vendored\urllib3\connectionpool.py in _validate_conn(self, conn)
   1040         # Force connect early to allow us to validate the connection.
   1041         if not getattr(conn, "sock", None):  # AppEngine might not have  `.sock`
-> 1042             conn.connect()
   1043 
   1044         if not conn.is_verified:

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\vendored\urllib3\connection.py in connect(self)
    412             context.load_default_certs()
    413 
-> 414         self.sock = ssl_wrap_socket(
    415             sock=conn,
    416             keyfile=self.key_file,

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\ssl_wrap_socket.py in ssl_wrap_socket_with_ocsp(*args, **kwargs)
     76     ret = ssl_.ssl_wrap_socket(*args, **kwargs)
     77 
-> 78     from .ocsp_asn1crypto import SnowflakeOCSPAsn1Crypto as SFOCSP
     79 
     80     log.debug(

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\ocsp_asn1crypto.py in <module>
     45 )
     46 from snowflake.connector.errors import RevocationCheckError
-> 47 from snowflake.connector.ocsp_snowflake import SnowflakeOCSP, generate_cache_key
     48 
     49 with warnings.catch_warnings():

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\ocsp_snowflake.py in <module>
     79         tuple[bytes, bytes, bytes],
     80         OCSPResponseValidationResult,
-> 81     ] = SFDictFileCache(
     82         entry_lifetime=constants.DAY_IN_SECONDS,
     83         file_path={

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\cache.py in __init__(self, file_path, entry_lifetime, file_timeout)
    404         self.last_loaded: datetime.datetime | None = None
    405         if os.path.exists(self.file_path):
-> 406             self._load()
    407 
    408     def _getitem_non_locking(

~\.conda\envs\py_38_env\lib\site-packages\snowflake\connector\cache.py in _load(self)
    485         try:
    486             with open(self.file_path, "rb") as r_file:
-> 487                 other = pickle.load(r_file)
    488             self._update(
    489                 other,

UnpicklingError: invalid load key, '\x00'.

解决方法 #

从错误栈可以看出，问题根源是系统崩溃导致Snowflake Connector的本地缓存文件损坏，加载时触发反序列化错误。

• 核心修复步骤

  1. 找到缓存目录：Windows系统下，缓存文件默认路径是C:\Users\<你的用户名>\.cache\snowflake
  2. 删除损坏缓存：直接删除整个.cache/snowflake文件夹，或者里面的ocsp_response_cache等缓存文件
  3. 重新运行代码：Snowflake Connector会自动生成新的缓存文件，连接即可恢复

• 额外排查选项

  • 升级连接器版本：执行pip install --upgrade snowflake-connector-python到最新稳定版
  • 临时禁用OCSP缓存（不推荐长期使用）：在connect方法中添加参数ocsp_fail_open=False

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