[SOLVED] Flask-SQLAlchemy: How to change table structure?

Flask-SQLAlchemy: How to change table structure?

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?

Solution

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