sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table

Question

I've defined a table named users_table and ran db.create_all() to create the table, but get the error no such table user_table on commit for updating user info.

How I test :

(under /project) python3 manage.py shell
>>> u = User(email='foo@bar.com', 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

/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 1

( I came across this question ("(Background on this error at: http://sqlalche.me/e/e3q8)"), but since I can't comment there, I'll provide my comment here since this question is related to the previous one. )

I encountered the same error (Background on this error at: http://sqlalche.me/e/e3q8) in my Flask app. The issue in my case was from the column name in the Postgres database.

In the Postgres database, I have a table named "Client" with three columns (for simplicity): clientId, name, age.

In short, the problem was with "clientId", it should have been "clientid" with lowercase 'i'.

Here's an example of the Python code I used:

#The model:
class Client(Base):
    __tablename__ = 'client'

    # clientId = Column(String, primary_key=True) # old
    clientid = Column(String, primary_key=True) # new
    name = Column(String)
    age = Column(String)

# A function using the "clientid" column:
def get_clients_from_db():
    try:
        Session = sessionmaker(bind=engine)
        session = Session()

        # Retrieve all rows from the "Client" table in Postgres
        client_rows = session.query(Client).all()
        
        # clients_list = [f"{row.clientId}:{row.name}" for row in client_rows] # old
        clients_list = [f"{row.clientid}:{row.name}" for row in client_rows] # new
        
        return clients_list
    except Exception as e:
        print(f"ERROR : {e}\nget_clients_from_db() ===> !!! ERROR !!!")

So, I replaced "clientId" with "clientid" in the model (and in the rest of the code where it's used).

Answer 2

This error may also show up if you create a database and add data into a table in it for the first time. In that case, creating all tables in the driver code before running the app like the following solves the issue.

if __name__ == "__main__":
    with app.app_context():
        db.create_all()
        app.run(debug=True)

---

A minimal example that reproduces the issue (along with its solution). In the example below, a database named mydatabase is created in the "instance" folder in the project directory. This database contains the my_table table which has two 3 columns: "id", "name" and "location".

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydatabase.db'
app.config['SECRET_KEY'] = 'my+secret+key'
db = SQLAlchemy(app)

class MyTable(db.Model):
    id = db.Column('id', db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    location = db.Column(db.Integer)
    def __init__(self, name, location):
        self.name = name
        self.location = location

@app.route('/')
def add_data():
    data = MyTable('cottontail', 'Stack Overflow')
    db.session.add(data)
    db.session.commit()
    return f"My name is {data.name} and I am on {data.location}."  # show data

if __name__ == "__main__":
    with app.app_context():         # <--- without these two lines, 
        db.create_all()             # <--- we get the OperationalError in the title
        app.run(debug=True)

Answer 3

Create a folder named "instance" in the root directory and move your database file to that folder.

Answer 4

I run into the same problem, after doing a YT tutorial. I solved it by adding this code at the end of my __init__.py

from .models import User, Post

with app.app_context():
    db.create_all()

Sidenote: Most tutorials don't use the with app.app_context(): . I think there was a update in flask, which is why this is needed. This caused errors in my code and maybe it helps someone who reads this.

I would like to mention that it was the flask tutorial from "corey schafer" after "part 6 - user authentication", and the error appeared when i ran some tests. just in case anyone else is doing the exact same tutorial and therfore finds it easier to identify my answer as helpful. I am not mentioning the creater for advertisement. I hope this is ok.

Answer 5

Very simple solution: in the app.py or main.py you can just add these lines of code for fixing this issue:

@app.before_first_request
def create_tables():
    db.create_all()

Answer 6

In your case, require to add following code into __init__.py:

from models import User, Role

@app.shell_context_processor
def make_shell_context():
    return dict(db=db, User=User, Role=Role)

then you do your previous works, it's all work.

Answer 7

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('a@a.com', '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()