Flask API facing InterfaceError with PostgreSQL

Question

I have a Flask API based on Flask RestPlus extension and is hosted on Google App Engine. The API does a basic job of fetching data from a Google Cloud SQL PostgreSQL. The API is working fine otherwise but sometimes it starts returning InterfaceError: cursor already closed.

Strangely, when I do a gcloud app deploy, the API starts working fine again.

Here's a basic format of the API:

import simplejson as json
import psycopg2

from flask import Flask, jsonify
from flask_restplus import Api, Resource, fields
from psycopg2.extras import RealDictCursor

app = Flask(__name__)
app.config['SWAGGER_UI_JSONEDITOR'] = True
api = Api(app=app,
          doc='/docs',
          version="1.0",
          title="Title",
          description="description")

app.config['SWAGGER_UI_JSONEDITOR'] = True
ns_pricing = api.namespace('cropPricing')

db_user = "xxxx"
db_pass = "xxxx"
db_name = "xxxxx"
cloud_sql_connection_name = "xxxxxx"

conn = psycopg2.connect(user=db_user,
                        password=db_pass,
                        host='xxxxx',
                        dbname=db_name)


@ns_pricing.route('/list')
class States(Resource):
    def get(self):
        """
        list all the states for which data is available

        """
        cur = conn.cursor(cursor_factory=RealDictCursor)
        query = """
                SELECT
                    DISTINCT state
                FROM
                    db.table
                """
        conn.commit()
        cur.execute(query)
        states = json.loads(json.dumps(cur.fetchall()))
        if len(states) == 0:
            return jsonify(data=[],
                           status="Error",
                           message="Requested data not found")
        else:

            return jsonify(status="Success",
                           message="Successfully retreived states",
                           data=states)

What should I fix to not see the error anymore?

Does this answer your question? psycopg2 : cursor already closed — gksingh
– gksingh, Commented Dec 28, 2019 at 12:00

gksingh · Accepted Answer · 2019-12-28 12:13:08Z

2

It would be good to use the ORMs such as SQLAlchemy / Flask-SQLAlchemy which would handle the establishing / re-establishing the connection part.

Though, if using psycopg2. you can use try except to catch the exception and re-establish the connection again.

try:
    cur.execute(query)
except psycopg2.InterfaceError as err:
    print err.message
    conn = psycopg2.connect(....)
    cur = conn.cursor()
    cur.execute(query)

answered Dec 28, 2019 at 12:13

gksingh

3253 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Flask API facing InterfaceError with PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related