保持mysql连接的正确方法 [英] Correct way of keeping mysql connection

问题描述

我有一个运行24/7的应用程序，该应用程序使用mysql.它的不同功能使用mysql.一种实现方法是在应用程序中创建一个全局mysql实例，如下所示:

I have an application running 24/7 which uses mysql. Different functions of it use mysql. One way to implement it is to create a global mysql instance in the application like this:

self.db = MySQLdb.connect(
            host=self.settings.MYSQL_HOST_LOCAL,
            user=self.settings.MYSQL_USER,
            passwd=self.settings.MYSQL_PASS,
            db=self.settings.MYSQL_DB,
            use_unicode=True,
            charset="utf8",
        )

并使用self.db.execute(...)执行命令.通过这样做，该应用程序使用1个连接.另一种方法是每次需要执行事务时都建立连接.

and execute commands using self.db.execute(...). By doing this, the application uses 1 connection. The other way is to create connection every time I need to execute a transaction.

方法1，阻止应用程序一遍又一遍地创建和删除连接，但是如果保持理想状态，它将面临"mysql走开"的问题.方法2，"mysql走开"没有问题，但是I/O过多.

Approach 1, prevents the application from creating and deleting connections over and over but it will face "mysql gone away" problem if it stays ideal. Approach 2, doesn't have a problem with "mysql gone away", but it has too much I/O.

我很确定这些批准缓存都不是正确的，但是正确的方法是什么?

I am pretty sure neither these approcaches are the right ones, but what is the right approach?

推荐答案

一种方法是每次执行时都创建一个连接.您也可以创建一个函数来为您执行此操作.这就是它的本意.这不是太多的I/O.

One way to do is to create a connection every time you need to execute. You can also create a function to do it for you. This is how it meant to be. It is not too much I/O..

您还可以执行以下操作:

You can also do the following:

while True:
    try:
        db_session.execute('SELECT * FROM table')
        break
    except SQLAlchemyError:
        db_session.rollback()

如果连接断开，将引发异常，会话将被回滚，它将再次尝试可能成功. (第一种解决方案要好得多)

If the connection has go away, this will raise an exception, the session will be rollbackd, it'll try again is likely to succeed. (The first solution is much better)

这篇关于保持mysql连接的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！