Default value ignored in non-nullable column

Question

I'm trying to create a new boolean, non-nullable column (with default=True) in a table with the following SQL alchemy script:

from sqlalchemy import MetaData, Table, Boolean, Column

def upgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    message_table = Table('message', meta, autoload=True)
    col = Column('include_signature', Boolean(), default=True, nullable=False)
    col.create(message_table)

This results in a rather puzzling error:

  File "src/adhocracy/migration/versions/061_optional_massmessage_signature.py", line 7, in upgrade
    col.create(message_table)
  File "eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/schema.py", line 528, in create
    engine._run_visitor(visitorcallable, self, connection, **kwargs)
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2302, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1972, in _run_visitor
    **kwargs).traverse_single(element)
  File "eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py", line 53, in traverse_single
    ret = super(AlterTableVisitor, self).traverse_single(elem)
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/sql/visitors.py", line 106, in traverse_single
    return meth(obj, **kw)
  File "eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/databases/sqlite.py", line 75, in visit_column
    super(SQLiteColumnGenerator,self).visit_column(column)
  File "eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py", line 101, in visit_column
    self.execute()
  File "eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py", line 42, in execute
    return self.connection.execute(self.buffer.getvalue())
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1628, in _execute_text
    statement, parameters
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "eggs/SQLAlchemy-0.7.10-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) Cannot add a NOT NULL column
with default value NULL '\nALTER TABLE message ADD include_signature BOOLEAN NOT NULL' ()

As far as I understand, sqlalchemy is missing a default value, but I did include default=True! What is wrong here?

score 34 · Accepted Answer · edited Apr 19 '13 at 08:43

34

Use server_default="true", like that:

col = Column('include_signature', Boolean(), default=True,
             server_default="true", nullable=False)

Where default is used for the class instance and server_default is used for the database column definition.

edited Apr 19 '13 at 08:43

phihag

278,196
72
453
469

answered Apr 19 '13 at 08:28

estin

3,051
1
24
31

Thanks! I didn't realize `default` is just a Python-only creation of sqlalchemy (it's what sqlalchemy *inserts* if the value is not given), and has nothing to do with the underlying database. – phihag Apr 19 '13 at 08:42
So weird, isn't it supposed to be `False` (capital F) to match the python syntax? – benjaminz Mar 30 '16 at 02:20
`server_default` like sql expression and not a python expression http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#sqlalchemy.schema.Column.params.server_default – estin Mar 30 '16 at 08:38
17

Careful with this as it will break when using an SQL dialect that does not support `true` as a literal, such as SQLite. A better approach is to use `sqlalchemy.sql.expression.literal(True)`, as with that SQLAlchemy will perform the necessary conversion when generating DDL, e.g. mapping `True` to `1` when using SQLite. – ThePhysicist Jan 16 '17 at 11:05

Default value ignored in non-nullable column

1 Answers1

Linked