Flask-SQLAlchemy：在无效事务回滚之前无法重新连接 [英] Flask-SQLAlchemy: Can't reconnect until invalid transaction is rolled back
问题描述
My app config.py
SQLALCHEMY_DATABASE_URI ='< RDS Host>'
SQLALCHEMY_POOL_RECYCLE = 60
我的__ init __。py
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
$ b application = Flask（__ name__）
application.config.from_object（'config'）
db = SQLAlchemy（应用程序）
我有我的主要application.py
从flask导入Flask
从应用程序导入db
从application.models导入flask.ext.restless
Person
$ b $ application = Flask（__ name__）
application.debug = True
db.init_app（application）
@ application.route（'/' ）
def index（）：
returnHello，世界！
manager = flask.ext.restless.APIManager（application，flask_sqlalchemy_db = db）
manager.create_api（Person，methods = ['GET'，'POST'，'DELETE']）
$ b $ if if __name__ =='__main__'：
application.run（host ='0.0.0.0'）
$ b $ models.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 =电子邮件
然后，我有一个脚本来填充数据库，以便在数据库创建和应用程序启动后进行测试：
从应用程序导入db
从application.models导入Person
person = Person（'Bob'，'Jones'，'bob@website.net'）
db.session.add（person）
db.session.commit（）
使用db.drop_all（）和db.create_all（）重新设置数据库后，我启动应用程序。 py，然后脚本来填充数据库。
服务器将以正确的JSON响应，但是如果在几小时后回来检查，我会得到我需要的错误以回滚或有时2006年MySQL服务器已经消失的错误。
人们建议我更改MySQL服务器上的超时设置，但没有解决任何问题。以下是我的设置：
pre $ innodb_lock_wait_timeout = 3000
max_allowed_packet = 65536
net_write_timeout = 300
wait_timeout = 300
然后当我看到RDS监视器时，它显示MySQL服务器保持连接打开有一段时间，直到超时。现在纠正我，如果我错了，但不是连接应该关闭完成后？看起来，应用程序服务器一直在确保数据库连接存在，然后当MySQL服务器超时时，Flask / Flask-SQLAlchemy会抛出一个错误，并使应用程序服务器随之关闭。
任何建议，赞赏，谢谢！
我想这是什么加入
db.init_app（应用程序）
在application.py中，自从有了错误。
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.
My app config.py
SQLALCHEMY_DATABASE_URI = '<RDS Host>'
SQLALCHEMY_POOL_RECYCLE = 60
My __ 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
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
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:
from application import db
from application.models import Person
person = Person('Bob', 'Jones', 'bob@website.net')
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.
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.
People suggested that I change timeout settings on the MySQL server but that hasn't fixed anything. Here are my settings:
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.
Any suggestions are appreciated, thanks!
I think what did it was adding
db.init_app(application)
in application.py, haven't had the error since.
这篇关于Flask-SQLAlchemy：在无效事务回滚之前无法重新连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
