通过 python 连接到 Snowflake 时出错“UnpicklingError: invalid load key, '\x00'”

我在使用snowflake.connector.connect通过python连接到snowflake时遇到以下错误

import snowflake.connector #pip install snowflake-connector-python
#i am getting the env from .env file i stored locally
cnx = snowflake.connector.connect(user=os.getenv('USER'),password=os.getenv('PASSWORD'),account=os.getenv('ACCOUNT'),warehouse=os.getenv('WAREHOUSE'), database=db,schema=schema )

这一直工作正常，直到今天我的系统在运行python代码时崩溃并且我不得不硬重启。我尝试了很多方法，例如删除 Users 文件夹中的 python、anaconda 及其所有相关文件，然后重新安装新的 anaconda 版本。但仍然是同样的错误。这是完整的错误消息。感谢帮助。

我什至尝试将用户名、传递和休息硬编码为变量。但仍然是同样的错误。所以该错误与 .env 文件无关。

---------------------------------------------------------------------------
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'.

The error message "UnpicklingError: invalid load key, '\x00'" typically occurs when trying to load a pickled object that was not properly pickled or has become corrupted. In the context of the Snowflake Connector, this error suggests an issue with the way the connector is storing or retrieving cached data.

Here's a breakdown of the error and how to resolve it:

Understanding the Error

• Pickling: Python uses "pickling" to serialize and deserialize objects, allowing you to save them to disk and load them back into memory.
• UnpicklingError: This error indicates a problem during the deserialization process, suggesting that the data being unpickled is not in the expected format or has been altered.
• "invalid load key, '\x00'": This specific error points to an invalid character ('\x00', the null character) found within the pickled data, usually indicating corruption.

Possible Causes and Solutions

1. Corrupted Snowflake Connector Cache:

2. Solution: The most likely culprit is a corrupted cache file used by the Snowflake Connector. These files often store connection details or authentication tokens.

3. Action:

   1. Locate and Delete Cache Files: Search for and delete any Snowflake Connector cache files. The location of these files may vary depending on your operating system and how you installed the connector. Some common locations include:
      • ~/.cache/snowflake (Linux/macOS)
      • %LOCALAPPDATA%\Snowflake\Caches (Windows)
   2. Retry Connection: Attempt to connect to Snowflake again. The connector will automatically create new cache files if needed.

4. Incompatible Snowflake Connector Version:

5. Solution: Rarely, an outdated or incompatible version of the Snowflake Connector might be causing issues with how data is pickled and unpickled.

6. Action:

   1. Update the Connector: Ensure you have the latest version installed: pip install --upgrade snowflake-connector-python
   2. Check for Compatibility: If you're using a specific Snowflake Connector version for compatibility reasons, verify that it's compatible with your Python version and the Snowflake environment.

7. Interference from Security Software:

8. Solution: Overly aggressive antivirus or security software can sometimes interfere with file operations, potentially corrupting cache files.

9. Action:
   • Temporarily Disable Security Software: Try temporarily disabling your antivirus or security software to see if it resolves the issue.
   • Add an Exception: If disabling the software fixes the problem, add an exception for the Snowflake Connector cache directory.

Code Improvements (for Prevention)

While the error likely stems from a corrupted cache, consider these coding practices to prevent similar issues:

• Error Handling: Implement try...except blocks around your Snowflake connection code to gracefully handle potential errors during connection, including issues with cache loading.

```python import snowflake.connector

try: cnx = snowflake.connector.connect( user=os.getenv("USER"), password=os.getenv("PASSWORD"), account=os.getenv("ACCOUNT"), warehouse=os.getenv("WAREHOUSE"), database=db, schema=schema, ) # ... your code to interact with Snowflake ...

except snowflake.connector.errors.Error as e: print(f"Error connecting to Snowflake: {e}") # Handle the error appropriately (e.g., log the error, retry, exit gracefully) ```

By deleting the cache files and implementing best practices, you should be able to resolve this "UnpicklingError" and establish a successful connection to Snowflake.