在PostgreSQL和Alembic中更新ENUM类型时出现问题。

huangapple
go评论96阅读模式
英文:

Problem with updating an ENUM type in postgresql and alembic

问题

我在postgresql和alembic中有一个关于ENUM的问题，我无法通过现有的StackOverflow主题解决这个问题。

我有以下的代码：

  1. from sqlalchemy.dialects.postgresql import ENUM
  2. from enum import Enum
  4. class StatusEnum(Enum):
  5.     requested = "requested"
  6.     accepted = "accepted"
  7.     declined = "declined"
  9. class EventModifications(BaseModel):
  10.     __tablename__ = "event_modifications"
  12.     old_value = Column(Text)
  13.     new_value = Column(Text)
  14.     status = Column(
  15.         ENUM(StatusEnum),
  16.         default=StatusEnum.accepted.value,
  17.         server_default=StatusEnum.accepted.value
  18.     )

然后我在枚举类中添加了一个实体，并且也将status列的默认值更改为新添加的值：

  1. class StatusEnum(Enum):
  2.     requested = "requested"
  3.     accepted = "accepted"
  4.     declined = "declined"
  5.     modified = "modified"  # 新添加的
  7. class EventModifications(BaseModel):
  8.     __tablename__ = "event_modifications"
  10.     old_value = Column(Text)
  11.     new_value = Column(Text)
  12.     status = Column(
  13.         ENUM(StatusEnum),
  14.         default=StatusEnum.modified.value,  # 更改了默认值
  15.         server_default=StatusEnum.modified.value
  16.     )

Alembic引发的错误：

  1. sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "status" cannot be cast automatically to type statusenum
  2. HINT:  You might need to specify "USING status::statusenum".
  4. [SQL: ALTER TABLE event_modifications ALTER COLUMN status TYPE statusenum ]

但是当我将StatusEnum类的名称更改为其他名称时，它正常工作！

英文:

I have an issue with ENUM in postgresql and alembic and I couldn't resolve the problem using the existing topics in the StackOverflow in this regard.

I had the following code:

  1. from sqlalchemy.dialects.postgresql import ENUM
  2. from enum import Enum
  4. class StatusEnum(Enum):
  5.     requested = "requested"
  6.     accepted = "accepted"
  7.     declined = "declined"
  9. class EventModifications(BaseModel):
  10.     __tablename__ = "event_modifications"
  12.     old_value = Column(Text)
  13.     new_value = Column(Text)
  14.     status = Column(
  15.         ENUM(StatusEnum),
  16.         default=StatusEnum.accepted.value,
  17.         server_default=StatusEnum.accepted.value
  18.     )

Then I appended an entity in the Enum class and also changed the default value of status column to the new added value:

  1. class StatusEnum(Enum):
  2.     requested = "requested"
  3.     accepted = "accepted"
  4.     declined = "declined"
  5.     modified = "modified"  # new one
  7. class EventModifications(BaseModel):
  8.     __tablename__ = "event_modifications"
  10.     old_value = Column(Text)
  11.     new_value = Column(Text)
  12.     status = Column(
  13.         ENUM(StatusEnum),
  14.         default=StatusEnum.modified.value,  # changed the defaults
  15.         server_default=StatusEnum.modified.value
  16.     )

Error caused by Alembic:

  1. sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "status" cannot be cast automatically to type statusenum
  2. HINT:  You might need to specify "USING status::statusenum".
  4. [SQL: ALTER TABLE event_modifications ALTER COLUMN status TYPE statusenum ]

But when I change the name of StatusEnum class to something else it works well!

答案1

得分: 2

你可以使用以下查询来解决你的问题：

  1. ALTER TABLE "event_modifications" DROP "status";
  1. DROP TYPE "statusenum"
  • 请注意你的数据将被删除，你需要再次调用alembic来进行迁移。
英文:

You can use these queries to solve your problem

1.

  1. ALTER TABLE "event_modifications" DROP "status";

2.

  1. DROP TYPE "statusenum"
  • note that your data will be removed and you need call alembic again to migratation.

huangapple
  • 本文由 发表于 2023年5月25日 19:33:44
  • 转载请务必保留本文链接：https://go.coder-hub.com/76331812.html
  • alembic
  • enums
  • postgresql
  • python
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定