Speasy import started failing in PyHC environment

[sapols]

I've had an import test in the PyHC environment for a while that simply imports all the packages in one big block. The Speasy import started failing sometime between February 15-19. The error seems to be deep in the weeds; some SQL statement from the diskcache package. If you all have seen this error before or have any idea what causes it, I'd appreciate any info you can give.

I'll paste the output below, but note you can reproduce it yourself with these steps:

1. Visit the PyHC environment in Binder (it may take upwards of 10 mins to build)
2. Open import-test.ipynb
3. Comment out import pydarn (because it'll fail before reaching speasy; they're fixing this problem in the next release)
4. Run import-test.ipynb

This was the error output:

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In[5], line 57
     55 import space_packet_parser
     56 import spacepy
---> 57 import speasy
     58 import spiceypy
     59 import sunkit_image

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/__init__.py:21](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/__init__.py#line=20)
     18 from .products import SpeasyVariable, Catalog, Event, Dataset, TimeTable, MaybeAnyProduct
     20 # keep this import last
---> 21 from .core.requests_scheduling.request_dispatch import get_data, list_providers, amda, cda, csa, ssc, archive
     24 # @TODO implement me, this function should be able to look inside all servers
     25 # and return something that could be passed to get_data
     26 def find_product(name: str) -> List[str]:

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/requests_scheduling/__init__.py:2](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/requests_scheduling/__init__.py#line=1)
      1 from .split_large_requests import SplitLargeRequests
----> 2 from .request_dispatch import get_data

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/requests_scheduling/request_dispatch.py:16](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/requests_scheduling/request_dispatch.py#line=15)
     14 from ...config import core as core_cfg, amda as amda_cfg
     15 from ...products import *
---> 16 from ...webservices import (AMDA_Webservice, CDA_Webservice, CSA_Webservice,
     17                             SSC_Webservice, GenericArchive)
     18 from ..http import is_server_up
     20 log = logging.getLogger(__name__)

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/__init__.py:1](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/__init__.py#line=0)
----> 1 from .amda import AMDA_Webservice
      2 from .cda import CDA_Webservice
      3 from .csa import CSA_Webservice

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/__init__.py:36](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/__init__.py#line=35)
     33 __email__ = '[email protected]'
     34 __version__ = '0.1.0'
---> 36 from .ws import AMDA_Webservice, ProductType

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/ws.py:9](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/ws.py#line=8)
      6 from enum import Enum
      7 from typing import Dict, List, Optional, Union
----> 9 from ._impl import is_private, is_public
     10 from .inventory import to_xmlid
     11 from .utils import get_parameter_args

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/_impl.py:6](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/_impl.py#line=5)
      3 from types import SimpleNamespace
      4 from typing import Dict, Optional
----> 6 from . import rest_client
      7 from .exceptions import MissingCredentials
      8 from .inventory import AmdaXMLParser

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/rest_client.py:9](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/webservices/amda/rest_client.py#line=8)
      7 from speasy.config import amda as amda_cfg
      8 from speasy.core import pack_kwargs, http
----> 9 from speasy.core.cache import CacheCall
     11 log = logging.getLogger(__name__)
     13 AMDA_BATCH_MODE_TIME = 240  # seconds

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/__init__.py:2](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/__init__.py#line=1)
      1 from .cache import Cache, CacheItem
----> 2 from ._function_cache import CacheCall
      3 from ._providers_caches import CACHE_ALLOWED_KWARGS, Cacheable, UnversionedProviderCache
      4 from ._instance import _cache

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/_function_cache.py:7](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/_function_cache.py#line=6)
      4 from functools import wraps
      5 from typing import Callable
----> 7 from ._instance import _cache
     10 def make_key_from_args(*args, **kwargs):
     11     key = list(map(str, args))

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/_instance.py:4](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/_instance.py#line=3)
      1 from .cache import Cache
      2 from ...config import cache as cache_cfg
----> 4 _cache = Cache(cache_cfg.path())

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/cache.py:34](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/cache.py#line=33), in Cache.__init__(self, cache_path, cache_type)
     32 if self.version < cache_version:
     33     self._data.clear()
---> 34     self.version = cache_version

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/cache.py:42](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/speasy/core/cache/cache.py#line=41), in Cache.version(self, v)
     40 @version.setter
     41 def version(self, v: Union[str, Version]):
---> 42     self._data["cache[/version](https://hub.binder.opensci.2i2c.cloud/version)"] = v if type(v) is str else version_to_str(v)

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/diskcache/core.py:823](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/diskcache/core.py#line=822), in Cache.__setitem__(self, key, value)
    814 def __setitem__(self, key, value):
    815     """Set corresponding `value` for `key` in cache.
    816 
    817     :param key: key for item
   (...)
    821 
    822     """
--> 823     self.set(key, value, retry=True)

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/diskcache/core.py:808](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/diskcache/core.py#line=807), in Cache.set(self, key, value, expire, read, tag, retry)
    806     self._row_update(rowid, now, columns)
    807 else:
--> 808     self._row_insert(db_key, raw, now, columns)
    810 self._cull(now, sql, cleanup)
    812 return True

File [/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/diskcache/core.py:857](https://hub.binder.opensci.2i2c.cloud/opt/conda/envs/pyhc-all/lib/python3.11/site-packages/diskcache/core.py#line=856), in Cache._row_insert(self, key, raw, now, columns)
    855 sql = self._sql
    856 expire_time, tag, size, mode, filename, value = columns
--> 857 sql(
    858     'INSERT INTO Cache('
    859     ' key, raw, store_time, expire_time, access_time,'
    860     ' access_count, tag, size, mode, filename, value'
    861     ') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
    862     (
    863         key,
    864         raw,
    865         now,  # store_time
    866         expire_time,
    867         now,  # access_time
    868         0,  # access_count
    869         tag,
    870         size,
    871         mode,
    872         filename,
    873         value,
    874     ),
    875 )

OperationalError: no such column: "size" - should this be a string literal in single-quotes?

[jeandet]

 @sapols thank you for the feedback, I was able to reproduce it with PyHC env.
A bit of googling got me here and here, this seems to be an issue or breaking change in sqlite3. I have to reproduce it on my machine, actually the newest lisqlite3 I get is 3.49.0 with Python 3.13. I'll get back to you ASAP once I find a solution.

[jeandet]

@sapols It turns out to be a conda package bug.
I hope they will fix this soon.

[Beforerr]

Instead of closing it and waiting, maybe we could pin the version now in pyproject.toml?

sqlite = "!=3.49.1" 

Related to SciQLop/Speasy.jl@3e11164

[jeandet]

Unless I missed something, you can't constrain core packages in pyproject.toml this is only a Conda thing (where the real issue is building it with SQLITE_DQS=0).
One solution would be to provide Conda packages for Speasy (that would imply dependencies like PyCdf++ too).
There is also a PR on diskcache for 3 days.
I really hope there will be a fix soon since there is not much I can easily do from Speasy.

[Beforerr]

Thanks for the clarification (I do not know about that before)! Hopefully, a fix of diskcache comes through soon. It seems that providing Conda packages for Speasy, including dependencies like PyCdf++, could be a good long-term solution.

[sapols]

@jeandet Thanks for pointing me to that conda package bug, and sorry for the slow reply! I see you already closed the issue but I'm not sure I understand a solution yet. I tried downgrading the PyHC env's sqlite version to 3.48.0 (a version that worked for someone who commented on that bug page) but the speasy import still fails with the same error. Are you able to import speasy?

[jeandet]

@sapols , how did you downgrade sqlite? I tried on binder but /opt/conda is not writable, I'm not a conda user myself so I'm not sure about the proper way to downgrade sqlite or if you have to specify it when you build your environment.

[sapols]

Oh sorry I didn't specify, @jeandet. You're right you can't downgrade the version in Binder due to security restrictions. I was testing in a local environment. I meant to ask if you're able to import speasy in any environment, because right now I can't figure out a combination of packages that allow it to import anywhere.

[jeandet]

I had to test an environment this morning for this issue, creating an environment with micromamba env create -n sciqlop python==3.13 "sqlite<3.49" worked perfectly with Speasy.
Note also that we are experiencing a power outage at the moment, the whole campus is down for more than 1h now, that might trigger some timeouts (because of Speasy trying to reach the cache server) when importing Speasy :/.

[sapols]

Thanks, good to know. I'll play around with package versions and see if I can find a combination that fixes the import for now.

[sapols]

Good news: I can now confirm that pinning these versions in environment.yml fixed the speasy import in the PyHC env:

- sqlite=3.48.0     # version 3.49.1 breaks speasy import
- libsqlite=3.48.0  # version 3.49.1 breaks speasy import

And also, your comment about /opt/conda not being writable sent me down a rabbit hole of trying to fix that, and I did. So users of the PyHC env can now install/modify packages at will in Binder, which improves the experience for everyone. Thanks for inspiring that! 🙂