postgresql – Alembic：alter column type

概述我试图使用alembic将SQLAlchemy PostgreSQL ARRAY(Text)字段转换为我的一个表列的BIT(vary = True)字段. 该列目前定义为： cols = Column(ARRAY(TEXT), nullable=False, index=True) 我想把它改成： cols = Column(BIT(varying=True), nullable=False, i 我试图使用alembic将sqlAlchemy Postgresql ARRAY(Text)字段转换为我的一个表列的BIT(vary = True)字段.

该列目前定义为：

cols = Column(ARRAY(TEXT),nullable=False,index=True)

我想把它改成：

cols = Column(BIT(varying=True),index=True)

默认情况下似乎不支持更改列类型,因此我手动编辑了alembic脚本.这是我目前的

def upgrade():    op.alter_column(        table_name='vIEws',column_name='cols',type_=postgresql.BIT(varying=True)    )def downgrade():    op.alter_column(        table_name='vIEws',type_=postgresql.ARRAY(sa.Text())    )

但是,运行此脚本会导致错误：

Traceback (most recent call last):  file "/home/home/.virtualenvs/deus_lex/bin/alembic",line 9,in <module>    load_entry_point('alembic==0.7.4','console_scripts','alembic')()  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/config.py",line 399,in main    Commandline(prog=prog).main(argv=argv)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/config.py",line 393,in main    self.run_cmd(cfg,options)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/config.py",line 376,in run_cmd    **dict((k,getattr(options,k)) for k in kwarg)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/command.py",line 165,in upgrade    script.run_env()  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/script.py",line 382,in run_env    util.load_python_file(self.dir,'env.py')  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/util.py",line 242,in load_python_file    module = load_module_py(module_ID,path)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/compat.py",line 79,in load_module_py    mod = imp.load_source(module_ID,path,fp)  file "./scripts/env.py",line 83,in <module>    run_migrations_online()  file "./scripts/env.py",line 76,in run_migrations_online    context.run_migrations()  file "<string>",line 7,in run_migrations  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/environment.py",line 742,in run_migrations    self.get_context().run_migrations(**kw)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/migration.py",line 305,in run_migrations    step.migration_fn(**kw)  file "/home/home/deus_lex/winslow/scripts/versions/2644864bf479_store_caseList_column_vIEws_as_bits.py",line 24,in upgrade    type_=postgresql.BIT(varying=True)  file "<string>",in alter_column  file "<string>",line 1,in <lambda>  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/util.py",line 387,in go    return fn(*arg,**kw)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/operations.py",line 470,in alter_column    existing_autoincrement=existing_autoincrement  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/ddl/impl.py",line 147,in alter_column    existing_nullable=existing_nullable,file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/ddl/impl.py",line 105,in _exec    return conn.execute(construct,*multiparams,**params)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 729,in execute    return meth(self,multiparams,params)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py",line 69,in _execute_on_connection    return connection._execute_ddl(self,params)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 783,in _execute_ddl    compiled  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 958,in _execute_context    context)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 1159,in _handle_dbAPI_exception    exc_info  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",line 199,in raise_from_cause    reraise(type(exception),exception,tb=exc_tb)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py",line 951,in _execute_context    context)  file "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py",line 436,in do_execute    cursor.execute(statement,parameters)sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "cols" cannot be cast automatically to type bit varyingHINT:  Specify a USING Expression to perform the conversion. 'ALTER table vIEws ALTER ColUMN cols TYPE BIT varying' {}

如何使用USING表达式更改我的脚本？

不幸的是,您需要使用原始sql作为alembic不会在更改类型时输出USING语句.

但是,为此编写自定义sql是非常容易的：

op.execute('ALTER table vIEws ALTER ColUMN cols TYPE bit varying USING expr')

当然,您必须使用将旧数据类型转换为新数据类型的表达式替换expr.

总结

以上是内存溢出为你收集整理的postgresql – Alembic：alter column type全部内容，希望文章能够帮你解决postgresql – Alembic：alter column type所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。