Python执行批量MySQL INSERT循环数分钟后出现连接不可用错误

问题背景 #

运行包含大量MySQL INSERT操作的Python循环，约5分钟后触发错误：

raise OperationalError("MySQL Connection not available")
mysql.connector.errors.OperationalError: MySQL Connection not available

游标配置为buffered=True、autocommit=False，尝试每次INSERT后提交事务未解决问题。

解决方案 #

1. 检查并调整MySQL服务器超时设置 #

MySQL默认的wait_timeout和interactive_timeout如果过短（比如小于300秒），会主动断开闲置连接。登录MySQL执行以下命令查看：

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

若值过小，临时调整（重启MySQL后失效）：

SET GLOBAL wait_timeout = 3600;
SET GLOBAL interactive_timeout = 3600;

永久生效需修改my.cnf（或my.ini）：

wait_timeout = 3600
interactive_timeout = 3600

修改后重启MySQL服务。

2. 添加连接保活机制 #

长时间运行的脚本可能因网络波动导致连接失效，在循环中定期执行简单查询维持连接：

count = 0
for result in result_set:
    # 你的数据处理代码
    target_mysql_cursor.execute(platform_stats_insert_query, platform_stats_record)
    count += 1
    # 每100条执行一次保活查询并提交
    if count % 100 == 0:
        target_mysql_cursor.execute("SELECT 1")
        target_mysql.commit()

3. 优化事务提交策略 #

单次INSERT提交会产生大量IO开销，改用批量提交：

batch_size = 1000
count = 0
for result in result_set:
    # 数据处理代码
    target_mysql_cursor.execute(platform_stats_insert_query, platform_stats_record)
    count += 1
    # 每1000条提交一次事务
    if count % batch_size == 0:
        target_mysql.commit()
# 循环结束后提交剩余未提交的记录
target_mysql.commit()

4. 修复连接有效性检查与重连逻辑 #

原代码中用&（按位与）判断连接状态是错误的，需改为and（逻辑与）；同时添加重连机制：

def reconnect_if_needed(conn, cursor):
    if not conn.is_connected():
        conn.reconnect()
        # 连接断开后旧游标失效，重新创建
        return conn.cursor(buffered=True)
    return cursor

# 在循环中定期检查并重连
for hour in range(0,23):
    target_mysql_cursor = reconnect_if_needed(target_mysql, target_mysql_cursor)
    source_mysql_cursor.execute(raw_data_query, (hour, hour))
    result_set = source_mysql_cursor.fetchall()
    # 后续处理代码...

5. 优化数据处理逻辑 #

原代码将查询结果转为字符串拆分的方式低效且易出错，直接使用元组索引：

# 替换原有的result_string和row处理逻辑
platform_stats_record = (
    result[0][2:], 
    result[1], result[2], result[3], result[4], result[5],
    result[6], result[7], result[8], result[9], result[10],
    result[11], result[12], result[13], result[14], result[15],
    result[16], result[17], result[18][:8], result[19][:8]
)

这种方式能大幅提升处理速度，减少循环运行时间，间接降低连接超时概率。

内容的提问来源于stack exchange，提问作者Pythonist