Python防止mysql.connector 连接数据库超时

最近python服务经常报连接不存在,后来发现原来的同事写的是缓存来存储连接对象,过期就关闭重连,可是有其他线程还在用该连接,于是就出现了连接不存在的错误,于是改进一下:

连接数据库时查看连接对象是否存在,如果不存在则创建,如果存在就做一下ping(True)的操作,如果连接被远程关闭了就会自动重连

代码如下:

import mysql.connector
 
conn = None
 
def db_connect(conf):
    """数据库连接
    根据配置信息,连接数据库
    Arguments:
        conf {dict} -- 连接配置信息
    Returns:
        MySQLConnection -- 数据库连接对象
    Raises:
        e -- 连接报错异常
    """
    global conn
    try:
        if isinstance(conn, mysql.connector.connection.MySQLConnection):
            conn.ping(True,10,3)
            return conn
    except Exception as e:
        print('mysql not connected')
 
    try:
        conn = mysql.connector.connect(**conf)
        print('new connect operation')
        return conn
    except Exception as e:
        raise e

ping(True,10,3)表示执行ping指令,如果无法ping则尝试重连,重连失败尝试10次,间隔3秒,最终如果实在无法连接,将会抛出重连失败的异常,这时我们需要重写实例化连接(可能是连接配置变化了或者其他情况)

测试1:

db_conf = {
    "host": "192.168.102.154",
    "user": "cab100001",
    "password": "CUPzyHcoK9",
    "database": "cab100001"
}
db1 = db_connect(db_conf)
print('db1:', db1)
db2 = db_connect(db_conf)
print('db2:', db2)
db3 = db_connect(db_conf)
print('db3:', db3)

结果都是同一个连接对象:

new connect operation
db1: <mysql.connector.connection.MySQLConnection object at 0x00000000025C00F0>
db2: <mysql.connector.connection.MySQLConnection object at 0x00000000025C00F0>
db3: <mysql.connector.connection.MySQLConnection object at 0x00000000025C00F0>

测试2:试着关闭一下连接:

db_conf = {
    "host": "192.168.102.154",
    "user": "cab100001",
    "password": "CUPzyHcoK9",
    "database": "cab100001"
}
db1 = db_connect(db_conf)
print('db1:', db1)
db1.close()
db2 = db_connect(db_conf)
print('db2:', db2)
conn = None
db3 = db_connect(db_conf)
print('db3:', db3)

结果只有conn清空后才会重新从配置创建新的连接对象

new connect operation
db1: <mysql.connector.connection.MySQLConnection object at 0x0000000002AF10B8>
db2: <mysql.connector.connection.MySQLConnection object at 0x0000000002AF10B8>
new connect operation
db3: <mysql.connector.connection.MySQLConnection object at 0x0000000002ECEC18>

测试3: 远程mysql服务关闭服务

db_conf = {
    "host": "192.168.102.154",
    "user": "cab100001",
    "password": "CUPzyHcoK9",
    "database": "cab100001"
}
n = 1
while True:
    db = db_connect(db_conf)
    print('db_{}:'.format(n), db)
    time.sleep(5)
    n += 1

mysql远程关闭:

show processlist;
-- 查看本地连接的process找出
id kill id; show processlist;
-- 发现对应process已经消失,再查一下processlist发现新的连接了 过程中的连接对象没变

new connect operation
db_1: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_2: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_3: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_4: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_5: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_6: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_7: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_8: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_9: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_10: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_11: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_12: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_13: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>
db_14: <mysql.connector.connection.MySQLConnection object at 0x0000000002ABD048>

ping源码:

def ping(self, reconnect=False, attempts=1, delay=0):
    """Check availability of the MySQL server
    When reconnect is set to True, one or more attempts are made to try
    to reconnect to the MySQL server using the reconnect()-method.
    delay is the number of seconds to wait between each retry.
    When the connection is not available, an InterfaceError is raised. Use
    the is_connected()-method if you just want to check the connection
    without raising an error.
    Raises InterfaceError on errors.
    """
    try:
        self.cmd_ping()
    except:
        if reconnect:
            self.reconnect(attempts=attempts, delay=delay)
        else:
            raise errors.InterfaceError("Connection to MySQL is"
                                        " not available.")

默认是不重连的,ping(True)如果发现无法ping则会调用reconnect重新连接,这样如果长时间没操作,远程mysql断开连接后,如果在用到该连接时发现无法连接将自动重连,不会再出现前面说明的问题了!