无法从 heroku 访问 sqlalchemy 数据库。关于一些我不存在的“用户”模型的奇怪错误

debugcn 发表于 Dev

下雨的时候

我有一个在 Flask 应用程序上运行的 SQLAlchemy 数据库，它在本地运行良好，但是一旦我在 Heroku 上运行它，我就会遇到一个奇怪的问题（请参阅下面的堆栈跟踪）。

我没有名为“用户”的模型，所以我不知道发生了什么。我已经确定我已经跑了db.create_all()。

在我的表中创建一行工作正常，但只有在我尝试选择一些行时才会出现问题 ( __init__.py)：

@app.route('/blog')
def blog():
    title = 'Blog'
    name = get_name()

    blog_text = []

    for post in sorted(Post.query.filter_by(category=Post.Category.blog),
                       key=lambda p: p.posted_at, reverse=True):
        content = post.content

        blog_text.append(content)

    content = '\n'.join(blog_text)

    return render_template('blog.html', **locals())

我的“发布”逻辑在我的models.py：


class Post(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(200), nullable=False)
    posted_at = db.Column(db.DateTime, nullable=False, default=datetime.now())
    content = db.Column(db.String(200), nullable=False)
    content_type = db.Column(db.String(200), default="md", nullable=False)  # md / html
    category = db.Column(db.String(200), nullable=False, default="blog")  # poetry / blog

    class Type:
        md = "md"
        html = "html"

    class Category:
        poetry = "poetry"
        blog = "blog"

   ...

这是错误：

2019-07-12T21:59:09.120100+00:00 app[web.1]: 10.63.22.102 - - [12/Jul/2019:21:59:09 +0000] "GET /blog HTTP/1.1" 500 290 "https://secure-savannah-20745.herokuapp.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36"
2019-07-12T21:59:09.120443+00:00 heroku[router]: at=info method=GET path="/blog" host=secure-savannah-20745.herokuapp.com request_id=c32b2484-c8ff-4910-a4e2-5089eca7c6d3 fwd="98.0.144.26" dyno=web.1 connect=2ms service=66ms status=500 bytes=455 protocol=https
2019-07-12T21:59:13.632588+00:00 app[web.1]: 10.63.22.102 - - [12/Jul/2019:21:59:13 +0000] "GET /projects HTTP/1.1" 200 2342 "https://secure-savannah-20745.herokuapp.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36"
2019-07-12T21:59:13.837771+00:00 app[web.1]: 10.63.22.102 - - [12/Jul/2019:21:59:13 +0000] "GET /static/circles.js HTTP/1.1" 200 0 "https://secure-savannah-20745.herokuapp.com/projects" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36"
2019-07-12T21:59:13.838434+00:00 heroku[router]: at=info method=GET path="/static/circles.js" host=secure-savannah-20745.herokuapp.com request_id=8476c020-fc32-47b3-a4e1-7b6faeb115ca fwd="98.0.144.26" dyno=web.1 connect=1ms service=6ms status=200 bytes=5449 protocol=https
2019-07-12T21:59:13.633394+00:00 heroku[router]: at=info method=GET path="/projects" host=secure-savannah-20745.herokuapp.com request_id=faa3d473-7bda-4e39-810b-e3994eac7390 fwd="98.0.144.26" dyno=web.1 connect=1ms service=1494ms status=200 bytes=2504 protocol=https
2019-07-12T21:59:18.074035+00:00 heroku[router]: at=info method=GET path="/blog" host=secure-savannah-20745.herokuapp.com request_id=e9748e89-dcce-43db-8db8-a16c7fe657ab fwd="98.0.144.26" dyno=web.1 connect=3ms service=12ms status=500 bytes=455 protocol=https
2019-07-12T21:59:18.070372+00:00 app[web.1]: [2019-07-12 21:59:18,069] ERROR in app: Exception on /blog [GET]
2019-07-12T21:59:18.070385+00:00 app[web.1]: Traceback (most recent call last):
2019-07-12T21:59:18.070396+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
2019-07-12T21:59:18.070398+00:00 app[web.1]: cursor, statement, parameters, context
2019-07-12T21:59:18.070403+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
2019-07-12T21:59:18.070405+00:00 app[web.1]: cursor.execute(statement, parameters)
2019-07-12T21:59:18.070407+00:00 app[web.1]: sqlite3.OperationalError: no such table: user
2019-07-12T21:59:18.070410+00:00 app[web.1]: 
2019-07-12T21:59:18.070412+00:00 app[web.1]: The above exception was the direct cause of the following exception:
2019-07-12T21:59:18.070414+00:00 app[web.1]: 
2019-07-12T21:59:18.070416+00:00 app[web.1]: Traceback (most recent call last):
2019-07-12T21:59:18.070418+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
2019-07-12T21:59:18.070421+00:00 app[web.1]: response = self.full_dispatch_request()
2019-07-12T21:59:18.070423+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
2019-07-12T21:59:18.070425+00:00 app[web.1]: rv = self.handle_user_exception(e)
2019-07-12T21:59:18.070427+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
2019-07-12T21:59:18.070430+00:00 app[web.1]: reraise(exc_type, exc_value, tb)
2019-07-12T21:59:18.070432+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
2019-07-12T21:59:18.070434+00:00 app[web.1]: raise value
2019-07-12T21:59:18.070436+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
2019-07-12T21:59:18.070438+00:00 app[web.1]: rv = self.dispatch_request()
2019-07-12T21:59:18.070446+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
2019-07-12T21:59:18.070450+00:00 app[web.1]: return self.view_functions[rule.endpoint](**req.view_args)
2019-07-12T21:59:18.070452+00:00 app[web.1]: File "/app/main.py", line 65, in blog
2019-07-12T21:59:18.070455+00:00 app[web.1]: key=lambda p: p.posted_at, reverse=True):
2019-07-12T21:59:18.070457+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3324, in __iter__
2019-07-12T21:59:18.070459+00:00 app[web.1]: return self._execute_and_instances(context)
2019-07-12T21:59:18.070461+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3349, in _execute_and_instances
2019-07-12T21:59:18.070463+00:00 app[web.1]: result = conn.execute(querycontext.statement, self._params)
2019-07-12T21:59:18.070466+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
2019-07-12T21:59:18.070468+00:00 app[web.1]: return meth(self, multiparams, params)
2019-07-12T21:59:18.070470+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
2019-07-12T21:59:18.070472+00:00 app[web.1]: return connection._execute_clauseelement(self, multiparams, params)
2019-07-12T21:59:18.070474+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
2019-07-12T21:59:18.070477+00:00 app[web.1]: distilled_params,
2019-07-12T21:59:18.070479+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
2019-07-12T21:59:18.070481+00:00 app[web.1]: e, statement, parameters, cursor, context
2019-07-12T21:59:18.070484+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
2019-07-12T21:59:18.070486+00:00 app[web.1]: util.raise_from_cause(sqlalchemy_exception, exc_info)
2019-07-12T21:59:18.070488+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 399, in raise_from_cause
2019-07-12T21:59:18.070491+00:00 app[web.1]: reraise(type(exception), exception, tb=exc_tb, cause=cause)
2019-07-12T21:59:18.070493+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
2019-07-12T21:59:18.070495+00:00 app[web.1]: raise value.with_traceback(tb)
2019-07-12T21:59:18.070498+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
2019-07-12T21:59:18.070500+00:00 app[web.1]: cursor, statement, parameters, context
2019-07-12T21:59:18.070502+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
2019-07-12T21:59:18.070504+00:00 app[web.1]: cursor.execute(statement, parameters)
2019-07-12T21:59:18.070507+00:00 app[web.1]: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: user
2019-07-12T21:59:18.070513+00:00 app[web.1]: [SQL: SELECT user.id AS user_id, user.name AS user_name, user.posted_at AS user_posted_at, user.content AS user_content, user.content_type AS user_content_type, user.category AS user_category
2019-07-12T21:59:18.070515+00:00 app[web.1]: FROM user
2019-07-12T21:59:18.070517+00:00 app[web.1]: WHERE user.category = ?]
2019-07-12T21:59:18.070520+00:00 app[web.1]: [parameters: ('blog',)]
2019-07-12T21:59:18.070661+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/e3q8)
2019-07-12T21:59:18.072919+00:00 app[web.1]: 10.63.22.102 - - [12/Jul/2019:21:59:18 +0000] "GET /blog HTTP/1.1" 500 290 "https://secure-savannah-20745.herokuapp.com/projects" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36"

