Skip to content

PostgreSQL arrays of enum break when enum contains spaces #7418

New issue
New issue
Closed
Closed
PostgreSQL arrays of enum break when enum contains spaces#7418
Assignees
CaselIT
Labels
array of enumsthe most troublesome datatype everbugSomething isn't workingdatatypesthings to do with database types, like VARCHAR and otherspostgresql
Milestone
 
1.4.x
@willnewton

Description

@willnewton
willnewton
opened on Dec 9, 2021

Describe the bug

When reflecting an object from a PostgreSQL schema containing a column with an array of enum type where the enum type contains a string including s space an error occurs.

To Reproduce

The database schema looks like:

create type object_type as enum (
  'Test',
  'Another Test'
);

create table test (
  object_types object_type[]
);

I then try to reflect the schema to an object:

class Test(Reflected, Base):
  __tablename__ = "test"

Error

'\"Another Test\"' is not among the defined enum values. Enum name: object_type. Possible values: Test, Another Test

"Traceback (most recent call last):",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/sql/sqltypes.py\", line 1643, in _object_value_for_elem",
            "    return self._object_lookup[elem]",
            "KeyError: '\"Drive Start\"'",
            "",
            "The above exception was the direct cause of the following exception:",
            "Traceback (most recent call last):",
            "  File \"/venv/lib/python3.10/site-packages/graphql/execution/execute.py\", line 617, in resolve_field",
            "    result = resolve_fn(source, info, **args)",
            "  File \"/venv/lib/python3.10/site-packages/amf_iq_api/event.py\", line 29, in resolve_event",
            "    return load_event_by_id(session, id)",
            "  File \"/venv/lib/python3.10/site-packages/amf_iq_api/event.py\", line 23, in load_event_by_id",
            "    event = result.fetchone()[0]",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py\", line 1032, in fetchone",
            "    row = self._onerow_getter(self)",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py\", line 456, in onerow",
            "    row = self._fetchone_impl()",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py\", line 1674, in _fetchone_impl",
            "    row = next(self.iterator, _NO_ROW)",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/orm/loading.py\", line 147, in chunks",
            "    fetch = cursor._raw_all_rows()",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py\", line 392, in _raw_all_rows",
            "    return [make_row(row) for row in rows]",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/engine/result.py\", line 392, in <listcomp>",
            "    return [make_row(row) for row in rows]",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/array.py\", line 380, in process",
            "    return super_rp(",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/array.py\", line 360, in process",
            "    return self._proc_array(",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/array.py\", line 312, in _proc_array",
            "    return collection(itemproc(x) for x in arr)",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/array.py\", line 312, in <genexpr>",
            "    return collection(itemproc(x) for x in arr)",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/sql/sqltypes.py\", line 1754, in process",
            "    value = self._object_value_for_elem(value)",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/sql/sqltypes.py\", line 1645, in _object_value_for_elem",
            "    util.raise_(",
            "  File \"/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py\", line 207, in raise_",
            "    raise exception",

Versions

  • OS: Linux
  • Python: 3.10
  • SQLAlchemy: 1.4.27
  • Database: PostgreSQL 14
  • DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg2 2.9.2

Additional context

I worked around the issue by adapting the old code fragment for prior SQLAlchemy versions:

import re

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

def remove_quotes(s):
    if s[0] == '"':
        return s[1:-1]
    return s

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)
            if not inner:
                return []
            return [remove_quotes(part) for part in inner.split(",")]  # Remove quotes from array elements!

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

Activity

willnewton
added
requires triageNew issue that requires categorization
on Dec 9, 2021
jvanasco
added
postgresql
reflectionreflection of tables, columns, constraints, defaults, sequences, views, everything else
and removed
requires triageNew issue that requires categorization
on Dec 9, 2021
jvanasco

jvanasco commented on Dec 9, 2021

@jvanasco
jvanasco
on Dec 9, 2021
Member

Tagging @CaselIT who has been managing the reflection system lately.

CaselIT

CaselIT commented on Dec 9, 2021

@CaselIT
CaselIT
on Dec 9, 2021
Member

It seems that the enum values are not properly unquoted.

zzzeek
added this to the 1.4.x milestone on Dec 9, 2021
zzzeek
added
bugSomething isn't working
datatypesthings to do with database types, like VARCHAR and others
on Dec 9, 2021
CaselIT

CaselIT commented on Dec 9, 2021

@CaselIT
CaselIT
on Dec 9, 2021
Member

Hi,

I cannot reproduce. Please try to change the provided script to reproduce the error:

import sqlalchemy as sa

e = sa.create_engine(
    "postgresql+psycopg2://scott:tiger@127.0.0.1:5432/test",
    echo="debug",
)
with e.begin() as conn:
    conn.exec_driver_sql(
        """
drop table if exists test;
drop type if exists object_type;

create type object_type as enum (
  'Test',
  'Another Test'
);

create table test (
  object_types object_type[]
);
"""
    )

m2 = sa.MetaData()
t2 = sa.Table("test", m2, autoload_with=e)

print(repr(t2))

the above prints the expected
Table('test', MetaData(), Column('object_types', ARRAY(ENUM('Test', 'Another Test', name='object_type')), table=<test>), schema=None)

CaselIT
added
awaiting infowaiting for the submitter to give more information
on Dec 9, 2021
willnewton

willnewton commented on Dec 10, 2021

@willnewton
willnewton
on Dec 10, 2021
Author

Thanks for putting together the test case, I should have done that really! I've expanded it a little to include code similar to mine:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import DeferredReflection
from sqlalchemy.orm import declarative_base, Session
from sqlalchemy.orm.decl_api import DeclarativeMeta

e = sa.create_engine(
    "postgresql+psycopg2://scott:tiger@127.0.0.1:5432/test",
    echo="debug",
)
with e.begin() as conn:
    conn.exec_driver_sql(
        """
drop table if exists test;
drop type if exists object_type;

create type object_type as enum (
  'Test',
  'Another Test'
);

create table test (
  object_id int primary key,
  object_types object_type[]
);

insert into test values (1, '{"Test"}');
insert into test values (2, '{"Another Test"}');
"""
    )

class Reflected(DeferredReflection):
    __abstract__ = True

Base: DeclarativeMeta = declarative_base()

class TestClass(Reflected, Base):
    __tablename__ = "test"

Reflected.prepare(e)

with Session(e) as session:
    result = session.execute(sa.select(TestClass))
    for obj in result.scalars():
        print(f"{obj.object_types}")

This gives me the error:

LookupError: '"Another Test"' is not among the defined enum values. Enum name: object_type. Possible values: Test, Another Tes..
CaselIT

CaselIT commented on Dec 10, 2021

@CaselIT
CaselIT
on Dec 10, 2021
Member

So the issue is not with reflection, but when selecting the data. That's why it's important to provide a reproduction example.

Will take a look

CaselIT
removed
reflectionreflection of tables, columns, constraints, defaults, sequences, views, everything else
awaiting infowaiting for the submitter to give more information
on Dec 10, 2021

7 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

array of enumsthe most troublesome datatype everbugSomething isn't workingdatatypesthings to do with database types, like VARCHAR and otherspostgresql

Type

No type

Projects

No projects

Milestone

Relationships

None yet

    Development

    No branches or pull requests

      Participants

      @willnewton@zzzeek@jvanasco@sqla-tester@CaselIT

      Issue actions
        PostgreSQL arrays of enum break when enum contains spaces · Issue #7418 · sqlalchemy/sqlalchemy