最新消息:20210917 已从crifan.com换到crifan.org

【无法解决】Flask-Migrate用Alembic脚本去升级数据库中字段的枚举值定义

Flask crifan 3110浏览 0评论

之前已经知道了:

【整理】Flask-Migrate经验 总结 心得 | 在路上

Flask-Migrate不支持自动检测出enum中的key和字符串value的变化

现在想办法去搞清楚:

对于如何把enmu的:

class TaskStatus(enum.Enum):
    Created = “Created”
    Started = “Started” # == task grabbed -> consider started
    ToBeDetermine = “ToBeDetermine” # after upload info, need initiator to be determined
    Delivering = “Delivering” # after upload info, no need initiator determine
    Completed = “Completed”
    Canceled = “Canceled”

改为:

class TaskStatus(enum.Enum):
    Created = “Created”
    Started = “Started” # == task grabbed -> consider started
    ToBeConfirmed = “ToBeConfirmed” # after upload info, need initiator confirm
    Delivering = “Delivering” # after upload info, no need initiator determine
    Completed = “Completed”
    Canceled = “Canceled”

即把TaskStatus中的ToBeDetermine改为ToBeConfirmed

-》之前的旧的办法是:

直接是通过(Mac的Sequel Pro中)直接修改(MySQL)数据库的定义而实现,修改enum的定义的:

现在要去搞清楚:

如何手动去改写生成的alembic脚本,去支持。 

flask-migrate  upgrade enum

flask-migrate   enum

alembic   enum

python – Flask-Migrate having issue with enum class in models – Stack Overflow

Handling changes to enum types · Issue #43 · miguelgrinberg/Flask-Migrate

建议:

最好不用enum,而改用引用表reference tables

python – Flask Migrate: Alembic converting choices into 255 – python-alembic-flask-migrateflaskflask-sqlalchemy – ItGo.me

python – Altering an Enum field using Alembic – Stack Overflow

Existing PostgreSQL ENUMs in Alembic (Example)

dw/alembic-autogenerate-enums: Alembic hooks to cause PostgreSQL ALTER TYPE .. ADD VALUE .. to be issued automatically

Re: Using alembic with the Declarative Enum recipe.

alembic change existing enum

zzzeek / alembic / issues / #270 – Altering enum type — Bitbucket

说也是可以的?

op.alter_column(“my_table”, “my_column”, existing_type=ENUM(…), type_=ENUM())

How do I change enum values after previously creating a table? – Google Groups

Since PostgreSQL types are not owned by tables, I don’t think there is a good way to make this automatic. You can try adding an “after-drop” DDL event that drops the User_Roles type when you drop the table, but that has its own problems.
If you are looking to migrate table data to use the new enum, a script like this should work:
ALTER TYPE User_Roles RENAME TO User_Roles_Old;
CREATE TYPE User_Roles AS ENUM (‘user’, ‘superuser’);
ALTER TABLE some_table ALTER COLUMN some_column TYPE User_Roles USING
  CASE some_column
    WHEN ‘user’::User_Roles_Old THEN ‘user’::User_Roles
    WHEN ‘admin’::User_Roles_Old THEN ‘superuser’::User_Roles
  END;
DROP TYPE User_Roles_Old;

Auto Generating Migrations — Alembic 0.8.9 documentation

  • Special SQLAlchemy types such as Enum when generated on a backend which doesn’t support ENUM directly – this because the representation of such a type in the non-supporting database, i.e. a CHAR+ CHECK constraint, could be any kind of CHAR+CHECK. For SQLAlchemy to determine that this is actually an ENUM would only be a guess, something that’s generally a bad idea. To implement your own “guessing” function here, use the sqlalchemy.events.DDLEvents.column_reflect() event to detect when a CHAR (or whatever the target type is) is reflected, and change it to an ENUM (or whatever type is desired) if it is known that that’s the intent of the type. Thesqlalchemy.events.DDLEvents.after_parent_attach() can be used within the autogenerate process to intercept and un-attach unwanted CHECK constraints.

-》此处不是postgresql,其内置支持ENUM,所以不支持这么写:

ENUM

flask.ext.sqlalchemy.SQLAlchemy.Enum – Nullege Python Samples

alembic alter enum

alembic  db enum

alembic document

Welcome to Alembic’s documentation! — Alembic 0.8.9 documentation

