Sqlalchemy error when trying to alter a column to non-null

[John Wood]

Hello folks,

I am seeing the error (copied below) when trying to apply a column change (not null -> nullable) after a new table create, via this command:

alembic upgrade head

I am using SQLAlchemy version 0.8.1, Alembic version 0.5.0 against a SQLite database (via pysqlite at 2.6.3).

Any insights would be helpful.

The create table alembic version (snippet) looks like this:

def upgrade():

    op.create_table(

        'test',

        sa.Column('id', sa.Integer, primary_key=True),

        sa.Column('name', sa.String(50), nullable=False),

        sa.Column('description', sa.Unicode(200)),

    )

I applied this first change via the upgrade command.

The modify table alembic version looks like this:

def upgrade():

    op.alter_column('test', u'name',

               existing_type=sa.String(50),

               nullable=True)

Trying to upgrade to this version produces the error below.  

Thanks,

John

----

Error from the second 'alembic upgrade head' command:

(.venv)M945D957XJ:barbican john.wood$ alembic upgrade head

INFO  [alembic.migration] Context impl SQLiteImpl.

INFO  [alembic.migration] Will assume non-transactional DDL.

INFO  [sqlalchemy.engine.base.Engine] PRAGMA table_info("alembic_version")

INFO  [sqlalchemy.engine.base.Engine] ()

DEBUG [sqlalchemy.engine.base.Engine] Col ('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk')

DEBUG [sqlalchemy.engine.base.Engine] Row (0, u'version_num', u'VARCHAR(32)', 1, None, 0)

INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num 

FROM alembic_version

INFO  [sqlalchemy.engine.base.Engine] ()

DEBUG [sqlalchemy.engine.base.Engine] Col ('version_num',)

DEBUG [sqlalchemy.engine.base.Engine] Row (u'1a0c2cdafb38',)

INFO  [alembic.migration] Running upgrade 1a0c2cdafb38 -> 40a9c7408b51, Test auto migration

INFO  [sqlalchemy.engine.base.Engine] ALTER TABLE test ALTER COLUMN name DROP NOT NULL

INFO  [sqlalchemy.engine.base.Engine] ()

INFO  [sqlalchemy.engine.base.Engine] ROLLBACK

Traceback (most recent call last):

  File "/Users/john.wood/projects/security/barbican/.venv/bin/alembic", line 9, in <module>

    load_entry_point('alembic==0.5.0', 'console_scripts', 'alembic')()

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/config.py", line 265, in main

    CommandLine(prog=prog).main(argv=argv)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/config.py", line 260, in main

    self.run_cmd(cfg, options)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/config.py", line 247, in run_cmd

    **dict((k, getattr(options, k)) for k in kwarg)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/command.py", line 123, in upgrade

    script.run_env()

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/script.py", line 193, in run_env

    util.load_python_file(self.dir, 'env.py')

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/util.py", line 177, in load_python_file

    module = imp.load_source(module_id, path, open(path, 'rb'))

  File "migration/env.py", line 71, in <module>

    run_migrations_online()

  File "migration/env.py", line 64, in run_migrations_online

    context.run_migrations()

  File "<string>", line 7, in run_migrations

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/environment.py", line 536, in run_migrations

    self.get_context().run_migrations(**kw)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/migration.py", line 220, in run_migrations

    change(**kw)

  File "migration/versions/40a9c7408b51_test_auto_migration.py", line 20, in upgrade

    nullable=True)

  File "<string>", line 7, in alter_column

  File "<string>", line 1, in <lambda>

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/util.py", line 275, in go

    return fn(*arg, **kw)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/operations.py", line 295, in alter_column

    existing_autoincrement=existing_autoincrement

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 99, in alter_column

    existing_nullable=existing_nullable,

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/alembic/ddl/impl.py", line 75, in _exec

    conn.execute(construct, *multiparams, **params)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute

    params)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl

    compiled

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context

    context)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception

    exc_info

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 163, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context

    context)

  File "/Users/john.wood/projects/security/barbican/.venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.OperationalError: (OperationalError) near "ALTER": syntax error u'ALTER TABLE test ALTER COLUMN name DROP NOT NULL' ()

[John Wood]

Hmmm...per the 'Altering constraints (SQLite)' email thread elsewhere in this group, I see this error: 'the altering constraints for sqlite dbs is not (yet ?) supported'.  

I would presume that this is my issue as well, though the error message is not as clear cut as posted in that other thread. Please let me know if I am mistaken about this however.

Thanks,

John

[Pierre Roth]

Hi,

You should try to pass default values with 'default' and 'server_default' args in your sa.Column(...) calls.

HTH

--
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-alem...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Michael Bayer]

On Jun 17, 2013, at 11:31 PM, John Wood <woo...@gmail.com> wrote:

> sqlalchemy.exc.OperationalError: (OperationalError) near "ALTER": syntax error u'ALTER TABLE test ALTER COLUMN name DROP NOT NULL' ()
>

SQLite does not support this (or most) ALTER syntaxes. They only support addition of columns:

http://www.sqlite.org/lang_altertable.html

See this thread for recent discussion: https://news.ycombinator.com/item?id=5886898

[John Wood]

Thanks for the response folks.  I ended up using Postgres with no problems. 

Thanks,

John