Python Flask-SQLAlchemy：在回滚无效事务之前无法重新连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31121948/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Flask-SQLAlchemy: Can't reconnect until invalid transaction is rolled back
提问by internetwhy
So I am using Amazon Web Services RDS to run a MySQL server and using Python's Flask framework to run the application server and Flask-SQLAlchemy to interface with the RDS.
所以我使用 Amazon Web Services RDS 来运行 MySQL 服务器，并使用 Python 的 Flask 框架来运行应用程序服务器和 Flask-SQLAlchemy 来与 RDS 进行交互。
My app config.py
我的应用程序 config.py
SQLALCHEMY_DATABASE_URI = '<RDS Host>'
SQLALCHEMY_POOL_RECYCLE = 60
My __ init __.py
我的 __ init __.py
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
application = Flask(__name__)
application.config.from_object('config')
db = SQLAlchemy(application)
I have my main application.py
我有我的主要 application.py
from flask import Flask
from application import db
import flask.ext.restless
from application.models import Person
application = Flask(__name__)
application.debug=True
db.init_app(application)
@application.route('/')
def index():
return "Hello, World!"
manager = flask.ext.restless.APIManager(application, flask_sqlalchemy_db=db)
manager.create_api(Person, methods=['GET','POST', 'DELETE'])
if __name__ == '__main__':
application.run(host='0.0.0.0')
The models.py
模型.py
class Person(db.Model):
__bind_key__= 'people'
id = db.Column(db.Integer, primary_key=True)
firstName = db.Column(db.String(80))
lastName = db.Column(db.String(80))
email = db.Column(db.String(80))
def __init__(self, firstName=None, lastName=None, email=None):
self.firstName = firstName
self.lastName = lastName
self.email = email
I then have a script to populate the database for testing purposes after db creation and app start:
然后我有一个脚本来填充数据库以在 db 创建和应用程序启动后进行测试：
from application import db
from application.models import Person
person = Person('Bob', 'Jones', '[email protected]')
db.session.add(person)
db.session.commit()
Once I've reset the database with db.drop_all() and db.create_all() I start the application.py and then the script to populate the database.
使用 db.drop_all() 和 db.create_all() 重置数据库后，我将启动 application.py，然后启动脚本以填充数据库。
The server will respond with correct JSON but if I come back and check it hours later, I get the error that I need to rollback or sometimes the 2006 error that the MySQL server has gone away.
服务器将以正确的 JSON 响应，但如果我在几小时后回来检查它，我会收到需要回滚的错误，或者有时会出现 MySQL 服务器已消失的 2006 错误。
People suggested that I change timeout settings on the MySQL server but that hasn't fixed anything. Here are my settings:
人们建议我更改 MySQL 服务器上的超时设置，但这并没有解决任何问题。这是我的设置：
innodb_lock_wait_timeout = 3000
max_allowed_packet = 65536
net_write_timeout = 300
wait_timeout = 300
Then when I look at the RDS monitor, it shows the MySQL server kept the connection open for quite a while until the timeout. Now correct me if I'm wrong but isn't the connection supposed to be closed after it's finished? It seems that the application server keeps making sure that the database connection exists and then when the MySQL server times out, Flask/Flask-SQLAlchemy throws an error and brings down the app server with it.
然后当我查看 RDS 监视器时，它显示 MySQL 服务器保持连接打开很长一段时间，直到超时。现在纠正我，如果我错了，但连接完成后不是应该关闭吗？应用服务器似乎一直在确保数据库连接存在，然后当 MySQL 服务器超时时，Flask/Flask-SQLAlchemy 会抛出错误并关闭应用服务器。
Any suggestions are appreciated, thanks!
任何建议表示赞赏，谢谢！
采纳答案by internetwhy
I think what did it was adding
我认为它添加了什么
db.init_app(application)
in application.py, haven't had the error since.
在 application.py 中，此后没有出现错误。
回答by manychairs
Alternatively, use this at the end of the script that populates your database:
或者，在填充数据库的脚本末尾使用它：
db.session.close()
That should prevent those annoying "MySQL server has gone away" errors.
这应该可以防止那些烦人的“MySQL 服务器已经消失”错误。
回答by leejaycoke
Everytime checking rollback or not is troublesome..
每次检查回滚与否都很麻烦..
I made insert, update functions which need commit.
我做了插入，更新需要提交的功能。
@app.teardown_request
def session_clear(exception=None):
Session.remove()
if exception and Session.is_active:
Session.rollback()
回答by Sheharyar Naseem
Here you missing pool recycle as MySql closes session after some time so you need to add pool recycle so that connections in pool get reconnect after pool recycle time.
在这里，您缺少池回收，因为 MySql 在一段时间后关闭会话，因此您需要添加池回收，以便池中的连接在池回收时间后重新连接。
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3600
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3600
