Flask-SQLAlchemy: How to change table structure?

Question

I have a table model in a flask application:

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(80), nullable=False)
    body = db.Column(db.Text, nullable=False)
    pubDate = db.Column(db.DateTime, nullable=False, default=datetime.datetime.now())

And I use db.crate_all() and db.add()/db.session to add some data to the above table, work well!

Then I want to update and add some properties of class Article:

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(80), nullable=False)
    body = db.Column(db.Text, nullable=False)
    createDate = db.Column(db.DateTime, nullable=False, default=datetime.datetime.now())
    touchDate = db.Column(db.DateTime, nullable=False, default=datetime.datetime.now())
    publishDate = db.Column(db.DateTime, nullable=False, default=datetime.datetime.now())
    isVisible = db.Column(db.Boolean, nullable=False, default=True)
    isDraft = db.Column(db.Boolean, nullable=False, default=True)

After I update the class Article, I use db.create_all() again. When I run my flask application, I got following error message:

cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: article.createDate
[SQL: SELECT article.id AS article_id, article.title AS article_title, article.body AS article_body, article."createDate" AS "article_createDate", article."touchDate" AS "article_touchDate", article."publishDate" AS "article_publishDate", article."isVisible" AS "article_isVisible", article."isDraft" AS "article_isDraft" 
FROM article 
WHERE article."isVisible" = 1]

Whenever I change the db.Model subclass, does the table in database sync automatically? What operation is needed after the properties of db.Model subclass change?

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

For an industrial-strength solution, Flask-Migrate is an extension that handles SQLAlchemy database migrations for Flask applications using Alembic.

Alembic is a database migration tool written by the author of SQLAlchemy. A migrations tool offers the following functionality:

Can emit ALTER statements to a database in order to change the structure of tables and other constructs

Provides a system whereby “migration scripts” may be constructed; each script indicates a particular series of steps that can “upgrade” a target database to a new version, and optionally a series of steps that can “downgrade” similarly, doing the same steps in reverse.

Allows the scripts to execute in some sequential manner.

It is also possible to execute raw SQL ALTER TABLE statements.

See How to execute raw SQL in Flask-SQLAlchemy app

score 2 · Answer 2 · answered Aug 27 '19 at 17:00

Using Flask Migrate :

1- you need to add manage.py that will handle the migration.

from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from app import app, db


migrate = Migrate(app, db)
manager = Manager(app)

manager.add_command('db', MigrateCommand)


if __name__ == '__main__':
    manager.run()

2 - Run the following after you make a change in models.py

- python manage.py db init #creates the migration folder (one time only)
- python manage.py db migrate

#Choose one of both options : 
- python manage.py db upgrade # update the db automatically
- python manage.py db upgrade --sql > migration.sql # update the "migration.sql" file with the updated sql script.

In the development mode, usually you need to run these two commands one by one after you make a new change on the model :

python manage.py db migrate
python manage.py db upgrade

In case of this error when migrating: " Target database is not up to date."

python manage.py db stamp head
python manage.py db migrate
python manage.py db upgrade

STEPS TO FIX ERRORS WITH MIGRATIONS FOLDER: this could happen if you updated the db manually for some reason or some other kind of problems.

drop table alembic_version  #sql command to run in front of the db ) 
delete migrations folder    #manually from the project tree
python manage.py db init
python manage.py db migrate
python manage.py db upgrade

Flask-SQLAlchemy: How to change table structure?

2 Answers2