之前用Flask-Migrate去升级SQLAlchemy的数据库，底层是操作MySQL的数据库
但是有个问题
当更改了其中一个table的字段Column时，比如：
class Bill(db.Model): __tablename__ = ‘bills’ disbursementFee = db.Column(db.Float, nullable=False, default = 0.0) |
改为：
class Bill(db.Model): __tablename__ = ‘bills’ advancedFee = db.Column(db.Float, nullable=False, default = 0.0) |
把disbursementFee改为advancedFee
然后去运行：
(RunningFast) ➜ staging python db_manager.py db migrate -m "update Bill, from disbursementFee to advancedFee" <div–<—————————————————————————— DEBUG in app [/root/RunningFast/staging/runningfast/app.py:86]: app=<Flask ‘runningfast.app’>, server_port=21085, api=<flask_restful.Api object at 0x7f850afdbc90>, redis_store=<flask_redis.FlaskRedis object at 0x7f850d473310>, db=<SQLAlchemy engine=’mysql://runningfast:Jiandao123@localhost/runningfast_dev’>, server_mode=staging, server_type=develop, rq=<flask_rq2.app.RQ object at 0x7f850aff2110>, sockets=<flask_sockets.Sockets object at 0x7f850aff2490> <div–<—————————————————————————— <div–<—————————————————————————— DEBUG in app [/root/RunningFast/staging/runningfast/app.py:181]: 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.autogenerate.compare] Detected added column ‘bills.advancedFee’ INFO [alembic.autogenerate.compare] Detected added foreign key (errandorBillTaskId)(id) on table bills INFO [alembic.autogenerate.compare] Detected added foreign key (initiatorBillTaskId)(id) on table bills INFO [alembic.autogenerate.compare] Detected removed column ‘bills.disbursementFee’ INFO [alembic.autogenerate.compare] Detected added foreign key (initiatorTaskId)(id) on table resources INFO [alembic.autogenerate.compare] Detected added foreign key (errandorTaskId)(id) on table resources INFO [alembic.autogenerate.compare] Detected added foreign key (errandorId)(id) on table tasks INFO [alembic.autogenerate.compare] Detected added foreign key (errandorEndLocationId)(id) on table tasks INFO [alembic.autogenerate.compare] Detected added foreign key (initiatorEndLocationId)(id) on table tasks INFO [alembic.autogenerate.compare] Detected added foreign key (initiatorStartLocationId)(id) on table tasks INFO [alembic.autogenerate.compare] Detected added foreign key (errandorStartLocationId)(id) on table tasks INFO [alembic.autogenerate.compare] Detected added foreign key (initiatorId)(id) on table tasks INFO [alembic.autogenerate.compare] Detected added foreign key (task_id)(id) on table tasks_promotions INFO [alembic.autogenerate.compare] Detected added foreign key (promotion_id)(id) on table tasks_promotions INFO [alembic.autogenerate.compare] Detected added foreign key (locationId)(id) on table users Generating /root/RunningFast/staging/migrations/versions/d48c252c778a_update_bill_from_disbursementfee_to_.py … done |
后，去看生成的脚本
/Users/crifan/dev/dev_root/daryun/Projects/RunningFast/sourcecode/RunningFast-Server/migrations/versions/d48c252c778a_update_bill_from_disbursementfee_to_.py
中的是：
def upgrade(): ### commands auto generated by Alembic – please adjust! ### op.add_column(‘bills’, sa.Column(‘advancedFee’, sa.Float(), nullable=False)) op.create_foreign_key(None, ‘bills’, ‘tasks’, [‘errandorBillTaskId’], [‘id’]) op.create_foreign_key(None, ‘bills’, ‘tasks’, [‘initiatorBillTaskId’], [‘id’]) op.drop_column(‘bills’, ‘disbursementFee’) op.create_foreign_key(None, ‘resources’, ‘tasks’, [‘initiatorTaskId’], [‘id’]) op.create_foreign_key(None, ‘resources’, ‘tasks’, [‘errandorTaskId’], [‘id’]) op.create_foreign_key(None, ‘tasks’, ‘users’, [‘errandorId’], [‘id’]) op.create_foreign_key(None, ‘tasks’, ‘locations’, [‘errandorEndLocationId’], [‘id’]) op.create_foreign_key(None, ‘tasks’, ‘locations’, [‘initiatorEndLocationId’], [‘id’]) op.create_foreign_key(None, ‘tasks’, ‘locations’, [‘initiatorStartLocationId’], [‘id’]) op.create_foreign_key(None, ‘tasks’, ‘locations’, [‘errandorStartLocationId’], [‘id’]) op.create_foreign_key(None, ‘tasks’, ‘users’, [‘initiatorId’], [‘id’]) op.create_foreign_key(None, ‘tasks_promotions’, ‘tasks’, [‘task_id’], [‘id’]) op.create_foreign_key(None, ‘tasks_promotions’, ‘promotions’, [‘promotion_id’], [‘id’]) op.create_foreign_key(None, ‘users’, ‘locations’, [‘locationId’], [‘id’]) ### end Alembic commands ### def downgrade(): ### commands auto generated by Alembic – please adjust! ### op.drop_constraint(None, ‘users’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks_promotions’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks_promotions’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks’, type_=’foreignkey’) op.drop_constraint(None, ‘tasks’, type_=’foreignkey’) op.drop_constraint(None, ‘resources’, type_=’foreignkey’) op.drop_constraint(None, ‘resources’, type_=’foreignkey’) op.add_column(‘bills’, sa.Column(‘disbursementFee’, mysql.FLOAT(), nullable=False)) op.drop_constraint(None, ‘bills’, type_=’foreignkey’) op.drop_constraint(None, ‘bills’, type_=’foreignkey’) op.drop_column(‘bills’, ‘advancedFee’) ### end Alembic commands ### |
即，对于：
upgrade
去
op.drop_column(‘bills’, ‘disbursementFee’)
op.add_column(‘bills’, sa.Column(‘advancedFee’, sa.Float(), nullable=False))
即：drop旧的字段，add新的字段，则旧的字段中的数据：
在drop后，就都丢失了。。。
所以希望是：
能否有个
op.rename_column
可以把之前的disbursementFee改为新的advancedFee
flask-migrate rename column
renaming columns – not dropping old and creating new · Issue #30 · miguelgrinberg/Flask-Migrate
去查查看，是否有个：
op.alter_column
Change from Integer to String not detected. · Issue #24 · miguelgrinberg/Flask-Migrate
No changes detected in Alembic autogeneration of migrations with Flask-SQLAlchemy – Stack Overflow
python – How to write alter column name migrations with sqlalchemy-migrate? – Stack Overflow
postgresql – Flask-migrate and changing column type – Stack Overflow
是有个：
op.alter_column
但是要去搞清楚，此处语法如何写。
alembic – can not rename column using alter_column – Stack Overflow
sqlite是不支持列改名的
-》MySQL应该是支持的。
Welcome to Flask-Migrate’s documentation! — Flask-Migrate documentation
结果没有找到语法。
从：
from alembic import op
看到：
此处的op是alembic，所以alter_column也应该是去找alembic
alembic alter_column
alembic alter_column rename
Operation Reference — Alembic 0.8.9 documentation
alter_column(table_name, column_name, nullable=None, server_default=False, new_column_name=None, type_=None, existing_type=None, existing_server_default=False, existing_nullable=None, schema=None, **kw) Issue an “alter column” instruction using the current migration context. Generally, only that aspect of the column which is being changed, i.e. name, type, nullability, default, needs to be specified. Multiple changes can also be specified at once and the backend should “do the right thing”, emitting each change either separately or together as the backend allows. MySQL has special requirements here, since MySQL cannot ALTER a column without a full specification. When producing MySQL-compatible migration files, it is recommended that the existing_type, existing_server_default, and existing_nullable parameters be present, if not being altered. Type changes which are against the SQLAlchemy “schema” types Boolean and Enum may also add or drop constraints which accompany those types on backends that don’t support them natively. The existing_type argument is used in this case to identify and remove a previous constraint that was bound to the type object. Parameters: table_name – string name of the target table. column_name – string name of the target column, as it exists before the operation begins. nullable – Optional; specify True or False to alter the column’s nullability. server_default – Optional; specify a string SQL expression, text(), or DefaultClause to indicate an alteration to the column’s default value. Set to None to have the default removed. new_column_name – Optional; specify a string name here to indicate the new name within a column rename operation. type_ – Optional; a TypeEngine type object to specify a change to the column’s type. For SQLAlchemy types that also indicate a constraint (i.e. Boolean, Enum), the constraint is also generated. autoincrement – set the AUTO_INCREMENT flag of the column; currently understood by the MySQL dialect. existing_type – Optional; a TypeEngine type object to specify the previous type. This is required for all MySQL column alter operations that don’t otherwise specify a new type, as well as for when nullability is being changed on a SQL Server column. It is also used if the type is a so-called SQLlchemy “schema” type which may define a constraint (i.e. Boolean, Enum), so that the constraint can be dropped. existing_server_default – Optional; The existing default value of the column. Required on MySQL if an existing default is not being changed; else MySQL removes the default. existing_nullable – Optional; the existing nullability of the column. Required on MySQL if the existing nullability is not being changed; else MySQL sets this to NULL. existing_autoincrement – Optional; the existing autoincrement of the column. Used for MySQL’s system of altering a column that specifies AUTO_INCREMENT. schema – Optional schema name to operate within. To control quoting of the schema outside of the default behavior, use the SQLAlchemy construct quoted_name. New in version 0.7.0: ‘schema’ can now accept a quoted_name construct. postgresql_using¶ – String argument which will indicate a SQL expression to render within the Postgresql-specific USING clause within ALTER COLUMN. New in version 0.8.8. |
可以看到有：column_name和new_column_name
应该就是rename了。
[python]通过 alembic 重命名表列名（Model字段名） – Mozillazg’s Blog
from sqlalchemy.dialects import mysql def upgrade(): ### commands auto generated by Alembic – please adjust! ### # 把 user 表里的 name 列重命名为 username op.alter_column(‘user’, ‘name’, new_column_name=’username’, existing_type=mysql.VARCHAR(length=20)) ### end Alembic commands ### def downgrade(): ### commands auto generated by Alembic – please adjust! ### op.alter_column(‘user’, ‘username’, new_column_name=’name’, existing_type=mysql.VARCHAR(length=20)) ### end Alembic commands ### |
alembic.op.alter_column – Nullege Python Samples
op.drop_constraint(‘foldersync_ibfk_1’, ‘foldersync’, type_=’foreignkey’) op.alter_column(‘foldersync’, ‘imapaccount_id’, new_column_name=’account_id’, existing_type=sa.Integer(), existing_nullable=False) |
结果：
然后去看对应的数据库的字段，是否真的改过来了，且原先的数据保留下来了：
结论是可以的。
【总结】
Flask-Migrate中去升级数据库字段，底层是调用Alembic去实现的。
而对于重命名Column列名的话，对应的函数是：alter_column
具体用法详见官网文档：
Operation Reference — Alembic 0.8.9 documentation
其中解释的也很清楚了：
一般来说，只需要传入需要alter修改的参数，比如表名，列名，是否为null，默认值，等等，即可。
但是MYSQL比较特殊，没有完整的参数的话，是不支持修改列名的
-》所以只能传入完整的参数了。。
-》所以修改列名，推荐同时把对应的参数传递上：
existing_type, existing_server_default, and existing_nullable
-》
但是此处，如果传了existing_server_default，则会导致出错：
然后不传existing_server_default，用：
from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import mysql def upgrade(): ### commands auto generated by Alembic – please adjust! ### # op.add_column(‘bills’, sa.Column(‘advancedFee’, sa.Float(), nullable=False)) op.alter_column(‘bills’, ‘disbursementFee’, new_column_name=’advancedFee’, existing_type=sa.Float, existing_nullable=False) #op.drop_column(‘bills’, ‘disbursementFee’) ### end Alembic commands ### def downgrade(): ### commands auto generated by Alembic – please adjust! ### # op.add_column(‘bills’, sa.Column(‘disbursementFee’, mysql.FLOAT(), nullable=False)) # op.drop_column(‘bills’, ‘advancedFee’) op.alter_column(‘bills’, ‘advancedFee’, new_column_name=’disbursementFee’, existing_type=sa.Float, existing_nullable=False) ### end Alembic commands ### |
就可以了。
然后就可以正常升级了：
python db_manager.py db upgrade