sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table
I am fresh to flask and was trying to build a blog on my own, and I ran into an issue with SQLite operation error. I have researched similar problem on Github and Stackoverflow but none of the typical typo or error in old questions happens to me. It would be appreciated and really great if anyone can help me because this problem is like killing me and already cost me two days, I feel really bad.
In the code I have defined the table name which is "users_table" and run "db.create_all()" at the beginning to create the table, but the error keeps occurring with "no such table user_table" each time when a commit happens for updating user info.
This is how I test the SQLite operation:
(under /project) python3 manage.py shell
>>> u = User(email='[email protected]', username='foobar', password='player')
>>> db.create_all()
>>> db.session.add(u)
>>> db.session.commit() # with following error message
Traceback (most recent call last):
File "C:\...\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context
context)
File "C:\...\Python\Python36-32\lib\site-packages\sqlalchemy\engine\default.py", line 470, in do_execute
cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: users_table
...
...
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: users_table
I have minimized the code into following four sections, which can reoccur the error message:
/project/app/__init__.py:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from config import config
db = SQLAlchemy()
def create_app(config_name):
app = Flask(__name__)
app.config.from_object(config[config_name])
config[config_name].init_app(app)
db.init_app(app)
return app
/project/app/models.py:
import os
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash
from flask import Flask
basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
db = SQLAlchemy(app)
class User(db.Model):
__tablename__ = 'users_table'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(64), unique=True, index=True)
username = db.Column(db.String(64), unique=True, index=True)
password_hash = db.Column(db.String(128))
def __repr__(self):
return '<User %r>' % self.username
@property
def password(self):
raise AttributeError('Password is not a readable attribute')
@password.setter
def password(self, password):
self.password_hash = generate_password_hash(password)
project/config.py:
import os
basedir = os.path.abspath(os.path.dirname(\__file__))
class Config:
SECRET_KEY = os.environ.get('SECRET_KEY') or 'fhuaioe7832of67^&*T#oy93'
SQLALCHEMY_COMMIT_ON_TEARDOWN = True
@staticmethod
def init_app(app):
pass
class DevelopmentConfig(Config):
DEBUG = True
SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'data.sqlite')
config = {
'development': DevelopmentConfig,
'default': DevelopmentConfig,
}
project/manage.py:
import os
from app import create_app, db
from app.models import User
from flask_script import Manager, Shell
app = create_app(os.getenv('FLASK_CONFIG') or 'default')
manager = Manager(app)
def make_shell_context():
return dict(app=app, db=db, User=User)
manager.add_command("shell", Shell(make_context=make_shell_context))
if __name__ == '__main__':
manager.run()
Answer
I just got done setting up a Flask app and I dealt with this kind of problem.
I strongly suspect the problem here is that the instance of db that you are creating in __init__.py is unaware of the contents of models.py, including the User class. The db object in __init__.py is a totally separate object from the db you are creating in models.py. So when you run db.create_all() in __init__.py, it is checking the list of tables that it knows about and isn't finding any. I ran into this exact issue.
What I discovered is that the models (like User) are registered with the particular db object that is listed in the model's class definition (e.g. class User(db.Model):).
So basically my understanding is that the way to fix this is to run db.create_all() using the same instance of db that is being used to define the models. In other words, run db.create_all() from within models.py.
Here's my code so you can see how I have it set up:
app.py:
#!flask/bin/python
import os
from flask import Flask
class CustomFlask(Flask):
jinja_options = Flask.jinja_options.copy()
jinja_options.update(dict(
variable_start_string='%%', # Default is '{{', I'm changing this because Vue.js uses '{{' / '}}'
variable_end_string='%%',
))
app = CustomFlask(__name__)
app.config['SECRET_KEY'] = 'hard to guess string'
import yaml
if os.environ['SERVER_ENVIRONMENT'] == 'PRODUCTION':
config_filename = "production.yaml"
elif os.environ['SERVER_ENVIRONMENT'] == 'LOCAL':
config_filename = "local.yaml"
else:
config_filename = "local.yaml"
base_directory = path = os.path.dirname(os.path.realpath(__file__))
with open(base_directory + "/config/" + config_filename) as config_file:
config = yaml.load(config_file)
db_config = config['database']
SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
username=db_config['username'],
password=db_config['password'],
hostname=db_config['hostname'],
databasename=db_config['databasename'],
)
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
db.app = app
def clear_the_template_cache():
app.jinja_env.cache = {}
app.before_request(clear_the_template_cache)
from flask_login import LoginManager
login_manager = LoginManager()
login_manager.init_app(app)
@login_manager.user_loader
def load_user(email):
from models import User
return User.query.filter_by(email=email).first()
if __name__ == '__main__':
from routes import web_routes
app.register_blueprint(web_routes)
from api import api
app.register_blueprint(api)
# To get PyCharm's debugger to work, you need to have "debug=False, threaded=True"
#app.run(debug=False, threaded=True)
app.run(debug=True)
models.py:
from app import db
import datetime
from werkzeug.security import generate_password_hash, \
check_password_hash
class Song(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(80))
datetime_created = db.Column(db.DateTime, default=datetime.datetime.utcnow())
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
lines = db.relationship('Line', cascade="all,delete", backref=db.backref('song', lazy='joined'), lazy='dynamic')
is_deleted = db.Column(db.Boolean, default=False)
class Line(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
song_id = db.Column(db.Integer, db.ForeignKey('song.id'))
spans_of_time = db.relationship('SpanOfTime', cascade="all,delete", backref=db.backref('line', lazy='joined'), lazy='dynamic')
class SpanOfTime(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
line_id = db.Column(db.Integer, db.ForeignKey('line.id'))
starting_64th = db.Column(db.Integer) # I'm assuming the highest-granularity desired will be a 1/64th note-length.
length = db.Column(db.Integer) # I guess this'll be in 1/64th notes, so a 1/16th note will be '4'.
content = db.Column(db.String(80))
class User(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
email = db.Column(db.String(80), primary_key=True, unique=True)
display_name = db.Column(db.String(80), default="A Rhymecraft User")
password_hash = db.Column(db.String(200))
datetime_subscription_valid_until = db.Column(db.DateTime, default=datetime.datetime.utcnow() - datetime.timedelta(days=1))
datetime_joined = db.Column(db.DateTime, default=datetime.datetime.utcnow())
songs = db.relationship('Song', cascade="all,delete", backref=db.backref('user', lazy='joined'), lazy='dynamic')
def __init__(self, email, password):
self.email = email
self.set_password(password)
def __repr__(self):
return '<User %r>' % self.email
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 is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return str(self.email)
def init_db():
db.create_all()
# Create a test user
new_user = User('[email protected]', 'aaaaaaaa')
new_user.display_name = 'Nathan'
db.session.add(new_user)
db.session.commit()
new_user.datetime_subscription_valid_until = datetime.datetime(2019, 1, 1)
db.session.commit()
if __name__ == '__main__':
init_db()