SQLAlchemy 1.0.8 Core fails to convert to native type

[Yegor Roganov]

I have the following model:

class PortfolioItem(Base):
    class STATUS(Enum):
        active = 0
        deleted = 1
    status = Column(EnumInt(STATUS), default=STATUS.active)
    # ...

where EnumInt is a custom field (it just converts an enum back and forth).

Consider the following statement:

PortfolioItem.__table__.insert(items).returning(PortfolioItem.id)

This then throws an exception:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type
'STATUS' [SQL: 'INSERT INTO portfolio (performer_id, status, date_created, description, category_id) VALUES (%(performer_id_0)s, %(status)s, %(date_created)s, %(description_0)s, %(category_id_0)s), (%(performer_id_1)s, %(status_1)s, %(date_created_1)s, %(description_1)s, %(category_id_1)s) RETURNING portfolio.id']
[parameters: {'description_1': '', 'performer_id_1': 1, 'category_id_1': None, 'category_id_0': None, 'status_1': <STATUS.active: 0>, 'performer_id_0': 1, 'description_0': '', 'date_created_1': datetime.datetime(2015, 8, 31, 13, 43, 58, 366724), 'date_created': datetime.datetime(2015, 8, 31, 13, 43, 58, 366716), 'status': 0}]

Note that status parameter has been converted to 0 as expected, while status_1 is of type Enum.

It works in 0.9.8

[Mike Bayer]

Hi there -

I need source code please, including your custom type's source as well as the contents of the "items" collection above. Cannot reproduce:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from enum import Enum

Base = declarative_base()


class EnumInt(TypeDecorator):
    impl = Integer

    def process_bind_param(self, value, dialect):
        if value is not None:
            value = value.value
        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            value = PortfolioItem.STATUS.active \
                if value == 0 else PortfolioItem.STATUS.deleted
        return value


class PortfolioItem(Base):
    __tablename__ = 'p'
    id = Column(Integer, primary_key=True)

    class STATUS(Enum):
        active = 0
        deleted = 1
    status = Column(EnumInt(), default=STATUS.active)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

items = [
    {'id': 1, 'status': PortfolioItem.STATUS.active},
    {'id': 2, 'status': PortfolioItem.STATUS.active},
    {'id': 3, 'status': PortfolioItem.STATUS.deleted}
]

assert type(PortfolioItem.STATUS.active) is PortfolioItem.STATUS

stmt = PortfolioItem.__table__.insert(items).returning(PortfolioItem.id)

e.execute(stmt)

output:

INSERT INTO p (id, status) VALUES (%(id_0)s, %(status_0)s), (%(id_1)s, %(status_1)s), (%(id_2)s, %(status_2)s) RETURNING p.id
2015-08-31 10:22:55,086 INFO sqlalchemy.engine.base.Engine {'id_1': 2, 'status_2': 1, 'id_2': 3, 'status_0': 0, 'status_1': 0, 'id_0': 1}

[Mike Bayer]

not enough information provided

[Yegor Roganov]

Hi! In your code, you're providing values for status field explicitly, while I want them to be picked up from the default parameter.

Custom type:

class EnumInt(TypeDecorator):

    impl = SmallInteger

    def __init__(self, enum, *args, **kwargs):
        self._enum = enum
        super().__init__(*args, **kwargs)

    def process_bind_param(self, enum, dialect):
        if enum is None:
            return None
        return enum.value

    def process_result_value(self, value, dialect):
        if value is not None:
            return self._enum(value)
        return value

Complete model:

class PortfolioItem(Base):

    __tablename__ = 'portfolio'

    class STATUS(Enum):
        active = 0
        deleted = 1

    id = Column(Integer, primary_key=True, nullable=False)
    performer_id = Column(Integer, ForeignKey('performer.id'))
    performer = relationship('Performer', backref='portfolio_items')

    status = Column(EnumInt(STATUS), default=STATUS.active)
    date_created = Column(DateTime, default=datetime.utcnow)

    description = Column(Text, nullable=False)

    category_id = Column(Integer, ForeignKey('category.id'))
    category = relationship('Category')

'items' collection: [{'description': 'asdfadsf', 'category_id': None, 'performer_id': 1}, {'description': 'asdfasdfasddfasdfasdf', 'category_id': None, 'performer_id': 1}]

Code that fails:

insert_stmt = PortfolioItem.__table__.insert(items).returning(PortfolioItem.id)
session.connection().execute(insert_stmt)

(Sorry, I should've posted more info when opening the issue)

[Mike Bayer]

okey doke, got a patch

[Mike Bayer]

• Fixed regression in 1.0-released default-processor for multi-VALUES insert statement, 3288, where the column type for the default-holding column would not be propagated to the compiled statement in the case where the default was being used, leading to bind-level type handlers not being invoked. fixes #3520

→ <<cset c39ff9978dbb>>

[Mike Bayer]

• Fixed regression in 1.0-released default-processor for multi-VALUES insert statement, 3288, where the column type for the default-holding column would not be propagated to the compiled statement in the case where the default was being used, leading to bind-level type handlers not being invoked. fixes #3520

(cherry picked from commit c39ff9978dbb77cbea4f1ee08234887d8aa1b165)

→ <<cset 161209c70add>>

[Mike Bayer]

thanks, this will be out in 1.0.9

[Yegor Roganov]

This was super quick, thanks!