在长时间运行的python脚本中处理MySQL连接的最佳实践

Question

我目前有一个长时间运行的python脚本(多天(，它偶尔会在mysql数据库中执行查询。查询是从不同的线程执行的。

我连接到数据库如下：

import mysql.connector
import time
class DB():
connection = None
def __init__(self, host, user, password, database):
self.host = host;
self.user = user;
self.password = password;
self.database = database;
self.connect()
def connect(self):
try:
self.connection =  mysql.connector.connect(
host = self.host,
user = self.user,
password = self.password,
database = self.database,
autocommit = True,
)
except Exception as exception:
print("[DBCONNECTION] {exception} Retrying in 5 seconds.".format(exception = str(exception)))
time.sleep(5)
self.connect()
db = DB("11.111.11.111", "mydb", "mydb", "mydb")

执行查询的功能之一：

def getUsersDB(self):
db.connection.ping(reconnect=True)
cursor = db.connection.cursor()
cursor.execute("SELECT * FROM Users")

users = cursor.fetchall()
return users

我相信，我离如何处理这种联系还有很长的路要走。对于这种情况，正确的方法是什么？

Answer 1

您的方法的问题是db.connection.ping(reconnect=True)没有向您承诺实时连接。

您可以在此处阅读函数的描述：https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-ping.html

你可以尝试使用这个：

class DB:
connection = None
def __init__(
self,
host: str,
user: str,
password: str,
database: str
) -> None:
self.host = host
self.user = user
self.password = password
self.database = database
self.connection = self.init_conn()
def init_conn(
self,
):
return mysql.connector.connect(
host=self.host,
user=self.user,
password=self.password,
database=self.database,
)
def get_cursor(
self,
):
# This will try to reconnect also.
# In case it fails, you will have to create a new connection
try:
self.connection.ping(
reconnect=True,
attempts=3,
delay=5,
)
except mysql.connector.InterfaceError as err:
self.connection = self.init_conn()
return self.connection.cursor()

并像这样使用DB对象：

def getUsersDB(self):
cursor = db.get_cursor() # cursor from a live connection 
cursor.execute("SELECT * FROM Users")

users = cursor.fetchall()
return users