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

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 import FastAPI
from fastapi.responses import StreamingResponse
import io
import pandas as pd

app = FastAPI()

@app.get("/get_csv")
async def get_csv():
    df = pd.DataFrame(dict(col1 = 1, col2 = 2), index=[0])
    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