React调用Flask接口时出现psycopg2递归连接错误

一、后端核心问题与修复 #

错误根源是全局数据库连接在多请求并发时的冲突：Flask默认多线程运行，代码中使用全局connection对象，当多个前端请求同时到达时，多线程会尝试递归进入同一个连接的上下文（with connection:），触发psycopg2的递归连接限制。

修复方案1：每次请求创建独立连接 #

将数据库连接的创建和销毁放在视图函数内部，确保每个请求使用独立连接：

import psycopg2
from flask import Flask

app = Flask(__name__)

PLOT_PROJECT_CHECK = "SELECT ..."  # 替换为你的查询语句
PLOT_PROJECT_DEACTIVATE = "UPDATE ..."  # 替换为你的更新语句

@app.get("/api/plot-project/<int:plot_id>/<int:project_id>")
def check_and_deactivate(plot_id, project_id):
    # 每次请求创建新连接
    conn = psycopg2.connect(
        user="你的数据库用户名",
        password="你的数据库密码",
        host="127.0.0.1",
        port="5432",
        database="你的数据库名"
    )
    try:
        with conn.cursor() as cursor:
            cursor.execute(PLOT_PROJECT_CHECK, (plot_id, project_id))
            data = cursor.fetchall()
            if len(data) == 0:
                return "No data found", 404
            removed = data.pop(0)
            if len(data) > 1:
                for row in data:
                    print(row[0])
                    cursor.execute(PLOT_PROJECT_DEACTIVATE, ('deleted', row[0], plot_id, project_id))
        # 提交事务
        conn.commit()
        return { "Remain": removed }, 200   
    finally:
        # 确保连接关闭
        conn.close()

修复方案2：使用连接池（推荐） #

频繁创建销毁连接会影响性能，用psycopg2的连接池复用连接，同时保证线程安全：

import psycopg2
from psycopg2 import pool
from flask import Flask

app = Flask(__name__)

# 初始化连接池（启动时创建）
connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    user="你的数据库用户名",
    password="你的数据库密码",
    host="127.0.0.1",
    port="5432",
    database="你的数据库名"
)

PLOT_PROJECT_CHECK = "SELECT ..."
PLOT_PROJECT_DEACTIVATE = "UPDATE ..."

@app.get("/api/plot-project/<int:plot_id>/<int:project_id>")
def check_and_deactivate(plot_id, project_id):
    # 从连接池获取连接
    conn = connection_pool.getconn()
    try:
        with conn.cursor() as cursor:
            cursor.execute(PLOT_PROJECT_CHECK, (plot_id, project_id))
            data = cursor.fetchall()
            if len(data) == 0:
                return "No data found", 404
            removed = data.pop(0)
            if len(data) > 1:
                for row in data:
                    print(row[0])
                    cursor.execute(PLOT_PROJECT_DEACTIVATE, ('deleted', row[0], plot_id, project_id))
        conn.commit()
        return { "Remain": removed }, 200   
    finally:
        # 将连接归还到池
        connection_pool.putconn(conn)

二、前端问题与优化 #

1. 并行请求加剧后端压力 #

原代码中forEach搭配async/await不会串行执行请求，而是同时发起所有请求，加剧连接冲突。改成for...of实现串行请求：

const handleGet = async () => {
  for (const item of data) {
    await getData(item.plotID, item.projectID);
  }
}

2. no-cors模式导致无法解析响应 #

mode: 'no-cors'会让fetch返回不透明响应，无法解析JSON。后端配置CORS后，去掉该模式：

const getData = async (plotID, projectID) => { 
  try {
    const response = await fetch(`http://127.0.0.1:5000/api/plot-project/${plotID}/${projectID}`, {  
      method: 'GET',  
      headers: {    
        'Content-Type': 'application/json',    
        'Authorization': `Bearer ${token}`  
      }
    });
    if (!response.ok) {
      throw new Error(`HTTP错误：${response.status}`);
    }
    const responseData = await response.json();
    console.log('mapping', plotID, "to", projectID);
    console.log('request succeeded with JSON response', responseData);
  } catch (error) {
    console.log('mapping', plotID, "to", projectID);
    console.log('请求失败', error);
  }
}

3. 后端配置CORS #

安装Flask-CORS扩展解决跨域问题：

pip install flask-cors

from flask_cors import CORS

app = Flask(__name__)
CORS(app)  # 生产环境可指定允许的源，如CORS(app, origins="http://localhost:3000")

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