使用pandas.to_sql向Redshift SUPER列写入数据时遇PGTypeCompiler错误求助

我参照一篇Stack Overflow帖子，尝试把带有JSON列的Pandas DataFrame写入Amazon Redshift的SUPER类型列，结果触发了如下错误：

AttributeError: 'PGTypeCompiler' object has no attribute 'visit_SUPER'

谷歌搜索仅找到两条相关结果，未能解决问题。以下是我的具体代码和完整报错堆栈：

具体代码 #

# 替换为实际运行的代码
import pandas as pd
from sqlalchemy import create_engine

# 构造包含JSON结构列的DataFrame
df = pd.DataFrame(
    data={
        'id': [1, 2, 3],
        'super_col': [
            {'name': 'Alice', 'age': 30}, 
            {'name': 'Bob', 'hobbies': ['reading', 'hiking']}, 
            {'name': 'Charlie'}
        ]
    }
)

# 建立Redshift连接
redshift_conn_str = 'redshift+psycopg2://<username>:<password>@<cluster-endpoint>:<port>/<db-name>'
engine = create_engine(redshift_conn_str)

# 尝试写入数据到目标表（表中super_col字段为SUPER类型）
df.to_sql(
    name='test_super_table',
    con=engine,
    if_exists='append',
    index=False
)

完整报错堆栈 #

Traceback (most recent call last):
  File "/path/to/your/script.py", line 20, in <module>
    df.to_sql(
  File "/path/to/venv/lib/python3.9/site-packages/pandas/core/generic.py", line 2987, in to_sql
    return sql.to_sql(
  File "/path/to/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "/path/to/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1725, in to_sql
    table.insert(chunksize=chunksize, method=method)
  File "/path/to/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 838, in insert
    exec_insert(conn, keys, chunk_iter)
  File "/path/to/venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1660, in _execute_insert
    conn.execute(stmt, data)
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1960, in _exec_single_context
    self.dialect.do_execute(
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 885, in do_execute
    cursor.execute(statement, parameters)
  File "/path/to/venv/lib/python3.9/site-packages/psycopg2/sql.py", line 155, in execute
    self._execute(cur, self, args)
  File "/path/to/venv/lib/python3.9/site-packages/psycopg2/sql.py", line 163, in _execute
    adapted_args = [adapt(a) for a in args]
  File "/path/to/venv/lib/python3.9/site-packages/psycopg2/sql.py", line 163, in <listcomp>
    adapted_args = [adapt(a) for a in args]
  File "/path/to/venv/lib/python3.9/site-packages/psycopg2/_json.py", line 79, in adapt
    o = json.dumps(obj, **json.dumps_args).encode('utf8')
  File "/path/to/venv/lib/python3.9/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1456, in visit_SUPER
    raise AttributeError(
AttributeError: 'PGTypeCompiler' object has no attribute 'visit_SUPER'

恳请有相关经验的开发者提供解决方案。

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