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:
- the table user is (and has been) there
- I have been able to add columns to the User model before
- 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.
You are issuing a query in the global scope of the application, in class
NewActionForm
. The query isUser.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.