CODEWITHSUNDEEP
pythonpostgresqlflask
Kostas
Kostas

Reputation: 390

Flask auto-reconnect PostgreSQL

I have a Flask app that connects to a remote PostgreSQL database (on Amazon RDS). Here is an example of how I connect to the database:

import psycopg2
import sys

from flask import (Flask, render_template, request, redirect, url_for)


# Start a Flask app
app = Flask(__name__, static_url_path='')

# Connect to RDS database
con = psycopg2.connect(**params)
cur = con.cursor()

@app.route('/', methods=['GET'])
def index():
    global cur

    args = {}
    try:
        cur.execute("SELECT * FROM x_table")        
    except psycopg2.InterfaceError:
        con = psycopg2.connect(**params)
        cur = con.cursor()

    # some additional code here ...
    return render_template('index.html', args=args)


if __name__ == "__main__":
    app.run(debug=True, host="0.0.0.0")

The problem I have is that after a few minutes of inactivity, the connection closes and I have to restart the app. I have added an exception control to catch that error and reconnect, but I still get the error that the cursor is already closed.

What is the proper way of automatically reconnecting?

Upvotes: 1

Views: 1471

Answers (1)

Cyrbil
Cyrbil

Reputation: 6478

You need to create a cursor for every query. You might use a cursor for many query if they are run together. But overrall stick with a new cursor each time:

con = psycopg2.connect(**params)

def run_query(query):
    with closing(con.cursor()) as cur:
        try:
            cur.execute(query)
            con.commit()
            if cur.rowcount > 0:
                return cur.fetchall()
            else:
                return cur.lastrowid
        except:
            con.rollback()
run_query(SELECT * FROM x_table)

Upvotes: 1

Related Questions