CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyfastapi
Slav Kirilov
Slav Kirilov

Reputation: 83

Python + sqlalchemy + for loop + psql* in fastapi =? Detached session

I have this scenario for everyone to ponder upon. I have a database manager which creates an engine and provides a session when needed.

import os

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session


class DatabaseManager:
    def __init__(self):
        self.engine = create_engine(
            os.environ.get("CONNECTION_STRING", ""),
            pool_size=100,
            max_overflow=0,
        )
        self.session = Session(engine)

    def get_db(self):
        return self.session


database_manager = DatabaseManager()

I would create a small method to get data from the db and return it in a list format.

from typing import List
from models import Item
from sqlalchemy.orm import Session
def get_all_items(db: Session) -> List[Item]:
    return db.query(Item).all()

Now create an endpoint which will use all of these:

from somewhere import get_all_items

@app.post("/")
def index(db: Session = Depends(database_manager.get_db)):
    processed_items = [ item.name for item in get_all_items(db=db) if item.value > 2]
    return {"items": processed_items}

And the question is as follows. I have been running this with no problems initially but at some point I get DetachedSessionError from sqlalchemy telling me the session is already in use. There are a lot more calls that fetch from db in the project but is it possible the session detachment to be caused by looping directly over the items or is it the database instance manager that bricks at some point?

One more thing: the DatabaseManager works for all other operations after the Detached error, but it consistently fails on the method described above. Once the app is restarted it goes away of course but that makes me wonder even more towards what the issue is. As all methods use the same Session.

I tried to make it as a separate alias to hold the values returned. No info if the problem persists yet. Will update!

Upvotes: 0

Views: 196

Answers (1)

Ian Wilson
Ian Wilson

Reputation: 9099

I don't see how it could relate to looping EXCEPT if that is taking longer then it is just holding the session for long enough for another thread to use it and then triggering an error. You need to use a thread local aware session maker as well as need to close the session as people mentioned in the comments. Like most threading bugs the behavior is "erratic": sometimes things might just work, especially with manually testing that is just using a single thread, and other times things break randomly. You could test this by opening a slow request in one browser tab and then opening the same request in another browser tab so they are running in parallel.

The Fast API tutorial explains how to set things up correctly import-the-sqlalchemy-parts and see the first (about close()) and second tip (about SessionLocal vs Session) here create-a-dependency.

In your specific case this might work:

class DatabaseManager:
    def __init__(self):
        self.engine = create_engine(
            os.environ.get("CONNECTION_STRING", ""),
            pool_size=100,
            max_overflow=0,
        )
        self.Session = sessionmaker(self.engine)

    def get_db(self):
        sess = self.Session()
        try:
            yield sess
        finally:
            sess.close()

Upvotes: 1

Related Questions