can't adapt type for enum in case()

Ouverte
  • Quelque chose ne va pas ?
#12,097 13 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
35/100
Type d'issue
Bug
Clarté
Plutôt claire
Activité
À l'abandon
Stack technique
postgresql, python
Domaine
databases

Piste de recherche

Commencez par le reproducer de l’issue avec PostgreSQL et psycopg2, puis examinez comment SQLAlchemy infère les types de la condition CASE et des valeurs de résultat lorsqu’ils sont associés à une colonne Enum. Vérifiez le comportement avec l’expression d’update indiquée et confirmez que des valeurs Enum équivalentes s’exécutent sans casts explicites.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

documentation expected behavior law of twos question
Describe the bug

I can't use an enum type as part of a case() without casting.

Looks very similar to https://github.com/sqlalchemy/sqlalchemy/issues/6515

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.36

DBAPI (i.e. the database driver)

psycopg2

Database Vendor and Major Version

PostgreSQL 12

Python Version

3.11

Operating system

macOS

To Reproduce
import enum

from sqlalchemy import case, create_engine, update, types as sqltypes
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker

Session = sessionmaker()


class Base(DeclarativeBase):
    pass


class State(enum.Enum):
    PENDING = enum.auto()
    RUNNING = enum.auto()
    CANCELLING = enum.auto()
    CANCELLED = enum.auto()
    
    
StateType = sqltypes.Enum(State)


class Job(Base):
    __tablename__ = "jobs"

    id: Mapped[int] = mapped_column(primary_key=True)
    state: Mapped[State] = mapped_column(StateType, server_default=State.PENDING.name)


def main(engine):
    with engine.connect() as conn:
        conn.execute(
            update(Job).values(
                state=case(
                    (
                        Job.state == State.PENDING,
                        State.CANCELLED,
                    ),
                    else_=State.CANCELLING,
                )
            )
        )


if __name__ == "__main__":
    engine = create_engine("postgresql:///issue12097", echo=True)
    Session.configure(bind=engine)
    Base.metadata.create_all(engine)
    try:
        main(engine)
    finally:
        Base.metadata.drop_all(engine)
Error
Traceback (most recent call last):
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: can't adapt type 'State'

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

Traceback (most recent call last):
  File "issue12096.py", line 50, in <module>
    main(engine)
  File "issue12096.py", line 32, in main
    conn.execute(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'State'
[SQL: UPDATE jobs SET state=CASE WHEN (jobs.state = %(state_1)s) THEN %(param_1)s ELSE %(param_2)s END]
[parameters: {'state_1': 'PENDING', 'param_1': <State.CANCELLED: 4>, 'param_2': <State.CANCELLING: 3>}]
(Background on this error at: https://sqlalche.me/e/20/f405)

It works if I add casts:

            update(Job).values(
                state=case(
                    (
                        Job.state == cast(State.PENDING, StateType),
                        cast(State.CANCELLED, StateType),
                    ),
                    else_=cast(State.CANCELLING, StateType),
                )
            )
Additional context

mcve requires you to run createdb issue12097 first.

Langage dominant
Python
Étoiles
12.3k
Forks
1.8k
Merge moyen
2 j 10 h
PR mergées (30 j)
9

Préparer son environnement

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de sqlalchemy/sqlalchemy

Toutes les issues de sqlalchemy/sqlalchemy

Issues similaires

Plus d'issues Python

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.