I'm using fastapi but I think this is maybe a general python/postgres question.
@app.get("/company/{company_ticker}/carbon-footprint")
async def getCompanyFootprint(
company_ticker: str,
http_client: aiohttp.ClientSession = fastapi.Depends(http_client)
):
query_statement = "select * from company.carbon where upper(ticker) = $1 order by year ASC"
#sending query to DB then return the results as a json
result_data = await main_db_instance.fetch_rows(query_statement, company_ticker.upper())
result_data_json = jsonable_encoder(result_data)
return JSONResponse(result_data_json)
But I want to also have a different endpoint that has similar logic as the above endpoint but aggreated over multiple inputs:
@app.post("/portfolio/analytics/carbon-footprint")
async def getPortfolioCarbonFootprint(
tickers: list = Body(...),
http_client: aiohttp.ClientSession = fastapi.Depends(http_client)
):
portfolioDF = pd.DataFrame()
#take the list of inputs and cycle through them one by one.
for element in tickers:
jsonResults= jsonable_encoder (await getCompanyFootprint(company_ticker=element.upper() ) )['body']
elementDF=pd.read_json(jsonResults)
portfolioDF = portfolioDF.append(elementDF, ignore_index = True)
portfolioDF = portfolioDF.select_dtypes(['number'])
gr=portfolioDF.groupby('year').agg('mean')
return portfolioDF.to_json()
As you can see I call the function one at a time but if a list of 1000 items is sent it's very very slow.
I thought maybe I could modify my postgres query to:
"select * from company.carbon where upper(ticker) = any($1) order by year ASC"
but I'm getting an error at company_ticker for the function getCompanyFootprint. I think it's due me trying to send it a list instead of a simple string.
Is there a way to have the endpoint accept a single string (via get) or multiple strings in a list(when calling the function directly)? Or is there a better way to approach this problem?
