烧瓶SQLAlchemy - 2013失去连接

Question

因此，我尝试使用数据库和烧瓶动态地构建一个站点。该站点的工作方式与我现在想要的一样，但只有大约一分钟的时间，然后收到一条错误消息，上面写着：

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

该数据库包含我希望为每个页面抓取的4个条目，以及一个主页面，其中我只从数据库中的每个条目中获取2个条目。在这条线的某个地方，它遇到了一个错误，无法再访问数据库，因此不允许任何使用数据库的页面加载。

有人能帮我弄清楚为什么会发生这种事吗？我对烧瓶和炼金术很陌生

下面是我的代码：

Database.py

from flask import Flask
from flask import render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://databaseaddress'
db = SQLAlchemy(app)


class Kitchen(db.Model):
    __tablename__ = 'kitchens'
    id = db.Column('id', db.Integer, primary_key=True)
    description = db.Column('description')
    galleryId = db.Column('galleryId')
    cpo = db.Column('cpo')

    def __init__(self, description, galleryId, cpo):
        self.description = description
        self.galleryId = galleryId
        self.cpo = cpo

    def __repr__(self):
        return '<Gallery %r>' % self.description

    def get_description(self):
        return self.description

    def get_galleryId(self):
        return self.galleryId

    def get_cpo(self):
        return self.cpo

app.py

from flask import Flask
from flask import render_template
from Database import Kitchen

app = Flask(__name__)


@app.route('/')
@app.route('/index')
def main():
    return render_template('index.html')


@app.route('/kitchens', methods=['GET'], defaults={'n': 0})
@app.route('/kitchens/<n>', methods=['GET'])
def kitchens(n):
    row = Kitchen.query.count()
    if int(n) is 0:
        description = []
        for num in range(1, row+1):
            data = Kitchen.query.filter_by(id=num).first()
            description.append(Kitchen.get_description(data))
        return render_template('Gallery Directory.html', portfolio='Kitchens', row=row, description=description)
    elif int(n) >= 1:
        data = Kitchen.query.filter_by(id=n).first()
        description = Kitchen.get_description(data)
        galleryId = Kitchen.get_galleryId(data)
        cpo = Kitchen.get_cpo(data)
        return render_template('Gallery Basic.html', portfolio='Kitchens', description=description, id=int(n),
                               galleryId=galleryId, cpo=cpo, row=row)


if __name__ == "__main__":
    app.run()

错误消息

[2017-02-10 10:17:31,776] ERROR in app: Exception on /kitchens/ 1 [GET]
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in execute
result = self._query(query)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 835, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1019, in _read_query_result
result.read()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1302, in read
first_packet = self.connection._read_packet()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 961, in _read_packet
packet_header = self._read_bytes(4)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 998, in _read_bytes
2013, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

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

Traceback (most recent call last):
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1988, in wsgi_app
response = self.full_dispatch_request()
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1641, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1544, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
raise value
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1639, in full_dispatch_request
rv = self.dispatch_request()
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1625, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/home/devin-matte/Documents/Coralite/Coralite.py", line 18, in kitchens
row = Kitchen.query.count()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 3024, in count
return self.from_self(col).scalar()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2778, in scalar
ret = self.one()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2749, in one
ret = self.one_or_none()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2719, in one_or_none
ret = list(self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2790, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2813, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in execute
result = self._query(query)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 835, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1019, in _read_query_result
result.read()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1302, in read
first_packet = self.connection._read_packet()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 961, in _read_packet
packet_header = self._read_bytes(4)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 998, in _read_bytes
2013, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT kitchens.id AS kitchens_id, kitchens.description AS kitchens_description, kitchens.`galleryId` AS `kitchens_galleryId`, kitchens.cpo AS kitchens_cpo \nFROM kitchens) AS anon_1']
127.0.0.1 - - [10/Feb/2017 10:17:31] "GET /kitchens/%201 HTTP/1.1" 500 -

Answer 1

这个问题不适用于方炼金术或烧瓶。实际上是在mysql服务器端。正在使用的服务器正在超时并与任何非本地主机的连接断开连接。

切换到本地site数据库解决了这个问题，直到我能够获得一个不同的服务器来承载mysql数据库，从而允许远程连接/在数据库的本地主机上承载站点。

Answer 2

我也有过类似的问题。这是我的解决方案：

从DB (MariaDB/MySQL)获取wait_timeout

SHOW GLOBAL VARIABLES LIKE "wait_timeout";

在烧瓶代码中设置SQLALCHEMY_POOL_RECYCLE：

app = Flask(__name__)
app.config['SQLALCHEMY_POOL_RECYCLE'] = <db_wait_timeout> - 1

Answer 3

首先，为什么要在app.py和Database.py中两次实例化Flask对象？您的项目应该只实例化一个Flask对象。如果您想使您的水瓶项目更加模块化，检查蓝图对象在这里http://flask.pocoo.org/docs/0.12/blueprints/#blueprints。

现在，尝试下面的内容，并注意我还没有测试过它，如果它不起作用，请告诉我：

Database.py

from flask import Flask
from flask import render_template
from app import db

class Kitchen(db.Model):
    __tablename__ = 'kitchens'
    id = db.Column('id', db.Integer, primary_key=True)
    description = db.Column('description')
    galleryId = db.Column('galleryId')
    cpo = db.Column('cpo')

    def __init__(self, description, galleryId, cpo):
        self.description = description
        self.galleryId = galleryId
        self.cpo = cpo

    def __repr__(self):
        return '<Gallery %r>' % self.description

    def get_description(self):
        return self.description

    def get_galleryId(self):
        return self.galleryId

    def get_cpo(self):
        return self.cpo

app.py

from flask import Flask
from flask import render_template
from Database import Kitchen
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://databaseaddress'
db = SQLAlchemy(app)

@app.route('/')
@app.route('/index')
def main():
    return render_template('index.html')


@app.route('/kitchens', methods=['GET'], defaults={'n': 0})
@app.route('/kitchens/<n>', methods=['GET'])
def kitchens(n):
    row = Kitchen.query.count()
    if int(n) is 0:
        description = []
        for num in range(1, row+1):
            data = Kitchen.query.filter_by(id=num).first()
            description.append(Kitchen.get_description(data))
        return render_template('Gallery Directory.html', portfolio='Kitchens', row=row, description=description)
    elif int(n) >= 1:
        data = Kitchen.query.filter_by(id=n).first()
        description = Kitchen.get_description(data)
        galleryId = Kitchen.get_galleryId(data)
        cpo = Kitchen.get_cpo(data)
        return render_template('Gallery Basic.html', portfolio='Kitchens', description=description, id=int(n),
                               galleryId=galleryId, cpo=cpo, row=row)


if __name__ == "__main__":
    db.create_all()
    app.run()