Menu
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?
783
Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.
A Nullable type field can't be included in table indexes. NULL is the default value for any Nullable type, unless specified otherwise in the ClickHouse server configuration.
If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.
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.
90
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
