-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
On 01/22/2016 07:44 AM, Michal Petrucha wrote:
> Hello people,
>
> I'm having difficluty changing the type of a column from Boolean
> to Integer on SQLite. Boolean generates an integer column with a
> CHECK constraint; changing the type should just drop the constraint
> (on SQLite at least).
>
> The problem is, when batch_op processes a command like this:
>
> batch_op.alter_column(name, type_=sa.Integer(),
> existing_type=sa.Boolean())
>
> errors out with errors looking something like this:
>
> Traceback (most recent call last): File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/batch.py",
> line 314, in drop_constraint del
> self.named_constraints[const.name] KeyError: '_unnamed_'
>
> During handling of the above exception, another exception
> occurred:
>
> Traceback (most recent call last): File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/bin/alembic",
> line 11, in <module> sys.exit(main()) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/config.py",
> line 471, in main CommandLine(prog=prog).main(argv=argv) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/config.py",
> line 465, in main self.run_cmd(cfg, options) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/config.py",
> line 448, in run_cmd **dict((k, getattr(options, k)) for k in
> kwarg) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/command.py",
> line 174, in upgrade script.run_env() File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/script/base.py",
> line 397, in run_env util.load_python_file(self.dir, 'env.py') File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/util/pyfiles.py",
> line 81, in load_python_file module = load_module_py(module_id,
> path) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/util/compat.py",
> line 68, in load_module_py module_id, path).load_module(module_id)
> File "<frozen importlib._bootstrap>", line 539, in
> _check_name_wrapper File "<frozen importlib._bootstrap>", line
> 1614, in load_module File "<frozen importlib._bootstrap>", line
> 596, in _load_module_shim File "<frozen importlib._bootstrap>",
> line 1220, in load File "<frozen importlib._bootstrap>", line 1200,
> in _load_unlocked File "<frozen importlib._bootstrap>", line 1129,
> in _exec File "<frozen importlib._bootstrap>", line 1471, in
> exec_module File "<frozen importlib._bootstrap>", line 321, in
> _call_with_frames_removed File "migrations/env.py", line 78, in
> <module> run_migrations_online() File "migrations/env.py", line 73,
> in run_migrations_online context.run_migrations() File "<string>",
> line 8, in run_migrations File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/runtime/environment.py",
> line 797, in run_migrations
> self.get_context().run_migrations(**kw) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/runtime/migration.py",
> line 312, in run_migrations step.migration_fn(**kw) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/migrations/versions
/eaab6fc3ef59_.py",
> line 25, in upgrade batch_op.alter_column('c1', type_=sa.Integer(),
> existing_type=sa.Boolean()) File
> "/usr/lib64/python3.4/contextlib.py", line 66, in __exit__
> next(self.gen) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/base.py",
> line 299, in batch_alter_table impl.flush() File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/batch.py",
> line 76, in flush fn(*arg, **kw) File
> "/home/koniiiik/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/batch.py",
> line 316, in drop_constraint raise ValueError("No such constraint:
> '%s'" % const.name) ValueError: No such constraint: '_unnamed_'
>
> We're using a naming convention, and it does apply correctly when
> the table is created. I also tried passing the naming convention as
> an argument to batch_alter_table, but it doesn't seem to make any
> difference.
>
> '_unnamed_' appears to be a sentinel value that the Boolean type
> sets if you don't pass it an explicit name for the CHECK
> constraint, and it seems that for some reason, it doesn't get
> properly replaced with the name provided by the naming convention
> before batch_op starts processing queued operations.
that should not be the case, but typically a naming convention for a
CHECK constraint includes the "name" given to the constraint itself,
which is the "constraint_name" token in the string.
I see in
batch_op.alter_column(name, type_=sa.Integer(),
existing_type=sa.Boolean())
you aren't giving it the constraint name, that's actually where the
"_unnamed_" is coming from.
I just added a test for this and it seems that this existing_type is
where the problem comes from, not the batch migration. So setting
create_constraint=False resolves the issue:
def _boolean_fixture(self):
t = Table(
'hasbool', self.metadata,
Column('x', Boolean(create_constraint=True, name='ck1'))
)
t.create(self.conn)
def test_bool_change(self):
self._boolean_fixture()
with self.op.batch_alter_table("hasbool") as batch_op:
batch_op.alter_column(
'x', type_=Integer, existing_type=Boolean(
create_constraint=False, name='ck1'))
however, it's still copying the constraint, and adding a DROP is not
working so I have to just cerate an issue for this, sorry.
https://bitbucket.org/zzzeek/alembic/issues/354/cant-change-type-of-bool
ean-w-batch
>
> I put up a sample project reproducing the issue here:
> https://github.com/koniiiik/alembic-sqlite-boolint I would have
> created a test for the alembic test suite, but it's not obvious to
> me how to properly do that.
>
> Am I doing something wrong here, or is this a bug?
>
>
> In the interest of avoiding the XY problem, here's how I found
> this issue.
>
> The actual problem I'm trying to solve is renaming a Boolean column
> in a way that works both on SQLite and MS SQL. The regular
> alter_column changing just the name works on SQLite, but errors out
> on MS SQL, because on MS SQL, the Boolean type is represented by a
> column of type BIT with an additional CHECK constraint. (This seems
> kind of redundant to me, but it's the way things are.)
>
> When running the column rename operation against MS SQL, it errors
> out, because MS SQL does not cascande column renames to
> constraints. That's why as a workaround I tried to do the
> following:
>
> batch_op.alter_column(old_name, type_=sa.Integer(),
> existing_type=sa.Boolean()) batch_op.alter_column(old_name,
> new_column_name=new_name) batch_op.alter_column(new_name,
> type_=sa.Boolean(), existing_type=sa.Integer())
>
> This works OK on MS SQL, but not on SQLite as described above.
>
> Can anyone help me come up with the best way of renaming a Boolean
> column on both backends? I'm about to try creating a new column
> with the new name, copying the data from the old column, and
> dropping the old one, but that's a really convoluted approach.
>
> Thanks in advance for your help.
>
> Michal
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