马丁·彼得斯

你错过了你的表user在你的模型中命名：

class Post(db.Model):
    __tablename__ = 'user'
    #               ^^^^^^

它不存在的原因是因为 Heroku不保留您的文件系统更改。您正在使用 SQLite 数据库：

... app[web.1]: sqlite3.OperationalError: no such table: user
#               ^^^^^^^

但是 SQLite 数据库存储在 Heroku dyno 的文件系统中。Heroku 一直在清理（循环）dynos，并为你启动一个新的dyno 来处理新的请求。从他们关于 Heroku 工作原理的文档中：

对一个 dyno 上的文件系统所做的更改不会传播到其他 dyno，并且不会在部署和 dyno 重新启动之间保持不变。更好且更具可扩展性的方法是使用共享资源，例如数据库或队列。

每次启动一个新的 dyno 来处理您的请求时，您将拥有一个没有 sqlite 数据库的新的、干净的文件系统，因此会再次创建一个空数据库，其中没有任何表。

底线：您不能在 Heroku 上使用 sqlite。

请改用 Postgres 数据库，请参阅Heroku Postgres 文档。别担心，您可以使用免费套餐。当您将数据库配置为附加组件时，DATABASE_URL会为您设置配置变量，您可以从应用程序环境中读取该变量。

我通常将设计为在 Heroku 上运行的 Flask 应用程序配置为读取环境变量，但在未设置环境变量时回退到 SQLite 数据库：

import os
from pathlib import Path

_project_root = Path(__file__).resolve().parent.parent
_default_sqlite_db = _project_root / "database.db"

SQLALCHEMY_DATABASE_URI = os.environ.get(
    "DATABASE_URL", f"sqlite:///{_default_sqlite_db}"
)

这使得在 SQLite 上本地开发应用程序变得容易，或者设置本地 Postgres 数据库进行集成测试，然后部署到 Heroku 进行暂存或生产。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。