Explaination
Hello, I'm currently doing a web app with Python/Flask. This application will do some tests on Router/Switches and store results in a postgreSQL database. I'm showing this results in a table using psycopg2 to connect to the database and extract these data.
This app is designed to run 24 hours a day. But the problem is, after 20/30 minutes I have a 500 Internal Server Error while I'm loading a webpage of the app. When I'm looking the logs here is the error :
psycopg2.OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
If I refresh a second times, I have another error message :
psycopg2.InterfaceError: connection already closed
And if I refresh a third time, it's working again but it's not possible to run this app in production if it crashes every 20/30 minutes.
I don't understand why I have this error and how I could resolve it. Here is some code :
Some code
main.py
app = Flask(__name__)
app.config['SECRET_KEY'] = "mysecretkey"
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(minutes=30)
app.config['postgreSQL_pool'] = psycopg2.pool.SimpleConnectionPool(1, 20,
user = "username",
password = "password",
host = "myserver",
port = "5432",
database = "stackoverflow")
def get_db():
if 'db' not in g:
g.db = app.config['postgreSQL_pool'].getconn()
return g.db
@app.teardown_appcontext
def close_conn(e):
db = g.pop('db', None)
if db is not None:
app.config['postgreSQL_pool'].putconn(db)
@app.route('/', methods=['GET', 'POST'])
@login_required
def home():
"""
Retourne la page principale avec la liste des équipements + export CSV possible
"""
db = get_db()
cursor = db.cursor()
mois_lot = "Juin"
cursor.execute("""SELECT *
FROM stackoverflow WHERE lot='%s'""" % (mois_lot))
liste_lot = cursor.fetchall()
count = Check.count(mois_lot, cursor)
count_ok = Check.count_ok(mois_lot, cursor)
count_ko = Check.count_ko(mois_lot, cursor)
if request.method == 'POST':
liste_lot.insert(0, ('IP Admin',
'Resultat',
'Equipement',
'Date derniere verification'))
cursor.close()
return excel.make_response_from_array(liste_lot, "csv", file_name="export_lot")
else:
cursor.close()
return render_template('index.html',
value=liste_lot,
value2=count,
value3=count_ok,
value4=count_ko)
check.py
class Check():
"""
Classe check des équipements
"""
@staticmethod
def count(lot, cursor):
"""
Compte le nombre d'équipements
"""
cursor.execute("""SELECT COUNT(*) FROM stackoverflow WHERE lot='%s'""" % (lot))
nb_equipement = cursor.fetchall()
nb_equipement = nb_equipement[0][0]
return nb_equipement
@staticmethod
def count_ok(lot, cursor):
"""
Compte le nombre d'équipement OK
"""
cursor.execute("""SELECT COUNT(*) FROM stackoverflow WHERE resultat='OK' and lot='%s'""" % (lot))
nb_ok = cursor.fetchall()
nb_ok = nb_ok[0][0]
return nb_ok
@staticmethod
def count_ko(lot, cursor):
"""
Compte le nombre d'équipement KO
"""
cursor.execute("""SELECT COUNT(*)
FROM stackoverflow WHERE lot='%s' and (resultat='KO' OR resultat IS NULL)""" % (lot))
nb_ko = cursor.fetchall()
nb_ko = nb_ko[0][0]
return nb_ko
Expected
I would like the app to not crash every 20/30 minutes. I think, I open and close the database connection correctly but maybe I'm wrong.
Thanks in advance for any help.
