sqlalchemy.exc.OperationalError：（sqlite3.OperationalError）没有这样的表：mytable

Question

抱歉，代码太乱了，我在试错阶段有点迷路了（我的咖啡用完了）

我尝试构建一个仪表板。为了跟踪我的订单、收入等。为此，我创建了一个包含所有相关数据的数据库。 之后，我下载了一个“flask dashboard模板”，并开始编辑它。 我可以收集我所有的订单数据，并能够通过卷发手镯在index.html中使用它

当我尝试将空闲中的所有收入行相加时

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)

它可以正常工作，但是当我尝试在我的路由文件中实现它时，就像这样

# -*- 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

我犯了一个错误

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)

Answer 1

我不使用flask，但使用sqlalchemy（ORM模式）、Python3.7.3和sqlite3时出现了相同的操作错误

也许考虑一下：https://docs.sqlalchemy.org/en/14/tutorial/metadata.html，“向数据库发出DDL”部分。

就我而言，我写道：

from sqlalchemy import MetaData
metadata = MetaData()
from sqlalchemy.orm import declarative_base
Base = declarative_base()
engine = create_engine("sqlite:///test.db")

(classes declarations)

metadata.create_all(engine)

然后我得到一个错误

我刚刚添加了“Base”。为了解决我的问题：

Base.metadata.create_all(engine)

Answer 2

我想你得把

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

以前

db1 = SQLAlchemy(app)

解释

当您的代码想要执行这一行时：

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

SQLAlchemy需要知道包含mytable的数据库。因此，在执行这一行之前，必须为数据库指定URI