最近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斷開連接配接後,如果在用到該連接配接時發現無法連接配接将自動重連,不會再出現前面說明的問題了!