Issue with generated enums not being insertable

[coby-astsec]

Things to check first

• I have searched the existing issues and didn't find my bug already reported there

• I have checked that my bug is still present in the latest release

Sqlacodegen version

4.0.0rc1

SQLAlchemy version

2.0.45

RDBMS vendor

PostgreSQL

What happened?

When using the generated enums, I can't insert any objects:

from sqlalchemy import create_engine                                                                                                                                                        
from sqlalchemy.orm import Session                                                                                                                                                          
                                                                                                                                                                                            
from object import MyTable, MyEnum                                                                                                                                                          
                                                                                                                                                                                            
                                                                                                                                                                                            
def main():                                                                                                                                                                                 
    engine = create_engine("postgresql+psycopg://postgres:postgres@localhost/")                                                                                                      
                                                                                                                                                                                            
    with Session(engine) as session:                                                                                                                                                        
        row = MyTable(my_enum=MyEnum.A)                                                                                                                                                     
        session.add(row)                                                                                                                                                                    
        session.commit()                                                                                                                                                                    
        print(f"Added row with id={row.id}")                                                                                                                                                
                                                                                                                                                                                            
                                                                                                                                                                                            
if __name__ == "__main__":                                                                                                                                                                  
    main()

Exception:

sqlalchemy.exc.DataError: (psycopg.errors.InvalidTextRepresentation) invalid input value for enum c.my_enum: "MyEnum.A"
CONTEXT:  unnamed portal parameter $1 = '...'
[SQL: INSERT INTO c.my_table (my_enum) VALUES (%(my_enum)s) RETURNING c.my_table.id]
[parameters: {'my_enum': 'MyEnum.A'}]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Database schema for reproducing the bug

Sql code:

CREATE SCHEMA c;
CREATE TYPE c.my_enum AS ENUM (
    'a',
    'b'
);
CREATE TABLE c.my_table (
    id INT PRIMARY KEY,
    my_enum c.my_enum NOT NULL
);

Generated code:

import enum

from sqlalchemy import Enum, Integer, PrimaryKeyConstraint
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass


class MyEnum(str, enum.Enum):
    A = 'a'
    B = 'b'


class MyTable(Base):
    __tablename__ = 'my_table'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='my_table_pkey'),
        {'schema': 'c'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    my_enum: Mapped[MyEnum] = mapped_column(Enum(MyEnum), nullable=False)

[sheinbergon]

Thank you for reporting this issue in the release candidate. I'll review it in the following days, and resort to a fix

[sheinbergon]

Looks sqlalchemy insists of passing the enum constant name instead of the value. SQLAlchemy has a way of working around it using values_callable. I'll add it and issue a PR. Expect 4.0.0rc2 to land sometimes next week

[sheinbergon]

Relead in 4.0.0rc2