Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

FastAPI, return a File response with the output of a sql query

I'm using FastAPI and currently I return a csv which I read from SQL server with pandas. (pd.read_sql()) However the csv is quite big for the browser and I want to return it with a File response: https://fastapi.tiangolo.com/advanced/custom-response/ (end of the page). I cannot seem to do this without first writing it to a csv file which seems slow and will clutter the filesystem with csv's on every request.

So my questions way, is there way to return a FileResponse from a sql database or pandas dataframe.

And if not, is there a way to delete the generated csv files, after it has all been read by the client?

Thanks for your help!

Kind regards,

Stephan

like image 422
SDuma Avatar asked Apr 10 '20 12:04

SDuma


People also ask

How do I return files on FastAPI?

To return a response with HTML directly from FastAPI, use HTMLResponse . Import HTMLResponse . Pass HTMLResponse as the parameter response_class of your path operation decorator.

How do I return data in JSON format in FastAPI?

When you create a FastAPI path operation you can normally return any data from it: a dict , a list , a Pydantic model, a database model, etc. By default, FastAPI would automatically convert that return value to JSON using the jsonable_encoder explained in JSON Compatible Encoder.


Video Answer


3 Answers

Based HEAVILY off this https://github.com/tiangolo/fastapi/issues/1277

  1. Turn your dataframe into a stream
  2. use a streaming response
  3. Modify headers so it's a download (optional)
    from fastapi.responses import StreamingResponse
    import io
    
    @app.get("/get_csv")
    async def get_csv():
    
       df = pandas.DataFrame(dict(col1 = 1, col2 = 2))
    
       stream = io.StringIO()
    
       df.to_csv(stream, index = False)
    
       response = StreamingResponse(iter([stream.getvalue()]),
                            media_type="text/csv"
       )
    
       response.headers["Content-Disposition"] = "attachment; filename=export.csv"

       return response
like image 95
Tom Greenwood Avatar answered Oct 10 '22 21:10

Tom Greenwood


I was beating my head against the wall on this one as well. My use case is slightly different as I am storing images, pdfs, etc. as blobs in my maria database. I found that the trick was to pass the blob contents to BytesIO and the rest was simple.

from fastapi.responses import StreamingResponse
from io import BytesIO

@router.get('/attachment/{id}')
async def get_attachment(id: int):
    mdb = messages(s.MARIADB)

    attachment = mdb.getAttachment(id)
    memfile = BytesIO(attachment['content'])
    response = StreamingResponse(memfile, media_type=attachment['contentType'])
    response.headers["Content-Disposition"] = f"inline; filename={attachment['name']}"

    return response
like image 36
David W. Avatar answered Oct 10 '22 21:10

David W.


Adding to the code that was previously mentioned, I found it useful to place another response header, in order for the client to be able to see the "Content-Disposition". This is due to the fact, that only CORS-safelisted response headers can be seen by default by the client. "Content-Disposition" is not part of this list, so it must be added explicitly https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Access-Control-Expose-Headers.

I don't know if there is another way to specify this, for the client or server in a more general way so that it applies to all the necessary endpoints, but this is the way I applied it.

@router.post("/files", response_class = StreamingResponse)
async def anonymization(file: bytes = File(...), config: str = Form(...)):
    # file as str
    inputFileAsStr = StringIO(str(file,'utf-8'))
    # dataframe
    df = pd.read_csv(inputFileAsStr)
    # send to function to handle anonymization
    results_df = anonymize(df, config)
    # output file
    outFileAsStr = StringIO()
    results_df.to_csv(outFileAsStr, index = False)
    response = StreamingResponse(
        iter([outFileAsStr.getvalue()]),
        media_type='text/csv',
        headers={
            'Content-Disposition': 'attachment;filename=dataset.csv',
            'Access-Control-Expose-Headers': 'Content-Disposition'
        }
    )
    # return
    return response
like image 4
dmm98 Avatar answered Oct 10 '22 22:10

dmm98