这里希望很小：）但是当我试图上传一个带有json列的pandas表时，我得到了这个错误，正如这篇SO文章中所描述的：How to push dict column into Redshift SUPER type column using pandas.to_sql?
第一个月
我试着在谷歌上搜索这个错误，它返回了两个结果，所以我想我是第三个遇到这个问题的人。但如果有人碰巧知道这件事，我会非常感激任何帮助。
from psycopg2.extensions import register_adapter
from psycopg2.extras import Json
from sqlalchemy_redshift import dialect
from sqlalchemy import create_engine, types
import pandas as pd
import json
register_adapter(dict, Json)
register_adapter(list, Json)
RS_creds = {}
RS_creds['host'] = %env RS_DATA_HOST
RS_creds['user'] = %env RS_DATA_USER
RS_creds['pass'] = %env RS_DATA_PASS
RS_creds['port'] = %env RS_DATA_PORT
RS_creds['db'] = %env RS_DATA_DB
test = pd.DataFrame([[1, json.dumps({"a": "A1", "b": "B1"})], [2, json.dumps({"a": "A2", "b": "B2"})]])
test.columns = ['x', 'y']
test_dict_types = {'x': types.INTEGER(), 'y': dialect.SUPER()}
url = f"postgresql://{RS_creds['user']}:{RS_creds['pass']}@{RS_creds['host']}:5439/{RS_creds['db']}"
engine = create_engine(url)
test.to_sql('test', engine, schema = 'test', index = False, dtype = test_dict_types})字符串
回溯：
---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py:77, in _generate_compiler_dispatch.<locals>._compiler_dispatch(self, visitor, **kw)
76 try:
---> 77 meth = getter(visitor)
78 except AttributeError as err:
AttributeError: 'PGTypeCompiler' object has no attribute 'visit_SUPER'
The above exception was the direct cause of the following exception:
UnsupportedCompilationError Traceback (most recent call last)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:4534, in DDLCompiler.visit_create_table(self, create, **kw)
4533 try:
-> 4534 processed = self.process(
4535 create_column, first_pk=column.primary_key and not first_pk
4536 )
4537 if processed is not None:
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:499, in Compiled.process(self, obj, **kwargs)
498 def process(self, obj, **kwargs):
--> 499 return obj._compiler_dispatch(self, **kwargs)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py:82, in _generate_compiler_dispatch.<locals>._compiler_dispatch(self, visitor, **kw)
81 else:
---> 82 return meth(self, **kw)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:4568, in DDLCompiler.visit_create_column(self, create, first_pk, **kw)
4566 return None
-> 4568 text = self.get_column_specification(column, first_pk=first_pk)
4569 const = " ".join(
4570 self.process(constraint) for constraint in column.constraints
4571 )
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/base.py:2734, in PGDDLCompiler.get_column_specification(self, column, **kwargs)
2733 else:
-> 2734 colspec += " " + self.dialect.type_compiler.process(
2735 column.type,
2736 type_expression=column,
2737 identifier_preparer=self.preparer,
2738 )
2739 default = self.get_column_default_string(column)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:533, in TypeCompiler.process(self, type_, **kw)
532 def process(self, type_, **kw):
--> 533 return type_._compiler_dispatch(self, **kw)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py:79, in _generate_compiler_dispatch.<locals>._compiler_dispatch(self, visitor, **kw)
78 except AttributeError as err:
---> 79 return visitor.visit_unsupported_compilation(self, err, **kw)
81 else:
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:536, in TypeCompiler.visit_unsupported_compilation(self, element, err, **kw)
535 def visit_unsupported_compilation(self, element, err, **kw):
--> 536 util.raise_(
537 exc.UnsupportedCompilationError(self, element),
538 replace_context=err,
539 )
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
210 try:
--> 211 raise exception
212 finally:
213 # credit to
214 # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
215 # as the __traceback__ object creates a cycle
UnsupportedCompilationError: Compiler <sqlalchemy.dialects.postgresql.base.PGTypeCompiler object at 0x162ac95a0> can't render element of type SUPER (Background on this error at: https://sqlalche.me/e/14/l7de)
The above exception was the direct cause of the following exception:
CompileError Traceback (most recent call last)
Cell In[38], line 1
----> 1 test.to_sql('test', \
2 engine, \
3 schema = 'wbx_data_persistent', \
4 index = False, \
5 if_exists = 'replace', \
6 dtype = test_dict_types)
File /opt/homebrew/lib/python3.10/site-packages/pandas/core/generic.py:2987, in NDFrame.to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
2830 """
2831 Write records stored in a DataFrame to a SQL database.
2832
(...)
2983 [(1,), (None,), (2,)]
2984 """ # noqa:E501
2985 from pandas.io import sql
-> 2987 return sql.to_sql(
2988 self,
2989 name,
2990 con,
2991 schema=schema,
2992 if_exists=if_exists,
2993 index=index,
2994 index_label=index_label,
2995 chunksize=chunksize,
2996 dtype=dtype,
2997 method=method,
2998 )
File /opt/homebrew/lib/python3.10/site-packages/pandas/io/sql.py:695, in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
690 elif not isinstance(frame, DataFrame):
691 raise NotImplementedError(
692 "'frame' argument should be either a Series or a DataFrame"
693 )
--> 695 return pandas_sql.to_sql(
696 frame,
697 name,
698 if_exists=if_exists,
699 index=index,
700 index_label=index_label,
701 schema=schema,
702 chunksize=chunksize,
703 dtype=dtype,
704 method=method,
705 engine=engine,
706 **engine_kwargs,
707 )
File /opt/homebrew/lib/python3.10/site-packages/pandas/io/sql.py:1728, in SQLDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
1678 """
1679 Write records stored in a DataFrame to a SQL database.
1680
(...)
1724 Any additional kwargs are passed to the engine.
1725 """
1726 sql_engine = get_engine(engine)
-> 1728 table = self.prep_table(
1729 frame=frame,
1730 name=name,
1731 if_exists=if_exists,
1732 index=index,
1733 index_label=index_label,
1734 schema=schema,
1735 dtype=dtype,
1736 )
1738 total_inserted = sql_engine.insert_records(
1739 table=table,
1740 con=self.connectable,
(...)
1747 **engine_kwargs,
1748 )
1750 self.check_case_sensitive(name=name, schema=schema)
File /opt/homebrew/lib/python3.10/site-packages/pandas/io/sql.py:1631, in SQLDatabase.prep_table(self, frame, name, if_exists, index, index_label, schema, dtype)
1619 raise ValueError(f"The type of {col} is not a SQLAlchemy type")
1621 table = SQLTable(
1622 name,
1623 self,
(...)
1629 dtype=dtype,
1630 )
-> 1631 table.create()
1632 return table
File /opt/homebrew/lib/python3.10/site-packages/pandas/io/sql.py:838, in SQLTable.create(self)
836 raise ValueError(f"'{self.if_exists}' is not valid for if_exists")
837 else:
--> 838 self._execute_create()
File /opt/homebrew/lib/python3.10/site-packages/pandas/io/sql.py:824, in SQLTable._execute_create(self)
821 def _execute_create(self):
822 # Inserting table into database, add to MetaData object
823 self.table = self.table.to_metadata(self.pd_sql.meta)
--> 824 self.table.create(bind=self.pd_sql.connectable)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:962, in Table.create(self, bind, checkfirst)
960 if bind is None:
961 bind = _bind_or_error(self)
--> 962 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3228, in Engine._run_ddl_visitor(self, visitorcallable, element, **kwargs)
3226 def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
3227 with self.begin() as conn:
-> 3228 conn._run_ddl_visitor(visitorcallable, element, **kwargs)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2211, in Connection._run_ddl_visitor(self, visitorcallable, element, **kwargs)
2204 def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
2205 """run a DDL visitor.
2206
2207 This method is only here so that the MockConnection can change the
2208 options given to the visitor so that "checkfirst" is skipped.
2209
2210 """
-> 2211 visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py:524, in ExternalTraversal.traverse_single(self, obj, **kw)
522 meth = getattr(v, "visit_%s" % obj.__visit_name__, None)
523 if meth:
--> 524 return meth(obj, **kw)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py:895, in SchemaGenerator.visit_table(self, table, create_ok, include_foreign_key_constraints, _is_metadata_operation)
891 if not self.dialect.supports_alter:
892 # e.g., don't omit any foreign key constraints
893 include_foreign_key_constraints = None
--> 895 self.connection.execute(
896 # fmt: off
897 CreateTable(
898 table,
899 include_foreign_key_constraints= # noqa
900 include_foreign_key_constraints, # noqa
901 )
902 # fmt: on
903 )
905 if hasattr(table, "indexes"):
906 for index in table.indexes:
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1380, in Connection.execute(self, statement, *multiparams, **params)
1376 util.raise_(
1377 exc.ObjectNotExecutableError(statement), replace_context=err
1378 )
1379 else:
-> 1380 return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py:80, in DDLElement._execute_on_connection(self, connection, multiparams, params, execution_options)
77 def _execute_on_connection(
78 self, connection, multiparams, params, execution_options
79 ):
---> 80 return connection._execute_ddl(
81 self, multiparams, params, execution_options
82 )
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1469, in Connection._execute_ddl(self, ddl, multiparams, params, execution_options)
1465 schema_translate_map = exec_opts.get("schema_translate_map", None)
1467 dialect = self.dialect
-> 1469 compiled = ddl.compile(
1470 dialect=dialect, schema_translate_map=schema_translate_map
1471 )
1472 ret = self._execute_context(
1473 dialect,
1474 dialect.execution_ctx_cls._init_ddl,
(...)
1478 compiled,
1479 )
1480 if self._has_events or self.engine._has_events:
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/elements.py:503, in ClauseElement.compile(self, bind, dialect, **kw)
498 url = util.preloaded.engine_url
499 dialect = url.URL.create(
500 self.stringify_dialect
501 ).get_dialect()()
--> 503 return self._compiler(dialect, **kw)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py:32, in _DDLCompiles._compiler(self, dialect, **kw)
28 def _compiler(self, dialect, **kw):
29 """Return a compiler appropriate for this ClauseElement, given a
30 Dialect."""
---> 32 return dialect.ddl_compiler(dialect, self, **kw)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:464, in Compiled.__init__(self, dialect, statement, schema_translate_map, render_schema_translate, compile_kwargs)
462 if self.can_execute:
463 self.execution_options = statement._execution_options
--> 464 self.string = self.process(self.statement, **compile_kwargs)
466 if render_schema_translate:
467 self.string = self.preparer._render_schema_translates(
468 self.string, schema_translate_map
469 )
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:499, in Compiled.process(self, obj, **kwargs)
498 def process(self, obj, **kwargs):
--> 499 return obj._compiler_dispatch(self, **kwargs)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py:82, in _generate_compiler_dispatch.<locals>._compiler_dispatch(self, visitor, **kw)
79 return visitor.visit_unsupported_compilation(self, err, **kw)
81 else:
---> 82 return meth(self, **kw)
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py:4544, in DDLCompiler.visit_create_table(self, create, **kw)
4542 first_pk = True
4543 except exc.CompileError as ce:
-> 4544 util.raise_(
4545 exc.CompileError(
4546 util.u("(in table '%s', column '%s'): %s")
4547 % (table.description, column.name, ce.args[0])
4548 ),
4549 from_=ce,
4550 )
4552 const = self.create_table_constraints(
4553 table,
4554 _include_foreign_key_constraints=create.include_foreign_key_constraints, # noqa
4555 )
4556 if const:
File /opt/homebrew/lib/python3.10/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
208 exception.__cause__ = replace_context
210 try:
--> 211 raise exception
212 finally:
213 # credit to
214 # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
215 # as the __traceback__ object creates a cycle
216 del exception, replace_context, from_, with_traceback
CompileError: (in table 'test', column 'y'): Compiler <sqlalchemy.dialects.postgresql.base.PGTypeCompiler object at 0x162ac95a0> can't render element of type SUPER型
1条答案
按热度按时间ac1kyiln1#
您的连接字符串正在使用
postgresql尝试将其更改为redshift从
字符串
到，
型
错误详情：
因为你使用的是Postgresql，也就是说，
PGTypeCompiler没有visit_SUPER方言。如果检查RedshiftTypeCompiler，则它包含
visit_SUPER方言。因此，更新连接字符串以使用
redshift将解决此问题。