2

I have the following class in my code, which manages all the database connections:

class Database:
    # There's more code here, but the important part is the one below

    def get_status(self) -> dict:
        self.connect_db() # Connects to the database and store it's connection in self.conn

        df_data = pd.read_sql("""SELECT col1, col2, col3 FROM table1""", self.conn)
        df_info = pd.read_sql("""SELECT col1, col2, col3 FROM table2""", self.conn)
        
        self.disconnect_db() # Closes the database connection
        
        return [df_data.to_dict(orient="list"), df_info.to_dict(orient="list")]

db = Database()

I have to call db.get_status() in a FastAPI route:

@app.get("/api/get_status")
async def get_status_api():
    return db.get_status()

The problem is, it takes a lot of time to complete, and while it is running, the entire website is blocked.

I tried parallelism with asyncio, however the get_status() function long time happens because of a CPU-intensive operation, not because of the database request.

Besides asyncio, I've already tried the following:

@app.get("/api/get_status")
async def get_status_api():
    data = {}
    thread = threading.Thread(target=db.get_status, args=(data,)) # Passing data as argument to simulate the returning value
    thread.start()
    thread.join()
    return data

@app.get("/api/get_status")
async def get_status_api():
    data = {}
    thread = multiprocessing.Process(target=db.get_status, args=(data,)) # Passing data as argument to simulate the returning value
    thread.start()
    thread.join()
    return data

@app.get("/api/get_status")
async def get_status_api():
    with ThreadPoolExecutor() as executor:
        data = list(executor.map(db.get_status, [None]))[0] #Altered the db.get_status() signature to get_status(self, _)
    return data

But no luck so far. So, how can I not block the entire website while pd.read_sql() is running? Taking a long time to run the query is fine, as long as it can handle parallel requests.

Semanual
  • 53
  • 7
  • If you remove the `async` part in front of your view function FastAPI will run it in a thread internally; have you tried that? How are you running your application? Does the ASGI server have more than one worker? – MatsLindh Aug 06 '22 at 21:58
  • I'm using uvicorn with `uvicorn.run(app, host="0.0.0.0", port=3000)`, but removing the async worked. I can't believe that the solution was simply to remove the async in the function. – Semanual Aug 06 '22 at 22:03

1 Answers1

2

As MatsLindh pointed out, the solution was simply to remove the async from the route function, as FastAPI internally already run it on a thread.

@app.get("/api/get_status")
def get_status_api():
    return db.get_status()

Altering the route function to the above worked.

Semanual
  • 53
  • 7
  • Please have a look at [**this answer**](https://stackoverflow.com/a/71517830/17865804) for more details on `def` vs `async def`. – Chris Aug 07 '22 at 03:45