SQLAlchemy在插入枚举列的Postgres数组时引发错误

Question

我创建了一个列，它是Enum类型的Postgres数组。当我尝试插入数据时，我得到一个很长的回溯，似乎相关的错误消息是：

psycopg2.errors.DatatypeMismatch: column "selected_groceries" is of type groceryenum[] but expression is of type text[]

from flask_sqlalchemy import SQLAlchemy
from marshmallow import Schema, fields
from marshmallow_enum import EnumField
import enum

# ensure to connect it to a postgresdb

db = SQLAlchemy()
Column = db.Column

class CustomCreateMixin:

    @classmethod
    def create(cls, **kwargs):
        """Create a new record and save it the database."""
        instance = cls(**kwargs)
        return instance.save()

    def save(self, commit=True):
        """Save the record."""
        db.session.add(self)
        if commit:
            db.session.commit()
        return self

class GroceryEnum(enum.Enum):
    APPLE = 'APPLE'
    BANANA = 'BANANA'
    PEAR = 'PEAR'
    def __str__(self):
        return self.values

class ExampleModel(CustomCreateMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    selected_groceries = Column(db.ARRAY(db.Enum(GroceryEnum)))
#, create_constraint=False, native_enum

class ExampleModelSchema(Schema):
    id = fields.Int(dump_only=True)
    selected_groceries = fields.List(EnumField(GroceryEnum))

执行此操作会导致错误：

# from the command line
flask shell
# import the db, model, schema and enum
from filename import db, ExampleModel, ExampleModelSchema, GroceryEnum
# init schema 
schema = ExampleModelSchema()

#example input
json_data = {"selected_groceries": ['APPLE']}
# use schema to validate or deserialise
data = schema.load(json_data)
# output of data = {'selected_groceries': [<GroceryEnum.APPLE: 'APPLE'>]}
# try to create - ntoe here you may use the standard methods as well instead of this exttension written by us
ExampleModel.create(**data)

Traceback (most recent call last):
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_
context
    self.dialect.do_execute(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_exec
ute
    cursor.execute(statement, parameters)
psycopg2.errors.DatatypeMismatch: column "selected_groceries" is of type groceryenum[] but expression is of type text[]
LINE 1: ...T INTO example_model (selected_groceries) VALUES (ARRAY['APP...
                                                             ^
HINT:  You will need to rewrite or cast the expression.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "C:\cygwin64\home\hamis\microservices\microservices\exampleapp\src\modules\main\examplemodule\models.py", line 168, in create
    return instance.save()
  File "C:\cygwin64\home\hamis\microservices\microservices\exampleapp\src\modules\main\examplemodule\models.py", line 174, in save
    db.session.commit()
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\session.py", line 1036, in commit
    self.transaction.commit()
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\session.py", line 503, in commit
    self._prepare_impl()
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\session.py", line 482, in _prepare_i
mpl
    self.session.flush()
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2496, in flush
    self._flush(objects)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2637, in _flush
    transaction.rollback(_capture_exception=True)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit
__
    compat.raise_(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2597, in _flush
    flush_context.execute()
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 422, in execute

    rec.execute(self)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 586, in execute

    persistence.save_obj(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 239, in save_o
bj
    _emit_insert_statements(
_insert_statements
    result = cached_connections[connection].execute(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute
    return meth(self, multiparams, params)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1097, in _execute_clauseelement
    ret = self._execute_context(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1287, in _execute_context
    self._handle_dbapi_exception(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _handle_dbapi_exception
    util.raise_(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "selected_groceries" is of type groceryenum[] but expression is of type text[]
LINE 1: ...T INTO example_model (selected_groceries) VALUES (ARRAY['APP...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

[SQL: INSERT INTO example_model (selected_groceries) VALUES (%(selected_groceries)s) RETURNING example_model.id]
[parameters: {'selected_groceries': ['APPLE']}]
(Background on this error at: http://sqlalche.me/e/f405)

它表明，在转换插入代码期间不会转换/强制转换类型，因此尝试输入文本数组而不是给定的枚举

1. 我尝试过使用下面的方法，结果没有任何不同Column(db.ARRAY(db.Enum(GroceryEnum, create_constraint=False)))
2. 我尝试了许多其他参数的组合，但没有成功
3. 我可能没有正确理解的一个参数是native_enum=False。在尝试此操作时，它会导致不同的错误，我已将其放在下面：

与native_enum=False有关的错误：

Creating tables Boasy Phone Deals Service...
Traceback (most recent call last):
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidTextRepresentation: malformed array literal: "APPLE"
DETAIL:  Array value must start with "{" or dimension information.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\users\hamis.desktop-u1seum7.000\appdata\local\programs\python\python38\lib\runpy.py", line 193, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "c:\users\hamis.desktop-u1seum7.000\appdata\local\programs\python\python38\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\Scripts\flask.exe\__main__.py", line 7, in <module>
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\flask\cli.py", line 966, in main
    cli.main(prog_name="python -m flask" if as_module else None)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\flask\cli.py", line 586, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\click\core.py", line 717, in main
    rv = self.invoke(ctx)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\click\core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\click\core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\click\core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\click\core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\click\decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\flask\cli.py", line 426, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\click\core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "C:\cygwin64\home\hamis\microservices\microservices\exampleapp\src\commands.py", line 25, in reset_database
    db.create_all()
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\flask_sqlalchemy\__init__.py", line 1033, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\flask_sqlalchemy\__init__.py", line 1025, in _execute_for_all_tables

    op(bind=self.get_engine(app, bind), **extra)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\sql\schema.py", line 4320, in create_all
    bind._run_visitor(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2058, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1627, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 144, in traverse_single
    return meth(obj, **kw)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 777, in visit_metadata
    self.traverse_single(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 144, in traverse_single
    return meth(obj, **kw)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 821, in visit_table
    self.connection.execute(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute
    return meth(self, multiparams, params)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\sql\ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1041, in _execute_ddl
    ret = self._execute_context(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1287, in _execute_context
    self._handle_dbapi_exception(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _handle_dbapi_exception
    util.raise_(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "c:\cygwin64\home\hamis\microservices\microservices\exampleapp\venv\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) malformed array literal: "APPLE"
DETAIL:  Array value must start with "{" or dimension information.

[SQL:
CREATE TABLE example_model (
        id SERIAL NOT NULL,
        selected_groceries VARCHAR(6)[],
        PRIMARY KEY (id),
        CONSTRAINT groceryenum CHECK (selected_groceries IN ('APPLE', 'BANANA', 'PEAR')),
        CONSTRAINT groceryenum CHECK (selected_groceries IN ('APPLE', 'BANANA', 'PEAR'))
)

]
(Background on this error at: http://sqlalche.me/e/9h9h)

我查过的参考资料：

• SQLAlchemy docs on Postgres array type
• PostgreSQL INSERT into an array of enums表明SQLalchemy和Postgres之间存在问题
• Array of Enum in Postgres with SQLAlchemy

版本：

Flask==1.1.1
flask-restx==0.1.1
Flask-SQLAlchemy==2.4.1
marshmallow==3.5.1
marshmallow-enum==1.5.1
psycopg2==2.8.4
psycopg2-binary==2.8.4
SQLAlchemy==1.3.16
SQLAlchemy-Utils==0.36.1

Answer 1

现有SQLAlchemy数据类型不直接支持在PostgreSQL中使用带枚举的数组，因此要使用此组合，您需要使用documented approach，它将构造一个简单的自定义类型，用于处理此组合中的特殊特性：

from sqlalchemy import TypeDecorator
from sqlalchemy.dialects.postgresql import ARRAY

class ArrayOfEnum(TypeDecorator):
    impl = ARRAY

    def bind_expression(self, bindvalue):
        return sa.cast(bindvalue, self)

    def result_processor(self, dialect, coltype):
        super_rp = super(ArrayOfEnum, self).result_processor(
            dialect, coltype)

        def handle_raw_string(value):
            inner = re.match(r"^{(.*)}$", value).group(1)
            return inner.split(",") if inner else []

        def process(value):
            if value is None:
                return None
            return super_rp(handle_raw_string(value))
        return process

此外，非本机ENUM生成的检查约束（这意味着它正在使用VARCHAR）在嵌入到数组中时同样不受支持，该检查约束在任何情况下都可能不是默认值，但要使用它，只需将其禁用，这绝对是最容易用于以下异常情况的枚举类型：

ARRAY(Enum(GroceryEnum, create_constraint=False, native_enum=False))