[Python] SQLAlchemy: Using sessionmaker, why do I get too many connections?

I have two wrapper functions to create an engine, a sessionmaker and a session:

@functools.lru_cache(maxsize=None)
def _get_sessionmaker() -> sessionmaker:
engine = get_engine()
sessionfactory = sessionmaker(bind=engine)
return sessionfactory


@functools.lru_cache(maxsize=None)
def get_session() -> Session:
return _get_sessionmaker()()


I call this in the function myfun() with a context manager, thus I expect the session to close properly:

def myfun():
with get_session() as session, session.begin():
# do some query


However, I noticed that the session/connection doesn't get closed/returned properly (see test below). (This shouldn't be the case, because eventually I have an issue with too many connections on my server.) Why is this the case?

I would like to understand this behavior. If I just run it without the wrappers, there is no problem:

def myfun_withoutwrapper():
engine = get_engine()
Session = sessionmaker(bind=engine)
with Session() as session, session.begin():
# some query


Thanks!


I test the number of connections before and after executing myfun() with the following code:

# test number of connections before and after
engine = get_engine()

count_sql = """SELECT COUNT(*) FROM pg_stat_activity;"""

with engine.connect() as conn:
pre_count_df = pd.read_sql(count_sql, conn)

# run myfun()
_ = my_fun()

with engine.connect() as conn:
post_count_df = pd.read_sql(count_sql, conn)


What I get is a difference of 1, i.e. after running myfun() I have one connection more, which shouldn't be the case. (As a proof, I ran the same counting test without calling myfun(), and I got the same number of connections. So the difference of 1 definitely comes from myfun(), regardless how many times I run it.)

I use Python 3.12.8, sqlalchemy 2.0.38, with a postgres 16 database.

Continue reading...