pymsql连接到数据库并插入数据

 

#定义类，初始化连接到mysql数据库，失败重连
class MySQLOPS:
    def __init__(self, db_host, db_user, db_password, db_database):
        self.db_config = {
            "host": db_host,
            "user": db_user,
            "password": db_password,
            "database": db_database,
            "charset": 'utf8mb4'
        }
        self.conn = None

    def _ensure_connection(self):
        #失败重连，最多重试2次
        max_retries = 2
        for _ in range(max_retries):
            print("_:",_)
            try:
                if self.conn == None or not self.conn.open:
                    self.conn = pymysql.connect(**self.db_config)
                    print('mysql connect successfully.')
                                #类似心跳检查机制，失败重连
                self.conn.ping(reconnect=True)
                return True
            except (pymysql.MySQLError, ConnectionError) as e:
                print(f'mysql connect failed: {e}, reconnecting...')
                self.conn = None
        return False

    def exec_insert(self, sql, sql_params = None):
        #执行sql insert语句
        try:
            if self._ensure_connection():
                with self.conn.cursor() as cursor:
                    cursor.execute(sql, sql_params)
                    self.conn.commit()
            else:
                print(f'connect to mysql failed: {e}')
        except Exception as e:
            print(f'mysql insert failed: {e}')
            self.conn = None
            raise

db_host = '10.0.6.2'
db_user = 'monitor'
db_password = 'password'
db_database = 'monitordb'

db = MySQLOPS(db_host, db_user, db_password, db_database)


mail_sender = 'liaaa@yx.com'
mail_recipients = 'lia@yx.com'
mail_subject = 'hello'
mail_status = 'no'
mail_receivetime = '2025-04-29 11:33:22'

sql_insert = """INSERT INTO tablename (sender, recipients, subject, status, receivetime) VALUES (%s, %s, %s, %s, %s)"""
sql_params = (mail_sender, mail_recipients, mail_subject, mail_status, mail_receivetime)
db.exec_insert(sql_insert,sql_params)

    

 

通过数据库连接池的方式连接到mysql，提供并发插入能力

#yum install mysql-devel pkgconfig

from concurrent.futures import ThreadPoolExecutor

#pip3 install pymysql-pool
#导入mysql模块
import pymysql
from pymysqlpool import ConnectionPool

#定义类，连接到Mysql并插入数据
class MySQLOPS:
    def __init__(self, db_host, db_user, db_password, db_database):
        self.pool = None
        self.db_config = {
            "host": db_host,
            "user": db_user,
            "password": db_password,
            "database": db_database,
            "charset": 'utf8mb4',
            "autocommit": True,
            "name": "my_pool",
            "size": 8,
            "pre_create_num": 8,
            "maxsize": 20
        }
        self._init_pool()

    def _init_pool(self):
        """初始化线程安全连接池"""
        try:
            # 创建具备自动重连能力的连接池
            self.pool = ConnectionPool(**self.db_config)
        except Exception as e:
            print(f"Error MySQL connection pool init failed: {e}")
            raise

    def _get_connection(self):
        """安全获取连接（带异常处理）"""
        try:
            # 从池中获取连接（自动管理生命周期）
            return self.pool.get_connection()
        except Exception as e:
            print(f"Error MySQL Get connection failed: {e}")
            return None

    def exec_insert(self, sql, sql_params = None):
        #执行sql insert语句
        try:
            conn = self._get_connection()
            if not conn:
                print("Error MySQL Failed to get valid connection from pool")
                return

            with conn.cursor() as cursor:
                cursor.execute(sql, sql_params)

        except pymysql.OperationalError as e:
            # 网络级错误自动触发连接池重建
            print(f"Error MySQL Operational error: {e}, resetting pool...")
            self._reset_pool()
        except Exception as e:
            print(f"Error MySQL Unexpected error: {e}")
        finally:
            # 确保连接归还到池中
            if conn and conn.open:
                conn.close()

    def _reset_pool(self):
        """重建连接池（应对网络中断等严重问题）"""
        try:
            if self.pool:
                self.pool.close()  # 优雅关闭旧连接
            self._init_pool()
        except Exception as e:
            logc.critical(f"Error MySQL Pool reset failed: {e}")


#定义mysql数据库连接信息
db_host = '10.0.6.22'
db_user = 'monitor'
db_password = 'password'
db_database = 'monitor'
#设置并发线程数量
thread_number = 4
#设置线程池执行器，max_workers可以根据需要调整
executor = ThreadPoolExecutor(max_workers=thread_number)

#连接到Mysql数据库
db = MySQLOPS(db_host, db_user, db_password, db_database)
sql_insert = """INSERT INTO fishmailcheck (sender, recipients, subject, status, receivetime) VALUES (%s, %s, %s, %s, %s)"""



#创建函数，插入数据
def insert(mail_sender, mail_recipients, mail_subject, mail_status, mail_receivetime):
    db.exec_insert(sql_insert,(mail_sender, mail_recipients, mail_subject, mail_status, mail_receivetime))


for r in range(10):   
    mail_sender = f'l.chen@yx.com-{r}'
    mail_recipients = f'liu@yx.com-{r}'
    mail_subject = f'subject-{r}'
    mail_status = f'yes-{r}'
    mail_receivetime = f'2025-05-09 16:0{r}'

    executor.submit(insert, mail_sender, mail_recipients, mail_subject, mail_status, mail_receivetime)