Flask+PyMySQL接口首次请求失败后续正常，调试时可成功（AttributeError）

问题 #

使用Flask+PyMySQL开发API，前端Vue通过Axios请求。首次请求必失败，后续请求全部正常；但如果插入breakpoint()分步调试，首次请求就能成功。所有请求参数完全一致，错误为AttributeError: 'NoneType' object has no attribute 'read'，回溯显示错误出现在cursor.execute()处。

API核心代码：

@app.route(f"{api_url_prefix}/warning/details", methods=["GET"])
@siwa.doc(query=WarningDetailModel)
def warnings():
    args = request.args.to_dict(flat=True)
    if "startts" not in args or "endts" not in args:
        return "Error query, start and end timestamp must be fed"
    start = int(args.get("startts"))
    end = int(args.get("endts"))
    start_str = datetime.datetime.fromtimestamp(start).strftime(config.time_format)
    end_str = datetime.datetime.fromtimestamp(end).strftime(config.time_format)
    query_sql = "SELECT * FROM warning_data WHERE time > %(start_str)s AND time < %(end_str)s ORDER BY time DESC"
    query_dic = {
        "start_str": start_str,
        "end_str": end_str,
    }
    check_db_connection(conn)
    with conn.cursor() as cursor:
        cursor.execute(query_sql, query_dic)
        res = cursor.fetchall()
        total_count = len(res)
    if res:
        # Correct the datetime format
        processed_res = []
        for item in res:
            item["time"] = item.get("time").strftime(config.time_format)
            processed_res.append(item)
        result = {
            "data": processed_res,
            "total_count": total_count,
        }
        return result
    else:
        return {
            "data": [],
            "total_count": 0,
        }

完整回溯信息：

Traceback (most recent call last):
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/flask/app.py", line 2190, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/flask/app.py", line 1486, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/flask_cors/extension.py", line 176, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/flask_siwadoc/__init__.py", line 164, in wrapper
    return func(*args, **kwargs)
  File "web.py", line 268, in warnings
    cursor.execute(query_sql, query_dic)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/connections.py", line 1207, in read
    self._read_result_packet(first_packet)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/connections.py", line 1283, in _read_result_packet
    self._get_descriptions()
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/connections.py", line 1368, in _get_descriptions
    field = self.connection._read_packet(FieldDescriptorPacket)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/connections.py", line 758, in _read_packet
    recv_data = self._read_bytes(bytes_to_read)
  File "/home/houlu/.local/share/virtualenvs/MQTT_client-e0BPhhQw/lib/python3.8/site-packages/pymysql/connections.py", line 779, in _read_bytes
    data = self._rfile.read(num_bytes)
AttributeError: 'NoneType' object has no attribute 'read'

原因分析 #

这个错误的核心是全局数据库连接对象conn在首次请求时处于失效状态：

1. 复用的全局连接可能因长时间无活动（MySQL默认8小时无交互会断开连接）被服务器主动切断，但本地连接对象未更新状态。
2. check_db_connection(conn)函数逻辑存在缺陷：仅检查连接的表面状态（如conn.open），未验证连接是否真的能和服务器通信，导致失效连接被继续使用。
3. 调试时成功是因为breakpoint()的停顿让连接检查/重建逻辑有足够时间完成，或者触发了隐性的连接修复流程。

当连接被服务器断开后，PyMySQL连接对象内部的_rfile会变为None，执行cursor.execute()时尝试读取服务器返回数据，就会抛出'NoneType' object has no attribute 'read'错误。后续请求时，check_db_connection可能正确重建了连接，因此请求正常。

解决方案 #

方案1：修复连接检查函数，确保连接真实可用 #

修改check_db_connection函数，通过测试查询验证连接有效性，失效时重建连接：

def check_db_connection(conn):
    try:
        # 执行ping操作自动重连失效连接
        conn.ping(reconnect=True)
    except Exception as e:
        # 手动重建连接
        conn = pymysql.connect(
            host=config.db_host,
            user=config.db_user,
            password=config.db_password,
            database=config.db_name,
            charset='utf8mb4'
        )
    return conn

在API函数中更新连接对象：

conn = check_db_connection(conn)
with conn.cursor() as cursor:
    # 后续代码不变

方案2：避免全局连接，每次请求创建新连接 #

Flask是多线程/多进程模型，全局连接并非线程安全，每次请求创建新连接更稳妥：

@app.route(f"{api_url_prefix}/warning/details", methods=["GET"])
@siwa.doc(query=WarningDetailModel)
def warnings():
    # 前面参数处理代码不变
    # 每次请求创建新连接
    conn = pymysql.connect(
        host=config.db_host,
        user=config.db_user,
        password=config.db_password,
        database=config.db_name,
        charset='utf8mb4'
    )
    try:
        with conn.cursor() as cursor:
            cursor.execute(query_sql, query_dic)
            res = cursor.fetchall()
            total_count = len(res)
    finally:
        conn.close()  # 确保连接关闭
    # 后续结果处理代码不变

方案3：使用连接池（推荐） #

用连接池管理数据库连接，自动处理连接的创建、复用和重建：

from dbutils.pooled_db import PooledDB
import pymysql

# 初始化连接池
pool = PooledDB(
    creator=pymysql,
    host=config.db_host,
    user=config.db_user,
    password=config.db_password,
    database=config.db_name,
    charset='utf8mb4',
    maxconnections=5,  # 最大连接数
    mincached=1,       # 初始化空闲连接数
)

@app.route(f"{api_url_prefix}/warning/details", methods=["GET"])
@siwa.doc(query=WarningDetailModel)
def warnings():
    # 参数处理代码不变
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            cursor.execute(query_sql, query_dic)
            res = cursor.fetchall()
            total_count = len(res)
    finally:
        conn.close()  # 放回连接池，并非真正关闭
    # 结果处理代码不变

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