Connection to database lost in Flask app and no reconnecting

Question

I have a Flask app deployed on Kubernetes that in the backend fetches data from the database and displays it in the frontend. I make the connection using pyodbc and read the data using pandas. If there's an error, I try to catch it in try-except statements and connect to the database again. Every time I deploy my app after a few hours I get below error:

pandas.errors.DatabaseError: Execution failed on sql 'SELECT * FROM registry.data': ('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]Communication link failure (0) (SQLExecDirectW)')

If I manually restart the deployment on kubernetes, then the connection is made again and the app starts to work again. But if not, it seems that the connection isn't being restarted and the app is stuck on the above error. Below is my code that connects to the database and an example endpoint where I fetch the data from the database. Is there something that I'm missing? Should I try to reconnect to the database in some other way?

from flask import Flask, jsonify
from flask_cors import CORS
from markupsafe import escape
import pyodbc
import pandas as pd
import os

db_connection_string=(
        'DRIVER=ODBC Driver 18 for SQL Server;'+
        'SERVER='+os.getenv('SERVER_NAME') +';'+
        'Database='+os.getenv('DB_NAME') +';'+
        'UID='+os.getenv('SERVER_ADMIN_LOGIN') +';' +
        'PWD='+os.getenv('PASSWORD') +';'
        )

global conn
conn = pyodbc.connect(db_connection_string)

app = Flask(__name__, static_folder='../build', static_url_path='/')
CORS(app)

def get_data():
    df = pd.read_sql('SELECT * FROM registry.data', conn)
    df = df.sort_values(by=["name"])
    df["id"] = range(1, len(df) + 1)
    return df

@app.route("/api/data/")
def api_data():
    try:
        df = get_data()
        return df.to_dict('records')
    except Exception as e:
        conn = pyodbc.connect(db_connection_string)
        df = get_data()
        return df.to_dict('records')

I was also thinking about starting a new connection in every endpoint I have and closing it immediately after getting the data, but I'm not sure if it's the best and most effective solution.

Answer 1

The pattern usually used in a Flask application is to create a new database connection for each request, and then close the connection when the request ends. As documented in the Flask documentation, you can store the database connection in the application context, inside the g object.

First, create a get_db function that should create a new connection the first time it is called during a request, and add it to g. When called again during a request, the get_db function will just return the already created db connection that was added to g earlier. You would call your get_db function inside of any of your functions to get the db connection.

Second, create a teardown_db function, which will pop the db connection off of the g object and then close the connection if it was created earlier (db is not None). Then you would register your teardown_db function using the app.teardown_appcontext function, either by decorating your teardown function with @app.teardown_appcontext or by just calling it directly like so: app.teardown_appcontext(teardown_db).

Here is the example from the pallets project documentation:

from flask import g

def get_db():
    if 'db' not in g:
        g.db = connect_to_database()

    return g.db

@app.teardown_appcontext
def teardown_db(exception):
    db = g.pop('db', None)

    if db is not None:
        db.close()