Flask/SQLAlchemy - unable to remove Session object in an application factory

Question

I'm trying to remove a scoped_session object at request teardown as the SQLAlchemy docs suggest but either I am misunderstanding the process (likely) or I'm doing something wrong and it's not working properly (just as likely).

I'm working blueprints, and here is how I'm initializing the DB connection:

# this is db.py

import os
import urllib
import pyodbc
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, exc

from config import Config as app_config

from .models import Base


# Turn off pyodbc pooling
pyodbc.pooling = False

# TODO: put these into ENV variables
connection_params = {
    # params here
}
db_params = urllib.parse.quote_plus(("DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password};TDS_Version=7.2;").format(**connection_params))

connection_string = "mssql+pyodbc:///?odbc_connect=%s" % db_params

engine = create_engine(
    connection_string, connect_args={"check_same_thread": False}
)

Base.prepare(engine, reflect=True)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# this is application.py

from flask import Flask, _app_ctx_stack
from sqlalchemy.orm import scoped_session

from config import Config
from .db import SessionLocal


def create_app(config_class=Config):
    app = Flask(__name__, instance_relative_config=False)
    app.config.from_object(config_class)

    # Database init
    app.session = scoped_session(SessionLocal, scopefunc=_app_ctx_stack.__ident_func__)

    with app.app_context():
        from application.errors import errors_bp
        app.register_blueprint(errors_bp)

        from application.main import main_bp
        app.register_blueprint(main_bp)
    
        return app

And this how I am tearing down the request in the main blueprint's routes.py file:

@main_bp.teardown_request
def remove_session(*args, **kwargs):
    current_app.session.remove()

I thought that if I do a session.remove() that would release the connection in the pool, but I still see a connection sleeping in the database. I also get the same memory address if I print out the session before and after the .remove()

teardown - session before remove

teardown - session After remove

How do I check if the session has been removed correctly?

I know a lot of the above is not up to par, I'm learning so I'd appreciate notes on how to do it better.

Flask/SQLAlchemy - unable to remove Session object in an application factory

0 Answers