una - convertir tipos de datos en postgresql

Alambique: modifique el tipo de columna con USING

Question

Intento usar alambic para convertir un campo SQLAlchemy PostgreSQL ARRAY (Texto) en un campo BIT (variable = Verdadero) para una de las columnas de mi tabla.

La columna se define actualmente como:

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

Quiero cambiarlo a:

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

Cambiar los tipos de columna no parece ser compatible por defecto, por lo que estoy editando el script alambique a mano. Esto es lo que tengo actualmente:

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())
)

Sin embargo, al ejecutar este script se obtiene el 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'' {}

¿Cómo puedo cambiar mi script con la expresión USING?

Answer 1

Desafortunadamente necesita usar SQL sin procesar ya que el alambique no emite una instrucción USING al cambiar los tipos.

Sin embargo, escribir SQL personalizado para esto es bastante fácil:

op.execute(''ALTER TABLE views ALTER COLUMN cols TYPE bit varying USING expr'')

Por supuesto, debe reemplazar expr con una expresión que convierta el tipo de datos anterior al nuevo tipo de datos.

Answer 2

Desde la versión 0.8.8, el alambic admite USING de PostgreSQL con el parámetro postgresql_using :

op.alter_column(''views'', ''cols'', type_=postgresql.BIT(varying=True), postgresql_using=''expr'')