避免“MySQL服务器已经离开”对不常用的Python / Flask服务器与SQLAlchemy [英] Avoiding "MySQL server has gone away" on infrequently used Python / Flask server with SQLAlchemy

问题描述

我有一个不经常访问的Python / Flask服务器使用SQLAlchemy，如何配置Flask / SQLAlchemy来创建新的数据库连接？它每隔几天访问一次，在第一次访问它经常抛出MySQL服务器已经走了的错误。后续网页浏览量很好，但是看起来不太专业，因为出现这个初始错误。

我想知道正确的处理方式 - 像make a really长时间，这将是大约4天长在这种情况下，似乎不正确。

解决方案

我之前遇到过这个问题，并发现处理它的方式是不保持会话。麻烦的是你试图保持连接打开太久。而是在 __ init __。py 中使用线程本地作用域会话，或者在任何地方导入的实用程序包中使用：

  from sqlalchemy.orm import scoped_session，sessionmaker 
 Session = scoped_session（sessionmaker（））
  

然后设置您的引擎和元数据一次。这允许您在每次连接/断开时跳过配置机制。之后，你可以这样做你的db工作：

  session = Session（）
 someObject = session.query （someMappedClass）.get（someId）
＃使用正常的会话... 
 session.close（）
  

如果你想保持旧的对象，并且你不想离开你的会话，那么你可以使用上面的模式，并重用这样的旧对象：

  session = Session（）
 someObject = session.merge（someObject）
＃more db stuff 
 session.close ）
  

关键是要打开会话，完成工作，然后关闭会话。这可以很好地避免超时。 .merge和.add有很多选项，允许您包含对分离对象所做的更改或从数据库加载新数据。文档非常详细，但是一旦你知道你在寻找什么，它可能会更容易找到。

要真正得到一切，并防止MySQL从走开，你需要解决你的连接池的问题，保持连接打开太久，并检查一个旧的连接为你。

若要获得全新的连接，您可以在 create_engine <>中设置 pool_recycle / code>调用。将此 pool_recycle 设置为您希望创建新连接而不返回现有连接的检出之间的连接池中的时间秒数。

How can Flask / SQLAlchemy be configured to create a new database connection if one is not present?

I have an infrequently visited Python / Flask server which uses SQLAlchemy. It gets visited every couple of days, and on the first visit it often throws a "MySQL server has gone away" error. Subsequent page views are fine, but it looks unprofessional to have this initial error.

I'd like to know the correct way to handle this - advice like "make a really long time out", which would be about 4 days long in this case, doesn't seem correct. How can I test for the lack of a database connection and create one if needed?

解决方案

I've had trouble with this before, and found that the way to handle it is by not keeping sessions around. The trouble is you are trying to keep a connection open for way too long. Instead, use a thread local scoped session like so either in __init__.py or in a utility package that you import everywhere:

from sqlalchemy.orm import scoped_session, sessionmaker
Session = scoped_session( sessionmaker() )

Then set up your engines and metadata once. This allows you to skip configuration mechanics every time you connect/disconnect. After that, you can do your db work like this:

session = Session()
someObject = session.query( someMappedClass ).get( someId )
# use session like normal ...
session.close()

If you want to hold on to old objects and you don't want to leave your session open, then you can use the above pattern and reuse old objects like this:

session = Session()
someObject = session.merge( someObject )
# more db stuff
session.close()

The point is, you want to open your session, do your work, then close your session. This avoids timeouts very well. There are lots of options for .merge and .add that allow you to either include changes you've made to detached objects or to load new data from the db. The docs are very verbose, but once you know what you are looking for it might be a little easier to find.

To actually get all the way there and prevent the MySQL from "going away", you need to solve the issue of your connection pool keeping connections open too long and checking out an old connection for you.

To get a fresh connection, you can set the pool_recycle option in your create_engine call. Set this pool_recycle to the number of seconds of time in the connection pool between checkouts that you want a new connection to be created instead of an existing connection to be returned.

这篇关于避免“MySQL服务器已经离开”对不常用的Python / Flask服务器与SQLAlchemy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！