Alembic:使用USING更改列类型 [英] Alembic: alter column type with USING
问题描述
我正在尝试使用alembic将我的一个表列的SQLAlchemy PostgreSQL ARRAY(Text)字段转换为BIT(varying = True)字段。
I'm attempting to use alembic to convert a SQLAlchemy PostgreSQL ARRAY(Text) field to a BIT(varying=True) field for one of my table columns.
该列当前定义为:
cols = Column(ARRAY(TEXT), nullable=False, index=True)
我想将其更改为:
cols = Column(BIT(varying=True), nullable=False, index=True)
默认情况下似乎不支持更改列类型,因此我正在手动编辑alembic脚本。这是我目前拥有的东西:
Changing column types doesn't seem to be supported by default, so I'm editing the alembic script by hand. This is what I have currently:
def upgrade():
op.alter_column(
table_name='views',
column_name='cols',
nullable=False,
type_=postgresql.BIT(varying=True)
)
def downgrade():
op.alter_column(
table_name='views',
column_name='cols',
nullable=False,
type_=postgresql.ARRAY(sa.Text())
)
但是,运行此脚本可以错误:
However, running this script gives the error:
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>", line 7, 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, multiparams, 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 varying
HINT: Specify a USING expression to perform the conversion.
'ALTER TABLE views ALTER COLUMN cols TYPE BIT VARYING' {}
如何更改我的脚本带有USING表达式?
How can I change my script with the USING expression?
推荐答案
不幸的是,您需要使用原始SQL,因为Alembic不会输出使用
语句来更改类型。
Unfortunately you need to use raw SQL as alembic doesn't output a USING
statement ever when changing types.
但是,为此编写自定义SQL很容易:
However, writing custom SQL for this is quite easy:
op.execute('ALTER TABLE views ALTER COLUMN cols TYPE bit varying USING expr')
当然,您必须用将旧数据类型转换为新数据类型的表达式替换 expr
。
Of course you have to replace expr
with an expression that converts the old data type to the new data type.
这篇关于Alembic:使用USING更改列类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!