can not rename column using alter_column

Question

I have an existing project that is based on pylons and sqlalchemy. I did not use alembic or any wrapper in this project, yet.

I migrated the project to flask and sqlalchemy. I took the old database of the old version 1.5 and generated the first migration script with Flast-Migrate. Hm, I realized I will have to do many things manually.

I am already stuck, when renaming columns. I am doing this with an SQLite database.

The old project had a table "Token", which was renamed to "token". Obviously there are some database that do not care about upper or lower case. When I try to rename the first column like this:

op.alter_column('token', 'privacyIDEATokenId', new_column_name='id')

I get this error:

sqlalchemy.exc.OperationalError: (OperationalError) near \
""privacyIDEATokenId"": syntax error u'ALTER TABLE token RENAME \
"privacyIDEATokenId" TO id' ()

The column Token.privacyIDEATokenId is the primary key that should be token.id in the new version. The same happens to columns that are not the primary key.

Full trace:

Traceback (most recent call last):
  File "./manage.py", line 107, in <module>
    manager.run()
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/flask_migrate/__init__.py", line 98, in upgrade
    command.upgrade(config, revision, sql = sql, tag = tag)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/command.py", line 129, in upgrade
    script.run_env()
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/script.py", line 208, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/util.py", line 230, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/compat.py", line 63, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "migrations/env.py", line 72, in <module>
    run_migrations_online()
  File "migrations/env.py", line 65, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/environment.py", line 696, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/migration.py", line 266, in run_migrations
    change(**kw)
  File "migrations/versions/112475c7f45_.py", line 103, in upgrade
    _upgrade_token_table()
  File "migrations/versions/112475c7f45_.py", line 49, in _upgrade_token_table
    op.alter_column('token', 'privacyIDEATokenId', new_column_name='id')
  File "<string>", line 7, in alter_column
  File "<string>", line 1, in <lambda>
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/util.py", line 353, in go
    return fn(*arg, **kw)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/operations.py", line 329, in alter_column
    existing_autoincrement=existing_autoincrement
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 131, in alter_column
    existing_nullable=existing_nullable,
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 81, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "/home/cornelius/src/privacyidea/venv/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/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 783, in _execute_ddl
    compiled
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "/home/cornelius/src/privacyidea/venv/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/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "/home/cornelius/src/privacyidea/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) near ""privacyIDEATokenId"": syntax error u'ALTER TABLE token RENAME "privacyIDEATokenId" TO id' ()

Answer 1

Nowadays you can use "batch migration" to enable ALTER-ing columns under SQLite:

with op.batch_alter_table('table_name') as bop:
    bop.alter_column('old_column_name', new_column_name='new_name')

Answer 2

Obivously sqlite can not rename a column. So finally I ended up with creating new tables with new columns and shoving data from one to the other.

This is ok, since I realized, I also have to mangle the data, adapt and modify it before writing to the new table again.

The script I ended up with, is here: https://github.com/privacyidea/privacyidea/blob/version2/migrations/versions/4f32a4e1bf33_.py#L241