db.create_all() is not adding specific column into the database

Question

I am trying to add the Product table into the database using flask sqlalchemy. It adds all the columns except for the userBudget Does anyone know why? On the cmd, I do

from app import db
db.create_all()

Then I go into sqlite3 and check the .schema but only the userBudget column is missing

sqlite> .schema
CREATE TABLE user (
        id INTEGER NOT NULL,
        username VARCHAR(15),
        email VARCHAR(50),
        password VARCHAR(80),
        PRIMARY KEY (id),
        UNIQUE (username),
        UNIQUE (email)
);
CREATE TABLE product (
        "userId" INTEGER NOT NULL,
        "productURL" VARCHAR NOT NULL,
        "currentPrice" INTEGER,
        PRIMARY KEY ("userId"),
        FOREIGN KEY("userId") REFERENCES user (id)
);

import requests
from flask import Flask, render_template, redirect, url_for, request
from flask_bootstrap import Bootstrap
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user

# Instantiation
app = Flask(__name__) 

app.config['SECRET_KEY'] = 'secret'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db'  # location of database
SQLALCHEMY_TRACK_MODIFICATIONS = False # no sqlalchemy warnings in console
bootstrap = Bootstrap(app) # allows use of flask-bootstrap
db = SQLAlchemy(app) # database

# Initialization
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'

# Database Tables
class User(UserMixin, db.Model): # UserMixin provides default implementations for the methods flask-login expects users to have
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(15), unique=True)
    email = db.Column(db.String(50), unique=True)
    password = db.Column(db.String(80))

class Product(db.Model):
    userId = db.Column(db.Integer, db.ForeignKey(User.id), primary_key=True)
    productURL = db.Column(db.String(), nullable=False)
    currentPrice = db.Column(db.Integer)
    userBudget = db.Column(db.Integer, nullable=False)

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))

if __name__ == '__main__':
    app.run(debug=True)

Answer 1

If you are still in development, do a db.drop_all() followed by a db.create_all().

You should track your db changes with e.g. https://github.com/miguelgrinberg/Flask-Migrate

Answer 2

Calling drop_all above worked for this issue. I'm just going to add an additional comment about my situation, here, just because of how unusual this was.

This was the weirdest phenomenon. We call "create_all" at the top of every test (via the setup function), and, until last night, we never had an issue (this integration has been running for about a year). Suddenly, it was correctly creating all tables (with all columns) at docker-compose bootup but, at the top of this test, we suddenly had a certain table missing a certain column. The docker-compose bootup calls a script that calls the same function as the unit-test setup to create the tables. Once the unit-tests run, all of the structure and data is flushed (we drop the database; if it can't then it fails), a fresh start-up of docker-compose will leave you with a complete and valid schema (so, it's an operational environment for development). When docker-compose started, we had all of the columns as expected.

I could add columns or remove columns from the entity, but, whenever the unit-test ran, we always ended-up with the exact same columns (exactly the one missing column with no, new columns present).

I added the drop_all() call before the create_all() call in the unit-test setup, and, magically, all of the correct columns are now there. SQLAlchemy is version-locked, so it definitely wasn't a [new] SA thing.

At least it works, now. Seems like a metadata issue, but I'm not sure where it'd live.

Answer 3

as someone suggested db.drop_all(), run then db.create_all(), restart worked for me