How to recreate database in SQLAlchemy from Flask?

Question

I'm using SQLAlchemy ORM framework from a Flask project. I wanted to add another column to one of my models. After I did, I used db.session.drop_all() to drop all current entries in the database, and then I tried recreating a new instance with the new field.

Whenever I tried I get this error

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no 
such table: user
[SQL: SELECT user.id AS user_id, user.username AS user_username, user.email AS user_email, user.password AS user_password, user.image_file AS user_image_file 
FROM user]

I think I might need to reconstruct the database in some way, but I'm not sure how. I looked into the documentation, but could not find anything useful.

__init__.py

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

models.py

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True, nullable=False)
    email = db.Column(db.String(40), unique=True, nullable=False) #newly added field
    password = db.Column(db.String(60), nullable=False)
    image_file = db.Column(db.String(20), nullable=False, default='default.jpg')
    messages = db.relationship('Message', backref='user', lazy=True)

Answer 1

When you used db.drop_all() you dropped all tables so now you can't insert data because there is no table. You need create the table again with db.create_all() as @SuperShoot mentioned.

You should use something for migrations like alembic or flask-sqlalchemy. This way you could add new column to your ORM. Run flask db migrate -m 'new column', this will detect changes. Then run flask db upgrade head to apply those changes to your database.