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.
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: Digital signature
