Flask-migrate: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL

[scheung38]

After successful
$python application.py db init
$python application.py db migrate

But upon
$python application.py db upgrade

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL [SQL: u'ALTER TABLE user ADD COLUMN title VARCHAR(10) NOT NULL']

In my database the new column is defined a 'title' as in:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(10), unique=False, nullable=False, info={'validators': InputRequired()})
    firstname = db.Column(db.String(30), unique=False, nullable=False, info={'validators': InputRequired()})
    middlename = db.Column(db.String(30), unique=False, nullable=True, info={'validators': Optional()})
    lastname = db.Column(db.String(30), unique=False, nullable=False, info={'validators': InputRequired()})
    email = db.Column(db.String(120), unique=True, nullable=False, info={'validators': Email()})
    password = db.Column(db.String(80), nullable=False)
    posts = db.relationship('Post', backref='user', lazy='dynamic')

    def __init__(self, title, firstname, middlename, lastname, email, password):
        self.title = title
        self.firstname = firstname
        self.middlename = middlename
        self.lastname = lastname
        self.email = email
        self.password = flask_bcrypt.generate_password_hash(password)

Or do I need to add attribute server_default or default to 'title' attribute? as previously defined data didn't have it

I do see some solutions but these are all in SQL syntax, staying within Python-Migrate constraint would be preferable.

[miguelgrinberg]

Right, the problem is that when you add a column that is not nullable the db does not know what to put as value for all the existing rows, you have to pass a default.

[scheung38]

Thanks Miguel could you let us know what a default value would be? Default for the existing Column or new attribute or both needed? But

title = db.Column(db.String(10), nullable=False, info={'validators': InputRequired()})

is what I want, but it seems like I need to apply nullable=True?

[miguelgrinberg]

If you set nullable=True then things should work, your existing rows will have NULL as value for the new column.

Alternatively, you can set a default to an empty string, that should work too.

[scheung38]

I did and it is still returning the same

Sent from my iPhone

On 11 Sep 2015, at 19:38, Miguel Grinberg [email protected] wrote:

If you set nullable=True then things should work, your existing rows will have NULL as value for the new column.

Alternatively, you can set a default to an empty string, that should work too.

—
Reply to this email directly or view it on GitHub.

[miguelgrinberg]

Did you check the migration code to see if it was updated to nullable=True?

On Fri, Sep 11, 2015, 11:48 AM Sebastian M Cheung [email protected]
wrote:

I did and it is still returning the same

Sent from my iPhone

On 11 Sep 2015, at 19:38, Miguel Grinberg [email protected]
wrote:

If you set nullable=True then things should work, your existing rows
will have NULL as value for the new column.

Alternatively, you can set a default to an empty string, that should
work too.

—
Reply to this email directly or view it on GitHub.

—
Reply to this email directly or view it on GitHub
https://github.com/miguelgrinberg/Flask-Migrate/issues/81#issuecomment-139625486
.

[smalekzadeh]

I have the same problem. Did you find a solution? In my case the field must be nullable=False, but even with adding default="" in both the model and migration script I still get the same error.
Can you help please

[miguelgrinberg]

@smalekzadeh your field cannot be nullable=False if you have existing columns that are NULL. Fix those columns first, then the migration will work.

[graydenshand]

Try setting the server_default parameter in your migration script/model.
default is handled by SQLAlchemy.
server_default actually sets this in your DB

https://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column

[graydenshand]

@mzord kind of defeats the purpose of using alembic/flask-migrate eh?

I`ve just deleted my migrations folder and run the flask db init, flask db migrate, flask db upgrade again and everything went fine!

[aramhamidi]

I had the same problem, but as Miguel suggested, I checked the migration code and I noticed although I have changed the nullable to True, but in migration script it was still False. I changed it to False manually in migration script, and it worked with the following warning:

UserWarning: Skipping unsupported ALTER for creation of implicit constraint
  "Skipping unsupported ALTER for "

And everything seems to be fine now.

[prashantjeena]

I had the same problem, but as Miguel suggested, I checked the migration code and I noticed although I have changed the nullable to True, but in migration script it was still False. I changed it to False manually in migration script, and it worked with the following warning:

UserWarning: Skipping unsupported ALTER for creation of implicit constraint
  "Skipping unsupported ALTER for "

And everything seems to be fine now.

how to change migration script manually?

[miguelgrinberg]

@prashantjeena migration scripts are auto-generated Python files. Once the migration is generated, you can open it in a text editor and make changes to it as you see fit.

[anakorellana]

I had the same problem, but as Miguel suggested, I checked the migration code and I noticed although I have changed the nullable to True, but in migration script it was still False. I changed it to False manually in migration script, and it worked with the following warning:

UserWarning: Skipping unsupported ALTER for creation of implicit constraint
  "Skipping unsupported ALTER for "

And everything seems to be fine now.

what do you mean by doing it manually in migration script?

[aramhamidi]

Please read one post above yours:

@prashantjeena migration scripts are auto-generated Python files. Once the migration is generated, you can open it in a text editor and make changes to it as you see fit.

[anakorellana]

@aramhamidi so open the models document in a text editor?

[jativar]

Did you check the migration code to see if it was updated to nullable=True?

On Fri, Sep 11, 2015, 11:48 AM Sebastian M Cheung [email protected]
wrote:

I did and it is still returning the same
Sent from my iPhone

On 11 Sep 2015, at 19:38, Miguel Grinberg [email protected]
wrote:
If you set nullable=True then things should work, your existing rows
will have NULL as value for the new column.
Alternatively, you can set a default to an empty string, that should
work too.
—
Reply to this email directly or view it on GitHub.

—
Reply to this email directly or view it on GitHub
#81 (comment)
.

after editing the migration code generated by Alembic:

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('nadadores', sa.Column('credencial', sa.String(length=2), nullable=False))
# ### end Alembic commands ###

nullable=False is replaced by nullable=True

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('nadadores', sa.Column('credencial', sa.String(length=2), nullable=True))
# ### end Alembic commands ###

and when executing the command "python3 main.py db upgrade" again:

$ python3 main.py db upgrade
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 285c1125963c, empty message
(allaboutpython3.8) [tecna@localhost recordsv1.0.33]$

the upgrade is successful.

Thanks!!!
-Ramiro Jativa [email protected]