[FIXED] psycopg2.ProgrammingError: the connection cannot be re-entered recursively

Issue

Am calling an endpoint from flask using fetch api from react. I keep getting psycopg2.ProgrammingError: the connection cannot be re-entered recursively

I the endpoint call is inside a loop.

@app.get("/api/plot-project/<int:plot_id>/<int:project_id>")
def check_and_deactivate(plot_id, project_id):
    with connection:
        with connection.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))
            return { "Remain": removed }, 200   

The react fuctions

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

the fetch handle

  const getData = async (plotID, projectID) => { 
    fetch(`http://127.0.0.1:5000/api/plot-project/${plotID}/${projectID}`, {  method : 'GET',  mode: 'no-cors', headers : {    'Content-Type': 'application/json',    'Authorization': `Bearer ${token}`  }})
      .then(data => data.json())
      .then((response) => {
        console.log('mapping', plotID, "to", projectID)
        console.log('request succeeded with JSON response', response)
      }).catch(function (error) {
        console.log('mapping', plotID, "to", projectID)
        console.log('no mapping yet')
      });
  }

Solution

This error happens when you are trying to call the context manager of the same connection, which is already called in the context manager.

To fix that issue you have few options:

• To use the same connection without context manager and to commit or rollback changes. The code will look like:

    try:
        result = None
        with connection.cursor() as cursor:
            cursor.execute(PLOT_PROJECT_CHECK, (plot_id, project_id))
            data = cursor.fetchall()
            if len(data) == 0:
                result = "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))
            result = { "Remain": removed }, 200   
            conn.commit()
        return result
        except Exception as e:
            logging.error(f"An error occurred: {str(e)}")
            conn.rollback()
        return "Database error", 500

• To use the same connection without context manager and to use autocommit. The code will look like:

    connection.autocommit = True (you can place that code where you are making the connection)
    with connection.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))
            return { "Remain": removed }, 200   

• To use connection pool and to enter the context manager of separate connection for every request.

Answered By - Mr. Terminix