flask db init returns "sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table"

Question

I've been working on a web app in flask using an sqlite database. This has been going fine so far, and I am aware of the fact sqlite is limited in terms of alterations to tables once a database exists.

However, so far i have been able to modify tables (add columns, rename columns, etc.) of several models in the models.py without issue. Noting here I do use

migrate = Migrate(app, db, render_as_batch=True)

in the app initialization, which has been needed to realize some changes to the database.

Now however, I have received the following error when trying to run flask db migrate after adding a new column to the model "User":

    Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: user

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.6/dist-packages/flask/cli.py", line 966, in main
    cli.main(prog_name="python -m flask" if as_module else None)
  File "/usr/local/lib/python3.6/dist-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/click/decorators.py", line 21, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/flask/cli.py", line 425, in decorator
    with __ctx.ensure_object(ScriptInfo).load_app().app_context():
  File "/usr/local/lib/python3.6/dist-packages/flask/cli.py", line 388, in load_app
    app = locate_app(self, import_name, name)
  File "/usr/local/lib/python3.6/dist-packages/flask/cli.py", line 240, in locate_app
    __import__(module_name)
  File "/home/arthur/Development/ISLWeb/islweb.py", line 1, in <module>
    from app import app, db
  File "/home/arthur/Development/ISLWeb/app/__init__.py", line 24, in <module>
    from app import routes, models
  File "/home/arthur/Development/ISLWeb/app/routes.py", line 11, in <module>
    from app.forms import (LoginForm, RegistrationForm, CreateLaunchForm, 
  File "/home/arthur/Development/ISLWeb/app/forms.py", line 93, in <module>
    class NewActionForm(FlaskForm):
  File "/home/arthur/Development/ISLWeb/app/forms.py", line 95, in NewActionForm
    users = User.query.all()
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3186, in all
    return list(self)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3342, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/orm/query.py", line 3367, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
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_hash AS user_password_hash, user.has_admin_rights AS user_has_admin_rights, user.is_customer AS user_is_customer 
FROM user]
(Background on this error at: http://sqlalche.me/e/e3q8)

this is strange to me, because:

1. the table user is (and has been) there
2. I have been able to add columns to the User model before
3. I can still add columns to other models without errors

Now, I have seen this sqlite3.OperationalError coming up in many question here and elsewhere, but none of them seem to relate to the situation at hand here, as the codebase was working fine before, and so did migrations (they still do with all other models as well) Also, I have tried as a manner of test to delete the migrations folder as well as the database file, starting anew with:

flask db init

Which in my understanding starts a whole DB anew (including new migration script). Funnily enough this throws the exact same error as above. Which I also do not understand at all, as there is no db to read from.

My user model is as follows (with the column I'm trying to add commented):

class User(UserMixin, db.Model):
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    actions = db.relationship('Action', backref='owner', lazy='dynamic')
    events = db.relationship('WebLogEvent', backref='user', lazy='dynamic')
    has_admin_rights = db.Column(db.Boolean)
    is_customer = db.Column(db.Boolean)
    #permissions = db.Column(db.String(256), index=True)
    
    def __repr__(self):
        return '<User {}>'.format(self.username)
        
    @staticmethod
    def table_header():
        return ["ID", "Username", "e-mail", "Admin"]
    
    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def as_table_row(self):
        return [self.id, self.username, self.email, self.has_admin_rights]

Any thoughts on what could be the matter here are most welcome.

Answer 1

You are issuing a query in the global scope of the application, in class NewActionForm. The query is User.query.all(), according to your stack trace.

The problem with this is that code that is in the global scope executes at import time. For many things this is okay, but database access is usually problematic, because you have to create and configure your Flask application instance before the database is accessible.

So the solution is to not query the database in the global scope. You can probably move that logic into the form's constructor method.