如何解决无法从sqlalchemy.sql.visitors导入VisitableType的问题

问题背景 #

三个月前部署的Streamlit学生信息收集应用，原本可正常同步数据到谷歌表格，昨日起出现以下错误：

cannot import name 'VisitableType' from 'sqlalchemy.sql.visitors'

本地Jupyter Notebook运行连接逻辑时也触发相同错误，尝试替换sqlalchemy安装目录下的visitors.py文件后问题未解决。

相关代码 #

import streamlit as st
import pandas as pd
import requests 
import sqlalchemy
import matplotlib.pyplot as plt
import leafmap.foliumap as leafmap
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import text
import shillelagh

engine = create_engine(
    "shillelagh://",
     adapters=["gsheetsapi"],
     adapter_kwargs={
         "gsheetsapi": {
             "service_account_info": {
                 "type": "service_account",
                 "project_id": "###########",
                 "private_key_id": "############",
                 "private_key": "############",
                 "client_email": "#########################",
                 "client_id": "####################",
                 "auth_uri": "##############################",
                 "token_uri": "#########################",
                 "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
                 "client_x509_cert_url": "#######################"
            },
             "catalog":
             {
                 "interactive########": "#########################"
             }
         },
     },
)

错误日志 #

File <string>:2, in create_engine(url, **kwargs)

File ~\Anaconda\lib\site-packages\sqlalchemy\util\deprecations.py:277, in deprecated_params.<locals>.decorate.<locals>.warned(fn, *args, **kwargs)
    270     if m in kwargs:
    271         _warn_with_version(
    272             messages[m],
    273             versions[m],
    274             version_warnings[m],
    275             stacklevel=3,
    276         )
--> 277 return fn(*args, **kwargs)

File ~\Anaconda\lib\site-packages\sqlalchemy\engine\create.py:556, in create_engine(url, **kwargs)
    552 u = _url.make_url(url)
    554 u, plugins, kwargs = u._instantiate_plugins(kwargs)
--> 556 entrypoint = u._get_entrypoint()
    557 _is_async = kwargs.pop("_is_async", False)
    558 if _is_async:

File ~\Anaconda\lib\site-packages\sqlalchemy\engine\url.py:754, in URL._get_entrypoint(self)
    752 else:
    753     name = self.drivername.replace("+", ".")
--> 754 cls = registry.load(name)
    755 # check for legacy dialects that
    756 # would return a module with 'dialect' as the
    757 # actual class
    758 if (
    759     hasattr(cls, "dialect")
    760     and isinstance(cls.dialect, type)
    761     and issubclass(cls.dialect, Dialect)
    762 ):

File ~\Anaconda\lib\site-packages\sqlalchemy\util\langhelpers.py:363, in PluginLoader.load(self, name)
    361     if impl.name == name:
    362         self.impls[name] = impl.load
--> 363         return impl.load()
    365 raise exc.NoSuchModuleError(
    366     "Can't load plugin: %s:%s" % (self.group, name)
    367 )

File ~\Anaconda\lib\site-packages\setuptools\_vendor\importlib_metadata\__init__.py:194, in EntryPoint.load(self)
    189 """Load the entry point from its definition. If only a module
    190 is indicated by the value, return that module. Otherwise,
    191 return the named object.
    192 """
    193 match = self.pattern.match(self.value)
--> 194 module = import_module(match.group('module'))
    195 attrs = filter(None, (match.group('attr') or '').split('.'))
    196 return functools.reduce(getattr, attrs, module)

File ~\Anaconda\lib\importlib\__init__.py:127, in import_module(name, package)
    125             break
    126         level += 1
--> 127 return _bootstrap._gcd_import(name[level:], package, level)

File <frozen importlib._bootstrap>:1030, in _gcd_import(name, package, level)

File <frozen importlib._bootstrap>:1007, in _find_and_load(name, import_)

File <frozen importlib._bootstrap>:986, in _find_and_load_unlocked(name, import_)

File <frozen importlib._bootstrap>:680, in _load_unlocked(spec)

File <frozen importlib._bootstrap_external>:850, in exec_module(self, module)

File <frozen importlib._bootstrap>:228, in _call_with_frames_removed(f, *args, **kwds)

File ~\Anaconda\lib\site-packages\shillelagh\backends\apsw\dialects\gsheets.py:19, in <module>
     16 from typing_extensions import TypedDict
     18 from shillelagh.adapters.api.gsheets.lib import get_credentials
--> 19 from shillelagh.backends.apsw.dialects.base import APSWDialect
     20 from shillelagh.exceptions import ProgrammingError
     22 _logger = logging.getLogger(__name__)

File ~\Anaconda\lib\site-packages\shillelagh\backends\apsw\dialects\base.py:10, in <module>
      8 from sqlalchemy.pool.base import _ConnectionFairy
      9 from sqlalchemy.sql.type_api import TypeEngine
--> 10 from sqlalchemy.sql.visitors import VisitableType
     11 from typing_extensions import TypedDict
     13 from shillelagh.adapters.base import Adapter

ImportError: cannot import name 'VisitableType' from 'sqlalchemy.sql.visitors' (C:\Users\############\Anaconda\lib\site-packages\sqlalchemy\sql\visitors.py)

help(sqlalchemy.sql.visitors)

解决方案 #

核心原因 #

Shillelagh版本与最新SQLAlchemy不兼容：SQLAlchemy 2.0+版本移除了VisitableType类，但旧版Shillelagh仍在尝试导入该类。

解决步骤 #

• 方案1：降级SQLAlchemy到兼容版本
  安装SQLAlchemy 1.4.x系列版本（该版本保留VisitableType）：

  # pip方式
  pip install sqlalchemy==1.4.49
  
  # conda方式
  conda install sqlalchemy=1.4.49
  

• 方案2：升级Shillelagh到最新兼容版本
  新版本Shillelagh已适配SQLAlchemy 2.0+，执行升级：

  pip install --upgrade shillelagh
  

• 验证修复
  运行以下命令确认版本匹配：

  pip show sqlalchemy shillelagh
  

  之后重新启动Streamlit应用或Jupyter Notebook测试连接逻辑。

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