Running “Batch” Migrations for SQLite and Other Databases — Alembic 0.8.9 documentation

参考自己之前的:

[已解决]SQLAlchemy中添加枚举类型字段

中的:

Downgrade don’t drop ENUM Column Types · Issue #48 · miguelgrinberg/Flask-Migrate

->

sa.Column(‘type’, sa.Enum(‘a’, ‘b’,’c’, name=’custom_type’), nullable=True),

和:

from alembic import op
import sqlalchemy as sa
NotificationTime = sa.Enum(u”不提醒”, u”事件发生时”, u”5分钟前”, u”15分钟前”, u”30分钟前”, u”1小时前”, u”2小时前”, u”1天前”, u”2天前”, u”
1周前”, name=’NotificationTime’)
def upgrade():
    ### commands auto generated by Alembic – please adjust! ###
    #op.add_column(‘events’, sa.Column(‘notification_time’, sa.Enum(<enum ‘NotificationTime’>), nullable=True))
    op.add_column(‘events’, sa.Column(‘notification_time’, NotificationTime, nullable=True))
    ### end Alembic commands ###

那么先去生成脚本,然后再去手动改为:

“””change ToBeDetermine to ToBeConfirmed of TaskStatus
Revision ID: ee1ce65b0254
Revises: 2fcd2aa96580
Create Date: 2016-11-04 17:53:21.701974
“””
# revision identifiers, used by Alembic.
revision = ‘ee1ce65b0254’
down_revision = ‘2fcd2aa96580’
from alembic import op
import sqlalchemy as sa
def upgrade():
    ### commands auto generated by Alembic – please adjust! ###
    。。。
    op.alter_column(“tasks”,
                    “statusType”,
                    default=”Created”,
                    existing_type=sa.Enum(“Created”, “Started”, “ToBeDetermine”, “Delivering”, “Completed”, “Canceled”, name=”TaskStatus”),
                    type=sa.Enum(“Created”, “Started”, “ToBeConfirmed”, “Delivering”, “Completed”, “Canceled”, name=”TaskStatus”))
    ### end Alembic commands ###
def downgrade():
    ### commands auto generated by Alembic – please adjust! ###
    。。。
    op.alter_column(“tasks”,
                    “statusType”,
                    default=”Created”,
                    existing_type=sa.Enum(“Created”, “Started”, “ToBeConfirmed”, “Delivering”, “Completed”, “Canceled”, name=”TaskStatus”),
                    type=sa.Enum(“Created”, “Started”, “ToBeDetermine”, “Delivering”, “Completed”, “Canceled”, name=”TaskStatus”))
    ### end Alembic commands ###

然后去upgrade试试:

没有报错:

(RunningFast) ➜  staging python db_manager.py db upgrade

<div–<——————————————————————————

DEBUG in app [/root/RunningFast/staging/runningfast/app.py:113]:
app=<Flask ‘runningfast.app’>, server_port=21085, api=<flask_restful.Api object at 0x7f4df3c9d2d0>, redis_store=<flask_redis.FlaskRedis object at 0x7f4df5f1b890>, db=<SQLAlchemy engine=’mysql://runningfast:Jiandao123@localhost/runningfast_dev’>, server_mode=staging, server_type=develop, rq=<flask_rq2.app.RQ object at 0x7f4df3c9d710>, sockets=<flask_sockets.Sockets object at 0x7f4df3c9da90>

<div–<——————————————————————————

<div–<——————————————————————————

DEBUG in app [/root/RunningFast/staging/runningfast/app.py:213]:
API_VERSION=1.0, API_URL_PREFIX=/runningfast/api/v1.0, OPEN_API_URL_PREFIX=/runningfast/api/v1.0/open

<div–<——————————————————————————

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 2fcd2aa96580 -> ee1ce65b0254, change ToBeDetermine to ToBeConfirmed of TaskStatus

然后再去数据库中看看,是否已经被改过来了:

结果还是没有改过来:

还是旧的:

ToBeDetermine

结果只能是手动去修改:

【总结】

此处,虽然可以修改Flask-Migrate生成的alembic的脚本,使用sa的Enum去alter_column

但是结果还是无效。

-》还是手动去数据库中修改Enum的值的吧。

转载请注明:在路上 » 【无法解决】Flask-Migrate用Alembic脚本去升级数据库中字段的枚举值定义

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
92 queries in 0.185 seconds, using 23.47MB memory