[FIXED] sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: mytable

Issue

Sorry for the messy code, I got a little lost in trial and error stage (I run out of coffee).

I try to build a dashboard. to track my orders, revenue etc. to do this, I created a database that include all the related data. After that, I download a "flask dashboard template" and started editing it. I can collect all my order data and able using it in the index.html via curly bracelets.

When I try to sum all revenue rows in IDLE;

from flask import Flask, render_template, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from sqlalchemy import func

engine = create_engine('sqlite:///transactions.db', echo=True)
conn = engine.connect()

global b
a = text('SELECT * FROM mytable')
b = conn.execute(a).fetchall()


app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + 'transactions.db'


db = SQLAlchemy(app)
revenue = db.engine.execute('select sum(total) from mytable').scalar()
revenue = int(revenue)
print(revenue)

it works without an issue, but when I try to implement it in my route file like this;

# -*- encoding: utf-8 -*-

from app.home import blueprint
from flask import Flask, render_template, redirect, url_for
from flask_login import login_required, current_user
from app import login_manager
from jinja2 import TemplateNotFound
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.sql import text

# custom block
from sqlalchemy import create_engine

engine = create_engine('sqlite:///transactions.db', echo=True)
conn = engine.connect()

global b
global revenue

a = text('SELECT * FROM mytable')
b = conn.execute(a).fetchall()

app = Flask(__name__)
dborder = SQLAlchemy(app)
db = SQLAlchemy(app)

#revenue
engin = create_engine('sqlite:///transactions.db', echo=True)
coni = engine.connect()

db1 = SQLAlchemy(app)
revenue = db1.engine.execute('select sum(total) from mytable').scalar()
revenue = int(revenue)
#revenue


app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + 'transactions.db'

class mytable(dborder.Model):
    name = dborder.Column(dborder.Integer, primary_key=True)
    add = dborder.Column(dborder.String)

# end custom block

@blueprint.route('/index')
@login_required
def index():

    #if not current_user.is_authenticated:
    #    return redirect(url_for('base_blueprint.login'))

    global b
    orderlist = b

    return render_template('index.html', orderlist = orderlist, revenue = revenue)

@blueprint.route('/<template>')
def route_template(template):

    if not current_user.is_authenticated:
        return redirect(url_for('base_blueprint.login'))

    try:

        return render_template(template + '.html')

    except TemplateNotFound:
        return render_template('page-404.html'), 404

    except:
        return render_template('page-500.html'), 500

I'm getting an error;

2020-05-29 00:41:13,977 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-05-29 00:41:13,977 INFO sqlalchemy.engine.base.Engine ()
2020-05-29 00:41:13,978 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-05-29 00:41:13,978 INFO sqlalchemy.engine.base.Engine ()
2020-05-29 00:41:13,979 INFO sqlalchemy.engine.base.Engine SELECT * FROM mytable
2020-05-29 00:41:13,979 INFO sqlalchemy.engine.base.Engine ()
C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\flask_sqlalchemy\__init__.py:808: UserWarning: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:".
  warnings.warn(
C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\flask_sqlalchemy\__init__.py:829: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  warnings.warn(FSADeprecationWarning(
C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\flask_sqlalchemy\__init__.py:829: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  warnings.warn(FSADeprecationWarning(
C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\flask_sqlalchemy\__init__.py:829: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  warnings.warn(FSADeprecationWarning(
Traceback (most recent call last):
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: mytable

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

Traceback (most recent call last):
  File "run.py", line 21, in <module>
    app = create_app(config_mode)
  File "C:\Users\Ozgur\Documents\python-colleect\admin panel\flask-material-dashboard\app\__init__.py", line 81, in create_app
    register_blueprints(app)
  File "C:\Users\Ozgur\Documents\python-colleect\admin panel\flask-material-dashboard\app\__init__.py", line 24, in register_blueprints
    module = import_module('app.{}.routes'.format(module_name))
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\importlib\__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "C:\Users\Ozgur\Documents\python-colleect\admin panel\flask-material-dashboard\app\home\routes.py", line 44, in <module>
    revenue = db1.engine.execute('select sum(total) from mytable').scalar()
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 2244, in execute
    return connection.execute(statement, *multiparams, **params)
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1012, in execute
    return self._execute_text(object_, multiparams, params)
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1181, in _execute_text
    ret = self._execute_context(
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1323, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1517, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\Ozgur\AppData\Local\Programs\Python\Python38-32\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: mytable
[SQL: select sum(total) from mytable]
(Background on this error at: http://sqlalche.me/e/e3q8)

Solution

I think you have to put

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + 'transactions.db' 

before

db1 = SQLAlchemy(app)

Explanation

when your code wants to execute this line:

revenue = db1.engine.execute('select sum(total) from mytable').scalar()

SQLAlchemy needs to know the database that contains mytable. So you have to specify the URI to your database before executing this line.

Answered By - Reza