mysql长连接_mysql.connector 数据库长连接

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

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

代码如下:

import mysql.connector

conn = None

def db_connect(conf):

"""数据库连接

根据配置信息,连接数据库

Arguments:

conf {dict} -- 连接配置信息

Returns:

MySQLConnection -- 数据库连接对象

Raises:

e -- 连接报错异常

"""

global conn

if isinstance(conn, mysql.connector.connection.MySQLConnection):

conn.ping(True)

return conn

try:

conn = mysql.connector.connect(**conf)

print('new connect operation')

return conn

except Exception as e:

raise e

测试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:

db2:

db3:

测试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:

db2:

new connect operation

db3:

测试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:

db_2:

db_3:

db_4:

db_5:

db_6:

db_7:

db_8:

db_9:

db_10:

db_11:

db_12:

db_13:

db_14:

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断开连接后,如果在用到该连接时发现无法连接将自动重连,不会再出现前面说明的问题了!