AWS Lambda中使用SQLAlchemy连接Redshift报错求助

尝试通过Python 3.8将pandas DataFrame加载至Redshift表，使用SQLAlchemy和redshift_connector创建连接，代码如下：

# PYTHON 3.8
import pandas as pd
import sqlalchemy as sq  
url = sq.engine.url.URL.create(
        drivername='redshift+redshift_connector', # 指定使用redshift_connector驱动和方言
        host=f"{redshift_endpoint}", # Amazon Redshift主机地址
        port=5439, # Amazon Redshift端口
        database='dev', # Amazon Redshift数据库名
        username=f"{redshift_db_user}", # Amazon Redshift用户名
        password=f"{redshift_db_password}" # Amazon Redshift密码
    )
print('Connection URL is',url)
engine=sq.create_engine(url)
cnn = engine.connect()

创建连接时出现如下错误：

[ERROR] AttributeError: module 'sqlalchemy.util' has no attribute 'text_type'
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 44, in lambda_handler
    cnn = engine.connect()
  File "/opt/python/sqlalchemy/engine/base.py", line 3268, in connect
    return self._connection_cls(self)
  File "/opt/python/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/opt/python/sqlalchemy/engine/base.py", line 3292, in raw_connection
    return self.pool.connect()
  File "/opt/python/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/opt/python/sqlalchemy/pool/base.py", line 1269, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/opt/python/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/opt/python/sqlalchemy/pool/impl.py", line 170, in _do_get
    self._dec_overflow()
  File "/opt/python/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/opt/python/sqlalchemy/pool/impl.py", line 167, in _do_get
    return self._create_connection()
  File "/opt/python/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "/opt/python/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/opt/python/sqlalchemy/pool/base.py", line 914, in __connect
    pool.dispatch.connect.for_modify(
  File "/opt/python/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "/opt/python/sqlalchemy/event/attr.py", line 487, in __call__
    fn(*args, **kw)
  File "/opt/python/sqlalchemy/engine/create.py", line 704, in on_connect
    do_on_connect(dbapi_connection)
  File "/opt/python/sqlalchemy_redshift/dialect.py", line 1395, in on_connect
    fn(conn)
  File "/opt/python/sqlalchemy_redshift/dialect.py", line 1373, in on_connect
    conn.py_types[quoted_name] = conn.py_types[util.text_type]

问题原因与解决方法 #

原因 #

该错误源于sqlalchemy-redshift版本与高版本SQLAlchemy（2.0+）不兼容：SQLAlchemy 2.0及以上版本移除了sqlalchemy.util.text_type属性，但旧版sqlalchemy-redshift仍在调用该属性。

解决方法 #

• 方法一：降级SQLAlchemy至兼容版本
  将SQLAlchemy降级到1.4.x系列，这是最直接的兼容方案，执行以下命令：

  pip install sqlalchemy~=1.4.0
  

• 方法二：升级sqlalchemy-redshift至最新兼容版本
  升级sqlalchemy-redshift到适配SQLAlchemy 2.0+的版本，执行以下命令：

  pip install --upgrade sqlalchemy-redshift
  

额外检查项 #

• 确保redshift_connector版本与sqlalchemy-redshift、SQLAlchemy版本相互兼容；
• 确认代码中redshift_endpoint、redshift_db_user、redshift_db_password变量已正确赋值。

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